sqlHelper ---转载

//引入命名空间
using System.Data.SqlClient;

/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
    private static readonly string sqlcon = ConfigurationManager.ConnectionStrings["enterpriseCon"].ConnectionString;

    //建立连接方法
    public SqlConnection createCon()
    {
        SqlConnection con = new SqlConnection(sqlcon);
        if (con.State != ConnectionState.Open)
            con.Open();
        return con;
    }
    //准备command命令
    public static void prepareCommand(SqlConnection con, SqlCommand cmd, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] para)
    {
        //如果数据库连接没有打开  则打开连接
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }
        cmd.Connection = con;
        cmd.CommandText = cmdText;
        if (trans != null)//如果trans存在的话 执行以下命令
        {
            cmd.Transaction = trans;
        }
        cmd.CommandText = cmdText;
        if (para != null)//如果para不为空 遍历一遍para
        {
            foreach (SqlParameter pa in para)
            {
                cmd.Parameters.Add(pa);
            }
        }
    }

    //从数据库中读取数据源 返回一个SqlDataReader对象
    public static SqlDataReader executeReader(string cmdText, CommandType cmdType, SqlParameter[] para)
    {
        //定义连接
        SqlConnection con = new SqlConnection(sqlcon);
        //捕获可能发生的异常
        try
        {
            SqlCommand cmd = new SqlCommand();
            prepareCommand(con, cmd, null, cmdType, cmdText, para);

            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return sdr;
        }
        catch (Exception)
        {
            throw;
        }
    }

    //更新数据库方法（增加，删除）
    public static int executeNonQuery(string cmdText, CommandType cmdType, SqlParameter[] para)
    {
        using (SqlConnection con = new SqlConnection(sqlcon))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                prepareCommand(con, cmd, null, cmdType, cmdText, para);
                int result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }

    //取得数据库中一行的值
    public static string executeScalar(string cmdText, CommandType cmdType, SqlParameter[] para)
    {
        using (SqlConnection con = new SqlConnection(sqlcon))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                prepareCommand(con, cmd, null, cmdType, cmdText, para);
                string result = Convert.ToString(cmd.ExecuteScalar());
                cmd.Parameters.Clear();
                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }
    }

    //DataSet
    public static DataSet executeDataAdapter(string cmdText,CommandType cmdType,SqlParameter[] para) {
        using (SqlConnection con=new SqlConnection(sqlcon))
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                prepareCommand(con, cmd, null, cmdType, cmdText, para);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
        }    
    }
}