using local table variable in sql asp.net query code behind

Question

Can I use a local DataTable with one column in sql query? And how?

List<int> k_p = null;
k_p = new List<int>();
k_p = (List<int>)Session["kosarica"];
DataTable spremljeno = new DataTable();
spremljeno.Columns.Add("id_k_p");
for(int i=0; i<k_p.Count; i++)
{
    spremljeno.Rows.Add(k_p[i]);
}
String ConnString = "Data Source=BRACO-PC\\SQL1;Initial Catalog=DiplomskiSQL1SQL;Integrated Security=True;";
SqlConnection Conn = new SqlConnection(ConnString);
Conn.Open();
DataTable ukosarici = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("Select Proizvod.id_p, Proizvod.ime, TipProizvoda.tip, Proizvod.dimenzije, Proizvod.cijena FROM Proizvod LEFT JOIN TipProizvoda ON Proizvod.tip=TipProizvoda.id_t WHERE Proizvod.id_p IN @spremljeno", Conn);
SqlCommandBuilder cmd = new SqlCommandBuilder(da);
da.Fill(ukosarici);
GridView1.DataSource = ukosarici;
GridView1.DataBind();
Conn.Close();

I want to show only data where id_p is equal to values in spremljeno, and do not want to do a temp table in db, but dont know if it is possible... Connecting to SQL server 2008...

Answer 1

You could add all ID's as parameter to an IN clause in SQL:

var parameters = new string[k_p.Count];
using(var cmd = new SqlCommand())
{
    for (int i = 0; i < k_p.Count; i++)
    {
        parameters[i] = string.Format("@id_p{0}", i);
        cmd.Parameters.AddWithValue(parameters[i], k_p[i]);
    }
    var sql = "Select Proizvod.id_p, Proizvod.ime, TipProizvoda.tip, Proizvod.dimenzije, Proizvod.cijena FROM Proizvod LEFT JOIN TipProizvoda ON Proizvod.tip=TipProizvoda.id_t WHERE Proizvod.id_p IN ({0})";
    cmd.CommandText = string.Format(sql, string.Join(", ", parameters));
    using(var con = new SqlConnection(connStr))
    {
        cmd.Connection = con;
        using(var da = new SqlDataAdapter(cmd))
        {
            da.Fill(ukosarici);
        }
    }
}

Note that you should use using-statements for everything that implements IDisposable(f.e. SqlConnection, Dispose also closes the connection). Also note that i used only your List<int>, the second DataTable was redundant.