martes, 10 de agosto de 2010
Consultas con parámetros opcionales sin SQL dinámico
Etiquetas:
linq from sql,
parameter opcional
Tiene que hacer una aplicación que se consulta a una tabla y que los usuario se le presentan 3 campos que se usarán para filtrar los resultados que se muestran al usuario. Todos ellos son campos opcionales. La pregunta es cuál es la mejor aplicación de la solución. ¿Soluciones?
1.) SQL dinámico
Es la opción más común, lo malo es que propensa a SQL injection y propenso a errores,
Sé que usted puede utilizar parámetros con que hasta cierto punto, pero creo que todavía no es una buena
elección por las siguientes razones.
1.1 Difícil de leer de leer
1.2 Dificil de formateo, depuración, etc.
Por estas razones, le recomiendo mantenerse alejado de esta opción.
2.) Stored Procedure
Le recomiendo usar la técnica de controles Or y NULL en la cláusula where. A continuación se muestra un ejemplo de un fragmento de T-SQL.
3.) LINQ
Puede utilizar LINQ to SQL o LINQ to Entities para resolver el problema
Es la mejor opción es una manera extremadamente fácil de implementar la funcionalidad deseada, adicionalmente LINQ tiene la característica del diseño protegido de inyección de SQL. Sin embargo esto no quiere decir que no utilizar un procedimiento almacenado.
Código:
Nota:
para usar like tiene que hacer un using System.Data.Linq.SqlClient;
Código:
Create proc BuscarClientes
@ProductID int= NULL ,
@ProductName varchar(40)= NULL ,
@CategoryID int= NULL
as
SELECT ProductID,ProductName,CategoryID,UnitPrice
FROM dbo.Products
WHERE
((@ProductID is null) or (@ProductID=ProductID))
AND
((@ProductName is null) or (ProductName like '%' + @ProductName + '%'))
AND
((@CategoryID is null) or (@CategoryID =CategoryID))
Formas de usarlo:
EXEC BuscarClientes 1,null,null
EXEC BuscarClientes null,'queso',null
EXEC BuscarClientes null,null,1
EXEC BuscarClientes
protected void Button1_Click(object sender, EventArgs e)
{
int? id = null;
string nombre = null;
int? cat = null;
if(TextBox1.Text!="")
id = Convert.ToInt32(TextBox1.Text);
if (TextBox2.Text != "")
nombre =TextBox2.Text;
if (TextBox3.Text != "")
cat = Convert.ToInt32(TextBox3.Text);
//List
List
GridView1.DataSource = pro;
GridView1.DataBind();
}
public List
{
NortwindDataContext db = new NortwindDataContext();
var query = from p in db.Products
select p;
if (id != null)
{
query = query.Where(p => p.ProductID == id);
}
if (nombre != null)
{
query = query.Where(p => SqlMethods.Like(p.ProductName, "%" + nombre + "%"));
}
if (cat != null)
{
query = query.Where(p => p.CategoryID == cat);
}
return query.ToList
}
Suscribirse a:
Comentarios de la entrada (Atom)
5 comentarios:
En el ejemplo del procedimiento almacenado la cláusula WHERE tiene algunas cosas al revés, debe ir:
WHERE
(@ProductID IS NULL OR ProductID = @ProductID)
AND
(@ProductName IS NULL OR ProductName LIKE @Nombre)
AND
(@CategoryID IS NULL OR CategoryID = @CategoryID)
Además, las cadenas '%' se pueden agregar en la aplicación para no tener que colocarlas en el procedimiento almacenado.
También es mejor usar string.Empty en lugar de ""
y en el query de Linq, agregar .AsParallel();
Hola ,no sabes como hacer para llamar desde vb.net a un procedimiento con este tipo de parametros opcionales?
Yo lo resulevo de este modo:
En la interfase de usuario, lo fuerzo a filtrar información y en base a eso, genera algo como:
Provincia in ('Mendoza','Cordoba','Santa Fe') and
Fecha between '20140101' and '20140131'
etc.
Luego tengo un store que recibe nombre de tabla, o vista que debe ejecutar y 9 parametros que pueden no ser enviados, y el valor default es null
cada parametro, es una condicion. Ejemplo:
@param1 = Provincia in ('Mendoza','Cordoba','Santa Fe')
@param2 = Fecha between '20140101' and '20140131'
etc.
Luego armo el query completo y lo ejecuto con:
execute (@query completo con where incluido)
y listo,
se convierte en dinamico en serio, lo resuelve el motor de base de datos, que para eso es potente y me funciona muy bien !!!
Saludos
Eduardo
Publicar un comentario