本教程介绍了如何通过的OleDb相关类来操作Access数据库的相关步骤。
1软件环境
开发平台:Microsoft Visual Studio
数据库:Microsoft Office Access
2建立数据库步骤
(1) 打开Microsoft Office Access ,选择空白数据库,然后输入数据库名称和存放路径,这里数据库名称为login.accdb,路径在D盘下,单机创建。
(2)选中表1,右键选择设计视图,然后输入表名,这里为tb_user,单击确定。
(3)填写字段名称和数据类型。
(4)选中tb_user表,单击右键—>打开,在里面添加数据即可。
3C#代码操作步骤
(1)命名空间
usingSystem.Data.OleDb;
(2)连接字符串
privatestaticstringconnStr =@"Provider= Microsoft.Ace.OLEDB.12.0;Data Source = d:\login.accdb";
注意:
a、DataSource = 数据库存放的路径(这里数据库放到了D盘目录下)
b、版本的Access数据库链接字符串:privatestaticstringconnStr =@"Provider = Microsoft.Jet.OLEDB.4.0;DataSource = d:\login.mdb";
版本的Access数据库链接字符串:privatestaticstringconnStr =@"Provider= Microsoft.Ace.OLEDB.12.0;Data Source = d:\login.accdb";
(3)建立连接:
OleDbConnectiontempconn =newOleDbConnection(connStr);
(4)使用OleDbCommand类来执行Sql语句:
OleDbCommandcmd=newOleDbCommand(sql,tempconn);
tempconn.Open();
cmd.ExecuteNonQuery();
(5)代码
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.OleDb;using System.Windows.Forms;using System.Data;namespace test{class AccessHelper{private static string connStr = @"Provider = Microsoft.Ace.OLEDB.12.0;Data Source = d:\login.accdb";public static OleDbConnection GetConn(){OleDbConnection tempconn = new OleDbConnection(connStr);MessageBox.Show(tempconn.DataSource);tempconn.Open();MessageBox.Show(tempconn.State.ToString());return (tempconn);}/// <summary>/// 执行增加、删除、修改指令/// </summary>/// <param name="sql">增加、删除、修改的sql语句</param>/// <param name="param">sql语句的参数</param>/// <returns></returns>public static int ExecuteNonQuery(string sql, params OleDbParameter[] param){using (OleDbConnection conn = new OleDbConnection(connStr)){using (OleDbCommand cmd = new OleDbCommand(sql,conn)){if (param != null){cmd.Parameters.AddRange(param);}conn.Open();return(cmd.ExecuteNonQuery());}}}/// <summary>/// 执行查询指令,获取返回的首行首列的值/// </summary>/// <param name="sql">查询sql语句</param>/// <param name="param">sql语句的参数</param>/// <returns></returns>public static object ExecuteScalar(string sql, params OleDbParameter[] param){using (OleDbConnection conn = new OleDbConnection(connStr)){using (OleDbCommand cmd = new OleDbCommand(sql, conn)){if (param != null){cmd.Parameters.AddRange(param);}conn.Open();return (cmd.ExecuteScalar());}}}/// <summary>/// 执行查询指令,获取返回的datareader/// </summary>/// <param name="sql">查询sql语句</param>/// <param name="param">sql语句的参数</param>/// <returns></returns>public static OleDbDataReader ExecuteReader(string sql, params OleDbParameter[] param){OleDbConnection conn = new OleDbConnection(connStr);OleDbCommand cmd = conn.CreateCommand();mandText = sql;mandType = CommandType.Text;if (param != null){cmd.Parameters.AddRange(param);}conn.Open();return (cmd.ExecuteReader(CommandBehavior.CloseConnection));}/// <summary>/// 执行查询指令,获取返回datatable/// </summary>/// <param name="sql">查询sql语句</param>/// <param name="param">sql语句的参数</param>/// <returns></returns>public static DataTable ExecuteDatable(string sql, params OleDbParameter[] param){using (OleDbConnection conn = new OleDbConnection(connStr)){using (OleDbCommand cmd = new OleDbCommand(sql, conn)){if (param != null){cmd.Parameters.AddRange(param);}DataTable dt = new DataTable();OleDbDataAdapter sda = new OleDbDataAdapter(cmd);sda.Fill(dt);return (dt);}}}}}
转载自:/zhouxy0901/article/details/39968437
如果觉得《C#操作Access数据库》对你有帮助,请点赞、收藏,并留下你的观点哦!