DB Query to foreach loops c#

Question

I'm developing some tool for a client, and they don't want to use LINQ to SQL, they also don't want to use WPF but I'm using it, they are afraid to new coding or something... But i can't with database, because they don't know anything about LINQ... so they gave me this annoying class (http://pastebin.com/VUzvN44i too long for paste here, sorry) which is "ok" when retrieving row by row...

But I have the following function, which is fully functional with linq to sql, but I'm lost with this class they gave me... So if someone can give me some advice on how to do it without modify the class it will be very helpful. Thanks.

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

I am not proud of what I'll be saying but since you desperately need a solution here it goes... The quick and dirty way to work with this is:

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

Now you can use the dictionary above to perform your calculations because the data is grouped by emisora field.

Answer 2

Well, I have the final code working, I've replaced OutputQuery with DataTable.AsEnumerable and actually it is working... This is what the code looks now

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");
}