Implement ASP.NET Membership into a new database

Almost of the systems created will have a function to login to manage all of its data, it called with a namely is Login function. So, Microsoft created a component as a template to do this work to save your time when building a new system.

You want to work with this function, then firstly you need to know how to integrate it into your SQL database.

• Step 1: Do the easily work to create the new database in SQL server.
• Step 2: Once your database has been created, then you will do the work to integrate the membership in to its.
• Step 3: Open the .NET Framework folder putted inside window folder as the path:C:\Windows\Microsoft.NET\Framework\v4.0.30319 (depends on your .NET framework version then your path will different), then run the application namely aspnet_regsql.exe.

Step 4: It will show an ASP.NET SQL Server Setup Wizard dialog. Click the Next button to go to the next step

• Step 5: Choose Configure SQL Server for application services if you want to apply this function to the new database. Otherwise, choose Remove application services information from an existing database if you want to remove this function from a database which is implementing it. In this guide I will chose option 1 is implementing it to the new one database.
Select option 1 and clicking to Next button, it will show a dialog as in step 6.

• Step 6: You need to put the information of your server and then choosing the database that you want to implement this function.

Once you selected the database, then clicking to Next button. Click Next button one more time to confirm you exactly want to implement this function into selected database. Waiting some seconds before the work is done. Click Finish button to complete your work.

• Step 7: Open your SQL server to check the result. If your work is successfully then your db will has some tables as the screenshot. 

Hope that helps.

Mouse events on Transparent Element

Recently I am working on a project named 2011.

I am working on a Document Manager Workflow Module, in that project i need to render a div element on a image element to draw some annotation. So what I wanted is to draw a div on image with transparent background and then catch a mouse click event on that div.

I were trying to achieve that since last 3 days but I couldn't. If I am setting background color or background image than I were able to trap that mouse event in IE but if I am setting transparent background than I weren't able to trap any mouse event.

After that googling but not get any solution or might be I were not able to search it properly.

So finally I were make some changes in my code logically, and at the end I got succedd.

What I have done is set transparent background then set a transparent image as a background of that div.

How it is working?

I need transparent background so I set transparent background using style attributes and then I overlap transparent image on that so that transparent image is able to trap mouse event

or simple way

I need transparent background so I set background with transparent image so that transparent image is able to trap mouse event

Here is the faulty code

<img src="yourimagepath" alt='' />

<div style="position:absolute;top:0px;left:0px;padding:10px;width:128px;height:128px;background-color:Transparent;" onclick="alert('you clicked me');">

</div>

Here you will not be able to trap onclick event of the div

Here is the final and working code

<img src="yourimagepath" alt='' />

<div style="position:absolute;top:0px;left:0px;padding:10px;width:128px;height:128px;background-image:url(yourtransparentimagepath);" onclick="alert('you clicked me');">

</div>

Here you will be able to trap onclick event of the div

* Note change image source with your image file path.

Find out the used space for a Table in SQL Server

Some time we would like to know how much space table is using to store the data on a disk.


So SQL Server has a built in SP to find out the used space by table.


sp_spaceused '‘Tablename'


once you will execute above stored procedure you will see the following result.

Actually it is very useful that how much amount of space data is occuping on the disk.


If you have a no of tables in database and you don't want to execute sp one by one then here is
the stored procedure which will retrieve all the talbe name from the system object and then will
pass one by one as a parameter to previous described sp and display the result on the screen at shot


Here is the code for SP




CREATE PROCEDURE [dbo].[dms_FindAllTableUsedSpace]

AS




DECLARE @TName VARCHAR(100)




--Declare cursor to find available tables in system objects

DECLARE curtablelist CURSOR

FOR

select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY




-- Create a temp table to hold the table name temporarily and then loop through all the table and run sp with that table name

CREATE TABLE #TableListTemp

(

TName varchar(100), NofR varchar(100), ResSize varchar(50), DataSize varchar(50), IndexSize varchar(50), FreeSize varchar(50)

)




-- We are opening cursor here

OPEN curtablelist




--Get the first table name from the cursor

FETCH NEXT FROM curtablelist INTO @TName




--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TableListTemp

EXEC sp_spaceused @TName




--Get the next table name

FETCH NEXT FROM curtablelist INTO @TName

END




--now close the cursor

CLOSE curtablelist

-- deallocate the used memory by cursor

DEALLOCATE curtablelist




--retrieve all the records from the temp table

SELECT * FROM #TableListTemp




--Drop the temp table free up the memory

DROP TABLE #TableListTemp







Here is the code to execute the SP

EXEC dms_FindAllTableUsedSpace

Disable a trigger temporarily

In my project I was thinking is it possible to disable all triggers temporarily?
Then answer is yes. We can disable it. The alternate is we can drop them and re-create it but it's not good way.


Any how to disable trigger it's not good idea because it can create a lot more issue with the data integrity. But you can perform this action when you alone are working on a database or in test environment or bulk inserting data.

But make sure once you have done your work enable all the trigger so you can maintain data integrity.

While you are importing bulk data then trigger should not fire once it's done then you have to fix to fire it again when actual data is coming.

Whenever I am doing this thing then I am disabling all the trigger using following command.

To disable all constraints and trigger:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

To enable all constraints and trigger:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER

How to use Rank( ), Dense_Rank( ) and Row_Number( ) function

Understanding Row Number Function

Here we will see the Row_Number(), Rank() and Dense_Rank()
function with details and example

Look at the Figure 1  which has some data and in next
figure 2 we are going to explain about the use of above mentioned function




Above figure has query and result we can see that there are 14
rows with column jobid, job_disc, minimum value and maximum
value.


Now see figure 2




Using ROW_NUMBER() Function

In figure 2 we can see the result of ROW_NUMBER() function, it just a sequential order starts from 1 to the number of rows returns within a result set.

Using RANK() Function

In figure 2 we can see the result of RANK() function, If result of two or more rows are same then it will give same rank and put a gap of repeated no of rows in next sequential number for next row. As we can see in figure 2 for row number 2,3 and 4 has same rank as 2, and for row number 5 it direct assign rank 5 and eliminate rank 3 and 4 because it's repeated in previous row.

Using DENSE_RANK() Function

In figure 2 we can see the result of DENSE_RANK() function, if result of two or more rows are same then it will assign same rank but for the next rank of next row it will not put any gap and assign continuous number to the next rank of next row. Like above example it will not eliminate rank 3 and 4. It will assign rank 3 for row number 5th.

List all the table in given database

Think that you have a database more than 1-2 hundred database tables and you want to get list of all the tables in particular database. I was working on a project with more than 356 table and required to get the list of all the table with column names, column types and length of data type so first I searched for list of table finding query and it give me different different result.

Then I thought let me write a quick post about all the available query which can list all the tables from specific database.

For a demo purpose I have created only 2 tables in one database and write a query for quick result and better image

Let's check one by one.


SELECT * FROM information_schema.tables

Above query will return the data from information_schema table. It has only tables related info.

look at the result with below screen shot

SELECT * FROM sysobjects WHERE xtype='U'

Above query will retrieve result from system object table which have all the information related to database like Primary Key, Stored Procedure, User table etc....

look at the result with below screen shot

SELECT * FROM Sys.Tables

Above query get the result along with object_id which will be very useful for join if you want to get column names etc. This object id will be tied with other table.

look at the result with below screen shot

exec sp_msforeachtable 'print ''?'''

Above query is little bit different than all other options. It will simply prints table name on the screen rather than returning a result in table.

look at the result with below screen shot

Concatenation in SQL, Using For XML PATH(), Concatenate more than one rows in one field

Some times it is required to concatenate more no of rows into one single filed separated by commas. There are some options for that like we can write our own UDF for that, in sql server built in COALESCE function is there with this function we can achieve it. But some time need is different or we want to achieve it in different way then there is another option that I am going to explain using FOR XML PATH().

Let me first show me my table design

and also show me the data which I have inserted for this testing purpose

Have you tried to access data in xml format from sql table, not yet ? Then try below query and see the result.

SELECT Employee_Name FROM Emp_Tech FOR XML path('')

When you run above query then you can see result like <Employee Name>Tarun</Employee Name>.... conti... and when you click on the resulted row then it will open .xml file in query window. But here we are understanding how to merge that all the rows into single row with comma separated so let me write a query for that.

SELECT

 DISTINCT

Employee_tech,employee_name =

 substring( (SELECT

 ',' + Employee_Name

 FROM

Emp_Tech et2

 Where

et2.Employee_Tech = et1.Employee_Tech

 FOR XML PATH(''), ELEMENTS

),2,500)

FROM

Emp_Tech et1

And this is the out put for that query

Out put

Table and Stored Procedure helptext using Keyboard - SQL Server Keyboard Shortcut

In the world of programming keyboard shortcuts are very useful for fast developing either in SQL server, DotNet or any programming language or any database.

In my project I always use SQL Server for backend and for editing or viewing SP or Database Table structure I used right click on Database object and then select eitherdesign as Figure 1 or modify as Figure 2 option. But it’s little bit lengthy procedure for getting a result.

Figure 1

Figure 2

So I was searching for shortcuts using that we can easily get the desire result and I found that.

How to do that for Table?

In my Sample Database there is one table Customer_Orders. Just  drag that table to query window or copy the name of table and paste it to query window. Then select it and just press Alt+F1 it’s default from Management Studio and it will show each and every details in result area like Figure 3.

 Figure 3

How to do that for Sps?

In my Sample Database there is one Sp getAllOrders. Just  drag that sp to query window or copy the name of sp and paste it to query window.  Append sp_helptextat the beginning of the sp name with space and select all and press F5 key and it will show the result in below pane.

Still it’s required 2 to 3 actions so set some default key for sp_helptext command using below steps.

Step 1 Go to Toolsà Options menu like Figure 4

Figure 4

In Figure 4 from left panel select keyboard option and from right side Ctrl+F1 has empty table cell. Add sp_helptext command there and press ok. Here you can set some keyboard shortcuts if you want like Ctrl+F2, Ctrl + F3 and more

Now open new query window drag sp name on query window and select it and pressCtrl+F1 and you can see result like Figure 5.

Figure 5

Figure 6

Above Figure 5 and Figure 6 both are same but still there is a little and important difference so let me take an opportunity to explain that difference. In Figure 5 the result is displaying in grid and in Figure 6 result is displaying as a plain text. If you check at the top then there is tooltip in yellow strip with text Results to Grid andResults to Text. Both options are at the top of the query window. If you see the result in grid view then you will loose the formation of the sp like indentation, spacing, beautify etc. But if you view result in plain text format then it will preserve all the format when you have created or modified sp.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)


This is the common error for sql server while establish connection from visual studio to sql server.


If you encounter this error, while establishing connection from visual studio to to sql server, than first things to check in web.config file that have you set sql server instance name properly and correct?


You can encounter above error if you have 


Change your PC name or sql instance name
or you have installed new isntance of sql server.


EG. Currently your PC name is 'PCName', and sql instance name is 'SQLServer'
Now you are changing your computer name from 'PCName' to 'MyPC' or sql server instance name from 'SQLServer' to 'MySQLServer' then you must change connection string 
in your web.config file  like below sample connection string


Old Connection String:
Data Source=PCName\SQLServer;Initial Catalog=SampleDBName;Integrated Security=True


New Connection String:
Data Source=MyPC\MySQLServer;Initial Catalog=SampleDBName;Integrated Security=True


Solution:
First check your sql server instance name after opening sql server management studio, then check connection string in your vs.net solutions,
then compare both instance name if both instance name are not same then please make both name same and save changes you made in web.config file and run the project

SQL Server – 2008 – Generate Script of Database, Generate script to copy whole database schemas and All other object in database, like Tables with data, SPs, Views, Triggers, Functions, Database constraints etc.

While we are working on any project and uses SQL Server as a database, we often need to copy database schema either with data or without data to create database on other server. 

Here I will explain step by step process with screen shot that how we can copy database schema with all the database objects.

First of all open SQL Server Management Studio and expand databases node like below images step 1.

Step 1

Now select the database for which you want to generate SQL Script as like below image Step 2. Here I have selected pubs database for demo. Right click on selected database, click on Tasks option and then click Generate Scripts...  I have marked option with red circle in image.

Step 2

Once you click on Generate Scripts option from above image it will show up the below image step 3

Just click on the next button from below image step 3

Step 3

Now in below image you can see the list of all the available databases, by default on which database you have right clicked it is selected but you can change here if you want to generate script for other database. In Step 4 image there is one check box labelled with ‘Script all objects in the selecteddatabase’.  Check this check box if you want to generate script for all the objects from database, if you will not check this check box it will ask on next step for database object selection.

Step 4

After selecting the database and checking check box it will show up the below image step 5.

Here you can select what you want to include in script, like script table with data or without data, you want to add script like if exist then drop the object first then create it etc. For scripting table with all the data select true in script data option as I have selected in below image. Then click on next button.

Step 5

Now this step 6 has some output file type and file location related options. I have selected 3^rd radio button in below image for generating a script in New Query Window but if you don’t want to generate script in new query window and want to generate script into clipboard then select 2^nd radio button labelled with Script to Clipboard. If you want to generate script in a file then select 1^st radio button Script to file, once you select this option it will ask file name and directory to save the file on hard drive.

I have marked files to generate option as a 3 and then 3.1 and 3.2 in the red colour. If you want to generate whole script in one single file then selectsingle file (3.1), by default it is selected. If you want to generate different file per object then select 3.2 option. If you select 3.2 option and suppose you have 10 tables in database then it will generate 10 different SQL file.

Step 6

Below image is just a summary page, showing all the options you have selected till step. Just click on the finish button to proceed.

Step 7 showing the progress for generating database script

Step 8 showing the successfully completion of script generation process.

Step 9 

shows that all database object has been scripted in new query window for pubs database. Now your script is ready...