如何在C#程序中执行存储过程

Question

如何在C#程序中执行存储过程

我想从C#程序中执行这个存储过程。

我在SQLSERVER查询窗口中编写了以下存储过程，并将其保存为Store 1：

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

编辑：

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();
        }
    }}

这将显示异常。Cannot find the stored procedure dbo.test..我需要提供这条路吗？如果是，应将存储过程存储在哪个位置？

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 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();}}