이 저장 프로 시저를 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();
}
}
}예외가 표시됩니다 Cannot find the stored procedure dbo.test. 경로를 제공해야합니까? 그렇다면 저장 프로 시저를 어느 위치에 저장해야합니까?
답변
using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("ProcedureName", conn) {
CommandType = CommandType.StoredProcedure }) {
conn.Open();
command.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"]);
}
}
}읽을 수있는 흥미로운 링크는 다음과 같습니다.
답변
C #에서 호출 저장 프로 시저
SqlCommand cmd = new SqlCommand("StoreProcedureName",con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@value",txtValue.Text);
con.Open();
int rowAffected=cmd.ExecuteNonQuery();
con.Close();답변
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();
}}답변
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"]);
}답변
리플렉션을 통해 매개 변수를 사용하거나 사용하지 않고 저장 프로 시저를 실행하기위한 코드입니다. 오브젝트 특성 이름은 스토어드 프로 시저의 매개 변수와 일치해야합니다.
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;
}답변
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