how to run a stored procedure from C# application

Question

how to run a stored procedure from C# application

sankar18

SSC Veteran

Points: 242
More actions
February 13, 2008 at 3:33 am

#91649

how to run a stored procedure from C# application

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Programmer07 SSC Enthusiast Points: 179 More actions · Answer 1

Try this. Just paste the code into a button's click event. You'll need to change the items shown bold to match your database settings.

string oConnString = "Data Source=localhost;Initial Catalog=ALocalDataBase;Integrated Security=True";

SqlConnection oConn = new SqlConnection(oConnString);

SqlCommand oCmd = new SqlCommand("Exec sp_AnSProc", oConn);

SqlDataAdapter oAdptr = new SqlDataAdapter(oCmd);

DataSet oDS = new DataSet();

oConn.Open();

oCmd.ExecuteNonQuery();

oCmd.CommandTimeout = 600;

oAdptr.Fill(oDS);

//uncomment the following line to view the xml representation of the data returned

//System.Diagnostics.Debug.WriteLine(oDS.GetXml());

oConn.Close();

Note: If your stored procedure requires that parameters be passed in, just use String Concatenation.

Good luck. I hope this helps.

Software Developer
I teach; therefore, I learn.

dmbaker SSCertifiable Points: 5139 More actions · Answer 2

If all you want to do is execute a stored proc that returns no results then you don't need a DataAdapter/DataSet (you don't necessarily need one even if you do want a resultset -- it depends on what you want to do with the results). You also don't need the "Exec" in your command text is you set the CommandType property of your SqlCommand to "StoredProcedure".

[font="Courier New"]string connectionString = "(your connection string here)";

string commandText = "usp_YourStoredProc";

using (SqlConnection conn = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand(commandText, conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandTimeout = 600;

conn.Open();

int affectedRows = cmd.ExecuteNonQuery();

conn.Close();

}[/font]

If you want to pass parameters, it'd also be better to define SqlParameter objects for the parameters, set their values, then add those to the command object, rather than concatenating them into the command text.

shr_khr SSC Enthusiast Points: 110 More actions · Answer 3

You can check this article HOW TO: SQL & C# to learn how to call a stored procedure in a MS SQL database from a C# application as well as a Java application.

You will learn how to pass parameters to the stored procedures as well as read Out parameters returned by the sp.

Also, you will learn to how pass parameterized SQL queries from the application and read data returned by the queries.

Shahriar Nour Khondokar[/url]