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

Question

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();

Have you ever used ADO.NET in your Global.getdatatablefromquery method? If so, just change your CommandType to StoredProcedure. — Soner Gönül
– Soner Gönül, Commented Jun 22, 2015 at 5:49

Community · Accepted Answer · 2017-05-23 12:06:01Z

2

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.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jun 22, 2015 at 6:00

Rohit Vipin Mathews

11.8k15 gold badges60 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related