数据库查询foreach循环c＃

Question

我正在开发一些客户端的工具，他们不想使用LINQ to SQL，他们也不想使用WPF，但我使用它，他们害怕到新的编码或其他东西......但我不能与数据库，因为他们不知道任何关于LINQ ......所以他们给了我这个讨厌的类（http://pastebin.com/VUzvN44i太长，粘贴在这里，对不起）这是“好”当检索行逐行...

但我有以下功能，这是完全与linq sql的功能，但我失去了他们给了我这个类...所以，如果有人可以给我一些建议如何在不修改课程的情况下做到这一点非常有帮助。谢谢。

private void GenerarFichero_Click(object sender, RoutedEventArgs e) 
{ 
    string valEmisora = "02827003"; 
    string codigoCabecera; 
    if (DateTime.Today.Day > 7) 
     codigoCabecera = "AE570200"; 
    else 
     codigoCabecera = "AE570100"; 
    DataBaseDataContext dc = new DataBaseDataContext(); 
    using (StreamWriter sw = new StreamWriter(codigoCabecera.Remove(6, 2))) 
    { 
     sw.WriteLine("775701 " + DateTime.Now.ToString("yy") + DateTime.Now.Month.ToString("d2") + DateTime.Now.AddDays(1).Day.ToString("d2") + DateTime.Now.Hour.ToString("d2") + DateTime.Now.Minute.ToString("d2") + "008910     00" + txtBanco.Text + codigoCabecera + txtBanco.Text + ""); 
     sw.WriteLine("0170  " + valEmisora + " " + this.txtBanco.Text + "   10" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy")); 

     var OutputQuery = 
      from o in dc.Seguros 
      group o by o.emisora; 
     List<int> TotalRegistros = new List<int>(); 
     List<int> TotalSumas = new List<int>(); 
     foreach (var grupo in OutputQuery) 
     { 
      sw.WriteLine("0270  " + valEmisora + grupo.Key + " " + this.txtBanco.Text + "   10" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy")); 
      List<int> Suma = new List<int>(); 
      foreach (var data in grupo) 
      { 
       Suma.Add(Convert.ToInt32(data.importe + data.importe_dec)); 
       sw.WriteLine("6070  " + valEmisora + data.emisora + "1" + data.banco + data.sucursal + data.fecha + data.importe + data.importe_dec + data.identificacion + "      " + data.referencia); 
      } 
      TotalRegistros.Add((grupo.Count() + 2)); 
      TotalSumas.Add(Suma.Sum()); 
      sw.WriteLine("8070  " + valEmisora + grupo.Key + " " + (grupo.Count() + 2).ToString().PadLeft(6, '0') + "  " + Suma.Sum().ToString().PadLeft(12, '0')); 
     } 
     sw.WriteLine("9070  " + valEmisora + " " + (TotalRegistros.Sum() + 2).ToString().PadLeft(6, '0') + "  " + TotalSumas.Sum().ToString().PadLeft(12, '0')); 
     this.txtTotal.Text = TotalSumas.Sum().ToString(); 
    } 
    MessageBox.Show("El fichero ha sido guardado, ya no se puede editar"); 
} 

Answer 1

好了，我最终代码的工作，我把它换成OutputQuery与DataTable.AsEnumerable，实际上它是工作......这是现在的代码如下

private void GenerarFichero_Click(object sender, RoutedEventArgs e) 
{ 
    string valEmisora = "02827003"; 
    string codigoCabecera; 
    if (DateTime.Today.Day > 7) 
     codigoCabecera = "AE570200"; 
    else 
     codigoCabecera = "AE570100"; 
    using (StreamWriter sw = new StreamWriter(codigoCabecera.Remove(6, 2))) 
    { 
     sw.WriteLine("775701 " + DateTime.Now.ToString("yy") + DateTime.Now.Month.ToString("d2") + DateTime.Now.AddDays(1).Day.ToString("d2") + DateTime.Now.Hour.ToString("d2") + DateTime.Now.Minute.ToString("d2") + "008910     00" + txtBanco.Text + codigoCabecera + txtBanco.Text + ""); 
     sw.WriteLine("0170  " + valEmisora + " " + this.txtBanco.Text + "   10" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy")); 

     SelectBD sel = new SelectBD(App.ConexBD, "SELECT * FROM Seguros"); 
     var Query = sel.DataTable.AsEnumerable().Select(row => 
       { 
        return new  
        {  
         banco = row["banco"].ToString(), 
         emisora = row["emisora"].ToString(), 
         sucursal = row["sucursal"].ToString(), 
         fecha = row["fecha"].ToString(), 
         identificacion = row["identificacion"].ToString(), 
         importe = row["importe"].ToString(), 
         importe_dec = row["importe_dec"].ToString(), 
         provincia = row["provincia"].ToString(), 
         referencia = row["referencia"].ToString(), 
        }; 
       }); 
     var OutputQuery = Query.GroupBy(l => l.emisora); 
     List<int> TotalRegistros = new List<int>(); 
     List<int> TotalSumas = new List<int>(); 
     foreach (var grupo in OutputQuery) 
     { 
      sw.WriteLine("0270  " + valEmisora + grupo.Key + " " + this.txtBanco.Text + "   10" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy")); 
      List<int> Suma = new List<int>(); 
      foreach (var data in grupo) 
      { 
       Suma.Add(Convert.ToInt32(data.importe + data.importe_dec)); 
       sw.WriteLine("6070  " + valEmisora + data.emisora + "1" + data.banco + data.sucursal + data.fecha + data.importe + data.importe_dec + data.identificacion + "      " + data.referencia); 
      } 
      TotalRegistros.Add((grupo.Count() + 2)); 
      TotalSumas.Add(Suma.Sum()); 
      sw.WriteLine("8070  " + valEmisora + grupo.Key + " " + (grupo.Count() + 2).ToString().PadLeft(6, '0') + "  " + Suma.Sum().ToString().PadLeft(12, '0')); 
     } 
     sw.WriteLine("9070  " + valEmisora + " " + (TotalRegistros.Sum() + 2).ToString().PadLeft(6, '0') + "  " + TotalSumas.Sum().ToString().PadLeft(12, '0')); 
     this.txtTotal.Text = TotalSumas.Sum().ToString(); 
    } 
    MessageBox.Show("El fichero ha sido guardado, ya no se puede editar"); 
} 

Answer 2

我不感到自豪我会说，但既然你迫切需要在这里解决它去...... 与此有关的工作快速和肮脏的方法是：

var connection = InitalizeConnectionToDb(); 
var select = new SelectBD(); 
// Get the data from database 
select.Open(connection, @"select * from Seguros"); 
// Simulate the grouping 
var dictionary = new Dictionary<string, List<DataRow>>(); 
foreach(var row in select.DataTable) 
{ 
    var key = Convert.ToString(row["emisora"]); 
    if(!dictionary.ContainsKey(key)) 
    { 
     dictionary[key] = new List<DataRow>(); 
    } 
    dictionary[key].Add(row); 
} 

现在，您可以使用上面的字典来执行计算，因为数据按emisora字段分组。