OleDbHelper类

http://hi.baidu.com/tuberose1605/blog/item/501ee319cae4f973dbb4bd32.html

//****************************应用 Demo ***************************

/// <summary>
/// 更新指定图层类型
/// </summary>
/// <param name="TypeID">图层类型编号</param>
/// <param name="TypeName">图层类型名称</param>
/// <param name="LayerName">图层名称</param>
/// <param name="TypeIcon">类型图标</param>
/// <param name="ParentID">父类别编号(0为顶级类别)</param>
/// <param name="ThemeID">单值层编号</param>
public void UpdateLayerType(int TypeID, string TypeName, string LayerName, string TypeIcon, int ParentID, int ThemeID)
{
//定义执行SQL语句所需的参数
OleDbParameter[] parlist ={
oleDbHelper.CreateInParam("@TypeName",OleDbType.Char,255,TypeName),
oleDbHelper.CreateInParam("@LayerName",OleDbType.Char,255,LayerName),
oleDbHelper.CreateInParam("@TypeIcon",OleDbType.Char,255,TypeIcon),
oleDbHelper.CreateInParam("@ParentID",OleDbType.Integer,8,ParentID),
oleDbHelper.CreateInParam("@ThemeID",OleDbType.Integer,8,ThemeID),
oleDbHelper.CreateInParam("@TypeID",OleDbType.Integer,8,TypeID)
};
//定义SQL语句
string sql = "update tb_LayerType set TypeName=@TypeName,LayerName=@LayerName,TypeIcon=@TypeIcon,ParentID=@ParentID,ThemeID=@ThemeID where TypeID=@TypeID";
try
{
//执行SQL语句
oleDbHelper.RunSQL(sql, parlist);
}
catch (Exception ex)
{
//将执行数据库异常写入日志文件
OleDbHelper.SystemError.CreateErrorLog(ex.ToString());
}
}

//****************************OleDbHelper类***************************

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.ComponentModel;

namespace OleDbHelper
{
/// <summary>
/// OleDbHelper类封装对Access数据库的添加、删除、修改和选择等操作
/// </summary>
public class OleDbHelper
{
/// 连接数据源
private OleDbConnection myConnection = null;
private readonly string RETURNVALUE = "RETURNVALUE";

/// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
// 打开数据库连接
if (myConnection == null)
{
myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["OLEDBCONNECTIONSTRING"].ToString());
}
if (myConnection.State == ConnectionState.Closed)
{
try
{
///打开数据库连接
myConnection.Open();
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭已经打开的数据库连接   
}
}
}

/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
///判断连接是否已经创建
if (myConnection != null)
{
///判断连接的状态是否打开
if (myConnection.State == ConnectionState.Open)
{
myConnection.Close();
}
}
}

/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (myConnection != null)
{
myConnection.Dispose();
myConnection = null;
}
}

/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <returns>返回值</returns>
public void RunSQL(string cmdText)
{
OleDbCommand cmd = CreateOleDbCommand(cmdText, null);
try
{
///执行SQL语句
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
}


/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <returns>返回值</returns>
public void RunSQL(string cmdText, OleDbParameter[] prams)
{
OleDbCommand cmd = CreateOleDbCommand(cmdText, prams);
try
{
///执行SQL语句
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
}

/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param> 
/// <param name="dataReader">返回DataReader对象</param>
public void RunSQL(string cmdText, out OleDbDataReader dataReader)
{
///创建Command
OleDbCommand cmd = CreateOleDbCommand(cmdText, null);

try
{
///读取数据
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
dataReader = null;
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
}

/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <param name="dataReader">返回DataReader对象</param>
public void RunSQL(string cmdText, OleDbParameter[] prams, out OleDbDataReader dataReader)
{
///创建Command
OleDbCommand cmd = CreateOleDbCommand(cmdText, prams);

try
{
///读取数据
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
dataReader = null;
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
}

/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="dataSet">DataSet对象</param>
public void RunSQL(string cmdText, ref DataSet dataSet)
{
if (dataSet == null)
{
dataSet = new DataSet();
}
///创建OleDbDataAdapter
OleDbDataAdapter da = CreateOleDbDataAdapter(cmdText, null);

try
{
///读取数据
da.Fill(dataSet);
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
}

/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <param name="dataSet">DataSet对象</param>
public void RunSQL(string cmdText, OleDbParameter[] prams, ref DataSet dataSet)
{
if (dataSet == null)
{
dataSet = new DataSet();
}
///创建OleDbDataAdapter
OleDbDataAdapter da = CreateOleDbDataAdapter(cmdText, prams);

try
{
///读取数据
da.Fill(dataSet);
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
}


/// <summary>
/// 创建一个OleDbCommand对象以此来执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <returns>返回OleDbCommand对象</returns>
private OleDbCommand CreateOleDbCommand(string cmdText, OleDbParameter[] prams)
{
///打开数据库连接
Open();

///设置Command
OleDbCommand cmd = new OleDbCommand(cmdText, myConnection);

///添加到语句的参数
if (prams != null)
{
foreach (OleDbParameter parameter in prams)
{
cmd.Parameters.Add(parameter);
}
}

///添加返回参数ReturnValue
cmd.Parameters.Add(RETURNVALUE, OleDbType.Integer, 4);

//返回创建的OleDbCommand对象
return cmd;
}


/// <summary>
/// 创建一个OleDbDataAdapter对象，用此来执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <returns>返回OleDbDataAdapter对象</returns>
private OleDbDataAdapter CreateOleDbDataAdapter(string cmdText, OleDbParameter[] prams)
{
///打开数据库连接
Open();

///设置OleDbDataAdapter对象
OleDbDataAdapter da = new OleDbDataAdapter(cmdText, myConnection);

///添加到语句的参数
if (prams != null)
{
foreach (OleDbParameter parameter in prams)
{
da.SelectCommand.Parameters.Add(parameter);
}
}

///添加返回参数ReturnValue
//da.SelectCommand.Parameters.Add(
//    new OleDbParameter(RETURNVALUE, OleDbType.Integer, 4, ParameterDirection.ReturnValue,
//    false, 0, 0, string.Empty, DataRowVersion.Default, null));

///返回创建的OleDbDataAdapter对象
return da;
}

/// <summary>
/// 生成SQL语句参数
/// </summary>
/// <param name="ParamName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public OleDbParameter CreateParam(string ParamName, OleDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
OleDbParameter param;

///当参数大小为0时，不使用该参数大小值
if (Size > 0)
{
param = new OleDbParameter(ParamName, DbType, Size);
}
else
{
///当参数大小为0时，不使用该参数大小值
param = new OleDbParameter(ParamName, DbType);
}

///创建输出类型的参数
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}

///返回创建的参数
return param;
}      

/// <summary>
/// 传入输入参数
/// </summary>
/// <param name="ParamName">参数名称</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的parameter 对象</returns>
public OleDbParameter CreateInParam(string ParamName, OleDbType DbType, int Size, object Value)
{
return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
// oleDbHelper.CreateInParam("@TypeName",OleDbType.Char,255,TypeName)
}

/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public OleDbParameter CreateOutParam(string ParamName, OleDbType DbType, int Size)
{
return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}

/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public OleDbParameter CreateReturnParam(string ParamName, OleDbType DbType, int Size)
{
return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
}
}
}