How to Create a Function That Accept Stored Procedure Name and Parameter and Return Data Table in ASP.Net Using C#

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

 

1. If the Stored Procedure needs parameters:
   

   string[,] aryPara = new string[,]
   {
         { "@Username", login .UserName } ,
         { "@Password",login .Password }
   };
   // List Of parameter the stored procedure needed.
   DataTable dt = new DataTable();// Date Table That Return required result.
   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

2. If the Stored Procedure needs no parameter:
   

   DataTable dt = new DataTable();
   
   // Date Table That Return required result.
   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.