[SQL] Procedimiento almacenado para Insertar datos con transacción y control de   errores. 220120092 Votes

Hola amigos, en muchas ocasiones no sabemos si estamos realizando correctamente un proceso de inserción, actualización o eliminación de datos a una tabla(s), esto para muchos es sumamente sencillo, pero me di a la tarea de preparar un pequeño ejemplo de esto.  El script de T-Sql consiste en realizar un procedimiento almacenado que reciba los datos necesarios para insertarlos en la tabla, para garantizar la ejecución correcta de las inserciones utilizo las transacciones “Transact SQL” y para validar la reversión de la transacción en caso de que ocurra unERROR utilizo el control de Errores Try – Catch con RollBack.

¿Qué es una Transacción?

Una transacción es un conjunto de operaciones T-SQL que funcionan como un solo bloque de instrucciones, esto significa que si ocurre un fallo durante la ejecución de alguna instrucción del bloque todas las demás fallaran, lo que implica que nada más se ejecutara y la transacción tendrá que deshacer todo lo que había ejecutado hasta donde ocurrió el fallo, a eso se la llama reversión de la transacción y se ejecuta con unROLLBACK, en caso de una ejecución correcta se tendrá que grabar la transacción conCOMMIT, el objetivo de las transacciones es garantizar que los datos que se inserten, actualicen o eliminen queden CONSISTENTES en la base de datos.

Después de una ligera explicación de las Transacciones veremos el siguiente ejemplo de una inserción dentro de un procedimiento almacenado con transacción.

Definiendo las estructuras.

1. La estructura del SP es:

CREATE PROCEDURE nombreProdedimiento

-- Parámetros del SP

@Param1 AS Varchar(25),

@Param2 AS Bigint

.

.

.

AS

BEGIN

-- Insertar bloque de instrucciones a ejecutar.

/*

SELECT

UPDATE

INSERT

DELETE

Lo que quieras....

*/

END

GO

 

2. La estructura del control de errores TRY-CATCH es:

Begin Try

/*Bloque de instrucciones a validar.

-----------------------------------------

-----------------------------------------

-----------------------------------------*/

End Try

Begin Catch

/*Bloque de instrucciones que se ejecutan si ocurre

un ERROR.

-----------------------------------------

-----------------------------------------

-----------------------------------------*/

End Catch

3. La estructura de una Transacción es:

Begin Tran NombreTransaccion--Inicio de la transacción con su nombre Tadd o el

que elijas.

/*Bloque de instrucciones a ejecutar en la Transacción

---------------------------------------

---------------------------------------*/

Commit Tran NombreTransaccion--Confirmación de la transacción.

Rollback Tran NombreTransaccion--Reversión de la transacción.

 

4. Para finalizar veremos todo lo anterior armado ya en conjunto de SP, Transacción y control de Errores.

-- =============================================

-- Author: Ivan Rangel Cuadros.

-- Create date: 22/Ene/2009

-- Description: Procedimiento para Insertar registros en una Tabla con

transacción y control de errores.

-- =============================================

CREATE PROCEDURE spAgregaUsuario

@nom AS VARCHAR(50),

@ape AS VARCHAR(50),

@ema AS VARCHAR(30),

@pas AS VARCHAR(20),

@idJer AS BIGINT,

@msg AS VARCHAR(100) OUTPUT

AS

BEGIN

SET NOCOUNT ON;

Begin Tran Tadd

Begin Try

INSERT INTO dbo.USUARIO_SYS (nombre, apellidos, email, pass, fecha_add)

VALUES (@nom, @ape, @ema, @pas, GETDATE())

INSERT INTO dbo.USUARIO_JERARQUIAS_SYS (id_usuario, id_jerarquia) VALUES

(@@IDENTITY, @idJer)

SET @msg = 'El Usuario se registro correctamente.'

COMMIT TRAN Tadd

End try

Begin Catch

SET @msg = 'Ocurrio un Error: ' + ERROR_MESSAGE() + ' en la línea ' +

CONVERT(NVARCHAR(255), ERROR_LINE() ) + '.'

Rollback TRAN Tadd

End Catch

END

GO

 

Explicando el procedimiento:

1. Recibe los datos a través de parámetros de los cuales uno es de salida OutPut , que servirá para indicar si la Inserción se realizo correctamente o no.

2. Crea una transacción llamada Tadd, valida con TRY-CATCH las inserciones a las tablas USUARIO y USUARIO_JERARQUIAS.

3. Si las operaciones de inserción se ejecutan correctamente las confirma con COMMIT TRAN Tadd y regresa la variable @msg con un valor de confirmación correcto, si ocurre un error se revierte la transacción con ROLLBACK TRAN Tadd y devuelve en@msg un valor de error.

Ejecutando el Procedimiento:

DECLARE @msg AS VARCHAR(100);

EXEC spAgregaUsuario 'Claudia','Perez Torres','clau@mail.com','a220109',1,@msg

OUTPUT

SELECT @msg AS msg

[SQL]Procedimientos almacenados paso a   paso 31102008

Un procedimiento almacenado (store procedure) no es más que una secuencia ordenada de instrucciones T-SQL, que pueden recibir y proporcionar parámetros provistos por el usuario y se pueden guardar en el servidor con un nombre, para posteriormente se invocados y ejecutados, por lo regular desde una aplicación (Escritorio o Web). Desde la versión 2005, se incorpora la posibilidad de utilizar procedimientos almacenados usando el CLR de .NET. Es decir tenemos dos tipos de procedimientos almacenados.

Un procedimiento almacendado CLR es una referencia a un método de un ensamble (dll) de .NET Framework que puede aceptar y devolver parámetros suministrados por el usuario.

Ventajas de usar SP

Compilación: La primera vez que se invoca un SP, el motor lo compila y a partir de ahí, se sigue usando la versión compilada del mismo, hasta que se modifique o se reinicie el servicio de

SQL. Esto siginifica que se tendrá un mejor rendimiento que las consultas directas que usan cadenas con las instrucciones T-SQL, que se compilan cada vez que se invocan.

Automatización: si tenemos un conjunto de instrucciones T-SQL, las cuales queremos ejecutar de manera ordenada, un SP es la mejor manera de hacerlo.

Administración: cuando realizamos aplicaciones con un gran numero de lineas de código, y queremos hacer cambios, solo implica modificar un SP y no toda la aplicación, lo que significa solo cambiamos los SP en el servidor y no tenemos que actualizar la aplicación en todos los equipos cliente.

Seguridad: una parte importante es que a los usuarios de nuestra aplicación, solo les proporcionamos los permisos para ejecutar los procedimientos almacenados y no el acceso a todos los objetos de la base. Es decir, si en nuestra aplicación encuentran una vulnerabilidad como SLQ Injection no se podrá explotar ejecutando SQL directamente.

Programabilidad: Los SP admiten el uso de variables y estructuras de control como IF, Bucles,  Case, etc. además del manejo de transacción y permite controlar excepciones. Y cuando trabajamos con SP CLR podemos hacer uso de cualquier lenguaje .NET como lo son C# y VB.NET.

Trafico de Red:  Pueden reducir el trafico de la red, debido a que se trabaja sobre el motor (en el servidor), y si una operación incluye hacer un trabajo de lectura primero y en base a eso realizar algunas operaciones, esos datos que se obtienen no viajan por la red.

Creando un Procedimiento almacenado

Para crear un procedimiento almacenado solo basta con ir a la base de datos desplegar el árbol hasta la parte de programación y luego en procedimientos almacenados y damos clic derecho en nuevo procedimiento almacenado como se ve en la siguiente figura:

Lo cual genera el siguiente código:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

-- Add the parameters for the stored procedure here

<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =

<Default_Value_For_Param1, , 0>,

<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =

<Default_Value_For_Param2, , 0>

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

GO

Es aquí en donde editamos nuestro procedimiento como mejor nos interese, en este caso usando la base de datos Northwind, crearemos un SP sencillo que queda más o menos así:

USE [Northwind]

GO

CREATE PROCEDURE [dbo].[GetAllEmployees]

-- Add the parameters for the stored procedure here

@LastName nvarchar(50),

@FirstName nvarchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT LastName, FirstName, Title

FROM dbo.Employees

WHERE FirstName = @FirstName AND LastName = @LastName

END

En este sencillo ejemplo, sólo devuelve el empleado especificado (nombre y apellidos), y el titulo a partir de una vista. Este procedimiento almacenado acepta coincidencias exactas de los parámetros pasados.

Para ejecutar el código debemos hacerlo de esta manera:

EXEC dbo.GetAllEmployees 'Davolio', 'Nancy'

Cómo lo consumimos desde una aplicación en .NET, pues de esta manera:

static void Main(string[] args)

{

//Creamos una nueva conexion.

SqlConnection miConn = new SqlConnection("Data Source=NORTABLEPC\\

SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

//Creamos un nuevo comando

SqlCommand miComm = new SqlCommand();

//Le asignamos la conexion.

miComm.Connection = miConn;

//especificamos que el comando es un stored procedure

miComm.CommandType = System.Data.CommandType.StoredProcedure;

//y escribimos el nombre del stored procedure a invocar

miComm.CommandText = "dbo.GetAllEmployees";

//Creamos un nuevo parametro

SqlParameter paramLastName = new SqlParameter();

paramLastName.ParameterName = "@LastName";

paramLastName.SqlDbType = System.Data.SqlDbType.NVarChar;

paramLastName.Value = "Davolio";

miComm.Parameters.Add(paramLastName);

SqlParameter paramFirstName = new SqlParameter();

paramFirstName.ParameterName = "@FirstName";

paramFirstName.SqlDbType = SqlDbType.NVarChar;

paramFirstName.Value = "Nancy";

miComm.Parameters.Add(paramFirstName);

//Y los agregamos a la coleccion de parametros del comando

myComm.Parameters.Add(myParam) 'Creamos un nuevo DataAdapter con nuestro comando.

SqlDataAdapter miDA = new SqlDataAdapter(miComm);

//Creamos un dataset para soportar los datos devueltos por el stored

procedure

DataSet EmpleadosDS = new DataSet();

//Pedimos al Data Adapter que llene el dataset (Esto llama a nuestro

comando)

miDA.Fill(EmpleadosDS);

//Y lo mostramos por pantalla

foreach (DataRow row in EmpleadosDS.Tables[0].Rows)

{

Console.WriteLine(row["LastName"].ToString() + " " +

row[1].ToString() + " " + row[2].ToString());

}

}

Procedimientos Almacenados con CLR

En Common Language Runtime (CLR), los procedimientos almacenados se implementan como métodos estáticos públicos en una clase de un ensamblado de Microsoft .NET Framework. El método estático se puede declarar como void o puede devolver un valor entero. Si devuelve un valor entero, éste se trata como el código devuelto desde el procedimiento. Los parámetros que se pasan a un procedimiento almacenado CLR pueden ser cualquiera de los tipos nativos de SQL Server que tengan un equivalente en código administrado. Para que la sintaxis Transact-SQL cree el procedimiento, estos tipos se deben especificar con el equivalente del tipo nativo de SQL más adecuado.

Ejemplo:

Para crear un SP con integración del CLR vamos a usar VS, y creamos un Proyecto de tipo SQL, al que llame StoreProcedure_Demo, lo primero que hace Visual Studio es preguntar el origen de datos, como se ve en la figura siguiente:

En donde seleccionamos Microsoft SQL Server (SqlClient) y damos clic en continuar. Ahora toca el turno de elegir el Servidor

En donde yo selecciono el servidor NORTABLEPC\SQLEXPRESS, y la base de datos Northwind, luego verifico la conexión y si todo va bien, por ultimo nos pedirá que si queremos habilitar la integración con el CLR, damos clic y listo. Luego damos clic derecho en nuestro proyecto Agregar->Procedimiento almacenado, como se ve en la figura:

Al procedimiento lo llamamos: GetEmpleado, damos y damos clic en Aceptar. Ahora vamos a escribir el siguiente código:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure (Name="GetEmpleado")]

public static void GetEmpleado()

{

// Inserte el código aquí

SqlContext.Pipe.Send("Hola Mundo! son las : " +

System.DateTime.Now.ToString() + "\n");

using (SqlConnection connection = new SqlConnection("context

connection=true"))

{

connection.Open();

SqlCommand command = new SqlCommand("SELECT LastName, FirstName FROM

Employees", connection);

SqlDataReader reader = command.ExecuteReader();

SqlContext.Pipe.Send(reader);

}

}

};

En el código anterior solo mandamos un mensaje, y devolvemos un select sencillo de los empleados, Bien para que esto funcione, debemos generar una solución de nuestro proyecto y después una implementación del mismo, esto permite que automáticamente se envié la dll a nuestra base de datos con el nombre de nuestro proyecto, y al mismo tiempo agrega el procedimiento llamado GetEmpledo

Ahora solo nos resta ejecutar nuestro procedimiento con la siguiente instrucción:

EXEC dbo.GetEmpleado

Lo interesante de esto, es que una vez que ya hemos llamado al SP, cuando se ejecute de nuevo, ya no se compilara instrucción por instrucción solo se llama al dll y listo, el resultado de ejecutarlo es el siguiente, por un lado manda el resultado y por otro el mensaje de “hola mundo” con la hora de la maquina, como se ve en la figura:

 

Hasta aquí dejamos este post, ya veremos en otro como ejecutar dichos procedimientos almacenados desde una aplicación. Espero que les sea de utilidad este post  y recuerden si lo creen conveniente tomen 5 min, para votar por este blog en la siguiente página : http://www.blogit.ms/ , voten por http://mspnor.wordpress.com

Saludos!!!

Etiquetas de Technorati: SQL Server,SQL Server 2005,SQL Server 2008

Descargar ejemplo   ConexionDB_02.zip

En esta ocasión vamos a continuar la clase conexión desarrollada unos artículos atrás, y ahora vamos a agregarle la opción de poder invocar procedimientos almacenados con múltiples parámetros de distintos tipos

de datos, de una forma muy sencilla y basados en los estándares altamente recomendados.

El primer ejemplo de esta Clase Conexion solo permite ejecutar querys simples.

Ahora la gran diferencia es que vamos a agregar dos clases extras, clase StoredProcedure y StoredProcedureParameter.

La clase StoredProcedure tiene dos variables, mNombreProcedimiento como un String y mParametros como una colección de datos, con base a estas variables privadas tenemos sus propias Propiedades Públicas llamadas Nombre y Parametros.

Public Class StoredProcedure

#Region " Variables "Private mNombreProcedimiento As StringPrivate mParametros As Collection#End Region

#Region " Propiedades "Public Property Nombre() As StringGetReturn mNombreProcedimientoEnd GetSet(ByVal Value As String)mNombreProcedimiento = ValueEnd SetEnd Property

Public Property Parametros() As CollectionGetReturn mParametrosEnd GetSet(ByVal Value As Collection)mParametros = ValueEnd SetEnd Property#End Region

.

.

.

Un punto importante de esta clase es su constructor, la cual inicializa las variables simples y la colección para los datos, y sin la cual no podríamos invocar los siguientes procedimientos y funciones.

#Region " Constructor "

'Solo recibe el nombre del procedimiento e inicializa la colección.Public Sub New(ByVal nNombre As String)TryNombre = nNombreParametros = New CollectionCatch ex As ExceptionThrow exEnd TryEnd Sub

#End Region

Ahora para poder utilizar esta clase tenemos el procedimiento AgregarParametro que recibe el nombre de la Variable y el Valor de esta variable. Algo con lo que hay que tener cuidado es que la variable se debe llamar igual a como esta declarada en el procedimiento y no hay que enviarle el @ ya que se agrega automáticamente en el procedimiento.

'Agrega los parametros del procedimiento y su respectivo valor.Public Sub AgregarParametro(ByVal pVariable As String, ByVal pValor As Object)TryDim iParametro As New StoredProcedureParameter("@" & pVariable, pValor)Me.Parametros.Add(iParametro)Catch ex As ExceptionThrow exEnd TryEnd Sub

Como podemos ver, al nombre de la variable se le agrega el @ necesario para que sea interpretado por el procedimiento almacenado, y el valor el cual es de un tipo Objeto, más adelante podremos ver como lo lee el sistema. Preferiblemente es que se le envien las variables en el mismo orden en que se reciben en el Procedimiento Almacenado.

Ahora para ejecutar el procedimiento con sus respectiva variables debemos invocar la funciónEjecutarProcedimiento que nos retorna un dataset.

'Ejecuta el procedimiento almacenado.Public Function EjecutarProcedimiento() As DataSetTryDim Conn As New ConexionDim sqlCmd As New SqlCommand(Me.Nombre, Conn.SQLConn)sqlCmd.CommandType = CommandType.StoredProcedure

Dim mParametro As StoredProcedureParameter

'Agrega las variables al procedimiento almacenadoFor Each mParametro In Me.ParametrosDim pParam As New SqlParameter(mParametro.Variable, mParametro.GetTypeProperty)pParam.Direction = ParameterDirection.InputpParam.Value = mParametro.ValorsqlCmd.Parameters.Add(pParam)Next

'SqlAdapter utiliza el SqlCommand para llenar el DatasetDim sda As New SqlDataAdapter(sqlCmd)

'Se llena el datasetDim ds As New DataSetsda.Fill(ds)

Conn.SQLConn.Close()Return dsCatch ex As ExceptionThrow exEnd TryEnd Function

En esta función declaramos una variable de tipo StoredProcedureParameter, la cual veremos más adelante.

En esta función EjecutarProcedimiento declaramos una variable SqlCommand y le indicamos que es de tipo StoredProcedure, ( sqlCmd.CommandType = CommandType.StoredProcedure ), con esto le

indicamos al SqlDataAdapter la forma en que se ejecuta la consulta. 

La última clase de este ejemplo, StoredProcedureParameter es la que administra las posibles variables del procedimiento, igualmente el procedimiento podría no recibir variables y la colección estar limpia.

Public Class StoredProcedureParameter

Private mVariable As StringPrivate mValor As Object

'Nombre de la variable, debe ser igual a la declarada en el procedimiento almacenadoPublic Property Variable() As StringGetReturn mVariableEnd GetSet(ByVal Value As String)mVariable = ValueEnd SetEnd Property

'Valor de la variable, puede ser de cualquier tipo de dato. preferible que 'coincida con las variables declaradas en GetTypePropertyPublic Property Valor()GetReturn mValorEnd GetSet(ByVal Value)mValor = ValueEnd SetEnd Property

'Se definen los posibles tipos de datos que se le van a enviar al procedimiento almacenado'Esta lista podria aumentar conforme se usen otro tipo de variable.Public ReadOnly Property GetTypeProperty() As SqlDbTypeGetIf mValor.GetType.FullName = "System.String" ThenReturn SqlDbType.VarCharElseIf mValor.GetType.FullName = "System.Int16" ThenReturn SqlDbType.IntElseIf mValor.GetType.FullName = "System.Int32" ThenReturn SqlDbType.IntElseIf mValor.GetType.FullName = "System.Int64" ThenReturn SqlDbType.IntElseIf mValor.GetType.FullName = "System.Decimal" ThenReturn SqlDbType.DecimalElseIf mValor.GetType.FullName = "System.Double" ThenReturn SqlDbType.BigIntElseIf mValor.GetType.FullName = "System.DateTime" ThenReturn SqlDbType.DateTimeElseIf mValor.GetType.FullName = "System.Byte" ThenReturn SqlDbType.ImageEnd IfEnd GetEnd Property

'Procedimiento de creacion de la variable.Public Sub New(ByVal pVariable As String, ByVal pValor As Object)TryMe.Variable = pVariableMe.Valor = pValorCatch ex As ExceptionThrow New Exception("Error en la creacion del Parametro" & vbCrLf & ex.Message)

End TryEnd Sub

End Class

El constructor recibe las dos variables del parametro, el nombre de la variable y el valor. 

La propiedad GetTypeProperty es muy importante en esta clase, ya que le indica al procedimiento de que tipo de datos es la variable que se le está enviando. 

Ahora para poder utilizar estas clases en un sistema de una forma muy sencilla deberíamos de invocarlas así:

Dim ds As New DataSet

Dim sp As New StoredProcedure("SP_Traer_Datos")sp.AgregarParametro("nombre_variable", valor_variable)sp.AgregarParametro("nombre_variable_2", valor_variable_2)

ds = sp.EjecutarProcedimiento()

Se pueden agregar tantas variable como sean necesarias no hay límite, esto en el caso de que se llame a un procedimiento almacenado, pero si lo que se ejecuta es una consulta directa (no recomendada para aplicaciones n capas), se debe hacer así:

Dim ds As New DataSetDim strQry As String

strQry = "SELECT * FROM TABLA"ds = con.ConsultaBD(strQry)

Me.dgDatos.DataSource = ds.Tables(0)

De esta forma podemos invocar procedimientos almacenados en nuestras aplicaciones y no tener que lidiar con estar desarrollando código distinto cada vez que lo necesitemos.

Uso de Parámetros en procedimientos almacenados con .NET y MySQL

Cómo usar procedimientos almacenados y sus parámetros usando MySQLConnector y .NET

 

Fecha: 18/Oct/2007 (11-oct-07)Autor: Jesús Utrera Burgal - jeutbur@yahoo.es

Colaborador de http://www.carlosutrera.com/blog/ (por si interesa)

 

Introducción

Después de intentos infructuosos, pedir ayuda y buscar información por aquí y por allá, conseguí algo a priori sencillo (siempre temo las cosas a priori sencillas): Usar parámetros con procedimientos almacenados bajo bases de datos MySQL. Como prometí, aquí pongo este trozo de conocimiento para todo aquel que necesite trabajar contra MySQL desde .NET. El código es muy fácil pasarlo a C# y otros asi que...

Aquí se puede ver desde cómo conectar con una base de datos MySQL (ya existe información por estos lares) hasta como usar procedimientos almacenados pasándo parámetros. Para ello uso programación en Visual Basic .NET 2003, las librerías de MySQLConnector 5.0 (http://www.mysql.com) y por supuesto MySQL.

 

Manos a la obra

Primero creamos un procedimiento almacenado en nuestra base de datos.

CREATE DEFINER=`root`@`localhost` PROCEDURE `mi_procedimiento`(IN mi_variable VARCHAR(15))

BEGIN

  declare variable1 VARCHAR(15);

  set variable1 = mi_variable;

  select * from MiTabla where Cod_Tabla like variable1;

END

pasamos la variable de entrada (de entrada: "IN", de salida: OUT, ambas: INOUT [o algo así ;-)]) mi_variable que es un texto.

Ahora toca usar el procedimiento almacenado en la base de datos desde nuestra aplicación en Visual Basic .NET ¿Cómo? sencillo (lo sencillo que se hace cuando ya lo sabes jejeje)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Dim conStr As String

  conStr = "server=localhost;user id=root;password=passwd;database=basedatos"

Dim con As New MySqlConnection(conStr)

  Try

    con.Open()

  Catch ex As Exception

    'Código de captura de la excepción

  End Try

  Dim comando As New MySqlCommand("mi_procedimiento", con)

  comando.CommandType = CommandType.StoredProcedure

  Dim fila As MySqlDataReader

  Dim p As New MySqlParameter("?mi_variable", MySql.Data.MySqlClient.MySqlDbType.VarChar)

  p.Value = "root"

  p.Direction = ParameterDirection.InputOutput

  comando.Parameters.Add(p)

  Try

    fila = comando.ExecuteReader

    While fila.Read

      MsgBox(fila(0).ToString)

    End While

    fila.Close()

  Catch ex As MySqlException

    'Código de captura de la excepción

  End Try

  con.Close()

End Sub

El primer bloque consiste en conectar con la base de datos, creo que en este punto no hace falta explicar como se conecta con la base de datos porque es idéntica a cualquier otra conexión con bases de datos diferentes. Ahora toca crear el comando que va a llamar al procedimiento almacenado:

Dim comando As New MySqlCommand("mi_procedimiento", con)

comando.CommandType = CommandType.StoredProcedure

sencillo, ¿no? declaramos el objeto MySQLCommand y le decimos (2ª lïnea) que se trata de un procedimiento almacenado.

Ahora toca lo interesante: pasarle el parámetro (con lo sencillo que se hacía en SQLServer).

Dim p As New MySqlParameter("?mi_variable", MySql.Data.MySqlClient.MySqlDbType.VarChar)

p.Value = "root"

p.Direction = ParameterDirection.InputOutput

comando.Parameters.Add(p)

Muy importante: dar la dirección del parámetro de entrada/salida (en mi caso, y teniendo en cuenta mis escasos dummie-conocimientos, era la única manera de que funcionara). El resto es como lo demás, declaras y creas el objeto parámetro (1ª línea), especificando el parámetro y su tipo, darle un valor (2ª línea), especificar la dirección (3ª línea) y añadirselo al comando (última línea) porque si no esto no vale "pa ná".

El último bloque es como el resto, en mi caso un datareader captura el resultado y lo proceso mostrando el valor del primer campo.

El código completo:

Primero, el procedimiento almacenado en MySQL:

CREATE DEFINER=`root`@`localhost` PROCEDURE `mi_procedimiento`(IN mi_variable VARCHAR(15))

BEGIN

  declare variable1 VARCHAR(15);

  set variable1 = mi_variable;

  select * from MiTabla where Cod_Tabla like variable1;

END

A continuación sigue código en Visual Basic .NET

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Dim conStr As String

  conStr = "server=localhost;user id=root;password=passwd;database=basedatos"

Dim con As New MySqlConnection(conStr)

  Try

    con.Open()

  Catch ex As Exception

    'Código de captura de la excepción

  End Try

  Dim comando As New MySqlCommand("mi_procedimiento", con)

  comando.CommandType = CommandType.StoredProcedure

  Dim fila As MySqlDataReader

  Dim p As New MySqlParameter("?mi_variable", MySql.Data.MySqlClient.MySqlDbType.VarChar)

  p.Value = "root"

  p.Direction = ParameterDirection.InputOutput

  comando.Parameters.Add(p)

  Try

    fila = comando.ExecuteReader

    While fila.Read

      MsgBox(fila(0).ToString)

    End While

    fila.Close()

  Catch ex As MySqlException

    'Código de captura de la excepción

  End Try

  con.Close()

End Sub

Eso es totototo totototodddo amigos. Espero que resulte muy útil para todo el mundo y nos ayude a crear amistad entre MySQL y .NET, que no todo fue un camino de rosas (hasta antes de MySQL Connector al menos). Y como decía un maestro: ahora, de ejercicio, hacerlo para C#, C++, etc. ;-)

Espacios de nombres usados en el código de este artículo:

MySql.Data.MySqlClient (de la libreria MySQLConnector)

Llamar a procedimientos almacenados desde VB .NET

Viernes 16 de Agosto de  2002

Introducción

    Aunque no a la velocidad que yo quisiera, si que es cierto que vamos adentrándonos en el mundo .NET. La verdad es que me fascina, es impresionante en mi opinión el trabajo que Microsoft ha hecho con este entorno de programación y aunque el cambio al principio pueda parecer demasiado grande, merece la pena hasta el último minuto. 

    Dentro del mundo de la programación, sabéis que lo que más importa a PortalSql.com es la relación con Sql-Server y en .NET esta relación es la más directa que jamás Microsoft ha proporcionado desde herramientas de desarrollo. ADO.net es la vía más rápida para entenderse con Sql-Server y las pruebas de rendimiento hechas por Microsoft  así lo demuestran. Además el maestro de los grupos de noticias y MVP Fernando Guerrero así lo ha manifestado en más de una ocasión en los grupos públicos de noticias.

    Lo que tenéis a continuación es un trozo de código escrito en VB.NET, El código crea un formulario con dos controles de tipo fecha para llamar a Sql-Server a un procedimiento almacenado. El procedimiento almacenado está al final del artículo (su código). Esta pensado todo para ejecutar el procedimiento almacenado en una B.D. que se llame pruebas, pero esto podéis cambiarlo si cambiáis la cadena de conexión, en concreto la parte donde dice 'Initial Catalog'. 

    Podéis crear un formulario y copiar y pegar todo el código que debería funcionar (aunque yo no lo he probado así). Tened en cuenta que la razón por la que antes en nuestro viejo V.B. no podía hacerse esto era porque parte de la definición del formulario (la que situaba los objetos) estaba inaccesible para nosotros, al menos por métodos limpios. Ahora eso no es así, y el código añadido por VB.NET para colocar los controles está disponible dentro del entorno sin mayores problemas. Así colocar controles de forma estática (diseño) o dinámica (Ejecución) no debería suponer mayor problema, aunque eso será otro día y en otro artículo.

    El principal interés de este artículo se encuentra AQUÍ     

Introducción

    Public Class frmLlamaraSpsInherits System.Windows.Forms.Form#Region " Código generado por el Diseñador de Windows Forms "Public Sub New()MyBase.New()'El Diseñador de Windows Forms requiere esta llamada.InitializeComponent()'Agregar cualquier inicialización después de la llamada a InitializeComponent()End Sub'Form reemplaza a Dispose para limpiar la lista de componentes.Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)If disposing ThenIf Not (components Is Nothing) Thencomponents.Dispose()End IfEnd IfMyBase.Dispose(disposing)End Sub'Requerido por el Diseñador de Windows FormsPrivate components As System.ComponentModel.IContainer'NOTA: el Diseñador de Windows Forms requiere el siguiente procedimiento'Puede modificarse utilizando el Diseñador de Windows Forms.'No lo modifique con el editor de código.Friend WithEvents lblDesde As System.Windows.Forms.LabelFriend WithEvents fechahasta As System.Windows.Forms.LabelFriend WithEvents Desde_Fecha As System.Windows.Forms.DateTimePickerFriend WithEvents CargarPedidos As System.Windows.Forms.ButtonFriend WithEvents Hasta_fecha As System.Windows.Forms.DateTimePickerFriend WithEvents Datos As System.Windows.Forms.DataGrid<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()Me.lblDesde = New System.Windows.Forms.Label()Me.fechahasta = New System.Windows.Forms.Label()Me.Desde_Fecha = New System.Windows.Forms.DateTimePicker()Me.Hasta_fecha = New System.Windows.Forms.DateTimePicker()Me.CargarPedidos = New System.Windows.Forms.Button()Me.Datos = New System.Windows.Forms.DataGrid()CType(Me.Datos, System.ComponentModel.ISupportInitialize).BeginInit()Me.SuspendLayout()''lblDesde

'Me.lblDesde.Location = New System.Drawing.Point(8, 16)Me.lblDesde.Name = "lblDesde"Me.lblDesde.Size = New System.Drawing.Size(72, 16)Me.lblDesde.TabIndex = 0Me.lblDesde.Text = "Desde Fecha"''fechahasta'Me.fechahasta.Location = New System.Drawing.Point(8, 35)Me.fechahasta.Name = "fechahasta"Me.fechahasta.Size = New System.Drawing.Size(72, 16)Me.fechahasta.TabIndex = 1Me.fechahasta.Text = "Hasta fecha:"''Desde_Fecha'Me.Desde_Fecha.Location = New System.Drawing.Point(84, 13)Me.Desde_Fecha.Name = "Desde_Fecha"Me.Desde_Fecha.Size = New System.Drawing.Size(208, 20)Me.Desde_Fecha.TabIndex = 2''Hasta_fecha'Me.Hasta_fecha.Location = New System.Drawing.Point(84, 32)Me.Hasta_fecha.Name = "Hasta_fecha"Me.Hasta_fecha.Size = New System.Drawing.Size(208, 20)Me.Hasta_fecha.TabIndex = 3''CargarPedidos'Me.CargarPedidos.Location = New System.Drawing.Point(184, 63)Me.CargarPedidos.Name = "CargarPedidos"Me.CargarPedidos.Size = New System.Drawing.Size(104, 24)Me.CargarPedidos.TabIndex = 4Me.CargarPedidos.Text = "Cargar Pedidos"''Datos'Me.Datos.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _Or System.Windows.Forms.AnchorStyles.Left) _Or System.Windows.Forms.AnchorStyles.Right)Me.Datos.DataMember = ""Me.Datos.HeaderForeColor = System.Drawing.SystemColors.ControlTextMe.Datos.Location = New System.Drawing.Point(8, 96)Me.Datos.Name = "Datos"Me.Datos.Size = New System.Drawing.Size(296, 160)Me.Datos.TabIndex = 5''frmLlamaraSps'Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)Me.ClientSize = New System.Drawing.Size(344, 273)Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Datos, Me.CargarPedidos, Me.Hasta_fecha, Me.Desde_Fecha, Me.fechahast

a, Me.lblDesde})Me.Name = "frmLlamaraSps"Me.Text = "Llamadas a procedimientos almacenados"CType(Me.Datos, System.ComponentModel.ISupportInitialize).EndInit()Me.ResumeLayout(False)End Sub#End RegionPRIVATE SUB CARGARPEDIDOS_CLICK(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CargarPedidos.Click' Necesitaremos un objeto Dataadapter para obtener el' conjunto de datos que devuelva nuestro procedimiento almacenado.' Para traspasar los datos al datasetDim oDa As SqlClient.SqlDataAdapter' y un Objeto Dataset en el que almacenar esos datos...Dim oDs As System.Data.DataSet' Vamos a cargar todo desde un único evento..' Normalmente habrían varios de los elementos que estarán a nivel de' aplicación como la conexión a Bd. Aunque se abra y se cierre periodicamente...Try    ' Declaramos y pasamos la cadena de conexión al    ' constructor del objeto SqlConnection.    ' Con esto realmente estamos abriendo la base de datos    ' en el mismo instante que la declaramos..    ' SqlConnection tiene un constructor 'sobrecargado' por lo que no    ' es imprescindible que la apertura se haga tal y como está aqui    ' descrito ...        Dim oConexion As SqlClient.SqlConnection = _                New SqlClient.SqlConnection("Data Source=127.0.0.1;Trusted_connection=yes;Initial     Catalog=Pruebas")    ' El constructor de Sql-Command también es sobrecargado    ' usamos esta notación por ser consecuentes con la anterior    ' pero ni mucho menos es la única correcta...    Dim oCommand As SqlClient.SqlCommand = _            New SqlClient.SqlCommand("[Ventas Northwind]", oConexion)    ' Para pasarle los parámetros al objeto command usaremos esta variable..    Dim oParameter As SqlClient.SqlParameter    ' Nuestor sp tiene dos parámetros que pasaremos mediante las siguientes    ' instrucciones    oParameter = oCommand.Parameters.Add("@FechaDesde", SqlDbType.DateTime)    oParameter.Value = Desde_Fecha.Value    oParameter = oCommand.Parameters.Add("@FechaHasta", SqlDbType.DateTime)    oParameter.Value = Hasta_fecha.Value    ' Decimos el tipo de commando    oCommand.CommandType = CommandType.StoredProcedure    ' Abrimos nuestra conexion.    oConexion.Open()    ' El constructor de DataAdapter, admite un objeto command.    ' el resultado de usarlo es un Data adapter relleno con el resultado    ' de ejecutar el procedimiento almacenado...    oDa = New SqlClient.SqlDataAdapter(oCommand)    ' Creamos una instancia de nuestro dataset

    oDs = New System.Data.DataSet()    ' y por último lo rellenamos.    oDa.Fill(oDs)    ' Hasta aquí las instruciones para llamar a nuestro sp    ' no sería necesario si no necesitasemos recuperar el conjunto    ' de datos que devuelve el Sp.    ' Eso sí una vez que los ha devuelto ,lo mejor será usarlos...    Datos.DataSource = oDs.Tables(0)    Datos.Refresh()    'Y destruimos aunque no sea necesario gracias al garbaje collector    oDs.Dispose()    oDa.Dispose()    oCommand.Dispose()    oConexion.Close()    oConexion.Dispose() Catch ex As Exception    System.Windows.Forms.MessageBox.Show(ex.Message.ToString)End TryEnd SubPrivate Sub frmLlamaraSps_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load    Desde_Fecha.Value = CDate("01/01/1997")End SubEnd Class    Y ahora el procedimiento almacenado ... create proc [Ventas Northwind]@FechaDesde Datetime,@FechaHasta Datetimeas    select o.OrderId,o.EmployeeId,o.Customerid,o.orderdate,                     e.LastName + ',' + e.firstname [Employee Name],                            ContactNamefrom Northwind..orders o         inner join Northwind..customers C on C.CustomerId=o.customerId         inner join Northwind..employees E on o.EmployeeID=e.EmployeeIDwhere OrderDAte between @FechaDesde and @Fechahasta Espero que os sirvaMiguel EgeaMicrosoft Sql-Server MVP

ursores en SQL Server

 

Descripción: Los cursores son una herramienta de SQL que nos permite recorrer el resultado de una consulta SQL y realizar operaciones en cada paso de ésta. Es así como nos ayuda a realizar operaciones que de otro modo serían más complejas o irrealizables. A continuación coloco el código de un cursor muy simple para el Analizador de Consultas de SQl Server.

/* Este cursor deja las contraseñas iguales al nombre de usuario. La tabla Cliente tiene estos tres campos: CliCod, CliUser, CliPass */

-- declaramos las variables declare @cod as int declare @user as varchar(50) declare @pass as varchar(50) -- declaramos un cursor llamado "CURSORITO". El select debe contener sólo los campos a utilizar. declare CURSORITO cursor for select CliCod, CliUser, CliPass from Cliente open CURSORITO -- Avanzamos un registro y cargamos en las variables los valores encontrados en el primer registro fetch next from CURSORITO into @cod, @user, @pass     while @@fetch_status = 0         begin         update Cliente set CliPass= @user where CliCod=@cod         -- Avanzamos otro registro         fetch next from CURSORITO         into @cod, @user, @pass         end -- cerramos el cursor close CURSORITO deallocate CURSORITO

Procedimiento almacenado en VB. Net 2005 y Sql ServerPUBLICADO POR EDUARDO REYES LUNES 2 DE NOVIEMBRE DE 2009 

Ahora realizaremos una aplicación donde

consultaremos (filtraremos) por fechas las ventas por país de los diferentes empleados,

para llevar a cabo esta aplicación haremos uso de un Procedimiento Almacenado

denominado “Employee Sales by Country” que se encuentra ya creado dentro de la

base de datos Northwind.

La aplicación a realizar consiste en consultar por fechas las ventas por país de los

empleados mostrando las coincidencias dentro de un listbox, toda esta consulta (filtro)

se realizara a través de un procedimiento almacenado (stored procedure), para ello

haremos uso del objeto SqlCommand el cual nos va a permitir representar al

procedimiento almacenado dentro de la aplicación a través de sus propiedades

CommandType, CommandText y Parameters y su método ExecuteReader el cual nos

permite ejecutar los comandos con las filas obtenidas por la ejecución del

procedimiento almacenado. También haremos uso del objeto SqlParameter el cual nos

permitirá almacenar los parámetros para ejecutar el procedimiento y por ultimo

usamos el objeto SqlDataReader el cual no permite leer los datos obtenidos por la

ejecución del procedimiento. A continuación en la siguiente figura mostramos el

procedimiento ya creado dentro de la base de datos Northwind.

Procedimiento Almacenado “Employee Sales by Country”

Primero crearemos un proyecto que se denominara WAccesoDatos. Una vez creado el

proyecto en VB .NET, importaremos los espacio de nombres y declararemos a nivel de

clase un conjunto de variables para la manipulación de los datos. Veamos la siguiente

figura.

Variables declaradas

Una vez declaradas las variables vamos a crear un formulario y lo llamaremos

frmStoredProcedure, luego agregamos los siguientes controles dentro del formulario

creado.

Una vez realizado el ingreso de todos los controles dentro del formulario, el

frmStoredProcedure debe de quedar como la siguiente figura.

Ventana del frmStoredProcedure

Ahora empezaremos con la escritura del código de la aplicación, codificando el evento

load del frmStoredProcedure; estableciendo la conexión a la base de datos. En la figura

siguiente se muestra el código del evento.

Código del evento load del frmStoredProcedure

Luego realizaremos la codificación del evento click del botón consultar donde se

realizara la ejecución del procedimiento almacenado. En la siguiente figura se muestra

el código del evento.

Código del evento load del frmStoredProcedure

Al terminar el código del evento load del frmStoredProcedure y del evento click del

btnconsultar, pasamos a ejecutar el formulario, el cual debe de mostrarse como en la

penultima figura, una vez ingresado las fechas se procede a presionar el botón

consultar mostrándose como en la ultima figura.

Ingreso de las fechas a consultar

G