Crear una consulta SQL con una condición de filtrado dinámico es algo que tarde o temprano sale a la palestra
En la solución que se muestra a continuación, se usa un objeto de tipo ExpandoObject que permite agregar propiedades en tiempo de ejecución. Lo explica muy bien @eiximenis en el post Var, object y dynamic
Si a ExpandoObject le sumamos Dapper, la solución es segura contra SQL injection y flexible en cuanto a su confección
En el ejemplo he procurado que sea vea que podemos tanto agregar propierdades de forma dinámica por el mero hecho de asignar un valor, así como castear el ExpandoObject a un dicccionario para agregar propiedades de forma dinámica
Primero el código SQL para crear una tabla y que el ejemplo funcione
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE [dbo].[Table_1]( [Id] [int] NOT NULL, [TenantId] [int] NOT NULL, [C1] [int] NULL, [C2] [nvarchar](50) NULL, [C3] [datetime2](7) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [Id] ASC) |
Ahora el código
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | using System;using System.Collections.Generic;using System.Data.SqlClient;using System.Dynamic;using System.Linq;using Dapper;namespace ConsoleApp1{ class Program { static void Main(string[] args) { QueryExample(new Dictionary<string, object>() { { "C1", 1 }, { "C2", "Foo" }, { "C3", DateTime.Now } }); } private static void QueryExample(IDictionary<string, object> criterias) { const string connectionString = @"Server=(LocalDB)\MSSQLLocalDB;Database=Example;Trusted_Connection=True;"; var sql = "SELECT * FROM Table_1 WHERE TenantId = @TenantId"; var columnsWhitelist = new[] { "C1", "C2", "C3" }; foreach (var criteria in criterias) { if (!columnsWhitelist.Contains(criteria.Key)) { throw new ArgumentException($"{criteria.Key} is not allowed as column name", nameof(criteria)); } sql += $" AND {criteria.Key} = @{criteria.Key}"; } dynamic parameters = new ExpandoObject(); parameters.TenantId = 1; var dictionary = (IDictionary<string, object>)parameters; foreach (var criteria in criterias) { dictionary.Add(criteria.Key, criteria.Value); } using (var connection = new SqlConnection(connectionString)) { connection.Query(sql, (ExpandoObject)parameters); } Console.ReadKey(); } }} |
El SQL que se ejecuta finalmente es el siguiente
1 | exec sp_executesql N'SELECT * FROM Table_1 WHERE TenantId = @TenantId AND C1 = @C1 AND C2 = @C2 AND C3 = @C3',N'@TenantId int,@C1 int,@C2 nvarchar(4000),@C3 datetime',@TenantId=1,@C1=1,@C2=N'Foo',@C3='2018-01-31 18:58:56.823' |
Un saludo!
