In Focus

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

This article provides a code snippet of a generalized function in C# that can accept any Stored Procedure name and parameters and returns the data table.

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

  1. void selectData(string StoredProcedureName, out DataTable dtemp, [Optional] string[,] aryParameters)  
  2. {  
  3.       using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["*******"].ConnectionString ))  
  4.       {  
  5.             using (SqlCommand cmd = new SqlCommand())  
  6.             {  
  7.                   con.Open();  
  8.                   cmd.CommandType = CommandType.StoredProcedure;  
  9.                   SqlDataAdapter adp = new SqlDataAdapter();  
  10.                   DataTable dt = new DataTable();  
  11.                   cmd.CommandText = StoredProcedureName;  
  12.                   cmd.CommandTimeout = 300;  
  13.                   try  
  14.                   {  
  15.                         for (int i = 0; i < aryParameters.GetLength(0); i++)  
  16.                         {   

  17.                               cmd.Parameters.Add(new SqlParameter(aryParameters[i, 0], aryParameters[i, 1]));  
  18.                         }  
  19.                   }  
  20.                   catch (Exception ex)  
  21.                   {  
  22.   
  23.                   }  

  24.                   cmd.Connection = con;  
  25.                   adp.SelectCommand = cmd;  
  26.                   adp.Fill(dt);  
  27.                   con.Close();  
  28.                   dtemp = dt;  
  29.             }  
  30.       }  
  31. }  
Function Call

 

  1. If the Stored Procedure needs parameters:
    1. string[,] aryPara = new string[,]   
    2. {   
    3.       { "@Username", login .UserName } ,   
    4.       { "@Password",login .Password }   
    5. };
    6. // List Of parameter the stored procedure needed.   
    7. DataTable dt = new DataTable();// Date Table That Return required result.  
    8. selectData("spLogin",out dt, aryPara);  
    Stored Procedure
    1. SET ANSI_NULLS ON  
    2. GO  
    3. SET QUOTED_IDENTIFIER ON  
    4. GO  
    5. ALTER PROCEDURE [dbo].[spLogin]   
    6. @Username varchar(15),  
    7. @Password nvarchar(50)  
    8. as  
    9. begin  
    10. DECLARE @loginId INT  
    11. 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')  

    12. BEGIN  
    13.   
    14. select @loginId= Userid from tblusers where(Username = @Username and Password =@Password COLLATE SQL_Latin1_General_CP1_CS_AS ) and ustatus = 'Active'  
    15. update tblusers set Lastlogin =GETDATE()where Userid = @loginId  
    16. 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  
    17. left outer join tblbranches on tblbranches.Branchname =tblusers .Branchcode   
    18.   
    19. where (Username = @Username and Password =@Password COLLATE SQL_Latin1_General_CP1_CS_AS ) and ustatus = 'Active'  
    20.   
    21. end  
    22. end  
  2. If the Stored Procedure needs no parameter:
    1. DataTable dt = new DataTable();

    2. // Date Table That Return required result.  
    3. 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.