using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/** <summary>
/// 数据访问层类,提供 数据库的连接以及sql语句的执行
/// </summary>
public class DB Access
{
private IDbCommand cmd = new SqlCommand();
private string strConnectionString = "";
private bool handleErrors = false;
private string strLastError = "";
public DB Access()
{
ConnectionStringSettings objConnectionStringSettings = ConfigurationManager.ConnectionStrings["conn"];
strConnectionString = objConnectionStringSettings.ConnectionString;
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = strConnectionString;
cmd.Connection = cnn;
//cmd.CommandTimeout=
}
public void SetCmdType(CommandType type)
{
cmd.CommandType = type;
}
public string ConnectionString
{
get
{
return strConnectionString;
}
set
{
strConnectionString = value;
}
}
public bool HandleExceptions
{
get
{
return handleErrors;
}
set
{
handleErrors = value;
}
}
public string LastError
{
get
{
return strLastError;
}
}
/** <summary>
/// 执行一条SQL语句或者存储过程,返回一个IDataReader
/// 执行参数ExecuteReader(CommandBehavior.CloseConnection)
/// 如果关闭DataReader,则相关联的Connection也关闭
/// </summary>
/// <returns>返回一个IDataReader</returns>
public IDataReader ExecuteReader()
{
IDataReader reader = null;
try
{
this.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return reader;
}
/** <summary>
/// 执行一条SQL语句或者存储过程,返回一个IDataReader
/// 如果关闭DataReader,则相关联的Connection也关闭
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回一个IDataReader</returns>
public IDataReader ExecuteReader(string commandtext)
{
IDataReader reader = null;
try
{
cmd.CommandText = commandtext;
reader = this.ExecuteReader();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return reader;
}
/** <summary>
/// 执行一条SQL语句
/// </summary>
/// <returns>返回所影响的行数</returns>
public int ExecuteNonQuery()
{
int i = -1;
try
{
this.Open();
i = cmd.ExecuteNonQuery();
this.Close();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return i;
}
/** <summary>
/// 执行一条SQL语句或者存储过程,该语句由参数给出
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回所影响的行数</returns>
public int ExecuteNonQuery(string commandtext)
{
int i = -1;
try
{
cmd.CommandText = commandtext;
i = this.ExecuteNonQuery();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return i;
}
/** <summary>
/// 执行CMD对象,返回DataSet
/// </summary>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet()
{
SqlDataAdapter da = null;
DataSet ds = null;
try
{
da = new SqlDataAdapter();
da.SelectCommand = (SqlCommand)cmd;
ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return ds;
}
/** <summary>
/// 执行SQL或者存储过程,返回DataSet
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet(string commandtext)
{
DataSet ds = null;
try
{
cmd.CommandText = commandtext;
ds = this.ExecuteDataSet();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return ds;
}
/** <summary>
/// 执行CMD对象,返回结果的第一行,一般用来执行聚合函数
/// </summary>
/// <returns>返回查询结果的第一行</returns>
public object ExecuteScalar()
{
object obj = null;
try
{
this.Open();
obj = cmd.ExecuteScalar();
this.Close();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return obj;
}
/** <summary>
/// 执行SQL或者存储过程,返回结果的第一行,一般用来执行聚合函数
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回查询结果的第一行</returns>
public object ExecuteScalar(string commandtext)
{
object obj = null;
try
{
cmd.CommandText = commandtext;
obj = this.ExecuteScalar();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return obj;
}
/** <summary>
/// 对CMD对象添加Parameter
/// </summary>
/// <param name="paramname">Parameter名字,形如@Name</param>
/// <param name="paramvalue">Parameter值,如zhangyue</param>
public void AddParameter(string paramname, object paramvalue)
{
SqlParameter param = new SqlParameter(paramname, paramvalue);
cmd.Parameters.Add(param);
}
/** <summary>
/// 将一个Parameter对象添加到CMD对象中
/// </summary>
/// <param name="param">Parameter对象</param>
public void AddParameter(IDataParameter param)
{
cmd.Parameters.Add(param);
}
private void Open()
{
try
{
cmd.Connection.Open();
}
catch (Exception e)
{
if (handleErrors)
strLastError = "连接 数据库失败";
else
throw;
}
}
private void Close()
{
//if (cmd.Connection.State != "Closed")
cmd.Connection.Close();
}
public void Dispose()
{
cmd.Dispose();
}
}
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/** <summary>
/// 数据访问层类,提供 数据库的连接以及sql语句的执行
/// </summary>
public class DB Access
{
private IDbCommand cmd = new SqlCommand();
private string strConnectionString = "";
private bool handleErrors = false;
private string strLastError = "";
public DB Access()
{
ConnectionStringSettings objConnectionStringSettings = ConfigurationManager.ConnectionStrings["conn"];
strConnectionString = objConnectionStringSettings.ConnectionString;
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = strConnectionString;
cmd.Connection = cnn;
//cmd.CommandTimeout=
}
public void SetCmdType(CommandType type)
{
cmd.CommandType = type;
}
public string ConnectionString
{
get
{
return strConnectionString;
}
set
{
strConnectionString = value;
}
}
public bool HandleExceptions
{
get
{
return handleErrors;
}
set
{
handleErrors = value;
}
}
public string LastError
{
get
{
return strLastError;
}
}
/** <summary>
/// 执行一条SQL语句或者存储过程,返回一个IDataReader
/// 执行参数ExecuteReader(CommandBehavior.CloseConnection)
/// 如果关闭DataReader,则相关联的Connection也关闭
/// </summary>
/// <returns>返回一个IDataReader</returns>
public IDataReader ExecuteReader()
{
IDataReader reader = null;
try
{
this.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return reader;
}
/** <summary>
/// 执行一条SQL语句或者存储过程,返回一个IDataReader
/// 如果关闭DataReader,则相关联的Connection也关闭
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回一个IDataReader</returns>
public IDataReader ExecuteReader(string commandtext)
{
IDataReader reader = null;
try
{
cmd.CommandText = commandtext;
reader = this.ExecuteReader();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return reader;
}
/** <summary>
/// 执行一条SQL语句
/// </summary>
/// <returns>返回所影响的行数</returns>
public int ExecuteNonQuery()
{
int i = -1;
try
{
this.Open();
i = cmd.ExecuteNonQuery();
this.Close();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return i;
}
/** <summary>
/// 执行一条SQL语句或者存储过程,该语句由参数给出
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回所影响的行数</returns>
public int ExecuteNonQuery(string commandtext)
{
int i = -1;
try
{
cmd.CommandText = commandtext;
i = this.ExecuteNonQuery();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return i;
}
/** <summary>
/// 执行CMD对象,返回DataSet
/// </summary>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet()
{
SqlDataAdapter da = null;
DataSet ds = null;
try
{
da = new SqlDataAdapter();
da.SelectCommand = (SqlCommand)cmd;
ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return ds;
}
/** <summary>
/// 执行SQL或者存储过程,返回DataSet
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet(string commandtext)
{
DataSet ds = null;
try
{
cmd.CommandText = commandtext;
ds = this.ExecuteDataSet();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return ds;
}
/** <summary>
/// 执行CMD对象,返回结果的第一行,一般用来执行聚合函数
/// </summary>
/// <returns>返回查询结果的第一行</returns>
public object ExecuteScalar()
{
object obj = null;
try
{
this.Open();
obj = cmd.ExecuteScalar();
this.Close();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return obj;
}
/** <summary>
/// 执行SQL或者存储过程,返回结果的第一行,一般用来执行聚合函数
/// </summary>
/// <param name="commandtext">存储过程名或者SQL语句</param>
/// <returns>返回查询结果的第一行</returns>
public object ExecuteScalar(string commandtext)
{
object obj = null;
try
{
cmd.CommandText = commandtext;
obj = this.ExecuteScalar();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
catch
{
throw;
}
return obj;
}
/** <summary>
/// 对CMD对象添加Parameter
/// </summary>
/// <param name="paramname">Parameter名字,形如@Name</param>
/// <param name="paramvalue">Parameter值,如zhangyue</param>
public void AddParameter(string paramname, object paramvalue)
{
SqlParameter param = new SqlParameter(paramname, paramvalue);
cmd.Parameters.Add(param);
}
/** <summary>
/// 将一个Parameter对象添加到CMD对象中
/// </summary>
/// <param name="param">Parameter对象</param>
public void AddParameter(IDataParameter param)
{
cmd.Parameters.Add(param);
}
private void Open()
{
try
{
cmd.Connection.Open();
}
catch (Exception e)
{
if (handleErrors)
strLastError = "连接 数据库失败";
else
throw;
}
}
private void Close()
{
//if (cmd.Connection.State != "Closed")
cmd.Connection.Close();
}
public void Dispose()
{
cmd.Dispose();
}
}