How to build where clause in SQL Server??

Question

I would like to build the where clase of a sql statement dynamically from hashtable in C#.

The key of the hash_table will be the column's name to be inserted and the value of hash_table will be value.

string sql_1="SELECT COL_1,COL_2 FROM MY_TABLE";
string sql_2="SELECT * FROM MY_TABLE WHERE COL_3='ABC'";  //note: some statment have where clause while some do NOT have. 

string sql= ToSql(sql_1,myHashTable); // the actual sql statment will be returned from ToSql
//execute sql
sql= ToSql(sql_2,myHashTable); // 
//execute sql

My Question is, how can I create function ToSql() function in LINQ?

NOTE: The data type of the value of hashtable will be taken into consideration.

Thanks in advance.

Answer 1

var q = String.Join(" AND ", myHashTable.Select(x => x.Key.ToString() + " = " + 
    (x.Value is string ? "'" : "") + x.Value.ToString() + 
    (x.Value is string ? "'" : "")))

Of course you're going to have to decide whether to add a "WHERE", "AND", have to strip off some "GROUP BY" clause first before adding "WHERE", and deal with escaping, but I trust you can handle that.

However, I would strongly suggest using parameterized queries instead, and add the parameters to the SqlCommand. Something like:

var q = String.Join(" AND ", myHashTable.Select(x => x.Key.ToString() + " = @" +
    x.Key.ToString()));

var parameters = myHashTable.Select(x => new SqlParameter("@" + x.Key.ToString(), 
    x.Value));