How to call a procedure in ASP.NET C #?
Here's my simple procedure:
CREATE PROCEDURE GetAdmin
(
@email VARCHAR(50),
@password VARCHAR (50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Admin WHERE Email = @email AND Password = @password
END
GO
First you create a Command in C #. The following example is for SQL Server DBMS:
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("GetAdmin");
cmd.Parameters.AddWithValue("@email", "[email protected]");
cmd.Parameters.AddWithValue("@password", "abcd1234");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
Then you use the Connection property of the Command and pass an object of type SQLConnection, previously configured and representing your connection to the Database.
System.Data.IDbCommand cmd = conn.CreateCommand("sp_AutalizaItemKDS");
Then just run Command:
cmd.ExecuteReader();
I've just created a library just to call stored procedures in the simplest way possible.
You can install it through NuGet.
Install-Package Neat.Procedure
ProcedureExecuter.ExecuteReader<SuaClasse>("GetAdmin");
Just have a class where the properties have the same name as the columns returned, and the mapping is done.
Complementing the above information. To execute a stored procedure you can do the following code:
using (var conn = new SqlConnection(connectionString))
{
try
{
SqlCommand command = new SqlCommand("[dbo].[nome_da_procedure]", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@PROC_PARAMETRO", SqlDbType.Int)).Value = 100;
command.Parameters.Add(new SqlParameter("@PROC_PARAMETRO1", SqlDbType.VarChar)).Value = 'valor';
conn.Open();
command.ExecuteNonQuery();
}
}
Some details regarding the procedure execution are: You can assign the result of a procedure to a variable:
var returnValue = command.ExecuteReader(); //Retorna a linha que foi executada
Taking the returned values:
string variavel = "";
while (returnValue.Read())
{
variavel = returnValue["COLUNA_TABELA"].ToString();
}
If you are using the Entity Framework, I recommend taking a look at these links here.
EF with Entity Framework | StackOverflow - EF with SP
Already with EF6:
this.Database.SqlQuery("storedProcedureName",params);