本次实例为C#在windows程序
主要是简单的实现
已知表名及字段的单表数据互导:
SqlServer->Access
从SqlServer中查询数据到DataGridView显示
然后选择Access数据库的相关位置
将DataGridView的数据导入Access相关表
Access->SqlServer
选择Access数据库的相关位置
从Access中查询数据到DataGridView显示
将DataGridView的数据导入SqlServer相关表
可以在操作界面
提供相关的用户名及密码的输入栏位
以提交程序的灵活性
同样可以扩充更多更强功能
主要操作示例代码如下:
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
从SQLServer中读取数据#region 从SQLServer中读取数据
private void button1_Click(object sender, EventArgs e)
{
string strConn = "server=192.168.0.36;database=StudyDB;user id=sa;password=sa";
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
string strComm = "select pID,pName from p1";
sqlConn.Open();
SqlDataAdapter da = new SqlDataAdapter(strComm, sqlConn);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
sqlConn.Close();
this.dataGridView1.DataSource = ds.Tables[0];
}
}
#endregion
将数据导入Access#region 将数据导入Access
private void button2_Click(object sender, EventArgs e)
{
if (this.textBox1.Text.Trim() == "")
{
MessageBox.Show("请先选择Access数据库的位置");
return;
}
DataTable dt = (DataTable)this.dataGridView1.DataSource;
if (dt.Rows.Count > 0)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
strConn += @"" + this.textBox1.Text + "";
//没有设置用户名及密码时 下面的字串省略
strConn += ";Jet OLEDB:System Database=";
strConn += @"" + this.textBox2.Text + ";";
strConn += "User ID=dba;Password=abcd;";
using (OleDbConnection OleConn = new OleDbConnection(strConn))
{
OleConn.Open();
OleDbCommand sqlComm = OleConn.CreateCommand();
OleDbTransaction trans = OleConn.BeginTransaction();
sqlComm.Connection = OleConn;
sqlComm.Transaction = trans;
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string strComm = "INSERT INTO p1(pID, pName ,pFlag) ";
strComm += " VALUES( ";
strComm += " " + dt.Rows[i]["pID"].ToString() + " ";
strComm += " ,'" + dt.Rows[i]["pName"].ToString() + "' ";
strComm += " ,'1' ) ";
sqlComm.CommandText = strComm;
sqlComm.ExecuteNonQuery();
}
trans.Commit();
MessageBox.Show("导入成功");
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show("导入失败" + ex.ToString());
}
}
}
}
#endregion
从Access中读取数据#region 从Access中读取数据
private void button3_Click(object sender, EventArgs e)
{
if (this.textBox1.Text.Trim() == "")
{
MessageBox.Show("请先选择Access数据库的位置");
return;
}
string strConn ="Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
strConn += @""+this.textBox1.Text+"";
//没有设置用户名及密码时 下面的字串省略
strConn += ";Jet OLEDB:System Database=";
strConn += @"" + this.textBox2.Text + ";";
strConn += "User ID=dba;Password=abcd;";
using (OleDbConnection OleConn = new OleDbConnection(strConn))
{
string strComm = "select pID,pName from p1";
OleConn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(strComm, OleConn);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
OleConn.Close();
this.dataGridView1.DataSource = ds.Tables[0];
}
}
#endregion
将数据导入SQLServer#region 将数据导入SQLServer
private void button4_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)this.dataGridView1.DataSource;
if (dt.Rows.Count > 0)
{
string strConn = "server=192.168.0.36;database=StudyDB;user id=sa;password=sa";
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
sqlConn.Open();
SqlCommand sqlComm = sqlConn.CreateCommand();
SqlTransaction trans = sqlConn.BeginTransaction();
sqlComm.Connection = sqlConn;
sqlComm.Transaction = trans;
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string strComm = "INSERT INTO p1(pID, pName) ";
strComm += " VALUES( ";
strComm += " " + dt.Rows[i]["pID"].ToString() + " ";
strComm += " ,'" + dt.Rows[i]["pName"].ToString() + "' ";
strComm += " ) ";
sqlComm.CommandText = strComm;
sqlComm.ExecuteNonQuery();
}
trans.Commit();
MessageBox.Show("导入成功");
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show("导入失败"+ex.ToString());
}
}
}
}
#endregion
打开选择Access的mdb对话框#region 打开选择Access的mdb对话框
private void button5_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = "E:\\";
openFileDialog1.Filter = "mdb files (*.mdb)|*.mdb|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = openFileDialog1.FileName;
}
}
#endregion
打开选择Access的mdw对话框#region 打开选择Access的mdw对话框
private void button7_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog2 = new OpenFileDialog();
openFileDialog2.InitialDirectory = "E:\\";
openFileDialog2.Filter = "mdw files (*.mdw)|*.mdw|All files (*.*)|*.*";
openFileDialog2.FilterIndex = 2;
openFileDialog2.RestoreDirectory = true;
if (openFileDialog2.ShowDialog() == DialogResult.OK)
{
this.textBox2.Text = openFileDialog2.FileName;
}
}
#endregion
清空dataGridView1数据#region 清空dataGridView1数据
private void button6_Click(object sender, EventArgs e)
{
this.dataGridView1.DataSource = null;
}
#endregion
}
附其它相关操作：
1.Access创建数据库db1.mdb(略)
2.创建db1.mdb对应的db1system.mdw
1)打开Access-文件-打开-选择dbq.mdb
打开按钮的后面的小下拉箭头 选择 以独占方式打开
打开db1.mdb
2)工具-安全-工作组管理员-创建
输入相关信息
并在相关位置保存mdw为db1system.mdw
3.创建db1.mdb的相关操作用户及密码
1)打开Access-文件-打开-选择dbq.mdb
打开按钮的后面的小下拉箭头 选择 以独占方式打开
打开db1.mdb
2)工具-安全-用户与组帐户
新建用户如dba 并选择隶属于哪个组 默认用户组
更改登录密码 如设置为abcd
确定并应用
4.修改用户与组权限
1)打开Access-文件-打开-选择dbq.mdb
打开按钮的后面的小下拉箭头 选择 以独占方式打开
打开db1.mdb
2)提示输入用户名密码 输入dba,abcd
3)工具-安全-用户与组权限
进行相关读取及更新删除设置等