How to join a user defined datatable with sql table in c# query in code behind?

Question

I have a grid view that shows to user all baskets that he or she buy in my web. My grid has check box in each row that user can select several basket to print them(Print its items in it.) When user check the check box and click the button to print them , my code check all rows in grid view and save the basket ID in a list and save it in session as list and in print page I can use it. But my problem is here : In print Page I define a DataTable with one column and fill it with basket ID. But I don't know how to join my select query with this data table
this is my query:

sqlQuery="SELECT sale.basketid as id ,sale.discount,ss.LBL as RowNo,sale.ColumnNo,Sale.ID as serial,Sale.Qty "
                   + "  FROM Sale INNER JOIN SansDate ON Sale.SID = SansDate.SID AND Sale.SansNumber = SansDate.SansNumber"
               + "  INNER JOIN Sect ON Sale.SID = Sect.SID INNER JOIN Saloon ON Sect.SaloonID = Saloon.SaloonID left outer join saloonstructure ss on ss.saloonid=saloon.saloonid and ss.rownum=sale.rowno "
              + "where sale.basketid=" + hash.decode(Request.QueryString["param"].ToString()));

and this is my DataTable :

List<string> arraybasket = (List<string>)Session["basketArray"];
            DataTable dt = new DataTable();
            dt.Columns.Add("bid");
            DataRow r = dt.NewRow();
            for (int i = 0; i < arraybasket.Count; i++)
            {
                r["bid"] = arraybasket[i];
                dt.Rows.Add(r);
            }

in where cluse i just want to show the items that have these Basket ID in my data table (dt)

Can't you use an IN clause without the datatable? "where qsale.basketid in (1, 2, 3, 4) — Mike Hixson
– Mike Hixson, Commented Jul 1, 2014 at 4:38
I can use it , but i think maybe to find better solution. (if i have 100 basket it, i think the speed is become slow (maybe)). Is there another solution?> — H.Ghassami
– H.Ghassami, Commented Jul 1, 2014 at 4:44
You will want to use a table valued parameter then. msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx — Mike Hixson
– Mike Hixson, Commented Jul 1, 2014 at 4:46

Hassan Nisar · Accepted Answer · 2014-07-01 05:19:22Z

in where cluse i just want to show the items that have these Basket ID in my data table (dt)

You could use IN operator of SQL.

No need to create a data table to get strings from List<string>. Directly get basketid from List<string> using string.Join;

List<string> arraybasket = (List<string>)Session["basketArray"];

sqlQuery=@"SELECT 
                 sale.basketid as id ,
                 sale.discount,
                 ss.LBL as RowNo,
                 sale.ColumnNo,
                 Sale.ID as serial,
                 Sale.Qty 
           FROM Sale 
           INNER JOIN SansDate ON Sale.SID = SansDate.SID AND Sale.SansNumber = SansDate.SansNumber
           INNER JOIN Sect ON Sale.SID = Sect.SID 
           INNER JOIN Saloon ON Sect.SaloonID = Saloon.SaloonID 
           LEFT OUTER JOIN saloonstructure ss on ss.saloonid=saloon.saloonid AND ss.rownum=sale.rowno 
           WHERE sale.basketid in (" + string.Join(",", arraybasket) + ");";

Collectives™ on Stack Overflow

How to join a user defined datatable with sql table in c# query in code behind?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related