code optimisation : list loop and query c# sql server compact edition

Question

the code here is for checking is there any interaction between added drugs in a dataGridView ( Productgridview )

• front end : C# with VS2012 .Net 4.0
• back end : sql server compact edition 4.0

the schema for database :

• table product : productId ; name

• table product_druginteractionclass : productId ; druginteractionclassId

• table interaction : interactionId ; druginteractionclassId1 ;
  druginteractionclassId2 ; riskcomment

• table druginteractionclass : druginteractionclassId ; name

many products can have same drug interaction class number

the approach is :

• looping throw Productgridview to retrieve the productid based on name and select druginteractionclassId based on productid then put the result in another dataGridView (listclassification)
• create 2 lists from data in column druginteractionclassId from dataGridView (listclassification) /// a minor problem is here it show interaction between same drug because the lists hold same all druginterctionclass number and double iterating throw them to test combination interactions /// how to create separate lists programmatically while i can not know how much drugs will be added ?
• looping throw 2 lists and select interactions based on combinations and join the result with a drug interaction class name

is it possible to create lists dynamically and looping throw them to do combinations while not know how much lists till run time ?

i think of also looping inside the interaction gridview and check if a products is repeated if no remove the inappropriate interaction .

• may be all off that can be summarized in a one query , how to wrote one (that kind of select of select of select)?

actually i am pharmacy student ( i just like to code and do not do it well, i do not even complete any c# book just around 600 page between c# / sql / ado.net so excuse me )

        var ds2 = new DataSet();
        for (var i = 0; i < Productgridview.Rows.Count; i++)
        {
            var listclasse = Productgridview.Rows[i].Cells["Productid"].Value.ToString();
            var datadrug2 = "SELECT *  FROM product_druginteractionclass" +
                            " where productId = '" +
                            listclasse + "'"; // listclasse is the list of  manually added drugClass
            var connection1 = new SqlCeConnection(connectionString);

            var dataadapter1 = new SqlCeDataAdapter(datadrug2, connection1);
            //var ds = new DataSet();
            connection1.Open();
            dataadapter1.Fill(ds2, "product_druginteractionclass");
            connection1.Close();
        }
        listclassification.DataSource = ds2;
        listclassification.DataMember = "product_druginteractionclass";

/////////////// put the druginteractionclass into 2 lists 
        var list1 = new List<string>();
        var list2 = new List<string>();
        foreach (DataGridViewRow item in listclassification.Rows)
            if ((item.Cells.Count >= 2) && //atleast two columns
                (item.Cells[1].Value != null)) //value is not null
            {
                list1.Add(item.Cells[1].Value.ToString());
                list2.Add(item.Cells[1].Value.ToString());
            }
        //for (var i = 0; i <= list.Count - 1; i++)
        //{
        //   // MessageBox.Show(list[i].ToString());
        //}

        //////////// select interaction based on druginteractionclass
        var ds = new DataSet();
        for (var i = 0; i <= list1.Count - 1; i++)
            for (var j = 0; j <= list2.Count - 1; j++)
            {
                var value = list1[i];
                var value1 = list2[j];
                var datadrug3 = "SELECT u1.name, u2.name  , m.* " +
                                "FROM druginteractionclass u1 " +
                                "left outer JOIN interaction m" +
                                " ON u1.druginteractionclassId = m.druginteractionclassId1 " +
                                "left outer JOIN druginteractionclass u2 " +
                                "ON u2.druginteractionclassId = m.druginteractionclassId2" +
                                " where m.druginteractionclassId1 = '" + value +
                                "' and m.druginteractionclassId2 ='" + value1 + "'" +
                                "Order by m.severity ";

                var connection = new SqlCeConnection(connectionString);

                var dataadapter = new SqlCeDataAdapter(datadrug3, connection);

                connection.Open();
                dataadapter.Fill(ds, "interaction");
                connection.Close();
            }
        dataGridView1.DataSource = null;
        dataGridView1.DataSource = ds;
        dataGridView1.DataMember = "interaction";

        /////// remove duplicated interactions  
        for (var currentRow = 0; currentRow < dataGridView1.Rows.Count - 1; currentRow++)
        {
            var rowToCompare = dataGridView1.Rows[currentRow];

            for (var otherRow = currentRow + 1; otherRow < dataGridView1.Rows.Count; otherRow++)
            {
                var row = dataGridView1.Rows[otherRow];

                var duplicateRow = true;

                for (var cellIndex = 0; cellIndex < row.Cells.Count; cellIndex++)
                    if (!rowToCompare.Cells[2].Value.Equals(row.Cells[2].Value))
                    {
                        duplicateRow = false;
                        break;
                    }

                if (duplicateRow)
                {
                    dataGridView1.Rows.Remove(row);
                    otherRow--;
                }
            }
        }

Answer 1

                        var datadrug3 =
                        @" SELECT m.interactionId , u1.name , n.name , u2.name,n2.name   , m.riskComment , m.precautionComment , m.severity 

                         FROM druginteractionclass u1 
                         left outer JOIN interaction m 
                         ON u1.druginteractionclassId = m.druginteractionclassId1 
                         left outer JOIN druginteractionclass u2 
                         ON u2.druginteractionclassId = m.druginteractionclassId2 

                         left outer join product_druginteractionclass p1 
                         ON  p1.druginteractionclassId = m.druginteractionclassId1 

                         left outer JOIN product_druginteractionclass p2 
                         ON  p2.druginteractionclassId = m.druginteractionclassId2 

                         left outer join product n 
                         ON  n.productId= p1.productId 

                         left outer join product n2 
                         ON n2.productId= p2.productId 
                         where n.productId = @value  and n2.productId = @value1 ";

                    var connection = new SQLiteConnection(connectionString3);
                    var com = new SQLiteCommand(datadrug3, connection);
                        com.Parameters.AddWithValue("@value", value);
                        com.Parameters.AddWithValue("@value1",value1 );
                        var dataadapter = new SQLiteDataAdapter(com);

                    connection.Open();
                    dataadapter.Fill(ds, "interaction");
                    connection.Close();