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

来源:互联网 发布:java项目有意思的案例 编辑:程序博客网 时间:2023/02/03 01:52

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


4 0
原创粉丝点击