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. {

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

  26. cmd.Connection = con;
  27. adp.SelectCommand = cmd;
  28. adp.Fill(dt);
  29. con.Close();
  30. dtemp = dt;
  31. }
  32. }
  33. }
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')

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

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