Colabora.NET: Paginado en Oracle.

Paginado en Oracle

Fecha: 08/Jun/2005 (08-06-2005)
Autor: Bruno Capuano ([email protected])

Introduccion

El objetivo de este documento es demostrar uno de los métodos de paginado que se pueden aplicar para que el trabajo de ordenamiento y selección de datos lo realice la base de datos

Base de Datos

Para el ejemplo de paginado, se trabajara con una Tabla con datos y un Procedimiento almacenado para acceder a los mismos. En este caso en particular se ha decidido trabajar sobre una tabla de ejemplo, con la siguiente estructura.

La misma posee 3 campos y el campo que definirá los criterios de búsqueda es DATO_ID. El script de la creación de la misma es el siguiente

/* Tabla de ejemplo para el paginado. */

create table PAGINADODATOS

(

DATO_ID NUMBER not null,

DATO_DESC VARCHAR2(20),

DATO_FECHA DATE

)

Nota: Para poder trabajar con esta tabla, se ha ejecutado un proceso que cargo automáticamente 100000 registros.

Luego hemos creado un procedimiento ObtenerDatosPaginados, que retorna un set de datos desde la tabla PAGINADO y aplica un filtro sobre la misma. Recibe 4 parámetros que son los que definen el criterio de paginado.

Filas, determina la cantidad de filas que se retornaran.
Índice, determina el filtro sobre el campo DATO_ID para localizar el puntero al set de datos.
Dirección, determina la dirección del filtro, ascendente o descendente.
Resultado, Oracle RefCursor donde se retornara el set de datos.

Dentro del procedimiento, se diferencia primero por el tipo de dirección que se solicita. Luego, para retornar el set de datos, se ejecuta una consulta Sql, con una subconsulta que filtra primero sobre el DATO_ID y retorna como un campo más el ROWID. La consulta superior, utiliza este ROWID, para filtrar la cantidad de campos que se retornaran.

Si la consulta es descendente, el criterio es el mismo, pero se hace una nueva subconsulta para ordenar el set de datos.

El script de creación del procedimiento es el siguiente

procedure ObtenerDatosPaginados(filas in NUMBER, indice in NUMBER, dirección in NUMBER,resultado out pqtTipos.typCursorRetorno) is

begin

    if (direccion = 0) then

       -- filtra por el campo DATO_ID y luego solo retorna la cantidad de filas solicitada

       open resultado for

          SELECT DATO_ID, DATO_DESC, DATO_FECHA

          FROM (SELECT ROWNUM AS R, DATO_ID, DATO_DESC, DATO_FECHA

                                 FROM PAGINADODATOS

                                 WHERE (DATO_ID > indice)) P

          WHERE (R <= filas)

          ORDER BY DATO_ID;

    else

      -- filtra por el campo DATO_ID y luego retorna la cantidad de filas solicitada

      -- pero ordena descendetemente para obtener la pagina anterior

           open resultado for

             SELECT DATO_ID, DATO_DESC, DATO_FECHA

              FROM (SELECT ROWNUM AS R, DATO_ID, DATO_DESC, DATO_FECHA

                                     FROM (SELECT DATO_ID, DATO_DESC, DATO_FECHA

                                           FROM PAGINADODATOS

                                           WHERE (DATO_ID < indice)

                                           ORDER BY DATO_ID DESC)P

                                     ) P

              WHERE (R <= filas)

              ORDER BY DATO_ID;

    end if;

end ObtenerDatosPaginados;

Visual Basic .Net

Luego cuando queremos invocarlo desde Visual Basic .Net, es necesario utilizar el componente de acceso a datos para Oracle. Para ello agregamos la referencia a nuestro proyecto de System.Data.OracleClient.dll y escribimos codigo de acceso a datos. O mejor aun, utilizando Enterprise Library 1.0, resumimos mucho mas aun la funcion. No vamos a entrar en detalle sobre la utilizacion de Enterprise Library, ya que en su website hay bastante documentacion sobre la misma.

Public Function ObtenerDatosPaginados(ByVal indice As Integer, ByVal filas As Integer, ByVal ascendente As Boolean) As DataSet

    Dim iDir As Integer = 0

    Dim db As Database = DatabaseFactory.CreateDatabase

    If Not ascendente Then iDir = 1

    Return db.ExecuteDataSet("ObtenerDatosPaginados", indice, filas, iDir, Nothing)

End Function

Notese que se cambia la direccion de un bool a un Number, como esta definido en el procedimiento y que ademas se le pasa un ultimo parametro nulo, para que retorne el cursor de datos.

Basados en un formulario con el siguiente diseño

El codigo correspondiente a cada uno de los botones dependera de los indices que manejemos en cuestion. Por ejemplo, para el next podemos definir

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

    ds = ObtenerDatosPaginados(iFilas, iLast, True)

    DataGrid1.DataSource = ds.Tables(0)

    iFirst = ds.Tables(0).Rows(0)("DATO_ID")

    iLast = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)("DATO_ID")

End Sub

El codigo completo de la aplicacion quedaria de la siguiente manera.

Dim ds As DataSet

Dim iFilas As Integer

Dim iFirst As Integer = 0

Dim iLast As Integer

Public Function ObtenerDatosPaginados(ByVal filas As Integer, ByVal indice As Integer, ByVal ascendente As Boolean) As DataSet

    Dim iDir As Integer = 0

    Dim db As Database = DatabaseFactory.CreateDatabase

    If Not ascendente Then iDir = 1

    Return db.ExecuteDataSet("ObtenerDatosPaginados", filas, indice, iDir, Nothing)

End Function

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    iFilas = 10

    Call btnNext_Click(Nothing, Nothing)

End Sub

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

    ds = ObtenerDatosPaginados(iFilas, 0, True)

    DataGrid1.DataSource = ds.Tables(0)

    iFirst = ds.Tables(0).Rows(0)("DATO_ID")

    iLast = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)("DATO_ID")

End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

    ds = ObtenerDatosPaginados(iFilas, iLast, True)

    DataGrid1.DataSource = ds.Tables(0)

    iFirst = ds.Tables(0).Rows(0)("DATO_ID")

    iLast = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)("DATO_ID")

End Sub

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

    ds = ObtenerDatosPaginados(iFilas, 999999, True)

    DataGrid1.DataSource = ds.Tables(0)

    iFirst = ds.Tables(0).Rows(0)("DATO_ID")

    iLast = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)("DATO_ID")

End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

    ds = ObtenerDatosPaginados(iFilas, iLast, False)

    DataGrid1.DataSource = ds.Tables(0)

    iFirst = ds.Tables(0).Rows(0)("DATO_ID")

    iLast = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)("DATO_ID")

End Sub

El punto importante a tener en cuenta es la utilizacion de ROWNUM dentro de la consulta de Oracle. Si utilizamos Sql Server con la instruccion TOP, podemos obtener el mismo resultado. De esta manera, delegamos el trabajo de ordenado y seleccion a la base de datos.

Ante cualquier duda, pueden escribirme a [email protected]

Saludos

El Bruno

http://spaces.msn.com/members/brunocapuano.