微软提供的Data Access Application Block中的SQLHelper类中封装了最常用的数据操作,各个使用者调用他而写的代码也有很大区别。
对于一个返回DataSet的方法我原来是这样写的:
public
DataSet GetDepartmentMemberList(
int
departmentID)
{
try
{
string sql = "OA_Department_GetDepartment_Members";
string conn = ConfigurationSettings.AppSettings["strConnection"];
SqlParameter[] p =
{
SqlHelper.MakeInParam("@departmentID",SqlDbType.Int,4,departmentID)
};
DataSet ds = SqlHelper.ExecuteDataset(conn,CommandType.StoredProcedure,sql,p);
return ds;
}
catch(System.Data.SqlClient.SqlException er)
{
throw new Exception(er.Message);
}
}
{
try
{
string sql = "OA_Department_GetDepartment_Members";
string conn = ConfigurationSettings.AppSettings["strConnection"];
SqlParameter[] p =
{
SqlHelper.MakeInParam("@departmentID",SqlDbType.Int,4,departmentID)
};
DataSet ds = SqlHelper.ExecuteDataset(conn,CommandType.StoredProcedure,sql,p);
return ds;
}
catch(System.Data.SqlClient.SqlException er)
{
throw new Exception(er.Message);
}
}
现在我是这样来写的:
//
连接字符串
private string _connectionString = ConfigurationSettings.AppSettings[ " strConnection " ];
public string ConnectionString
{
get {return this._connectionString;}
set {this._connectionString = value;}
}
public DataSet GetNewsToIndexPage( int NewsTypeID)
{
return GetDataSet("yzb_GetNewsToIndexPage",GetNewsToIndexPage_Parameters(NewsTypeID));
}
// 设置存储过程参数
private SqlParameter[] GetNewsToIndexPage_Parameters( int NewsTypeID)
{
SqlParameter[] p =
{
SqlHelper.MakeInParam("@NewsTypeID",SqlDbType.Int,4,NewsTypeID)
};
return p;
}
// 这里才真正调用SqlHelper
private DataSet GetDataSet( string sql, params SqlParameter[] p)
{
return SqlHelper.ExecuteDataset(ConnectionString,CommandType.StoredProcedure,sql,p);
}
private string _connectionString = ConfigurationSettings.AppSettings[ " strConnection " ];
public string ConnectionString
{
get {return this._connectionString;}
set {this._connectionString = value;}
}
public DataSet GetNewsToIndexPage( int NewsTypeID)
{
return GetDataSet("yzb_GetNewsToIndexPage",GetNewsToIndexPage_Parameters(NewsTypeID));
}
// 设置存储过程参数
private SqlParameter[] GetNewsToIndexPage_Parameters( int NewsTypeID)
{
SqlParameter[] p =
{
SqlHelper.MakeInParam("@NewsTypeID",SqlDbType.Int,4,NewsTypeID)
};
return p;
}
// 这里才真正调用SqlHelper
private DataSet GetDataSet( string sql, params SqlParameter[] p)
{
return SqlHelper.ExecuteDataset(ConnectionString,CommandType.StoredProcedure,sql,p);
}
代码更加灵活,更加安全了:P