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;
}*/
}
}
2049