CODEWITHSUNDEEP
c#sqllistvisual-studio-2005
CJ7
CJ7

Reputation: 23275

How to generate SQL statement from List?

If I have a list of Strings, ie. List<String>, how can I generate a SQL statement such as:

SELECT Column1 FROM Table1 WHERE Column1 IN ('String1','String2','String3')

where 'String1','String2','String3' are the contents of List<String>?

No LINQ etc. as I am using VS2005.

Upvotes: 4

Views: 2671

Answers (9)

MAXE
MAXE

Reputation: 5122

// Assume your list (List<string>) is named "myList"

// Please put the next line in an external string resource...
string selectStatement = "SELECT Column1 FROM Table1 WHERE Column1 IN ({0})";

StringBuilder stringBuilder = new StringBuilder("(");
foreach(string colName in myList)
    stringBuilder.Append(String.Format("'{0}',", colName));
stringBuilder.Append(")");

return String.Format(selectStatement, stringBuilder.ToString().Replace(",)", ")");

Upvotes: 0

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48568

Since you said its an internal operation and hence there is no need to be worried about SQL Injection, then you can achieve what you want by this.

string str = "";

foreach(string s in list)
  str += "'" + s.Replace("'", "''") + "',";

str = str.SubString(0, str.Length - 1);

str = "SELECT Column1 FROM Table1 WHERE Column1 IN (" + str + ")";

//str will have your command ready.

I have tested it. It works perfectly.

Upvotes: 0

DrFloyd5
DrFloyd5

Reputation: 13787

To properly handle sql injection, a better answer may be to make the query of the form...

select results.* from (
    select pk from table where column=value1 union
    select pk from table where column=value2 union
    select pk from table where column=value3 union
    select pk from table where column=value4 union
    select pk from table where column=value5
) filtered join table as results on filtered.pk = results.pk

and then make it more c# friendly

string items_filter = "";
int item_index=0;
OracleParameterCollection parameters = new OracleParameterCollection(); // Not sure what class to use here exactly, but just collect a bunch of stored procedure parameters

foreach (string item in list_of_items) {
    string item_name = string.Format("i_item{0}",item_index);  
    string item_sql = string.Format("select pk from table where column=:{0} union",item_name);
    parameters.Add(new Parameter("item_name",item));
    item_index+=1;  
}
if (items_filter.IsNullOrEmpty()) 
    return;

string sql = String.Format("select results.* from ({0}) filtered join table as results on filtered.pk = results.pk",items_filter);

OracleCommand c = new OracleCommand();
c.command = sql;
c.parameters = parameters;
c.execute();

More or less.

Upvotes: 0

usr
usr

Reputation: 171178

Please don't use the other answers that have been submitted so far. They contain SQL injection for no obvious reason.

    List<String> strlist = new List<string>();
    strlist.Add("st1");
    strlist.Add("st2");
    strlist.Add("st3");

    var dynamicPart = string.Join(", ",
     Enumerable.Range(0, strlist.Count).Select(i => "@" + i).ToArray());
    for(i = 0 to strlist.Count)
     { /* add parameter to SqlCommand here with name ("@" + i) */ }
    string query = "SELECT Column1 FROM Table1 WHERE Column1 IN (" +
     dynamicPart + ")";

Use parameters instead of literals for multiple reasons (research them!).

And instead of a clumsy concatenation loop use string.Join which does all of that for us.

Upvotes: 0

DrFloyd5
DrFloyd5

Reputation: 13787

Don't for get to guard against SQL Injection.

string sql_list = "";
foreach (string s in lst)
    sql_list+=string.Format("{0},",s.Replace("'","''"));
sql_list = string.Format("({0})",sql_list.substring(0,sql_list.length-2));

that might help some, and use string builder, or not.

Upvotes: 0

GSerjo
GSerjo

Reputation: 4778

Take a look on following version

    [Test]
    public void Test()
    {
        var list = new List<string> {"String1", "String2", "String3"};
        string values = ArrayToString(list);
        string sql = string.Format("SELECT Column1 FROM Table1 WHERE Column1 IN ( {0} )", values);
    }

    private static string ArrayToString(IEnumerable<string> array)
    {
        var result = new StringBuilder();

        foreach (string element in array)
        {
            if (result.Length > 0)
            {
                result.Append(", ");
            }
            result.Append("'");
            result.Append(element);
            result.Append("'");
        }
        return result.ToString();
    }

result statement SELECT Column1 FROM Table1 WHERE Column1 IN ( 'String1', 'String2', 'String3' )

Upvotes: 4

Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13582

List<string> items = new List<string>();
items.Add("string1");
items.Add("string2");
items.Add("string3");
string AllItems = "";
foreach (string item in items)
{
    AllItems += string.Format("\"{0}\",",item);
}
AllItems = AllItems.TrimEnd(',');
string YourSQLQuery = string.Format("SELECT Column1 FROM Table1 WHERE Column1 IN ({0})", AllItems);
MessageBox.Show(YourSQLQuery);

Upvotes: 0

Habib Zare
Habib Zare

Reputation: 1204

try :

        List<String> strlist = new List<string>();
        strlist.Add("st1");
        strlist.Add("st2");
        strlist.Add("st3");

        string query = "SELECT Column1 FROM Table1 WHERE Column1 IN (";

        for (int i = 0; i < strlist.Count; i++)
        {
            query += "\'" + strlist[i] + "\'" + (i == strlist.Count - 1 ? "" : ",");
        }

        query += ")";

Upvotes: 0

Anirudha
Anirudha

Reputation: 32797

List<string> lst=new List<string>();lst.Add("Hello");lst.Add("Hello World");
string s="";
foreach(string l in lst)s+="\""+l+"\"";
s=Regex.Replace(s,"\"\"","\",\"");

string output="SELECT Column1 FROM Table1 WHERE Column1 ("+s+")";

Upvotes: 0

Related Questions