Pass Array Parameter in SqlCommand

Question

I am trying to pass array parameter to SQL commnd in C  like below  but it does not work  Does anyone meet it before    string sqlCommand    SELECT   from TableA WHERE Age IN   Age    SqlConnection sqlCon   new SqlConnection connectString   SqlCommand sqlComm   new SqlCommand    sqlComm Connection   sqlCon  sqlComm CommandType   System Data CommandType Text  sqlComm CommandText   sqlCommand  sqlComm CommandTimeout   300  sqlComm Parameters Add   Age   SqlDbType NVarChar   StringBuilder sb   new StringBuilder    foreach  ListItem item in ddlAge Items         if  item Selected                  sb Append item Text                   sqlComm Parameters   Age   Value   sb ToString   TrimEnd

User · Answer

Use  AddWithValue    So   sqlComm Parameters AddWithValue   Age   sb ToString   TrimEnd         Alternatively  you could use this   sqlComm Parameters Add      new SqlParameter   Age   sb ToString   TrimEnd         SqlDbType   SqlDbType  NVarChar            Your total code sample will look at follows then   string sqlCommand    SELECT   from TableA WHERE Age IN   Age    SqlConnection sqlCon   new SqlConnection connectString   SqlCommand sqlComm   new SqlCommand    sqlComm Connection   sqlCon  sqlComm CommandType   System Data CommandType Text  sqlComm CommandText   sqlCommand  sqlComm CommandTimeout   300   StringBuilder sb   new StringBuilder    foreach  ListItem item in ddlAge Items         if  item Selected                  sb Append item Text                   sqlComm Parameters AddWithValue   Age   sb ToString   TrimEnd            OR     sqlComm Parameters Add new SqlParameter   Age   sb ToString   TrimEnd         SqlDbType   SqlDbType  NVarChar

User · Answer

If you are using MS SQL Server 2008 and above you can use table-valued parameters like described here http   www sommarskog se arrays-in-sql-2008 html   1  Create a table type for each parameter type you will be using  The following command creates a table type for integers   create type int32 id list as table  id int not null primary key    2  Implement helper methods  public static SqlCommand AddParameter lt T gt  this SqlCommand command  string name  IEnumerable lt T gt  ids      var parameter   command CreateParameter             parameter ParameterName   name    parameter TypeName   typeof T  Name ToLowerInvariant       id list     parameter SqlDbType   SqlDbType Structured    parameter Direction   ParameterDirection Input     parameter Value   CreateIdList ids      command Parameters Add parameter     return command     private static DataTable CreateIdList lt T gt  IEnumerable lt T gt  ids      var table   new DataTable      table Columns Add  id   typeof  T       foreach  var id in ids          table Rows Add id          return table      3  Use it like this  cmd CommandText    select   from TableA where Age in  select id from  age     cmd AddParameter   age   new     1 2 3 4 5

User · Answer

I want to propose another way  how to solve limitation with IN operator   For example we have following query  select   from Users U WHERE U ID in   ids    We want to pass several IDs to filter users  Unfortunately it is not possible to do with C  in easy way  But I have fount workaround for this by using  string split  function  We need to rewrite a bit our query to following   declare  ids nvarchar max     1 2 3   SELECT   FROM Users as U CROSS APPLY string split  ids       as UIDS WHERE U ID   UIDS value   Now we can easily pass one parameter enumeration of values separated by comma

User · Answer

Here is a minor variant of Brian s answer that someone else may find useful  Takes a List of keys and drops it into the parameter list     keyList is a List lt string gt  System Data SqlClient SqlCommand command   new System Data SqlClient SqlCommand    string sql    SELECT fieldList FROM dbo tableName WHERE keyField in     int i   1  foreach  string key in keyList        sql   sql     key    i            command Parameters AddWithValue   key    i  key       i      sql   sql TrimEnd

User · Answer

If you can use a tool like  dapper   this can be simply   int   ages     20  21  22       could be any common list-like type var rows   connection Query lt YourType gt   SELECT   from TableA WHERE Age IN  ages             new   ages    ToList      Dapper will handle unwrapping this to individual parameters for you

User · Answer

You will need to add the values in the array one at a time   var parameters   new string items Length   var cmd   new SqlCommand    for  int i   0  i  lt  items Length  i          parameters i    string Format   Age 0    i       cmd Parameters AddWithValue parameters i   items i       cmd CommandText   string Format  SELECT   from TableA WHERE Age IN   0     string Join       parameters    cmd Connection   new SqlConnection connStr     UPDATE  Here is an extended and reusable solution that uses Adam s answer along with his suggested edit  I improved it a bit and made it an extension method to make it even easier to call    public static class SqlCommandExt             lt summary gt          This will add an array of parameters to a SqlCommand  This is used for an IN statement          Use the returned value for the IN part of your SQL call   i e  SELECT   FROM table WHERE field IN   paramNameRoot             lt  summary gt           lt param name  cmd  gt The SqlCommand object to add parameters to  lt  param gt           lt param name  paramNameRoot  gt What the parameter should be named followed by a unique value for each value  This value surrounded by    in the CommandText will be replaced  lt  param gt           lt param name  values  gt The array of strings that need to be added as parameters  lt  param gt           lt param name  dbType  gt One of the System Data SqlDbType values  If null  determines type based on T  lt  param gt           lt param name  size  gt The maximum size  in bytes  of the data within the column  The default value is inferred from the parameter value  lt  param gt      public static SqlParameter   AddArrayParameters lt T gt  this SqlCommand cmd  string paramNameRoot  IEnumerable lt T gt  values  SqlDbType  dbType   null  int  size   null                   An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually              Each item in the array will end up being it s own SqlParameter so the return value for this must be used as part of the            IN statement in the CommandText                      var parameters   new List lt SqlParameter gt             var parameterNames   new List lt string gt             var paramNbr   1          foreach  var value in values                        var paramName   string Format    0  1    paramNameRoot  paramNbr                 parameterNames Add paramName               SqlParameter p   new SqlParameter paramName  value               if  dbType HasValue                  p SqlDbType   dbType Value              if  size HasValue                  p Size   size Value              cmd Parameters Add p               parameters Add p                      cmd CommandText   cmd CommandText Replace       paramNameRoot        string Join      parameterNames             return parameters ToArray               It is called like this     var cmd   new SqlCommand  SELECT   FROM TableA WHERE Age IN   Age      cmd AddArrayParameters  Age   new int     1  2  3       Notice the   Age   in the sql statement is the same as the parameter name we are sending to AddArrayParameters  AddArrayParameters will replace the value with the correct parameters

User · Answer

Overview  Use the DbType to set the parameter type   var parameter   new SqlParameter    parameter ParameterName     UserID   parameter DbType   DbType Int32  parameter Value   userID ToString     var command   conn CreateCommand   command Parameters Add parameter   var reader   await command ExecuteReaderAsync

User · Answer

Since there is a method on  SqlCommand Parameters AddWithValue parameterName  value    it might be more convenient to create a method accepting a parameter  name  to replace and a list of values  It is not on the Parameters level  like AddWithValue  but on command itself so it s better to call it AddParametersWithValues and not just AddWithValues   query   SELECT   from TableA WHERE Age IN   age    usage   sqlCommand AddParametersWithValues   age   1  2  3     the extension method   public static class SqlCommandExtensions       public static void AddParametersWithValues lt T gt  this SqlCommand cmd   string parameterName  params T   values                var parameterNames   new List lt string gt             for int i   0  i  lt  values Count    i                          var paramName      param    i              cmd Parameters AddWithValue paramName  values ElementAt i                parameterNames Add paramName                      cmd CommandText   cmd CommandText Replace parameterName  string Join      parameterNames

User · Answer

I wanted to expand on the answer that Brian contributed to make this easily usable in other places        lt summary gt      This will add an array of parameters to a SqlCommand  This is used for an IN statement      Use the returned value for the IN part of your SQL call   i e  SELECT   FROM table WHERE field IN  returnValue        lt  summary gt       lt param name  sqlCommand  gt The SqlCommand object to add parameters to  lt  param gt       lt param name  array  gt The array of strings that need to be added as parameters  lt  param gt       lt param name  paramName  gt What the parameter should be named  lt  param gt  protected string AddArrayParameters SqlCommand sqlCommand  string   array  string paramName           An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually          Each item in the array will end up being it s own SqlParameter so the return value for this must be used as part of the        IN statement in the CommandText              var parameters   new string array Length       for  int i   0  i  lt  array Length  i                  parameters i    string Format    0  1    paramName  i           sqlCommand Parameters AddWithValue parameters i   array i               return string Join       parameters       You can use this new function as follows   SqlCommand cmd   new SqlCommand     string ageParameters   AddArrayParameters cmd  agesArray   Age    sql   string Format  SELECT   FROM TableA WHERE Age IN   0     ageParameters    cmd CommandText   sql     Edit  Here is a generic variation that works with an array of values of any type and is usable as an extension method    public static class Extensions       public static void AddArrayParameters lt T gt  this SqlCommand cmd  string name  IEnumerable lt T gt  values                  name   name StartsWith        name         name          var names   string Join       values Select  value  i    gt                 var paramName   name   i               cmd Parameters AddWithValue paramName  value                return paramName                         cmd CommandText   cmd CommandText Replace name  names              You can then use this extension method as follows    var ageList   new List lt int gt    1  3  5  7  9  11    var cmd   new SqlCommand    cmd CommandText    SELECT   FROM MyTable WHERE Age IN   Age        cmd AddArrayParameters  Age   ageList     Make sure you set the CommandText before calling AddArrayParameters   Also make sure your parameter name won t partially match anything else in your statement  i e   AgeOfChild

User · Answer

try it like this  StringBuilder sb   new StringBuilder     foreach  ListItem item in ddlAge Items           if  item Selected                     string sqlCommand    SELECT   from TableA WHERE Age IN   Age               SqlConnection sqlCon   new SqlConnection connectString              SqlCommand sqlComm   new SqlCommand               sqlComm Connection   sqlCon             sqlComm CommandType   System Data CommandType Text             sqlComm CommandText   sqlCommand             sqlComm CommandTimeout   300             sqlComm Parameters Add   Age   SqlDbType NVarChar             sb Append item Text                    sqlComm Parameters   Age   Value   sb ToString   TrimEnd

User · Answer

Passing an array of items as a collapsed parameter to the WHERE  IN clause will fail since query will take form of WHERE Age IN   11  13  14  16     But you can pass your parameter as an array serialized to XML or JSON   Using nodes   method   StringBuilder sb   new StringBuilder     foreach  ListItem item in ddlAge Items    if  item Selected      sb Append   lt age gt     item Text     lt  age gt        actually it s xml-ish  sqlComm CommandText     SELECT   from TableA WHERE Age IN       SELECT Tab col value       int   as Age from  Ages nodes   age   as Tab col     sqlComm Parameters Add   Ages   SqlDbType NVarChar   sqlComm Parameters   Ages   Value   sb ToString      Using OPENXML method   using System Xml Linq      XElement xml   new XElement  Ages     foreach  ListItem item in ddlAge Items    if  item Selected      xml Add new XElement  age   item Text    sqlComm CommandText     DECLARE  idoc int      EXEC sp xml preparedocument  idoc OUTPUT   Ages      SELECT   from TableA WHERE Age IN       SELECT Age from OPENXML  idoc    Ages age   with  Age int  text         EXEC sp xml removedocument  idoc   sqlComm Parameters Add   Ages   SqlDbType Xml   sqlComm Parameters   Ages   Value   xml ToString      That s a bit more on the SQL side and you need a proper XML  with root    Using OPENJSON method  SQL Server 2016     using Newtonsoft Json      List lt string gt  ages   new List lt string gt      foreach  ListItem item in ddlAge Items    if  item Selected      ages Add item Text    sqlComm CommandText     SELECT   from TableA WHERE Age IN       select value from OPENJSON  Ages     sqlComm Parameters Add   Ages   SqlDbType NVarChar   sqlComm Parameters   Ages   Value   JsonConvert SerializeObject ages     Note that for the last method you also need to have Compatibility Level at 130

[c#] Pass Array Parameter in SqlCommand

Examples related to c#

Examples related to tsql