C#複製資料庫，將資料庫資料轉到另一個資料庫

標籤：資料庫   dataset   c#   複製   

本文章以一個表為例，要轉多個表則可將DataSet關聯多個表，下面給出完整代碼，包括引用以及main函數與複製函數。

要說明的是，必須先用Sql語句複製表結構，才能順利的使用以下代碼複製資料。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Data.Common;namespace CopyData{    class Program    {        static void Main(string[] args)        {            //要複製的表名            string table = "V_Position";                        //構造連接字串            SqlConnectionStringBuilder builder1 = new SqlConnectionStringBuilder();            builder1.DataSource = ".\\CANFLY";      //執行個體名稱為CANFLY            builder1.InitialCatalog = "desdata";    //目標資料庫            builder1.IntegratedSecurity = true;     //使用Windows身分識別驗證            SqlConnectionStringBuilder builder2 = new SqlConnectionStringBuilder();            builder2.DataSource = ".\\CANFLY";            builder2.InitialCatalog = "bddata";     //來源資料庫            builder2.IntegratedSecurity = true;            //調用複製資料庫函數            InsertTable(builder1.ConnectionString, builder2.ConnectionString, table);        }                //參數為兩個資料庫的連接字串        private static void InsertTable(string conString1, string conString2, string tabStr)        {            //串連資料庫            SqlConnection conn1 = new SqlConnection();            conn1.ConnectionString = conString1;            conn1.Open();            SqlConnection conn2 = new SqlConnection();            conn2.ConnectionString = conString2;            conn2.Open();            //填充DataSet1            SqlDataAdapter adapter1 = new SqlDataAdapter("select * from " + tabStr, conn1);            DataSet dataSet1 = new DataSet();            if (dataSet1 != null)            {                adapter1.Fill(dataSet1, tabStr);            }            SqlDataAdapter adapter2 = new SqlDataAdapter("select * from " + tabStr, conn2);            DataSet dataSet2 = new DataSet();            SqlCommand cmd2 = new SqlCommand("select count(*) from " + tabStr, conn2);            Object res2 = cmd2.ExecuteScalar();            if (res2 != null)            {                int nCount = Convert.ToInt32(res2.ToString());                if (nCount == 0)                {                    conn1.Close();                    conn2.Close();                    return;                }            }            //填充DataSet2            if (dataSet2 != null)            {                adapter2.Fill(dataSet2, tabStr);            }            //複製資料            for (int j = 0; j < dataSet2.Tables[0].Rows.Count; j++)            {                dataSet1.Tables[0].LoadDataRow(dataSet2.Tables[0].Rows[j].ItemArray, false);            }            //將DataSet變換顯示在與其關聯的目標資料庫            SqlCommandBuilder cb = new SqlCommandBuilder(adapter1);            adapter1.Update(dataSet1, tabStr);            cb.RefreshSchema();            Console.WriteLine("表" + tabStr + "複製成功！");            conn1.Close();            conn2.Close();        }    }}

C#複製資料庫，將資料庫資料轉到另一個資料庫