代码优化：列表循环和查询C＃SQL Server精简版

Question

这里的代码是检查是否有一个DataGridView（Productgridview）添加药物间的相互作用

• 前端：C＃与VS2012 .NET 4.0
• 后端：SQL Server精简版4.0

架构数据库：

• 表产品：productId;名称

• 表product_druginteractionclass：productId; druginteractionclassId

• table interaction：interactionId; druginteractionclassId1;
  druginteractionclassId2;风险评论

• 表druginteractionclass：druginteractionclassId;命名

很多产品能够具有相同的药物相互作用类数

的做法是：

• 循环掷Productgridview基于名 检索的productid并选择druginteractioncla SSID基于的productid然后把结果在其他的dataGridView（listclassification）
• 创建从DataGridView列druginteractionclassId从数据2所列出（listclassification）///一个小问题，这表明它们之间的相互作用药物，因为列表持有相同的所有druginterctionclass号和双重迭代扔他们测试组合相互作用/// 如何以编程方式创建单独的列表，而我不知道会增加多少药物？
• 循环罚球2所列出并根据组合选择的互动和与药物相互作用的类名

连接的结果是它可以动态地创建列表和循环它们扔到 做组合虽然不知道怎么直到运行时间有很多列表？

我想循环里面的交互gridview和检查产品是否重复，如果没有删除不适当的交互。

• 可能会全部关闭，可以在一个查询来概括，如何写 一个（那样的选择的选择的选择）？

其实我药房学生（我只是喜欢写代码，不要把它做好，我甚至不完成任何C＃本书C＃/ SQL/ado.net之间就在600页所以请原谅我）

 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();