Fill dataset using stored procedure in .Net

 // Within the code body set your variable  
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataSet ds = new DataSet();
            try
            {
                string query = "exec GetData  '" + FromDate + "','" + ToDate + "'";
                using (SqlConnection connection = new SqlConnection(connectionString))
                using (SqlCommand command = new SqlCommand(query, connection))
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    adapter.Fill(ds);
                }
                return ds;
             
            }
            catch
            {
                return ds;
            }

What is cursor and how to use in sql server?

Cursor: Cursor is basically a database object to retrieve data row by from resultset. cursor is used to loop through a resultset.

Steps to use cursor:

declare @studentid int

1. Declare Cursor-

DECLARE @MyCursor CURSOR

2. Set Cursor

SET @MyCursor = CURSOR FAST_FORWARD
FOR
select studentid from tbl_students

3.Open Cursor

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @studentid
WHILE @@FETCH_STATUS = 0

WHILE @@FETCH_STATUS = 0//loop till last row

BEGIN
 query you want to execute
END

4. Close Cursor

CLOSE @MyCursor

5. Deallocate Cursor

DEALLOCATE @MyCursor

How to create non clustered index

Create Clustered Index

/*
USE [your db name]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[table_name] ([column1],[column1])
INCLUDE ([column1],[column1],[column1])
GO

How to check dead lock process id and kill that blocking process in sql server database

Query to check blocking process in sql server.

SELECT
sp.spid
, sp.blocked AS BlockingProcess
, DB_NAME(sp.dbid) AS DatabaseName
, sp.loginame,sp.cpu
, CAST(text AS VARCHAR(1000)) AS SqlStatement
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle)   order by BlockingProcess desc,sqlstatement

Kill blocking process

Kill 20 and execute

Execute Stored Procedure with parameters and Fill Dataset

Introcution: Here you can call and execute stored procedure from code behind and pass the parameters and fill dataset.

Implementation:
DataSet ds = new DataSet();

            DataTable dt;

            sb.Append("<table>");

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {

                    using (SqlCommand command = new SqlCommand("sp_DistwiseDetails1", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add("@RetriveType", SqlDbType.VarChar).Value = 2;

                        command.Parameters.Add("@FromDate", SqlDbType.VarChar).Value = DateTime.Now.AddYears(-2);

                        command.Parameters.Add("@ToDate", SqlDbType.VarChar).Value = DateTime.Now.ToShortDateString();

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(ds);
                        }

                    }
                }
                dt = ds.Tables[0];
}
catch
{
}

SQL Server Database Queries Part 1

Select Statement:

select * from Subjects;

Aliasing Column Name:
you can rename column according to your need using AS keyword.
select subjectname as subject from subjects;


Eliminating duplicate records:(Distinct Clause)
you can eliminate duplicate records using DISTINCT Keyword from resultset.
Select distinct subjectname from subjects;

Filtering Data(Where Clause):

you can filter data using WHERE clause with some condition,
Select subject;name from subjects where subjectid=3;
Select subject;name from subjects where subjectid>3;



The 'Not' Clause:

you can use NOT keyword condition
Select subjectname from subjects where subjectname IS NOT NULL;
SELECT SUBJECTNAME FROM SUBJECTS WHERE SUBJECTID NOT IN (2,3);

Filtering String:
you can filter string using LIKE keyword and %.
'H%' will return all the records start with 'H'.
'%H' will return all the records end with 'H'.
'%H%'  will return all the records start and end with 'H'.

Select subjectname from subject where name like('H%')  ;
  
The In and Between Keywords:

you can use IN clause when you have multiple ids you can get all records which are IN();
select * from subjects where subjectid in (2,3);

you can user BETWEEN clause when you want to get records between some range.

select * from subjects where subjectid BETWEEN 2 and 3;

Sorting Data(Order By Clause):
you can sort resultset using ORDER BY clause.
select * from subjects ORDER BY subjectname;

Limiting Records(Top Clause):

you can limit records by using TOP clause in your query.
select TOP 2 * from subjects ORDERBY subjectname;

OFFSET FETCH:

you can use OFFSET to keep and skip records. It is useful in paging concept.
SELECT * FROM SUBJECTS OFFSET 10 ROWS FETCH NEXT 25 ROWS ONLY;

AGGREGATING DATA; GROUP BY AND HAVING CLAUSE
you can use GROUP BY clause to get record set in Group.
SELECT COUNT(*) FROM SUBJECTS GROUP BY SUBJECTNAME;

Select from multiple tables (Using Joins)

Joins are very useful when you want to get data from multiple tables.
select * from subjects , books;

select a.subjectname,b.bookname from subjects a, books b where a.SubjectID=b.Subject;

There are different type of joins in sql.

Cross Join:

It will return Carthesian Product. 

select * from books CROSS JOIN subjects;

Inner Join:

It is the most useful join. It is optional to use Inner Keyword. It is used in place of where and is a
proper way of join. it will return all the matching rows based on conditon.

select * from subjects INNER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Outer Join:

Outer joins are used when we want to matching and unmatching data from tables. it is of two types.

Left Outer Join:

It returns all matching of left and all of right records.


SELECT * FROM SUBJECTS LEFT OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Right Outer Join:

It return all record of left and matching of right.

SELECT * FROM SUBJECTS Right OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT; 

Ranking Functions:
 
 There are four ranking functions in sql server.

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. NTILE

ROW_NUMBER simply select the no of current row.
RANK and DENSE_RANK assisgn unique number to each row.
RANK return total no of rows of same rank plus 1.
DENSE_RANK return rank plus 1.

How to add all the rows of a column to string with comma seperated

 Introduction: Here we have to add all the rows of a column to string with comma seperated.
Description:I am going to use coalesce function of sql server to achieve the result.
Query:

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ',', '') + Name
FROM tbl_Names
print @Names

How to change SQL server database mode from single user to multiuser and multi user to single user

Introduction: In this example you can set database mode to single user or multiuser

Description: Here you will see how to change database mode to single and multiuser.

Implementation:

Set Multi User Mode  to Single User Mode

USE MASTER;
GO
ALTER DATABASE [databasename] SET SINGLE_USER

Set Single User Mode to Multi User Mode 

USE MASTER;
GO
ALTER DATABASE [databasename] SET MULTI_USER

Bind Asp.net Dropdownlist from database in C#, VB.net

Introduction: Here I will explain how to bind dropdownlist from database.
Description: Today I will show you how to bind dropdownlist static and from database also I will
explain you how to find data in dropdownlist by text and by value.
Before implement this example we need database

Column Name	DataType	AllowNulls
UserId	Int(set identity true)	No
UserName	VarChar(50)	Yes
Location	VarChar(50)	Yes

Once table is designed write below code to your aspx page

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>how to show data in dropdownlist from database in asp.net</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<b>Selected UserName:</b>

<asp:DropDownList ID="ddlUser" runat="server" />

</div>

</form>

</body>

</html


Now add the following namespace to code behind

C# code

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

After add namespace write the following code to code behind

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindUserdropdown();

}

}

/// <summary>

/// Bind Userdropdown

/// </summary>

protected voidBindUserdropdown ()

{

//conenction path for database

using (SqlConnection con = new SqlConnection("Data Source=Manoj;Integrated Security=true;Initial Catalog=MySchool"))

{

con.Open();

SqlCommand cmd = new SqlCommand("Select UserId,UserName FROM UserInformation", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

ddlUser.DataSource = ds;

ddlUser.DataTextField = "UserName";

ddlUser.DataValueField = "UserId";

ddlUser.DataBind();

ddlUser.Items.Insert(0, new ListItem("--Select--", "0"));

con.Close();

}

}
VB.Net Code 
 

Imports System.Data

Imports System.Data.SqlClient

Imports System.Web.UI.WebControls

Partial Class VBSample

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

If Not IsPostBack Then

BindUserdropdown()

End If

End Sub

''' <summary>

''' Bind UserDropdownlist

''' </summary>

Protected SubBindUserdropdown ()

'conenction path for database

Using con As New SqlConnection("Data Source=Manoj;Integrated Security=true;Initial Catalog=MySchool")

con.Open()

Dim cmd As New SqlCommand("Select UserId,UserName FROM UserInformation", con)

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet()

da.Fill(ds)

ddlUser.DataSource = ds

ddlUser.DataTextField = "UserName"

ddlUser.DataValueField = "UserId"

ddlUser.DataBind()

ddlUser.Items.Insert(0, New ListItem("--Select--", "0"))

con.Close()

End Using

End Sub

End Class
 

Stored Procedure to Get all table names from database

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[proc_GetAllTables]      
        
AS   
       
BEGIN   
   
 Begin Try   

  SELECT name FROM sys.Tables
       
 End Try   
   
 BEGIN CATCH   
   
     return  '0';   
   
 END CATCH   
   
END

Stored Procedure to activate or deactivate user

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[proc_ActivateDeactivateUser]      
 ( 
 @UserId varchar(50),
 @IsActive bit, 
 @LastModifiedBy int 
 ) 
AS   
       
BEGIN   
   
 Begin Try 

    IF(@IsActive = 1)
        BEGIN
            set @IsActive=0;
        END
    ELSE
        BEGIN
            set @IsActive=1;;
        END

   Update tbl_Users set IsActive=@IsActive,LastModifiedBy = @LastModifiedBy where UserId =@UserId  
 
 End Try   
   
 BEGIN CATCH   
   
     return  '0';   
   
 END CATCH   
   
END

Stored Procedure to delete user by userid with exception handling

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[proc_DeleteUser]      
 ( 
 @UserId varchar(50), 
 @LastModifiedBy int 
 ) 
AS   
       
BEGIN   
   
 Begin Try    

   Update tbl_Users set IsDeleted='true',LastModifiedBy = @LastModifiedBy where UserId =@UserId  
       
 End Try   
   
 BEGIN CATCH   
   
     return  '0';   
   
 END CATCH   
   
END 

GO

Stored Procedure to delete user by UserId with exception handling

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[proc_DeleteUser]      
 ( 
 @UserId varchar(50), 
 @LastModifiedBy int 
 ) 
AS   
       
BEGIN   
   
 Begin Try    

   Update tbl_Users set IsDeleted='true',LastModifiedBy = @LastModifiedBy where UserId =@UserId  
       
 End Try   
   
 BEGIN CATCH   
   
     return  '0';   
   
 END CATCH   
   
END 

GO

Stored Procedure to Validate duplicate entry for same user at the time of insert and update

/****** Object:  StoredProcedure [dbo].[proc_IsExistUser]    Script Date: 09/18/2014 08:23:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec [proc_IsExistCategory] 'aa'   
CREATE Procedure [dbo].[proc_IsExistUser]          
 (     
  @UserId bigint, 
  @UserName varchar(50)     
 )     
AS       
           
BEGIN      
       
 Begin Try   
 if (@UserId = 0)     
   select * from View_Users where UserName =@UserName      
 else 
   select * from View_Users where UserName =@UserName and UserId <> @UserId     
  
           
 End Try       
       
 BEGIN CATCH       
       
     return  '0';       
       
 END CATCH       
       
END
GO

Stored procedure to Validate duplicate insert for same Username

/****** Object:  StoredProcedure [dbo].[proc_IsExistUser]    Script Date: 09/18/2014 08:23:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec [proc_IsExistUser] 'aa'   
CREATE Procedure [dbo].[proc_IsExistUser]          
 (       
  @UserName varchar(50)     
 )     
AS       
           
BEGIN      
       
 Begin Try     
  
   select * from View_Users where UserName =@UserName  
  
 End Try       
       
 BEGIN CATCH       
       
     return  '0';       
       
 END CATCH       
       
END
GO

Stored Procedure to search user by Username or Userid

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[SEARCH_USER]
(
    @USER_FIRST_NAME VARCHAR(100),
    @USER_ID_PK         BIGINT
)
AS
BEGIN
    IF LEN(@USER_ID_PK) > 0
    BEGIN
        SELECT * FROM USER_REGISTRATION WHERE USER_ID_PK = @USER_ID_PK AND IS_RECSTATUS = 1;
    END
    ELSE
    BEGIN
        SELECT * FROM USER_REGISTRATION WHERE USER_FIRST_NAME LIKE @USER_FIRST_NAME AND IS_RECSTATUS = 1 ORDER BY USER_FIRST_NAME;
    END

END

Stored Procedure to Insert User Information to Database

--AUTHOR:    MANOJ KUMAR
--PURPOSE:     AUTHONTICATE USER
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[INSERT_USER]
(
    @LOGIN_ID                VARCHAR(100),
    @PASSWORD                VARCHAR(20),
    @GENDER                    VARCHAR(10),
    @DOB                    datetime,
    @FIRST_NAME                VARCHAR(100),
    @LAST_NAME                VARCHAR(100),
    @ALTERNATE_EMAIL        VARCHAR(100),
    @COUNTRY                BIGINT,
    @STATE                    BIGINT,
    @CITY                    BIGINT,
    @PHONE_NO                VARCHAR(100),
    @MOBILE_NO                VARCHAR(100),
    @TOTAL_EXPERIENCE        VARCHAR(50),
    @CURRENT_EMPLOYER        VARCHAR(200),
    @ANNUAL_SALARY            VARCHAR(100),
    @FUNC_AREA                BIGINT,
    @INDUSTRY                BIGINT,
    @KEY_SKILLS                VARCHAR(500),
    @SKILL_EXPERIENCE        VARCHAR(50),
    @SKILL_LEVEL            BIGINT,
    @PREFERED_JOB_LOCATION    BIGINT,
    @EXPECTED_ROLE            VARCHAR(500),
    @EXPECTED_PACKAGE        VARCHAR(100),
    @RESUME_HEADLINE        VARCHAR(200),
    @RESUME                    VARCHAR(5000),
    @GRADUATION                BIGINT,
    @GRAD_SPECIFICATION        VARCHAR(300),
    @GRAD_INSTITUTE            VARCHAR(300),
    @POST_GRADUATION        BIGINT,
    @PGRAD_SPECIFICATION    VARCHAR(300),
    @PGRAD_INSTITUTE        VARCHAR(300),
    @DOCTRATE                BIGINT,
    @DOC_SPECIFICATION        VARCHAR(300),
    @DOC_INSTITUTE            VARCHAR(300),
    @COURSE_ONE                VARCHAR(300),
    @COURSE_TWO                VARCHAR(300),
    @COURSE_THREE            VARCHAR(300),
    @OUTPUT                 VARCHAR(200) OUTPUT
)
AS
BEGIN
        DECLARE        @TranName VARCHAR(10)
        SELECT      @TranName =  'MyTransaction';
        --BEGIN TRANSACTION  @TranName;
INSERT INTO USER_REGISTRATION
        (
            LOGIN_ID,PASSWORD,GENDER,DATE_OF_BIRTH,USER_FIRST_NAME,USER_LAST_NAME,
            ALTERNATE_EMAIL_ID,COUNTRY_ID_FK,STATE_ID_FK,CITY_ID_FK,PHONE_NO,MOBILE_NO,TOTAL_EXPERIENCE,
            CURRENT_EMPLOYER,ANNUAL_SALARY,FUNC_AREA_ID_FK,INDUSTRY_ID_FK,KEY_SKILLS,
            SKILL_EXPERIENCE,SKILL_LEVEL,PREFERED_JOB_LOCATION_ID_FK,
            EXPECTED_ROLE_ID_FK,EXPECTED_PACKAGE,RESUME_HEADLINE,RESUME,GRADUATION_ID_FK,
            GRAD_SPECIFICATION,GRAD_INSTITUTE,POST_GRADUATION_ID_FK,
            PGRAD_SPECIFICATION,PGRAD_INSTITUTE,DOCTRATE_ID_FK,DOC_SPECIFICATION,DOC_INSTITUTE,
            COURSE_ONE,COURSE_TWO,COURSE_THREE,IS_RECSTATUS,REC_DATE
        )

VALUES (
            @LOGIN_ID,@PASSWORD,@GENDER,@DOB,@FIRST_NAME,@LAST_NAME,
            @ALTERNATE_EMAIL,@COUNTRY,@STATE,@CITY,@PHONE_NO,@MOBILE_NO,@TOTAL_EXPERIENCE,
            @CURRENT_EMPLOYER,@ANNUAL_SALARY,@FUNC_AREA,@INDUSTRY,@KEY_SKILLS,
            @SKILL_EXPERIENCE,@SKILL_LEVEL,@PREFERED_JOB_LOCATION,
            @EXPECTED_ROLE,@EXPECTED_PACKAGE,@RESUME_HEADLINE,@RESUME,@GRADUATION,
            @GRAD_SPECIFICATION,@GRAD_INSTITUTE,@POST_GRADUATION,@PGRAD_SPECIFICATION,
            @PGRAD_INSTITUTE,@DOCTRATE,@DOC_SPECIFICATION,@DOC_INSTITUTE,
            @COURSE_ONE,@COURSE_TWO,@COURSE_THREE,1,GETDATE()
        );

        BEGIN
            SELECT @OUTPUT = (SELECT MAX(ISNULL(USER_ID_PK,1)) FROM USER_REGISTRATION WHERE IS_RECSTATUS = 1);
        END
        RETURN @OUTPUT;
IF @@ERROR = 0
    BEGIN       
        COMMIT TRANSACTION @TranName;
    END
ELSE
    BEGIN
        ROLLBACK  TRANSACTION @TranName;
    END
END

Sql Server Stored Procedure to Authenticate User with output paramater

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--AUTHOR:    MANOJ KUMAR
--PURPOSE:     AUTHONTICATE USER

CREATE PROCEDURE [dbo].[AUTHENTICATE_USER_LOGIN]
(
    @PASSWORD VARCHAR(50),
    @LOGIN_ID VARCHAR(200),
    @OUTPUT VARCHAR(200) OUTPUT

)
AS
    DECLARE @COUNT INT
    BEGIN
        /*SET @COUNT =  (SELECT COUNT(*) FROM USER_REGISTRATION WHERE CAST ([PASSWORD] AS BINARY)= CAST(@PASSWORD AS BINARY)
        AND CAST([LOGIN_ID] AS BINARY)=CAST(@LOGIN_ID  AS BINARY)  AND REC_STATUS =1)*/

        SET @COUNT =  (SELECT COUNT(0) FROM USER_REGISTRATION WHERE [PASSWORD] =  @PASSWORD
        AND CAST([LOGIN_ID] AS BINARY)=CAST(@LOGIN_ID  AS BINARY)  AND IS_RECSTATUS =1);

        IF (@COUNT > 0)
        BEGIN
            SET @OUTPUT ='YES'
        END
        ELSE
        BEGIN
            SET @OUTPUT ='NO'
        END
    END
--    RETURN @OUTPUT
GO