.net - plan - Consultas parametrizadas con condiciones LIKE e IN

optimizar inner join sql server

Question

Las consultas parametrizadas en .Net siempre se ven así en los ejemplos:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID = @categoryid
", 
   conn);
comm.Parameters.Add("@categoryid", SqlDbType.Int);
comm.Parameters["@categoryid"].Value = CategoryID;

Pero estoy corriendo hacia una pared de ladrillos tratando de hacer lo siguiente:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID IN (@categoryids) 
      OR  name LIKE ''%@name%''
", 
   conn);
comm.Parameters.Add("@categoryids", SqlDbType.Int);
comm.Parameters["@categoryids"].Value = CategoryIDs;
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = Name;

Dónde

• CategoryIDs es una lista de números separados por comas "123,456,789" (sin comillas)
• El nombre es una cadena, posiblemente con comillas simples y otros caracteres incorrectos

¿Cuál es la sintaxis correcta para esto?

Answer 1

Este enfoque no funcionará. Período.

La cláusula IN espera una lista de parámetros en sí misma, de modo que cuando le vincula un parámetro, tiene la posibilidad de pasar un solo valor.

Construya su cadena de extracto de cuenta de forma dinámica, con la cantidad exacta de marcadores de posición de cláusula IN individuales que desea pasar, y luego agregue parámetros y vincule valores en un bucle.

Answer 2

Necesita el "%" en valor del parámetro sql.

SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID IN (@categoryid1, @categoryid2) OR name LIKE @name", conn);
comm.Parameters.Add("@categoryid1", SqlDbType.Int);
comm.Parameters["@categoryid1"].Value = CategoryID[0];
comm.Parameters.Add("@categoryid2", SqlDbType.Int);
comm.Parameters["@categoryid2"].Value = CategoryID[1];
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = "%" + Name + "%";

Answer 3

Supongamos que tiene los ID de categoría en una matriz de enteros y Name es una cadena. El truco consiste en crear el texto del comando para permitirle ingresar todos sus identificadores de categoría como parámetros individuales y construir la coincidencia difusa para el nombre. Para hacer lo anterior, usamos un bucle para construir una secuencia de nombres de parámetros @ p0 a través de @ pN-1, donde N es el número de identificadores de categoría en el conjunto. Luego, construimos un parámetro y lo agregamos al comando con la identificación de categoría asociada como el valor para cada parámetro con nombre. Luego usamos la concatenación en el nombre en la consulta misma para permitir la búsqueda difusa en el nombre.

string Name = "someone";
int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617,
                                1618, 1619, 1620, 1951, 1952,
                                1953, 1954, 1955, 1972, 2022 };

SqlCommand comm = conn.CreateCommand();

string[] parameters = new string[categoryIDs.Length];
for(int i=0;i<categoryIDs.Length;i++)
{
   parameters[i] = "@p"+i;
   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
}
comm.Parameters.AddWithValue("@name",$"%{Name}%");
comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";
comm.CommandText += " OR name LIKE @name";

Esta es una consulta completamente parametrizada que debería hacer feliz a tu DBA. Sospecho que, dado que son enteros, no constituiría un gran riesgo de seguridad simplemente construir el texto de comando directamente con los valores, sin dejar de parametrizar el nombre. Si los ID de su categoría están en una matriz de cadenas, simplemente divida la matriz en comas, conviértalas a un entero y guárdelas en la matriz de enteros.

Nota: Digo array y lo uso en el ejemplo, pero debería funcionar para cualquier colección, aunque su iteración probablemente sea diferente.

Idea original de http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

Answer 4

no estoy seguro de si esta es la manera correcta, pero es una forma en que lo he hecho antes

list templist = nueva lista

comm.Parameters.Add ("@categories", SqlDbType.varchar); comm.Parameters ["@categorids"]. value = string.join (",", templist.toarray ())