SQLHelper类

using System;using System.Data;using System.Configuration;using System.Web;using System.Collections;using System.Data.SqlClient;public class SqlHelper{    #region 配置连接    private static SqlConnection connection;    static SqlConnection Connection    {        get        {            string connectionString = ConfigurationManager.ConnectionStrings["数据库连接名"].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;        }    }    #endregion    #region 设置SqlCommand    public static SqlCommand GetStoredProcCommond(string storedProcedure)    {        SqlCommand cmd = Connection.CreateCommand();        cmd.CommandText = storedProcedure;        cmd.CommandType = CommandType.StoredProcedure;        return cmd;    }    public static SqlCommand GetSqlStringCommond(string sqlQuery)    {        SqlCommand cmd = Connection.CreateCommand();        cmd.CommandText = sqlQuery;        cmd.CommandType = CommandType.Text;        return cmd;    }    #endregion    #region 增加参数    public static SqlCommand AddParameterCollection(SqlCommand cmd, SqlParameterCollection SqlParameterCollection)    {        foreach (SqlParameter SqlParameter in SqlParameterCollection)        {            cmd.Parameters.Add(SqlParameter);        }        return cmd;    }    public static void AddOutParameter(SqlCommand cmd, string parameterName, DbType dbType, int size)    {        SqlParameter SqlParameter = cmd.CreateParameter();        SqlParameter.DbType = dbType;        SqlParameter.ParameterName = parameterName;        SqlParameter.Size = size;        SqlParameter.Direction = ParameterDirection.Output;        cmd.Parameters.Add(SqlParameter);    }    public static void AddInParameter(SqlCommand cmd, string parameterName, DbType dbType, object value)    {        SqlParameter SqlParameter = cmd.CreateParameter();        SqlParameter.DbType = dbType;        SqlParameter.ParameterName = parameterName;        SqlParameter.Value = value;        SqlParameter.Direction = ParameterDirection.Input;        cmd.Parameters.Add(SqlParameter);    }    public static void AddReturnParameter(SqlCommand cmd, string parameterName, DbType dbType)    {        SqlParameter SqlParameter = cmd.CreateParameter();        SqlParameter.DbType = dbType;        SqlParameter.ParameterName = parameterName;        SqlParameter.Direction = ParameterDirection.ReturnValue;        cmd.Parameters.Add(SqlParameter);    }    public static SqlParameter GetParameter(SqlCommand cmd, string parameterName)    {        return cmd.Parameters[parameterName];    }    #endregion    #region 执行    public static DataSet ExecuteDataSet(SqlCommand cmd)    {        SqlDataAdapter da = new SqlDataAdapter();        da.SelectCommand = cmd;        DataSet ds = new DataSet();        da.Fill(ds);        cmd.Dispose();        return ds;    }    public static DataTable ExecuteDataTable(SqlCommand cmd)    {        SqlDataAdapter da= new SqlDataAdapter();        da.SelectCommand = cmd;        DataTable dataTable = new DataTable();         da.Fill (dataTable);        cmd.Dispose();        da.Dispose();        return dataTable;    }    public static SqlDataReader ExecuteReader(SqlCommand cmd)    {        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);        cmd.Dispose();        return reader;    }    public static int ExecuteNonQuery(SqlCommand cmd)    {        int ret = cmd.ExecuteNonQuery();        cmd.Connection.Close();        cmd.Dispose();        return ret;    }    public static object ExecuteScalar(SqlCommand cmd)    {        object ret = cmd.ExecuteScalar();        cmd.Connection.Close();        cmd.Dispose();        return ret;    }    #endregion}