NBDXSY/LeaRun.Application/LeaRun.Application.Web/Common/SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace LeaRun.Application.Web.Common
{
    public class SqlHelper
    {
        //连接字符串
        private static readonly string connStrN = ConfigurationManager.ConnectionStrings["BaseDb"].ConnectionString;

        //IP地址
        //private static String SqlServerId = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlServerId"), "WWKJ");
        ////用户名
        //private static String SqlUserId = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlUserId"), "WWKJ");
        ////用户密码
        //private static String PassWord = Wujian_Jm.C_DesDecrypt(GetConfigValue("PassWord"), "WWKJ");
        //数据库名
        //private static String DbaseName = GetConfigValue("DbaseName");

        //拼接链接字符串
       // private static r string connStrN = "data source=" + SqlServerId + ";initial catalog=" + DbaseName + ";user id=" + SqlUserId + ";password=" + PassWord + ";";

        //1.执行insert/delete/update的方法
        #region 配置文件字段的读取函数
        public static string GetConfigValue(string key)
        {
            string value = "";
            try
            {
                String Path = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "XmlConfig/database.config";
                string assemblyConfigFile = Assembly.GetEntryAssembly().Location;
                Configuration config = ConfigurationManager.OpenExeConfiguration(assemblyConfigFile);
                AppSettingsSection app = config.AppSettings;
                value = app.Settings[key].Value;
                return value;
            }
            catch
            {
                return value;
            }
        }

        public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms)
        {

            using (SqlConnection con = new SqlConnection(connStrN))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //设置当前执行的是存储过程还是带参数的Sql语句
                    cmd.CommandType = cmdType;
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    try
                    {
                        con.Open();
                        return cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        con.Close();
                        con.Dispose();
                        throw;
                    }
                }
            }
        }
        #endregion

        public static int ExecuteNoQuery(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(connStrN))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //设置当前执行的是存储过程还是带参数的Sql语句
                    cmd.CommandType = cmdType;
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    try
                    {
                        con.Open();
                        return cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        con.Close();
                        con.Dispose();
                        throw;
                    }
                }
            }
        }

        //2.执行返回单个值的方法
        public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            ////IP地址
            //String SqlServerIP = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlServerId"), "WWKJ");
            ////用户名
            //String userId = Wujian_Jm.C_DesDecrypt(GetConfigValue("SqlUserId"), "WWKJ");
            ////用户密码
            //String pwd = Wujian_Jm.C_DesDecrypt(GetConfigValue("PassWord"), "WWKJ");
            ////数据库名
            //String dbName = GetConfigValue("DbaseName");

            ////拼接链接字符串
            //string connStrNew = "data source=" + SqlServerIP + ";initial catalog=" + dbName + ";user id=" + userId + ";password=" + pwd + ";";
            using (SqlConnection con = new SqlConnection(connStrN))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.Clear();
                    //设置当前执行的是存储过程还是带参数的Sql语句
                    cmd.CommandType = cmdType;
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }

                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }


        //3.返回SqlDataReader的方法
        public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(connStrN);

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = cmdType;
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    con.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //修改 刘学铸 by 2017-06-02
                    cmd.Parameters.Clear();
                    return dr;//cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch
                {
                    con.Close();
                    con.Dispose();
                    throw;
                }
            }

        }

        //4.返回DataTable
        public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStrN))
            {
                adapter.SelectCommand.CommandType = cmdType;
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }
                adapter.Fill(dt);
                return dt;
            }
        }


        /// <summary>
        /// 执行sql语句无参数
        /// </summary>
        public static int ExecuteNoParams(string sql, CommandType cmdType)
        {
            using (SqlConnection conn = new SqlConnection(connStrN))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = cmdType;
                    try
                    {

                        return cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        conn.Close();
                        throw;
                    }
                }

            }



        }

        /// <summary>
        /// 带事务的存储过程 
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">存储过程参数-不能为空</param>
        /// <returns>0:执行成功 异常抛出</returns>
        public static int ExecStoredProcedure(string procName, params SqlParameter[] parameters)
        {
            int rtn = 0;
            using (SqlConnection conn = new SqlConnection(connStrN))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    SqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        cmd.CommandText = procName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters);
                        rtn = cmd.ExecuteNonQuery();
                        st.Commit();
                        cmd.Parameters.Clear();//修改 刘学铸 at 2017-07-13
                        return rtn;
                    }
                    catch (SqlException sqlex)
                    {
                        st.Rollback();
                        throw sqlex;
                    }
                }
            }
        }



    }
}