通用数据库连接执行类（SQL）
using System;
using System.Data;
using System.Data.SqlClient;
namespace Public
{
///<summary>
/// CustomADO 数据连接执行类。
///</summary>
public class CustomADO
{
#region定义或创建类私有变量或对象
private string _connstr; //连接字符串存储变量
private string _procedure; //存储过程名或数据命令字符串变量
private bool _isprocedure; //是否使用存储过程
private SqlConnection _conn; //连接对象
private SqlCommand _comm; //数据命令对象
private SqlDataReader _dr; //定义数据读取器
private DataSet _ds; //定义数据存储器
#endregion
#region构造函数
///<summary>
///构造函数重载1
///</summary>
public CustomADO()
{
_connstr = null;
_procedure = null;
_isprocedure = false;
_conn = new SqlConnection();
_comm = new SqlCommand();
}
///<summary>
///构造函数重载2
///</summary>
///<param name="connstring">数据库连接字符串</param>
///<param name="procedure">存储过程或SQL命令</param>
///<param name="isprocedure">是否使用存储过程</param>
public CustomADO(string connstring,string procedure,bool isprocedure)
{
_connstr = connstring;
_procedure = procedure;
_isprocedure = isprocedure;
_conn = new SqlConnection();
_comm = new SqlCommand();
}
#endregion
#region属性
///<summary>
///设置或获取数据库连接字符串
///</summary>
public string ConnString
{
get
{
return _connstr;
}
set
{
_connstr = value;
_conn.ConnectionString = _connstr;
_comm.Connection = _conn;
}
}
///<summary>
///设置或获取存储过程名或SQL命令字符串
///</summary>
public string Procedure
{
get
{
return _procedure;
}
set
{
_procedure = value;
_comm.CommandText = _procedure;
}
}
///<summary>
///设置是否存储过程属性
///</summary>
public bool IsProcedure
{
set
{
_isprocedure = value;
if (_isprocedure)
{
_comm.CommandType = CommandType.StoredProcedure;
}
else
{
_comm.CommandType = CommandType.Text;
}
}
}
#endregion
#region方法
///<summary>
///打开连接
///</summary>
public void OpenConnection()
{
_conn.Open();
}
///<summary>
///关闭连接和数据读取器并清空数据集合
///</summary>
public void CloseConnection()
{
_conn.Close();
}
///<summary>
///销毁连接对象和方法
///</summary>
public void Dispost()
{
if (_ds != null)
{
_ds.Clear();
}
if (_dr!=null)
{
_dr.Close();
}
_comm.Dispose();
_conn.Close();
_conn.Dispose();
}
///<summary>
///添加参数重载1
///</summary>
///<param name="dbtype">参数类型</param>
///<param name="parametername">参数名</param>
///<param name="pvalue">参数值</param>
public void AddParameter(DbType dbtype,string parametername,object pvalue)
{
SqlParameter p = new SqlParameter();
p.DbType = dbtype;
p.ParameterName = parametername;
p.Value = pvalue;
_comm.Parameters.Add(p);
}
///<summary>
///添加参数重载2
///</summary>
///<param name="dbtype">参数类型</param>
///<param name="parametername">参数名</param>
///<param name="pvalue">参数值</param>
///<param name="parameterdirection">参数类型</param>
public void AddParameter(DbType dbtype,string parametername,object pvalue,ParameterDirection parameterdirection)
{
SqlParameter p = new SqlParameter();
p.DbType = dbtype;
p.ParameterName = parametername;
p.Value = pvalue;
p.Direction = parameterdirection;
_comm.Parameters.Add(p);
}
///<summary>
///添加返回参数
///</summary>
///<param name="dbtype">DbType参数类型</param>
///<param name="parametername">参数名</param>
public void AddParameterReturnValue(DbType dbtype,string parametername)
{
SqlParameter p = new SqlParameter();
p.DbType = dbtype;
p.ParameterName = parametername;
p.Direction = ParameterDirection.ReturnValue;
_comm.Parameters.Add(p);
}
///<summary>
///返回DataSet对象
///</summary>
///<returns>执行数据命令后返回的DataSet对象</returns>
public DataSet ExecuteDataSet()
{
_ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = _comm;
da.Fill(_ds);
return _ds;
}
///<summary>
///返回DataReader对象 重载1
///</summary>
///<returns>数据读取器</returns>
public SqlDataReader ExecuteDataReader()
{
_dr = _comm.ExecuteReader();
return _dr;
}
///<summary>
///返回DataReader对象 重载2
///</summary>
///<param name="behavior">CommandBehavior参数,对参数结果的数据库影响说明</param>
///<returns>数据读取器</returns>
public SqlDataReader ExecuteDataReader(CommandBehavior behavior)
{
_dr = _comm.ExecuteReader(behavior);
return _dr;
}
///<summary>
///执行数据命令并返回影响行数
///</summary>
public int ExecuteNonQuery()
{
return _comm.ExecuteNonQuery();
}
///<summary>
///通过参数名获得参数值
///</summary>
///<param name="parametername">参数名</param>
///<returns>返回参数值</returns>
public object GetParameterValue(string parametername)
{
object returnvalue;
returnvalue = _comm.Parameters[parametername].Value;
return returnvalue;
}
#endregion
}
}
