How to merge the datatable in dataset? Asp.net C#

Question

0.00/5 (No votes)

See more:

i have one excel document and multiple sheets.sheets have many columns and row.i upload the document into my project.and the multiple sheets is bind to tables(sheet name is set as table name) using for loop.after the bind i change the table name to source1,source2,source3 (three sheets). Now how to bind(merge) the three tables in single table (source1).

What I have tried:

i have tried to rename the all tables to same name but the duplicate table is not allowed.how to bind the same table.

Posted 11-Aug-16 3:27am

Raja Ganapathy

Updated 11-Aug-16 5:39am

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)

Karthik_Mahalingam · Accepted Answer · 2016-08-11T05:39:00

Solution 1

Its not possible to rename all the datatable names to same value.
use DataTable.Merge Method [^] to merge all the datatable's data from the dataset.

refer this example:
assuming your dataset contains 3 tables as below

C#

DataSet dataSetExcel = new DataSet();
DataTable dt1 = new DataTable();
dt1.Columns.Add("ID");
dt1.Columns.Add("Name");
dt1.Rows.Add(1, "one");

DataTable dt2 = new DataTable();
dt2.Columns.Add("ID");
dt2.Columns.Add("Name");
dt2.Rows.Add(2, "two");

DataTable dt3 = new DataTable();
dt3.Columns.Add("ID");
dt3.Columns.Add("Name");
dt3.Rows.Add(3, "three");
dataSetExcel.Tables.AddRange(new DataTable[] {dt1,dt2,dt3 });

try like this to merge all the data to a new DataTable

C#

DataTable dtFinal = new DataTable();
           for (int i = 0; i < dataSetExcel.Tables.Count; i++)
           {
               if (i == 0)
                   dtFinal = dataSetExcel.Tables[i].Copy();
               else
                   dtFinal.Merge(dataSetExcel.Tables[i]);
           }

or, you shall merge the data from sheet 2 and sheet 3 to sheet 1 and delete the sheet 2 and 3

C#

dataSetExcel.Tables[0].Merge(dataSetExcel.Tables[1]);
          dataSetExcel.Tables[0].Merge(dataSetExcel.Tables[2]);
          dataSetExcel.Tables.RemoveAt(1); //  duplicate code to avoid index exception
          dataSetExcel.Tables.RemoveAt(1); //  duplicate code to avoid index exception
          dataSetExcel.AcceptChanges();

Posted 11-Aug-16 5:39am

Karthik_Mahalingam

Comments

Raja Ganapathy 16-Aug-16 0:29am

I have used below code to convert Excelsheet to Dataset and named table as table1,table2,table3. and generate xml xml tags have combined all three tables.i have add column in table and start tag and tag as <table1></table1>... <table n></table n> now the starting and ending tag must be same.then only back end sql is get the value so how to merge the table.i try this code if any other method is available pls suggest me.sorry for the late reply and same question ask again and again.

for (int k = 0; k < ds.Tables.Count; k++)
{
int c = k + 1;
string sheetname = ds.Tables[k].ToString();
if (ds.Tables[k].ToString() == "ExcelSheetName" + c)
{
ds.DataSetName = "Search"; ds.Tables[k].TableName = "Table" + c; ds.Tables["Table" + c].AcceptChanges();
}
}

Karthik_Mahalingam 16-Aug-16 0:33am

if (ds.Tables[k].TableName == "ExcelSheetName" + c)

Raja Ganapathy 16-Aug-16 0:54am

this is example. "ExcelSheetName"+c it is excel sheet name uploader give the name to the excel sheet name(common)like Form 1,Form 2 upto Form N.Sheet is declare as common and the +c is add the number Form 1 Form 2 Form 3 ...

Karthik_Mahalingam 16-Aug-16 1:16am

so you need to convert "ExcelSheetName" to "Form"?

Raja Ganapathy 16-Aug-16 1:20am

Form is table name in dataset.

Karthik_Mahalingam 16-Aug-16 1:20am

boss, what is your input and what you are expecting

Raja Ganapathy 16-Aug-16 1:21am

i am near to answer. use this one.getting some errors only :D

DataTable dtFinal = new DataTable();
for (int i = 0; i < dataSetExcel.Tables.Count; i++)
{
if (i == 0)
dtFinal = dataSetExcel.Tables[i].Copy();
else
dtFinal.Merge(dataSetExcel.Tables[i]);
}

Karthik_Mahalingam 16-Aug-16 1:27am

this should work, i have testedd it

Raja Ganapathy 16-Aug-16 1:28am

Okay brother naan some change enn code la panni finish panniduven.merge ahutu thank you. :D

Raja Ganapathy 16-Aug-16 1:25am

My input is Excel Document.output is stored as datatable in dataset.but the back end developers need the same name for all tables.athavathu avunga enna sonnangana datatable la irunthu naan XML ah convert panni avungaluku kuduthuten but stating and ending tag vanthu <table1></table1> ippadi mudiyuthu ithe format la thaan ethana table la vanthalum mudiyuthu.avunga enna solrangana ethana table vanthalum starting tag and ending tag same iruntha easy ah get panniduvom nu solranga.ippadi thani thani table ah tags mudinja get panna mudiyathu nu sonnanga.ithaan full problem. sorry brother romba disturb pandra mathiri iruku.

Karthik_Mahalingam 16-Aug-16 1:28am

merge panna kodatha?

Raja Ganapathy 16-Aug-16 1:30am

Merge pannanum pannuna antha format ku vanthurum.ithaan peria issue va poitu iruku.avunga more than evalo table vanthalum start tag and end tag same ah irukanum nu solranga.athaan merge panni same table ah kudutha same ah irukum nu naan athuku idea keten.

Karthik_Mahalingam 16-Aug-16 1:56am

cal panrengala?

Raja Ganapathy 16-Aug-16 2:01am

Enna bro call?
mobile?

Karthik_Mahalingam 16-Aug-16 2:02am

s

Karthik_Mahalingam 16-Aug-16 2:03am

.