How to execute a stored procedure within C# program

Question

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 1

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

Answer 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