Database Helpers for IN Clauses ---how it works for multi-placeholder? RSS

• • Reply
  

  haixing

  Member

  2 Points

  16 Posts

  Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 05, 2012 01:48 PM|LINK

  Hi,

  I've found this code for IN Clauses written by Mike:

  public static class DatabaseExtensions
  {
      public static IEnumerable<dynamic> QueryIn(this Database db, string commandText, string values)
      {
          if(string.IsNullOrEmpty(values))
              throw new ArgumentException("Value cannot be null or an empty string", "values");
          var temp = values.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
          var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
          var inclause = string.Join(",", parms);
          return db.Query(string.Format(commandText, inclause), temp);
      }
      
      public static int ExecuteIn(this Database db, string commandText, string values)
      {
          if(string.IsNullOrEmpty(values))
              throw new ArgumentException("Value cannot be null or an empty string", "values");
          var temp = values.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
          var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
          var inclause = string.Join(",", parms);
          return db.Execute(string.Format(commandText, inclause), temp);
      }
  }

  @{     var db = Database.Open("Books");
      var categories = db.Query("Select CategoryId, Category FROM Categories");
      if(IsPost){
          var sql = "SELECT Category FROM Categories WHERE CategoryId IN ({0})";
          categories = db.QueryIn(sql, Request["categoryId"]);
      }
  }

  Link: http://www.mikesdotnetting.com/Article/156/WebMatrix-Database-Helpers-for-IN-Clauses

  But this code can be used just for one placeholder, how can we change it for two placeholder ?

  Thanks!

• • Reply
  

  Mikesdotnett...

  All-Star

  154927 Points

  19867 Posts

  Moderator

  MVP

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 07, 2012 09:19 AM|LINK

  Here's a way of handling an extra placeholder: 

  var input = "1,2,3,4,5,6,7,8";
  var extraPlaceholderValue = "some value";
  
  var temp = input.Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries).ToList();
  var parms = temp.Select((s, i) => "@" + i.ToString());
  var inclause = string.Join(",", parms);
  var sql = String.Format("SELECT CategoryName FROM Categories WHERE CategoryId IN ({0})", inclause);
  
  var extraPlaceholder = "@" + temp.Count();
  temp.Add(extraPlaceholderValue);
  sql += " AND SomeValue = " + extraPlaceholder;
  var data = db.Query(sql, temp);

  
  
  

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  haixing

  Member

  2 Points

  16 Posts

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 09, 2012 11:56 AM|LINK

  Thanks so much mike! I follow your Tutorial but i have a little error, this is the code i've written:

  var db = Database.Open("test_elec") ;
  
      var check1 = Request["checkbox1"];
      var check2 = Request["checkbox2"];
  
      var temp = Request["checkbox1"].Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
      var parms = temp.Select((s, i) => "@" + i.ToString());
      var inclause = string.Join(",", parms);
  
      var selectCommand = String.Format("SELECT FT_SOUSTESTS.VALUE "+
                          "FROM IMPLANTS INNER JOIN F_TESTS ON IMPLANTS.ID_IMPLANT = F_TESTS.ID_IMPLANT "+
                                     "INNER JOIN FT_SOUSTESTS ON F_TESTS.ID_TEST = FT_SOUSTESTS.ID_TEST "+
                          "WHERE IMPLANTS.NUM_SERIE_G IN ({0})",inclause);
  
      var check2Placeholder = "@"+ temp.Count();
      temp.ToList().Add(check2);
      
      selectCommand += "AND FT_SOUSTESTS.N_SST IN ({0})" + check2Placeholder;
  
      var selectedData = db.Query(selectCommand,temp);

  And i have a error : System.Data.SqlClient.SqlException: Incorrect syntax near '0'. 
  For me everything looks correct, and i don't know where is the problem. Anyone can help me ? Thanks

• • Reply
  

  Mikesdotnett...

  All-Star

  154927 Points

  19867 Posts

  Moderator

  MVP

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 09, 2012 05:17 PM|LINK

  I didn't realise you wanted a second IN clause. You need to generate the second one in the same way as the first.

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  haixing

  Member

  2 Points

  16 Posts

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 10, 2012 09:06 AM|LINK

  Thanks mike,

  But if i generate the second one, the SQL query doesn't work :(

  var temp = Request["checkbox1"].Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
      var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
      var inclause = string.Join(",", parms);
  
      var temp2 = Request["checkbox2"].Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
      var parms2 = temp2.Select((s, i) => "@" + i.ToString()).ToArray();
      var inclause2 = string.Join(",", parms2);
  
      var selectCommand = String.Format("SELECT FT_SOUSTESTS.VALUE "+
                          "FROM IMPLANTS INNER JOIN F_TESTS ON IMPLANTS.ID_IMPLANT = F_TESTS.ID_IMPLANT "+
                              "INNER JOIN FT_SOUSTESTS ON F_TESTS.ID_TEST = FT_SOUSTESTS.ID_TEST "+
                          "WHERE IMPLANTS.NUM_SERIE_G IN ({0}) AND FT_SOUSTESTS.N_SST IN
                                 ({1})",inclause,inclause2);

• • Reply
  

  haixing

  Member

  2 Points

  16 Posts

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 10, 2012 01:23 PM|LINK

  I have this error: 

  "no mapping exists from object type system.string to a known managed provider native type"

• • Reply
  

  Mikesdotnett...

  All-Star

  154927 Points

  19867 Posts

  Moderator

  MVP

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 11, 2012 06:51 AM|LINK

  Almost - you need to make sure that the second parameter array generates parameter markers that go on from the highest number in the first array. So if your code has already generate @0,@1,@2 in the first set, your second one needs to start at @3. You can use parms.Length to do that. Then you need to combine both sets of parameter values into one array at the end:

  var temp1 = Request["checkbox1"].Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
  var parms1 = temp1.Select((s, i) => "@" + i.ToString()).ToArray();
  var inclause1 = string.Join(",", parms1);
  
  var temp2 = Request["checkbox2"].Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
  var parms2 = temp2.Select((s, i) => "@" + (i + parms1.Length).ToString()).ToArray();
  var inclause2 = string.Join(",", parms2);
  
  var selectCommand = String.Format(@"SELECT FT_SOUSTESTS.VALUE
                          FROM IMPLANTS INNER JOIN F_TESTS ON IMPLANTS.ID_IMPLANT = F_TESTS.ID_IMPLANT
                          INNER JOIN FT_SOUSTESTS ON F_TESTS.ID_TEST = FT_SOUSTESTS.ID_TEST
                          WHERE IMPLANTS.NUM_SERIE_G IN ({0}) AND FT_SOUSTESTS.N_SST IN
                          ({1})",inclause1,inclause2);
                          
  var temp = new string[temp1.Length + temp2.Length];
  temp1.CopyTo(temp, 0);
  temp2.CopyTo(temp, temp1.Length);
  
  var data = db.Query(selectCommand,temp);

  
  
  

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Marked as answer by haixing on Jul 11, 2012 07:53 AM
• • Reply
  

  haixing

  Member

  2 Points

  16 Posts

  Re: Database Helpers for IN Clauses ---how it works for multi-placeholder?

  Jul 11, 2012 07:46 AM|LINK

  Thanks very much Mike, il works very well. Sorry i'm a newbie  ! Thanks again !

  • Edited by haixing on Aug 06, 2012 12:03 PM