Access数据库和C#的应用实例

1、我的小项目需要，所以使用了一个Access作为数据库，C#作为开发工具 来做的实例给大家 看看，有不对的地方请谅解。第一:使用Access建立数据库MemberData.mdb，包括一个数据库表t_memberdataAccess不像SQL那么方便的使用存储过程，虽然Access的也是叫存储过程， 可是这个是非常简单的存储过程， 建立方法可以参照网上的建立方法，本人里面仅仅使用了五个存储过程【其实网上说那些可以称为存储过程，可是跟SQL的存储过程有很大的区别】，Access里面的存储过程也是使用参数化的形式。1. proc_memberdataadd（添加会员信息）INSERT INTO t_

2、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_selectbymemberam

3、end（修改会员信息）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#代码部分了我的项目是:UsingAccessOperatri

4、on项目包括了一个Form 1窗体 一个OleDbHelper的数据库操作类，我们要开启 如Access数 据库中的数据，必须用ADOT透过OLEDB来开启 【这个链接是介绍它的http:/ 所以你要在项目里面引用这个命名空间：usingSystem.Data.OleDb;OleDbHelper数据库操作类想象代码可以查看项目还是用了一个实体类M_Member.csUsingAccessOperatrion这个项目里面很详细的写出了使用Access数据进行添加/删除/修改这些基本的功能，希望读者自己认真看看那个项目即可明白。若有不明白的地方，咱们可以共同谈论，我的qq是：823783931一下

5、是贴上代码:项目的节目如下:第一个【确定】按钮代码：private void btSearchOk_Click( object sender, EventArgs e) /查询会员信息tryif (tbMn.Text =)MessageBoxShow(请输入会员姓名”);elsestring strProName = proc_selectbymembername”; string conn = UsingAccessOperatrion.OleDbHelper .connString;CommandTypeomType = CommandTypStoredProcedure;OleDbPar

6、ameter paras =newOleDbParameter (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.Tablesmemberd

7、ata;int resultRows = dataSets.Tables memberdata .Rows.Count;if (resultRows =1)MessageBoxShow(成功查询)；elseMessageBoxShow(暂无该会员信息);catch ( Exception ex)MessageBox.Show(ex.Message);第二个【确定】代码：private void btDeleteOk_Click( object sender, EventArgs e)/删除会员信息tryif (tbMn.Text =)MessageBoxShow(请输入会员姓名);elseMe

8、ssageBoxButtons messageButton = MessageBoxButtons .OKCancel;DialogResult result = MessageBoxShow(确定要删除吗？，删除会员信息messageButton);if (result = DialogResult .OK)string strProName = proc_memberdatadelete ;string conn = UsingAccessOperatrion. OleDbHelper .connString;CommandTypeiomType = CommandTypStoredPro

9、cedure;OleDbParameter paras = (new OleDbParameter ( t_membername,SqlDbType .VarChar);paras0.Value = tbMn.Text;int number = UsingAccessOperatrion. OleDbHelper .ExecuteNonQuery(conn,comType, strProName, paras);if (number = 1)(MessageBoxShow(成功删除)；else(MessageBoxShow(没有该会员，无法执行删除”);GetMemberInformation

10、();/窗体信息刷新tbMn.Text =string .Empty;this .label1.Visible =false ;this .tbMn.Visible =false ;this .btDeleteOk.Visible =false ;this .toolStripLabel1.Visible =truethis .toolStripLabel2.Visible =truethis .toolStripLabel3.Visible =truethis .toolStripLabel4.Visible =truethis .btAdd.Visible =true ;this .btA

11、mend.Visible = true ;this .btSearch.Visible =true ;Point pToolStript =new Point (0, 0);toolStrip1.Location = pToolStript;catch ( Exception ex)(MessageBox.Show(ex.Message);第三个【会员查询信息】按钮代码如下:privatevoid btSearch_Click( object sender,EventArgs e)(this.label1.Visible =true ;/Point pLable1 = new Point (9

12、, 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.Visi

13、ble =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

14、;this .tbMn.Visible = true ;this.btDeleteOk.Visible =true;this.toolStripLabel1.Visible =falsethis.toolStripLabel2.Visible =falsethis.toolStripLabel3.Visible =falsethis.toolStripLabel4.Visible =falsethis.btAdd.Visible =false ;this.btAmend.Visible = false ;this.btSearch.Visible =false ;第五个【会员信息添加】按钮代码

15、如下：private void btAdd_Click( object sender, EventArgs e) (this .label1.Visible = false ;this .toolStripLabel1.Visible = false ;this .toolStripLabel2.Visible =falsethis .toolStripLabel3.Visible =falsethis .toolStripLabel4.Visible =this .btAdd.Visible =false ;this .btAmend.Visible = false ;this .btSea

16、rch.Visible =false ;this .btDelete.Visible =false ;falsePoint 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 =

17、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, Eve

18、ntArgs 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 = falsethis .btSearch.Visible =falsethis .btDelete.Visible =falsePoin

19、t 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 =falsethis .tbMname.ReadOnly = true ;tbMage.BackColor =Color .White;tbMjob

20、.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);thisthis.MemberGridView.Locat

21、ion = P;.panel.Visible =false ;this.label1.Visible =false ;this.tbMn.Visible =false ;this.btDeleteOk.Visible =false ;this.btSearchOk.Visible =false ;this.toolStripLabel1.Visible =truethis.toolStripLabel2.Visible =truethis.toolStripLabel3.Visible =truethis.toolStripLabel4.Visible =truethis.btAdd.Visi

22、ble =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) /添加会员信息tryif (tbMname.Text = | tbMage.Tex

23、t = | tbMjob.Text = |tbMcontact.Text =)( MessageBoxShow(请输入完整的会员信息);) else(M_Membem = new M_Membe；m.MemberName = tbMname.Text;m.MemberAge = tbMage.Text;m.MemberWork = tbMjob.Text;m.MemberContact = tbMcontact.Text;string strProName = proc_memberdataadd;string conn = UsingAccessOperatrion. OleDbHelper

24、 .connString; CommandTypeomType =CommandTypStoredProcedure;OleDbParameter paras =(new OleDbParameter (t_membername, SqlDbType .VarChar), newOleDbParameter (t_memberage , SqlDbType .VarChar), newOleDbParameter (t_memberwork , SqlDbType .VarChar), newOleDbParameter (t_membercontact,SqlDbType .VarChar)

25、;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)(MessageBoxShow(成功添加会员信息);GetMemberInformation();/窗体信息刷新SetTextBox();/此方法使文本框为

26、空/Point P = new Point(101,43);/this.MemberGridView.Location = P; /this.panel.Visible = false;)else(MessageBoxShow(已存在该会员);)catch ( Exception ex)MessageBoxShow(ex.Message);)第九个【修改信息】按钮代码如下：private void btAddOk_Click( object sender, EventArgs e)(/添加会员信息try(if (tbMname.Text = | tbMage.Text = | tbMjob.T

27、ext = |tbMcontact.Text = )(MessageBoxShow(请输入完整的会员信息”);) else(M_Memberm = new M_Membe()r;m.MemberName = tbMname.Text;m.MemberAge = tbMage.Text;m.MemberWork = tbMjob.Text;m.MemberContact = tbMcontact.Text;string strProName = proc_memberdataadd ;string conn = UsingAccessOperatrion. OleDbHelper .connSt

28、ring; CommandTypecomType =CommandTyp.eStoredProcedure;OleDbParameter paras =(new OleDbParameter (t_membername”, SqlDbType .VarChar), newOleDbParameter (t_memberage , SqlDbType .VarChar), newOleDbParameter (t_memberwork , SqlDbType .VarChar), newOleDbParameter ( t_membercontact ,SqlDbType .VarChar);p

29、aras0.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)(MessageBoxShow(成功添加会员信息”);GetMemberInformation();/窗体信息刷新/Point P = new Point(10

30、1,43);/this.MemberGridView.Location = P;/this.panel.Visible = false;elseMessageBoxShow(已存在该会员”);catch ( Exception ex)MessageBox.Show(ex.Message);还有一个DataGidview的RowEnter事件【作用是：把DataGidview当前行的数据库赋到文本框里面】private void MemberGridView_RowEnter( object sender, DataGridViewCellEventArgs e)/获取GridView信息填充到

31、文本框tryint rowIndex = e.RowIndex;tbMname.Text =MemberGridView.RowsrowIndex.CellstbMage.Text =MemberGridView.RowsrowIndex.Cells tbMjob.Text =MemberGridView.RowsrowIndex.CellstbMcontact.Text =catch ( Exception ex)MessageBoxShow(ex.Message);另外还有两个方法：1、private void SetTextBox() /定义一个方法：使文本框在click事件之后变为空t

32、bMname.Text =string .Empty;SetTextBox();/此方法使文本框为空membername.Value.ToString();memberage.Value.ToString();memberwork .Value.ToString();MemberGridView.RowsrowIndex.Cellsmembercontact .Value.ToString();tbMage.Text =string .Empty;tbMjob.Text =string .Empty;tbMcontact.Text =string .Empty;tbMn.Text =strin

33、g .Empty;2、private void GetMemberInformation()(try(string strProName = proc_memberdataquery;string oldbConn = UsingAccessOperatrion. OleDbHelper .connString;CommandTypomType = CommandTyp.StoredProcedure;DataSet datasets = new DataSet ();datasets = UsingAccessOperatrion.OleDbHelper .GetDataSetNop(dat

34、asets,t_memberdata , oldbConn, comType, strProName);MemberGridView.DataSource = datasets.Tablest_memberdata;catch ( Exception ex)(MessageBoxShow(ex.Message);那个OleDbHelper.cs数据库操作类的代码如下:using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.OleDb;using Sys

35、tem.Data.SqlTypes;using System.Collections;using System.Windows.Forms;namespace UsingAccessOperatrion(public abstract class OleDbHelperpublic static readonly string connString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= +Application .StartupPath +DataBaseWMemberData.mdb;private static Hashtable

36、parmCache = Hashtable .Synchronized( new Hashtable ();public static int ExecuteNonQuery( string connectionString, CommandTypecmdType, string cmdText, paramsOleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);using ( OleDbConnection conn = new OleDbConnection (connectionString)Prepa

37、reCommand(cmd, conn,null , cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;public static int ExecuteNonQuery( OleDbConnection connection, CommandTypecmdType, string cmdText, paramsOleDbParameter commandParameters)OleDbCommandcmd = new OleDbComma

38、n(d);PrepareCommand(cmd, connection,null , cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;public static int ExecuteNonQuery( OleDbTransaction trans, CommandTypecmdType, string cmdText, paramsOleDbParameter commandParameters)OleDbCommandcmd = ne

39、w OleDbComman(d);PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;public static OleDbDataReader ExecuteReader( string connectionString, CommandTypecmdType, string cmdText,params OleDbParameter commandP

40、arameters)OleDbCommanCmd = new OleDbCommand；OleDbConnection conn = new OleDbConnection (connectionstring);/ we use a try/catch here because if the method throws an exception we want to-我们使用try / catch语句在这里，因为如果方法抛出一个例外，我们要/ close the connection throw code, because no datareader will exist, hence the

41、 -关闭连接扔代码，因为没有DataReader的将存在，因此/ commandBehaviour.CloseConnection will not work-commandBehaviour.CloseConnection将无法工作tryPrepareCommand(cmd, conn,null , cmdType, cmdText, commandParameters);OleDbDataReader rdr = cmd.ExecuteReader( CommandBehavior.CloseConnection);cmd.Parameters.Clear();return rdr;cat

42、chconn.Close();throw ;public static Dataset GetDataset( Dataset dataset, string connectionstring, CommandType cmdType, stringcmdText, params OleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);OleDbConnection conn = new OleDbConnection (connectionstring);tryPrepareCommand(cmd, conn

43、,null , cmdType, cmdText, commandParameters);OleDbDataAdapter adapter = new OleDbDataAdapter ();adapter.selectCommand = cmd;adapter.Fill(dataset);cmd.Parameters.Clear();return dataset;catchconn.Close();throw ;public static DataSet GetDataSet( DataSet dataset, string table, string connectionstring,Co

44、mmandTypemdType, string cmdText, params OleDbParameter commandparameters)(OleDbCommandmd = new OleDbComman();OleDbConnection conn = new OleDbConnection (connectionString);try(prepareCommand(cmd, conn,null , cmdType, cmdText, commandparameters);OleDbDataAdapter adapter = new OleDbDataAdapter ();adapt

45、er.SelectCommand = cmd;adapter.Fill(dataset, table);cmd.parameters.Clear();return dataset;catch(conn.Close();throw ;public static DataSet GetDataSetNop( DataSet dataset, string table, string connectionString,CommandTypecmdType, string cmdText)(OleDbCommandcmd = new OleDbComman(d);OleDbConnection con

46、n = new OleDbConnection (connectionString);try(prepareCommandNop(cmd, conn,null , cmdType, cmdText);OleDbDataAdapter adapter = new OleDbDataAdapter ();adapter.SelectCommand = cmd;adapter.Fill(dataset, table);cmd.parameters.Clear();return dataset;catch(conn.Close();throw ;public static object Execute

47、Scalar( string connectionstring, CommandTypemdType, string cmdText, paramsOleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);using ( OleDbConnection connection = new OleDbConnection (connectionString)PrepareCommand(cmd, connection,null , cmdType, cmdText, commandParameters);object

48、 val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;public static object ExecuteScalar( OleDbConnection connection, CommandTypecmdType, string cmdText, paramsOleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);PrepareCommand(cmd, connection,null , cmdType, cmdText, command

49、Parameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;public static void CacheParameters( string cacheKey, params OleDbParameter commandParameters)parmCachecacheKey = commandParameters;public static OleDbParameter GetCachedParameters( string cacheKey)OleDbParameter cachedParms = (