How to Merge this data tables in data set Correctly

Question

0.00/5 (No votes)

See more:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public partial class _Default : System.Web.UI.Page 
{
    DataSet ds;
  
    DataTable dt1,dt2;
    DataRow r;
    DataColumn c1, c2, c3, c4, c5, c6;
    protected void Page_Load(object sender, EventArgs e)
    {
        ds = new DataSet("D-base");
        dt1 = new DataTable();
        dt2 = new DataTable();
      
        c1 = new DataColumn("empno");
        c2 = new DataColumn("empname");
        c3 = new DataColumn("depno");
        c4 = new DataColumn("depno");
        c5 = new DataColumn("depname");
        c6 = new DataColumn("depaddress");
        dt1.TableName = "emp";
        dt2.TableName = "dept";
        dt1.Columns.Add(c1); dt1.Columns.Add(c2); dt1.Columns.Add(c3);
        dt2.Columns.Add(c4); dt2.Columns.Add(c5); dt2.Columns.Add(c6);

       

        //dt1.PrimaryKey = dt1.Columns[0];
        //dt2.PrimaryKey = dt2.Columns[3];
       
    }

 
    protected void Showdt_btn_Click(object sender, EventArgs e)
    {

        for (int i = 0; i < 10; i++)
        {
            r = dt1.NewRow();
            r[c1] = i;
            r[c2] = "ramu" + i;
            r[c3] = 100 + i;
            dt1.Rows.Add(r);
        }
        ds.Tables.Add(dt1);

        GridView1.DataSource = ds.Tables[0].DefaultView;
        GridView1.DataBind();
        GridView1.Visible = true;
        for (int i = 0; i < 10; i++)
        {
            r = dt2.NewRow();
            r[c4] = 100 + i;
            r[c5] = "IT deptno " + i;
            r[c6] = "Hyd cir no" + i;
            dt2.Rows.Add(r);
        }
        ds.Tables.Add(dt2);
        GridView2.DataSource = ds.Tables[1].DefaultView;
        GridView2.DataBind();
        GridView1.Visible = true;

        dt1.Merge(dt2, false, MissingSchemaAction.AddWithKey);
        GridView3.DataSource = ds.Tables[0].DefaultView;
        GridView3.DataBind();
        GridView3.Visible = true;
    }

in the Out put it is not getting correct format

there are blank values in between i want them to be correct
any solution?

empno empname depno depname    depaddress 
0     ramu0   100     
1     ramu1   101     
2     ramu2   102     
3     ramu3   103     
4     ramu4   104     
5     ramu5   105     
6     ramu6   106     
7     ramu7   107     
8     ramu8  108     
9     ramu9  109     
            100    IT deptno 0  Hyd cir no0 
            101    IT deptno 1  Hyd cir no1 
            102    IT deptno 2  Hyd cir no2 
            103    IT deptno 3  Hyd cir no3 
            104    IT deptno 4  Hyd cir no4 
            105    IT deptno 5  Hyd cir no5 
            106    IT deptno 6  Hyd cir no6 
            107    IT deptno 7  Hyd cir no7 
            108    IT deptno 8  Hyd cir no8 
            109    IT deptno 9  Hyd cir no9

here in this output i want to eliminate blank values and want to merge values below into blank spaces please tell me how ? it is possible to make out put likle hits

empno empname depno depname      depaddress 
 0    ramu0   100   IT deptno 0  Hyd cir no0   
 1    ramu1   101   IT deptno 1  Hyd cir no1  
 2    ramu2   102   IT deptno 2  Hyd cir no2    
 3    ramu3   103   IT deptno 3  Hyd cir no3    
 4    ramu4   104   IT deptno 4  Hyd cir no4    
 5    ramu5   105   IT deptno 5  Hyd cir no5    
 6    ramu6   106   IT deptno 6  Hyd cir no6   
 7    ramu7   107   IT deptno 7  Hyd cir no7    
 8    ramu8   108   IT deptno 8  Hyd cir no8    
 9    ramu9   109   IT deptno 9  Hyd cir no9

Posted 15-Aug-10 22:14pm

kakani santosh kumar

Updated 15-Aug-10 22:26pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chirantan Upadhyay · Answer 1 · 2010-08-15T22:49:00

Solution 2

Do One Thing. Create two tables Employee and Department. Add them to Data Set.
Now create Ralation between them
ds.Relations.Add("PersonPet",person.Columns["Id"], pet.Columns["OwnerId"]);
ds.Relations.Add("DeptEmp",Dept.Columns["DeptId"], Emp.Columns["DeptId"]);
Now Traverse Dept Table and get all child rows from Employee
foreach (DataRow DeptRow in Dept.Rows)
{
Console.WriteLine("{0} - {1}",DeptRow ["DeptId"], DeptRow["DeptName"]);

foreach (DataRow EmpRow in DeptRow.GetChildRows("DeptEmp"))
{
Console.WriteLine("{0} - {1}", EmpRow["EmpId"], petRow["EmpName"]);
}
}
Here is am writing to console, you can store the values in a table and bind to some grid view

Posted 15-Aug-10 22:49pm

Chirantan Upadhyay

Comments

kakani santosh kumar 16-Aug-10 6:07am

not coming out oput its saying "Object reference not set to an instance of an object."

 DataTable dt3 = new DataTable("dt3");
        c12 = new DataColumn("empno");
        c22 = new DataColumn("empname");
        c32 = new DataColumn("depno");
        c42 = new DataColumn("depname");
        dt3.Columns.Add(c12); dt3.Columns.Add(c22); dt3.Columns.Add(c32); dt3.Columns.Add(c42);


        ds.Relations.Add("DepEmp", ds.Tables["dt1"].Columns[2], ds.Tables["dt2"].Columns[0]);
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            r = dt3.NewRow();
            r[0]= dr["empno"];
            r[1] = dr["empname"];

           
            foreach (DataRow Er in dr.GetChildRows("DepEmp"))
            {
               r[2]=Er["depno"];
                r[3]=Er["depname"];
            }
        
        }

Chirantan Upadhyay 18-Aug-10 17:12pm

please look at your code carefully. initially it looks bad. Dept Table will have DeptNo and Dept name. Employee table will have EmpNo, EmpName and DeptNo. DeptNo will work as Relation "DeptEmp". now traverse Dept Table and get childs by GetChildRows. then add all these values DeptNo,EmpNo, EployeeName in Combined Table (DeptNo,Empno,EmpName).
also there are so many objest so let me know in which line there is error. obviously looks it will be with dt1 or dt2 . because i can't see instance creation using new or adding columns etc.