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

小开

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

小开

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


}}

小开

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

这里有一些有趣的链接，你可以读一下:

小开

你的意思是你的代码是DDL? 如果是这样，MSSQL没有区别。上面的例子很好地展示了如何调用它。只是确保< / p >

CommandType = CommandType.Text

小开

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

小开

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

小开

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

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

小开

通过Ado.net

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace PBDataAccess
{
public class AddContact
{
// for preparing connection to sql server database


private SqlConnection conn;


// for preparing sql statement or stored procedure that
// we want to execute on database server


private SqlCommand cmd;


// used for storing the result in datatable, basically
// dataset is collection of datatable


private DataSet ds;


// datatable just for storing single table


private DataTable dt;


// data adapter we use it to manage the flow of data
// from sql server to dataset and after fill the data
// inside dataset using fill() method


private SqlDataAdapter da;




// created a method, which will return the dataset


public DataSet GetAllContactType()
{






// retrieving the connection string from web.config, which will
// tell where our database is located and on which database we want
// to perform opearation, in this case we are working on stored
// procedure so you might have created it somewhere in your database.
// connection string will include the name of the datasource, your
// database name, user name and password.


using (conn = new SqlConnection(ConfigurationManager.ConnectionString["conn"]
.ConnectionString))


{
// Addcontact is the name of the stored procedure
using (cmd = new SqlCommand("Addcontact", conn))


{
cmd.CommandType = CommandType.StoredProcedure;


// here we are passing the parameters that
// Addcontact stored procedure expect.
cmd.Parameters.Add("@CommandType",
SqlDbType.VarChar, 50).Value = "GetAllContactType";


// here created the instance of SqlDataAdapter
// class and passed cmd object in it
da = new SqlDataAdapter(cmd);


// created the dataset object
ds = new DataSet();


// fill the dataset and your result will be
stored in dataset
da.Fill(ds);
}
}
return ds;
}
}


****** Stored Procedure ******


CREATE PROCEDURE Addcontact
@CommandType VARCHAR(MAX) = NULL
AS
BEGIN
IF (@CommandType = 'GetAllContactType')
BEGIN
SELECT * FROM Contacts
END
END

小开

请查阅Crane(我是作者)

https://www.nuget.org/packages/Crane/

SqlServerAccess sqlAccess = new SqlServerAccess("your connection string");
var result = sqlAccess.Command().ExecuteNonQuery("StoredProcedureName");

还有一堆你可能会喜欢的其他功能。

小开

这是一个存储过程的示例，它返回一个值并在c#中执行

CREATE PROCEDURE [dbo].[InsertPerson]
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),@LastName nvarchar(50),
@PersonID int output
AS
BEGIN
insert [dbo].[Person](LastName,FirstName) Values(@LastName,@FirstName)


set @PersonID=SCOPE_IDENTITY()
END
Go




--------------
// Using stored procedure in adapter to insert new rows and update the identity value.
static void InsertPersonInAdapter(String connectionString, String firstName, String lastName) {
String commandText = "dbo.InsertPerson";
using (SqlConnection conn = new SqlConnection(connectionString)) {
SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);


mySchool.InsertCommand = new SqlCommand(commandText, conn);
mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;


mySchool.InsertCommand.Parameters.Add(
new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));
mySchool.InsertCommand.Parameters.Add(
new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));


SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));
personId.Direction = ParameterDirection.Output;


DataTable persons = new DataTable();
mySchool.Fill(persons);


DataRow newPerson = persons.NewRow();
newPerson["FirstName"] = firstName;
newPerson["LastName"] = lastName;
persons.Rows.Add(newPerson);


mySchool.Update(persons);
Console.WriteLine("Show all persons:");
ShowDataTable(persons, 14);

小开

此代码用于通过反射执行带参数和不带参数的存储过程。请注意，对象的属性名需要与存储过程的参数匹配

private static string ConnString = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
private SqlConnection Conn = new SqlConnection(ConnString);


public void ExecuteStoredProcedure(string procedureName)
{
SqlConnection sqlConnObj = new SqlConnection(ConnString);


SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConnObj);
sqlCmd.CommandType = CommandType.StoredProcedure;


sqlConnObj.Open();
sqlCmd.ExecuteNonQuery();
sqlConnObj.Close();
}


public void ExecuteStoredProcedure(string procedureName, object model)
{
var parameters = GenerateSQLParameters(model);
SqlConnection sqlConnObj = new SqlConnection(ConnString);


SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConnObj);
sqlCmd.CommandType = CommandType.StoredProcedure;


foreach (var param in parameters)
{
sqlCmd.Parameters.Add(param);
}


sqlConnObj.Open();
sqlCmd.ExecuteNonQuery();
sqlConnObj.Close();
}


private List<SqlParameter> GenerateSQLParameters(object model)
{
var paramList = new List<SqlParameter>();
Type modelType = model.GetType();
var properties = modelType.GetProperties();
foreach (var property in properties)
{
if (property.GetValue(model) == null)
{
paramList.Add(new SqlParameter(property.Name, DBNull.Value));
}
else
{
paramList.Add(new SqlParameter(property.Name, property.GetValue(model)));
}
}
return paramList;


}

小开

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

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

小开

在我的例子中，我想在dataGridView中显示过程的结果:

using (var command = new SqlCommand("ProcedureNameHere", connection) {
// Set command type and add Parameters
CommandType = CommandType.StoredProcedure,
Parameters = { new SqlParameter("@parameterName",parameterValue) }
})
{
// Execute command in Adapter and store to dataset
var adapter = new SqlDataAdapter(command);
var dataset = new DataSet();
adapter.Fill(dataset);
// Display results in DatagridView
dataGridView1.DataSource = dataset.Tables[0];
}

小开

最简单和直接的…

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