How to execute a stored procedure within C# program
Solution 1:
using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("ProcedureName", conn) {
CommandType = CommandType.StoredProcedure }) {
conn.Open();
command.ExecuteNonQuery();
}
Solution 2:
using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI")) {
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("CustOrderHist", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));
// execute the command
using (SqlDataReader rdr = cmd.ExecuteReader()) {
// iterate through results, printing each to console
while (rdr.Read())
{
Console.WriteLine("Product: {0,-35} Total: {1,2}",rdr["ProductName"],rdr["Total"]);
}
}
}
Here are some interesting links you could read:
- http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
- http://www.c-sharpcorner.com/UploadFile/dclark/InsOutsinCS11302005072332AM/InsOutsinCS.aspx
- http://www.codeproject.com/KB/cs/simplecodeasp.aspx
- http://msdn.microsoft.com/en-us/library/ms171921(VS.80).aspx