学步园

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
	
}