Búsqueda dinámica en SQL Server con EntityLite

A mi siempre me ha gustado mucho el famoso artículo  Dynamic search conditions
in T-SQL
de Erland Sommarskog. De hecho, he hecho referecia a él en algunas
de mis respuestas en los foros de SQL Server.

Sin embargo, en este artículo voy a implementar la búsqueda dinámica para el
mismo caso de studio con EntityLite.

Primero se crea la siguiente vista:

CREATE VIEW [dbo].[OrderDetail_Extended]
AS
	SELECT
		O.OrderDate, O.CustomerID,
		C.CompanyName AS CustomerName, C.[Address], C.City, C.Region, C.PostalCode, C.Country, C.Phone,
		OD.[OrderDetailID], OD.[OrderID], OD.[ProductID], OD.[UnitPrice], OD.Quantity, OD.Discount,
		(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS SubTotal,
		P.ProductName, P.UnitsInStock, P.UnitsOnOrder,
		 Cat.CategoryName
	FROM
		dbo.Orders O
		INNER JOIN dbo.OrderDetails OD ON O.OrderID = OD.OrderID
		INNER JOIN dbo.Customers C ON O.CustomerID = C.CustomerID
		INNER JOIN dbo.Products P ON OD.ProductID = P.ProductID
		INNER JOIN dbo.Categories Cat ON P.CategoryID = Cat.CategoryID
GO

Luego extendemos el repositorio de OrderDetails con el siguiente código. Observa que primero definimos una clase que represente el criterio de búsqueda. La búsqueda incluirá la columna correspondiente si la propiedad no es nula, y se combirarán con AND.

public class OrderDetailSearchCriteria
{

    public int? OrderId { get; set;}
    public DateTime? FromDate { get; set;}
    public DateTime? ToDate { get; set;}
    public Decimal? MinPrice { get; set;}
    public Decimal? MaxPrice { get; set;}
    public string CustomerId { get; set;}
    public string CustomerName { get; set;}
    public string City { get; set;}
    public string Region { get; set;}
    public string Country { get; set;}
    public int? ProductId { get; set;}
    public string ProductName { get; set;}

}

public partial class OrderDetailRepository
{
    public IQueryLite SearchQuery(OrderDetailSearchCriteria criteria)
    {
        var query = this.Query(Projection.Extended);
        if (criteria.City != null) query.And(OrderDetailFields.City, OperatorLite.Equals, criteria.City);
        if (criteria.Country != null) query.And(OrderDetailFields.Country, OperatorLite.Equals, criteria.Country);
        if (criteria.CustomerId != null) query.And(OrderDetailFields.CustomerId, OperatorLite.Equals, criteria.CustomerId);
        if (criteria.CustomerName != null) query.And(OrderDetailFields.CustomerName, OperatorLite.StartsWith, criteria.CustomerName);
        if (criteria.FromDate.HasValue) query.And(OrderDetailFields.OrderDate, OperatorLite.GreaterOrEquals, criteria.FromDate);
        if (criteria.MaxPrice.HasValue) query.And(OrderDetailFields.UnitPrice, OperatorLite.LessOrEquals, criteria.MaxPrice);
        if (criteria.MinPrice.HasValue) query.And(OrderDetailFields.UnitPrice, OperatorLite.GreaterOrEquals, criteria.MinPrice);
        if (criteria.OrderId.HasValue) query.And(OrderDetailFields.OrderId, OperatorLite.Equals, criteria.OrderId);
        if (criteria.ProductId.HasValue) query.And(OrderDetailFields.ProductId, OperatorLite.Equals, criteria.ProductId);
        if (criteria.ProductName != null) query.And(OrderDetailFields.ProductName, OperatorLite.StartsWith, criteria.ProductName);
        if (criteria.Region != null) query.And(OrderDetailFields.Region, OperatorLite.Equals, criteria.Region);
        if (criteria.ToDate.HasValue) query.And(OrderDetailFields.OrderDate, OperatorLite.LessOrEquals, criteria.ToDate);
        return query;
    }
}

 

Y lo usamos de la siguiente manera:

using (var ds = new NorthwindDataService("Northwind"))
{
    var criteria = new OrderDetailSearchCriteria
    {
        ProductName = "C",
        CustomerId = "ALFKI"
    };

    var orderDetails = ds.OrderDetailRepository.SearchQuery(criteria)
        .Fields(OrderDetailFields.OrderId, OrderDetailFields.OrderDate, OrderDetailFields.ProductName, OrderDetailFields.SubTotal)
        .OrderByDesc(OrderDetailFields.OrderDate)
        .OrderBy(OrderDetailFields.ProductName)
        .ToList(0, 9);

    foreach(var od in orderDetails)
    {
        Console.WriteLine("{0}, {1},  {2}, {3}", od.OrderId, od.OrderDate, od.ProductName, od.SubTotal);
    }
                    
}

 

Bueno, realmente este código funcionaría igualmente para ORACLE, MySQL, Postgre-SQL y SQLite. Pero observa que esta manera de hacer la búsqueda dinámica tiene una serie de ventajas con respecto al artículo de Erland:

  • Puedes especificar las columna a recuperar.
  • Puedes epecificar el orden de las columnas
  • Puedes hacer paginación de consultas.

 

Si lo ejecutamos con el SQL Profiler abierto veremos lo siguiente:

 

exec sp_executesql N'
SELECT [OrderDate], [OrderDetailID], [OrderID], [ProductName], [SubTotal]
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY [OrderDate] DESC,
    [ProductName]) AS __RowNumber__
FROM [dbo].[OrderDetail_Extended]

WHERE
    ([CustomerID] = @P0
    AND [ProductName] LIKE @P1 + ''%'')
) T
WHERE __RowNumber__ BETWEEN @P2 AND @P3
ORDER BY __RowNumber__;',N'@P0 nvarchar(5),@P1 nvarchar(40),@P2 int,@P3 int',@P0=N'ALFKI',@P1=N'C',@P2=1,@P3=10

 

O sea, código sql eficiente, controlado y predecible. Algo bastante diferente de lo que nos encontramos con Entity Framework.

EntityLite publicado

EntiLite es un micro ORM que he desarrollado y que está publicado en versión reléase desde hace unas semanas en Nuget: https://www.nuget.org/packages/EntityLite/

El código fuente está en GitHub: https://github.com/jesuslpm/EntityLite

Y como documentación hay un artículo en CodeProject recientemente publicado: http://www.codeproject.com/Articles/753796/i-nercya-EntityLite-A-Lightweight-Database-First-M

EntityLite es diferente de otros ORM’s, su enfoque es relacional, y está diseñado para sacarle todo el partido posible a la base de datos. Con EntityLite puedes hacer cualquier consulta que se te pueda ocurrir por muy complicada que sea.

 

 

EntityLite es el ORM más rápido de todos

Frans Bouma, el creador del ORM LlBlGen, escribió en diciembre un blog post donde comparaba el rendimiento de carga de varios ORM’s (http://weblogs.asp.net/fbouma/archive/2013/12/09/fetch-performance-of-various-net-orm-data-access-frameworks.aspx). Naturalmente Frans Bouma no incluyó EntityLite, un micro ORM creado por mi, ya que hace muy poco que está disponible en NuGet y aún está en beta.

Al darme cuenta de que el código fuente del bench mark estaba disponible (https://github.com/FransBouma/RawDataAccessBencher), me decidí a incluir el EntityLite también en las pruebas haciendo un fork del repositorio (https://github.com/jesuslpm/RawDataAccessBencher).

Cual fue mi sorpresa al descubrir que EntityLite es de los más rápidos. Ciertamente el benchmark no siempre da los mismos resultados y no siempre EntityLite queda el primero, pero muchas veces así es. Aquí tenermos algunos de los resultados.

Non-change tracking fetches, set fetches (10 runs), no caching
------------------------------------------------------------------------------
Handcoded materializer using DbDataReader       : 185,50ms.	Enumeration average: 1,13ms
PetaPoco Fast v4.0.3                            : 230,50ms.	Enumeration average: 1,38ms
EntityLite 1.0.10-Beta                          : 233,50ms.	Enumeration average: 1,00ms
Entity Framework v6.0.0.0 (v6.0.21211.0)        : 244,13ms.	Enumeration average: 1,13ms
PetaPoco v4.0.3                                 : 267,38ms.	Enumeration average: 2,00ms
Linq to Sql v4.0.0.0 (v4.0.30319.18408)         : 276,00ms.	Enumeration average: 1,13ms
Dapper                                          : 314,50ms.	Enumeration average: 2,50ms
ServiceStack OrmLite v4.0.5.0 (v4.0.5.0)        : 425,00ms.	Enumeration average: 2,00ms
LLBLGen Pro v4.1.0.0 (v4.1.13.1213), typed view : 516,25ms.	Enumeration average: 5,63ms
Oak.DynamicDb using dynamic Dto class           : 934,63ms.	Enumeration average: 226,50ms

 

Non-change tracking individual fetches (100 elements, 10 runs), no caching
------------------------------------------------------------------------------
PetaPoco Fast v4.0.3                            : 1,55ms per individual fetch
Handcoded materializer using DbDataReader       : 1,59ms per individual fetch
EntityLite 1.0.10-Beta                          : 1,59ms per individual fetch
Dapper                                          : 1,75ms per individual fetch
ServiceStack OrmLite v4.0.5.0 (v4.0.5.0)        : 1,87ms per individual fetch
Oak.DynamicDb using dynamic Dto class           : 1,91ms per individual fetch
LLBLGen Pro v4.1.0.0 (v4.1.13.1213), typed view : 2,23ms per individual fetch
Entity Framework v6.0.0.0 (v6.0.21211.0)        : 3,00ms per individual fetch
Linq to Sql v4.0.0.0 (v4.0.30319.18408)         : 4,77ms per individual fetch
PetaPoco v4.0.3                                 : 9,91ms per individual fetch

EntityLite está en NuGet: (https://www.nuget.org/packages/EntityLite/1.0.10-Beta)

El código fuente en GitHub: (https://github.com/jesuslpm/EntityLite)

La documentación de EntityLite está confeccionándose, estoy escribiendo un artículo para CodeProject, aquí está lo que llevo escrito hasta el momento (https://github.com/jesuslpm/EntityLite/tree/master/CodeProject)

 

FastDynamic una alternativa eficiente a Reflection

Hace unos años cuando trabajaba para Solid escribí un artículo Cargar listas desde un datareader con DynamicMethod . En él hacía uso de DynamicMethod para crear métodos en tiempo de ejecución para acceder a propiedades de objetos.

Ahora he querido  escribir una pequeñísima librería (379 líneas de código) basada en aquella idea que permita, de forma eficiente, acceder no sólo a propiedades sino también a campos públicos, además de poder crear objetos al vuelo, una alternativa eficiente a Activator.CreateInstance.

FastDynamic es mucho más eficiente que reflection,  Aquí tenéis el resultado de un microbenchmak:

Using reflection.. 1480 ms
Using CreateObject, GetMemberValue and SetMemberValue.. 621 ms
Using getters, setters and activator .. 85 ms
Known at compile time.. 9 ms

FastDynamic es además más fácil de usar. Por ejemplo para obtener el valor de una propiedad sólo tienes que escribir obj.GetMemberValue(“NombrePropiedad”).

¿Para qué está indicado FastDynamic?. Bien, su uso estaría indicado en aquellas aplicaciones que trabajan con tipos no conocidos en tiempo de compilación, pero que sí se conocen en tiempo de ejecución. Que necesitan crear objetos de estos tipos, o que necesitan acceder a sus propiedades y campos públicos.

Una aplicación de FastDynamic sería por ejemplo para crear un exportador a ficheros csv. Este exportador le pasaríamos un IEnumerable con los objetos de la lista a exportar, y una lista de propiedades que quisiéramos incluir, junto con los encabezados de las columnas. Evidentemente el exportador no conoce en tiempo de compilación el tipo de los objetos, ni siquiera el nombre de las propiedades. En un próximo blog post os presentaré una implementación de este exportador.

FastDynamic también sería también útil para una framework de serialización como SharpSerializer ( http://sharpserializer.codeplex.com/discussions/361322) que usa Reflection de forma extensiva. Por cierto tengo planeado hacer un fork de esta librería para añadir capacidad de serialización de campos públicos y aumentar su rendimiento usando FastDynamic.

Por cierto FastDynamic está aquí : https://github.com/jesuslpm/FastDynamic

Saludos:

Jesús López

 

 

 

 

 

 

Crear un generador de registro de auditoría (Audit Trail) para SQL Server

Muchas veces el cliente nos pide que la aplicación que estamos desarrollando tenga registro de auditoría, o sea, que se pueda saber qué se ha cambiado en la base de datos, por quien y cuando. El método más extendido para implementar el registro de auditoría consiste en crear tablas de auditoría y triggers de auditoría. Sin embargo hay dos enfoques a lo hora de diseñar estas tablas. Uno se centra en las columnas y otro se centra en las filas. El primero genera un registro de auditoría por cada columna que se modifica y el otro genera un registro de auditoría por cada fila que se modifica. Por otro lado hay diseños que intentan ser genéricos diseñando un modelo con muy pocas tablas y otros diseños que son específicos de los datos a auditar.

A la hora de diseñar el registro de auditoría tenemos que tener en cuenta lo siguiente:

  • Espacio requerido para la información de auditoría.
  • Complejidad del diseño.
  • Eficiencia del diseño.
  • Capacidad de consulta del diseño.

Teniendo en cuenta todos estos aspectos yo me inclino por un diseño centrado en filas en el que tenemos una tabla de auditoría por cada tabla que queremos auditar y triggers de auditoría en cada tabla que queremos auditar. Esta tabla de auditoría tendrá una estructura muy similar a la tabla auditada, pero con información adicional de auditoría. A mi entender, la peor inconveniente de este diseño es que hay que crear muchas tablas y muchos triggers. Pero la buena noticia es que estas tablas y triggers se pueden crear automáticamente con un generador de sólo 147 líneas de código.

Pare entender el diseño creo que lo mejor es poner un ejemplo. Supongamos que queremos auditar la tabla Contacts:

CREATE TABLE dbo.Contacts
(
      ContactId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
      ContactName nvarchar(128) NOT NULL,
      ContactAddress nvarchar(256),
      ContactPhone varchar(50),
      ContactEmailAddress varchar(128)
)

 La tabla de auditoría dbo.ContactsAudit tendrá los mismos campos que dbo.Contacts más información de auditoría. Esta información de auditoría incluye el usuario que está realizando la modificación.  En principio podríamos usar la función ORIGINAL_LOGIN() para obtener el usuario, pero muchas veces las aplicaciones utilizan el mismo login independientemente del usuario que utiliza la aplicación, por lo que ORIGINAL_LOGIN() no nos da ninguna información acerca de ese usuario. Una solución es que la aplicación, en cuanto identifique al usuario, ponga el Id del usuario en la información de contexto, ejecutando un procedimiento almacenado como este:

CREATE PROCEDURE SetUserContext
	@UserId int
AS
	DECLARE @uc binary(4)
	SET @uc = CONVERT(binary(4), @UserId)
	SET CONTEXT_INFO @ci

 Así, puede obtenerse el Id del usuario de la siguiente manera:

DECLARE @UserId int
SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4))

 La tabla de auditoría dbo.ContactAudit incluye también el tipo de modificación (‘I’: insert, ‘U’: update, ‘D’ delete) y la fecha y hora de la modificación. Cuando se realiza una inserción, sólo registramos en la tabla dbo.ContactAudit la información adicional de auditoría, más la clave primaria, el resto de las columnas las dejamos nulas, ya que esta información ya está en la tabla dbo.Contact, no es necesario ponerla también en la tabla de auditoría, así ahorramos espacio. Cuando se hace un update o un delete, registramos en la tabla dbo.ContactAudit la información de auditoría y los valores originales de todas las columnas de dbo.Contact.

La tabla dbo.ContactAudit sería la siguiente (generado automáticamente)

CREATE TABLE dbo.ContactsAudit
(
    [_ChangeId] int IDENTITY (1,1) PRIMARY KEY,
    [_OriginalLogin] sysname,
    [_UserId] int,
    [_ChangeDate] datetimeoffset NOT NULL,
    [_ChangeType] char(1) NOT NULL,
    [ContactId] int NOT NULL ,
    [ContactName] nvarchar(128) NULL ,
    [ContactAddress] nvarchar(256) NULL ,
    [ContactPhone] varchar(50) NULL ,
    [ContactEmailAddress] varchar(128) NULL 
)

 Los triggers de audotoría  serían los siguientes (generado automáticamente):

CREATE TRIGGER dbo.Contacts_AuditTrail_Insert
ON  dbo.Contacts AFTER INSERT
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO dbo.ContactsAudit([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType], [ContactId])
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'I', [ContactId]
    FROM inserted
GO

CREATE TRIGGER dbo.Contacts_AuditTrail_Update
ON  dbo.Contacts AFTER UPDATE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO dbo.ContactsAudit([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType], [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress])
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'U', [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress]
    FROM deleted
GO

CREATE TRIGGER dbo.Contacts_AuditTrail_Delete
ON  dbo.Contacts AFTER DELETE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO dbo.ContactsAudit([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType], [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress])
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'D', [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress]
    FROM deleted
GO

 El generador es una plantilla T4 de Visual Studio (2010 ó 2012). En un proyecto de Visual Studio creamos un nuevo archivo de texto llamado “AuditTrail.ttinclude” con el siguiente contenido:

<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="$(TargetPath)" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.Common" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text" #>
<#+
    public string FullTableName;
    public string FullAuditTableName;

void Render()
{
    string ConnectionString = @"Data Source=SQLDSQL2008R2;Integrated Security=SSPI;Initial Catalog=App";
    DataTable schema = null;
    StringBuilder sb = new StringBuilder();
    StringBuilder sbpk = new StringBuilder();
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand("SELECT * FROM " + FullTableName, cn))
    {
        cn.Open();
        using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
        {
            schema = reader.GetSchemaTable();
        }
    }
#>
USE App
GO
IF OBJECT_ID('<#= FullTableName  #>_AuditTrail_Insert') IS NOT NULL
	DROP TRIGGER <#= FullTableName #>_AuditTrail_Insert
GO
IF OBJECT_ID('<#= FullTableName  #>_AuditTrail_Update') IS NOT NULL
	DROP TRIGGER <#= FullTableName #>_AuditTrail_Update
GO
IF OBJECT_ID('<#= FullTableName  #>_AuditTrail_Delete') IS NOT NULL
	DROP TRIGGER <#= FullTableName #>_AuditTrail_Delete
GO
IF OBJECT_ID('<#= FullAuditTableName #>') IS NOT NULL
	DROP TABLE <#= FullAuditTableName #>	
GO

CREATE TABLE <#= FullAuditTableName #>
(
    [_ChangeId] int IDENTITY (1,1) PRIMARY KEY,
	[_OriginalLogin] sysname,
    [_UserId] int,
	[_ChangeDate] datetimeoffset NOT NULL,
	[_ChangeType] char(1) NOT NULL,
<#+ 
    DataRowCollection rows = schema.Rows;
    for(int rowIndex = 0; rowIndex < rows.Count; rowIndex++ )
    {
        DataRow row = rows[rowIndex];
        string fieldName = (string) row["ColumnName"];
        string dataTypeName= GetCondensedTypeName(row);
        if (dataTypeName != "text" && dataTypeName != "ntext" && dataTypeName != "image")
        {
            sb.Append(", [").Append(fieldName).Append("]");
            if (object.Equals(row["IsKey"], true)) sbpk.Append(", [").Append(fieldName).Append("]");
#>
    [<#=row["ColumnName"] #>] <#=dataTypeName #> <#= object.Equals(row["IsKey"], true) ? "NOT NULL": "NULL" #> <#= rowIndex < rows.Count - 1 ? "," : string.Empty #>
<#+
        }
    }
    string fieldNameCommaSeparatedList= sb.ToString();
    string pkFieldNameCommaSeparatedList= sbpk.ToString();
#>
)
GO

CREATE TRIGGER <#= FullTableName #>_AuditTrail_Insert
ON  <#= FullTableName #> AFTER INSERT
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO <#= FullAuditTableName #>([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType]<#= pkFieldNameCommaSeparatedList #>)
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'I'<#= pkFieldNameCommaSeparatedList #>
    FROM inserted
GO

CREATE TRIGGER <#= FullTableName #>_AuditTrail_Update
ON  <#= FullTableName #> AFTER UPDATE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO <#= FullAuditTableName #>([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType]<#= fieldNameCommaSeparatedList #>)
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'U'<#= fieldNameCommaSeparatedList #>
    FROM deleted
GO

CREATE TRIGGER <#= FullTableName #>_AuditTrail_Delete
ON  <#= FullTableName #> AFTER DELETE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO <#= FullAuditTableName #>([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType]<#= fieldNameCommaSeparatedList #>)
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'D'<#= fieldNameCommaSeparatedList #>
    FROM deleted
GO
<#+
}
private static string[] nonSizedTypes = { 
    "bit", "tinyint", "smallint", "int", "bigint", "real", "float", "money", "smallmoney", "text", "ntext", "sql_variant",
    "image", "smalldatetime", "datetime", "date", "xml", "hierarchyid", "geography", "geometry", "uniqueidentifier"
    };
public string GetCondensedTypeName(DataRow row)
{
    string dataTypeName = (string) row["DataTypeName"];
    if (dataTypeName.EndsWith("hierarchyid")) dataTypeName = "hierarchyid";
    if (dataTypeName.EndsWith("geography")) dataTypeName = "geography";
    if (dataTypeName.EndsWith("geometry")) dataTypeName = "geometry";
    if (Array.IndexOf(nonSizedTypes, dataTypeName) >= 0) return dataTypeName;
    if (dataTypeName == "numeric" || dataTypeName == "decimal")
    {
        dataTypeName = "numeric(" + row["NumericPrecision"].ToString() + ", " + row["NumericScale"].ToString() + ")";
    }
    else if ((Type) row["DataType"] == typeof(string) || dataTypeName == "varbinary" || dataTypeName == "binary")
    {
        if ( (int) row["ColumnSize"] == 2147483647)
        {
            dataTypeName = dataTypeName + "(max)";
        }
        else
        {
            dataTypeName = dataTypeName + "(" + row["ColumnSize"].ToString() + ")";
        }
    }
    else if (dataTypeName == "timestamp" || dataTypeName == "rowversion")
    {
        dataTypeName = "binary(8)";
    }
    else if (dataTypeName == "datetime2" || dataTypeName == "datetimeoffset" || dataTypeName == "time")
    {
        dataTypeName = dataTypeName + "(" + row["NumericScale"].ToString() + ")";
    }
    return dataTypeName;
}
#>

Observa que en el método Render está la cadena de conexión. Modifícala para que apunte a la base de datos en cuestión. Observa también que en AuditTrail.ttinclude hay una instrucción USE App, cámbiala para la base de datos que estés utilizando

Luego para cada tabla que queramos auditar añadimos un nuevo T4 Template (Botón derecho del ratón sobre el proyecto -> “Add” -> “new Ítem” -> “Text Template”) con el nombre de la tabla, por ejemplo “Contacts.tt” y un contenido como el siguiente:

<#@ template language="C#" hostspecific="True" debug="True" #>
<#@ output extension="sql" #>
<#@ include file="AuditTrail.ttinclude" #>
<#
    this.FullTableName = "dbo.Contacts";
    this.FullAuditTableName = "dbo.ContactsAudit";
    Render();
#>

 Cuando guardes el archivo se generará un archivo Contacts.sql que “cuelga” de “Contacts.tt”. Puedes abrirlo y ejecutar el script desde el propio Visual Studio.

Llamar a servicios web desde dentro de SQL Server 2005

El otro día, por diversión, me dio por jugar a llamar a un servicio web desde un procedimiento almacenado de SQL Server 2005 escrito en C#. La cosa parecía fácil y divertida, pero de eso nada, no había manera de hacer funcionar aquello…. Por fin, después de buscar un rato por el Google encontré la solución a mis problemas. Para evitar que a vosotros también os pase lo mismo, quisiera compartir con todos vosotros esta experiencia que os permitirá saber de antemano los problemas que os vais a encontrar al hacer este tipo de cosas y como solucionarlos.


Para empezar supongamos que tenemos una solución en Visual Studio 2005 con un sitio web donde está definido el siguiente servicio web accesible mediante la url http://localhost/ServiciosWeb/ServicioWeb.asmx


public class ServicioWeb : System.Web.Services.WebService

  [WebMethod] 
  public string RealizarProceso(int IdProceso) 
  { 
    if (IdProceso % 5 == 0) 
    { 
      throw new SoapException(“No me gustan los múltiplos de cinco”,
                               SoapException.ClientFaultCode); 
    } 
    else 
    {
      return “Proceso número ” + IdProceso.ToString() +
             ” realizado correctamente”; 
    } 
  }
}



Supongamos también que creamos un proyecto de SQL Server de nombre ServiciosWebClr. Lo primero que se nos ocurre es añadir una referencia web al servicio, a la que llamamos ServiciosWeb. Luego añadirmos un procedimiento almacenado llamado RealizarProceso con este código:



using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ServiciosWebClr.ServiciosWeb;



public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void RealizarProceso(SqlInt32 IdProceso)
  {
    using (ServicioWeb sw = new ServicioWeb())
    {
      string Resultado = sw.RealizarProceso(IdProceso.Value);
    }
  }
};



Para que un procedimiento almacenado CLR pueda llamar a un servicio web, el ensamblado en el que reside tiene que tener permiso de EXTERNAL ACCESS. Para ello vamos a las propiedades del proyecto, ficha database y seleccionamos external en el cuadro combinado permission level. Pero esto no es suficiente. Si intentamos desplegar (deploy) el proyecto recibiremos el siguiente error:



CREATE ASSEMBLY for assembly ‘ServiciosWebClr’ failed because assembly ‘ServiciosWebClr’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. ServiciosWebClr



Para solucionarlo ejecutamos la siguiente sentencia en el Management Studio:



ALTER DATABASE <base de datos> SET TRUSTWORTHY ON



Con esto ya podremos desplegar el proyecto en SQL Server. Pero si ejecutamos este procedimiento almacenado en el Management Studio, nos encontramos con otro problema, recibimos el siguiente mensaje de error:



Msg 6522, Level 16, State 1, Procedure RealizarProceso, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate ‘RealizarProceso’:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
etc etc.



Los servicios web serializan y deserializan los objetos en formato XML. Para realizar estas operaciones utilizan un XmlSerializer el cual genera y carga en tiempo de ejecución un ensamblado con clases especializadas en serializar y deserializar los tipos de nuestro  ensamblado. Por razones de seguridad y fiabilidad, SQL Server no permite cargar ensamblados en tiempo de ejecución, por eso recibimos esta excepción.


La solución, como sugiere el mesaje de error, es utitlizar un serializador XML pregenerado. Entre las herramientas de línea de comandos, tenemos SGen.exe que genera un ensamblado para serialización XML. Pero es más sencillo ir a las propiedades del proyecto, ficha build y seleccionar on en el cuadro combinado generate serialization assembly. Con esto, se generará el ensamblado ServiciosWebClr.XmlSerializers.dll cada vez que generemos el proyecto. Pero este ensamblado no se instala automáticamente en SQL Server, tenemos que hacerlo nosotros manualmente. Para ello añadimos los scripts predeployscript.sql y postdeployscript.sql en la raíz del proyecto (no en la carpeta Test Scripts) con el siguiente contenido:



Predeployscript.sql:



IF EXISTS (SELECT [name]
           FROM sys.assemblies
           WHERE [name] = N’ServiciosWebClr.XmlSerializers’)
DROP ASSEMBLY [ServiciosWebClr.XmlSerializers]
WITH NO DEPENDENTS



Postdeployscript.sql:



CREATE ASSEMBLY [ServiciosWebClr.XmlSerializers]
FROM ‘<path>ServiciosWebClr.XmlSerializers.dll’


Con esto ya debería funcionar.



Otra cuestión es que la mayoría de las veces no resulta adecuado llamar al servicio web de forma síncrona, ya que normalmente la llamada tarda algunos segundos. Así que veamos qué pasa cuando llamamos a los servicios web de forma asíncrona.


Supongamos que creamos la siguiente tabla y procedimiento almacenado para registrar todas las llamadas al servicio web, incluyendo el resultado, información de tiempos y errores:



CREATE TABLE RegistroProcesos
(
      IdRegistro int IDENTITY(1,1) PRIMARY KEY,
      IdProceso int NOT NULL,
      Comienzo datetime NOT NULL,
      Fin datetime NOT NULL,
      Correcto bit NOT NULL DEFAULT 1,
      Resultado varchar(100),
      DescripcionError varchar(1000)
)

GO
 
CREATE PROCEDURE RegistrarProceso
      @IdProceso int,
      @Comienzo datetime,
      @Fin datetime,
      @Correcto bit,
      @Resultado varchar(100),
      @DescripcionError varchar(1000)
AS
      INSERT INTO RegistroProcesos(
            IdProceso, Comienzo, Fin,
            Correcto, Resultado, DescripcionError)
      VALUES (
            @IdProceso, @Comienzo, @Fin,
            @Correcto, @Resultado, @DescripcionError)


Nuestra clase StoredProcedures podría ser la siguiente:


public partial class StoredProcedures
{


  private class Estado
  {
    public int IdProceso;
    public ServicioWeb ServicioWeb;
    public DateTime Comienzo;
 
    public Estado(
        int IdProceso,
        ServicioWeb ServicioWeb,
        DateTime Comienzo)
    {
      this.IdProceso = IdProceso;
      this.ServicioWeb = ServicioWeb;
      this.Comienzo = Comienzo;
    }
  }
 
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void RealizarProceso(SqlInt32 IdProceso)
  {
    ServicioWeb sw = new ServicioWeb();
    Estado Estado = new Estado(IdProceso.Value, sw, DateTime.Now);
    sw.BeginRealizarProceso(
          IdProceso.Value, DelegadoProcesoRealizado, Estado);
  }
 


  private static readonly AsyncCallback DelegadoProcesoRealizado =
       new AsyncCallback(ProcesoRealizado);
 
  private static void ProcesoRealizado(IAsyncResult ar)
  {
    Estado Estado = (Estado)ar.AsyncState;
    try
    {
      String Resultado = Estado.ServicioWeb.EndRealizarProceso(ar);
      RegistrarProceso(Estado.IdProceso, Estado.Comienzo,
                       DateTime.Now, true, Resultado, null);
    }
    catch (Exception ex)
    {
      RegistrarProceso(Estado.IdProceso, Estado.Comienzo,
                       DateTime.Now, false, null, ex.Message);
    }
   }
 
   private const string ConnectionString =
     “Data Source=(local);Integrated Security=SSPI;” +
     “Initial Catalog=AdventureWorks”;
 
 
   private static void RegistrarProceso(
     int IdProceso,   
     DateTime Comienzo,
     DateTime Fin,
     bool Correcto,
     string Resultado,
     string DescripcionError)
   {
     using (SqlConnection cn = new SqlConnection(ConnectionString))
     using (SqlCommand cmd = new SqlCommand(“RegistrarProceso”, cn))
     {
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add(“@IdProceso”, SqlDbType.Int).Value =
           IdProceso;
       cmd.Parameters.Add(“@Comienzo”, SqlDbType.DateTime).Value =
           Comienzo;
       cmd.Parameters.Add(“@Fin”, SqlDbType.DateTime).Value = Fin;
       cmd.Parameters.Add(“@Correcto”, SqlDbType.Bit).Value =
           Correcto;
       cmd.Parameters.Add(“@Resultado”,SqlDbType.VarChar, 100).Value =
           Resultado == null ? (object) DBNull.Value :
                        (object) Resultado;
       cmd.Parameters.Add(“@DescripcionError”,
                          SqlDbType.VarChar, 1000).Value =
       DescripcionError == null ? (object) DBNull.Value :
                           (object) DescripcionError;
       cn.Open();
       cmd.ExecuteNonQuery();
     }


   }
}


Observa que la cadena de conexión no es “context connection=true”, sino una cadena de conexión regular. Esto es así porque el método de devolución de llamada ProcesoRealizado se ejecuta en un hilo diferente que el procedimiento almacenado, este hilo no es un hilo de ejecución de SQL Server así que no se puede usar la conexión de contexto.


Si desplegamos ahora el proyecto y ejecutamos lo siguiente en el ManagementStudio:


TRUNCATE TABLE RegistroProcesos
DECLARE @IdProceso int
SET @IdProceso = 1
WHILE @IdProceso <= 10
BEGIN
  EXEC RealizarProceso @IdProceso
  SET @IdProceso = @IdProceso + 1
END


Y luego ejecutamos;


SELECT * FROM RegistroProcesos


Veremos que el procedimiento almacenado funciona como se espera.


Pues nada más, espero que esto os sirva de ayuda y os quite algún que otro quebradero de cabeza si es que alguna vez necesitáis llamar a un servicio web desde dentro de SQL Server 2005.


Un saludo:


Jesús López