CODEWITHSUNDEEP
c#sql-serverstored-procedures
Ishaan shringi
Ishaan shringi

Reputation: 47

Using SQL queries in C#. Trouble with defining stored procedure

I'm sharing a snippet from my code below: 

string str = "select * from contacts";
DataSet dt = Global.getdatatablefromquery(str);
ExcelGrid.DataSource = dt;
ExcelGrid.DataBind();

I am changing all my queries to stored procedures, but I don't know exactly how will I define my stored procedure in this code? I want something like:

string str = "storedprocedurename";
DataSet dt = Global.getdatatablefromquery(str);
ExcelGrid.DataSource = dt;
ExcelGrid.DataBind();

Upvotes: 0

Views: 71

Answers (1)

Rohit Vipin Mathews
Rohit Vipin Mathews

Reputation: 11787

You have 2 choices (as far as I know):

1. Execute it as Text by specifying EXEC explicitly.

Eg: 

cmd = new SqlCommand("EXEC storedprocedurename(@p1, @p2)");
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

2. You can use CommandType as StoredProcedure

Eg: 

cmd = new SqlCommand("storedprocedurename");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

The difference between the 2 approaches is how message pumping happens.(source)

Using the second approach in which the CommandType is explicitly specified as StoredProcedure is more clear and cleaner.

Upvotes: 2

Related Questions