在ASP.NET中写一个数据层基类-----DbObject
public abstract class DbObject
{
protected SqlConnection Connection;
private string connectionString;
//定义两个构造函数
public DbObject()
{}
public DbObject(string connString)
{
connectionString =connString;
Connection = new SqlConnection(ConnectionString);
}
protected string ConnectionString
{
get {return connectionString;}
}
//根据指定的存储过程名称和参数生成对应的SQL命令对象。
private SqlCommand BuilQueryCommand(string storedName,SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand();
command.Connection = this.Connection;
command.CommandText ="["+storedName.Trim()+"]";
command.CommandType = CommandType.StoredProcedure;
if(parameters!=null)
{
foreach(SqlParameter p in parameters)
command.parameters.add(p);
}
return command;
}
//执行不返回结果的存储过程
protected int RunProcedure(string storedProcName,SqlParameter[] parameters,out int rowsAffected)
{
int result;
SqlCommand cmd = BuildQueryCommand(storedProcName,parameters);
cmd.parameters.add(
new SqlParameter(
"@RETURN_VALUE", //参数名
SqlDbType.Int, //参数类型
4, //参数长度
ParameterDirection.ReturnValue,//参数方向
false,//是否可以为空
((system.byte)(0)),//精度
((system.byte)(0)),//小数位数
"",//源列的名称
DataRowVersion.Current,//行版本
null//参数值。
));
Connection.open();
rowsAffected = cmd.ExecuteNonQuery();
result =(int)(cmd.Parameters["@Return_value"].value);
Connection.close();
return result;
}
//执行返回结果的存储过程,最后返回一个sqldatareader对象.
protected SqlDataReader RunProcedure(string storedProcName,SqlParameter[] parameters)
{
SqlDataReader reader;
SqlCommand cmd=BuildQueryCommand(storedProcName,parameters);
Connection.open();
reader=cmd.ExecuteReader(CommandBehavior.closeConnection);
return reader;
}
}
{
protected SqlConnection Connection;
private string connectionString;
//定义两个构造函数
public DbObject()
{}
public DbObject(string connString)
{
connectionString =connString;
Connection = new SqlConnection(ConnectionString);
}
protected string ConnectionString
{
get {return connectionString;}
}
//根据指定的存储过程名称和参数生成对应的SQL命令对象。
private SqlCommand BuilQueryCommand(string storedName,SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand();
command.Connection = this.Connection;
command.CommandText ="["+storedName.Trim()+"]";
command.CommandType = CommandType.StoredProcedure;
if(parameters!=null)
{
foreach(SqlParameter p in parameters)
command.parameters.add(p);
}
return command;
}
//执行不返回结果的存储过程
protected int RunProcedure(string storedProcName,SqlParameter[] parameters,out int rowsAffected)
{
int result;
SqlCommand cmd = BuildQueryCommand(storedProcName,parameters);
cmd.parameters.add(
new SqlParameter(
"@RETURN_VALUE", //参数名
SqlDbType.Int, //参数类型
4, //参数长度
ParameterDirection.ReturnValue,//参数方向
false,//是否可以为空
((system.byte)(0)),//精度
((system.byte)(0)),//小数位数
"",//源列的名称
DataRowVersion.Current,//行版本
null//参数值。
));
Connection.open();
rowsAffected = cmd.ExecuteNonQuery();
result =(int)(cmd.Parameters["@Return_value"].value);
Connection.close();
return result;
}
//执行返回结果的存储过程,最后返回一个sqldatareader对象.
protected SqlDataReader RunProcedure(string storedProcName,SqlParameter[] parameters)
{
SqlDataReader reader;
SqlCommand cmd=BuildQueryCommand(storedProcName,parameters);
Connection.open();
reader=cmd.ExecuteReader(CommandBehavior.closeConnection);
return reader;
}
}