C# - How to merge to MDB table values and write those values in Excel File

every mdb will have one table and every table will have one record.

i want to combine all three records and write into excel file in particular range.

you probably can load the records into a datatable, and merge them together as per your requirement. then you should able to output the datatable to Excel when necessary.

Link all three database tables to the same FE.  Then you can use a Union query to get data from all three tables into a single recordset.  Then you can use TransferSpreadsheet to export the union query to the spreadsheet.

Alternatively, you can use OLE automation to export each table separately to a specific set of cells.

Thanks for your reply..

we are selecting multiple db and reading one by one as per below code..


foreach (String file1 in openFileDialog1.FileNames)
{

   ds = mdbDataset.ReadMdbData(file1, false);
}

But, i waint to combine the dataset of all the databases and write into Excel File.

try use the :

DataSet.Merge Method (DataTable)
https://msdn.microsoft.com/en-us/library/803bh6bc(v=vs.110).aspx

hence, you could try this (not tested)

DataSet ds_combined = new DataSet();

foreach (String file1 in openFileDialog1.FileNames)
{
   ds = mdbDataset.ReadMdbData(file1, false);
   ds_combined = ds_combined.merge(ds);
}

Select allOpen in new window

then refer to ds_combined

or simply:

foreach (String file1 in openFileDialog1.FileNames)
{
   ds.merge(mdbDataset.ReadMdbData(file1, false));
}

Select allOpen in new window

getting error : cannot implicitly convert type 'void' to 'system.data.dataset' for the below line..

 ds_combined = ds_combined.merge(ds);

ooops, try this instead:

ds_combined.merge(ds);

Select allOpen in new window

?

it will not return anything.

I have one row in every table. so it should return two rows.

see my complete code below..

foreach (String file1 in openFileDialog1.FileNames)
{
	   DataSet ds_combined = new DataSet();
           DataTable res = new DataTable();
	   ds = mdbDataset.ReadMdbData(file1, false);
           res = frmMain.MergeMultiplemdb(ds);
           ds_combined.Merge(ds); // Nothing will display
}

private static DataTable MergeMultiplemdb(DataSet ds1)
{
            DataTable retval = new DataTable();

            string tablename1 = "EMP_TABLE";
            DataTable DTRes1 = ds1.Tables[tablename1];
           

            var results = (from myRow in DTRes1.AsEnumerable()
                          
                           select myRow).CopyToDataTable();

            DataTable dtTemp = (DataTable)results;

            retval = dtTemp;

          
            return retval;

}

Select allOpen in new window

how's your table in your Access databases look like? are these tables identical?

can you also post codes for method: ReadMdbData?

both table structure is same and also the table values are also same some time.

public static DataSet ReadMdbData(string mdbname, bool pass)
        {
            DataSet ds = new DataSet(mdbname);
            string connectionstring = string.Empty;
            if (pass == true)
            {
                connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + mdbname + ";" + "Jet OLEDB:Database Password=XXXXXXXXX";
            }
            else
            {
                connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
            }
           
            using (OleDbConnection cn = new OleDbConnection(connectionstring))
            {
                cn.Open();                
                DataTable Schema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null,null,null,"TABLE"});
                for (int i = 0; i <= Schema.Rows.Count - 1; i++)
                {
                    try
                    {
                        DataTable dt = new DataTable((Schema.Rows[i]["TABLE_NAME"]).ToString());
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + (Schema.Rows[i]["TABLE_NAME"]).ToString(), cn))
                        {
                            adapter.Fill(dt);
                        }
                        ds.Tables.Add(dt);
                    }
                    catch (Exception)
                    {

                    }                   
                }
                cn.Close();
            }            
            return ds;
        }

Select allOpen in new window

NOTE : it will return last table value in the loop

just found that you declared the DataSet ds_combined within the foreach loop, which is a wrong approach.

I tried to load the datatable that derived from ds_combined into a datagridview and it's working well.

openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            if (openFileDialog1.FileName != "")
            {
                DataSet ds_combined = new DataSet();
                foreach (String file1 in openFileDialog1.FileNames)
                {
                    DataTable res = new DataTable();
                    ds = mdbDataset.ReadMdbData(file1, false);
                    res = frmMain.MergeMultiplemdb(ds);
                    ds_combined.Merge(ds);
                }
                dataGridView1.AutoGenerateColumns= true;
                dataGridView1.DataSource = ds_combined.Tables[0];
            }

Select allOpen in new window

Thanks for your mentioning wrong place.

How you are getting the results..?

because i am using the above code. But,  res = frmMain.MergeMultiplemdb(ds);

"res" return Datatable.

How you are using "ds" in merge section. see the code below

ds_combined.Merge(ds);

I corrected and i used "res" instead of "ds"

ds_combined.Merge(res);.

eventhogu i am getting table1 and table2 both in seperate (for my case two databases.. not merging.

when i print below after for eachloop getting only one record.

Datatable ds_Final = ds_combined.Tables[0];

How it is possible. eventhough it is not merging and display table1 and table2,etc..

Excellent. done a very good job.

the meaning of using using the procedure is, i want to use only specific columns in a table. (means it will not required to read all columns).

Note : How do we specify the particular columns in the table..

 DataTable res = ds_combined.Tables["EMP_TABLE"]; (instead of displaying all the columns, iwant to display only particular columns)..

How to specify the particular column in   the below code.

 DataTable res = ds_combined.Tables["EMP_TABLE"];

Thanks for your excellent and great help.

Kudoos..

How to specify the particular column in   the below code.

 DataTable res = ds_combined.Tables["EMP_TABLE"];

a quick fix would be remove the columns after that, like:

DataTable res = ds_combined.Tables["EMP_TABLE"];
                String fieldToKeep = "Field1";
                int recCnt = res.Columns.Count;
                for (int i = recCnt - 1; i >= 0; i--)
                {
                    if (res.Columns[i].ColumnName != fieldToKeep)
                    {
                        res.Columns.RemoveAt(i);
                    }
                }

Select allOpen in new window

if more than two fields to maintain, shall is use below code..?

DataTable res = ds_combined.Tables["EMP_TABLE"];
                String fieldToKeep1 = "Field1";
                string fieldToKeep2 = "field2";
               string fieldToKeep3 = "field3:'
               //etc....
                int recCnt = res.Columns.Count;
                for (int i = recCnt - 1; i >= 0; i--)
                {
                    if (res.Columns[i].ColumnName != fieldToKeep1)
                    {
                        res.Columns.RemoveAt(i);
                    }
                     if (res.Columns[i].ColumnName != fieldToKeep2)
                    {
                        res.Columns.RemoveAt(i);
                    }
                      if (res.Columns[i].ColumnName != fieldToKeep3)
                    {
                        res.Columns.RemoveAt(i);
                    }
                }

Select allOpen in new window

Thanks....

you can try like this as well:

DataTable res = ds_combined.Tables["EMP_TABLE"];
                String[] fieldsToKeep = new string[] { "ID", "Field1" };
                int recCnt = res.Columns.Count;
                for (int i = recCnt - 1; i >= 0; i--)
                {
                    Boolean KeepIt = false;
                    for (int j = 0; j < fieldsToKeep.Length; j++)
                    {
                        if (res.Columns[i].ColumnName == fieldsToKeep[j])
                        {
                            KeepIt = true;
                            break;
                        }
                    }
                    if (KeepIt == false)
                    {
                        res.Columns.RemoveAt(i);
                    }
                }

Select allOpen in new window

Thanks and perfect...kudoos...

It's working....

Perfect Solution..