Need some C# help with datasets, multiple tables, and combining them. (complicated)

v6maro

v6maro

[H]ard|Gawd
Joined
Oct 10, 2002
Messages
1,552
Ok, I have sql query where I fill a dataset. The sql query has 4 queries, so 4 datatables are returned to the dataset.

Table
Table1
Table2
Table3

Table is unique and has every Employee Number (all unique)


---------
EmpNo

10001
10002
10003
...

Table1 is a result set of Employee Numbers between two dates, so there are duplicates of the same EmpNo. This table also includes Employee Name, last name, etc..
[Table1]
---------
EmpNo
Fname
Lname

10001 John Doe
10001 John Doe
10002 Jane Smith
10002 Jane Smith
...

Table2 is a result set of Employee Numbers, the start date, end date, and 3 deduction columns. Since an Employee can have multiple checks over a single month, EmpNo is duplicated here as well.
[Table2]
---------
EmpNo
WeekEnd
CheckDate
DedNo 1
DedNo 2
DedNo 3

10001 05-06-2007 05-11-2007 1 0 0
10001 05-20-2007 05-25-2007 1 0 0
10002 05-06-2007 05-11-2007 1 0 0
10002 05-20-2007 05-25-2007 1 0 0
...

Table3 is a result set of Employee Numbers, start date, end date, Earnings 1, Hours 1, Earnings 2, Hours 2. Since an Employee can have multiple checks over a single month, EmpNo is duplicated here as well.
[Table3]
---------
EmpNo
WeekEnd
CheckDate
Earnings 1
Hours 1
Earnings 2
Hours 2

10001 05-06-2007 05-11-2007 923.00 80.00 .00 .00
10001 05-20-2007 05-25-2007 923.00 80.00 .00 .00
10002 05-06-2007 05-11-2007 311.00 56.98 .00 .00
10002 05-20-2007 05-25-2007 311.00 56.98 .00 .00
...

Ok, after I have all this data, I need to make one large dataset in C#

like so:
EmpNo
WeekEnd
CheckDate
Fname
Lname
DedNo1
DedNo2
DedNo3
Earnings1
Hours1
Earnings2
Hours2
Code: 
10001  05-06-2007 05-11-2007 John  Doe    0  0  1  923.00  80.00  .00  .00
10001  05-20-2007 05-25-2007 John  Doe    0  0  1  923.00  80.00  .00  .00
10001  05-06-2007 05-11-2007 Jane  Smith  0  0  1  311.00  56.98  .00  .00
10001  05-20-2007 05-25-2007 Jane  Smith  0  0  1  311.00  56.98  .00  .00
So, how would one combine these via datasets in c# like above?

I have tried DataTable.Merge(sourceTable);
That results in this:
Code: 
10001  05-06-2007 05-11-2007 John  Doe    
10001  05-20-2007 05-25-2007 John  Doe    
10002  05-06-2007 05-11-2007 Jane  Smith  
10002  05-20-2007 05-25-2007 Jane  Smith  
10001  05-06-2007 05-11-2007                   0  0  1  
10001  05-20-2007 05-25-2007                   0  0  1  
10002  05-06-2007 05-11-2007                   0  0  1  
10002  05-20-2007 05-25-2007                   0  0  1  
10001  05-06-2007 05-11-2007                               923.00  80.00  .00  .00
10001  05-20-2007 05-25-2007                               923.00  80.00  .00  .00
10002  05-06-2007 05-11-2007                               311.00  56.98  .00  .00
10002  05-20-2007 05-25-2007                               311.00  56.98  .00  .00
Obviously not what I want.

I have tried adding a 'MasterTable' to the dataset, setting the unique table as the main one, using relations to relate them all, then using a DataSetHelper class calling InsertJoinInto but I keep getting errors saying not unique fields, this is really driving me crazy.

Any help would be greatly appreciated
 
The right way to do this is to code a join on the database. Are you doing this with datasets because your data source doesn't support joins?
 
mikeblas said:
The right way to do this is to code a join on the database. Are you doing this with datasets because your data source doesn't support joins?
Click to expand...

I too was going to suggest this, but it seemed so obvious...
 
We cannot do the join within sql, otherwise I would be doing is that way, trust me.

We are on sql2000 and we have a few statements (3 of the 4) with 'in' statements in the where clause. We have a cross-tab (pivot table) stored procedure to output each column in the in statement as:
(original query)
select *
from table
where deductions in ('1', '2', '3')

would result in
Code: 
EmpNo deduction number deduction code deduction amount
10001   1                         DedCode1        $100
10001   2                         DedCode2        $50

Putting it thru the cross-tab did this for us:
Code: 
EmpNo DedCode1 DedCode2 
10001   $100         $50
Basically creating a pivot table or flat file.

Now, we could insert each result into temp tables, but that would use up a TON of database memory and HDD activity. We are limited in our servers (they suck) so we decided to let the client side (c# code in memory) handle the problem.

We would also just use the pivot table stuff in SQL2005, but our boss wont buy it, so we are limited to doing it this way (fun).

Now, if I could just join all the data, that would be swell, but in this case we cant. (as far as I know)

But, I have made some headway, and found a DataSet Join class that does just this. Working out the kinks right now.
 
You can do the join in c#

create a DataColumn as pk
pk = empno dataSet.Tables("Table").Columns("EmpNo")
empno dataSet.Tables("Table").PrimaryKey = pk

then add it as a fk to your other tables,
fk(0) = dataSet.Tables("Table1").Columns("EmpNo")
relation = New DataRelation("IssuerChild", pk(0), fk(0), False)
fkc = New ForeignKeyConstraint(pk(0), fk(0))
dataSet.Tables("Table1").Constraints.Add(fkc)
dataSet.Relations.Add(relation)

sorry all that s unformatted & in vb, but you get the idea.. just google those class names :D
 
Adding a datarelation creates a hierarchical view, we need a flat file. I'll be posting some code in a few of how I got this done, we did a join on 3-4 tables, then did a union on them to smush them all into one.

Thanks for the help guys.
 
#1, I didn't write this.

I used these classes to combine my datasets, I had to join, then union the data



Code: 
public static DataTable Join(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
        {

            return SQLOps.Join(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC });

        }

        public static DataTable Join(DataTable First, DataTable Second, string FJC, string SJC)
        {

            return SQLOps.Join(First, Second, new DataColumn[] { First.Columns[FJC] }, new DataColumn[] { First.Columns[SJC] });

        }

        public virtual DataTable Union(DataTable First, DataTable Second)
        {
            return SQLOps.Union(First, Second);
        }

        public class SQLOps
        {

            public static DataTable Union(DataTable First, DataTable Second)
            {
                //Result table
                DataTable table = new DataTable("Union");

                //Build new columns
                DataColumn[] newcolumns = new DataColumn[First.Columns.Count];

                for (int i = 0; i < First.Columns.Count; i++)
                {
                    newcolumns[i] = new DataColumn(First.Columns[i].ColumnName, First.Columns[i].DataType);
                }

                //add new columns to result table
                table.Columns.AddRange(newcolumns);
                table.BeginLoadData();

                //Load data from first table
                foreach (DataRow row in First.Rows)
                {
                    table.LoadDataRow(row.ItemArray, true);
                }

                //Load data from second table
                foreach (DataRow row in Second.Rows)
                {
                    table.LoadDataRow(row.ItemArray, true);
                }

                table.EndLoadData();

                return table;
            }


            //FJC = First Join Column

            //SJC = Second Join Column

            public static DataTable Join(DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC)
            {
                //Create Empty Table
                DataTable table = new DataTable("Join");

                // Use a DataSet to leverage DataRelation
                using (DataSet ds = new DataSet())
                {
                    //Add Copy of Tables
                    ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() });

                    //Identify Joining Columns from First
                    DataColumn[] parentcolumns = new DataColumn[FJC.Length];
                    for (int i = 0; i < parentcolumns.Length; i++)
                    {

                        parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];

                    }

                    //Identify Joining Columns from Second

                    DataColumn[] childcolumns = new DataColumn[SJC.Length];

                    for (int i = 0; i < childcolumns.Length; i++)
                    {

                        childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];

                    }


                    //Create DataRelation

                    DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);

                    ds.Relations.Add(r);


                    //Create Columns for JOIN table

                    for (int i = 0; i < First.Columns.Count; i++)
                    {

                        table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);

                    }

                    for (int i = 0; i < Second.Columns.Count; i++)
                    {

                        //Beware Duplicates

                        if (!table.Columns.Contains(Second.Columns[i].ColumnName))

                            table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);

                        else
                            
                            table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);

                    }


                    //Loop through First table

                    table.BeginLoadData();

                    foreach (DataRow firstrow in ds.Tables[0].Rows)
                    {

                        //Get "joined" rows

                        DataRow[] childrows = firstrow.GetChildRows(r);

                        if (childrows != null && childrows.Length > 0)
                        {

                            object[] parentarray = firstrow.ItemArray;

                            foreach (DataRow secondrow in childrows)
                            {

                                object[] secondarray = secondrow.ItemArray;

                                object[] joinarray = new object[parentarray.Length + secondarray.Length];

                                Array.Copy(parentarray, 0, joinarray, 0, parentarray.Length);

                                Array.Copy(secondarray, 0, joinarray, parentarray.Length, secondarray.Length);

                                table.LoadDataRow(joinarray, true);

                            }

                        }

                    }

                    table.EndLoadData();

                }


                return table;

            }

            public static DataTable Project(DataTable Table, DataColumn[] Columns, bool Include)
            {

                DataTable table = Table.Copy();

                table.TableName = "Project";

                int columns_to_remove = Include ? (Table.Columns.Count - Columns.Length) : Columns.Length;

                string[] columns = new String[columns_to_remove];

                int z = 0;

                for (int i = 0; i < table.Columns.Count; i++)
                {

                    string column_name = table.Columns[i].ColumnName;

                    bool is_in_list = false;

                    for (int x = 0; x < Columns.Length; x++)
                    {

                        if (column_name == Columns[x].ColumnName)
                        {

                            is_in_list = true;

                            break;

                        }

                    }

                    if (is_in_list ^ Include)

                        columns[z++] = column_name;

                }



                foreach (string s in columns)
                {

                    table.Columns.Remove(s);

                }



                return table;



            }



            public static DataTable Project(DataTable Table, DataColumn[] Columns)
            {

                return Project(Table, Columns, true);

            }



            public static DataTable Project(DataTable Table, params     string[] Columns)
            {

                DataColumn[] columns = new DataColumn[Columns.Length];

                for (int i = 0; i < Columns.Length; i++)
                {

                    columns[i] = Table.Columns[Columns[i]];

                }

                return Project(Table, columns, true);

            }

            public static DataTable Project(DataTable Table, bool Include, params string[] Columns)
            {

                DataColumn[] columns = new DataColumn[Columns.Length];

                for (int i = 0; i < Columns.Length; i++)
                {

                    columns[i] = Table.Columns[Columns[i]];

                }

                return Project(Table, columns, Include);

            }
        }
 
You must log in or register to reply here.
Back
Top