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

First thing that comes into my mind is. Why does your client determine which technology to use? is it an IT company or something? Second, move that code to a corresponding DAL layer. Don't put DB access code in a Button click handler. It hurts my eyes just to see that. — Fede
– Fede, Commented Mar 11, 2013 at 12:24
Actually, I'm using what they want... Not what I want, it is an IT Company, which asked me to make this program following their steps, so I haven't freedom to code... I made the app using Linq and stuff, fully working but they don't want it that way, because their programmers don't know anything about it, and if they want to modify something, they don't know how... u_u Maybe parsing the DataTable to IENumerable can help? =/ — Robert W. Hunter
– Robert W. Hunter, Commented Mar 11, 2013 at 12:29

RePierre · Accepted Answer · 2013-03-11 12:36:31Z

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.

answered Mar 11, 2013 at 12:36

RePierre

9,5762 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert W. Hunter Over a year ago

Thanks for your comment, but I got it working (see my own answer) I think is the best solution for this situation.

Robert W. Hunter · Accepted Answer · 2013-03-11 12:36:11Z

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

So you decided that it is ok to use LINQ to DataSets? I guess your client will have to move up to the LINQ world at some point. Please make sure to mark the answer you prefer as the answer to your question.
Yes, because they can still use their class and this little tweak can work... So everyone wins (not me, ugly code) I told them to move to LINQ, but they refuse to anything -_-

Collectives™ on Stack Overflow

DB Query to foreach loops c#

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related