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

我想从一个c#程序执行这个存储过程。

我在SqlServer查询窗口中编写了以下存储过程，并将其保存为 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();
        }
    }
}

这将显示异常无法找到存储过程dbo.test。我需要提供路径吗?如果是，存储过程应该存储在哪个位置?

当前回答

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

2011-06-29 10:46:51

其他回答

用c#调用存储过程:

SqlCommand cmd = new SqlCommand("StoredProcedureName",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@value",txtValue.Text);

con.Open();
int rowAffected = cmd.ExecuteNonQuery();
con.Close();

2013-12-24 19:48:14

这里没有衣冠楚楚的答案。所以我加了一个

using Dapper;
using System.Data.SqlClient;

using (var cn = new SqlConnection(@"Server=(local);DataBase=master;Integrated Security=SSPI"))
    cn.Execute("dbo.test", commandType: CommandType.StoredProcedure);

2016-07-01 16:00:36

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

2009-08-11 15:06:31

使用短小精悍的。所以我加了这个，希望大家有所帮助。

public void Insert(ProductName obj)
        {
            SqlConnection connection = new SqlConnection(Connection.GetConnectionString());
            connection.Open();
            connection.Execute("ProductName_sp", new
            { @Name = obj.Name, @Code = obj.Code, @CategoryId = obj.CategoryId, @CompanyId = obj.CompanyId, @ReorderLebel = obj.ReorderLebel, @logo = obj.logo,@Status=obj.Status, @ProductPrice = obj.ProductPrice,
                @SellingPrice = obj.SellingPrice, @VatPercent = obj.VatPercent, @Description=obj.Description, @ColourId = obj.ColourId, @SizeId = obj.SizeId,
                @BrandId = obj.BrandId, @DisCountPercent = obj.DisCountPercent, @CreateById =obj.CreateById, @StatementType = "Create" }, commandType: CommandType.StoredProcedure);
            connection.Close();
        }

2019-10-16 11:29:18

最简单和直接的…

SqlCommand cmd = new SqlCommand("StoredProcedureName",con); // Just like you declared it
cmd.CommandType = CommandType.StoredProcedure; // an attribute related to the object
cmd.Parameters.AddWithValue("@value",txtValue.Text); // Parameter name and text source 

con.Open();
int rowAffected = cmd.ExecuteNonQuery();
con.Close();

2021-05-30 09:27:27

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

推荐文章

最新文章

标签