SqlHelper

SQLHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;



namespace DBUtility
{
    /// <summary>
    /// 数据库通用
    /// </summary>
    public class SQLHelper
    {
        //封装数据库链接字符串
        private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();
        #region 格式化sql语句的各种方法

        /// <summary>
        /// 增加，删除，改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql,conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string erroInfo = "调用public static int Update(string sql)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo); 
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 返回单一查询结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string erroInfo = "调用public static object GetSIngleResult(string sql)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 获取一个结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                string erroInfo = "调用public static object SqlDataReader(string sql)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);//创建数据适配器
            DataSet ds = new DataSet(); //创建内存数据集

            try
            {
                conn.Open();
                da.Fill(ds);//使用数据适配器填充数据集
                return ds;
            }
            catch (Exception ex)
            {
                string erroInfo = "调用 public static DataSet GetDataSet(string sql)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        #region 带参数的
        public static int Update(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);//数组形式添加
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string erroInfo = "调用public static int Update(string sql,SqlParameter[] param)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        public static object GetSingleResult(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string erroInfo = "调用public static object GetSingleResult(string sql, SqlParameter[] param)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        public static SqlDataReader GetReader(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                string erroInfo = "调用public static SqlDataReader GetReader(string sql, SqlParameter[] param)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
        }
        /// <summary>
        /// 启用事务提交多条带参数的sql语句
        /// </summary>
        /// <param name="mainSql">主表sql语句</param>
        /// <param name="mainParam">主表sql语句参数</param>
        /// <param name="detialSql">明细表sql语句</param>
        /// <param name="detailParam">明细表sql语句参数</param>
        /// <returns>返回事务是否执行成功</returns>
        public static bool UpdateByTran(string mainSql,SqlParameter[] mainParam,string detialSql,List<SqlParameter[]> detailParam)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开始事务
                if (mainSql!=null&&mainSql.Length !=0)
                {
                    cmd.CommandText = mainSql;
                    cmd.Parameters.AddRange(mainParam);
                    cmd.ExecuteNonQuery();
                }
                foreach (SqlParameter[] param in detailParam)
                {
                    cmd.CommandText = detialSql;
                    cmd.Parameters.Clear();//清除以前的参数
                    cmd.Parameters.AddRange(param);
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction !=null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                string erroInfo = "调用public static bool UpdateByTran(string mainSql,SqlParameter[] mainParam,string detialSql," +
                    "List<SqlParameter[]> detailParam)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
            finally
            {
                if (cmd.Transaction !=null)
                {
                    cmd.Transaction = null;//清空事务
                }
                conn.Close();
            }
        }
        #endregion 

        #region 存储过程
        public static int UpdateByProdecure(string spName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(spName, conn);
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;//声明当前操作是存储过程
                cmd.Parameters.AddRange(param);//数组形式添加
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string erroInfo = "调用public static int UpdateByProdecure(string sql,SqlParameter[] param)发生错误：" + ex.Message;
                //将异常保存到日志
                WriteLog(erroInfo);
                throw new Exception(erroInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 其他方法
        private static void WriteLog(string log)
        {
            FileStream fs = new FileStream("sqlhelper.log", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(DateTime.Now.ToString() + "  " + log);
        }
        #endregion
    }
}