c# - pass - sql server parameters array
Pasar List<> a SQL Stored Procedure (8)
¿Por qué no utilizar un parámetro con valores de tabla? http://msdn.microsoft.com/en-us/library/bb675163.aspx
A menudo tuve que cargar varios elementos a un registro particular en la base de datos. Por ejemplo: una página web muestra elementos para incluir en un único informe, todos los cuales son registros en la base de datos (el informe es un registro en la tabla Informe, los elementos son registros en la tabla Artículo). Un usuario selecciona elementos para incluir en un solo informe a través de una aplicación web, y digamos que seleccionan 3 elementos y los envían. El proceso agregará estos 3 elementos a este informe agregando registros a una tabla llamada ReportItems (ReportId, ItemId).
Actualmente, haría algo como esto en el código:
public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
Database db = DatabaseFactory.CreateDatabase(connStr);
string sqlCommand = "AddItemsToReport"
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
string items = "";
foreach (int i in itemList)
items += string.Format("{0}~", i);
if (items.Length > 0)
items = items.Substring(0, items.Length - 1);
// Add parameters
db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
db.AddInParameter(dbCommand, "Items", DbType.String, perms);
db.ExecuteNonQuery(dbCommand);
}
y esto en el procedimiento Almacenado:
INSERT INTO ReportItem (ReportId,ItemId)
SELECT @ReportId,
Id
FROM fn_GetIntTableFromList(@Items,''~'')
Donde la función devuelve una tabla de enteros de una columna.
Mi pregunta es esta: ¿hay una mejor manera de manejar algo como esto? Tenga en cuenta que no estoy preguntando sobre la normalización de la base de datos ni nada de eso, mi pregunta se relaciona específicamente con el código.
Aquí hay una explicación muy clara para Table Valued Parameters de sqlteam.com: Table Value Valts
Hace lo que ya tiene, pasa una cadena delimitada y luego analiza el valor de una tabla, o la otra opción pasa en un widge de XML y casi de la misma manera:
Todavía no he tenido la oportunidad de ver SQL 2008 para ver si agregaron alguna funcionalidad nueva para manejar este tipo de cosas.
Si ir a SQL Server 2008 es una opción para usted, hay una nueva característica llamada "Parámetros con valores de tabla" para resolver este problema exacto.
Consulte más detalles sobre TVP here y here o simplemente solicite a Google los "parámetros de tabla de SQL Server 2008": encontrará mucha información y muestras.
Muy recomendado: si puede pasar a SQL Server 2008 ...
Su lógica de unión de cadena probablemente se pueda simplificar:
string items =
string.Join("~", itemList.Select(item=>item.ToString()).ToArray());
Eso te ahorrará algo de concatenación de cadenas, que es caro en .Net.
No creo que haya ningún problema con la forma en que está guardando los artículos. Está limitando los viajes a la base de datos, lo cual es algo bueno. Si su estructura de datos fuera más compleja que una lista de ints, sugeriría XML.
Nota: Me preguntaron en los comentarios si esto nos ahorraría cualquier concatenación de cadenas (lo hace indeeed). Creo que es una excelente pregunta y me gustaría seguir con eso.
Si quita una cadena abierta. Conéctese con Reflector y verá que Microsoft está usando un par de técnicas inseguras (en el sentido de .Net), que incluyen el uso de un puntero y una estructura llamada UnSafeCharBuffer. Lo que están haciendo, cuando realmente lo reducen, es usar punteros para caminar sobre una cuerda vacía y construir la unión. Recuerde que la razón principal por la cual la concatenación de cadenas es tan costosa en .Net es que un nuevo objeto de cadena se coloca en el montón para cada concatenación, porque la cadena es inmutable. Esas operaciones de memoria son costosas. String.Join (..) es esencialmente asignar la memoria una vez, luego operar sobre ella con un puntero. Muy rapido.
Un problema potencial con su técnica es que no maneja listas muy grandes: puede exceder la longitud máxima de cadena para su base de datos. Utilizo un método auxiliar que concatena los valores enteros en una enumeración de cadenas, cada una de las cuales es menor que un máximo especificado (la siguiente implementación también comprueba y elimina de forma opcional los ID duplicados):
public static IEnumerable<string> ConcatenateValues(IEnumerable<int> values, string separator, int maxLength, bool skipDuplicates)
{
IDictionary<int, string> valueDictionary = null;
StringBuilder sb = new StringBuilder();
if (skipDuplicates)
{
valueDictionary = new Dictionary<int, string>();
}
foreach (int value in values)
{
if (skipDuplicates)
{
if (valueDictionary.ContainsKey(value)) continue;
valueDictionary.Add(value, "");
}
string s = value.ToString(CultureInfo.InvariantCulture);
if ((sb.Length + separator.Length + s.Length) > maxLength)
{
// Max length reached, yield the result and start again
if (sb.Length > 0) yield return sb.ToString();
sb.Length = 0;
}
if (sb.Length > 0) sb.Append(separator);
sb.Append(s);
}
// Yield whatever''s left over
if (sb.Length > 0) yield return sb.ToString();
}
Entonces lo usa algo así como:
using(SqlCommand command = ...)
{
command.Connection = ...;
command.Transaction = ...; // if in a transaction
SqlParameter parameter = command.Parameters.Add("@Items", ...);
foreach(string itemList in ConcatenateValues(values, "~", 8000, false))
{
parameter.Value = itemList;
command.ExecuteNonQuery();
}
}
Vea http://www.sommarskog.se/arrays-in-sql-2005.html para una discusión detallada de este tema y los diferentes enfoques que podría usar.
Consultar un campo único para valores múltiples en un procedimiento almacenado
http://www.norimek.com/blog/post/2008/04/Query-a-Single-Field-for-Multiple-Values-in-a-Stored-Procedure.aspx