using System; using System.Data; using System.Data.SqlClient;namespace Core { /// <summary> /// DbObject 的摘要说明。 /// </summary> public abstract class DbObject { protected SqlConnection Connection;//可继承的数据库链接 private string connectionString;//私有的连接字符串 public DbObject( string newConnectionString )//构造函数 { connectionString = newConnectionString;//将参数给上面声明的字符串 Connection = new SqlConnection( connectionString );//用字符串实例化链接 } /// <summary> /// Protected property that exposes the connection string /// to inheriting classes. Read-Only. /// </summary> protected string ConnectionString//只读的字符串属性 { get { return connectionString; } } /// <summary> /// Private routine allowed only by this base class, it automates the task /// of building a SqlCommand object designed to obtain a return value from /// the stored procedure. /// </summary> /// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param> /// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param> /// <returns>Newly instantiated SqlCommand instance</returns> private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)//私有方法,返回一个SqlCommand对像 { SqlCommand command = BuildQueryCommand( storedProcName, parameters ); //给命令添加一个参数 command.Parameters.Add( new SqlParameter ( "ReturnValue", SqlDbType.Int,//参数类型 4, /* 参数大小(长度)*/ ParameterDirection.ReturnValue,//参数表示诸如存储过程、内置函数或用户定义函数之类的操作的返回值 false, /* 是否可为空 */ 0, /* 获取或设置用来表示 Value 属性的最大位数。 */ 0, /* 获取或设置 Value 解析为的小数位数 */ string.Empty, DataRowVersion.Default, null )); return command;//返回命令 } /// <summary> /// Builds a SqlCommand designed to return a SqlDataReader, and not /// an actual integer value. /// </summary> /// <param name="storedProcName">Name of the stored procedure</param> /// <param name="parameters">Array of IDataParameter objects</param> /// <returns></returns> private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand( storedProcName, Connection );//用参数来实例化命令 command.CommandType = CommandType.StoredProcedure;//命令类型为存储过程 foreach (SqlParameter parameter in parameters)//在命令集合中循环 { command.Parameters.Add( parameter );//添加所有命令 } return command;//返回命令 } /// <summary> /// Runs a stored procedure, can only be called by those classes deriving /// from this base. It returns an integer indicating the return value of the /// stored procedure, and also returns the value of the RowsAffected aspect /// of the stored procedure that is returned by the ExecuteNonQuery method. /// </summary> /// <param name="storedProcName">Name of the stored procedure</param> /// <param name="parameters">Array of IDataParameter objects</param> /// <param name="rowsAffected">Number of rows affected by the stored procedure.</param> /// <returns>An integer indicating return value of the stored procedure</returns> protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )//方法,返回一个整形值 { int result;//声明一个整形的变量 Connection.Open();//打开链接 SqlCommand command = BuildIntCommand( storedProcName, parameters );//命令对像从BuildIntCommand方法的返回值中得到 rowsAffected = commaresultnd.ExecuteNonQuery();//执行命令,返回的行数赋给rowsAffected result = (int)command.Parameters["ReturnValue"].Value;//result等于命令参数ReturnValue的值 Connection.Close();//关闭链接 return result;//返回result } /// <summary> /// Will run a stored procedure, can only be called by those classes deriving /// from this base. It returns a SqlDataReader containing the result of the stored /// procedure. /// </summary> /// <param name="storedProcName">Name of the stored procedure</param> /// <param name="parameters">Array of parameters to be passed to the procedure</param> /// <returns>A newly instantiated SqlDataReader object</returns> protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )//方法,返回一个读取器 { SqlDataReader returnReader;//声明一个读取器 Connection.Open();//打开链接待站 SqlCommand command = BuildQueryCommand( storedProcName, parameters );//命令值从BuildQueryCommand方法的返回值中获得 command.CommandType = CommandType.StoredProcedure;//命令类型是存储过程 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);//执行命令将值给读取器 //Connection.Close(); return returnReader;//返回读取器 } /// <summary> /// Creates a DataSet by running the stored procedure and placing the results /// of the query/proc into the given tablename. /// </summary> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <param name="tableName"></param> /// <returns></returns> protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName ) { DataSet dataSet = new DataSet();//实例化一个dataSet Connection.Open();//打开链接 SqlDataAdapter sqlDA = new SqlDataAdapter();//实例化适配器 sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );//适配器的命令参数从BuildQueryCommand方法获得 sqlDA.Fill( dataSet, tableName );//将数据填充到dataSet,表名为tableName Connection.Close();//关闭链接 return dataSet;//返回dataSet } protected SqlDataAdapter RunProcedure(string storedProcName, IDataParameter[] parameters, int Sql ) {//方法返回数据适配器 // DataSet dataSet = new DataSet();//实例化dataSet Connection.Open();//打开链接 SqlDataAdapter sqlDA = new SqlDataAdapter();实例化适配器 sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );//适配器命令从BuildQueryCommand方法获得 // sqlDA.Fill( dataSet, tableName );//填充dataSet,表名为tableName Connection.Close();//关闭链接 return sqlDA;返回适配器 } /// <summary> /// Takes an -existing- dataset and fills the given table name with the results /// of the stored procedure. /// </summary> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <param name="dataSet"></param> /// <param name="tableName"></param> /// <returns></returns> protected void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName ) {//方法返回空 Connection.Open();//打开链接 SqlDataAdapter sqlDA = new SqlDataAdapter();//实例化适配器 sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );适配器命令从BuildIntCommand方法获得 sqlDA.Fill( dataSet, tableName );//填充dataSet,表名为tableName Connection.Close();//关闭链接 } } }
using System.Data;
using System.Data.SqlClient;namespace Core
{
public abstract class DbObject//声明是个虚类,只能用于继承
{
protected SqlConnection Connection;//声明SqlConnection对象,即连接对象
private string connectionString;//声明连接字符串 public DbObject( string newConnectionString )//构造函数用来生成连接对象
{
connectionString = newConnectionString;
Connection = new SqlConnection( connectionString );//出始化连接对象
}
protected string ConnectionString
{
get
{
return connectionString;//验证连接字符串的和法性
}
}
private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
//调用Sql命令,与下面这个函数重载
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
//该SQL命令是一个带参数的存储过程
command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null ));
//用该存储过程名和参数值出始化这个command对象
return command;
}
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, Connection );
command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
} return command;
} //下面的几个RunProcedure都是重载关系
protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
int result; Connection.Open();//打开连接
SqlCommand command = BuildIntCommand( storedProcName, parameters );
//出始化一个SqlCommand对象,该对象是一个SQl命令或是一个存储过程
rowsAffected = command.ExecuteNonQuery();//执行这个SQl命令(或存储过程),且返回受影响的行数
result = (int)command.Parameters["ReturnValue"].Value;//将存储过程的结果存在result中
Connection.Close();//关闭连接
return result;
}
protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
SqlDataReader returnReader;
//SqlDataReader是只读的,且只能向前,因此它比DataSet能更快地遍历一个数据集
Connection.Open();
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Connection.Close();
return returnReader;
}
protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
DataSet dataSet = new DataSet();
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
//将SelectCommand的查询结果,来填充DataSet
Connection.Close(); return dataSet;
}
protected SqlDataAdapter RunProcedure(string storedProcName, IDataParameter[] parameters, int Sql )
{
// DataSet dataSet = new DataSet();
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );
// sqlDA.Fill( dataSet, tableName );
Connection.Close(); return sqlDA;
}
protected void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )
{
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
Connection.Close();
}
}
}
using System.Data;
using System.Data.SqlClient;namespace Core
{
/// <summary>
/// DbObject 的摘要说明。
/// </summary>
public abstract class DbObject
{
protected SqlConnection Connection;//可继承的数据库链接
private string connectionString;//私有的连接字符串
public DbObject( string newConnectionString )//构造函数
{
connectionString = newConnectionString;//将参数给上面声明的字符串
Connection = new SqlConnection( connectionString );//用字符串实例化链接
} /// <summary>
/// Protected property that exposes the connection string
/// to inheriting classes. Read-Only.
/// </summary>
protected string ConnectionString//只读的字符串属性
{
get
{
return connectionString;
}
} /// <summary>
/// Private routine allowed only by this base class, it automates the task
/// of building a SqlCommand object designed to obtain a return value from
/// the stored procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>
/// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>
/// <returns>Newly instantiated SqlCommand instance</returns>
private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)//私有方法,返回一个SqlCommand对像
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
//给命令添加一个参数
command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,//参数类型
4, /* 参数大小(长度)*/
ParameterDirection.ReturnValue,//参数表示诸如存储过程、内置函数或用户定义函数之类的操作的返回值
false, /* 是否可为空 */
0, /* 获取或设置用来表示 Value 属性的最大位数。 */
0, /* 获取或设置 Value 解析为的小数位数 */
string.Empty,
DataRowVersion.Default,
null )); return command;//返回命令
}
/// <summary>
/// Builds a SqlCommand designed to return a SqlDataReader, and not
/// an actual integer value.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <returns></returns>
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, Connection );//用参数来实例化命令
command.CommandType = CommandType.StoredProcedure;//命令类型为存储过程 foreach (SqlParameter parameter in parameters)//在命令集合中循环
{
command.Parameters.Add( parameter );//添加所有命令
} return command;//返回命令 } /// <summary>
/// Runs a stored procedure, can only be called by those classes deriving
/// from this base. It returns an integer indicating the return value of the
/// stored procedure, and also returns the value of the RowsAffected aspect
/// of the stored procedure that is returned by the ExecuteNonQuery method.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <param name="rowsAffected">Number of rows affected by the stored procedure.</param>
/// <returns>An integer indicating return value of the stored procedure</returns>
protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )//方法,返回一个整形值
{
int result;//声明一个整形的变量 Connection.Open();//打开链接
SqlCommand command = BuildIntCommand( storedProcName, parameters );//命令对像从BuildIntCommand方法的返回值中得到
rowsAffected = commaresultnd.ExecuteNonQuery();//执行命令,返回的行数赋给rowsAffected
result = (int)command.Parameters["ReturnValue"].Value;//result等于命令参数ReturnValue的值
Connection.Close();//关闭链接
return result;//返回result
} /// <summary>
/// Will run a stored procedure, can only be called by those classes deriving
/// from this base. It returns a SqlDataReader containing the result of the stored
/// procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of parameters to be passed to the procedure</param>
/// <returns>A newly instantiated SqlDataReader object</returns>
protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )//方法,返回一个读取器
{
SqlDataReader returnReader;//声明一个读取器 Connection.Open();//打开链接待站
SqlCommand command = BuildQueryCommand( storedProcName, parameters );//命令值从BuildQueryCommand方法的返回值中获得
command.CommandType = CommandType.StoredProcedure;//命令类型是存储过程 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);//执行命令将值给读取器
//Connection.Close();
return returnReader;//返回读取器
} /// <summary>
/// Creates a DataSet by running the stored procedure and placing the results
/// of the query/proc into the given tablename.
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
DataSet dataSet = new DataSet();//实例化一个dataSet
Connection.Open();//打开链接
SqlDataAdapter sqlDA = new SqlDataAdapter();//实例化适配器
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );//适配器的命令参数从BuildQueryCommand方法获得
sqlDA.Fill( dataSet, tableName );//将数据填充到dataSet,表名为tableName
Connection.Close();//关闭链接 return dataSet;//返回dataSet
}
protected SqlDataAdapter RunProcedure(string storedProcName, IDataParameter[] parameters, int Sql )
{//方法返回数据适配器
// DataSet dataSet = new DataSet();//实例化dataSet
Connection.Open();//打开链接
SqlDataAdapter sqlDA = new SqlDataAdapter();实例化适配器
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );//适配器命令从BuildQueryCommand方法获得
// sqlDA.Fill( dataSet, tableName );//填充dataSet,表名为tableName
Connection.Close();//关闭链接 return sqlDA;返回适配器
}
/// <summary>
/// Takes an -existing- dataset and fills the given table name with the results
/// of the stored procedure.
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="dataSet"></param>
/// <param name="tableName"></param>
/// <returns></returns>
protected void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )
{//方法返回空
Connection.Open();//打开链接
SqlDataAdapter sqlDA = new SqlDataAdapter();//实例化适配器
sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );适配器命令从BuildIntCommand方法获得
sqlDA.Fill( dataSet, tableName );//填充dataSet,表名为tableName
Connection.Close();//关闭链接
}
}
}