Copy Data from MS Access DB to Sql DB (Copy data from 1 db to another db Sample)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace menuBar
{
    public partial class WinApp : Form
    {
        OleDbConnection ObjOleDbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Tawaf Offline Application\jtodf.tod; Jet OLEDB:Database Password=Cracker4Life");

        OleDbDataAdapter ObjOleDbDataAdapter;

        SqlConnection ObjSqlConnection = new SqlConnection("Data Source=176.12.14.29;Initial Catalog=AlMohsinTravels;User ID=sa;Password=Verity1@9");

        SqlCommand ObjSqlCommand;

        //DataSet ObjDataSet;

        public WinApp()
        {
            InitializeComponent();
        }



        protected DataSet ReadTableData(string TableName)
        {
            DataSet oledbDataset = new DataSet();
            ObjOleDbDataAdapter = new OleDbDataAdapter("select * from " + TableName + "", ObjOleDbConnection);
            ObjOleDbDataAdapter.Fill(oledbDataset);
            return oledbDataset;
        }

        protected int InsertQuery(string fromTable, string ToTable)
        {
            DataSet Objds = ReadTableData(fromTable);

            string strCmd = "";//"Insert Into " + ToTable +" (";
            //for (int iCol = 0; iCol < Objds.Tables[0].Columns.Count; iCol++)
            //{
            //    strCmd += Objds.Tables[0].Columns[iCol].ColumnName+",";
            //}

            //strCmd = strCmd.TrimEnd(',');
            //strCmd += ")";
            //strCmd += " values(";

            ObjSqlConnection.Open();
            for (int iRow = 0; iRow < Objds.Tables[0].Rows.Count; iRow++)
            {
                strCmd = "Insert Into " + ToTable + "  values(";
                for (int iColumn = 0; iColumn < Objds.Tables[0].Columns.Count; iColumn++)
                {
                    //strCmd += Objds.Tables[0].Columns[iColumn].ColumnName + ",";

                    if (Objds.Tables[0].Rows[iRow][iColumn].ToString() != "")
                        // strCmd+=
                        strCmd += "'" + Objds.Tables[0].Rows[iRow][iColumn] + "'" + ",";
                    //if (Objds.Tables[0].Rows[iRow][iColumn].ToString() == "0")
                    //    // strCmd+=
                    //    strCmd += "'" + "0" + "'" + ",";
                    else
                        strCmd += "Null,";

                }
                strCmd = strCmd.TrimEnd(',');
                strCmd += ")";
                ObjSqlCommand = new SqlCommand(strCmd, ObjSqlConnection);
                int count = ObjSqlCommand.ExecuteNonQuery();
            }
            //strCmd = strCmd.TrimEnd(',');
            //strCmd += ")";


            ObjSqlConnection.Close();
            return 1;

        }

        private void btnSendToSql_Click(object sender, EventArgs e)
        {
            btnSendToSql.Enabled = false;
            this.Text = "Inserting........";
            label1.Visible = true;
            label2.Visible = true;
            backgroundWorker.RunWorkerAsync();


            #region old data
            //This is agents
            //OleDbDataAdapter ObjOleDbDataAdapterAgents = new OleDbDataAdapter("select * from Agents", ObjOleDbConnection);

            //DataSet ObjDataSetAgents = new DataSet();

            //ObjOleDbDataAdapterAgents.Fill(ObjDataSetAgents);


            //for (int i = 0; i < ObjDataSetAgents.Tables[0].Rows.Count; i++)
            //{

            //   ObjSqlCommand = new SqlCommand("insert into Agents(EA_Branch,EA_NAME_AR,EA_Name_LA,EA_Country,UO_Code,UO_Branch,UO_Name_AR,UO_Name_LA,UName,UPass,UDisplay) values(" + ObjDataSetAgents.Tables[0].Rows[i][1].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][2].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][3].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][4].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][5].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][6].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][7].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][8].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][9].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][10].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][11].ToString() + "')", ObjSqlConnection);

            //    ObjSqlConnection.Open();

            //    int RES = ObjSqlCommand.ExecuteNonQuery();

            //    ObjSqlConnection.Close();

            //    if (RES != 0)
            //    {
            //        //MessageBox.Show("Record Inserted");
            //    }

            //    else
            //    {
            //        //MessageBox.Show("Record Not Inserted");
            //    }
            //}


            //This is Group
            //OleDbDataAdapter ObjOleDbDataAdapterGroups = new OleDbDataAdapter("select * from Groups", ObjOleDbConnection);

            //DataSet ObjDataSetGroups = new DataSet();

            //ObjOleDbDataAdapterGroups.Fill(ObjDataSetGroups);


            //for (int i = 0; i < ObjDataSetGroups.Tables[0].Rows.Count; i++)
            //{

            //    ObjSqlCommand = new SqlCommand("insert into Groups(Grp_Name,Grp_Package,Grp_Embassy,Grp_Arrival,Grp_Departure,Grp_Status,Grp_Date,EA_Code,EA_Branch,Grp_Online,Grp_SentDate,Grp_Origin) values('" + ObjDataSetGroups.Tables[0].Rows[i][1].ToString() + "','" + ObjDataSetGroups.Tables[0].Rows[i][2].ToString() + "'," + ObjDataSetGroups.Tables[0].Rows[i][3].ToString() + ",'" + ObjDataSetGroups.Tables[0].Rows[i][4].ToString() + "','" + ObjDataSetGroups.Tables[0].Rows[i][5].ToString() + "'," + ObjDataSetGroups.Tables[0].Rows[i][6].ToString() + "," + ObjDataSetGroups.Tables[0].Rows[i][7].ToString() + "," + ObjDataSetGroups.Tables[0].Rows[i][8].ToString() + "," + ObjDataSetGroups.Tables[0].Rows[i][9].ToString() + "," + ObjDataSetGroups.Tables[0].Rows[i][10].ToString() + "," + ObjDataSetGroups.Tables[0].Rows[i][11].ToString() + "," + ObjDataSetGroups.Tables[0].Rows[i][12].ToString() + ")", ObjSqlConnection);

            //    ObjSqlConnection.Open();

            //    int RES = ObjSqlCommand.ExecuteNonQuery();

            //    ObjSqlConnection.Close();

            //    if (RES != 0)
            //    {
            //        //MessageBox.Show("Record Inserted");
            //    }

            //    else
            //    {
            //        //MessageBox.Show("Record Not Inserted");
            //    }
            //}

            //This is MutData
            //OleDbDataAdapter ObjOleDbDataAdapterMutData = new OleDbDataAdapter("select * from MutData", ObjOleDbConnection);

            //DataSet ObjDataSetMutData = new DataSet();

            //ObjOleDbDataAdapterMutData.Fill(ObjDataSetMutData);


            //for (int i = 0; i < ObjDataSetMutData.Tables[0].Rows.Count; i++)
            //{


            //    for (int j = 1; j < ObjDataSetMutData.Tables[0].Columns.Count; j++)
            //    {
            //       //ObjSqlCommand = new SqlCommand("insert into MutData(GRP_Code,UmraType,FirstName_AR,FatherName_AR,GrandFatherName_AR,FamilyName_AR,FirstName_LA,FatherName_LA,GrandFatherName_LA,FamilyName_LA,Title,Gender,DpnSerial,Nationality,MaritalStatus,MaritalStatus,Education,Job,CurrCountry,CurrCity,BirthCountry,BirthCity,BirthDate,MutAge,Mahram,Relation,PassportNo,PassportType,PassportCity,PassportCountry,PassportIssue,PassportExpire,Address_AR,Area_AR,Address_LA,Area_LA,PhoneNo,FaxNo,MobileNo,ZipCode,UO_Code,EA_Code,EA_Branch,StatusID,User_ID,Online,MainMutID,FamilyNo,Remark,DevFlag,GrpTemp) values('" + ObjDataSetAgents.Tables[0].Rows[i][1].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][2].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][3].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][4].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][5].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][6].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][7].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][8].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][9].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][10].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][11].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][11].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][11].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][12].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][13].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][14].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][15].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][16].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][17].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][18].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][19].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][20].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][21].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][22].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][23].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][24].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][25].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][26].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][27].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][28].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][29].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][30].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][31].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][32].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][33].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][34].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][35].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][36].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][37].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][38].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][39].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][40].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][41].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][42].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][43].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][44].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][45].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][46].ToString() + ",'" + ObjDataSetAgents.Tables[0].Rows[i][47].ToString() + "','" + ObjDataSetAgents.Tables[0].Rows[i][48].ToString() + "'," + ObjDataSetAgents.Tables[0].Rows[i][49].ToString() + "," + ObjDataSetAgents.Tables[0].Rows[i][50].ToString() + ")", ObjSqlConnection);
            //        ObjSqlCommand = new SqlCommand("insert into MutData(UmraType,FirstName_AR,FatherName_AR,GrandFatherName_AR,FamilyName_AR,FirstName_LA,FatherName_LA,GrandFatherName_LA,FamilyName_LA,Title,Gender,DpnSerial,Nationality,MaritalStatus,MaritalStatus,Education,Job,CurrCountry,CurrCity,BirthCountry,BirthCity,BirthDate,MutAge,Mahram,Relation,PassportNo,PassportType,PassportCity,PassportCountry,PassportIssue,PassportExpire,Address_AR,Area_AR,Address_LA,Area_LA,PhoneNo,FaxNo,MobileNo,ZipCode,UO_Code,EA_Code,EA_Branch,StatusID,User_ID,Online,MainMutID,FamilyNo,Remark,DevFlag,GrpTemp) values('" + ObjDataSetMutData.Tables[0].Rows[i][j].ToString() + "')", ObjSqlConnection);
            //    }
            //        ObjSqlConnection.Open();

            //        int RES = ObjSqlCommand.ExecuteNonQuery();

            //    ObjSqlConnection.Close();

            //    if (RES != 0)
            //    {
            //        //MessageBox.Show("Record Inserted");
            //    }

            //    else
            //    {
            //        //MessageBox.Show("Record Not Inserted");
            //    }
            //}

            //This is Settings
            //OleDbDataAdapter ObjOleDbDataAdapter = new OleDbDataAdapter("select * from Settings", ObjOleDbConnection);

            //DataSet ObjDataSet = new DataSet();

            //ObjOleDbDataAdapter.Fill(ObjDataSet);



            //for (int i = 0; i < ObjDataSet.Tables[0].Rows.Count; i++)
            //{
            //    SqlCommand ObjSqlCommand = new SqlCommand("insert into Settings(Login_Pin, Login_Required, Default_Lang, Default_Arrival, Arrival_Days, Default_PassExpire, PassExpire_Days, PassValidity) values('" + ObjDataSet.Tables[0].Rows[i][1].ToString() + "'," + ObjDataSet.Tables[0].Rows[i][2].ToString() + "," + ObjDataSet.Tables[0].Rows[i][3].ToString() + "," + ObjDataSet.Tables[0].Rows[i][4].ToString() + "," + ObjDataSet.Tables[0].Rows[i][5].ToString() + "," + ObjDataSet.Tables[0].Rows[i][6].ToString() + ",'" + ObjDataSet.Tables[0].Rows[i][7].ToString() + "'," + ObjDataSet.Tables[0].Rows[i][8].ToString() + ")", ObjSqlConnection);

            //    ObjSqlConnection.Open();

            //    int RES = ObjSqlCommand.ExecuteNonQuery();

            //    ObjSqlConnection.Close();

            //    if (RES != 0)
            //    {
            //        //MessageBox.Show("Record Inserted");
            //    }

            //    else
            //    {
            //        //MessageBox.Show("Record Not Inserted");
            //    }
            //}

            #endregion

        }

        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {

            string[] TableNames = new string[] { "Agents", "Groups", "MutData", "L_Cities", "L_EduLevels", "L_Embassies", "L_Genders", "L_GrpStatus", "L_MaritalStatus", "L_MutStatus", "L_Nationalities", "L_MutStatus", "L_MutTypes", "L_Nationalities", "L_Packages", "L_PassTypes", "L_Relations", "L_Titles", "Settings" };

            for (int iCount = 0; iCount < TableNames.Count(); iCount++)
            {
                label2.Invoke(new MethodInvoker(() => label2.Text = TableNames[iCount]));
                label2.Invoke(new MethodInvoker(() => label2.ForeColor=Color.Red));
             
                backgroundWorker.ReportProgress(((100 / TableNames.Count()) * iCount));
                InsertQuery(TableNames[iCount], TableNames[iCount]);
            }

        }

        private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            SendToSql.Value = e.ProgressPercentage;


        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            SendToSql.Value = 0;
            btnSendToSql.Enabled = true;
            label2.Visible = false;
            label1.Visible = false;
            this.Text = "Insert data To Sql";
            MessageBox.Show("Sucessfully Inserted");
       
           
        }

    }
}