using System;
using System.Data;
using System.Data.OleDb;
public class OleDBHelper
{
private OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D://Design//WebSite_JY//Data//jiayi.accdb");
#region ExecuteNonQuery 增删改方法
/// <summary>
/// 执行SQL语句的增删改方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>受影响行数</returns>
public bool ExecuteNonQuery(string SQLObject,params OleDbParameter[] paramerts)
{
cn.Open();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
int rows = cmd.ExecuteNonQuery();
cn.Close();
return rows>0;
}
/// <summary>
/// 执行存储过程的增删改方法
/// </summary>
/// <param name="SQLObject">存储过程名称</param>
/// <param name="paramerts">参数数组</param>
/// <returns>存储过程的返回值</returns>
public int ExecuteNonQueryProc(string SQLObject,params OleDbParameter[] paramerts)
{
cn.Open();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
int rows = cmd.ExecuteNonQuery();
cn.Close();
return rows;
}
/// <summary>
/// 使用事务执行SQL语句的增删改方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>事务是否执行成功</returns>
public bool ExecuteNonQueryTrans(string[] SQLObject,params OleDbParameter[][] paramerts)
{
bool success;
cn.Open();
OleDbCommand cmd =new OleDbCommand();
OleDbTransaction ta=cn.BeginTransaction();
cmd.Transaction=ta;
try
{
for(int i=0;i<SQLObject.Length;i++)
{
cmd= InitCommand(SQLObject[i],paramerts[i]);
cmd.ExecuteNonQuery();
}
ta.Commit();
}
catch
{
ta.Rollback();
success= false;
}
finally
{
cn.Close();
success= true;
}
return success;
}
#endregion
#region ExecuteScalar 获取标量值
/// <summary>
/// 执行SQL语句获取标量值方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>标量值</returns>
public object ExecuteScalar(string SQLObject, params OleDbParameter[] paramerts)
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
/// <summary>
/// 执行存储过程获取标量值方法
/// </summary>
/// <param name="SQLObject">存储过名称程</param>
/// <param name="paramerts">参数数组</param>
/// <returns>标量值</returns>
public object ExecuteScalarProc(string SQLObject,params OleDbParameter[] paramerts)
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
#endregion
#region ExecuteDataSet 获取数据集
/// <summary>
/// 执行SQL语句获取数据集的方法
/// </summary>
/// <param name="SQLObject"></param>
/// <param name="paramerts"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string SQLObject,params OleDbParameter[] paramerts)
{
DataSet ds = new DataSet();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.Text;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
/// <summary>
/// 执行存储过程获取数据集的方法
/// </summary>
/// <param name="SQLObject">存储过程名称</param>
/// <returns></returns>
public DataSet ExecuteDataSetProc(string SQLObject,params OleDbParameter[] paramerts)
{
DataSet ds = new DataSet();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
/// <summary>
/// 执行视图获取数据集的方法
/// </summary>
/// <param name="SQLObject">视图或者表的名称</param>
/// <returns></returns>
public DataSet ExecuteDataSetView(string SQLObject, OleDbParameter[] paramerts)
{
DataSet ds = new DataSet();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.TableDirect;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
#endregion
#region ExecuteDataReader 获取流水游标
/// <summary>
/// 执行SQL语句获取流水游标的方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>指向查询结果的流水游标</returns>
public OleDbDataReader ExecuteDataReader(string SQLObject,params OleDbParameter[] paramerts)
{
try
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cn.Open();
OleDbDataReader dr= cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch
{
cn.Close();
return null;
}
}
/// <summary>
///执行存储过程获取流水游标的方法
/// </summary>
/// <param name="SQLObject">存储过程名称</param>
/// <param name="paramerts">参数数组</param>
/// <returns>指向查询结果的流水游标</returns>
public OleDbDataReader ExecuteDataReaderProc(string SQLObject,params OleDbParameter[] paramerts)
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
cn.Open();
OleDbDataReader dr= cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
#endregion
#region InitCommand 设置命令集参数
/// <summary>
/// //扩展QuerryCMD
/// </summary>
/// <param name="SQLObject"></param>
/// <param name="paramerts"></param>
/// <returns></returns>
private OleDbCommand InitCommand(string SQLObject, params OleDbParameter[] paramerts)
{
OleDbCommand cmd = new OleDbCommand(SQLObject,cn);
foreach(OleDbParameter pt in paramerts)//往Command里添加参数
{
cmd.Parameters.Add(pt);
}
return cmd;
}
private OleDbCommand InitCommandProc(string SQLObject, OleDbParameter[] paramerts)
{
OleDbCommand cmd = new OleDbCommand(SQLObject,cn);
foreach(OleDbParameter pt in paramerts)//往Command里添加参数
{
cmd.Parameters.Add(pt);
}
OleDbParameter pa= new OleDbParameter("ReturnValue",OleDbType.Integer,6,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null);//添加参数映射返回值
cmd.Parameters.Add(pa);
return cmd;
}
#endregion
public DataTable FillDataTable(string SQLObject,params OleDbParameter[] paramerts)
{
DataTable ds = new DataTable();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.Text;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
}
using System.Data;
using System.Data.OleDb;
public class OleDBHelper
{
private OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D://Design//WebSite_JY//Data//jiayi.accdb");
#region ExecuteNonQuery 增删改方法
/// <summary>
/// 执行SQL语句的增删改方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>受影响行数</returns>
public bool ExecuteNonQuery(string SQLObject,params OleDbParameter[] paramerts)
{
cn.Open();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
int rows = cmd.ExecuteNonQuery();
cn.Close();
return rows>0;
}
/// <summary>
/// 执行存储过程的增删改方法
/// </summary>
/// <param name="SQLObject">存储过程名称</param>
/// <param name="paramerts">参数数组</param>
/// <returns>存储过程的返回值</returns>
public int ExecuteNonQueryProc(string SQLObject,params OleDbParameter[] paramerts)
{
cn.Open();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
int rows = cmd.ExecuteNonQuery();
cn.Close();
return rows;
}
/// <summary>
/// 使用事务执行SQL语句的增删改方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>事务是否执行成功</returns>
public bool ExecuteNonQueryTrans(string[] SQLObject,params OleDbParameter[][] paramerts)
{
bool success;
cn.Open();
OleDbCommand cmd =new OleDbCommand();
OleDbTransaction ta=cn.BeginTransaction();
cmd.Transaction=ta;
try
{
for(int i=0;i<SQLObject.Length;i++)
{
cmd= InitCommand(SQLObject[i],paramerts[i]);
cmd.ExecuteNonQuery();
}
ta.Commit();
}
catch
{
ta.Rollback();
success= false;
}
finally
{
cn.Close();
success= true;
}
return success;
}
#endregion
#region ExecuteScalar 获取标量值
/// <summary>
/// 执行SQL语句获取标量值方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>标量值</returns>
public object ExecuteScalar(string SQLObject, params OleDbParameter[] paramerts)
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
/// <summary>
/// 执行存储过程获取标量值方法
/// </summary>
/// <param name="SQLObject">存储过名称程</param>
/// <param name="paramerts">参数数组</param>
/// <returns>标量值</returns>
public object ExecuteScalarProc(string SQLObject,params OleDbParameter[] paramerts)
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
#endregion
#region ExecuteDataSet 获取数据集
/// <summary>
/// 执行SQL语句获取数据集的方法
/// </summary>
/// <param name="SQLObject"></param>
/// <param name="paramerts"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string SQLObject,params OleDbParameter[] paramerts)
{
DataSet ds = new DataSet();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.Text;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
/// <summary>
/// 执行存储过程获取数据集的方法
/// </summary>
/// <param name="SQLObject">存储过程名称</param>
/// <returns></returns>
public DataSet ExecuteDataSetProc(string SQLObject,params OleDbParameter[] paramerts)
{
DataSet ds = new DataSet();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
/// <summary>
/// 执行视图获取数据集的方法
/// </summary>
/// <param name="SQLObject">视图或者表的名称</param>
/// <returns></returns>
public DataSet ExecuteDataSetView(string SQLObject, OleDbParameter[] paramerts)
{
DataSet ds = new DataSet();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.TableDirect;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
#endregion
#region ExecuteDataReader 获取流水游标
/// <summary>
/// 执行SQL语句获取流水游标的方法
/// </summary>
/// <param name="SQLObject">SQL语句</param>
/// <param name="paramerts">参数数组</param>
/// <returns>指向查询结果的流水游标</returns>
public OleDbDataReader ExecuteDataReader(string SQLObject,params OleDbParameter[] paramerts)
{
try
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cn.Open();
OleDbDataReader dr= cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch
{
cn.Close();
return null;
}
}
/// <summary>
///执行存储过程获取流水游标的方法
/// </summary>
/// <param name="SQLObject">存储过程名称</param>
/// <param name="paramerts">参数数组</param>
/// <returns>指向查询结果的流水游标</returns>
public OleDbDataReader ExecuteDataReaderProc(string SQLObject,params OleDbParameter[] paramerts)
{
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.StoredProcedure;
cn.Open();
OleDbDataReader dr= cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
#endregion
#region InitCommand 设置命令集参数
/// <summary>
/// //扩展QuerryCMD
/// </summary>
/// <param name="SQLObject"></param>
/// <param name="paramerts"></param>
/// <returns></returns>
private OleDbCommand InitCommand(string SQLObject, params OleDbParameter[] paramerts)
{
OleDbCommand cmd = new OleDbCommand(SQLObject,cn);
foreach(OleDbParameter pt in paramerts)//往Command里添加参数
{
cmd.Parameters.Add(pt);
}
return cmd;
}
private OleDbCommand InitCommandProc(string SQLObject, OleDbParameter[] paramerts)
{
OleDbCommand cmd = new OleDbCommand(SQLObject,cn);
foreach(OleDbParameter pt in paramerts)//往Command里添加参数
{
cmd.Parameters.Add(pt);
}
OleDbParameter pa= new OleDbParameter("ReturnValue",OleDbType.Integer,6,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null);//添加参数映射返回值
cmd.Parameters.Add(pa);
return cmd;
}
#endregion
public DataTable FillDataTable(string SQLObject,params OleDbParameter[] paramerts)
{
DataTable ds = new DataTable();
OleDbCommand cmd = InitCommand(SQLObject,paramerts);
cmd.CommandType=CommandType.Text;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
}