处女作代码 o(∩_∩)o...哈哈!
使用DBHelper完成数据层的访问 及 执行
public class DBHelper
{
private static string connString;
static DBHelper()
{
connString = ConfigurationManager.ConnectionStrings["MySchoolConnectionString"].ToString();
}
public SqlConnection GetConnection(bool hasOpen)
{
SqlConnection conn = new SqlConnection(connString);
if (hasOpen)
conn.Open();
return conn;
}
public DataSet GetDataSet(string sql, CommandType cmdType, string dsName)
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(connString);
SqlDataAdapter objAdapter = new SqlDataAdapter(sql, conn);
objAdapter.SelectCommand.CommandType = cmdType;
objAdapter.Fill(ds, dsName);
conn.Close();
conn.Dispose();
return ds;
}
public SqlDataReader GetReader(string sql, CommandType cmdType, params SqlParameter[] sqlParams)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = cmdType;
cmd.CommandText = sql;
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
SqlConnection conn = GetConnection(true);
cmd.Connection = conn;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public int ExecuteSql(string sql, CommandType cmdType, params SqlParameter[] sqlParams)
{
int ret = 0;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = cmdType;
cmd.CommandText = sql;
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
SqlConnection conn = GetConnection(true);
cmd.Connection = conn;
try
{
ret = (int)cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
return ret;
}
}
调用DBHelper:
public class GradeService
{
#region Private Members
//从配置文件中读取数据库连接字符串
private readonly string connString = ConfigurationManager.ConnectionStrings["MySchoolConnectionString"].ToString();
private readonly string dboOwner = ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();
#endregion
#region Public Methods
/// <summary>
/// 获得全部年级信息
/// </summary>
/// <returns>年级数据集</returns>
public DataSet GetAllGrades()
{
DBHelper dbHelper = new DBHelper();
return dbHelper.GetDataSet(dboOwner+".usp_SelectGradesAll",CommandType.StoredProcedure,"grade");
}
/// <summary>
/// 根据年级名称获得年级ID
/// </summary>
/// <param name="gradeName">年级名称</param>
/// <returns>年级ID</returns>
public int GetGradeIDByGradeName(string gradeName)
{
int number = 0;
DBHelper dbHelper = new DBHelper();
SqlParameter sqlParams = new SqlParameter("@GradeName", SqlDbType.NVarChar, 50);
sqlParams.Value = gradeName;
SqlDataReader objReader =dbHelper.GetReader(dboOwner + ".usp_SelectGradeByGradeName", CommandType.StoredProcedure, sqlParams);
if (objReader.Read())
number = Convert.ToInt32(objReader["GradeID"]);
objReader.Close();
objReader.Dispose();
return number;
}
/// <summary>
/// 新增年级
/// </summary>
public void AddGrade(DataSet dsGrade)
{
DBHelper dbHelper = new DBHelper();
SqlParameter[] sqlParams= new SqlParameter[1];
sqlParams[0] = new SqlParameter("@GradeName", SqlDbType.NVarChar, 50);
//sqlParams[1] = new SqlParameter("@GradeID", SqlDbType.Int);
sqlParams[0].Value = dsGrade.Tables["Grade"].Rows[0]["GradeName"];
//sqlParams[1].Value = 0;
dbHelper.ExecuteSql(dboOwner + ".usp_SelectGradeByGradeName", CommandType.StoredProcedure, sqlParams);
}
#endregion
}
联系客服