Convert Dataset to datatable

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)

Select allOpen in new window

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();

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);

Select allOpen in new window

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

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)

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
                           };

Select allOpen in new window

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();
        }

Select allOpen in new window

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
                           };

In post 24096512, I gave a complete solution with a solution for the dbnull-problem in linq