Convert Dataset to datatable

Good-day,

I have a dataset that contains two tables, with two relations.  (See schema image...)  I would like to flatten the dataset into one datatable using C# (VS 2008 .NET 3.5).

How do I flatten the two tables from the dataset into one table?
schema.png
setfocusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
you can do it like.

Dim dt1 As System.Data.DataTable = ds.Tables(0)
Dim dt2 As System.Data.DataTable = ds.Tables(1)


Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection
        conn.ConnectionString = "Data Source=.;Initial Catalog=adventureworks;Integrated Security=True"
        conn.Open()
 
        Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
        cmd.CommandType = System.Data.CommandType.StoredProcedure
        cmd.Connection = conn
        cmd.CommandText = "[getEmpList]"
 
        Dim dap As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim ds As System.Data.DataSet = New System.Data.DataSet
        dap.SelectCommand = cmd
        dap.Fill(ds)
        cmd.Dispose()
        conn.Close()
 
        Dim dt As System.Data.DataTable = ds.Tables(0)

Open in new window

0
drypzCommented:
Hi! Why don't you just create a dataview?  Something like

        DataViewManager dm = new DataViewManager(ds); //assuming ds is your dataset that has 2 tables.

       then if you want to bind in a gridview, then do it like

       Gridview1.DataSource = dm.CreateDataView();
       Gridview1.DataBind();
0
DhaestCommented:
I would also use linq to query the both dataTables.
LINQ Query Expressions (C# Programming Guide).

You can link the result directly to your datagridview, or store them in a datatable first (see example below)
// Direct linking
var results = from r in dtTable1.AsEnumerable()              
         join c in dtTable2.AsEnumerable() 
              on c.categoryID equals r.CategoryID
      select new List<object>(r.ItemArray).Concat(new List<object>() { c.Field2 })
datagridview.DataSource = results;
 
// Storing it in an datatable first
// get the rows you want and combine them into logical rows here:
var combinedRows = from p1 in dt1.AsEnumerable() join p2 in dt2.AsEnumerable() on p1["ID"] equals p2["ID"] orderby p1["ID"] select new { p1,p2 };
 
// now make the new datatable
DataTable dt = new DataTable();
DataColumn dcId = new DataColumn("ID", typeof(int));
DataColumn dcType = new DataColumn("Type", typeof(string));
DataColumn dcPart = new DataColumn("Part", typeof(int));
DataColumn dcQuantity = new DataColumn("Quantity", typeof(int));
dt.Columns.Add(dcId);
dt.Columns.Add(dcType);
dt.Columns.Add(dcPart);
dt.Columns.Add(dcQuantity);
//table is made, now just take and insert your values into the new DataTable
 
foreach (var v in combinedRows)
{
    DataRow dr = dt.NewRow();
    dr["ID"] = v.p1["ID"];//this is in v.p1
    dr["Type"] = v.p1["Type"];
    dr["Part"] = v.p2["Part"];//notice we are looking in v.p2 here now
    dr["Quantity"] = v.p2["Quantity"];
    dt.Rows.Add(dr);
}
 
//now put it on the screent o make sure it worked
GridView gv = new GridView();
gv.DataSource = dt;
gv.DataBind();
form1.Controls.Add(gv);

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

setfocusAuthor Commented:
Thanks for all of your posts;

RiteshShah: A requirement for this is to be in C#

drypz: CreateDataView requires a datatable as a parameter;

Dhaest: The LINQ option looks very interesting, and I think would work.  If you look at the original schema posted, you'll notice that the table 'Category' has a one to many relationship with itself.  How could this be coded in LINQ?

Thanks for your time
0
DhaestCommented:
You can normally join as many as you like

var results = from c in category.AsEnumerable()              
                            join s in site.AsEnumerable()
                                 on r.categoryID equals s.CategoryID
                            join c2 in category.AsEnumerable()  
                                on c2.categoryID_0 equals c.category
      select r, c, s)
0
setfocusAuthor Commented:
I have run into some difficulties with LINQ;  when I run the code below, I receive an error: "The value for column 'Category_Id_0' in table 'Category' is DBNull."

The Category table consists of:

Catagory_ID, Category_Id_0, and Name.

Category_Id_0 is the main category, and some records are null; while Catagory_ID is the subcategory and maps to the other table...

How can a join be dome if this is the case?

Thanks for your time;

            var root =
                from p in catalog1.Category.AsEnumerable()
                join c in catalog1.Site.AsEnumerable()
                    on
                    p.Category_Id
                    equals
                    c.Category_Id
                join c2 in catalog1.Category.AsEnumerable()
                    on
                    p.Category_Id
                    equals
                    c2.Category_Id_0
                select new
                           {
                               ProductName = p.Name,
                               Category = c.Title
                           };

Open in new window

0
DhaestCommented:
Please give us some time to research this ....

I tried the code below. The only thing I still need to figure out is the dbnull.Value
        private void ConvertDataSetToDataTable()
        {
            DataSet dsTables = new DataSet();
            // Create the table Category
            DataTable dtCategory = new DataTable("Category");
            DataColumn dcName = new DataColumn("Name", typeof(string));
            DataColumn dcCategory_ID = new DataColumn("Category_ID", typeof(int));
            DataColumn dcCategory_ID_0 = new DataColumn("Category_ID_0", typeof(int));
            dtCategory.Columns.Add(dcName);
            dtCategory.Columns.Add(dcCategory_ID);
            dtCategory.Columns.Add(dcCategory_ID_0);
            dsTables.Tables.Add(dtCategory);
            // Create the table Site
            DataTable dtSite = new DataTable("Site");
            DataColumn dcsID = new DataColumn("ID", typeof(int));
            DataColumn dcsTitle = new DataColumn("Title", typeof(string));
            DataColumn dcsDescription = new DataColumn("Description", typeof(string));
            DataColumn dcsCategory_ID = new DataColumn("Category_ID", typeof(int));
            dtSite.Columns.Add(dcsID);
            dtSite.Columns.Add(dcsTitle);
            dtSite.Columns.Add(dcsDescription);
            dtSite.Columns.Add(dcsCategory_ID);
            dsTables.Tables.Add(dtSite);
            // Fill table category
            DataRow drCategory = dtCategory.NewRow();
            drCategory[dcName] = "Test";
            drCategory[dcCategory_ID] = "1";
            drCategory[dcCategory_ID_0] = "2";
            dtCategory.Rows.Add(drCategory);
 
            drCategory = dtCategory.NewRow();
            drCategory[dcName] = "Test 2";
            drCategory[dcCategory_ID] = "2";
            drCategory[dcCategory_ID_0] = "2";
            dtCategory.Rows.Add(drCategory);
 
            // Fill table site
            DataRow drSite = dtSite.NewRow();
            drSite[dcsID] = "1";
            drSite[dcsTitle] = "Title 1";
            drSite[dcsDescription] = "Description 1";
            drSite[dcsCategory_ID] = "1";
            dtSite.Rows.Add(drSite);
 
            drSite = dtSite.NewRow();
            drSite[dcsID] = "2";
            drSite[dcsTitle] = "Title 2";
            drSite[dcsDescription] = "Description 2";
            drSite[dcsCategory_ID] = "1";
            dtSite.Rows.Add(drSite);
            // query both tables
            var root =
                from p in dtCategory.AsEnumerable()
                join c in dtSite.AsEnumerable()
                    on p["Category_ID"] equals c["Category_ID"]
                join c2 in dtCategory.AsEnumerable()
                    on  p["Category_Id"] equals c2["Category_Id_0"]
                into cat
                select new
                {
                    ProductName = p["Name"],
                    Category = c["Title"]
                };
            // show result
 
            foreach (var v in root)
            {
                Console.WriteLine(v.Category + " " + v.ProductName);
            }
 
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            ConvertDataSetToDataTable();
        }

Open in new window

0
DhaestCommented:
By the way, why should you add the third join if you don't use this table ?
I see in the query, the Category table just once !

var root =
                from p in catalog1.Category.AsEnumerable()
                join c in catalog1.Site.AsEnumerable()
                    on
                    p.Category_Id
                    equals
                    c.Category_Id
                join c2 in catalog1.Category.AsEnumerable()
                    on
                    p.Category_Id
                    equals
                    c2.Category_Id_0
                select new
                           {
                               ProductName = p.Name,
                               Category = c.Title
                           };
0
DhaestCommented:
To overcome the problem of DBNull, you can concatenate several linq-query's together
Example below
        private void ConvertDataSetToDataTable()
        {
            DataSet dsTables = new DataSet();
            // Create the table Category
            DataTable dtCategory = new DataTable("Category");
            DataColumn dcName = new DataColumn("Name", typeof(string));
            DataColumn dcCategory_ID = new DataColumn("Category_ID", typeof(int));
            DataColumn dcCategory_ID_0 = new DataColumn("Category_ID_0", typeof(int));
            dtCategory.Columns.Add(dcName);
            dtCategory.Columns.Add(dcCategory_ID);
            dtCategory.Columns.Add(dcCategory_ID_0);
            dsTables.Tables.Add(dtCategory);
            // Create the table Site
            DataTable dtSite = new DataTable("Site");
            DataColumn dcsID = new DataColumn("ID", typeof(int));
            DataColumn dcsTitle = new DataColumn("Title", typeof(string));
            DataColumn dcsDescription = new DataColumn("Description", typeof(string));
            DataColumn dcsCategory_ID = new DataColumn("Category_ID", typeof(int));
            dtSite.Columns.Add(dcsID);
            dtSite.Columns.Add(dcsTitle);
            dtSite.Columns.Add(dcsDescription);
            dtSite.Columns.Add(dcsCategory_ID);
            dsTables.Tables.Add(dtSite);
            // Fill table category
            DataRow drCategory = dtCategory.NewRow();
            drCategory[dcName] = "Test";
            drCategory[dcCategory_ID] = "1";
            drCategory[dcCategory_ID_0] = "2";
            dtCategory.Rows.Add(drCategory);
 
            drCategory = dtCategory.NewRow();
            drCategory[dcName] = "Test 2";
            drCategory[dcCategory_ID] = "2";
            drCategory[dcCategory_ID_0] = DBNull.Value;
            dtCategory.Rows.Add(drCategory);
 
            // Fill table site
            DataRow drSite = dtSite.NewRow();
            drSite[dcsID] = "1";
            drSite[dcsTitle] = "Title 1";
            drSite[dcsDescription] = "Description 1";
            drSite[dcsCategory_ID] = "1";
            dtSite.Rows.Add(drSite);
 
            drSite = dtSite.NewRow();
            drSite[dcsID] = "2";
            drSite[dcsTitle] = "Title 2";
            drSite[dcsDescription] = "Description 2";
            drSite[dcsCategory_ID] = "2";
            dtSite.Rows.Add(drSite);
            // query both tables
            var root =
                from p in dtCategory.AsEnumerable()
                join c in dtSite.AsEnumerable()
                    on p["Category_ID"] equals c["Category_ID"]
                where p["Category_Id_0"] != DBNull.Value
                select new
                {
                    ProductName = p["Name"],
                    Category = c["Title"]
                };
            var root2 =
                from p in dtCategory.AsEnumerable()
                join c in dtSite.AsEnumerable()
                    on p["Category_ID"] equals c["Category_ID"]
                where p["Category_Id_0"] == DBNull.Value
 
                select new
                {
                    ProductName = p["Name"],
                    Category = c["Title"]
                };
            
            var root3 =
                (from p in dtCategory.AsEnumerable()
                join c in dtSite.AsEnumerable()
                    on p["Category_ID"] equals c["Category_ID"]
                where p["Category_Id_0"] != DBNull.Value
                                select new
                {
                    ProductName = p["Name"],
                    Category = c["Title"]
                }).Concat
                (
                from p in dtCategory.AsEnumerable()
                join c in dtSite.AsEnumerable()
                    on p["Category_ID"] equals c["Category_ID"]
                where p["Category_Id_0"] == DBNull.Value
                select new
                {
                    ProductName = p["Name"],
                    Category = c["Title"]
                });
 
 
            // show result
 
            foreach (var v in root)
            {
                Console.WriteLine(v.Category + " " + v.ProductName);
            }
            foreach (var v in root2)
            {
                Console.WriteLine(v.Category + " " + v.ProductName);
            }
 
            foreach (var v in root3)
            {
                Console.WriteLine(v.Category + " " + v.ProductName);
            }
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            ConvertDataSetToDataTable();
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DhaestCommented:
In post 24096512, I gave a complete solution with a solution for the dbnull-problem in linq
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.