Access数据库和C的应用实例

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

2、NTO 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_selectb

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

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

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

6、ndTypeomType = CommandTypStoredProcedure;OleDbParameter paras =newOleDbParameter ("t_membername", OleDbType.VarChar);paras0.Value = tbMn.Text.ToString();DataSet dataSets = new DataSet ();dataSets = UsingAccessOperatrion.OleDbHelper .GetDataSet(dataSets,"memberdata" , conn, comTyp

7、e, strProName, paras);MemberGridView.DataSource = dataSets.Tables"memberdata"int resultRows = dataSets.Tables "memberdata" .Rows.Count;if (resultRows >=1) MessageBoxShow("成功查询")； else MessageBoxShow("暂无该会员信息"); catch ( Exception ex)MessageBox.Show(ex.Messag

8、e); 第二个【确定】代码：private void btDeleteOk_Click( object sender, EventArgs e) /删除会员信息 tryif (tbMn.Text ="")MessageBoxShow("请输入会员姓名"); elseMessageBoxButtons messageButton = MessageBoxButtons .OKCancel;DialogResult result =MessageBoxShow("确定要删除吗？"，"删除会员信息messageButton);if

9、 (result = DialogResult .OK)string strProName = "proc_memberdatadelete" ; string conn = UsingAccessOperatrion. OleDbHelper .connString; CommandTypeomType = CommandTypStoredProcedure; OleDbParameter paras = new OleDbParameter ( "t_membername", SqlDbType .VarChar); paras0.Value = t

10、bMn.Text;int number = UsingAccessOperatrion. OleDbHelper .ExecuteNonQuery(conn, comType, strProName, paras);if (number >= 1) MessageBoxShow("成功删除"); else MessageBoxShow("没有该会员，无法执行删除"); GetMemberInformation();/ 窗体信息刷新tbMn.Text =string .Empty; this .label1.Visible = false ; thi

11、s.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 pToolSt

12、ript = 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 = t

13、rue ;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 .

14、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.Visib

15、le = 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,

16、 EventArgs e)(this .label1.Visible = false ;this .toolStripLabel1.Visible = false ;this.toolStripLabel2.Visible =falsethis.toolStripLabel3.Visible =falsethis.toolStripLabel4.Visible =falsethis.btAdd.Visible =false ;this.btAmend.Visible = false ;this .btSearch.Visible =false ;this .btDelete.Visible =

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

18、.btAddOk.Visible = true ;this .btAmendOk.Visible = false ;tbMname.BackC010r =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.tool

19、StripLabel1.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 Poin

20、t (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.Bac

21、kColor =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

22、 .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 .btAmen

23、d.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.Text =&quo

24、t;"| tbMjob.Text =""tbMcontact.Text ="")(MessageBoxShow("请输入完整的会员信息 ");)else(M_Membem = new M_Member;m.MemberName = tbMname.Text;m.MemberAge = tbMage.Text;m.MemberWork = tbMjob.Text;m.MemberContact = tbMcontact.Text;string strProName = "proc_memberdataadd"

25、;string conn = UsingAccessOperatrion. OleDbHelper .connString; CommandTypeomType = CommandTypStoredProcedure;OleDbParameter 口 paras =new OleDbParameter ("t_membername", SqlDbType .VarChar), new OleDbParameter ("t_memberage" , SqlDbType .VarChar), new OleDbParameter ("t_membe

26、rwork" , 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, strPr

27、oName, paras);if (val >= 1)MessageBoxShow("成功添加会员信息");GetMemberInformation();/ 窗体信息刷新SetTextBox();/此方法使文本框为空/Point P = new Point(101,43);/this.MemberGridView.Location = P;/this.panel.Visible = false; elseMessageBoxShow("已存在该会员");catch ( Exception ex)MessageBoxShow(ex.Message);

28、)第九个【修改信息】按钮代码如下：private void btAddOk_Click( object sender, EventArgs e)(/添加会员信息try(if (tbMname.Text ="" | tbMage.Text ="" | tbMjob.Text ="" |tbMcontact.Text ="" )(MessageBoxShow("请输入完整的会员信息 ");)else(M_Memberm = new M_Membe()r;m.MemberName = tbMname.

29、Text;m.MemberAge = tbMage.Text;m.MemberWork = tbMjob.Text;m.MemberContact = tbMcontact.Text;string strProName = "proc_memberdataadd" ;string conn = UsingAccessOperatrion. OleDbHelper .connString; CommandTypecomType = CommandTyp.eStoredProcedure;OleDbParameter paras =new OleDbParameter (&qu

30、ot;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.Membe

31、rAge;paras2.Value = m.MemberWork;paras3.Value = m.MemberContact;int val = UsingAccessOperatrion. OleDbHelper .ExecuteNonQuery(conn, comType, strProName, paras);if (val >= 1)MessageBoxShow("成功添加会员信息");GetMemberInformation();/ 窗体信息刷新SetTextBox();/此方法使文本框为空/Point P = new Point(101,43);/thi

32、s.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信

33、息填充到文本框 tryint rowIndex = e.RowIndex;tbMname.Text =MemberGridView.RowsrowIndex.Cells tbMage.Text =MemberGridView.RowsrowIndex.Cells tbMjob.Text =MemberGridView.RowsrowIndex.Cells tbMcontact.Text =MemberGridView.RowsrowIndex.Cells catch ( Exception ex)membername".Value.ToString();memberage"

34、.Value.ToString();memberwork" .Value.ToString();membercontact" .Value.ToString();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

35、.Text =string .Empty;2、 private void GetMemberInformation() trystring strProName = "proc_memberdataquery" ;string oldbConn = UsingAccessOperatrion. OleDbHelper .connString;CommandTypecomType = CommandTyp.eStoredProcedure;DataSet datasets = new DataSet ();datasets = UsingAccessOperatrion.Ol

36、eDbHelper .GetDataSetNop(datasets,"t_memberdata" , oldbConn, comType, strProName);MemberGridView.DataSource = datasets.Tables"t_memberdata" ; catch ( Exception ex)MessageBox.Show(ex.Message);那个OleDbHelper.cs数据库操作类的代码如下:using System;using System.Collections.Generic;using System.Te

37、xt;using System.Data;using System.Data.OleDb;using System.Data.SqlTypes;using System.Collections;using System.Windows.Forms;namespace UsingAccessOperatrionpublic abstract class OleDbHelperpublic static readonly string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Applicati

38、on .StartupPath +"DataBaseWMemberData.mdb"private static Hashtable parmCache = Hashtable .Synchronized( new Hashtable ();public static int ExecuteNonQuery( string connectionString, CommandTypecmdType, string cmdText, params OleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman

39、(d);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, CommandTypecmdType, string

40、 cmdText, params OleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);PrepareCommand(cmd, connection,null , cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear(); return val;public static int ExecuteNonQuery( OleDbTransaction trans, CommandTypec

41、mdType, string cmdText, params OleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear(); return val;public static OleDbDataReader ExecuteReader( strin

42、g connectionString, CommandTypecmdType, string cmdText, params OleDbParameter commandParameters)OleDbCommanCmd = new OleDbComman。；OleDbConnection conn = new OleDbConnection (connectionstring);/ we use a try/catch here because if the method throws an exception we want to- 我们使用try / catch语句在这里，因为如果方法抛

43、出一个例外，我们要/ close the connection throw code, because no datareader will exist, hence the - 关闭连接扔代码，因为没有DataReader的将存在，因此/ commandBehaviour.CloseConnection will not work-commandBehaviour.CloseConnection 将无法工作tryPrepareCommand(cmd, conn,null , cmdType, cmdText, commandParameters);OleDbDataReader rdr =

44、cmd.ExecuteReader( CommandBehavior.CloseConnection);cmd.Parameters.Clear(); return rdr; catch conn.Close();throw ; public static Dataset GetDataset( Dataset dataset, string connectionstring, CommandType cmdType, string cmdText, params OleDbParameter commandParameters)OleDbCommandcmd = new OleDbComma

45、n(d);OleDbConnection conn = new OleDbConnection (connectionstring);try PrepareCommand(cmd, conn,null , cmdType, cmdText, commandParameters);OleDbDataAdapter adapter = new OleDbDataAdapter ();adapter.selectCommand = cmd;adapter.Fill(dataset);cmd.Parameters.Clear();return dataset; catchconn.Close();th

46、row ;public static DataSet GetDataSet( DataSet dataset, string table, string connectionstring,CommandTypemdType, string cmdText, params OleDbParameter commandparameters) (OleDbCommandmd = new OleDbComman();OleDbConnection conn = new OleDbConnection (connectionString);try (prepareCommand(cmd, conn,nu

47、ll , cmdType, cmdText, commandparameters);OleDbDataAdapter adapter = new OleDbDataAdapter ();adapter.SelectCommand = cmd;adapter.Fill(dataset, table);cmd.parameters.Clear();return dataset; catch (conn.Close();throw ; public static DataSet GetDataSetNop( DataSet dataset, string table, string connecti

48、onString, CommandTypecmdType, string cmdText)(OleDbCommandcmd = new OleDbComman(d);OleDbConnection conn = new OleDbConnection (connectionString);try (prepareCommandNop(cmd, conn,null , cmdType, cmdText);OleDbDataAdapter adapter = new OleDbDataAdapter ();adapter.SelectCommand = cmd;adapter.Fill(datas

49、et, table);cmd.parameters.Clear();return dataset; catch(conn.Close();throw ;public static object ExecuteScalar( string connectionstring, CommandTypemdType, string cmdText, params OleDbParameter commandParameters)OleDbCommandcmd = new OleDbComman(d);using ( OleDbConnection connection = new OleDbConne

50、ction (connectionString) PrepareCommand(cmd, connection,null , cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear(); return val; public static object ExecuteScalar( OleDbConnection connection, CommandTypecmdType, string cmdText, params OleDbParameter commandPa

51、rameters)OleDbCommandcmd = new OleDbComman(d);PrepareCommand(cmd, connection,null , cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear(); return val; public static void CacheParameters( string cacheKey, params OleDbParameter commandParameters)parmCachecacheKey = commandParameters;public static OleDbParameter GetCachedParameters( s