将远程服务器数据库数据写入本地数据库(c#+sql server)

1、界面
在这里插入图片描述
在这里插入图片描述
注:实现方式关键在于sql语句,即:select * into " + textBox3.Text.Trim() + " from openrowset(‘SQLOLEDB’,‘10.113.8.171’;‘r’;‘123’,ERP.dbo." + textBox3.Text.Trim() + ")。该条语句一次写入一个数据表,openrowset这个东西要设置下数据库,上网搜那两句sql。
要用的话可以自己做一个界面,再改一下数据库的信息就行。

2、代码

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.SqlClient;   //引用Sql Server数据提供服务

namespace ConsoleApplication1
{
    public partial class Operation : Form
    {
        public Operation()
        {
            InitializeComponent();
        }

        //static string ConStr = "server=BJ;database=CRack;uid=sa;password=123456";//创建一个字符串用于存储连接数据库字符串
        //SqlConnection connLocal = new SqlConnection(ConStr);  //用以关闭和打开本地数据库   
        //static string conStr = "server=10.113.8.171;database=ERP;uid=r;password=123";
        //SqlConnection connOther = new SqlConnection(conStr);  //远程
        SqlConnection connLocal;
        SqlConnection connOther;

        //连接本地数据库
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                //创建一个字符串用于存储连接数据库字符串
                string ConStr = "server=BJ;database=Rack;uid=sa;password=123456";
                connLocal = new SqlConnection(ConStr);
                connLocal.Open();
                if (connLocal.State == ConnectionState.Open)
                {
                    //将数据库连接信息打印在消息框
                    showInfomation.Items.Add("本地Local数据库打开成功");
                    connLocalDb.Enabled = false;
                    button1.Enabled = true;//关闭
                    selectLocalInfo.Enabled = true;//查询
                }
            }
            catch
            {
                showInfomation.Items.Add("本地Local数据库打开失败!!!!");
            }
            //启用数据写入按钮
            if (connLocal.State == ConnectionState.Open)
            {
                button3.Enabled = true;
            }
        }

        //用于断开本地数据库连接
        private void button1_Click_1(object sender, EventArgs e)
        {
            try
            {
                connLocal.Close();
                if (connLocal.State == ConnectionState.Closed)
                {
                    showInfomation.Items.Add("关闭本地数据库成功");
                }
                connLocalDb.Enabled = true;
                button1.Enabled = false;//关闭按钮
                selectLocalInfo.Enabled = false;//查询按钮
                button3.Enabled = false;//数据写入按钮
            }
            catch
            {
                showInfomation.Items.Add("本地关闭数据库失败!!!!!");
            }
        }

        //用于连接远程数据库
        private void connOherDb_Click(object sender, EventArgs e)
        {
            try
            {
                string conStr = "server=10.113.8.171;database=ERP;uid=r;password=123";
                connOther = new SqlConnection(conStr);
                connOther.Open();
                if (connOther.State==ConnectionState.Open)
                {
                    showInfomation.Items.Add("远程数据库打开成功");
                }
                connOherDb.Enabled = false;
                button2.Enabled = true;//关闭
                selectOtherInfo.Enabled = true;//查询
            }
            catch
            {
                showInfomation.Items.Add("远程数据库打开失败!!!!");
                //MessageBox.Show("失败");
            }
            //如果两数据库都连接了,启用数据写入按钮
            //if (connLocal.State == ConnectionState.Open)
            //{
                //button3.Enabled = true;
            //}
        }
        //断开远程数据库连接
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                connOther.Close();
                if (connOther.State == ConnectionState.Closed)
                {
                    showInfomation.Items.Add("关闭远程数据库成功");
                }
                connOherDb.Enabled = true;
                button2.Enabled = false;//关闭按钮
                selectOtherInfo.Enabled = false;//查询按钮
            }
            catch
            {
                showInfomation.Items.Add("关闭远程数据库失败!!!!!");
            }
        }

        //消息显示列表
        private void showInfomation_SelectedIndexChanged(object sender, EventArgs e)
        {
        }

        //查询本地数据库的数据
        private void selectLocalInfo_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox1.Text != "" && connLocal.State == ConnectionState.Open)
                {
                    SqlCommand cmd = new SqlCommand();  //创建一个SqlCommad对象
                    cmd.Connection = connLocal;   //指定其使用connLocal连接本地数据库
                    cmd.CommandText = "select * from " + textBox1.Text.Trim(); //文本框输入表名称
                    cmd.CommandType = CommandType.Text;  //对数据执行的方式为sql语句
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = cmd;//设置属性为cmd
                    DataSet ds = new DataSet();//用来存储读取出的数据
                    sda.Fill(ds, "cs");//Fill方法填充DataSet数据集
                    dataGridView1.DataSource = ds.Tables[0];
                }
            }
            catch
            {
                MessageBox.Show("表名不存在");
            }
        }

        //查询远程数据库数据
        private void selectOherInfo_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox2.Text != "" && connOther.State == ConnectionState.Open)
                {
                    SqlCommand cmd = new SqlCommand();  //创建一个SqlCommad对象
                    cmd.Connection = connOther;   //指定其使用connLocal连接本地数据库
                    cmd.CommandText = "select * from " + textBox2.Text.Trim(); //文本框输入表名称
                    cmd.CommandType = CommandType.Text;  //对数据执行的方式为sql语句

                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = cmd;//设置属性为cmd
                    DataSet ds = new DataSet();//用来存储读取出的数据
                    sda.Fill(ds, "cs");//Fill方法填充DataSet数据集
                    dataGridView1.DataSource = ds.Tables[0];
                }
            }
            catch
            {
                MessageBox.Show("表名不存在!");
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        //将DataSet中的数据写入到数据库中
        private void button3_Click(object sender, EventArgs e)
        {
            if(connLocal.State==ConnectionState.Open && connOther.State == ConnectionState.Open)
            {
                try
                {
                    //进行判断,目标数据库中表是否已经存在
                   /* SqlCommand cmdtest = new SqlCommand("select count(*) from sys.tables where name = '" + textBox3.Text.Trim() + "'", connLocal);
                    SqlDataReader sdr = cmdtest.ExecuteReader();
                    if (sdr.HasRows)
                    {
                        MessageBox.Show("目标数据库已存在同名表");
                    }
                    else
                    {*/
                        //SQL语句将源数据库中的表的结构与数据都复制到本地数据库中
                        string str = "select * into " + textBox3.Text.Trim() + " from openrowset('SQLOLEDB','10.113.8.171';'rgzn';'chaint@123',ZNERP.dbo." + textBox3.Text.Trim() + ")";
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = connLocal;
                        cmd.CommandText = str;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    //}
                }
                catch
                {
                    MessageBox.Show("目标数据库已存在同名表");
                }
            }
        }

        private void label3_Click(object sender, EventArgs e)
        {

        }

        //在显示区域显示信息
        /*private void SendMessage(string strMsg)
        {
            if (strMsg.Length == 0) return;
            showInfomation.Items.Add(string.Format("【{0}】—{1}", DateTime.Now.ToString("HH:mm:ss"), strMsg));
            if (showInfomation.Items.Count > 0) showInfomation.SelectedIndex = showInfomation.Items.Count - 1;
        }*/
    }
}

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页