Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5015
  • Last Modified:

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
0
setfocus
Asked:
setfocus
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now