Solved

Convert Dataset to datatable

Posted on 2009-04-01
12
4,735 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:setfocus
12 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24046529
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
 
LVL 13

Expert Comment

by:drypz
ID: 24046701
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 24046935
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:setfocus
ID: 24056350
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 24057000
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
 

Author Comment

by:setfocus
ID: 24065892
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 24096340
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 24096384
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
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 24096512
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 24135302
In post 24096512, I gave a complete solution with a solution for the dbnull-problem in linq
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question