HOW TO EXECUTE A STORED PROCEDURE WITHIN C# PROGRAM

Takeshi Okada

Total Post:89

Points:629
Posted by  Takeshi Okada
C# 
C#
 1040  View(s)
Ratings:
Rate this:

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


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?

  1. Pravesh Singh

    Post:412

    Points:2888
    Re: How to execute a stored procedure within C# program

    Hi Takeshi,

    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

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!