DBHelper数据库操作类
vs2010+Access开发程序用到的DBHelper类
分享备用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;
namespace DBHelper
{
class DBHelp
{
//SqlDataReader dataReader = null; //dataRader 对象
OleDbDataReader dr = null;
//SqlCommand com = null; //执行命令
OleDbCommand cmd = null;
//SqlConnection con = null; //连库类
OleDbConnection conn = null;
OleDbDataAdapter sda = null;
/// <summary>
/// 构造函数
/// </summary>
public DBHelp()
{
//连接数据库字符串
string dbpath = Application.StartupPath+"\\";
//string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=E:\db.mdb;Jet OleDb:DataBase Password=dbdb";
//获取程序当前目录下的数据库
string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source="+dbpath+"db.mdb;Jet OleDb:DataBase Password=dbdb";
conn = new OleDbConnection(connStr);
//打开数据库连接
conn.Open();
cmd = new OleDbCommand();
cmd.Connection = conn;
}
/// <summary>
/// 增删改查
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int GetAddUpDel(string sql)
{
//cmd.CommandText = sql;
OleDbCommand cmd = new OleDbCommand(sql, conn);
int data = Convert.ToInt32(cmd.ExecuteScalar());
return data;
}
/// <summary>
/// 增删改查,参数查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int UpdateDeleteAdd(string sql,string query1,string query2)
{
cmd.CommandText = sql;
cmd.Parameters.AddWithValue(query1,query2);
int data = Convert.ToInt32(cmd.ExecuteScalar());
return data;
}
///<summary>
///带参数的执行命令
///</summary>
///<param name="sql"></param>
///<returns></returns>
public int ExecuteCommand(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 增删改查,参数查询,两个参数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int GetData_value(string sql, string query1, string query2,string query3, string query4)
{
cmd.CommandText = sql;
cmd.Parameters.AddWithValue(query1, query2);
cmd.Parameters.AddWithValue(query3, query4);
int data = Convert.ToInt32(cmd.ExecuteScalar());
return data;
}
/// <summary>
/// 查询多个值的方法,正解20160816
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public OleDbDataReader GetReader(string safeSql ,string query1, string query2)
{
OleDbCommand cmd = new OleDbCommand(safeSql, conn);
cmd.Parameters.AddWithValue(query1, query2);
// return cmd.ExecuteReader(CommandBehavior.CloseConnection);
return cmd.ExecuteReader();
}
/// <summary>
/// 查询多个值的方法，过时
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public OleDbDataReader GetDataReader(string sql)
{
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
return dr;
}
/// <summary>
/// 查询多个值的方法,参数查询，过时
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public OleDbDataReader SelectAll(string sql, string query1, string query2)
{
// cmd.CommandText = sql;
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.AddWithValue(query1, query2);
dr = cmd.ExecuteReader();
return dr;
}
/// <summary>
/// 获取OleDbDataAdapter,参数查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public OleDbDataAdapter GetAdapter(string sql, string query1, string query2)
{
cmd.CommandText = sql;
cmd.Parameters.AddWithValue(query1, query2);
sda = new OleDbDataAdapter(cmd);
return sda;
}
/// <summary>
/// 获取OleDbDataAdapter,参数查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string strSQL)
{
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
///<summary>
///执行查找语句，返回datatable对象
///</summary>
public DataTable GetDataTable(string strSQL)
{
DataTable dt = new DataTable();
try
{
OleDbDataAdapter sda = new OleDbDataAdapter(strSQL, conn);
sda.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
///<summary>
///执行查找语句，返回datatable对象,带参数
///</summary>
public DataTable GetDataTable_var(string strSQL,string var1,string var2)
{
DataTable dt = new DataTable();
try
{
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
cmd.Parameters.AddWithValue(var1, var2);
OleDbDataAdapter sda = new OleDbDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
//string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=E:\db.mdb;Jet OleDb:DataBase Password=dbdb";
string dbpath = Application.StartupPath + "\\";
string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=" + dbpath + "db.mdb;Jet OleDb:DataBase Password=dbdb";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();//打开数据库连接
using (OleDbTransaction trans = conn.BeginTransaction())//开始数据库事务
{
OleDbCommand cmd = new OleDbCommand();//创建SqlCommand命令
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)//循环哈希表（本例中 即，循环执行添加在哈希表中的sql语句
{
string cmdText = myDE.Key.ToString();//获取键值（本例中 即，sql语句）
OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;//获取键值（本例中 即，sql语句对应的参数）
PrepareCommand(cmd, conn, trans, cmdText, cmdParms); //调用PrepareCommand()函数，添加参数
int val = cmd.ExecuteNonQuery();//调用增删改函数ExcuteNoQuery()，执行哈希表中添加的sql语句
cmd.Parameters.Clear(); //清除参数
}
trans.Commit();//提交事务
}
catch //捕获异常
{
trans.Rollback(); //事务回滚
throw; //抛出异常
}
}
}
}
//添加参数
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)//如果数据库连接为关闭状态
conn.Open();//打开数据库连接
cmd.Connection = conn;//设置命令连接
cmd.CommandText = cmdText;//设置执行命令的sql语句
if (trans != null)//如果事务不为空
cmd.Transaction = trans;//设置执行命令的事务
cmd.CommandType = CommandType.Text;//设置解释sql语句的类型为“文本”类型（也是就说该函数不适用于存储过程）
if (cmdParms != null)//如果参数数组不为空
{
foreach (OleDbParameter parameter in cmdParms) //循环传入的参数数组
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value; //获取参数的值
}
cmd.Parameters.Add(parameter);//添加参数
}
}
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void GetOpen()
{
conn.Open();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void GetClose()
{
conn.Close();
}
}
}
