SqlServer通用数据处理类

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CRMWin
{

    public static class DBHelper
    {
        //数据库连接属性
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString ="数据库连接字符串";//一般从web.config里读取(App.config)
                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;
            }
        }

        /// <summary>
        /// 执行带参存储过程
        /// </summary>
        public static void ExecProc(string strProName, SqlParameter[] para)
        {
            connection.Open();
            try
            {
                SqlCommand sqlcmd = connection.CreateCommand();
                sqlcmd.CommandText = strProName;
                sqlcmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter paras in para)
                {
                    sqlcmd.Parameters.Add(paras);
                }
                sqlcmd.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 根据sql语句执行非查询操作
        /// </summary>
        public static bool ExecNoQuery(string sql)
        {

            try
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                return true;
            }
            catch
            {
                return false;
            }

        }
        /// <summary>
        /// 根据sql语句获得一个DataTable
        /// </summary>
        public static DataTable ExecQuery(string Sql)
        {
            connection.Open();
            SqlDataAdapter da = new SqlDataAdapter(Sql, connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
            connection.Close();

        }
        /// <summary>
        /// 执行无参SQL语句
        /// </summary>
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 执行带参SQL语句
        /// </summary>
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 执行无参SQL语句，并返回执行记录数
        /// </summary>
        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行有参SQL语句，并返回执行记录数
        /// </summary>
        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行无参SQL语句，并返回SqlDataReader
        /// </summary>
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 执行有参SQL语句，并返回SqlDataReader
        /// </summary>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 根据sql语句获得一个单值字符串
        /// </summary>
        public static string ReturnStringScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            try
            {
                string result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return "0";
            }
            connection.Close();
        }
        /// <summary>
        /// 执行事务处理SQL
        /// </summary>
        public static bool ExecTSQL(string[] sqls)
        {
            connection.Open();
            SqlTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sqls.Length; i++)
                {
                    if (sqls[i] == "" || sqls[i] == null)
                    {
                        continue;
                    }
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.Transaction = trans;
                    cmd.CommandText = sqls[i];
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return true;
            }
            catch
            {

                trans.Rollback();
                return false;
            }
            finally
            {
                trans = null;
                connection.Close();
            }
        }

    }

}