What do you call a "Stored Procedure"?

Question

How to call a procedure in ASP.NET C#?

Follow 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

Answer 1

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 Command Connection property and pass an object of type Sqlconnection, previously configured and that represents your connection to the Database.

System.Data.IDbCommand cmd = conn.CreateCommand("sp_AutalizaItemKDS");

Then just run Command:

cmd.ExecuteReader();

Answer 2

By chance I created a library just to call stored procedures in the simplest way possible.

https://github.com/achvaicer/Neat.Procedure

It is possible to 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 returned columns, and the mapping is done.

Answer 3

Supplementing the above information. To run a stored trial you can do according to the code below:

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 execution of the precedent are: You can assign the result of a precedent 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();
}

Answer 4

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 the EF6:

this.Database.SqlQuery("storedProcedureName",params);