最新的DBHelper

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace ZhiyiHelper
{
public partial class DBHelper
{
/**
* 以下的方法需要在调用的可见区域内：手动(即调用OpenCon(); CloseCon();方法)打开和关闭连接
* 方法注释中有[Notice Con] 或 [Notice Connection] 或方法名中含有“_Other”，则调用的是以下的方法
* */

#region 数据库操作方法

/// <summary>
/// 执行增，删，改命令的方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Execute_Other(string sql, CommandType commandType, params OleDbParameter[] sqlParams)
{
OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = commandType;
cmd.CommandTimeout = 180;
SetParams(cmd, sqlParams);

try
{
return cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
}
}

/// <summary>
/// 返回第一行第一列的值[Int]
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteScalar_Int_Other(string sql, CommandType comType, params OleDbParameter[] sqlParams)
{
object reObj = ExecuteScalar_Object_Other(sql, comType, sqlParams);
return reObj == null ? 0 : Convert.ToInt32(reObj);
}

/// <summary>
/// 返回第一行第一列的值[Object]
/// </summary>
/// <returns></returns>
public static object ExecuteScalar_Object_Other(string sql, CommandType comType, params OleDbParameter[] sqlParams)
{
OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = comType;
cmd.CommandTimeout = 180;
DBHelper.SetParams(cmd, sqlParams);

try
{
return cmd.ExecuteScalar();
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
}
}


/// <summary>
/// 返回OleDbDataReader的方法
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static OleDbDataReader GetReader_Other(string sql, CommandType commandType, params OleDbParameter[] sqlParams)
{
OleDbDataReader reader = null;

OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = commandType;

SetParams(cmd, sqlParams);
try
{
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
}
return reader;
}

/// <summary>
/// 返回OleDbDataReader的方法 [reader和数据库连接都需要显示关闭]
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static OleDbDataReader GetReader_Other2(string sql, CommandType commandType, params OleDbParameter[] sqlParams)
{
OleDbDataReader reader = null;

OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = commandType;

SetParams(cmd, sqlParams);
try
{
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
}
return reader;
}



/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran_Other(List<string> SQLStringList)
{
if (SQLStringList == null || SQLStringList.Count == 0)
return;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conObject;
cmd.CommandType = CommandType.Text;

OleDbTransaction tx = conObject.BeginTransaction();
cmd.Transaction = tx;
try
{
string sql = String.Empty;
for (int n = 0; n < SQLStringList.Count; n++)
{
sql = SQLStringList[n];
if (sql.Trim().Length > 1)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OleDb.OleDbException e)
{
tx.Rollback();
throw e;
}
finally
{
//释放资源
if (tx != null)
{
tx.Dispose();
tx = null;
}
DisponseCmd(cmd);
}
}

/// <summary>
/// 关闭和释放数据读取器
/// </summary>
/// <param name="reader"></param>
public static void CloseReader(OleDbDataReader reader)
{
if (reader != null && reader.IsClosed)
{
reader.Dispose();
reader.Close();
reader = null;
}
}

#endregion
}

}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace ZhiyiHelper
{
public partial class DBHelper
{
private static string connstr = String.Empty;
private static OleDbConnection conObject = null;

public DBHelper()
{

}

#region 基础方法

/// <summary>
/// 获取连接字符串的属性
/// </summary>
private static string Connstr
{
get
{
if (connstr == String.Empty)
{
connstr = ConfigHelper.GetConnectionStringsString("accessConSql");
connstr = GetConnString();
}
return connstr;
}
}

/// <summary>
/// 得到数据库连接方法
/// </summary>
/// <returns>数据库连接</returns>
private static void Getconn()
{
if (conObject == null)
conObject = new OleDbConnection(Connstr);
}


/// <summary>
/// 获得并打开数据库连接方法
/// </summary>
/// <returns></returns>
public static void OpenCon()
{
Getconn();
if (conObject.State == ConnectionState.Open)
return;
if (conObject.State != ConnectionState.Closed)
conObject.Close();
conObject.Open();
}

/// <summary>
/// 关闭数据库连接方法
/// </summary>
public static void CloseCon()
{
if (conObject != null && conObject.State != ConnectionState.Closed)
conObject.Close();
}

#endregion

#region 数据库操作方法

public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ") from " + TableName;
try
{
return GetScalar(strsql);
}
catch (OleDbException ex)
{
throw ex;
}
}

/// <summary>
/// 删除制定表中的一个字段(文本列)
/// </summary>
/// <param name="colName"></param>
/// <param name="TableName"></param>
public static void DelColumn(string colName, string TableName)
{
if (string.IsNullOrEmpty(colName.Trim()))
return;
try
{
Execute("alter table ["+TableName+"] drop COLUMN ["+colName+"]");
}
catch (OleDbException ex)
{
throw ex;
}
}

/// <summary>
/// 在制定表中添加一个字段(文本列)
/// </summary>
/// <param name="colName"></param>
/// <param name="TableName"></param>
public static void AddColumn(string colName, string TableName)
{
try
{
Execute("ALTER TABLE " + TableName + " ADD COLUMN " + colName + " TEXT(100)");//TEXT不加长度，则为此字段类型的默认最大长度
}
catch (OleDbException ex)
{
throw ex;
}
}

/// <summary>
/// 在制定表中添加一个字段(数字(double)列)
/// </summary>
/// <param name="colName"></param>
/// <param name="TableName"></param>
public static void AddColumn_Double(string colName, string TableName)
{
try
{
Execute("ALTER TABLE " + TableName + " ADD COLUMN " + colName + " Double DEFAULT 0");
}
catch (OleDbException ex)
{
throw ex;
}
}

/// <summary>
/// 执行增，删，改命令的方法(一) [非存储过程SQL]
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Execute(string sql)
{
return Execute(sql, CommandType.Text);
}


/// <summary>
/// 执行增，删，改命令的方法(二)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Execute(string sql, CommandType commandType, params OleDbParameter[] sqlParams)
{
OpenCon();
OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = commandType;
cmd.CommandTimeout = 180;
SetParams(cmd, sqlParams);

try
{
return cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
CloseCon();
}
}

/// <summary>
/// 执行增，删，改命令的方法 ----重载方法 [存储过程]
/// </summary>
/// <param name="sql">存储过程名</param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static int Execute(string sql, params OleDbParameter[] sqlParams)
{
return Execute(sql, CommandType.StoredProcedure, sqlParams);
}

/// <summary>
/// 返回第一行第一列的值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteScalar(string sql, CommandType comType, params OleDbParameter[] sqlParams)
{
object reObj = ExecuteScalar1(sql, comType, sqlParams);
return reObj == null ? 0 : Convert.ToInt32(reObj);
}

/// <summary>
/// 返回第一行第一列的值 ----非存储过程SQL查询方法
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static int GetScalar(string sql, params OleDbParameter[] sqlParams)
{
return ExecuteScalar(sql, CommandType.Text, sqlParams);
}

/// <summary>
/// 返回第一行第一列的值 ----[存储过程]重载方法
/// </summary>
/// <param name="sql">存储过程名</param>
/// <returns></returns>
public static int ExecuteScalar(string sql, params OleDbParameter[] sqlParams)
{
return ExecuteScalar(sql, CommandType.StoredProcedure, sqlParams);
}

/// <summary>
/// 返回第一行第一列的值[Object]
/// </summary>
/// <returns></returns>
public static object ExecuteScalar1(string sql, CommandType comType, params OleDbParameter[] sqlParams)
{
OpenCon();
OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = comType;
cmd.CommandTimeout = 180;
DBHelper.SetParams(cmd, sqlParams);

try
{
return cmd.ExecuteScalar();
}
catch (OleDbException ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
CloseCon();
}
}


/// <summary>
/// 返回第一行第一列的值 ----[存储过程]重载方法
/// </summary>
/// <param name="sql">存储过程名</param>
/// <returns></returns>
public static Object ExecuteScalar2(string sql, params OleDbParameter[] sqlParams)
{
return ExecuteScalar1(sql, CommandType.StoredProcedure, sqlParams);
}


/// <summary>
/// 执行增，删，改命令的方法 ----非存储过程SQL查询方法
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static void ExecuteCommand(string sql, params OleDbParameter[] sqlParams)
{
Execute(sql, CommandType.Text, sqlParams);
}




/// <summary>
/// 查询返回数据表的方法 ---非存储过程SQL查询方法
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="commandType"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static DataTable GetTable(string sql)
{
return GetTable(sql, CommandType.Text);
}


/// <summary>
/// 查询返回数据表的重载方法
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static DataTable GetTable(string sql, CommandType commandType, params OleDbParameter[] sqlParams)
{
DataSet dataSet = null;
Getconn();
OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = commandType;

SetParams(cmd, sqlParams);

OleDbDataAdapter adp = new OleDbDataAdapter();
adp.SelectCommand = cmd;

try
{
dataSet = new DataSet();
adp.Fill(dataSet, "table");
}
catch (Exception ex)
{
throw ex;
}
finally
{
//释放资源
DisponseAdp(adp);
DisponseCmd(cmd);
CloseCon();
}

if (dataSet != null && dataSet.Tables[0] != null)
return dataSet.Tables[0];
return null;
}


/// <summary>
/// 查询返回数据表的重载方法 ---非存储过程SQL
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static DataTable GetTable(string sql, params OleDbParameter[] sqlParams)
{
return GetTable(sql, CommandType.Text, sqlParams);
}


/// <summary>
/// 返回OleDbDataReader的方法
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static OleDbDataReader GetReader(string sql, CommandType commandType, params OleDbParameter[] sqlParams)
{
OleDbDataReader reader = null;

OpenCon();
OleDbCommand cmd = new OleDbCommand(sql, conObject);
cmd.CommandType = commandType;

SetParams(cmd, sqlParams);
try
{
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//释放资源
DisponseCmd(cmd);
}
return reader;
}

/// <summary>
/// 返回OleDbDataReader的方法 ---非存储过程的SQL语句 ---重载
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static OleDbDataReader GetReader(string sql, params OleDbParameter[] sqlParams)
{
return GetReader(sql, CommandType.Text, sqlParams);
}


/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(List<string> SQLStringList)
{
if (SQLStringList == null || SQLStringList.Count == 0)
return;
OpenCon();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conObject;
cmd.CommandType = CommandType.Text;

OleDbTransaction tx = conObject.BeginTransaction();
cmd.Transaction = tx;
try
{
string sql = String.Empty;
for (int n = 0; n < SQLStringList.Count; n++)
{
sql = SQLStringList[n];
if (sql.Trim().Length > 1)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OleDb.OleDbException e)
{
tx.Rollback();
throw e;
}
finally
{
//释放资源
if (tx != null)
{
tx.Dispose();
tx = null;
}
DisponseCmd(cmd);
CloseCon();
}
}

/// <summary>
/// 设置命令中参数的方法
/// </summary>
/// <param name="cmd"></param>
/// <param name="sqlParams"></param>
private static void SetParams(OleDbCommand cmd, params OleDbParameter[] sqlParams)
{
if (sqlParams != null && sqlParams.Length > 0)
cmd.Parameters.AddRange(sqlParams);
}


#endregion

#region 释放资源的方法

private static void DisponseCmd(OleDbCommand cmd)
{
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
}

private static void DisponseAdp(OleDbDataAdapter adp)
{
if (adp != null)
{
adp.Dispose();
adp = null;
}
}

#endregion
}
}