Solved

Convert Dataset to datatable

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Authentication of Web Services 3 46
.Net Web Site IIS Web.Config Content-Security-Policy 1 36
Receiving a string from a WebService Push 21 32
Exit the loop 4 31
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

16 Experts available now in Live!

Get 1:1 Help Now