1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4using System.Configuration;
  5//该源码首发自www.51aspx.com(５1ａｓｐｘ．ｃｏｍ)
  6
  7namespace NBBS.DALHelper
  8...{
  9    /**//// <summary>
 10    /// 数据访问层辅助类——SQLServer
 11    /// 密封类，不可继承
 12    /// 仅包含静态方法，不需实例化直接使用（也可使用Singleton模式）
 13    /// </summary>
 14    public sealed class SQLServerHelper
 15    ...{
 16        /**//// <summary>
 17        /// 用于连接SQLServer数据库的连接字符串，存于Web.config中
 18        /// </summary>
 19        private static readonly string _sqlConnectionString = ConfigurationManager.AppSettings["SQLServerConnectionString"];
 20
 21        /**//// <summary>
 22        /// 执行SQL命令，不返回任何值
 23        /// </summary>
 24        /// <param name="sql">SQL命令</param>
 25        public static void ExecuteSQLNonQurey(string sql)
 26        ...{
 27            SqlConnection connection = new SqlConnection(_sqlConnectionString);
 28            SqlCommand command = new SqlCommand(sql,connection);
 29            connection.Open();
 30            command.ExecuteNonQuery();
 31            connection.Close();
 32        }
 33
 34        /**//// <summary>
 35        /// 执行SQL命令，并返回SqlDataReader
 36        /// </summary>
 37        /// <param name="sql">SQL命令</param>
 38        /// <returns>包含查询结果的SqlDataReader</returns>
 39        public static SqlDataReader ExecuteSQLReader(string sql)
 40        ...{
 41            SqlConnection connection = new SqlConnection(_sqlConnectionString);
 42            SqlCommand command = new SqlCommand(sql, connection);
 43            connection.Open();
 44            SqlDataReader sqlReader = command.ExecuteReader();
 45            //connection.Close();
 46
 47            return sqlReader;
 48        }
 49
 50        /**//// <summary>
 51        /// 执行存储过程，不返回任何值
 52        /// </summary>
 53        /// <param name="storedProcedureName">存储过程名</param>
 54        /// <param name="parameters">参数</param>
 55        public static void ExecuteProcedureNonQurey(string storedProcedureName,IDataParameter[] parameters)
 56        ...{
 57            SqlConnection connection = new SqlConnection(_sqlConnectionString);
 58            SqlCommand command = new SqlCommand(storedProcedureName,connection);
 59            command.CommandType = CommandType.StoredProcedure;
 60            if (parameters != null)
 61            ...{
 62                foreach (SqlParameter parameter in parameters)
 63                ...{
 64                    command.Parameters.Add(parameter);
 65                }
 66            }
 67            connection.Open();
 68            command.ExecuteNonQuery();
 69            connection.Close();
 70        }
 71
 72        /**//// <summary>
 73        /// 执行存储，并返回SqlDataReader
 74        /// </summary>
 75        /// <param name="storedProcedureName">存储过程名</param>
 76        /// <param name="parameters">参数</param>
 77        /// <returns>包含查询结果的SqlDataReader</returns>
 78        public static SqlDataReader ExecuteProcedureReader(string storedProcedureName,IDataParameter[] parameters)
 79        ...{
 80            SqlConnection connection = new SqlConnection(_sqlConnectionString);
 81            SqlCommand command = new SqlCommand(storedProcedureName,connection);
 82            command.CommandType = CommandType.StoredProcedure;
 83            if (parameters != null)
 84            ...{
 85                foreach (SqlParameter parameter in parameters)
 86                ...{
 87                    command.Parameters.Add(parameter);
 88                }
 89            }
 90            connection.Open();
 91            SqlDataReader sqlReader = command.ExecuteReader();
 92            //connection.Close();
 93
 94            return sqlReader;
 95        }
 96
 97        /**//// <summary>
 98        /// 为可空字符串字段赋值
 99        /// 如果为null则赋予DBNull;
100        /// </summary>
101        /// <param name="fieldValue">字段值</param>
102        /// <param name="parameter">参数</param>
103        public static void SetNullableString(object fieldValue, SqlParameter parameter)
104        ...{
105            if (fieldValue == null)
106            ...{
107                parameter.Value = DBNull.Value;
108            }
109            else
110            ...{
111                parameter.Value = fieldValue;
112            }
113        }
114
115        /**//// <summary>
116        /// 获得可空字段的值
117        /// 如果为DBNull则返回null
118        /// </summary>
119        /// <param name="fieldValue">字段值</param>
120        /// <returns>返回值</returns>
121        public static object GetNullableValue(object fieldValue)
122        ...{
123            if (fieldValue == DBNull.Value)
124            ...{
125                return null;
126            }
127            else
128            ...{
129                return fieldValue;
130            }
131        }
132    }
133}
134