How to execute a stored procedure from c# program

Question

Hi

i want to execute this StoredProcedure from c# program How can i do this using c#

Any Help is Greatly Apreciated

I have written the following stored procedure in 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();
        }

     }
  }

Then it display the exception can not find the stored procedure dbo.test .can i need to give the path?? if yes in which location the stored [procedures wil stored.

Help me in this regard

Answer 1

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

Answer 2

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 3

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"]);
      }

Here are some interisting 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 4

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