How to execute a stored procedure within C# program

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program.

I have written the following stored procedure in a SqlServer query window and saved it as stored1:

use master   go  create procedure dbo.test as    DECLARE @command as varchar(1000), @i int  SET @i = 0  WHILE @i < 5  BEGIN  Print 'I VALUE ' +CONVERT(varchar(20),@i)  EXEC(@command)  SET @i = @i + 1  END  

EDITED:

using System;  using System.Collections.Generic;  using System.Text;  using System.Data;  using System.Data.SqlClient;  namespace AutomationApp  {      class Program      {          public void RunStoredProc()          {              SqlConnection conn = null;              SqlDataReader rdr  = null;                Console.WriteLine("\nTop 10 Most Expensive Products:\n");                try              {                  conn = new SqlConnection("Server=(local);DataBase=master;Integrated Security=SSPI");                  conn.Open();                  SqlCommand cmd = new SqlCommand("dbo.test", conn);                  cmd.CommandType = CommandType.StoredProcedure;                  rdr = cmd.ExecuteReader();                  /*while (rdr.Read())                  {                      Console.WriteLine(                          "Product: {0,-25} Price: ${1,6:####.00}",                          rdr["TenMostExpensiveProducts"],                          rdr["UnitPrice"]);                  }*/              }              finally              {                  if (conn != null)                  {                      conn.Close();                  }                  if (rdr != null)                  {                      rdr.Close();                  }              }          }          static void Main(string[] args)          {              Console.WriteLine("Hello World");              Program p= new Program();              p.RunStoredProc();                    Console.Read();          }      }  }  

This displays the exception Cannot find the stored procedure dbo.test. Do I need to provide the path? If yes, in which location should the stored procedures be stored?

Answer by Mehrdad Afshari for How to execute a stored procedure within C# program

using (var conn = new SqlConnection(connectionString))  using (var command = new SqlCommand("ProcedureName", conn) {                              CommandType = CommandType.StoredProcedure }) {     conn.Open();     command.ExecuteNonQuery();  }  

Answer by BlackTigerX for How to execute a stored procedure within C# program

using (SqlConnection sqlConnection1 = new SqlConnection("Your Connection String")) {  using (SqlCommand cmd = new SqlCommand()) {    Int32 rowsAffected;      cmd.CommandText = "StoredProcedureName";    cmd.CommandType = CommandType.StoredProcedure;    cmd.Connection = sqlConnection1;      sqlConnection1.Open();      rowsAffected = cmd.ExecuteNonQuery();    }}  

Answer by Bhaskar for How to execute a stored procedure within C# program

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

Answer by Dewfy for How to execute a stored procedure within C# program

You mean that your code is DDL? If so, MSSQL has no difference. Above examples well shows how to invoke this. Just ensure

CommandType = CommandType.Text  

Answer by abc for How to execute a stored procedure within C# program

SqlConnection conn = null;  SqlDataReader rdr  = null;  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  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"]);  }  

Answer by Vaibhav Bhootna for How to execute a stored procedure within C# program

Calling Store Procedure in C#

SqlCommand cmd = new SqlCommand("StoreProcedureName",con);  cmd.CommandType=CommandType.StoredProcedure;  cmd.Parameters.AddWithValue("@value",txtValue.Text);  int rowAffected=cmd.ExecuteNonQuery();  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72