c#操作access数据库DBHelp类

小渣渣 · 发表于 2015-9-23 09:41:24

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace Model
{
public class AccessDBHelper
{
/// <summary>
/// 操作结果消息
/// </summary>
public static string errorMessage = string.Empty;
public const string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\aaa.mdb; Jet OLEDB:Database Password=111";
/// <summary>
/// 执行SQL语句.返回执行结果对象
/// 【Select:返回DataTable 】【Insert/Update/Delete:返回Int型受印象行数】【 CreateTable/DropTable:返回True||False】
/// </summary>
/// <param name="sqlStr">要执行的SQL语句</param>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="opt">要执行的操作</param>
/// <returns>
/// 返回执行结果对象【Select:返回DataTable 】【Insert/Update/Delete:返回Int型受印象行数】【 CreateTable/DropTable:返回True||False】
/// </returns>
/// <Author> frd 2011-9-9</Author>
public static object ExecSql(string sqlStr, OperateType opt)
{
object returnObj = new object();
OleDbConnection odbConn = new OleDbConnection(connStr);
odbConn.Open();
switch (opt)
{
case OperateType.Select:
{
DataTable selectResult = new DataTable();
try
{
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlStr, connStr);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Select");
returnObj = dataSet.Tables[0];
}
catch (Exception ex)
{
returnObj = -1;
errorMessage = "数据查询失败：" + ex.ToString();
}
finally
{
odbConn.Close();
}
}
break;
case OperateType.Insert:
case OperateType.Update:
case OperateType.Delete:
{
try
{
OleDbCommand odbc = new OleDbCommand(sqlStr, odbConn);
returnObj = odbc.ExecuteNonQuery();
}
catch (Exception ex)
{
returnObj = -1;
errorMessage = "数据操作失败：" + ex.ToString();
}
finally
{
odbConn.Close();
}
}
break;
case OperateType.CreateTable:
case OperateType.DropTable:
{
try
{
OleDbCommand odbc = new OleDbCommand(sqlStr, odbConn);
odbc.ExecuteNonQuery();
returnObj = true;
}
catch (Exception ex)
{
returnObj = false;
errorMessage = "数据操作失败：" + ex.ToString();
}
finally
{
odbConn.Close();
}
}
break;
default:
{ odbConn.Close(); }
break;
}
return returnObj;
}
/// <summary>
/// 数据库操作类型
/// </summary>
public enum OperateType
{
/// <summary>
/// 查询记录
/// </summary>
Select,
/// <summary>
/// 插入记录
/// </summary>
Insert,
/// <summary>
/// 更新记录
/// </summary>
Update,
/// <summary>
/// 删除记录
/// </summary>
Delete,
/// <summary>
/// 创建表
/// </summary>
CreateTable,
/// <summary>
/// 删除表
/// </summary>
DropTable
}
}
}

复制代码

未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序解决办法
http://www.itsvse.com/thread-2293-1-1.html
(出处: 武汉软件工程职业学院)
可能会报这个错误。

admin · 发表于 2015-9-23 18:25:44

小渣渣 · 发表于 2016-2-26 18:27:10

1楼的方法在并发的情况下容易挂掉，一会打开一会关闭的。不推荐，用一下我下面的方法吧！

public class AccessDB
{
//变量声明处#region 变量声明处
public OleDbConnection Conn;
public string ConnString;//连接字符串
//构造函数与连接关闭数据库#region 构造函数与连接关闭数据库
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="Dbpath">ACCESS数据库路径</param>
public AccessDB(string Dbpath)
{
//ConnString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
ConnString += Dbpath;
Conn = new OleDbConnection(ConnString);
Conn.Open();
}
/**//// <summary>
/// 打开数据源链接
/// </summary>
/// <returns></returns>
public OleDbConnection DbConn()
{
Conn.Open();
return Conn;
}
/**//// <summary>
/// 请在数据传递完毕后调用该函数，关闭数据链接。
/// </summary>
public void Close()
{
Conn.Close();
}
//数据库基本操作#region 数据库基本操作
/**//// <summary>
/// 根据SQL命令返回数据DataTable数据表,
/// 可直接作为dataGridView的数据源
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public DataTable SelectToDataTable(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataTable Dt = new DataTable();
adapter.Fill(Dt);
return Dt;
}
/**//// <summary>
/// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。
/// </summary>
/// <param name="SQL"></param>
/// <param name="subtableName">在返回的数据集中所添加的表的名称</param>
/// <returns></returns>
public DataSet SelectToDataSet(string SQL, string subtableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataSet Ds = new DataSet();
Ds.Tables.Add(subtableName);
adapter.Fill(Ds, subtableName);
return Ds;
}
/**//// <summary>
/// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。
/// </summary>
/// <param name="SQL"></param>
/// <param name="subtableName">添加的表名</param>
/// <param name="DataSetName">被添加的数据集名</param>
/// <returns></returns>
public DataSet SelectToDataSet (string SQL, string subtableName, DataSet DataSetName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataTable Dt = new DataTable();
DataSet Ds = new DataSet();
Ds = DataSetName;
adapter.Fill(DataSetName, subtableName);
return Ds;
}
/**//// <summary>
/// 根据SQL命令返回OleDbDataAdapter，
/// 使用前请在主程序中添加命名空间System.Data.OleDb
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
return adapter;
}
/**//// <summary>
/// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public bool ExecuteSQLNonquery(string SQL)
{
OleDbCommand cmd = new OleDbCommand(SQL, Conn);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
}

复制代码

小渣渣 · 发表于 2016-3-30 16:07:28

小渣渣发表于 2016-2-26 18:27
1楼的方法在并发的情况下容易挂掉，一会打开一会关闭的。不推荐，用一下我下面的方法吧！

Model.AccessDB db = new Model.AccessDB(@".\AlipayDB.mdb;Jet OLEDB:Database Password=123");

		自动登录	找回密码
密码			注册[Register]

[资料] c#操作access数据库DBHelp类

相关帖子