sábado, 18 de julio de 2015

SQL Server, Crear un procedimiento almacenado de paginación

Algunas veces en un formulario se muestran sólo algunos datos de todos los que tenemos que mostar y necesitamos algo para mostar todos, para ello utilizamos los botones típicos de paginación.
SQL Server, procedimiento almacenado de paginación



que nos permiten cargar el formulario con una vista completa y cambiar esta vista por la siguiente como si de página se tratara cada vez que pulsemos el botón correspondiente.

Presento a continuación un procedimiento almacenado de SQL Server para realizar una paginación.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[sp_glo_paginar] (@sintNPag SMALLINT, @sintPagActual SMALLINT, @varSQL VARCHAR(8000))
AS
      DECLARE @varTmpTable VARCHAR(20)-- Nombre de la tabla temporal
      DECLARE @varTmpSQLDespuesFROM VARCHAR(8000)-- Después del FROM
      DECLARE @varTmpSQLCampos VARCHAR(8000)   -- Campos seleccionados
      DECLARE @varTmpSQL VARCHAR(8000)   -- SQL final
      DECLARE @sintLenSQL SMALLINT -- Longitud de la cadena recibida
      DECLARE @sintPosicionSELECT SMALLINT     -- Posición del SELECT
      DECLARE @sintPosicionFROM SMALLINT       -- Posición del FROM
      DECLARE @sintPosicionPrimerAsterisco SMALLINT-- Posición de * para validar
      DECLARE @sintPosicionDISTINCT SMALLINT   -- Posición del distinct
      DECLARE @sintPosicionALL SMALLINT        -- Posición del ALL
      DECLARE @intPrincipioPagina INTEGER     -- Número de registro del que partimos
     
      SET @varSQL = UPPER(@varSQL)

      IF LEN(LTRIM(RTRIM(@varSQL))) = 0 OR @varSQL IS NULL
            BEGIN
                  PRINT 'Se debe indicar una sentencia SQL'
                  RETURN
            END

      SET @sintPosicionPrimerAsterisco = CHARINDEX('*', @varSQL)
      SET @sintLenSQL = LEN(@varSQL)
      SET @sintPosicionFROM = @sintLenSQL - CHARINDEX(' MORF ', REVERSE(@varSQL)) - 3
      IF @sintPosicionPrimerAsterisco < @sintPosicionFROM AND @sintPosicionPrimerAsterisco <> 0
            BEGIN
                  PRINT 'Se deben indicar los campos, no vale *'
                  RETURN
            END

      IF CHARINDEX(' TOP ', @varSQL) <> 0
            BEGIN
                  PRINT 'No tiene sentido utilizar TOP en este proceso'
                  RETURN
            END

      IF @sintNPag <= 0
            SET @sintNPag = 1

      IF @sintPagActual <= 0
            SET @sintPagActual = 1

      SET @intPrincipioPagina = @sintNPag * (@sintPagActual-1)

      SET @varTmpSQLCampos = LEFT(@varSQL, @sintPosicionFROM-1)

      SET @varTmpSQLDespuesFROM = SUBSTRING(@varSQL, @sintPosicionFROM, @sintLenSQL - @sintPosicionFROM + 1)

      SET @sintPosicionSELECT = CHARINDEX('SELECT ', @varTmpSQLCampos)
      SET @varTmpSQLCampos = STUFF(@varTmpSQLCampos, @sintPosicionSELECT, 6,'')

      SET @sintPosicionDISTINCT = CHARINDEX(' DISTINCT ', @varTmpSQLCampos)
      IF @sintPosicionDISTINCT <> 0
            SET @varTmpSQLCampos = STUFF(@varTmpSQLCampos, @sintPosicionDISTINCT + 1, 8,'')

      SET @sintPosicionALL = CHARINDEX(' ALL ', @varTmpSQLCampos)
      IF @sintPosicionALL <> 0
            SET @varTmpSQLCampos = STUFF(@varTmpSQLCampos, @sintPosicionALL + 1, 3,'')

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

      EXEC DBO.sp_glo_tmp_tabla_global @varTmpTable OUTPUT

      SET @varTmpSQL = ' SELECT TOP 0 ' + @varTmpSQLCampos +  ', IDENTITY(int, 1 ,1) intPosicionRegistro INTO ' + @varTmpTable + ' ' + @varTmpSQLDespuesFROM + CHAR(13)
      EXECUTE (@varTmpSQL)

      SET @varTmpSQLCampos = ''
      SELECT @varTmpSQLCampos = @varTmpSQLCampos + CASE @varTmpSQLCampos WHEN '' THEN '' ELSE ', ' END + name FROM tempdb..syscolumns WHERE id = OBJECT_ID('tempdb..' + @varTmpTable) AND name <> 'intPosicionRegistro' ORDER BY colorder
     
      SET @varTmpSQL = ' INSERT INTO ' + @varTmpTable + '(' + @varTmpSQLCampos + ')' + @varSQL + CHAR(13)
      SET @varTmpSQL = @varTmpSQL + ' SELECT TOP ' + CAST(@sintNPag AS VARCHAR(20)) + ' ' + @varTmpSQLCampos + ' , (SELECT max(intPosicionRegistro) FROM ' + @varTmpTable + ') intTotalRegistros  FROM ' + @varTmpTable + ' WHERE intPosicionRegistro > ' + CAST(@intPrincipioPagina AS VARCHAR(20)) + ' ORDER BY intPosicionRegistro ASC ' + CHAR(13)
      SET @varTmpSQL = @varTmpSQL + ' DROP TABLE ' + @varTmpTable
      EXECUTE (@varTmpSQL)

Una vez creado nuestro procedimiento almacenado en SQL Server sólo tendremos que añadir una llamada desde nuestro código para utilizarlo.
El formulario en .NET sería del tipo:

paginar con un procedimiento almacenado de SQL Server

A continuación pongo un esbozo de cómo sería más o menos la estructura del código en .NET (ojo este código no funciona directamente es un código aproximativo)
Las funciones a definir por el desarrollador están entre corchetes y en verde [ ]

Imports System.Data.SqlClient
Imports Microsoft.SqlServer

Public Class Form1
    Private mblnEjecutaSP As Boolean
    Private mstrSql As String
    Private mintRegistrosPorPagina As Integer
    Private mintPaginaActual As Integer
    Private mintTotalPaginas As Integer
   
    'Procedimiento Paginar.
    'Desde el Código se llama:

    Public Sub Paginar(ByVal intNumeroRegistrosPagina As Integer, ByVal intNumeroPagina As Integer, ByVal strSql As String, ByVal blnEjecutaSP As Boolean)

        '[Definicion de conexión]

        rdoQ.SQL = "{ call dbo.sp_glo_paginar(" & intNumeroRegistrosPagina & "," & intNumeroPagina & "," & strSql & ") }"


        If Not [Fin de Fichero] Then
            mintPaginaActual = intNumeroPagina
            cmdIrPrincipio.Enabled = (mintPaginaActual <> 1)
            cmdIrAtras.Enabled = (mintPaginaActual <> 1)

            If mintTotalPaginas = 0 Then
                mblnEjecutaSP = blnEjecutaSP
                cmdIrPrincipio.Visible = True
                cmdIrAtras.Visible = True
                cmdIrAdelante.Visible = True
                cmdIrFinal.Visible = True
                mintRegistrosPorPagina = intNumeroRegistrosPagina
                mstrSql = strSql
            End If

            lblPagina.Text = "Pág " & mintPaginaActual & "/" & mintTotalPaginas

            cmdIrAdelante.Enabled = (mintPaginaActual <> mintTotalPaginas)
            cmdIrFinal.Enabled = (mintPaginaActual <> mintTotalPaginas)
        Else
            cmdIrAdelante.Enabled = False
            cmdIrFinal.Enabled = False
            cmdIrPrincipio.Enabled = False
            cmdIrAtras.Enabled = False
        End If

    End Sub


    Private Sub cmdIrAdelante_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrAdelante.Click
        Paginar(mintRegistrosPorPagina, mintPaginaActual + 1, mstrSql, mblnEjecutaSP)
        '[Llamada a nuestro procedimiento de Carga ]
    End Sub

    Private Sub cmdIrPrincipio_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrPrincipio.Click
        Paginar(mintRegistrosPorPagina, 1, mstrSql, mblnEjecutaSP)
        '[Llamada a nuestro procedimiento de Carga ]
    End Sub

    Private Sub cmdIrAtras_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrAtras.Click
        Paginar(mintRegistrosPorPagina, mintPaginaActual - 1, mstrSql, mblnEjecutaSP)
        '[Llamada a nuestro procedimiento de Carga ]
    End Sub

    Private Sub cmdIrFinal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrFinal.Click
        Paginar(mintRegistrosPorPagina, mintTotalPaginas, mstrSql, mblnEjecutaSP)
        '[Llamada a nuestro procedimiento de Carga ]
    End Sub
End Class

Y aquí un esbozo en Visual Basic 6.0


Global dbDocumentos As rdoConnection

Public Sub Paginar(intNumeroRegistrosPagina As Integer, intNumeroPagina As Integer, strSql As String, blnEjecutaSP As Boolean)
Dim rdoQ As New rdoQuery
Dim i As Integer
Dim mintPaginaActual As Integer
Dim mintTotalPaginas As Integer
Dim mblnEjecutaSP As Boolean
Dim mintRegistrosPorPagina As Integer
Dim mstrSql As String
Dim rec As rdoResultset

rdoQ.ActiveConnection = dbDocumentos
rdoQ.sql = "{ call dbo.sp_glo_paginar(?,?,?) }"

rdoQ.rdoParameters(0) = intNumeroRegistrosPagina

rdoQ.rdoParameters(1) = intNumeroPagina

rdoQ.rdoParameters(2) = strSql

rec = rdoQ.OpenResultset
If Not rec.EOF Then


mintPaginaActual = intNumeroPagina

cmdIrPrincipio.Enabled = (mintPaginaActual <> 1)

cmdIrAtras.Enabled = (mintPaginaActual <> 1)
If mintTotalPaginas = 0 Then


mblnEjecutaSP = blnEjecutaSP
cmdIrPrincipio.Visible = True
cmdIrAtras.Visible = True
cmdIrAdelante.Visible = True
cmdIrFinal.Visible = True
cboIrPagina.Visible = True


cboIrPagina.Clear()

mintRegistrosPorPagina = intNumeroRegistrosPagina
mintTotalPaginas = Fix(rec!IntTotalRegistros / mintRegistrosPorPagina) + IIf(rec!IntTotalRegistros Mod mintRegistrosPorPagina <> 0, 1, 0)

mstrSql = strSql
For i = 1 To mintTotalPaginas


cboIrPagina.AddItem i
Next i

cboIrPagina.Enabled = (mintTotalPaginas > 1)
End If
lblPagina.Caption = "Pág " & mintPaginaActual & "/" & mintTotalPaginas

cmdIrAdelante.Enabled = (mintPaginaActual <> mintTotalPaginas)

cmdIrFinal.Enabled = (mintPaginaActual <> mintTotalPaginas)
Else
cmdIrAdelante.Enabled = False
cmdIrFinal.Enabled = False
cmdIrPrincipio.Enabled = False
cmdIrAtras.Enabled = False
End If

End Sub




No hay comentarios:

Publicar un comentario