C#的access操作工具类
access 操作工具类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace www.xinduofen.com
{
class AccessOperateTool
{
private static String appBaseDirectory = AppDomain.CurrentDomain.BaseDirectory;//应用程序安装基目录
private static string catalog = appBaseDirectory + "resources\\accessDb\\";//所有数据源所在目录
private const string data_password = "lgq";//数据库的密码
/// <summary>
/// 获取数据库连接对象
/// </summary>
/// <param name="dbFile">传入的数据库名(无需带有后缀名".mdb")</param>
/// <returns>返回值为null代表获取连接失败</returns>
public static OleDbConnection getConnection(string dbFile)
{
OleDbConnection db_connection = null;
if (string.IsNullOrEmpty(dbFile))//如果传入的参数异常
{
return null;
}
db_connection = new OleDbConnection("provider=microsoft.jet.oledb.4.0; Data Source="
+ catalog + dbFile + ".mdb;" + "Jet OLEDB:Database Password=" + data_password);
try
{
db_connection.Open();
}
catch (InvalidOperationException)//如果连接已经打开
{
Console.WriteLine("连接已经打开");
}
catch (OleDbException)//如果数据库文件丢失或者被其他程序占用或者是连接超时
{
db_connection = new OleDbConnection("provider=microsoft.jet.oledb.4.0; Data Source="
+ catalog + dbFile + ".mdb;" + "Jet OLEDB:Database Password=" + data_password);//假设是连接超时
try
{
db_connection.Open();
}
catch (OleDbException)//如果不是连接超时,就是数据库文件丢失或者被其他程序占用
{
Console.WriteLine("数据库文件丢失或者被其他程序占用");
return null;//数据库连接失败,此语句必须含有
}
}
return db_connection;
}
/// <summary>
/// 创建一个事务,并且开始执行事务
/// </summary>
/// <param name="dbConnection">数据库连接对象</param>
/// <returns>返回为null代表创建事务失败</returns>
public static OleDbTransaction beginTransaction(OleDbConnection dbConnection)
{
OleDbTransaction transaction = null;
if (dbConnection == null)
{
return null;
}
try
{
transaction = dbConnection.BeginTransaction();//创建事务,并且开始执行事务
}
catch (Exception)
{
Console.WriteLine("创建事务时产生了异常!");
}
return transaction;
}
/// <summary>
/// 提交事务
/// </summary>
/// <param name="transaction">与一个事务相关的对象</param>
public static void transactionCommit(OleDbTransaction transaction)
{
try
{
//提交事务
transaction.Commit();
}
catch (Exception)
{
Console.WriteLine("提交事务时产生了异常!");
}
}
/// <summary>
/// 回滚事务
/// </summary>
/// <param name="transaction">与一个事务相关的对象</param>
public static void transactionRollback(OleDbTransaction transaction)
{
try
{
//回滚事务
transaction.Rollback();
}
catch (Exception)
{
Console.WriteLine("回滚事务时产生了异常!");
}
}
/// <summary>
/// 关闭数据库的连接
/// </summary>
/// <param name="db_connection">数据库连接对象</param>
public static void closeConnection(OleDbConnection db_connection)
{
if (db_connection != null)//如果连接不为空
{
if (db_connection.State == ConnectionState.Open)//如果连接处于打开状态
db_connection.Close();
}
}
/// <summary>
/// 查询单个信息
/// </summary>
/// <param name="sql">sql查询语句</param>
/// <param name="db_connection">数据库连接对象</param>
/// <param name="oleDbTransaction">数据库连接事务对象</param>
/// <returns>
/// 如果返回值为空代表查询失败;
/// 此方法只能返回一个值,主要用于查行数,等其他用途(传入的参数均不能为空);
/// 返回 Object 类型的数据,执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行;
/// </returns>
public static Object dataSingle(string sql, OleDbConnection db_connection, OleDbTransaction oleDbTransaction)
{
Object result = null;
if (string.IsNullOrEmpty(sql) || db_connection == null)//如果传入参数不合法
{
return null;
}
try
{
OleDbCommand db_command = db_connection.CreateCommand();//创建数据库执行命令语句对象
if (oleDbTransaction!=null)
{
db_command.Transaction = oleDbTransaction;
}
db_command.CommandText = sql;//执行相关的sql语句
result = db_command.ExecuteScalar();//此命令只会返回一个值
}
catch (Exception)//如果sql命令执行失败
{
Console.WriteLine("sql查询语句不合法或者数据库连接对象异常");
}
return result;//返回读到的值
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql">sql执行语句</param>
/// <param name="db_connection">数据库连接对象</param>
/// <param name="oleDbTransaction">数据库连接事务对象</param>
/// <returns>
/// 返回值为0代表sql命令执行后对数据库无任何影响,如果返回值大于0代表sql命令执行后对数据库产生了影响;
/// 此方法只能返回一个值(指令在数据库影响的行数),主要用于执行 insert、delete、alter操作;
/// </returns>
public static int executeNonQuery(string sql, OleDbConnection db_connection, OleDbTransaction oleDbTransaction)
{
int resultCnt = 0;
if (string.IsNullOrEmpty(sql) || db_connection == null)//如果传入参数不合法
{
return 0;
}
try
{
OleDbCommand db_command = db_connection.CreateCommand();//创建数据库执行命令语句对象
if (oleDbTransaction!=null)
{
db_command.Transaction = oleDbTransaction;
}
db_command.CommandText = sql;//执行相关的sql语句
resultCnt = db_command.ExecuteNonQuery();//此命令无查询功能
}
catch (Exception)//如果sql命令执行失败
{
Console.WriteLine("sql查询语句不合法或者数据库连接对象异常");
}
return resultCnt;//返回影响的行数
}
/// <summary>
/// 查询多行信息
/// </summary>
/// <param name="sql">sql查询语句</param>
/// <param name="db_connection">数据库连接对象</param>
/// <param name="oleDbTransaction">数据库连接事务对象</param>
/// <returns>
/// 返回为null,代表查询失败,返回 OleDbDataReader 类型的数据,主要用于检索多行值;
/// 此方法只能返回 OleDbDataReader 类型的数据,只能一行一行的读数据;
/// </returns>
public static OleDbDataReader dataReader(string sql, OleDbConnection db_connection, OleDbTransaction oleDbTransaction)
{
OleDbDataReader data_read = null;
if (string.IsNullOrEmpty(sql) || db_connection == null)
{
return null;
}
try
{
OleDbCommand db_command = db_connection.CreateCommand();//创建数据库执行命令语句对象
if (oleDbTransaction!=null)
{
db_command.Transaction = oleDbTransaction;
}
db_command.CommandText = sql;//执行相关的sql语句
data_read = db_command.ExecuteReader();//读取数据库中的数据
}
catch (Exception)//如果sql命令执行失败
{
Console.WriteLine("sql查询语句不合法或者数据库连接对象异常");
}
return data_read;//返回读到的数据
}
/// <summary>
/// 此方法是向传入的 dataset 中填入数据,“不支持更新功能”
/// </summary>
/// <param name="sql">sql查询语句</param>
/// <param name="dataset">用于存储查询信息的数据集缓存区</param>
/// <param name="table_name">是dataset要缓存查询信息的DataTble名称</param>
/// <param name="db_connection">数据库连接对象</param>
/// <param name="oleDbTransaction">数据库连接事务对象</param>
/// <returns>查询成功返回true,失败返回false</returns>
public static bool dataAdappterDataSet(string sql, DataSet dataset, string table_name
, OleDbConnection db_connection, OleDbTransaction oleDbTransaction)
{
//初始化为“查询失败”
bool result = false;
if (string.IsNullOrEmpty(sql) || dataset == null || string.IsNullOrEmpty(table_name) || db_connection == null)
{
return false;
}
try
{
//定义将数据库的数据匹配到DataSet的适配器对象
OleDbDataAdapter data_adappter = new OleDbDataAdapter(sql, db_connection);
if (oleDbTransaction!=null)
{
data_adappter.SelectCommand.Transaction = oleDbTransaction;
}
data_adappter.Fill(dataset, table_name);
result = true;//查询成功
}
catch (Exception)//如果sql命令执行失败
{
Console.WriteLine("sql查询语句不合法或者数据库连接对象异常");
}
return result;//返回查询结果
}
/// <summary>
/// 此方法是向传入的 dataset 中填入数据,“支持更新功能”,执行更新之前不能断开与数据库的连接
/// </summary>
/// <param name="sql">sql查询语句</param>
/// <param name="dataset">用于存储查询信息的数据集缓存区</param>
/// <param name="table_name">是dataset要缓存查询信息的DataTble名称</param>
/// <param name="db_connection">数据库连接对象</param>
/// <param name="oleDbTransaction">数据库连接事务对象</param>
/// <returns>返回一个 OleDbCommandBuilder 对象,可以用来进行更新操作,将内存dataset中改变的数据同步到数据库中</returns>
public static OleDbCommandBuilder dataAdappterDataSetUpdate(string sql, DataSet dataset, string table_name
, OleDbConnection db_connection, OleDbTransaction oleDbTransaction)
{
OleDbCommandBuilder commandBuilder = null;
if (string.IsNullOrEmpty(sql) || dataset == null || string.IsNullOrEmpty(table_name) || db_connection == null)
{
return null;
}
try
{
//定义将数据库的数据匹配到DataSet的适配器对象
OleDbDataAdapter data_adappter = new OleDbDataAdapter(sql, db_connection);
if (oleDbTransaction!=null)
{
data_adappter.SelectCommand.Transaction = oleDbTransaction;
}
OleDbCommandBuilder builder = new OleDbCommandBuilder(data_adappter);
data_adappter.Fill(dataset, table_name);
commandBuilder = builder;//查询成功
}
catch (Exception)//如果sql命令执行失败
{
Console.WriteLine("sql查询语句不合法或者数据库连接对象异常");
}
return commandBuilder;//返回查询结果
}
}
}
内容来自:越康体育
C#的access操作工具类
来源: http://www.bubuko.com/infodetail-2154159.html