Ejecutar procedimientos almacenados desde C#

Un procedimiento almacenado (store procedure) no es más que un conjunto de instrucciones T-SQL que se ejecutan de forma ordenada en el Servidor de bases de datos pudiendo este recibir o devolver parámetros al usuario.

Entre las principales ventajas de utilizar sp  podemos mencionar las siguientes:

Seguridad: Le otorgamos permisos a los usuarios de ejecutar determinado sp y no a los objetos de la Base de datos.

Mejor Rendimiento: Al invocar un sp este se compila en el motor de base de datos y esta versión compilada será utilizada hasta que el servicio SQL se reinicie. Las instrucciones que mandamos en cadena desde nuestra aplicación son ejecutadas cada vez que se manda al motor de base de datos.

Bueno vamos a lo que nos interesa. Al código J

Diremos que tenemos una tabla llamada PERSONA  y esta contiene los siguientes campos:

Idpersona --> Numerico y se  incrementa de uno en uno automáticamente.

nombre -->  String  nvarchar(50)

apellido -->  String  nvarchar(50)

Vamos a crear el sp que inserte un nuevo registro:

CREATE PROCEDURE sp_insert_tabla  @nombre nvarchar(50),

                                 @apellido nvarchar(50),

                                  @msg as nvarchar(100) output

AS

BEGIN

 SET NOCOUNT ON;

 BEGIN TRAN TADD

 BEGIN TRY

  INSERT INTO PERSONA (nombre,apellido) VALUES (@nombre,@ apellido)   

  COMMIT TRAN TADD

 END TRY

 BEGIN CATCH

      SET @msg = 'OCURRIO UN ERRO: ' + ERROR_MESSAGE() + 'EN LA LINEA ' + CONVERT(NVARCHAR(255),ERROR_LINE()) + '.'

      ROLLBACK TRAN TADD

 END CATCH

END

GO

Datos a tener en cuenta en el procedimiento almacenado:

SET NOCOUNT ON --Muestra el recuento de No. de filas afectadas

BEGIN TRAN TADD -- Marca el inicio de la transacción. Incrementa el @@TRANCOUNT en 1

COMMIT TRAN TADD --Marca el final de la transacción correcta si @@TRANCOUNT=1 hace que todos cambios efectuados en los datos desde el inicio de la transacción sean parte permanente de la base de dato.

ROLLBACK TRAN TADD--Revierte una transacción hasta el inicio se puede decir que borra toda las modificaciones de los datos realizada desde el inicio de la transacción.Tambien libera recursos que mantenia la transacción.

Una vez que tenemos el procedimiento almacenado vamos a invocarlo desde nuestra aplicación con C# y creamos una función para esto:

/*Función que ejecuta sp de inserción, aqui utilizamos una cadena de conexion que esta declara en el web.config*/

protected void ejecutar_sp_insert(string wnombre, string wapellido)

{

    string error = "";

    SqlConnection conexion = new SqlConnection();

    conexion.ConnectionString =         ConfigurationManager.ConnectionStrings["TESTINGConnectionString"].ConnectionString;

    SqlCommand cmd = new SqlCommand("sp_insert_tabla", conexion);

    cmd.CommandType = CommandType.StoredProcedure;       

    cmd.Parameters.Add("@nombre",SqlDbType.NVarChar,50).Value = wnombre;

    cmd.Parameters.Add("@apellido",SqlDbType.NVarChar,50).Value = wapellido;

    cmd.Parameters.Add("@msg", SqlDbType.NVarChar,100);

    cmd.Parameters["@msg"].Direction = ParameterDirection.Output;

    try

    {

         conexion.Open();

         cmd.ExecuteNonQuery();

         conexion.Close();

    }

    catch(System.Exception  ex)

    {

         error = "Error: " + ex.Message;

    }

}

En esta función lo mas relevante es los siguiente:

Al SqlCommand le pasamos el nombre del sp sp_insert_tabla mas la conexión.

Tambien agregamos los parametros de entrada del sp en este caso es nombre y apellido. Se le espcifica que el parametro msg es una variable de salida. Abrimos la conexión, ejecutamos, cerramos la conexión todo esto en try/catch para manipular un eventual error.

Y listo asi de simple es ejecutar un sp desde c#.