使用DBHelper

处女作代码　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>
        /// 根据年级名称获得年级ＩＤ
        /// </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
    }