LINQ to SQL (I): Mapeo Objeto-Relacional


A estas alturas ya debemos de tener una idea bastante precisa del funcionamiento básico de LINQ. Hemos visto cómo lanzar consultas a listados de objetos (LINQ to Objects) e incluso algún ejemplo sobre cómo iterar sobre ficheros XML (LINQ to XML). Todas estas operaciones tienen un factor común: se realizaban sobre listados que implementaban la interfaz IEnumerable.

Además de poder lanzar consultas sobre listados de objetos, LINQ nos permite una opción aún más potente: lanzar consultas sobre bases de datos SQL Server mediante LINQ to SQL, que no es más que un mapper objeto-relacional, es decir, encargado de transformar sentencias LINQ en expresiones T-SQL y viceversa.

La potencia de LINQ to SQL radica en que, además de permitir realizar operaciones de consulta sobre una base de datos, también es posible realizar inserciones, actualizaciones y eliminaciones de forma nativa. A continuación veremos cómo realizar esto.

La principal desventaja de usar LINQ to SQL es que nuestra base de datos ha de ser, obligatoriamente, SQL Server (no obstante, existen proveedores LINQ para otras bases de datos, como LINQ to Oracle o LINQ to MySQL (vía NuGet). En caso de que todo esto falle y que necesitemos un proveedor personalizado, siempre podremos desarrollar por cuenta propia nuestro propio proveedor. En cualquier caso, siempre podremos utilizar un mapper objeto-relacional más genérico, como por ejemplo Entity Framework, del que hablaremos en posteriores artículos.

En este primer acercamiento a LINQ to SQL hablaremos de cómo establecer la relación entre nuestro programa (orientado a objetos) y nuestra base de datos (relacional). Para ello disponemos de varias posibilidades, que mostraremos a continuación:

Mapeo mediante POCOs + XML

El primer método de mapeo entre nuestro modelo orientado a objetos y la base de datos relacional será a través de los Plain Old CLR Objects o POCOs. Estos objetos no son más que una representación objetual de un registro perteneciente a una tabla de una base de datos. Estas clases se servirán de un fichero de mapeo en formato XML para establecer la relación entre los elementos de la clase y las tablas y columnas de la base de datos.

Tomemos como referencia la base de datos AdventureWorks 2012 de Microsoft (puedes descargarla desde aquí). Seleccionaremos una tabla sencilla como por ejemplo HumanResources.Department, y veremos que tiene las siguientes columnas:

Nuestro POCO debe modelar los datos de las columnas de la tabla mediante propiedades cuyos tipos coincidan (es decir, sean compatibles) con los de las propias columnas. Así, crearemos en nuestro proyecto un namespace llamado Poco en el que crearemos una clase vacía a la que le añadiremos cuatro propiedades cuyos tipos coincidan con los de la tabla. Como veremos más adelante, no es necesario que los nombres de las propiedades también coincidan, ya que los campos se mapearán en un fichero XML en el siguiente paso, pero siempre es una buena práctica respetarlos en la medida de lo posible siempre y cuando la nomenclatura de la base de datos sea similar a la que utilizamos en nuestro programa. Por lo tanto, este será nuestro POCO:


namespace Poco.HumanResources
{
    public class Department
    {
        public short Id { get; set; }
        public string Name { get; set; }
        public string GroupName { get; set; }
        public DateTime ModifiedDate { get; set; }
    }
}

Como vemos, se trata de cuatro propiedades cuyos tipos equivalen a los codificados en la base de datos, al igual que sus nombres… excepto el campo Id. Hemos cambiado el nombre de uno de los campos para demostrar cómo mapear las columnas a través del fichero XML, que será el siguiente paso.

Si nos encontramos en un proyecto web, lo más común será añadir estos ficheros en la carpeta App_Data. En este caso particular, dado que nos encontramos en un proyecto de consola, crearemos una carpeta con este nombre para guardar la similitud del proyecto web, pero en realidad podríamos almacenarlos en la ruta de nuestra conveniencia.

Una vez en esta carpeta, creamos un fichero XML al que llamaremos HumanResources.DepartmentMapping.xml, y en el que añadiremos las siguientes líneas:


<?xml version="1.0" encoding="utf-8" ?>
<Database Name="AdventureWorks2012" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="HumanResources.Department">
    <Type Name="LinqEjemplo.Poco.HumanResources.Department">
      <Column Name="DepartmentID" Member="Id" />
      <Column Name="Name" Member="Name" />
      <Column Name="GroupName" Member="GroupName" />
      <Column Name="ModifiedDate" Member="ModifiedDate" />
    </Type>
  </Table>
</Database>

La estructura del fichero XML, tal y como observamos arriba, es la siguiente:

  • Database: Base de datos cuyas tablas vamos a mapear como objetos.
    • Table: Nombre que la tabla posee en la fuente de datos.
      • Type: Clase que modelará cada uno de los registros de la tabla
        • Column: Realizará el mapeo entre los campos de la tabla (Name) y los atributos de la clase (Member)

Por lo tanto, ya tenemos configurado nuestro POCO junto al fichero XML encargado de informar al DataContext acerca de cómo realizar el mapeo objeto-relacional. Es hora de añadir un par de referencias a nuestro proyecto para continuar. Necesitaremos acceder a las cadenas de conexión a la base de datos y utilizar la clase DataContext, por lo que deberemos importar las bibliotecas que contienen estos elementos. Lo primero será pulsar click derecho sobre las referencias de nuestro proyecto y seleccionar la opción Add Reference…

A continuación seleccionaremos las bibliotecas del Framework que contienen las clases DataContext y ConfigurationManager. El primero de estos elementos se encuentra en el espacio de nombres System.Data.Linq.

El segundo lo podremos encontrar en el espacio de nombres System.Configuration. Seleccionamos ambas bibliotecas y pulsamos el botón [OK]

Sin embargo, todavía no tenemos forma de saber cómo nos conectaremos a nuestra base de datos. Esto lo realizaremos a través de una cadena de conexión, que codificaremos dentro de la sección configuration > connectionStrings. En dicha sección añadiremos las cadenas de conexión, que vendrán definidas por un nombre, la cadena de conexión en sí y un proveedor. Si usamos SQL Server, bastará con indicar, además del nombre de la cadena de conexión (AdventureWorks2012) y del proveedor (System.Data.SqlClient) los datos de conexión que vendrán implícitos dentro de la propia cadena:

  • Database: nombre de la base de datos.
  • Server: dirección IP o dominio del servidor.
  • User ID: nombre de usuario válido dentro de SQL Server
  • Password: contraseña correspondiente al usuario indicado en el atributo anterior

Es posible añadir más atributos a nuestra cadena, pero con estos será suficiente para lograr la conexión. Por lo tanto, retocamos nuestro App.config o Web.config para que disponga de un aspecto similar al siguiente en su sección connectionStrings:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="AdventureWorks2012"
         connectionString="Database=AdventureWorks2012;Server=192.168.1.100;User ID=user;Password=8he&5kiss;Connection Timeout=30;pooling=false;"
         providerName="System.Data.SqlClient"
    />
  </connectionStrings>
</configuration>

A continuación llega la hora de instanciar el DataContext encargado de realizar el mapeo entre la base de datos y nuestro esquema orientado a objetos. Necesitaremos para ello la cadena de conexión a la base de datos y el fichero XML que contiene el mapeo objeto-relacional. A partir del DataContext, basta con lanzar una sentencia LINQ sobre uno de sus métodos (por ejemplo, GetTable<Department>(), que simbolizará la tabla modelada por la clase Department).

El directorio en el que almacenamos los mapeos XML dependerá de nuestra arquitectura. Si estamos en una aplicación web y los almacenamos en el directorio App_Data/, la forma de acceder al fichero será tal y como se muestra en el siguiente ejemplo. En caso de que se trate de una aplicación de escritorio, haremos uso de otros métodos, como AppDomain.CurrentDomain.BaseDirectory(), para localizar la ruta en la que almacenamos los ficheros.

Finalmente, utilizaremos un foreach para recorrer todos los elementos devueltos por la consulta.


            // Obtenemos la cadena de conexión de App.config o Web.config
            string cadenaConexion = ConfigurationManager.ConnectionStrings["AdventureWorks2012"].ConnectionString;

            // Indicamos la ruta del fichero XML que almacenará el mapeo entre objeto y tabla
            string xmlMapping = Server.MapPath("~/App_Data/HumanResources.Department.xml");
            XmlMappingSource mappingSource = XmlMappingSource.FromUrl(xmlMapping);

            // Instanciamos un DataContext a partir de los dos elementos anteriores
            DataContext dataContext = new DataContext(cadenaConexion, mappingSource);

            // Realizamos la consulta sobre la tabla mapeada mediante la clase "Department"
            var departamentos = from departamento in dataContext.GetTable<Department>()
                                select departamento;
            // Mostramos por pantalla los campos de cada uno de los elementos que conforman nuestra consulta
            foreach (Department departamento in departamentos)
            {
                Console.WriteLine(string.Format("ID: {0}\n\tNOMBRE: {1}\n\tGRUPO: {2}\n\tMODIF:{3}\n\n",
                    departamento.Id,
                    departamento.Name,
                    departamento.GroupName,
                    departamento.ModifiedDate));
            }

Recordemos que la proyección es una operación lazy, por lo que la consulta no se ejecutará hasta que se haga uso de ella (en el propio foreach). El resultado de la consulta será el siguiente:

Por supuesto, siempre es posible añadir cláusulas where u order by a nuestra consulta. Por ejemplo, podemos crear una clase que herede de DataContext en la que codifiquemos los métodos ejecutados sobre la tabla. Un método de consulta que admita filtrar por los parámetros de la tabla podría ser el siguiente:


    public class DepartmentDataContext : DataContext
    {
        private static string _xmlMapping = AppDomain Server.MapPath("~/App_Data/HumanResources.Department.xml");

        public DepartmentDataContext(string connectionString) : base(connectionString, XmlMappingSource.FromUrl(_xmlMapping))
        {
        }

        public IQueryable<Department> getDepartments(short id, string nombre, string grupo, DateTime fechaDesde, DateTime fechaHasta, bool contains)
        {
            // Generamos la consulta básica
            var consulta = from departamento in this.GetTable<Department>()
                           select departamento;

            // Añadimos las cláusulas where mediante expresiones lambda

            // Si el ID es > 0, añadimos la comparacion
            if(id > 0)
                consulta = consulta.Where(departamento => departamento.Id == id);

            // Si el booleano "contains" es falso, se comparará literalmente.
            // Si es verdadero, se comparará con Contains (equivale a un like en SQL)
            if (!contains)
            {
                if (!string.IsNullOrEmpty(nombre))
                    consulta = consulta.Where(departamento => departamento.Name.Equals(nombre));
                if (!string.IsNullOrEmpty(grupo))
                    consulta = consulta.Where(departamento => departamento.GroupName.Equals(grupo));
            }
            else
            {
                if (!string.IsNullOrEmpty(nombre))
                    consulta = consulta.Where(departamento => departamento.Name.Contains(nombre));
                if (!string.IsNullOrEmpty(grupo))
                    consulta = consulta.Where(departamento => departamento.GroupName.Contains(grupo));
            }

            // Comparamos con las fechas, si procede
            if (fechaDesde != DateTime.MinValue)
                consulta = consulta.Where(departamento => departamento.ModifiedDate >= fechaDesde);

            if (fechaHasta != DateTime.MinValue)
                consulta = consulta.Where(departamento => departamento.ModifiedDate <= fechaHasta);

            return consulta;
        }
    }

Invocamos el método:


            // Obtenemos la cadena de conexión de App.config o Web.config
            string cadenaConexion = ConfigurationManager.ConnectionStrings["AdventureWorks2012"].ConnectionString;

            DepartmentDataContext dataContext = new DepartmentDataContext(cadenaConexion);

            var departamentos = dataContext.getDepartments (0, "Production", "Manufacturing", new DateTime(2001, 01, 01), DateTime.MinValue, true);

El resultado serían aquellos registros que contengan “Production” en el nombre, “Manufacturing” en el grupo y se hayan modificado después del 01 de Enero del 2001:

Mapeo mediante atributos

La ventaja que tiene utilizar un fichero XML junto a los POCOs es la posibilidad de configurar la relación entre las tablas de nuestra base de datos relacional y nuestro modelo de objetos de forma que, si un campo o tabla de la base de datos cambia su nombre, sea posible corregir el cambio sin necesidad de recompilar la aplicación. Sin embargo, existe la posibilidad de codificar el mapeo objeto-relacional dentro del propio POCO mediante un conjunto de atributos presentes en el espacio de nombres System.Data.Linq.Mapping.

Realizando esta operación no necesitaremos, por ejemplo, instanciar el DataContext pasándole como parámetro el fichero XML del mapping tal y como hacíamos en el ejemplo anterior. Así, el equivalente al fichero XML en nuestra tabla HumanResources.Departments sería el siguiente:


namespace LinqEjemplo.Poco.HumanResources
{
    [Table(Name="HumanResources.Department")]
    public class Department
    {
        [Column(Name="DepartmentId", IsPrimaryKey=true, IsDbGenerated=true)]
        public short Id { get; set; }

        [Column(DbType="nvarchar(50)", CanBeNull=false)]
        public string Name { get; set; }

        [Column(Name="GroupName", CanBeNull = false)]
        public string GroupName { get; set; }

        [Column(CanBeNull = false)]
        public DateTime ModifiedDate { get; set; }
    }
}

La clase tiene asociado un atributo Table cuyo parámetro Name se corresponde con el nombre de la tabla del modelo relacional.

Los atributos que se identifican con columnas se decoran con el atributo Column, que puede aceptar también distintos parámetros, como Name (nombre del campo de la base de datos que está mapeando la propiedad, únicamente necesario si el nombre de la propiedad es distinto al del campo), DbType (tipo de datos de la columna en la base de datos), CanBeNull (indica si la columna acepta nulos), IsPrimaryKey (indica si el campo forma parte de la clave primaria) o IsDbGenerated (indica que el campo es generado automáticamente por la base de datos, como por ejemplo los identificadores autoincrementables).

Con este caso, deberíamos modificar, por lo tanto, el constructor de nuestro DataContext, ya que el fichero XML del mapeo ya no sería necesario.


        public DepartmentDataContext(string connectionString) : base(connectionString)
        {
        }

Mapeo mediante ficheros DBML

Pese a que el mapeo objeto-relacional es conceptualmente sencillo, realizar manualmente este proceso para todas las entidades de nuestra base de datos podría tornarse una tarea titánica que consumiría horas y horas de codificación. Por ello disponemos de un conjunto de herramientas que nos permitirán automatizar, en mayor o menor medida, los procesos de generación del código destinado a realizar este mapeo.

La primera de estas herramientas es el DataContext Designer, herramienta integrada en Visual Studio que nos permitirá generar clases heredadas de DataContext (tal y como vimos en el ejemplo anterior de una forma un tanto burda) que contendrán los POCOs de las entidades que deseemos (no necesariamente un POCO por cada DataContext, tal y como vimos antes.

Para iniciar este proceso generaremos un fichero dbml con la información sobre el mapeo y el acceso a datos. Haremos click derecho sobre la carpeta en la que queramos crear nuestro DataContext (si se trata de un proyecto web será en App_Code) y seleccionaremos la opción Add > New Item…

Seleccionaremos Data en el árbol de elementos mostrado a la izquierda y buscaremos entre los elementos la opción LINQ to SQL Classes, a la que le asignaremos un nombre representativo del DataContext que pretendemos crear.

Esta acción generará un fichero de extensión .dbml que estará compuesto por otros dos elementos: un layout que almacenará un diagrama con los elementos relacionales que queremos mapear y una clase de extensión .cs (si programamos en C#, de lo contrario será .vb) con todo el código automático generado a partir de la interfaz visual configurada en el layout.

El layout mostrará dos elementos: un diseñador que nos permitirá arrastrar objetos desde el explorador de servidores y un espacio para crear métodos personalizados. Comenzaremos pulsando el enlace Server Explorer para seleccionar los objetos de nuestra base de datos.

Una vez abierto el explorador de servidores, haremos click derecho sobre las conexiones de datos y añadiremos una nueva conexión (hasta ahora todo lo hemos realizado de forma manual).

Configuramos nuestra fuente de datos, seleccionando el tipo de base de datos. Dado que trabajamos con LINQ to SQL, no nos queda otro remedio que hacerlo con SQL Server.

Una vez conectados a nuestra base de datos, arrastramos las tablas que queremos incluir en nuestro modelo al layout.

El layout generará automáticamente las relaciones entre los elementos, extrayendo los metadatos de nuestro modelo relacional.

Si así lo deseamos, podemos modificar información como nombres de tabla, nombres de campos (propiedades), tal y como lo hacíamos manualmente en los dos ejemplos anteriores. Sólo que esta vez lo haremos a través de un editor gráfico que generará el código por nosotros.

Terminada esta parte, salvaremos y le prestaremos un poco de atención a la clase generada por nuestro layout:

Vemos en primer lugar que se trata de una clase parcial heredada de DataContext que posee un atributo DatabaseAttribute indicando la base de datos a la cual está enlazada.


	[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="AdventureWorks2012")]
	public partial class EmployeeDepartmentDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

Además de heredar de DataContext, vemos que contiene un conjunto de subclases cuya estructura recuerda bastante a los POCOs que veíamos antes. Se generan tanto las propiedades como los atributos que (en última instancia) están mapeando. Las propiedades get y set se muestran más completas, realizando operaciones más complejas que la simple asignación (por ejemplo, comprobando si se está modificando el valor actual de una propiedad y, si es el caso, disparar los eventos adecuados).


	[global::System.Data.Linq.Mapping.TableAttribute(Name="HumanResources.Department")]
	public partial class Department : INotifyPropertyChanging, INotifyPropertyChanged
	{
		
		private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
		
		private short _DepartmentID;
		
		private string _Name;
		
		private string _GroupName;
		
		private System.DateTime _ModifiedDate;
		
		private EntitySet<EmployeeDepartmentHistory> _EmployeeDepartmentHistories;
				
		public Department()
		{
			this._EmployeeDepartmentHistories = new EntitySet<EmployeeDepartmentHistory>(new Action<EmployeeDepartmentHistory>(this.attach_EmployeeDepartmentHistories), new Action<EmployeeDepartmentHistory>(this.detach_EmployeeDepartmentHistories));
			OnCreated();
		}
		
		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_DepartmentID", AutoSync=AutoSync.OnInsert, DbType="SmallInt NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
		public short DepartmentID
		{
			get
			{
				return this._DepartmentID;
			}
			set
			{
				if ((this._DepartmentID != value))
				{
					this.OnDepartmentIDChanging(value);
					this.SendPropertyChanging();
					this._DepartmentID = value;
					this.SendPropertyChanged("DepartmentID");
					this.OnDepartmentIDChanged();
				}
			}
		}

Si además le echamos un vistazo a nuestro fichero App.config o Web.config veremos, además, que se ha agregado una nueva cadena de conexión correspondiente a la configurada previamente a través del editor.


    <add name="LinqEjemplo.Properties.Settings.AdventureWorks2012ConnectionString"
      connectionString="Data Source=192.168.1.100;Initial Catalog=AdventureWorks2012;Persist Security Info=True;User ID=user;Password=8he&5kiss"
      providerName="System.Data.SqlClient" />

Para acceder a la fuente de datos, basta con acceder al POCO que simboliza la tabla que se encontrará dentro del recién creado DataContext, tal y como se muestra a continuación:


            EmployeeDepartmentDataContext dataContext = new EmployeeDepartmentDataContext();
            var consulta = from departamento in dataContext.Departments
                           orderby departamento.GroupName
                           select departamento;

El resultado, después de recuperar todos los registros y ordenarlos por grupo, sería el siguiente (similar al caso anterior):

Mapeo mediante sqlmetal.exe

Visual Studio nos proporciona, además del editor que acabamos de ver, una herramienta externa llamada sqlmetal.exe que es capaz de realizar el mapeo de forma automática por nosotros. Esta herramienta posee la habilidad de generar tanto mapeo XML como mapeo por atributos, además de poder generar un fichero dbml que podremos utilizar con el DataContext Editor.

Como desventajas, sqlmetal.exe no tiene forma alguna de sincronizar un esquema previamente generado con el estado actual de la base de datos, es decir, si generamos nuestro mapeo y la base de datos cambia, deberemos realizar los cambios sobre el mapeo de forma manual o generar de nuevo todo el mapeo. Como segunda desventaja: al igual que LINQ to SQL, únicamente es capaz de trabajar con las familias de bases de datos SQL Server.

La herramienta, dependiendo de la versión de Visual Studio que estemos utilizando, se encontrará en una de las siguientes rutas:

  • C:\Program Files\Microsoft SDKs\Windows\vX.X\bin\
  • C:\Program Files\Microsoft SDKs\Windows\vX.X\bin\NETFX X.X Tools

Su sintaxis, dependiendo de lo que queramos generar es la siguiente:

  • Opciones de acceso:
    • SqlMetal.exe
      • /server:<DireccionServidor>: Dirección IP o host del servidor
      • /database:<NombreBaseDeDatos>: Nombre de la base de datos
      • /user:<UsuarioBaseDeDatos>: Nombre de usuario de la base de datos
      • /password:<Password>: Contraseña correspondiente al usuario anterior
      • /conn:<ConnectionString>: En lugar de usar los parámetros anteriores, es posible pasar una cadena de conexión completa en un solo parámetro
  • Tipos de objetos a modelar:
    • SqlMetal.exe
      • /views: Generará las vistas de la base de datos.
      • /functions: Generará las funciones de la base de datos.
      • /sprocs: Generará los procedimientos almacenados de la base de datos.
  • Opciones de generación de código
    • SqlMetal.exe
      • /language<vb|cs>: Lenguaje en el que se generará el código fuente. Puede ser VB o C#. Por defecto se generará en C#.
      • /namespace<EspacioDeNombres>: Espacio de nombres al que pertenecerán las clases generadas
      • /serialization<None|Unidirectional>: Hace que las clases generadas sean serializables o no.
      • /provider<Proveedor>: Especifica el proveedor de la base de datos (SQLCompact, SQL2000, SQL2005, SQL2008, …)
  • Artefactos a generar
    • SqlMetal.exe
      • /dbml:<Fichero.dbml>: Genera un fichero .dbml que podrá ser utilizado por el DataContext Editor. No puede usarse con la opción /map
      • /code:<Fichero.cs>: Genera un fichero .cs. Si se prescinde de la opción /map, realizará el mapeo en la propia clase haciendo uso de atributos. No puede usarse con la opción /dbml.
      • /map:<Fichero.xml>: Genera un mapeo xml. No puede usarse con la opción /dbml.

Ilustraremos esta orden mediante nuestra pequeña base de datos de clientes, pedidos y productos, cuyo esquema podemos ver a continuación.

Además de las tablas, crearemos una vista que muestre la línea de pedido en detalle incluyendo el nombre del producto, su precio y un campo calculado con el total de la línea de producto (multiplicando el precio unitario del producto por la cantidad de elementos.

Crearemos también un par de procedimientos almacenados y una función. El primer procedimiento almacenado será estático y devolverá algo parecido a lo obtenido por la vista: un listado con el nombre del cliente, la fecha del pedido, la descripción del producto, la cantidad de productos comprado por el cliente, el precio unitario y un campo calculado con el total (cantidad x precio).


create procedure [dbo].[sp_Cliente_ProductosDesdeHasta]
	@IdCliente	int,
	@FechaDesde datetime,
	@FechaHasta datetime
as
begin

	select C.Nombre, P.FechaPedido, PR.Descripcion as Producto, LP.Cantidad, PR.Precio, (LP.Cantidad * PR.Precio) as Total
		from dbo.LineaPedido LP
		inner join dbo.Producto PR
			on LP.IdProducto = PR.IdProducto
		inner join dbo.Pedido P
			on P.IdPedido = LP.IdPedido
		inner join dbo.Cliente C
			on P.IdCliente = C.IdCliente
	where C.IdCliente = @IdCliente
		and P.FechaPedido between @FechaDesde and @FechaHasta;
end;


 

Un segundo procedimiento, esta vez dinámico, calculará el total de dinero gastado por un cliente específico entre dos fechas determinadas.


create procedure [dbo].[sp_Cliente_TotalGastadoDesdeHasta]
	@IdCliente	int,
	@FechaDesde datetime,
	@FechaHasta datetime
as
begin
	set NoCount on;

	declare @sqlSelect as nvarchar(2000);
	declare @sqlWhere  as nvarchar(1000);
	declare @sqlParams as nvarchar(1000);

	set @sqlSelect = 'select sum(LP.Cantidad * PR.Precio)
						from dbo.LineaPedido LP
							inner join dbo.Producto PR
								on LP.IdProducto = PR.IdProducto
							inner join dbo.Pedido P
								on P.IdPedido = LP.IdPedido
							inner join dbo.Cliente C
								on P.IdCliente = C.IdCliente ';
	
	set @sqlWhere = ' where C.IdCliente = @IdCliente';

	set @sqlParams = '@IdCliente int, @FechaDesde datetime, @FechaHasta datetime';

	if (@FechaDesde is not null)
	begin
		set @sqlWhere  = @sqlWhere  + ' and P.FechaPedido >= @FechaDesde ';
	end;

	if (@FechaHasta is not null)
	begin
		set @sqlWhere  = @sqlWhere  + ' and P.FechaPedido <= @FechaHasta ';
	end;

	set @sqlSelect = @sqlSelect + @sqlWhere;

	print @sqlSelect;

	exec sp_executesql @sqlSelect, @sqlParams, @IdCliente, @FechaDesde, @FechaHasta;
end;


Finalmente, crearemos una function estática que realice la misma operación que el procedimiento anterior, pero sin permitir realizar el filtrado por fecha.


create function [dbo].[fn_Cliente_TotalGastado]
(
	@IdCliente	int
)
returns money
as
begin
	declare @total money;

	select @total = sum(LP.Cantidad * PR.Precio)
		from dbo.LineaPedido LP
		inner join dbo.Producto PR
			on LP.IdProducto = PR.IdProducto
		inner join dbo.Pedido P
			on P.IdPedido = LP.IdPedido
		inner join dbo.Cliente C
			on P.IdCliente = C.IdCliente
	where C.IdCliente = @IdCliente;

	return @total;
end;

Ahora podremos utilizar la orden SqlMetal.exe para mapear aquellos elementos que nos interesen. Por ejemplo:

Esto generará el fichero TestDbContext.cs, que contendrá un conjunto de clases que simbolizarán el DataContext junto a las clases que simbolizan las tablas. El contenido del fichero tendrá un aspecto como el siguiente:

A su vez, dentro de las clases que modelan las tablas vemos cómo las propiedades están decoradas con atributos indicando que se realiza un mapeo de cada una de las columnas. Por tanto, mediante este proceso se está generando un único fichero que realiza un mapeo mediante atributos, es decir, el segundo caso que hemos estudiado.


	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_IdCliente", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
	public int IdCliente
	{
		get
		{
			return this._IdCliente;
		}
		set
		{
			if ((this._IdCliente != value))
			{
				this.OnIdClienteChanging(value);
				this.SendPropertyChanging();
				this._IdCliente = value;
				this.SendPropertyChanged("IdCliente");
				this.OnIdClienteChanged();
			}
		}
	}

 

Si optamos por escribir lo siguiente:

  • sqlmetal /server:192.168.1.100 /database:testdb /user:user /password:8e&5kiss /code:TestDb.cs /map:TestDb.xml

Observaremos que en lugar de generarse un fichero TestDbContext.cs se han creado dos ficheros: TestDb.cs y TestDb.xml.

Si abrimos el fichero TestDb.cs veremos que contiene la misma información que en el caso anterior, salvo que en este caso no aparece decorado con ningún atributo que especifique el mapeo. Por ejemplo:


	public int IdCliente
	{
		get
		{
			return this._IdCliente;
		}
		set
		{
			if ((this._IdCliente != value))
			{
				this.OnIdClienteChanging(value);
				this.SendPropertyChanging();
				this._IdCliente = value;
				this.SendPropertyChanged("IdCliente");
				this.OnIdClienteChanged();
			}
		}
	}

El motivo, como podremos imaginar, se debe a que el mapeo se ha extraído en el fichero xml que le hemos indicado, TestDb.xml, que contiene, por ejemplo, código como el siguiente:


<?xml version="1.0" encoding="utf-8"?>
<Database Name="testdb" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="dbo.Cliente" Member="Cliente">
    <Type Name="Cliente">
      <Column Name="IdCliente" Member="IdCliente" Storage="_IdCliente" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Column Name="Nombre" Member="Nombre" Storage="_Nombre" DbType="NVarChar(50) NOT NULL" CanBeNull="false" />
      <Column Name="FechaNacimiento" Member="FechaNacimiento" Storage="_FechaNacimiento" DbType="Date NOT NULL" />
      <Association Name="FK_Pedido_Cliente" Member="Pedido" Storage="_Pedido" ThisKey="IdCliente" OtherKey="IdCliente" DeleteRule="NO ACTION" />
    </Type>
  </Table>
 

Aún nos queda el tercer caso posible: indicarle como salida el fichero .dbml. Por lo tanto, si indicamos lo siguiente:

  • sqlmetal /server:192.168.1.100 /database:testdb /user:user /password:8e&5kiss /dbml:TestDb.dbml

Con esto se generará un fichero .dbml como el siguiente:

Nuevamente, si abrimos el fichero .dbml con Visual Studio, comprobaremos que se ha generado un fichero exactamente igual al que nosotros creamos manualmente a través del editor.

Por último, podemos incluir también en nuestros ficheros elementos como procedimientos, funciones o vistas añadiendo los parámetros /views, /functions y /sprocs, de la siguiente forma:

  • sqlmetal /server:192.168.1.100 /database:testdb /user:user /password:8e&5kiss /dbml:TestDb.dbml /views /functions /sprocs

El fichero .dbml habrá cambiado ligeramente, incluyendo en su definición la vista que creamos previamente (LineaProductoDetalle) junto a los dos procedimientos y la función:

Ejemplos de uso

Codifiquemos ahora un nuevo ejemplo para comprobar si podemos acceder a todos estos elementos. Comenzaremos por instanciar la clase que hereda de DataContext, encargada de realizar el trabajo sucio. También declararemos un conjunto de variables para probar nuestros procedimientos almacenados y la función.


            //Obtenemos la cadena de conexión de App.config o Web.config
            string cadenaConexion = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;

            // Instanciamos el DataContext a partir del fichero dbml
            LinqEjemplo.App_Code.Testdb db = new LinqEjemplo.App_Code.Testdb(cadenaConexion);

            // Declaramos un conjunto de parámetros de entrada
            int idCliente = 1;
            DateTime fechaDesde = new DateTime(2000, 10, 10);
            DateTime fechaHasta = new DateTime(2013, 11, 22);

 

Probamos a recuperar la tabla de clientes.


            // Obtenemos un listado de la tabla clientes
            var clientes = from cliente in db.Cliente
                           select cliente;

            // Mostramos el contenido de la tabla
            foreach (var cliente in clientes)
            {
                Console.WriteLine(string.Format("{0}, {1}, {2}",
                    cliente.IdCliente, cliente.Nombre, cliente.FechaNacimiento));
            }
            Console.WriteLine("--------------------------------------------\n");

 

Hacemos lo propio con la vista LineaProductoDetalle.


            // Obtenemos un listado de la vista LineaProductoDetalle
            var productosDetalle = from productoDetalle in db.LineaProductoDetalle
                                    select productoDetalle;

            // Mostramos el contenido de la vista
            foreach (var detalle in productosDetalle)
            {
                Console.WriteLine(string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}",
                    detalle.IdPedido, detalle.IdProducto, detalle.IdLineaPedido, detalle.Descripcion,
                    detalle.Cantidad, detalle.PrecioUnidad, detalle.PrecioTotal));
            }
            Console.WriteLine("--------------------------------------------\n");

 

Invocamos la función.


            // Invocamos una función
            decimal totalCliente1 = db.Fn_Cliente_TotalGastado(1).Value;

            // Mostramos el retorno de la función
            Console.WriteLine(string.Format("El cliente {0} ha gastado un total de {1}", idCliente, totalCliente1));
            Console.WriteLine("--------------------------------------------\n");

Turno del procedimiento almacenado, que nos devolverá un listado de campos personalizados. Como podemos observar, el Intellisense nos muestra incluso los campos que el procedimiento devuelve al ser invocado.



            // Invocamos un procedimiento almacenado que devuelve un cursor
            var productos = db.Sp_Cliente_ProductosDesdeHasta(idCliente, fechaDesde, fechaHasta);

            // Mostramos el contenido del cursor devuelto por el procedimiento almacenado
            foreach (var producto in productos)
            {
                Console.WriteLine(string.Format("{0}, {1}, {2}, {3}, {4}, {5}",
                    producto.Nombre, producto.Producto, producto.FechaPedido, producto.Precio,
                    producto.Cantidad, producto.Total));
            }
            Console.WriteLine("--------------------------------------------\n");

 

Por ultimo, recuperamos el valor recuperado por un procedimiento que devuelve un único elemento. De todos modos, al tratarse de un cursor, sería igualmente necesario realizar drill down sobre el listado (recuperamos un listado con un único elemento, pero aún así, se trata de un listado.


            // Invocamos un procedimiento almacenado que devuelve un valor
            var gastado = db.Sp_Cliente_TotalGastadoDesdeHasta(idCliente, fechaDesde, fechaHasta);

            // Mostramos el valor escalar devuelto por el procedimiento almacenado
            foreach(var valor in gastado)
                Console.WriteLine(string.Format("El cliente {0} ha gastado un total de {1}", idCliente, valor.Column1));

 

Como resultado de la invocación, aquí podemos observar el resultado:

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s