Programación

Actualizar un DataSet con PrimaryKeys autoincrementales o identidad

Por alguna razón puede que necesitemos recuperar un modelo de datos complejo, con relaciones entre tablas, a un DataSet. Durante todo el tiempo que trabajemos con este DataSet en memoria se pueden producir las tipicas operaciones CRUD (Create, Read, Update, Delete) de forma que los datos contenidos en el DataSet sean completamente diferentes a los que hay en la base de datos. Para la mayoría de casos el DataSet se maneja bastante bien a la hora de volcar los resultados de nuevo en base de datos ya que cada fila contenida en cada una de las tablas guardará su estado, mediante la enumeración DataRowState (Added, Deleted, Detached, Modified y Unchanged).

El "problema" surge cuando tenemos varios DataTable relacionados por columnas identidad o autoincrementales. En este caso mientras no vayamos a base de datos el DataSet asignará un valor propio a estas columnas que no tiene nada que ver con el que posteriormente en base de datos... ¿y donde surge el "problema"? cuando actualizamos el DataSet contra base de datos nos encontramos con que no solo tiene que hacer las operaciones necesarias CRUD contra la base de datos sino que además deberá actualizar los indices de los registros creados y a su vez de las tablas asociadas. Me he referido a problema entre comillas por que tiene una "fácil solución", también entre comillas.

Para que el DataSet se encargue de actualizar los valores de las columnas identidad y las referencias a estas en las tablas asociadas lo único que debemos hacer es asignar a la propiedad UpdateRowSource, del comando que este referenciado en la propiedad InsertCommand del DataAdapter, el valor UpdateRowSource.Both. Y ya está: las columnas se actualizarán automáticamente tanto en la tabla afectada como en las tablas relacionadas dentro del DataSet.

Pero aún nos queda el problema, sin comillas. Se trata de un problema con la forma que tienen de trabajar los DataSet en .NET cuando al insertar utilizamos el valor UpdateRowSource.Both en la propiedad UpdatedRowSource del comando que se le pasa al DataAdapter. Esta propiedad hace que al insertar un registro en una tabla con una clave autonumérica: la recoja y actualice esta clave por la que contenía el registro, dentro del DataTable en el DataSet, y, a su vez, la de todos las columnas de DataTable que tengan relaciones con esa.
El problema surge cuando el identificador devuelto de base de datos es un identificador ya existente dentro del DataTable en el DataSet. Por ejemplo: 
 

Nosotros recuperamos un conjunto de filas de las tablas A, B y C que estan relacionadas entre sí y además con la tabla D y decidimos insertar esos registros de nuevo cambiando el registro de la tabla D con el que estan relacionados. Es decir copiariamos los datos relacionados de un registro en la tabla D para otro.
Si los registros que recuperamos de la tabla A tienen los Id's 100 y 101 de la base de datos, en el DataSet se asignaran tambien estos y el Id para el siguiente registro insertado será 102.
Si insertamos otro registro en el DataSet, le asignará el Id 102, y al volcar los cambios a base de datos -no hay que olvidar que hará una inserción ya que lo único que hicimos al recuperar fue leerlos dejando los DataRow con DataRowState.Added-, se asignará para el Id 100 en el DataSet el Id 102 en base de datos. Al actualizarse automáticamente el Id del DataSet intentará ponerle 102 cosa que nos dará una excepción de clave duplicada: por el registro que habiamos insertado

Este problema es un caso muy concreto pero se nos puede reproducir en otras ocasiones como al dar de alta tantos registros más uno como los que contenga la tabla leída de base de datos, de esta forma el identificador del último elemento de la DataTable, en el DataSet, podría tratarse del identificador que la base de datos otorgué al primer elemento insertado en la tabla al volcar los datos del DataSet mediante el método Update del DataAdapter, con lo cual obtendriamos la citada excepción.

Una solución elegida es modificar la semilla de la columna del DataTable, AutoIncrementSeed, de forma que buscamos en base de datos el identificador mayor y le sumamos el número de registros recuperados de base de datos más 1. Para otros casos la solución también sería jugar con la semilla del DataTable.

Obtener un conjunto de tablas en un DataSet (.NET)

Si bien un DataAdapter crea varias tablas en el DataSet cuando encuentra múltiples conjuntos de resultados -tablas devueltas a una o varias consultas sql-, pueden existir casos en los que nos sea necesario obtener la misma funcionalidad en una consulta que devuelve el conjunto de resultado en el formato XML que devuelven las consultas ejecutadas con FOR XML. Por ejemplo la siguiente consulta SQL:

SELECT *
FROM [PLPC_Agrupacio]
FOR XML RAW, XMLDATA

Cuyo resultado será:

<Schema name="Schema3" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="row" content="empty" model="closed">
<AttributeType name="PLPC_AgrupacioId" dt:type="i4" />
<AttributeType name="PLPC_ConvocatoriaId" dt:type="uuid" />
<AttributeType name="PLPC_CodiAgrupacio" dt:type="i4" />
<AttributeType name="PLPC_AgrupacioNom" dt:type="string" />
<attribute type="PLPC_AgrupacioId" />
<attribute type="PLPC_ConvocatoriaId" />
<attribute type="PLPC_CodiAgrupacio" />
<attribute type="PLPC_AgrupacioNom" />
</ElementType>
</Schema>
<row xmlns="x-schema:#Schema3" PLPC_AgrupacioId="40" PLPC_ConvocatoriaId="081F2367-1121-4804-B66A-DAA92A89F3C8"
PLPC_CodiAgrupacio="1" PLPC_AgrupacioNom="grup 1" />
<row xmlns="x-schema:#Schema3" PLPC_AgrupacioId="41" PLPC_ConvocatoriaId="081F2367-1121-4804-B66A-DAA92A89F3C8" PLPC_CodiAgrupacio="2" PLPC_AgrupacioNom="grup 2" />
<row xmlns="x-schema:#Schema3" PLPC_AgrupacioId="42" PLPC_ConvocatoriaId="081F2367-1121-4804-B66A-DAA92A89F3C8" PLPC_CodiAgrupacio="3" PLPC_AgrupacioNom="grup 3" />

Para ello utilizaremos un DataSet y el método ReadXml de este. Mediante el parametro mode, que es del tipo XmlReadMode -una enumeración-, le indicaremos que utilize el modo ReadSchema que lee cualquier esquema en línea y carga los datos de modo que si el DataSet ya contiene un esquema se agregan las nuevas tablas -se lanza una excepción si cualquier tabla del esquema en línea ya existe en el DataSet.

SqlConnection dataBaseConnection = null;
SqlCommand dataBaseCommand;
SqlDataReader dataBaseReader = null;
StringBuilder xmlResult = new StringBuilder();
DataSet result = new DataSet ();
System.IO.StringReader reader;

dataBaseConnection = new SqlConnection(connectionString);
dataBaseCommand = new SqlCommand("PLPC_SP_ObtenirAgrupacions", dataBaseConnection);
dataBaseCommand.CommandType = CommandType.StoredProcedure;
dataBaseCommand.Parameters.Add("@PLPC_ConvocatoriaId", SqlDbType.UniqueIdentifier).Value = convocatoriaId;

dataBaseConnection.Open();
dataBaseReader = dataBaseCommand.ExecuteReader();xmlResult.Append ("<root>");
while (dataBaseReader.Read())
{
    xmlResult.Append (dataBaseReader.GetString(0));
}

xmlResult.Append ("</root>");
reader = new System.IO.StringReader (xmlResult.ToString());
result.ReadXml (reader, XmlReadMode.ReadSchema);
dataBaseReader.Close();
result.Tables[0].TableName = "PLPC_Agrupacio";
dataBaseCommand.CommandText = "PLPC_SP_ObtenirPlans";
dataBaseCommand.CommandType = CommandType.StoredProcedure;
xmlResult.Remove (0, xmlResult.Length);
xmlResult.Append ("<root>");

dataBaseReader = dataBaseCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (dataBaseReader.Read())
{
    xmlResult.Append (dataBaseReader.GetString(0));
}
xmlResult.Append ("</root>");
reader = new System.IO.StringReader (xmlResult.ToString());
result.ReadXml (reader, XmlReadMode.ReadSchema);
dataBaseReader.Close();
result.Tables[1].TableName = "PLPC_Pla";
result.DataSetName = "Convocatoria";

Con este metodo es necesario ejecutar varias consultas asi que estoy abierto a metodos mejores.

No hay que olvidar que las relaciones entre las tablas y las restricciones dentro del DataSet y las deberemos crear mediante código una vez devuelto el conjunto de resultados.

Consultas SQL para modificar el resultado de una columna en base a una condición (Transact-SQL)

Hay veces que tenemos la necesidad de obtener un resultado diferente al de una columna en base a este. Por ejemplo, podriamos necesitar que una columna nos indicase si tiene o no informado valor de una forma más legible indicando 'CIERTO' o 'FALSO'.

Para lograr esto utilizamos la función CASE de Transact-SQL:

CASE
     WHEN Boolean_expression THEN result_expression
    [ ...n ]
     [
    ELSE else_result_expression
     ]
END

Como ejemplo de uso podriamos utilizar una consulta que indicaque si un contacto dispone de telefono para contactar con él:

SELECT FirstName, Lastname, 'Has Telephone to Contact' =
     CASE
          WHEN TelephoneNumber IS NULL THEN CAST (0 AS BIT)
          ELSE CAST (1 AS BIT)
     END