数据库工具类

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DBHelper
{
    public class SqlHelper
    {
        private static string connectionstring =ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
		public static int ExecuteCommand(string sql)
        {
            using(SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
            }
        }

        public static int ExecuteCommand(string SQL, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = new SqlCommand(SQL, conn))
                {
                    cmd.Parameters.AddRange(values);
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
            }
        }

        public static string ReturnStringScalar(string SQL)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = new SqlCommand(SQL, conn))
                {
                    conn.Open();
                    string result = cmd.ExecuteScalar().ToString();
                    return result;
                }
            }
        }

        public static int GetScalar(string SQL)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = new SqlCommand(SQL, conn))
                {
                    conn.Open();
                    int result = Convert.ToInt32(cmd.ExecuteScalar());
                    return result;
                }
            }
        }


        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(values);
                    conn.Open();
                    int result = Convert.ToInt32(cmd.ExecuteScalar());
                    return result;
                }
            }
        }


        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddRange(values);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            } 
        }

        public static DataTable GetDataSet(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(values);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        public static bool ExecProc(string strProcName)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = strProcName;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }

        public static void ExecProc(string strProcName, SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = strProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static bool ExecTSQL(string[] sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    conn.Open();
                    for (int i = 0; i < sql.Length; i++)
                    {
                        if (sql[i] == "" || sql[i] == null) { continue; }
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.Transaction = trans;
                            cmd.CommandText = sql[i];
                            cmd.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception)
                {
                    trans.Rollback();
                    return false;
                }
            }
        }
    }
}