Fran Otero

Las CTE o Common Table Expression son expresiones que facilitan la labor de escribir consultas complicadas, permitiendo definir subconsultas previas como tablas que se utilizarán en la consulta. Suena complicado, y realmente a veces lo es: las CTE pueden incluso ser recursivas y hacer consultas que ni te imaginabas que se pudiesen conseguir en una sola consulta (queda pendiente para otro post). Pero conocer y saber utilizar CTE básicas nos puede ayudar a mejorar nuestras consultas de forma muy sencilla. Ese es el caso de este ejemplo.

En la base de datos AdventureWorks existe una tabla con históricos de precios de productos.

Que da como resultado una tabla como esta:

 Queremos hacer una consulta que nos devuelva el primer precio que tuvo asignado cada producto, es decir, para el producto 707 tendría que devolver la prima fila, que se corresponde con el año 2001 y un precio de 12,0278. Lo primero que se nos puede venir a la cabeza será un GROUP BY, pero os adelanto que no va a salir bien :-). Mediante una agrupación podríamos saber la primera fecha para cada productId, pero: ¿y el precio? Tendríamos que hacer un InnerJoin posterior que utilizase este resultado: se complica mucho más si metemos más campos debido a que en el JOIN vamos a tener que igualar todos los campos para asegurarnos de que estamos seleccionando la misma fila.

La solución con CTE quedaría así:

Como ves la CTE crea una tabla previa, en este caso llamada «Ampliada» que podemos utilizar en la consulta siguiente (realmente es la misma consulta). Aquí el único truco es el campo índice que está construído a partir de la función Rank().

 

Creo que el título no puede ser más descriptivo: un artículo publicado esta semana en ScribD en el que comento algunas técnicas útiles para análisis bursátil utilizando consultas de Sql Server. Únicamente con consultas de Sql Server conseguimos detectar patrones de velas japonesas que indican comienzos o finales de tendencias, proporcionando un apoyo a la decisión de inversión.

 

Publico en la zona de medios la base de datos real que utilizo en el artículo.

Para los que les haya gustado mi anterior artículo en el cual substituiamos el uso de cursores por un bucle While, aquí está la continuación DEFINITIVA. Supongo que muchos pensareis que no ganamos mucho substituyendo una iteración por la otra, y en parte tenéis razón. No obstante la no utilización de cursores tiene beneficios intrínsecos que van más allá del propio rendimiento de la consulta, como podría ser la seguridad ante fallos de que no tendremos objetos instanciados en memoria.

De todas formas viendo el código anterior uno se queda con la sensación de que es un poco rudimentario. Hay otras alternativasparecidas, como puede ser incluir en la tabla temporal una columna IDENTITY que nos permita iterar la tabla en el While a través de una variable puntero que iremos incrementando en el propio While: más de lo mismo. Ahora bien, que os parecería si os digo que se puede substituir todo el bucle, el recorrido de filas, la comparación e incluso la tabla temporal por una única instrucción SELECT «corriente». Lo de corriente tiene un poco de trampa, pero nada de complicación: si utilizamos una instrucción select de asignación, la asignación se repetirá para cada fila de la consulta. Si además introducimos una expresión de tipo «case … then …. else ….. end» podemos en cada fila comparar el valor máximo con el que llevamos agregado y asignar al máximo el mayor de ellos.

— Creamos la tabla del ejemplo. Para simplificar introducimos solo los

–datos que utilizaremos, pero esto no afecta al ejemplo

declare @movimientos as table (Producto nvarchar(10), Cantidad int, Fecha date)

insert into @movimientos values (‘tornillos’,500,‘20090101’)

insert into @movimientos values (‘tornillos’,-200,‘20090104’)

insert into @movimientos values (‘tornillos’,-50,‘20090102’)

insert into @movimientos values (‘tornillos’,-220,‘20090110’)

insert into @movimientos values (‘tornillos’,500,‘20090111’)

insert into @movimientos values (‘tornillos’,-100,‘20090120’)

–Variables auxiliares que utilizaremos

declare @Acc int,@Max

int set @Acc=0 –Valor acumulado hasta la fila actual

set @Max=0 –Valor máximo hasta la fila actual

— Consulta «importante»

select

@Acc=@Acc+Cantidad, –Valor acumulado hasta la fila actual

@Max=case sign(@Acc–@Max) when 1 then @Acc else @Max end –Asigna el nuevo máximo o se queda con el que había

From @movimientos

Where Producto= ‘tornillos’

orderby fecha asc

print ‘Máximo alcanzado:’

print @Max

 

— Héchale una pensada, puede ser muy útil !!!!

 

 

 

Tenemos una tabla en nuestra base de datos llamada “Movimientos”, que registra entradas y salidas de material de nuestro almacén. La siguiente consulta es el resultado de consultar los movimientos de “tornillos” en el mes de enero. Cómo se puede ver la tabla carece de un autonumérico, los registros no tienen porqué estar en orden cronológico y hay más productos mezclados. 

tornillos	500	01/01/2009
tornillos	-50	02/01/2009
tornillos	-200	04/01/2009
tornillos	-220	10/01/2009
tornillos	500	11/01/2009
tornillos	-100	20/01/2009

Si en este escenario nos pidiesen obtener la cantidad máxima de tornillos que llegó a haber en el almacén podrían ponernos en un pequeño apuro. La idea sería conseguir una tercera columna con la cantidad en stock y poder seleccionar el máximo. El problema de eso es que necesitamos de alguna manera recorrer la tabla fila a fila, pero claro, dicen por ahí que eso de los cursores es cosa mala….

tornillos	500	01/01/2009	500
tornillos	-50	02/01/2009	450
tornillos	-200	04/01/2009	250
tornillos	-220	10/01/2009	30
tornillos	500	11/01/2009	530
tornillos	-100	20/01/2009	430

He aquí un método alternativo a la utilización de los cursores para obtener la columna “agregado” y poder seleccionar el máximo stock, que tiene lugar el 11 de enero con un valor de 530 tornillos.

1º) Creamos una tabla temporal con los registros que nos interesan, ordenándolos por orden cronológico

2º) Recorremos un bucle que analice la primera fila y la eliminamos, de esta forma podemos recorrer la tabla completa.

Veámoslo en el ejemplo:

–Creamos la tabla del ejemplo. Para simplificar introducimos solo los

–datos que utilizaremos, pero esto no afecta al ejemplo

declare @movimientos as table

(Producto nvarchar(10), Cantidad int, Fecha date)

insert into @movimientos values (‘tornillos’,500,‘20090101’)

insert into @movimientos values (‘tornillos’,-200,‘20090104’)

insert into @movimientos values (‘tornillos’,-50,‘20090102’)

insert into @movimientos values (‘tornillos’,-220,‘20090110’)

insert into @movimientos values (‘tornillos’,500,‘20090111’)

insert into @movimientos values (‘tornillos’,-100,‘20090120’)

 

–Definimos una tabla temporal, con la misma estructura que la original

declare @tmp as table

(Producto nvarchar(10), Cantidad int, Fecha date)

 

–Introducimos aquí los datos que nos interesen, ordenados convenientemente

insert into @tmp

      select * from @movimientos

      where Producto=‘tornillos’

      order by fecha asc

 

–Variables auxiliares que utilizaremos

declare @Acc int,@Max int

set @Acc=0 –Valor acumulado hasta la fila actual

set @Max=0 –Valor máximo hasta la fila actual

 

–Esta instrucción elimina de la ventana de mensajes el recuento de filas afectadas

set nocount on

 

–Mientras exista alguna fila en @tmp recorro el bucle

While(Exists(select * from @tmp))

      BEGIN

            –Sumamos la 1ªfila al acumulado

            select top(1) @Acc=@Acc+Cantidad from @tmp;

            –Eliminamos la 1ª fila; ya está computada

            delete top(1) from @tmp;

            –Comprobamos si el acumulado es máximo

            if(@Acc>@Max)

                  set @Max=@Acc;

           

            print @Acc;

      END

     

print »

print ‘Máximo alcanzado:’

print @Max

 

Lo que nos devuelve por pantalla la columna de acumulados y el máximo stock que llegó a haber en el almacén:

500

450

250

30

530

430

 

Máximo alcanzado:

530

 

La pega de este método son la cantidad de deletes que utiliza, así que en el siguiente post tocará un método alternativo (sin cursores, of course) más eficaz.

 

A vueltas con esto de hacer una aplicación accesible y estándar con ASP.NET me encontré con un detalle aparentemente trivial: cómo generar un formulario estándar. Vale, utilizando un control de datos el problema «se resuelve solo», pero si pienso en un par de etiquetas con cuadros de texto para recolectar datos de forma sencilla supongo que todo el mundo pensaría en algo como esto:

<asp:label runat=»server» Text=»Nombre: » > <asp:textbox runat=»server»><br/>

…etc,etc

Lo cual nos genará un aparente formulario, pero cuyos textos están en un span. ¿Qué implicaciones tiene esto? Pues por ejemplo, que el span es un elemento inline por defecto, y nos costará un poquito más formatearlo con css, a parte de que los textos no estarán asociados a los cuadros de texto correspondientes con lo cual la accesibilidad se verá mermada. La primera solución que se nos ocurre supongo que será, ¿porqué un control de asp.net para la etiqueta? Es cierto, si substituyo el control asp.label por un label html normal con el atributo «for» establecido al id del cuadro de texto asunto resuelto!.

¿Pero qué ocurre si estas etiquetas han de ser accedidas desde servidor? ¿O simplemente si la página ha de ser localizable? Hay solución para todo, pero lo cómodo sería poder utilizar el código mostrado al principio indicándole que se renderizase como <label> en lugar de hacerlo como <span> y se estableciese el atributo «for» al id que se le asigne al control <input> que generará el control textbox. Pues bien, eso es exactamente lo que se consigue con el atributo AssociatedControlId:

<asp:label runat=»server» Text=»Nombre: » AssociatedControlId=»tbxNombre» > <asp:textbox runat=»server» ID=»tbxNombre»><br/>

 

 Sencillo, pero hay que saberlo!!!

 

Otra de esas pequeñas cosas que nos puede hacer perder el tiempo. Queremos cargar un Datalist con una lista de items, en el caso del ejemplo imágenes, y hacer que una de ellas aparezca seleccionada. Si recorremos los métodos y propiedades disponibles en este control, nos daremos cuenta de que la única forma de seleccionar por código un elemento es estableciendo la propiedad SelectedIndex. Existe la propiedad SelectedValue, pero es únicamente de lectura. Por desgracia, el índice no suele ser lo más recomendable para identificar a un elemento, así que en este ejemplo vamos a ver cómo hacer que este mismo control permita establecer su SelectedValue.

Se trata de un método extensor que recorrerá los elementos de la colección buscando coincidencias de clave, y una vez encontrada selecciona el elemento por su índice. Fijaos en que la dificultad reside en que la lista de claves no está indexada, con lo cual la tenemos que recorrer en un foreach y llevar la cuenta del índice «a pelo».

  public static void SetSelectedValue(this DataList dataList,String value)
    {
        if (dataList.Items.Count > 0)
            {
                int i = 0;
                foreach (Object key in dataList.DataKeys)
                {//Recorremos las claves, llevando el índice en la vble i
                    if (value ==  key.ToString())
                    {//Elemento a seleccionar, fin del método
                        dataList.SelectedIndex = i;
                        break;
                    }
                    else
                        i++;
                }
            }
    }

 

Como siempre, espero que a alguien más le resulte útil 😉

 

Sabido es que ciertos controles no funcionan correctamente dentro de un UpdatePanel. Es el caso de los Treeview, menú, gridview o detailsView entre otros. Algunos de estos no funcionan bajo determinadas circunstancias, o simplemente quedan totalmente invalidados. A este último grupo pertenece el control FileUpload, que nos  permite subir archivos al servidor.

Buscando por ahí, se encuentran varias soluciones, (destacaría la de Isaac Fernández en este mismo site), la mayoría enfocadas a la situación sencilla de que el control fileUpload esté situado directamente dentro del update panel. En este caso basta con agregar a la lista de triggers del UpdatePanel un nuevo PostBackTrigger con el ControlId apuntando al botón de subir archivo de nuestro FileUpload.

El problema de esta solución viene cuando la arquitectura se complica y tenemos, por ejemplo, el fileUpload dentro de un control de usuario propio, que a su vez será utilizado en páginas con varias MasterPage anidadas y el UpdateManager se encuentra en la primera de dichas MasterPages (suena rebuscado así contado, pero no estoy inventando nada :-)) Está claro que no podemos referirnos directamente desde el ASCX al UpdateManager (de forma sencilla, se entiende) ni viceversa. En este caso la solución sería tan simple como crear el PostBackTrigger referido a nuestro botón desde código, para lo cual necesitamos obtener el ScriptManager que se está utilizando en la página actual. El siguiente código muestra cómo hacerlo:

  

    protected void
Page_Load(object sender, EventArgs e)

    {

        ScriptManager scripManager = ScriptManager.GetCurrent(this.Page);

        scripManager.RegisterPostBackControl(lnkNuevaImagen);

    }

 

 

Asunto resuelto, nos cargamos la funcionalidad asíncrona para este botón, pero aseguramos que funciona con sólo incluir dos líneas de código fácilmente entendibles.

Espero que a alguien más le sirva!

A veces uno se encuentra con pequeños problemas que deberían de ser sencillos. Este fue el caso de cuando me propuse realizar una interfaz para una aplicación windows donde el usuario pudiese elegir cual era su localización en un mapa de google maps y calcular la distancia a uno de nuestros almacenes. La opción buena sería utilizar la api de google maps para estas tareas, pero quiero algo más sencillo, que funcione sin tener que instalar nada a mayores.

Primer inconveniente, ¿de donde saco las coordenadas del usuario? Googleando un poco encontré un sencillo script que comparten muchos sitios. Basta con introducir en la barra de direcciones el script java_script:void(prompt(»,gApplication.getMap().getCenter()))  y nos aparecerá un mensaje con la latitud y longitud.

Segundo problema, ¿como recojo esto en mi código? Bueno, pues básicamente el truco está en utilizar ese resultado no para sacarlo en un prompt, si no para redirijir la página del webbrowser en donde está cargado google maps. De esta forma en el evento navigating puedo analizar la dirección a la que intento navegar, cancelarla y conseguir las coordenadas. 

El siguiente ejemplo   hace precisamente eso, en un formulario donde unicamente se ha agregado un webBrowser (webBrowser1) y un botón (button1):

        private void Form2_Load(object sender, EventArgs e)

        {

            webBrowser1.Url = new Uri(«http://maps.google.es»);

        }

        private void button1_Click(object sender, EventArgs e)

        {

            webBrowser1.Navigate(«java_script:navigate(‘» + clave + «‘+gApplication.getMap().getCenter())»);

        }

 

        private void webBrowser1_Navigating(object sender, WebBrowserNavigatingEventArgs e)

        {

            string aux = e.Url.ToString();

 

            if (aux.StartsWith(«http://maps.google.es/»+clave))

            {//Navegación iniciada por nuestro código javascript               

                PointF direccion;

                aux = aux.Substring((«http://maps.google.es/»+clave).Length);//Eliminamos la url incial, quedándonos solo con el punto

 

                aux= aux.Replace(«(«, «»).Replace(«)»,«»); //Eliminamos los paréntesis

                string[] punto=aux.Split(‘,’);//Dividimos el texto en las dos coordenadas

                direccion = new PointF(                

                    float.Parse(punto[0].Replace(«.», «,»)),

                    float.Parse(punto[1].Replace(«.», «,»))); //Parseamos las dos coordenadas al punto resultado

 

                e.Cancel = true; //Cancelamos la navegación, la página resultante no es válida.

 

                //Ya tenemos el punto, podemos hacer lo que queramos con él!!! 

                MessageBox.Show(direccion.ToString());

            }

        }

 Para completar el ejemplo falta la parte de ¿que hago ahora con el punto? y ¿como encuentro la distancia a mis almacenes registrados en Sql Server?.

Queda pendiente para el siguiente post…

 Nota: substituir java_script por el equivalente sin guión bajo para que funcione el código.

El ControlState es un mecanismo alternativo al ViewState para guardar el estado de sesión en un control de usuario. Como es bien conocido disponemos múltiples objectos para guardar variables de sesión, todos ellos con una utilización parecida (básicamente son colecciones clave-valor de tipo string-object), que nos permiten mantener el estado entre llamadas en distintos ámbitos. El ámbito del controlState es pareceido al ViewState, con la diferencia fundamental de que éste último puede desactivarse. Éste es el motivo por el cual datos críticos para el buen funcionamiento del control deberían de guardarse en el ControlState (Ver Artículo MSDN) . Esta semana he constatado que su especial funcionamiento constituye una barrera de entrada para su utilización en programadores poco iniciados. Por este motivo me he decidido a publicar este post, con un pequeño código que, insertado en nuestro control, hará posible la utilización del ControlState de forma análoga al objeto Cache, ViewState, Session, etc.

    protected Dictionary<string,object> ControlState;

 

    //Guarda el diccionario personalizado y lo que

    //tuviese que guardar en el método base

    protected override object SaveControlState()

    {

        return new Pair(base.SaveControlState(), ControlState);

    }

 

    //Recupera por separado el diccionario personalizado

    //y el objeto para el método original

    protected override void LoadControlState(object savedState)

    {

        if (savedState != null)

        {

            Pair par=(Pair)savedState;

            base.LoadControlState(par.First);

            if (par.Second != null)

            {

                ControlState = (Dictionary<string, Object>)par.Second;

            }

        }

       if(ControlState==null)

           ControlState=new Dictionary<string,object>();

    }

 

Y para que todo esto funcione solo nos resta añadir en el evento load de la página la siguiente línea:

        Page.RegisterRequiresControlState(this);

 

Tenemos nuestro nuevo proyecto preparado para el despliegue. Subimos nuestro sitio web al servidor de producción, ejecutamos el script de creación de la base de datos, configuramos la aplicación y vamos a probar y ….. no funciona grrrr#**&*!!!! ¡A cuántos nos sonará este tema! Lo cierto es que es posible ( e incluso probable) que con el cambio de entorno tengamos que hacer unos cuantos retoques en nuestra aplicación, realizar unas cuantas pruebas, etc. Despues de esto nuestra recién creada base de datos estará sucia y posiblemente queramos volver a inicializarla.

Por diversos motivos puede que no nos interese cargarnos la base de datos otra vez, con lo cual intentaremos unicamente borrar las tablas (puede que ni siquiera tengamos permisos para borrar la base y volverla a crear, de ahí el interes de este método). Podemos utilizar un procedimiento que recorra las tablas listadas en INFORMATION_SCHEMA.TABLES e vaya ejecutando un delete sobre ellas. Con este método únicamente borraremos parte de las tablas, ya que tenemos altas probabilidades de que muchas de las filas se queden bloqueadas por relaciones foreign key al ejecutar el barrido con un orden arbitrario (el de INFORMATION_SCHEMA.TABLES). Además tenemos el problema añadido de que los índices de las tablas no se reinicializan ya que debido otra vez a las foreign keys no podemos utilizar Truncate en lugar de Delete.

El código listado a continuación viene a solucinonar la problemática descrita, borrando todas las tablas de la base de datos independientemente de sus relaciones y reinicializando los índices a 0 en las tablas que dispongan de este tipo de campos. Nótese que el código hace incidencia especialmente en lo funcional, obviando consideraciones que nos distaigan del objetivo principal

            

            //Definimos la conexión a la base

            SqlConnection cn = new SqlConnection(«ConnectionString»);

            //Definimos un comando auxiliar

            SqlCommand aux = new SqlCommand(«», cn);

            //Por último un comando auxiliar que carga las tablas de la base de datos.

            SqlCommand select = new SqlCommand(@»select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE like ‘BASE TABLE'», cn);

            SqlDataAdapter selectDA = new SqlDataAdapter(select);

            DataTable tablas;

            selectDA.Fill(tablas = new DataTable());

            cn.Open();

            bool repetir;

            do

            {

                repetir=false;

                foreach (DataRow tabla in tablas.Rows)

                {//Borramos cada una de las tablas

                    aux.CommandText = «Delete « + tabla[«TABLE_NAME»].ToString();

                    try

                    {

                        aux.ExecuteNonQuery();

                    }

                    catch (SqlException ex)

                    {

                                               if(ex.Number==547) //Infracción de FK: hay que repetir el borrado

                            repetir=true;

                    }

                }

            } while (repetir);

            foreach (DataRow tabla in tablas.Rows)

            {                   

                aux.CommandText = «DBCC CHECKIDENT (« + tabla[«TABLE_NAME»] + «,RESEED,0)»;

                try

                {

                    aux.ExecuteNonQuery();

                }

                catch

                {}//No todas las tablas tienen columnas de identidad para regenerar

            }

            cn.Close();