Pasar parámetros opcionales a un procedimiento almacenado

En ocasiones se nos presenta situaciones en las que necesitamos pasar parámetros opcionales a un procedimiento almacenado. Vamos a tomar como ejemplo a éste post (Listar días de un rango de fechas) que publiqué hace un tiempo atrás,  como ven,  éste procedimiento almacenado tiene  parámetros con valor por defecto, es decir, cuando se ejecuta el procedimiento almacenado y no le asigno ningún valor entonces toma el valor por defecto.

¿Pero cómo le pasamos estos parámetros desde nuestro código C#?

La idea principal es que el usuario seleccione la fecha desde y fecha hasta (estos datos deben ser obligatorios), ahora bien, para demostrar el funcionamiento de este ejemplo vamos a permitir  que  el usuario seleccione uno o más días de la semana y también que no seleccione ninguno.

Éste es el código .aspx

    <asp:Label ID="lbFechaDesde" runat="server" ></asp:Label>
    <asp:TextBox  ID="txtFechaDesde" runat="server" ></asp:TextBox>
    <asp:Calendar ID="wmcFechaDesde" runat="server" Width="260px" Height="260px"
                            OnSelectionChanged="wmcFechaDesde_OnSelectedDateChanged" />

    <asp:Label ID="lbFechaHasta" runat="server" Text="Fecha Hasta" ></asp:Label>
    <asp:TextBox ID="txtFechaHasta" runat="server" ></asp:TextBox>
    <asp:Calendar ID="wmcFechaHasta" runat="server" Width="260px" Height="260px"
                            OnSelectionChanged="wmcFechaHasta_OnSelectedDateChanged" />

    <asp:CheckBox ID="cbLunes"       runat="server" Checked="false" Text="Lu" />
    <asp:CheckBox ID="cbMartes"      runat="server" Checked="false" Text="Ma" />
    <asp:CheckBox ID="cbMiercoles" runat="server" Checked="false" Text="Mi" />
    <asp:CheckBox ID="cbJueves"      runat="server" Checked="false" Text="Ju" />
    <asp:CheckBox ID="cbViernes"     runat="server" Checked="false" Text="Vi" />

El control Button tiene el evento OnClick que se lanza cuando el usuario produce un clic en el boton y ejecuta el código que ingresemos dentro de él.

    <asp:Button ID="btnVerFechas" runat="server" Text="Ver Fechas" Width="120px"
                        OnClick="OnClick_btnVerFechas" />

    <asp:Label ID="lbMostrarFechas" runat="server" ></asp:Label>

Y éste es el código .aspx.cs

        protected void OnClick_btnVerFechas(object sender, EventArgs e)
        {
                if (DesdeEsmenorHasta(txtFechaDesde.Text, txtFechaHasta.Text))
               {
                    /*En esta parte utilizo el operador condicional ?  que no es más que hacer lo siguiente
                    string lunes = "";
                    if (cbLunes.Checked)
                          lunes = "Lunes";
                    else
                          lunes = null; */
                     string lunes        = cbLunes.Checked       ? "Lunes"        : null;
                     string martes      = cbMartes.Checked      ? "Martes"      : null;
                     string miercoles  = cbMiercoles.Checked ? "Miercoles" : null;
                     string jueves      = cbJueves.Checked       ? "Jueves"      : null;
                     string viernes      = cbViernes.Checked     ? "Viernes"    : null;
                     /*Des esta forma sabemos si el usuario seleccionó los días de la semana*/

                     DateTime FechaDesde = Convert.ToDateTime(txtFechaDesde.Text);
                     DateTime FechaHasta = Convert.ToDateTime(txtFechaHasta.Text);

                     DataSet ds = new DataSet();
                   MiPrueba mp = new MiPrueba();
                     ds = mp.Fecha_RangoFechas(lunes, martes, miercoles, jueves, viernes,
                                                                  FechaDesde, FechaHasta);
                      if (ds != null)
                     {
                            if (ds.Tables.Count > 0)
                           {
                                  lbMostrarFechas.Text = "";
                                  foreach (DataTable dt in ds.Tables)
                                 {
                                         if (dt != null)
                                        {
                                               if (dt.Rows.Count > 0)
                                              {
                                                       lbMostrarFechas.Text = lbMostrarFechas.Text +
                                                  dt.Rows[j][0].ToString() + "</br>";
                                               }
                                         }
                                  }
                           }
                             else
{
                                 showMessage("No hay datos para mostrar.");
                                    return;
}
}
               }
else
{
                   showMessage("Fecha hasta debe ser mayor a fecha desde.");
                      return;
}
        }

        protected void wmcFechaDesde_OnSelectedDateChanged(object sender, EventArgs e)
        {
              txtFechaDesde.Text = wmcFechaDesde.SelectedDate.ToString("dd/MM/yyyy");                                     }

protected void wmcFechaHasta_OnSelectedDateChanged(object sender, EventArgs e)
        {
              if (txtFechaDesde.Text != "")
              {
                    txtFechaHasta.Text = wmcFechaHasta.SelectedDate.ToString("dd/MM/yyyy");
               }
               else
               {
                    showMessage("Seleccione fecha desde");
                    return;
               }
        }

   Método que verifica si la fecha hasta es menor a fecha desde
       private bool DesdeEsmenorHasta(string desde, string hasta)
        {
                DateTime date;
                if (!DateTime.TryParse(desde, out date) || !DateTime.TryParse(hasta, out date))
                       return false;

                DateTime dateDesde = DateTime.Parse(desde);
                DateTime dateHasta = DateTime.Parse(hasta);

                return dateDesde.Date <= dateHasta.Date;
        }


     

Generar Gridview de forma dinámica con el control Repeater

En este post voy a explicar cómo utilizar el control Repeater para la visualización de datos.

El control Repeater permite, como su nombre lo indica, repetir una plantilla compuesta por diversos controles un números de veces determinado de acuerdo a una estructura de datos que pueda proveer los datos necesarios para poder rellenar esos controles.

La base de datos que vamos a utilizar para este ejemplo contendrá una tabla Usuario con los siguientes campos.

       Id  |  Nombre    |  Apellido        |   Telefono          |  Direccion          
       1       Juan           Romero         1538486655         av. San Martin    
       2       Pedro         Gutierrez        1532145214         av. Santa Fé      
       3       Maria          Fernandez     1565425314         av. Cabildo        

La idea principal es generar Gridview a partir de la tabla Usuario, en este caso el resultado final será tres Gridview con los datos del usuario.

Inicialmente el control Repeater tiene la siguiente estructura:

          <asp:Repeater ID="rptUsuarios" runat="server">
          </asp:Repeater>

Al interior del mismo podemos introducir controles html, tablas, controles aspx, debemos tener en cuenta que estos controles se repetirán un números de veces igual al número de elementos del DataSource que le asignemos al Repeater.

El control Repeater hace uso de las siguientes plantillas:

• HeaderTemplate - El contenido de esta plantilla no se repetirá y se coloca en la sección posición más alta, es decir la cabeza del control Repeater.
• ItemTemplate - El contenido de esta plantilla se repetirá para cada registro presente en su origen de datos.
• AlternatingItemTemplate - se utiliza para añadir elementos alternos. Se utiliza junto con ItemTemplate.
• SeparatorTemplate - se utiliza para agregar un separador entre dos elementos del control del repetidor.
• FooterTemplate - El contenido de esta plantilla no se repetirá y se colocará en la sección de pie de página.

Para nuestro ejemplo vamos a insertar una tabla con un gridview, el código será el siguiente.

        <asp:Repeater ID="rptUsuarios" runat="server"                                                                                                               onitemdatabound="rptUsuarios_ItemDataBound">
               <ItemTemplate>
                       <table>
                             <tr>
                                    <td align="center">
                                              <asp:GridView runat="server" ID="gvUsuarios"
                                                                       AutoGenerateColumns="true">
                                               </asp:GridView>
                                   </td>
                           </tr>
                      </table>
              </ItemTemplate>
      </asp:Repeater>

El control Repeater tiene un evento "ItemDataBound" el cual se lanza al llamar al método DataBind().
La llamada al DataBind() lanzará un evento "ItemDataBound" por cada uno de sus elementos de su DataSource. El DataSource puede ser un DataTable, DataSet, ArrayList, etc. En nuestro caso el evento se lanzará tres veces, uno por cada elemento del "dtRepeater".

        protected void Page_Load(object sender, EventArgs e)
        {
               if (!IsPostBack)
               {
                      //El método Obtener_Usuario() simplemente obtiene los datos del usuario a través de                           //un  select a la tabla Usuario de la BD.
                      Usuario u = new Usuario();
                      dtRepeater = u.Obtener_Usuario();

                      if (dtRepeater.Rows.Count > 0)
                      {
                             rptUsuarios.DataSource = dtRepeater;
                             rptUsuarios.DataBind();
                      }
               }
        }

Además, el contenido del elemento del DataSource será accesible desde el evento "ItemDataBound" a través de la propiedad e.Item.

Dentro de este evento se realiza la asignación de los datos al Repeater, es decir la asignación de la propiedad DataBind del GridView, para hacer esto utilizamos el método FindControl para instanciar el objeto en función de la iteración.

        protected void rptUsuarios_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
                if (e.Item.ItemType == ListItemType.Item ||
                      e.Item.ItemType == ListItemType.AlternatingItem)
                {
                         int id = e.Item.ItemIndex;

                        GridView gv = (GridView)e.Item.FindControl("gvUsuarios");
                        if (gv != null)
                        {
                               gv.DataSource = GetUsuario(id);
                               gv.DataBind();
                        }
                }
        }

        private DataTable GetUsuario(int id)
        {
                DataTable dt = new DataTable();
                dt.Columns.Add("Id");
                dt.Columns.Add("Nombre");
                dt.Columns.Add("Apellido");
                dt.Columns.Add("Telefono");
                dt.Columns.Add("Direccion");
             
               DataRow dr = dt.NewRow();
               dr[0] = dtRepeater.Rows[id][0];
               dr[1] = dtRepeater.Rows[id][1];
               dr[2] = dtRepeater.Rows[id][2];
               dr[3] = dtRepeater.Rows[id][3];
               dr[4] = dtRepeater.Rows[id][4];
               dt.Rows.Add(dr);

              return dt;
       }

Este ViewState me permite almacenar y manipular los datos.

        private DataTable dtRepeater
        {
              get
               {
                       if (ViewState["dtRepeater"] != null)
                            return (DataTable)ViewState["dtRepeater"];
                       else
                            return null;
               }
              set
               {
                      if (ViewState["dtRepeater"] != null)
                           ViewState["dtRepeater"] = value;
                     else
                          ViewState.Add("dtRepeater", value);
               }
        }

Paginación en GridView

En ocasiones tenemos gran cantidad de datos en un GridView que necesitamos paginar y mostrar un conjunto de datos en cada página.
En este post voy explicar cómo mostrar GridView con paginación. Esto lo conseguimos si la propiedad AllowPaging lo ponemos en true, esta propiedad es muy pesado y consume una gran cantidad de memoria pero aporta mucha funcionalidad, también podemos indicarle el número de filas por medio de la propiedad PageSize, para establecer el estilo de paginación utilizamos la propiedad PagerSettings-Mode.

• Si no le indicamos la propiedad PageSize la grilla tendrá 10 filas por defecto en cada página y si hay más de 10 páginas se mostrará en puntos suspensivos (..) juntos a los números.
• Si no ponemos la propiedad PagerSettings-Mode, por defecto tendrá el estilo "Numeric".

El código aspx sería así

       <asp:GridView runat="server" ID="gvPaginacion"
                                AutoGenerateColumns="true"
                                AllowPaging="true" PageSize="15"
                                OnPageIndexChanging="gvPaginacion_OnPageIndexChanging" >
       </asp:GridView>


Con la propiedad AllowPaging en true ademas de mostrar los resultados en forma paginada también nos muestra el control de paginación.

        protected void Page_Load(object sender, EventArgs e)
        {
               if (!IsPostBack)
              {
                     CargarDatos();
              }
        }

El evento OnPageIndexChanging se ejecuta cada vez que pulsamos en uno de los link del control de paginación. 

Así pues, al indicarle el número de página a mostrar, en los argumentos del evento (del tipo GridViewPageEventArgs) recibimos el índice de la página y le asignamos al PageIndex del GridView. 

        protected void gvPaginacion_OnPageIndexChanging(object sender,
                                                                                              GridViewPageEventArgs e)
        {
                 gvPaginacion.PageIndex = e.NewPageIndex;
                 CargarDatos();
        }

        private void CargarDatos()
        {
                 //La entidad Usuario tiene atributos como Id, Nombre, Apellido, etc.
                 Usuario u = new Usuario();
                 //El método Listar_Usuarios() simplemente obtiene los datos del usuario a través de                             //un  select a la tabla Usuario de la BD.
                 DataTable dt = u.Listar_Usuarios();

                 if (dt != null)
                {
                       if (dt.Rows.Count > 0)
                      {
                             gvPaginacion.DataSource = dt;
                             gvPaginacion.DataBind();
                      }
                      else
                     {
                            gvPaginacion.DataSource = null;
                            gvPaginacion.DataBind();
                      }
               }
               else
               {
                     gvPaginacion.DataSource = null;
                     gvPaginacion.DataBind();
               }
        }

Y esto es todo, ustedes pueden probar cambiando el estilo de la propiedad PagerSettings-Mode los estilos son:

1. NumericFirstLast
2. Numeric
3. NextPrevious
4. NextPreviousFirstLast

Query que verifica si un usuario tiene permiso para ingresar a la aplicación

La base de datos que vamos a utilizar para este ejemplo contendrá una tabla Usuario con los siguientes campos.

       Id  |  Nombre  |  Apellido   |   useNombre     |  Clave             |  Activo
       1       Juan           Romero         jromero            jromero123            1
       2       Pedro         Gutierrez       pgutierrez        gjutierrez123          0

En la tabla usuario tenemos que asegurarnos que el campo useNombre  sea un valor único e irrepetible, es decir que no podemos tener dos o más usuarios con el mismo useNombre. Este paso, aunque importante, no lo vamos a ver, pues sólo nos vamos a centrar en verificar si un usuario tiene permiso para ingresar a la aplicación.

Este es el procedimiento almacenado.

CREATE PROCEDURE Autenticacion_Usuario
@useNombre varchar(30),
@Clave          varchar(30)
as
begin
declare @Encontrado int
set @Encontrado = 0
   /*Paso 1*/
if ((select COUNT(*) from Usuario
                                      where useNombre = @useNombre) < 1)
begin
select 1 as [Id], 'El usuario no existe' as [Descripcion]
end
else
begin
             /*Paso 2*/
if ((select COUNT(*) from Usuario
                                             where useNombre = @useNombre
                                                 and Clave          = @Clave) < 1)
begin
select 2 as [Id], 'Su clave no es correcta' as [Descripcion]
end
else
begin
                     /*Paso 3*/
if ((select COUNT(*) from Usuario
                                                     where useNombre = @useNombre
                                                         and Clave          = @Clave
                                                         and Activo        = 1) < 1)
begin
select 3 as [Id], 'Usuario dado de baja' as [Descripcion]
end
else
begin
set @Encontrado = 1
select 4 as [Id], 'Usuario encontrado' as [Descripcion]
end
end
end
if (@Encontrado = 1)
begin
select u.Id
                ,u.Nombre
     ,u.Apellido
    from Usuario u
where u.useNombre = @useNombre
           and u.Clave          = @Clave
           and u.Activo         = 1
end
end

Es muy sencillo lo que hacemos, si la sentencia select del paso 3 nos devuelve 1 significa que los datos del usuario están registrados en la base de datos.

Ahora Probemos

--dbo.Autenticacion_Usuario 'jromero', 'jromero1er23'
Resultado 1
Id  |   Descripcion
--    -----------------------------
2       Su clave no es correcta

--dbo.Autenticacion_Usuario 'jromero', 'jromero123'
Resultado 2
Id  |   Descripcion
--      -------------------------
4       Usuario Encontrado
======================
Id  |  Nombre  |  Apellido
--    ------------  ------------
1       Juan          Romero

Este resultado debemos obtener en un dataset sea el lenguaje que utilicemos, esto porque, como vimos el Resultado 2 nos devuelve dos tablas.

Asignar el resultado de una consulta a una variable

Voy a poner como ejemplo una tabla Usuario con los siguientes campos.
       Id  |  Nombre  |  Apellido
       1       Juan           Romero
       2       Pedro         Gutierrez 

Dentro de un procedimiento o función a veces necesitamos almacenar el resultado de una consulta, existen dos formas de hacerlo.

1. Asignar el resultado a la variable directamente dentro de la consulta.

        

          declare @var int

          set @var = 0

          select top 1 @var = Id from Usuario where Nombre = 'Juan'

          select  @var as 'Id'

         El resultado es:
              Id
              1

     2. Utilizar la instrucción SET 

        

         

         declare @var int

         set @var = 0

         set @var = (select top 1 Id from Usuario where Nombre = ' Pedro ')

         select  @var as 'Id'

         El resultado es:
              Id
              2

Hasta aquí todo bien, pero el set y select tienen diferentes formas de comportamiento en lo que se refiere al valor de la variable si la consulta no devuelve ningún resultado.
Utilicemos el mismo ejemplo.

          

         declare @var int

          set @var = 0

          select top 1 @var = Id from Usuario where Nombre = 'Maria'

          select  @var as 'Id'

          El resultado es:
              Id
              0
       
Esto significa que la variable conserva el valor que se le asignó antes de usarla, ahora veamos el otro ejemplo:
         

         declare @var int

         set @var = 0

         set @var = (select top 1 Id from Usuario where Nombre = ' Maria')

         select  @var as 'Id'

         El resultado es:
              Id
              NULL

Como la consulta no devuelve ningún valor, SQL le cambia el valor de la variable a NULL.
     
Es importante conocer esto, ya que nos ayudará a impedir posibles errores. Pero como evitamos el NULL cada vez que nuestra consulta no devuelva ningún valor.? para esto vamos a utilizar la instrucción ISNULL
      

        declare @var int

         set @var = 0

         set @var = (select top 1 Id from Usuario where Nombre = ' Maria')

         select  ISNULL(@var, 0) as 'Id'

         El resultado es:
              Id
              0

Listar fechas de un rango en Sql

Hace poco realicé un procedimiento almacenado (para una aplicación que estaba desarrollando) que recibía como parámetros los días de la semana y un rango de fecha, como resultado me devolvía las o la fecha de los días que le pasaba como parámetro. Por ejemplo:

Rango de fechas: 12/06/2016 al 23/06/2016

Días:  Lunes, Miércoles

Entonces el resultado sería todos las fecha de los días lunes y miércoles que se encuentran dentro del rango de fecha.

Resultado: 13/06/2016 - 15/06/2016 - 20/06/2016 - 22/06/2016

Para este ejemplo vamos a hacer uso de varias funciones  por ejemplo DateDiff, DateAdd y DateName.

Procedimiento

• Obtener la cantidad de días que hay dentro del rango de fechas.
• Dentro del ciclo while iterar hasta que se cumpla la condición.

Ahora les comparto el query

create procedure Fecha_RangoFechas
@lunes       varchar(20) = null,
@martes      varchar(20) = null,
@miercoles   varchar(20) = null,
@jueves      varchar(20) = null,
@viernes     varchar(20) = null,
@fecha_desde datetime,
@fecha_hasta datetime
as
begin

    declare @cantdias  int
           ,@i         int
           ,@fecha     datetime
           ,@dia       varchar(20)

    set @cantdias = datediff (day, @fecha_desde, @fecha_hasta)
    set @i = 0

    while @i <= @cantdias
    begin
          set @fecha = DATEADD(day, @i, @fecha_desde)
          set @dia   = DATENAME(WEEKDAY, @fecha)

          if (@lunes is not null  and @lunes = @dia )
                 select convert(varchar(20), @fecha, 103)

          if (@martes is not null  and @martes = @dia )
                select convert(varchar(20), @fecha, 103)

          if (@miercoles is not null  and @miercoles = @dia )
                select convert(varchar(20), @fecha, 103)

           if (@jueves is not null  and @jueves = @dia )
                 select convert(varchar(20), @fecha, 103)

           if (@viernes is not null  and @viernes = @dia )
                 select convert(varchar(20), @fecha, 103)

           set @i = @i + 1
     end
end

/*Ahora probemos*/

--[dbo].[Fecha_RangoFechas] 'Lunes', null, 'Miércoles', null, null, '12/06/2016', '23/06/2016'

--[dbo].[Fecha_RangoFechas] 'Lunes', 'Martes', 'Miércoles', null, null, '12/06/2016', '23/06/2016'