CODEWITHSUNDEEP
c#sqldatatabledataset
Jeebwise
Jeebwise

Reputation: 177

Working with data in a datatable

I am using this code to retrieve data from a sql database

DataTable oeeTable = new DataTable();

oeeTable.Columns.Add("ProductionSystem", typeof(string));
oeeTable.Columns.Add("PartNumber", typeof (string));
oeeTable.Columns.Add("RunRate", typeof (int));
oeeTable.Columns.Add("Doses",typeof(int));
oeeTable.Columns.Add("HoursOfGoodProduct",typeof(double));

DataView view = new DataView(oeeTable);
dataGridView1.DataSource = view;

const string sqlQuery = @"SELECT ProdSysShift.ProdSys, LotShift.PartNumber,PartNumberRunRates.TabletsPerHour, LotShift.Doses
                            FROM LotShift INNER JOIN
                            ProdSysShift ON LotShift.intShiftID = ProdSysShift.intShiftID INNER JOIN
                            PartNumberRunRates ON LotShift.PartNumber = PartNumberRunRates.PartNumber
                            WHERE (ProdSysShift.dtmDate >= '3/3/2014') AND (ProdSysShift.dtmDate <='3/5/2014')
                            ORDER BY ProdSysShift.ProdSys, LotShift.PartNumber";

SqlCommand myCommand = new SqlCommand(sqlQuery,_myConnection);

try
{
    _myConnection.Open();
    SqlDataReader myReader = myCommand.ExecuteReader();
    while (myReader.Read())
    {
        string prodSystem = myReader["ProdSys"].ToString();
        string partNumber = myReader["PartNumber"].ToString();
        int tabletsPerHour = (int)myReader["TabletsPerHour"];
        int doses = (int) myReader["Doses"];
        double hoursOfGood = ((double)doses/tabletsPerHour);
        hoursOfGood = Math.Round(hoursOfGood, 2);

        oeeTable.Rows.Add(prodSystem,partNumber,tabletsPerHour, doses, hoursOfGood);
    }
    _myConnection.Close();
}
catch (Exception e)
{
    MessageBox.Show(e.ToString());
}

The output looks roughly like this and sites in a datagrid view

T193    HT19    172800  109872  0.64
T193    HT19    172800  242644  1.4
T193    HT19    172800  745887  4.32
T193    HT19    172800  843160  4.88
T193    HT19    172800  20777   0.12
T193    HT19    172800  384450  2.22
T193    HT19    172800  0   0
T193    HT19    172800  600020  3.47
T193    HT70A   172800  612618  3.55
T193    HT70A   172800  0   0
T371    MT06    172800  0   0
T371    MT06    172800  0   0
T371    MT06    172800  0   0
T371    MT06    172800  1123025 6.5
T371    MT06    172800  98892   0.57
T371    MT06    172800  1001704 5.8
T371    MT06    172800  136080  0.79

The next things I want to do is sum up the values in the far right column grouped by the distinct value of the far left column. So all of the T193s would be together and summed. All the T371s would be together and summed. I am not too sure what the next step would be or if I went about the first part wrong.

Upvotes: 0

Views: 63

Answers (2)

Jeebwise
Jeebwise

Reputation: 177

I ended up figuring it out what I needed to do through a stored procuded. Thank you DonHoitnott for the idea. I used a stored procedure that creates a temp table using a select into statment. Some of my math is completed in that part. The rest of my math is completed in the same stored procedure using a follow up select statement from that temp table that also groups based on the production system. Using the data retured by this stored procuded I now have the results I require for my C# program

Upvotes: 0

C4F
C4F

Reputation: 682

Create a new table. Run two queries, first one should get you a queryable of distinct ProductionSystems.

First column of new table:

Select(row=>row.ProductionSystem).Distinct();

Create a loop, each of the values above,

foreach(var ps in query1above)
    table.Where(row=>row.ProductionSystem == ps).Select(row=>row.HoursofGoodProduction).Sum()

This will give you your sum.

Add both the sum and the production system key to the new table.

Upvotes: 2

Related Questions