반응형
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();
}
}
}
예외가 표시됩니다 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"]);
}
}
}
읽을 수있는 흥미로운 링크는 다음과 같습니다.
- http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
- http://www.c-sharpcorner.com/UploadFile/dclark/InsOutsinCS11302005072332AM/InsOutsinCS.aspx
- http://www.codeproject.com/KB/cs/simplecodeasp.aspx
- http://msdn.microsoft.com/en-us/library/ms171921(VS.80).aspx
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();
}}
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();
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
이것은 값을 반환하는 저장 프로 시저의 예이며 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);
크레인을 확인하십시오 (저는 저자입니다)
https://www.nuget.org/packages/Crane/
SqlServerAccess sqlAccess = new SqlServerAccess("your connection string");
var result = sqlAccess.Command().ExecuteNonQuery("StoreProcedureName");
또한 당신이 좋아할만한 많은 다른 기능들이 있습니다.
코드가 DDL이라는 것을 의미합니까? 그렇다면 MSSQL에는 차이가 없습니다. 위의 예제는 이것을 호출하는 방법을 잘 보여줍니다. 그냥 확인
CommandType = CommandType.Text
여기에는 Dapper가 없습니다 . 그래서 나는 하나를 추가
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);
참고 URL : https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program
반응형
'Programing' 카테고리의 다른 글
| C ++ 애플리케이션에 리플렉션을 추가하려면 어떻게해야합니까? (0) | 2020.04.11 |
|---|---|
| 중심에 초점을 맞춘 난수 얻기 (0) | 2020.04.11 |
| RecyclerView로 빈보기를 표시하는 방법은 무엇입니까? (0) | 2020.04.11 |
| SQL Server에서 외래 키를 어떻게 만듭니 까? (0) | 2020.04.11 |
| 안드로이드 : ViewPager WRAP_CONTENT를 가질 수 없습니다 (0) | 2020.04.11 |