C#Access数据库应用实例.doc

我的小项目需要，所以使用了一个Access作为数据库，C#作为开发工具 来做的实例给大家看看，有不对的地方请谅解。第一：使用Access建立数据库MemberData.mdb ，包括一个数据库表t_memberdataAccess不像SQL那么方便的使用存储过程，虽然Access的也是叫存储过程，可是这个是非常简单的存储过程，建立方法可以参照网上的建立方法，本人里面仅仅使用了五个存储过程【其实网上说那些可以称为存储过程，可是跟SQL的存储过程有很大的区别】，Access里面的存储过程也是使用参数化的形式。1. proc_memberdataadd（添加会员信息）INSERT INTO t_memberdata ( membername, memberage, memberwork, membercontact )VALUES (t_membername, t_memberage, t_memberwork, t_membercontact);2. proc_memberdatadelete（删除会员信息）DELETE *FROM t_memberdataWHERE membername=t_membername;3. proc_memberdataquery（查询会员信息的全部）SELECT *FROM t_memberdata;4. proc_selectbymemberamend（修改会员信息）UPDATE t_memberdata SET memberage = t_memberage, memberwork = t_memberwork, membercontact = t_membercontactWHERE membername=t_membername;5. proc_selectbymembername（根据会员名称查询会员的信息）SELECT *FROM t_memberdataWHERE membername=t_memberdata;以上是数据库和数据库表还有需要用到的存储过程第二：c#代码部分了我的项目是：UsingAccessOperatrion项目包括了一个Form1窗体 一个OleDbHelper的数据库操作类，我们要开启如Access 数据库中的数据，必须用ADOT 透过OLEDB 来开启 【这个链接是介绍它的/view/570821.htm】， 所以你要在项目里面引用这个命名空间：using System.Data.OleDb; OleDbHelper数据库操作类想象代码可以查看项目还是用了一个实体类M_Member.csUsingAccessOperatrion 这个项目里面很详细的写出了使用Access数据进行添加/删除/修改 这些基本的功能，希望读者自己认真看看那个项目即可明白。若有不明白的地方，咱们可以共同谈论，我的qq是：823783931一下是贴上代码：项目的节目如下：第一个【确定】按钮代码：private void btSearchOk_Click(object sender, EventArgs e) /查询会员信息 try if (tbMn.Text = ) MessageBox.Show(请输入会员姓名); else string strProName = proc_selectbymembername; string conn = UsingAccessOperatrion.OleDbHelper.connString; CommandType comType = CommandType.StoredProcedure; OleDbParameter paras = new OleDbParameter(t_membername,OleDbType.VarChar); paras0.Value = tbMn.Text.ToString(); DataSet dataSets = new DataSet(); dataSets = UsingAccessOperatrion.OleDbHelper.GetDataSet(dataSets, memberdata, conn, comType, strProName, paras); MemberGridView.DataSource = dataSets.Tablesmemberdata; int resultRows = dataSets.Tablesmemberdata.Rows.Count; if (resultRows =1) MessageBox.Show(成功查询); else MessageBox.Show(暂无该会员信息); catch (Exception ex) MessageBox.Show(ex.Message); 第二个【确定】代码：private void btDeleteOk_Click(object sender, EventArgs e) /删除会员信息 try if (tbMn.Text = ) MessageBox.Show(请输入会员姓名); else MessageBoxButtons messageButton = MessageBoxButtons.OKCancel; DialogResult result = MessageBox.Show(确定要删除吗？, 删除会员信息, messageButton); if (result = DialogResult.OK) string strProName = proc_memberdatadelete; string conn = UsingAccessOperatrion.OleDbHelper.connString; CommandType comType = CommandType.StoredProcedure; OleDbParameter paras = new OleDbParameter (t_membername, SqlDbType.VarChar); paras0.Value = tbMn.Text; int number = UsingAccessOperatrion.OleDbHelper.ExecuteNonQuery(conn, comType, strProName, paras); if (number = 1) MessageBox.Show(成功删除); else MessageBox.Show(没有该会员，无法执行删除); GetMemberInformation();/窗体信息刷新 tbMn.Text = string.Empty; this.label1.Visible = false; this.tbMn.Visible = false; this.btDeleteOk.Visible = false; this.toolStripLabel1.Visible = true; this.toolStripLabel2.Visible = true; this.toolStripLabel3.Visible = true; this.toolStripLabel4.Visible = true; this.btAdd.Visible = true; this.btAmend.Visible = true; this.btSearch.Visible = true; Point pToolStript = new Point(0, 0); toolStrip1.Location = pToolStript; catch (Exception ex) MessageBox.Show(ex.Message); 第三个【会员查询信息】按钮代码如下：private void btSearch_Click(object sender, EventArgs e) this.label1.Visible = true; /Point pLable1 = new Point (9, 12); /label1.Location = pLable1; this.tbMn.Visible = true; Point P3 = new Point(101, 0); this.toolStrip1.Location = P3; this.label1.Visible = true; this.tbMn.Visible = true; this.btSearchOk.Visible = true; this.toolStripLabel1.Visible = false; this.toolStripLabel2.Visible = false; this.toolStripLabel3.Visible = false; this.toolStripLabel4.Visible = false; this.btDelete.Visible = false; this.btAdd.Visible = false; this.btAmend.Visible = false; 第四个【会员信息删除】按钮代码如下：private void btDelete_Click(object sender, EventArgs e) Point P3 = new Point(101, 0); this.toolStrip1.Location = P3; this.label1.Visible = true; this.tbMn.Visible = true; this.btDeleteOk.Visible = true; this.toolStripLabel1.Visible = false; this.toolStripLabel2.Visible = false; this.toolStripLabel3.Visible = false; this.toolStripLabel4.Visible = false; this.btAdd.Visible = false; this.btAmend.Visible = false; this.btSearch.Visible = false; 第五个【会员信息添加】按钮代码如下：private void btAdd_Click(object sender, EventArgs e) this.label1.Visible = false; this.toolStripLabel1.Visible = false; this.toolStripLabel2.Visible = false; this.toolStripLabel3.Visible = false; this.toolStripLabel4.Visible = false; this.btAdd.Visible = false; this.btAmend.Visible = false; this.btSearch.Visible = false; this.btDelete.Visible = false; Point P1 = new Point(1, 52); Point P2 = new Point(176, 55); Point P3 = new Point(73, 30); this.panel.Location = P1; this.MemberGridView.Location = P2; this.toolStrip1.Location = P3; Point btAddOkPoint = new Point(79, 160); this.panel.Visible = true; this.btAddOk.Location = btAddOkPoint; this.btAddOk.Visible = true; this.btAmendOk.Visible = false; tbMname.BackColor = Color.White; tbMage.BackColor = Color.White; tbMjob.BackColor = Color.White; tbMcontact.BackColor = Color.White; 第六个【会员信息修改】按钮代码如下： private void btAmend_Click(object sender, EventArgs e) this.label1.Visible = false; this.toolStripLabel1.Visible = false; this.toolStripLabel2.Visible = false; this.toolStripLabel3.Visible = false; this.toolStripLabel4.Visible = false; this.btAdd.Visible = false; this.btAmend.Visible = false; this.btSearch.Visible = false; this.btDelete.Visible = false; Point P1 = new Point(1, 52); Point P2 = new Point(176, 55); Point P3 = new Point(73, 30); this.panel.Location = P1; this.MemberGridView.Location = P2; this.toolStrip1.Location = P3; this.panel.Visible = true; this.btAddOk.Visible = false; this.tbMname.ReadOnly = true; tbMage.BackColor = Color.White; tbMjob.BackColor = Color.White; tbMcontact.BackColor = Color.White; 第七个【窗体刷新】按钮代码如下：private void Refresh_Click(object sender, EventArgs e) SetTextBox();/此方法使文本框为空 GetMemberInformation();/窗体信息刷新 Point P3 = new Point(0, 0); this.toolStrip1.Location = P3; Point P = new Point(0, 43); this.MemberGridView.Location = P; this.panel.Visible = false; this.label1.Visible = false; this.tbMn.Visible = false; this.btDeleteOk.Visible = false; this.btSearchOk.Visible = false; this.toolStripLabel1.Visible = true; this.toolStripLabel2.Visible = true; this.toolStripLabel3.Visible = true; this.toolStripLabel4.Visible = true; this.btAdd.Visible = true; this.btAmend.Visible = true; this.btSearch.Visible = true; this.btDelete.Visible = true; this.btAddOk.Visible = true; this.btAmendOk.Visible = true; this.tbMname.ReadOnly = false; 第八【添加信息】按钮代码如下：private void btAddOk_Click(object sender, EventArgs e) /添加会员信息 try if (tbMname.Text = | tbMage.Text = | tbMjob.Text = | tbMcontact.Text = ) MessageBox.Show(请输入完整的会员信息); else M_Member m = new M_Member(); m.MemberName = tbMname.Text; m.MemberAge = tbMage.Text; m.MemberWork = tbMjob.Text; m.MemberContact = tbMcontact.Text; string strProName = proc_memberdataadd; string conn = UsingAccessOperatrion.OleDbHelper.connString; CommandType comType = CommandType.StoredProcedure; OleDbParameter paras = new OleDbParameter(t_membername, SqlDbType.VarChar), new OleDbParameter(t_memberage, SqlDbType.VarChar), new OleDbParameter(t_memberwork, SqlDbType.VarChar), new OleDbParameter(t_membercontact, SqlDbType.VarChar); paras0.Value = m.MemberName; paras1.Value = m.MemberAge; paras2.Value = m.MemberWork; paras3.Value = m.MemberContact; int val = UsingAccessOperatrion.OleDbHelper.ExecuteNonQuery(conn, comType, strProName, paras); if (val = 1) MessageBox.Show(成功添加会员信息); GetMemberInformation();/窗体信息刷新 SetTextBox();/此方法使文本框为空 /Point P = new Point(101, 43); /this.MemberGridView.Location = P; /this.panel.Visible = false; else MessageBox.Show(已存在该会员); catch (Exception ex) MessageBox.Show(ex.Message); 第九个【修改信息】按钮代码如下：private void btAddOk_Click(object sender, EventArgs e) /添加会员信息 try if (tbMname.Text = | tbMage.Text = | tbMjob.Text = | tbMcontact.Text = ) MessageBox.Show(请输入完整的会员信息); else M_Member m = new M_Member(); m.MemberName = tbMname.Text; m.MemberAge = tbMage.Text; m.MemberWork = tbMjob.Text; m.MemberContact = tbMcontact.Text; string strProName = proc_memberdataadd; string conn = UsingAccessOperatrion.OleDbHelper.connString; CommandType comType = CommandType.StoredProcedure; OleDbParameter paras = new OleDbParameter(t_membername, SqlDbType.VarChar), new OleDbParameter(t_memberage, SqlDbType.VarChar), new OleDbParameter(t_memberwork, SqlDbType.VarChar), new OleDbParameter(t_membercontact, SqlDbType.VarChar); paras0.Value = m.MemberName; paras1.Value = m.MemberAge; paras2.Value = m.MemberWork; paras3.Value = m.MemberContact; int val = UsingAccessOperatrion.OleDbHelper.ExecuteNonQuery(conn, comType, strProName, paras); if (val = 1) MessageBox.Show(成功添加会员信息); GetMemberInformation();/窗体信息刷新 SetTextBox();/此方法使文本框为空 /Point P = new Point(101, 43); /this.MemberGridView.Location = P; /this.panel.Visible = false; else MessageBox.Show(已存在该会员); catch (Exception ex) MessageBox.Show(ex.Message); 还有一个 DataGidview的RowEnter事件【作用是：把DataGidview当前行的数据库赋到文本框里面】private void MemberGridView_RowEnter(object sender, DataGridViewCellEventArgs e) /获取GridView信息填充到文本框 try int rowIndex = e.RowIndex; tbMname.Text = MemberGridView.RowsrowIndex.Cellsmembername.Value.ToString(); tbMage.Text = MemberGridView.RowsrowIndex.Cellsmemberage.Value.ToString(); tbMjob.Text = MemberGridView.RowsrowIndex.Cellsmemberwork.Value.ToString(); tbMcontact.Text = MemberGridView.RowsrowIndex.Cellsmembercontact.Value.ToString(); catch (Exception ex) MessageBox.Show(ex.Message); 另外还有两个方法：1、 private void SetTextBox()/定义一个方法：使文本框在click事件之后变为空 tbMname.Text = string.Empty; tbMage.Text = string.Empty; tbMjob.Text = string.Empty; tbMcontact.Text = string.Empty; tbMn.Text = string.Empty; 2、 private void GetMemberInformation() try string strProName = proc_memberdataquery; string oldbConn = UsingAccessOperatrion.OleDbHelper.connString; CommandType comType = CommandType.StoredProcedure; DataSet datasets = new DataSet(); datasets = UsingAccessOperatrion.OleDbHelper.GetDataSetNop(datasets, t_memberdata, oldbConn, comType, strProName); MemberGridView.DataSource = datasets.Tablest_memberdata; catch (Exception ex) MessageBox.Show(ex.Message); 那个OleDbHelper.cs数据库操作类的代码如下：using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.OleDb;using System.Data.SqlTypes;using System.Collections;using System.Windows.Forms;namespace UsingAccessOperatrion public abstract class OleDbHelper public static readonly string connString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Application.StartupPath + DataBaseMemberData.mdb; private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable(); public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter commandParameters) OleDbCommand cmd = new OleDbCommand(); using (OleDbConnection conn = new OleDbConnection(connectionString) PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; public static int ExecuteNonQuery(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter commandParameters) OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter commandParameters) OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParame