C#复制数据库，将数据库数据转到另一个数据库

最新推荐文章于 2021-04-07 06:59:17 发布

至浊至愚

最新推荐文章于 2021-04-07 06:59:17 发布

阅读量8.6k

点赞数 7

分类专栏：数据库 .Net 文章标签：数据库 dataset c# 复制

本文链接：https://blog.csdn.net/u011421608/article/details/39957873

版权

.Net 同时被 2 个专栏收录

13 篇文章 1 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

本文章以一个表为例，要转多个表则可将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();

        }
    }
}