Cómo ejecutar un procedimiento almacenado dentro del progtwig C #

Quiero ejecutar este procedimiento almacenado desde un progtwig C #.

He escrito el siguiente procedimiento almacenado en una ventana de consulta SqlServer y lo guardé como 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 

EDITADO:

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

Esto muestra la excepción Cannot find the stored procedure dbo.test . ¿Debo proporcionar el camino? En caso afirmativo, ¿en qué ubicación deben almacenarse los procedimientos almacenados?

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

Aquí hay algunos enlaces interesantes que podría leer:

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

Llamar al procedimiento de almacenamiento en C #

 SqlCommand cmd = new SqlCommand("StoreProcedureName",con); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@value",txtValue.Text); int rowAffected=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"]); } 

Al usar 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 

Este es un código para ejecutar procedimientos almacenados con y sin parámetros a través de la reflexión. Tenga en cuenta que los nombres de propiedades de los objetos deben coincidir con los parámetros del procedimiento almacenado.

 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 GenerateSQLParameters(object model) { var paramList = new List(); 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; } 

este es un ejemplo de un procedimiento almacenado que devuelve un valor y su ejecución en 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); 

¿Quiere decir que su código es DDL? Si es así, MSSQL no tiene diferencia. Los ejemplos anteriores también muestran cómo invocar esto. Solo asegúrate

 CommandType = CommandType.Text 

No Dapper respuesta aquí. Así que agregué uno

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

Por favor, echa un vistazo a Crane (soy el autor)

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

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

También tiene muchas otras características que te pueden gustar.