Data Table Copy not showing properly in GridView

Question

I have a stored procedure which actually concatenates 2 columns and returns a DataTable. Its as below:

create procedure [dbo].[spEnqIDFyYear]
as
begin
select CONVERT(nvarchar(50),enq_id)+'/'+CONVERT(nvarchar(50),YEAR(fy_year)) as EnqIDFyYear from Sample.dbo.enquiry_details
end
GO

The output is as follows:

EnqIDFyYear
1/2015
2/2014

I have another procedure, whose output is as below:

profile_name
ProfileA
ProfileB

I bind both the procedures to 2 DataTables, merge those 2 DataTables into a 3rd one and bind the resulting DataTable to the gridview.

But the gridview is not showing as proper. It shows as below:

The rows should be aligned to each other. How to achieve it?

Code behind is as below:

protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt1 = PopulateDashBoardGrid1();
            DataTable dt2 = PopulateDashBoardGrid2();
            DataTable dt3 = new DataTable();
            dt3 = dt1.Copy();
            dt3.Merge(dt2);
            GridView1.DataSource = dt3;
            GridView1.DataBind();
        }

        //Populate the main DashBoard Grid
        public DataTable PopulateDashBoardGrid1()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("spEnqIDFyYear", con);
                DataTable dt = new DataTable();
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.Fill(dt);
                return dt;
            }

        }

        public DataTable PopulateDashBoardGrid2()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("spClientProfileName", con);
                DataTable dt = new DataTable();
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.Fill(dt);
                return dt;
            }

        }

Experts please help.

Regards Anurag

Answer 1

You are getting that kind of output, because Merge works in that way if the columns are not same of two datatables. So you need to write some custom code.

Here is what I have:-

static DataTable MergeDataTables(DataTable dt1, DataTable dt2)
{
     DataTable dt3 = dt1.Copy();
     foreach (DataColumn dc in dt2.Columns)
     {
         dt3.Columns.Add(dc.ColumnName).DataType = dc.DataType;
     }

     for (int i = 0; i < dt3.Rows.Count; i++)
     {
         foreach (DataColumn dc in dt2.Columns)
         {
             string col = dc.ColumnName;
             dt3.Rows[i][col] = dt2.Rows[i][col];
         }
     }
     return dt3;
}

You can call this method to merge both datatables.