Solved

Convert Dataset to datatable

Posted on 2009-04-01
12
4,653 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:setfocus
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:setfocus
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
In post 24096512, I gave a complete solution with a solution for the dbnull-problem in linq
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
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!
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now