一个基类的写法(DBHelper)

今天上课做测验竟然忘记了连接数据库怎么写了。很大程度和自己的记忆有关，特意把这东西放上来，好让自己随时复习，过目。以后无论做如何深入的研究，基础是不能缺的。提醒自己，现在时刻，不能放松哦。这个是用codesmith生成的，有了辅助功能，给学懒了哦。忘记了基础，是不好的

专注，再专注

 

namespace CodeSmith.Helper
{
    public static class DBHelper
    {
        static string connectionString = ConfigurationManager.ConnectionStrings["DBHelplerDbConnection"].ConnectionString;

        public static string ConnecttionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }

        public static int ExecuteCommand(string safeSql)
        {
            return ExecuteCommand(safeSql, null);
        }

        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(ConnecttionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, sql, values);
                return cmd.ExecuteNonQuery();
            }
        }

        public static int GetIntScalar(string safeSql)
        {
            return GetIntScalar(safeSql, null);
        }

        public static int GetIntScalar(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(ConnecttionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, sql, values);
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        public static SqlDataReader GetReader(string safeSql)
        {
            return GetReader(safeSql,null);
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlConnection conn = new SqlConnection(ConnecttionString);
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, sql, values);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static DataTable GetDataSet(string safeSql)
        {
            return GetDataSet(safeSql,null);
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            using( SqlConnection conn = new SqlConnection(ConnecttionString) )
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, sql, values);

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];           
            }
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}