我之前有过的ASP.NET数据层访问方法

今天在博客园开博，希望能够在这个开放的平台上学到更多，也希望自己能够经常抽出时间增加博客数量，在写博客的同时思考程序代码，思考编程思想，同时沉淀技术.

 今天把我之前做三层Web开发的数据层访问代码拿出来讨论：大家可以在阅读代码后留下自己的看法

/*********************************
 * 类名：DBHelper
 * 功能描述：提供数据访问基础操作
 * ******************************/
namespace kDAL
{
    public static class DBHelper
    {
        

 private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
       代码 /// <summary>
        /// 执行返回int的SQL语句或存储过程
        /// </summary>
        /// <param name="sSqlOrProc">SQL语句或存储过程名</param>
        /// <param name="nType">0-存储过程,1-SQL语句</param>
        /// <returns>int</returns>
        public static int ExecuteCommand(string sSqlOrProc,int nType)
        {
            SqlCommand cmd = new SqlCommand(sSqlOrProc, Connection);
            if (nType == 0)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            return cmd.ExecuteNonQuery();
        }

 

/// <summary> /// 执行返回int的SQL语句或存储过程 /// </summary> /// <param name="sSqlOrProc">SQL语句或存储过程名</param> /// <param name="nType">0-存储过程,1-SQL语句</param> /// <param name="values">存储过程参数列表</param> /// <returns>int</returns> public static int ExecuteCommand(string sSqlOrProc, int nType, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sSqlOrProc, Connection); if (nType == 0) { cmd.CommandType = CommandType.StoredProcedure; } cmd.Parameters.AddRange(values); SqlParameter returnValue = cmd.Parameters.Add("returnValue", SqlDbType.Int, 4); returnValue.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); return Convert.ToInt32(returnValue.Value.ToString()); } /// <summary> /// 执行返回int（output）的SQL语句或存储过程 /// </summary> /// <param name="sSqlOrProc">SQL语句或存储过程名</</param> /// <param name="nType">0-存储过程,1-SQL语句</param> /// <param name="nOutput">output参数</param> /// <param name="values">存储过程参数列表</param> /// <returns>int（output）</returns> public static int ExecuteCommand1(string sSqlOrProc, int nType,string sOutput, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sSqlOrProc, Connection); if (nType == 0) { cmd.CommandType = CommandType.StoredProcedure; } cmd.Parameters.AddRange(values); SqlParameter output = cmd.Parameters.Add(sOutput, SqlDbType.BigInt, 8); output.Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); return Convert.ToInt32(output.Value.ToString()); } /// <summary> /// 执行无参SQL语句，并返回执行行数 /// </summary> public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 执行有参SQL语句，并返回执行行数 /// </summary> public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 执行有参SQL语句或者存储过程，并返回执行行数 存储过程中用select @@表示 /// 0为存储过程 1为sql语句 /// </summary> /// <param name="sql"></param> /// <param name="nType"></param> /// <param name="values"></param> /// <returns></returns> public static int GetScalar(string sql,int nType, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); if (nType == 0) { cmd.CommandType = CommandType.StoredProcedure; } if (nType == 1) { cmd.CommandType = CommandType.Text; } cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } } }