Tuesday, 9 July 2013

Copy table data from one database to another database using Datatable in C#.NET

Try this method to copy table data from one database to another database using Datatable..............

static public void CopyDatabaseRows(string ConnectionStringDEV, string ConnectionStringLOCAL, string queryString)
        {

            //Connect to first database table to retreive row/rows and populate dataset + datatable.
            DataSet dataSet = new DataSet();
            SqlConnection conn = new SqlConnection(ConnectionStringDEV);
            conn.Open();

            SqlCommand command = new SqlCommand(queryString, conn);
            DataTable dataTable = new DataTable();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, conn);


            dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
            dataAdapter.Fill(dataSet, "Product_Det");
            dataTable = dataSet.Tables["Product_Det"];
            conn.Close();


            //Connect to second Database and Insert row/rows.
            SqlConnection conn2 = new SqlConnection(ConnectionStringLOCAL);
            conn2.Open();

            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn2);
            bulkCopy.DestinationTableName = "Product_Det";
            bulkCopy.WriteToServer(dataTable);
        }