Colaboraciones en el Guille

Usando SQLDataReaders en Visual Basic .Net

Uso de Procedimientos Almacenados 

Fecha: 15/Oct/2005 (12 de Octubre de 2005)
Autor: Ing. Fernando Luque Sánchez - DCE 4 Estrellas
fls2307@hotmail.com
 

Sres. Desarrolladores, en esta ocasión comparto con ustedes un ejemplo del uso de SQLDataReaders con Procedimientos Almacenados.

Un poco de teoría

Visual Studio .Net permite el acceso a datos en dos formas bastante marcadas, entornos desconectados y entornos conectados. Los entornos desconectados se crean cuando usamos SQLDataAdapters, estos no necesitan de mantener la conexión abierta y ademas no requieren que se habra y cierre explicitamente la conexión. Los entornos conectados se dan con el uso de DataReaders, este requieren  que se abra la conexión usando el método Open, luego se ejecute el DataReader, se lean los datos almacenados en el buffer, se cierre el DataReader y luego cerrar la conexión, estos dos últimos pasos con los metodos Close de cada objeto.

Un ejemplo lo encuentran en:
http://www.elguille.info/colabora/NET2005/FernandoLuque_Usando_SQLDataReader.htm

Para crear un SqlDataReader, se debe llamar al método ExecuteReader  del objeto SQLCommand, en vez de utilizar directamente un constructor.

Mientras se utiliza SqlDataReader, la conexión SQLConnetion asociada está ocupada atendiendo a SqlDataReader, y no se pueden realizar otras operaciones en SqlConnection, aparte de cerrarla. Esto es así hasta que se llama al método Close del SqlDataReader. Por ejemplo, sólo se pueden recuperar los parámetros de salida después de llamar a Close.

El usuario de SqlDataReader puede ver los cambios que realiza otro proceso o subproceso en un conjunto de resultados mientras se leen los datos. Sin embargo, el comportamiento exacto depende de los intervalos de tiempo.

IsClosed y RecordsAffected son las únicas propiedades a las que se puede llamar después de cerrar SqlDataReader. Aunque se puede tener acceso a la propiedad RecordsAffected siempre que SqlDataReader exista, se debe llamar siempre a Close antes de devolver el valor de RecordsAffected para asegurarse de que se devuelve el valor exacto.

El ejemplo

El formulario tiene el siguiente aspecto:

Aplicación en N Capas..un ejemplo

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

El botón para crear los Procedimientos Almacenados

Private Sub CrearSPs(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles cmdCrearSP.Click

        Dim cn As New SqlConnection("user id=sa;initial catalog=Northwind;persist security info=False")
        Dim vExito As Boolean = False

        Try

            'Borrar los SP si existen y crearlos nuevamente
            'Los procedimientos que se crearán son:
            '               ObtenerProductos
            '               Los20Caros
            '               ContarPromedio

            ' OBTENER PRODUCTOS
            Dim vSQLObtenerProductos As String = _
                "IF EXISTS (SELECT * FROM northwind.dbo.sysobjects " & _
                "WHERE Name = 'ObtenerProductos' AND TYPE = 'p')" & vbCrLf & _
                "DROP PROCEDURE ObtenerProductos"
            Dim vComando1 As New SqlCommand(vSQLObtenerProductos, cn)

            cn.Open()
            vComando1.ExecuteNonQuery()
            vComando1.CommandText = _
                                "CREATE PROCEDURE ObtenerProductos" & vbCrLf & _
                                "@CategoryID Int " & vbCrLf & _
                                "AS " & vbCrLf & _
                                "SELECT ProductID, ProductName, UnitPrice, UnitsInStock " & _
                                "FROM Northwind.dbo.Products " & _
                                "WHERE CategoryID = @CategoryID"
            vComando1.ExecuteNonQuery()

            'Para contar los productos y obtener el promedio de precios
            vComando1.CommandText = _
                        "IF EXISTS (SELECT * FROM northwind.dbo.sysobjects " & _
                        "WHERE Name = 'ContarPromedio' " & _
                        "AND TYPE = 'p')" & vbCrLf & _
                        "DROP PROCEDURE ContarPromedio"
            vComando1.ExecuteNonQuery()
            vComando1.CommandText = _
                                "CREATE PROCEDURE ContarPromedio " & vbCrLf & _
                                "@CatID Int, @Promedio Int OUT" & vbCrLf & _
                                "AS " & vbCrLf & _
                                "DECLARE @SumProdPrices Money " & vbCrLf & _
                                "SELECT @Promedio = SUM(UnitPrice)/COUNT(ProductID) " & _
                                "FROM Northwind.dbo.Products " & _
                                "WHERE CategoryID = @CatID" & vbCrLf & _
                                "RETURN " & _
                                "(SELECT COUNT(ProductID) " & _
                                "FROM Northwind.dbo.Products " & _
                                "WHERE CategoryID = @CatID)"
            vComando1.ExecuteNonQuery()

            'PARA LOS 20 PRODUCTOS MAS CAROS
            vComando1.CommandText = _
                                      "IF EXISTS (SELECT * FROM northwind.dbo.sysobjects " & _
                                    "WHERE Name = 'Los20Caros' " & _
                                    "AND TYPE = 'p')" & vbCrLf & _
                                    "DROP PROCEDURE Los20Caros"
            vComando1.ExecuteNonQuery()
            vComando1.CommandText = _
                    "Create procedure Los20Caros AS SET ROWCOUNT 20 " & vbCrLf & _
                    "SELECT ProductId, ProductName,UnitPrice,UnitsInStock FROM Products" & vbCrLf & _
                    "ORDER BY Products.UnitPrice DESC"
            vComando1.ExecuteNonQuery()
            vExito = True

        Catch ex As Exception

            MessageBox.Show("Error : " & ex.Message)
            vExito = False
            cmdMasCaros.Enabled = False
            cmdObtener.Enabled = False
            cmdTotalPromedio.Enabled = False

        Finally
            cn.Close()

        End Try
        If vExito = True Then
            cmdMasCaros.Enabled = True
            cmdObtener.Enabled = True
            cmdTotalPromedio.Enabled = True

            'Origen del combo de Categorias
            Dim dsDatos As New DataSet
            Dim daCat As New SqlDataAdapter("Select CategoryId, CategoryName from Categories", cn)
            daCat.Fill(dsDatos, "Categorias")
            cboCat.DataSource = dsDatos.Tables("Categorias")
            cboCat.DisplayMember = dsDatos.Tables("Categorias").Columns(1).ToString
            cboCat.ValueMember = dsDatos.Tables("Categorias").Columns(0).ToString
            cboCat.SelectedIndex = 0
        End If
End Sub

Para obtener los 20 productos mas caros

Private Sub MasCaros(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles cmdMasCaros.Click
        Dim cn As New SqlConnection("user id=sa;initial catalog=Northwind;persist security info=False")

        Dim vMasCaro As New SqlCommand("Los20Caros", cn)
        vMasCaro.CommandType = CommandType.StoredProcedure
        Dim drMasCaros As SqlDataReader
        cn.Open()
        drMasCaros = vMasCaro.ExecuteReader

        'Llenar el ListView

        Dim vpos As Integer
        lvCaros.Items.Clear()

        While drMasCaros.Read
            lvCaros.Items.Add(drMasCaros(0))
            lvCaros.Items(vpos).SubItems.Add(drMasCaros(1))
            lvCaros.Items(vpos).SubItems.Add(drMasCaros(2))
            lvCaros.Items(vpos).SubItems.Add(drMasCaros(3))
            vpos += 1
        End While

        drMasCaros.Close()
        cn.Close()

End Sub

El código completo se encuentra en el archivo adjunto. Bájalo y no te olvides de votar, tus puntos me alientan a seguir compartiendo.

Suerte a todos y a seguir desarrollando.

Ing. Fernando Luque Sánchez
DCE 4 Estrellas


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

System.Data
System.DataSQLClient


Fichero con el código de ejemplo: FernandoLuque_DataReaders_SP.zip - (09) KB


ir al índice principal del Guille