Select Query with Where condition depending on list values in asp.net

Question

I've an sql table. I've also a list in my Asp.Net code (C#).

public static List listColumns = new List();

And listColumns contains 1,2,3...10 randomly. For example it can contains 1, 5, 6, 9. I want to make a query with this conditions. So, If the list has these values, my query should be:

SELECT * FROM Table WHERE id=1 or id=5 or id=6 or id=9

The listColumns may contains different values and has different counts. How can I do this query regularly?

is it that `((System.Web.UI.MobileControls.List)listColumns).ListItem.Value` can have a string value with comma separated int types? Please show how the listColumns object contains these random int's. — JP Hellemons, Oct 20 '14 at 13:33
But the user can only select one item at a time or is it multi-select? can you show us some more code? — JP Hellemons, Oct 20 '14 at 14:18
list.Items.Clear(); string values = DropDownList4.SelectedValue; string[] words = values.Split(','); foreach (string s in words) if (s != "" && s != string.Empty && s != null) list.Items.Add(s); — 1teamsah, Oct 20 '14 at 14:20
List is filled what is selected from dropdownlist. User can select anything from dropdown. Dropdown has some values like this: "1,2,6,9", "1,5,6" or "1,2,3,4,5,6,7,8,9". etc. — 1teamsah, Oct 20 '14 at 14:23

Anik Islam Abhi · Answer 1 · 2015-11-12T07:31:27.763

3

 //assume a list of int value name listColumns
  var listColumns = new List<int>() { 1, 5, 9 };
  var sb = new StringBuilder();
  sb.Append("SELECT * FROM Table");

   for (int i = 0; i < listColumns.Count; i++)
   {
       if (i == 0)
          {
             sb.Append(" where ");
             sb.Append(" id=" + listColumns[i]);
          }
        else
             sb.Append(" or id=" + listColumns[i]);
    }
   Console.Write(sb.ToString());

pass query variable to sqlcommand

edited Nov 12 '15 at 07:31

answered Oct 20 '14 at 09:25

Anik Islam Abhi

25,137
8
58
80

please use `StringBuilder` for string concatenation in a loop and secondly, I would prefer the use of parameters when building a SQL statement. – JP Hellemons Oct 20 '14 at 10:28

JP Hellemons · Accepted Answer · 2014-10-21T08:26:52.030

Here is a solution without a for loop, but unfortunately also without a parameterized SQL statement:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

public class test {
  public static void Main(string[] args)
  {
    //List<int> listColumns = new List<int>(){ 1, 5, 6, 9};
    System.Collections.Generic.List<int> listColumns = new System.Collections.Generic.List<int>(){ 1, 5, 6, 9};
    string s = String.Join(", ", listColumns.Select(x => x.ToString()));

    string sql = String.Format("SELECT * FROM Table WHERE ID IN ({0})", s);
    Console.WriteLine(sql);
  }
}

Please note that using select * is considered as a bad practice

edit Here is some new code because your list is of type System.Web.UI.MobileControls.List

string sql = String.Format("SELECT * FROM Table WHERE ID IN ({0})", 
                 listColumns.ListItem.Value);

edit 2 I have taken the code from your comment:

list.Items.Clear(); 
string values = DropDownList4.SelectedValue; 
string[] words = values.Split(','); 

foreach (string s in words) 
    if (s != "" && s != string.Empty && s != null)     
        list.Items.Add(s);

I guess that you have this on the dropdown changed event or something? and your dropdown has a string like "1,5,6,9" in the value. If all my assumptions are correct you can use:

System.Collections.Generic.List<int> selectedValues = new     System.Collections.Generic.List<int>();

foreach (string s in words)
    if (!String.IsNullOrWhiteSpace(s))
        selectedValues.Add(Convert.ToInt32(s));
string ids = String.Join(", ", selectedValues.Select(x => x.ToString()));
string sql = String.Format("SELECT * FROM Table WHERE ID IN ({0})", ids);

I take an error: Error 1 'System.Web.UI.MobileControls.List' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'System.Web.UI.MobileControls.List' could be found (are you missing a using directive or an assembly reference?) — 1teamsah, Oct 20 '14 at 11:54
Ehh the List object I was using was not of type `MobileControls` but of System.Collections.Generic.List http://msdn.microsoft.com/en-us/library/6sh2ey19%28v=vs.110%29.aspx — JP Hellemons, Oct 20 '14 at 13:24
ps. msdn says: `The System.Web.Mobile.dll assembly has been deprecated and should no longer be used. For information about how to develop ASP.NET mobile applications, see http://go.microsoft.com/fwlink/?LinkId=157231` http://msdn.microsoft.com/en-us/library/system.web.ui.mobilecontrols.list%28v=vs.110%29.aspx — JP Hellemons, Oct 20 '14 at 13:27

Damith · Answer 3 · 2014-10-20T09:07:34.173

//assume input is List of integers 
List<int> ids = new List<int> { 1,5,6,9 };
//build your SQL statement as below
string cmdText = "SELECT * FROM Table  WHERE id IN ({0})";
// add parameters for each id value 
string[] paramNames = ids.Select(
    (s, i) => "@id" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
//set the parameter values
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < ids.Count; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], ids[i]);
    }
}

John Castleman · Answer 4 · 2015-02-18T17:17:55.263

Linq-to-SQL:

var listColumns = new List<int>() { 1, 5, 6, 9 };
db.Table.Where(x => listColumns.Distinct().Contains(x.id)).ToString();

Generated SQL:

SELECT
    [Extent1].[id] AS [id],
FROM [Table] AS [Extent1]
WHERE [Extent1].[id] IN (1, 5, 6, 9)

EDIT

An approach that yields parameterized SQL (not that it's all that useful if the number of parameters keeps changing):

listColumns.Distinct().Aggregate(db.Table, (current, c) => current.Where(x => c == x.id)).ToString();

Select Query with Where condition depending on list values in asp.net

4 Answers4

Linked