Introduction
This code snippet explains a generalized function in C# that can accept any Stored Procedure name and parameters and it returns the data table.
If no parameter is needed then just pass "null" in the string array. Pass all the parameters in string format, no matter what parameter(s) the Stored Procedure needs. Just pass the correct format in the string, it will work.
Function Code
- void selectData(string StoredProcedureName, out DataTable dtemp, [Optional] string[,] aryParameters)
- {
- using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["*******"].ConnectionString ))
- {
- using (SqlCommand cmd = new SqlCommand())
- {
- con.Open();
- cmd.CommandType = CommandType.StoredProcedure;
- SqlDataAdapter adp = new SqlDataAdapter();
- DataTable dt = new DataTable();
- cmd.CommandText = StoredProcedureName;
- cmd.CommandTimeout = 300;
- try
- {
- for (int i = 0; i < aryParameters.GetLength(0); i++)
- {
- cmd.Parameters.Add(new SqlParameter(aryParameters[i, 0], aryParameters[i, 1]));
- }
- }
- catch (Exception ex)
- {
-
- }
- cmd.Connection = con;
- adp.SelectCommand = cmd;
- adp.Fill(dt);
- con.Close();
- dtemp = dt;
- }
- }
- }
Function Call
- If the Stored Procedure needs parameters:
- string[,] aryPara = new string[,]
- {
- { "@Username", login .UserName } ,
- { "@Password",login .Password }
- };
-
- DataTable dt = new DataTable();
- selectData("spLogin",out dt, aryPara);
Stored Procedure
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [dbo].[spLogin]
- @Username varchar(15),
- @Password nvarchar(50)
- as
- begin
- DECLARE @loginId INT
- IF EXISTS ( select Userid ,Username ,Branchcode,tblroles .Rolename as 'uRole' from tblusers left outer join tblroles on tblroles.Roleid = tblusers .Roleid where (Username = @Username and Password =@Password COLLATE SQL_Latin1_General_CP1_CS_AS ) and ustatus = 'Active')
- BEGIN
-
- select @loginId= Userid from tblusers where(Username = @Username and Password =@Password COLLATE SQL_Latin1_General_CP1_CS_AS ) and ustatus = 'Active'
- update tblusers set Lastlogin =GETDATE()where Userid = @loginId
- select Userid ,Username ,Branchid as 'Branchcode' ,tblroles.Rolename as 'uRole',tblroles .Roleid,Branchcode as 'BranchName' from tblusers left outer join tblroles on tblroles.Roleid = tblusers .Roleid
- left outer join tblbranches on tblbranches.Branchname =tblusers .Branchcode
-
- where (Username = @Username and Password =@Password COLLATE SQL_Latin1_General_CP1_CS_AS ) and ustatus = 'Active'
-
- end
- end
- If the Stored Procedure needs no parameter:
- DataTable dt = new DataTable();
-
-
- selectData("spLogin",out dt, null);
Summary
In this illustration we learned about some ADO.NET code that accepts a Stored Procedure name as parameter and returns the required data table. In the code above just change the connection string name, Stored Procedure name and parameters name; it will then work for nearly all Stored Procedures. Please provide your valuable comments about this article.