using NFine.Code;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace NFine.Data.Extensions
{
    /// <summary>
    /// 数据库访问辅助类，add by ngye, on 2013-08-14.
    /// </summary>
    public static class SqlHelper
    {
        #region [ 连接串相关 ]
        /// <summary>
        /// 数据中心DB的连接字符串
        /// </summary>
        public static string DataCenterConnString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
        public static string DataCenterU8ConnString = ConfigurationManager.ConnectionStrings["U8connstr"].ConnectionString;
        /// <summary>
        /// 获取同步服务器的连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetDataCenterConn()
        {
            return new SqlConnection(DataCenterConnString);
        }
        /// <summary>
        /// 根据连接串获取连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetConnByStr(string connStr)
        {
            string ConnString = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
            return new SqlConnection(ConnString);
        }
        /// <summary>
        /// MD5解密
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string FromMd5(string str)
        {
            //return str;
            return Decrypt(str, "&%#@?,:*_");
        }
        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="strText"></param>
        /// <param name="sDecrKey"></param>
        /// <returns></returns>
        private static String Decrypt(String strText, String sDecrKey)
        {
            Byte[] byKey = { };
            Byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
            Byte[] inputByteArray = new byte[strText.Length];
            try
            {
                byKey = System.Text.Encoding.UTF8.GetBytes(sDecrKey.Substring(0, 8));
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();
                inputByteArray = Convert.FromBase64String(strText);
                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, IV),
           CryptoStreamMode.Write);
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();
                System.Text.Encoding encoding = System.Text.Encoding.UTF8;
                return encoding.GetString(ms.ToArray());
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        /// <summary>
        /// 根据连接串获取连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetConnByString(string conn)
        {
            return new SqlConnection(conn);
        }

        /// <summary>
        /// 测试连接串是否能正确
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <returns></returns>
        public static bool TestConnectionString(string connectionString)
        {
            bool result = true;
            try
            {
                using (SqlConnection conn = GetConnByString(connectionString))
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ex)
                    {
                        result = false;
                    }
                }
            }
            catch (Exception)
            {
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 测试连接串是否正确
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <param name="timeOut">测试的超时秒数</param>
        /// <param name="errInfo">错误时输出的错误信息</param>
        /// <returns>是否能正常连接</returns>
        public static bool TestConnectionString(string connectionString, int timeOut, ref string errInfo)
        {
            bool result = true;
            string[] arr = connectionString.Split(new char[] { ';' });
            List<string> list = new List<string>();

            foreach (string s in arr)
            {
                if (s.ToLower().IndexOf("timeout") == -1)
                {
                    list.Add(s);
                }
                else
                {
                    list.Add(String.Format("Connection Timeout={0}", timeOut));
                }
            }

            SqlConnection con = null;

            try
            {
                con = new SqlConnection(string.Join(";", list.ToArray()));
                con.Open();
            }
            catch (Exception ex)
            {
                result = false;
                errInfo = ex.Message;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
            return result;
        }
        #endregion

        #region [ 超时设置 ]
        public static int CommandTimeout
        {
            get
            {
                return 7200;
            }
        }
        #endregion

        public static string HttpPost(string url, string body)
        {
            try
            {
                Encoding encoding = Encoding.UTF8;
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "POST";
                request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                request.ContentType = "application/json; charset=utf-8";

                byte[] buffer = encoding.GetBytes(body);
                request.ContentLength = buffer.Length;
                request.GetRequestStream().Write(buffer, 0, buffer.Length);
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
                {
                    return reader.ReadToEnd();
                }
            }
            catch (WebException ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Http Get请求
        /// </summary>
        /// <param name="url"></param>
        /// <param name="headerValue"></param>
        /// <returns></returns>
        public static String httpGet(string url)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            WebHeaderCollection headers = new WebHeaderCollection();
            //headers.Add("Token", headerValue[0]);
            //headers.Add("Timespan", headerValue[1]);
            request.UserAgent = null;
            request.Headers = headers;
            request.Method = "GET";

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            var httpStatusCode = (int)response.StatusCode;
            Console.WriteLine("返回码为 {0}", httpStatusCode);
            if (httpStatusCode == 200)
            {
                Stream myResponseStream = response.GetResponseStream();
                StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
                string retString = myStreamReader.ReadToEnd();
                myStreamReader.Close();
                myResponseStream.Close();
                return retString;
            }
            else
            {
                Console.WriteLine("未返回数据 {0}", httpStatusCode);
                throw new Exception("no data response");
            }

        }
        //有header头使用
        public static String httpGetByHeader(string url, string HeaderName, string HeaderValue)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            WebHeaderCollection headers = new WebHeaderCollection();
            headers.Add(HeaderName, HeaderValue);
            //headers.Add("Timespan", headerValue[1]);
            request.UserAgent = null;
            request.Headers = headers;
            request.Method = "GET";

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            var httpStatusCode = (int)response.StatusCode;
            Console.WriteLine("返回码为 {0}", httpStatusCode);
            if (httpStatusCode == 200)
            {
                Stream myResponseStream = response.GetResponseStream();
                StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
                string retString = myStreamReader.ReadToEnd();
                myStreamReader.Close();
                myResponseStream.Close();
                return retString;
            }
            else
            {
                Console.WriteLine("未返回数据 {0}", httpStatusCode);
                throw new Exception("no data response");
            }

        }

        #region [ ExecuteNonQuery ]
        /// <summary>
        /// 根据sql语句和参数，返回受影响行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                return cmd.ExecuteNonQuery();
            }
        }


        //多语句一起事务中执行(Li编写)
        public static int CmdExecuteNonQueryLi(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    cmd.CommandText = sql;
                    int RES = cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                    return RES;
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
                return 0;
            }
        }

        public static int ExecuteNonQuery(string connStr, string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region [ ExecuteScalar_ForProc ]
        /// <summary>
        /// 根据存储过程和参数，返回Scalar结果
        /// </summary>
        /// <param name="proc">存储过程</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalar_ForProc(string proc, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(proc, conn).AddTimeout();
                cmd.CommandType = CommandType.StoredProcedure;
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                object obj = cmd.ExecuteScalar();
                return obj;
            }
        }

        /// <summary>
        /// 根据存储过程和参数，返回Scalar结果，但不加上超时设置 ( 避免循环做 AddTimeout )
        /// </summary>
        /// <param name="proc">存储过程</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalar_ForProc_WithoutAddTimeout(string proc, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(proc, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                object obj = cmd.ExecuteScalar();
                return obj;
            }
        }
        #endregion

        #region [ ExecuteScalar ]
        /// <summary>
        /// 根据sql语句和参数，返回Scalar结果
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                object obj = cmd.ExecuteScalar();
                return obj;
            }
        }
        #endregion
        /// <summary>
        /// 获取数据字典公共方法
        /// </summary>
        /// <param name="F_EnCode"></param>
        /// <returns></returns>
        public static string GetItemsDetails(string F_EnCode)
        {
            string sql = @"SELECT a.F_ItemCode,a.F_ItemName FROM Sys_SRM_ItemsDetail a 
                              LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
                              WHERE b.F_EnCode = '{0}'";
            sql = string.Format(sql, F_EnCode);
            DataTable dt = GetDataTableBySql(sql);
            return dt.Rows[0]["F_ItemCode"].ToString();

        }

        #region [ QueryByProc ]
        /// <summary>
        /// 根据存储过程及参数，返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSetByProc(string proc, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(proc, conn).AddTimeout();
                cmd.CommandType = CommandType.StoredProcedure;
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
        }

        /// <summary>
        /// 根据存储过程及参数，返回DataTable
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableByProc(string proc, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(proc, conn).AddTimeout();
                cmd.CommandType = CommandType.StoredProcedure;
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                DataTable dt = cmd.ExecuteDataTable();
                return dt;
            }
        }

        /// <summary>
        /// 根据sql语句和参数，返回DataRow
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataRow</returns>
        public static DataRow GetDataRowByProc(string proc, params SqlParameter[] spArr)
        {
            DataTable dt = GetDataTableByProc(proc, spArr);
            if (dt == null || dt.Rows.Count == 0)
                return null;

            return dt.Rows[0];
        }
        #endregion

        #region [ Query ]
        /// <summary>
        /// 根据sql语句和参数，返回DataSet
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSetBySql(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
        }

        /// <summary>
        /// 根据sql语句和参数，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableBySql(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                DataTable dt = cmd.ExecuteDataTable();
                return dt;
            }
        }
        public static string GetSerialCodeBYVenDor(string workPointCode, string tbName, string colName, string Pre, int numLen)
        {
            string sql = "EXEC Addins_GetSerialCode '','{0}','{1}','{2}',{3}";
            sql = string.Format(sql, new object[] { tbName, colName, Pre, numLen });
            return DbHelper.ExecuteScalarByVenDor(CommandType.Text, sql).ToString();
        }


        /// <summary>
        /// 根据sql语句和参数，返回DataRow
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataRow</returns>
        public static DataRow GetDataRowBySql(string sql, params SqlParameter[] spArr)
        {
            DataTable dt = GetDataTableBySql(sql, spArr);
            if (dt == null || dt.Rows.Count == 0)
                return null;

            return dt.Rows[0];
        }
        #endregion

        #region [ GetValue ]
        public static object GetValue(DataRow dr, string fieldName, object replaceValue)
        {
            if (dr.IsNull(fieldName))
                return replaceValue;
            return dr[fieldName];
        }

        public static object GetValue(DataRow dr, int idx, object replaceValue)
        {
            if (dr.IsNull(idx))
                return replaceValue;
            return dr[idx];
        }
        #endregion

        #region [ GetString ]
        public static string GetString(DataRow dr, string fieldName, string replaceValue)
        {
            if (dr.IsNull(fieldName))
                return replaceValue;
            return Convert.ToString(dr[fieldName]);
        }

        public static string GetString(DataRow dr, int idx, string replaceValue)
        {
            if (dr.IsNull(idx))
                return replaceValue;
            return Convert.ToString(dr[idx]);
        }
        #endregion

        #region [ GetDateTime ]
        public static DateTime GetDateTime(DataRow dr, string fieldName, DateTime replaceValue)
        {
            if (dr.IsNull(fieldName))
                return replaceValue;
            return Convert.ToDateTime(dr[fieldName]);
        }
        public static DateTime GetDateTime(DataRow dr, int idx, DateTime replaceValue)
        {
            if (dr.IsNull(idx))
                return replaceValue;
            return Convert.ToDateTime(dr[idx]);
        }
        #endregion

        #region [ 非数据中心库操作 ]

        #region [ ExecuteScalar ]
        /// <summary>
        /// 根据sql语句和参数，返回Scalar结果
        /// </summary>
        /// <param name="connString">连接串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalarWithConn(string connString, string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetConnByString(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                object obj = cmd.ExecuteScalar();
                return obj;
            }
        }
        #endregion

        #region [ ExecuteNonQuery ]
        /// <summary>
        /// 根据sql语句和参数，返回受影响行数
        /// </summary>
        /// <param name="connString">连接串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQueryWithConn(string connString, string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetConnByString(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region [ Query ]
        /// <summary>
        /// 根据sql语句和参数，返回DataTable
        /// </summary>
        /// <param name="connString">连接串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableBySqlWithConn(string connString, string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetConnByString(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                DataTable dt = cmd.ExecuteDataTable();
                return dt;
            }
        }

        /// <summary>
        /// 根据sql语句和参数，返回DataRow
        /// </summary>
        /// <param name="connString">连接串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataRow</returns>
        public static DataRow GetDataRowBySqlWithConn(string connString, string sql, params SqlParameter[] spArr)
        {
            DataTable dt = GetDataTableBySqlWithConn(connString, sql, spArr);
            if (dt == null || dt.Rows.Count == 0)
                return null;

            return dt.Rows[0];
        }
        #endregion
        #endregion

        #region [ 根据SQL文件路径执行 ]
        /// <summary>
        /// 执行SQL文件
        /// </summary>
        /// <param name="connString"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool ExecuteNonQueryWithConnAndSqlFilePath(string connString, string filePath)
        {
            string sql = System.IO.File.ReadAllText(filePath);
            return ExecuteNonQueryWithConnAndGO(connString, sql);
        }
        #endregion

        #region [ 执行带Go语句 ]
        /// <summary>  
        /// 执行带"GO"的SQL，返回最后一条SQL的受影响行数  
        /// </summary>  
        /// <param name="connString">连接串</param>  
        /// <param name="sql">sql语句</param>  
        /// <returns>返回最后一条SQL的受影响行数</returns>
        public static bool ExecuteNonQueryWithConnAndGO(string connString, string sql)
        {
            bool result = true;
            string[] arr = System.Text.RegularExpressions.Regex.Split(sql, @"\bGO\b", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            using (SqlConnection conn = GetConnByString(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand().AddTimeout();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < arr.Length; n++)
                    {
                        string strsql = arr[n];
                        if (strsql.Trim().Length > 1 && strsql.Trim().Replace(";", "") != "")
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    result = false;
                    //return -1;  
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return result;
        }
        #endregion


        /// <summary>
        /// 设置Sql参数
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static SqlParameter SetSqlParameter(string parameterName, object value, SqlDbType dbType)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
            {
                return new SqlParameter(parameterName, DBNull.Value);
            }
            SqlParameter sp = new SqlParameter(parameterName, value);
            sp.SqlDbType = dbType;
            return sp;
        }

        /// <summary>
        /// 根据sql及带 in 的id参数列表, 得到DataTable
        /// </summary>
        /// <param name="sql">带in ( {0} )的sql</param>  
        /// <param name="ids">以逗号分隔的id字符串</param>
        /// <param name="idDbType">id的Sql数据类型</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableWithIn(string sql, string ids, SqlDbType idDbType)
        {
            SqlParameter[] spArr = GetWithInSqlParameters(ref sql, ids, idDbType);
            return GetDataTableBySql(sql, spArr);
        }

        /// <summary>
        /// 根据sql及带 in 的id参数列表,　得到受影响行数
        /// </summary>
        /// <param name="sql">带in ( {0} )的sql</param>  
        /// <param name="ids">以逗号分隔的id字符串</param>
        /// <param name="idDbType">id的Sql数据类型</param>
        /// <returns>DataTable</returns>
        public static int ExecuteNonQueryWithIn(string sql, string ids, SqlDbType idDbType)
        {
            SqlParameter[] spArr = GetWithInSqlParameters(ref sql, ids, idDbType);
            return ExecuteNonQuery(sql, spArr);
        }

        #region [ 带 in 不确定参数的执行方法 ]
        /// <summary>  
        /// 获取带 in 的sql参数列表  
        /// </summary>  
        /// <param name="sql">带in ( {0} )的sql</param>  
        /// <param name="ids">以逗号分隔的id字符串</param>
        /// <param name="idDbType">id的Sql数据类型</param>
        /// <returns>sql参数列表</returns>  
        public static SqlParameter[] GetWithInSqlParameters(ref string sql, string ids, SqlDbType idDbType)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return null;
            }

            string[] idArr = ids.Split(',');
            //组建sql在in中的字符串  
            StringBuilder sbCondition = new StringBuilder();
            List<SqlParameter> spList = new List<SqlParameter>();
            for (int i = 0; i < idArr.Length; i++)
            {
                string id = idArr[i];
                string spName = string.Format("@id{0}", i);
                sbCondition.AppendFormat("{0},", spName);
                spList.Add(SetSqlParameter(spName, id, idDbType));
            }
            //重新构建sql  
            sql = string.Format(sql, sbCondition.ToString().TrimEnd(','));
            return spList.ToArray();
        }
        #endregion  

        public static string Organize_F_ParentId(string F_Account)
        {
            string F_ParentId = string.Empty;
            string sql = @"
            SELECT c.F_ID,c.F_ParentId FROM sys_srm_user a
            LEFT JOIN sys_SRM_Role b ON a.F_RoleID=b.F_ID
            LEFT JOIN Sys_SRM_Organize c ON b.F_OrganizeId=c.F_ID
            WHERE a.F_Account='{0}'";
            sql = string.Format(sql, F_Account);
            DataTable dt = GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["F_ParentId"].ToString() == "0")
                {
                    F_ParentId = "0";
                }
                else
                {
                    F_ParentId = dt.Rows[0]["F_ID"].ToString();
                }

            }
            return F_ParentId;
        }
        //public static string OrganizeByVendor_F_ParentId(string sqls,string F_Account)
        //{
        //    string F_ParentId = string.Empty;
        //    string sql = @"
        //    SELECT b.F_EnCode FROM (SELECT* FROM  Sys_SRM_OrganizeByVendor a
        //    WHERE a.F_EnCode= '{0}')aa
        //    LEFT JOIN Sys_SRM_OrganizeByVendor b ON aa.F_ID=b.F_ParentID";
        //    sql = string.Format(sql, F_Account);
        //    DataTable dt = GetDataTableBySql(sql);
        //    if (dt.Rows.Count > 0)
        //    {
        //        if (string.IsNullOrWhiteSpace( dt.Rows[0]["F_EnCode"].ToString()))
        //        {
        //            F_ParentId = "0";
        //        }
        //        else
        //        {
        //            sqls += "and  vencode IN (" + sql + ")";
        //        }

        //    }
        //    return sqls;
        //}

        public static string OrganizeByVendor_F_ParentId(string sqls, string F_Account)
        {
            string F_ParentId = string.Empty;
            string purchugcode = string.Empty;
            string sql = @"
            SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
                               LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
            WHERE  a.F_EnCode='Vendor_Category' AND  b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
            sql = string.Format(sql, F_Account);
            DataTable dt = GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
                }
                sqls += @"and  vencode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";

            }
            //else
            //{
            //    sqls += @"and  vencode = '" + purchugcode.TrimEnd(',') + "'";
            //}
            return sqls;
        }


        public static string OrganizeByVendor_F_ParentIdByCommint(string sqls, string F_Account)
        {
            string F_ParentId = string.Empty;
            string purchugcode = string.Empty;
            string sql = @"
            SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
                               LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
            WHERE  a.F_EnCode='Vendor_Category' AND  b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
            sql = string.Format(sql, F_Account);
            DataTable dt = GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
                }
                sqls += @"and  a.vencode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";

            }
            sqls += "  GROUP BY POCode,CreateDateTime,e.ProjectCode,a.VenCode,d.VenName,ReleaseState,CreatePerson,a.Filename,a.uploadcomment ,a.SignBackStatus,a.WorkPoint ,a.EATTRIBUTE7,a.EATTRIBUTE8,                           a.EATTRIBUTE9,a.EATTRIBUTE10 ,a.EATTRIBUTE11 ,a.EATTRIBUTE12,a.BackComment";
            return sqls;
        }

        public static string OrganizeByVendor_F_ParentIdBYJX(string sqls, string F_Account)
        {

            string F_ParentId = string.Empty;
            string purchugcode = string.Empty;
            string sql = @"
            SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
                               LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
            WHERE  a.F_EnCode='Vendor_Category' AND  b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
            sql = string.Format(sql, F_Account);
            DataTable dt = GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
                }
                sqls += @"and   a.vencode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";

            }
            return sqls;


            //string F_ParentId = string.Empty;
            //string sql = @"
            //SELECT b.F_EnCode FROM (SELECT* FROM  Sys_SRM_OrganizeByVendor a
            //WHERE a.F_EnCode= '{0}')aa
            //LEFT JOIN Sys_SRM_OrganizeByVendor b ON aa.F_ID=b.F_ParentID";
            //sql = string.Format(sql, F_Account);
            //DataTable dt = GetDataTableBySql(sql);
            //if (dt.Rows.Count > 0)
            //{
            //    if (string.IsNullOrWhiteSpace(dt.Rows[0]["F_EnCode"].ToString()))
            //    {
            //        F_ParentId = "0";
            //    }
            //    else
            //    {
            //        sqls += "and  a.vencode IN (" + sql + ")";
            //    }

            //}
            //return sqls;
        }

        public static string OrganizeByVendor_F_ParentIdBYDZ(string sqls, string F_Account)
        {
            string F_ParentId = string.Empty;
            string purchugcode = string.Empty;
            string sql = @"
            SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
                               LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
            WHERE  a.F_EnCode='Vendor_Category' AND  b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
            sql = string.Format(sql, F_Account);
            DataTable dt = GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
                }
                sqls += @"and  SupplierCode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";

            }
            return sqls;



            //string F_ParentId = string.Empty;
            //string sql = @"
            //SELECT b.F_EnCode FROM (SELECT* FROM  Sys_SRM_OrganizeByVendor a
            //WHERE a.F_EnCode= '{0}')aa
            //LEFT JOIN Sys_SRM_OrganizeByVendor b ON aa.F_ID=b.F_ParentID";
            //sql = string.Format(sql, F_Account);
            //DataTable dt = GetDataTableBySql(sql);
            //if (dt.Rows.Count > 0)
            //{
            //    if (string.IsNullOrWhiteSpace(dt.Rows[0]["F_EnCode"].ToString()))
            //    {
            //        F_ParentId = "0";
            //    }
            //    else
            //    {
            //        sqls += "and  SupplierCode IN (" + sql + ")";
            //    }

            //}
            //return sqls;
        }



        public static string Organize_F_Account(string F_ParentId, string Selectsql)
        {
            DataTable dt = null;
            string sql = @"with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='{0}'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            ){1}
                            AND CreatePerson IN

                        (select F_RealName from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )";
            sql = string.Format(sql, F_ParentId, Selectsql);
            return sql;
        }

        public static DataTable FindTablePageBySql_OtherTemp(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTemp(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }
        //订单回签

        public static DataTable FindTablePageBySql_OtherTempByCommint(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempByCommint(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }
        public static DataTable FindTablePageBySql_OtherTempByVendor(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempByVendor(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }

        public static DataTable FindTablePageBySql_OtherTemp2(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTemp2(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }

        public static DataTable FindTablePageBySql_OtherTempKbs(ref Pagination Pagination, DataTable dts)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempKbs(pageIndex, pageSize, ref totalRow, dts);
            Pagination.records = totalRow;
            return dt;
        }

        public static DataTable FindTablePageBySql_OtherTemp3(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTemp3(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }

        public static DataTable FindTablePageBySql_OtherTemp4(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTemp4(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }

        private static DataTable GetPageTable_EXISTSTemp(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {

            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by " + orderField + " " + orderType + "   ";

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql = @" with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )  Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += @" As rowNum, * From  (" + sqlTempName + @"  AND PersonCode IN

                        (select F_Account from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                //string ss = ds.Tables[0].Rows[0][0].ToString();
                count = ds.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }

        private static DataTable GetPageTable_EXISTSTempByCommint(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {

            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by " + orderField + " " + orderType + "   ";
            string sqlcount = string.Empty;
            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql = @" with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )  Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += @" As rowNum, * From  (" + sqlTempName + @"  AND PersonCode IN

                        (select F_Account from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )  GROUP BY POCode,CreateDateTime,e.ProjectCode,a.VenCode,d.VenName,ReleaseState,CreatePerson,a.Filename,a.uploadcomment ,a.SignBackStatus,a.WorkPoint ,a.EATTRIBUTE7,a.EATTRIBUTE8,                           a.EATTRIBUTE9,a.EATTRIBUTE10 ,a.EATTRIBUTE11 ,a.EATTRIBUTE12,a.BackComment) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";

                sqlcount += @"with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )  Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")";
                sqlcount += @" As rowNum, * From  (" + sqlTempName + @"  AND PersonCode IN

                        (select F_Account from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )  GROUP BY POCode,CreateDateTime,e.ProjectCode,a.VenCode,d.VenName,ReleaseState,CreatePerson,a.Filename,a.uploadcomment ,a.SignBackStatus,a.WorkPoint ,a.EATTRIBUTE7,a.EATTRIBUTE8,                           a.EATTRIBUTE9,a.EATTRIBUTE10 ,a.EATTRIBUTE11 ,a.EATTRIBUTE12,a.BackComment) As T ) As N";
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                SqlCommand cmdcount = new SqlCommand(sqlcount, conn).AddTimeout();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                SqlDataAdapter adaptercount = new SqlDataAdapter(cmdcount);
                DataSet dscount = new DataSet();
                adaptercount.Fill(dscount);
                //string ss = ds.Tables[0].Rows[0][0].ToString();
                count = dscount.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(dscount.Tables[0].Rows.Count);
                return ds.Tables[0];
            }
        }
        
        private static DataTable GetPageTable_EXISTSTempByVendor(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {

            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by " + orderField + " " + orderType + "   ";

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql = @" with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )  Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += @" As rowNum, * From  (" + sqlTempName + @"  AND PersonCode IN

                        (select F_Account from CTE a
                         )) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                //string ss = ds.Tables[0].Rows[0][0].ToString();
                count = ds.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }
        //招投标权限


        private static DataTable GetPageTable_EXISTSTemp2(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by InvCode desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[1].Rows[0][0].ToString();
                count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds.Tables[1];
            }
        }



        private static DataTable GetPageTable_EXISTSTempKbs(int pageIndex, int pageSize, ref int count, DataTable dts)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            //string OrderBy = " order by InvCode desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                //conn.Open();
                //sql += " Select Count(1) From  " + sqlTempName;
                //sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                //sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                //sql += Droptable;
                //SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                //SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //DataSet ds = new DataSet();
                //adapter.Fill(ds);
                //string ss = dts.Rows[0][0].ToString();
                //count = Convert.ToInt32(dts.Rows.Count.ToString());
                //return dts;

                DataTable resultTable = dts.Clone();
                for (int i = num; i < Math.Min(num1, dts.Rows.Count); i++)
                {
                    resultTable.ImportRow(dts.Rows[i]);
                }

                // 计算总记录数
                count = dts.Rows.Count;
                return resultTable;


            }
        }


        private static DataTable GetPageTable_EXISTSTemp3(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by VENDORCODE desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[0].Rows[0][0].ToString();
                if (ds.Tables[1].Rows.Count > 0)
                {
                    count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                    return ds.Tables[0];
                }
                else
                {
                    return new DataTable();
                }
            }
        }

        private static DataTable GetPageTable_EXISTSTemp4(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            //string OrderBy = " order by dDate desc   ";
            string OrderBy = " order by " + orderField + " " + orderType + "   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[0].Rows[0][0].ToString();
                if (ds.Tables[1].Rows.Count > 0)
                {
                    count = Convert.ToInt32(ss);
                    return ds.Tables[1];
                }
                else
                {
                    return new DataTable();
                }
            }
        }

        public static DataTable FindTablePageBySql_OtherTempbyLOGUSER(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempbyLOGUSER(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }
        private static DataTable GetPageTable_EXISTSTempbyLOGUSER(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {

            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by " + orderField + " " + orderType + "   ";

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql = @" with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )  Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += @" As rowNum, * From  (" + sqlTempName + @"  AND A.LOGUSER IN

                        (select F_RealName from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                //string ss = ds.Tables[0].Rows[0][0].ToString();
                count = ds.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }


        public static DataTable FindTablePageBySql_OtherTempByZTB(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempByZTB(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }
        private static DataTable GetPageTable_EXISTSTempByZTB(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by InvCode desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[1].Rows[0][0].ToString();
                count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }
        public static DataTable FindTablePageBySql_OtherTempSORRFQbyChan(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempSORRFQbyChan(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }
        private static DataTable GetPageTable_EXISTSTempSORRFQbyChan(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by 物料编码 desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[1].Rows[0][0].ToString();
                count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }
        public static DataTable FindTablePageBySql_OtherTempSORRFQ(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTempSORRFQ(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }
        private static DataTable GetPageTable_EXISTSTempSORRFQ(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by ITEMCODE desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[1].Rows[0][0].ToString();
                count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }

        public static DataTable FindTablePageBySql_OtherTemps(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
        {
            string orderField = Pagination.sidx;
            string orderType = Pagination.sord;
            int pageIndex = Pagination.page;
            int pageSize = Pagination.rows;
            int totalRow = Pagination.records;
            DataTable dt = GetPageTable_EXISTSTemps(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
            Pagination.records = totalRow;
            return dt;
        }

        private static DataTable GetPageTable_EXISTSTemps(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            //StringBuilder strSql = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = " order by VENDORCODE desc   ";
            //if (!string.IsNullOrEmpty(orderField))
            //    OrderBy = "Order By " + orderField + " " + orderType + "";
            //else
            //    OrderBy = "order by (select 0)";
            //strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            //strSql.Append(" As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
            //count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From  " + sqlTempName +" "+ Droptable, param));
            //IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
            //return DatabaseReader.ReaderToDataTable(dr);

            using (SqlConnection conn = SqlHelper.GetDataCenterConn())
            {
                conn.Open();
                sql += " Select Count(1) From  " + sqlTempName;
                sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
                sql += " As rowNum, * From  " + sqlTempName + "  ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
                sql += Droptable;
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                string ss = ds.Tables[1].Rows[0][0].ToString();
                count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds.Tables[0];
            }
        }
        public static string GetZZNumber(string ClassCode)
        {
            string result = string.Empty;
            switch (ClassCode)
            {
                case "1":
                    result = "YW";
                    break;
                case "2":
                    result = "BW";
                    break;
                case "3":
                    result = "CW";
                    break;
                case "4":
                    result = "FW";
                    break;
                case "5":
                    result = "PW";
                    break;
                case "6":
                    result = "DW";
                    break;
                case "7":
                    result = "GW";
                    break;
                case "8":
                    result = "LW";
                    break;
            }
            return result;
        }
        public static DataTable GetDataSetBySql(string sql, SqlCommand cmd)
        {
            cmd.CommandText = sql;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable ds = new DataTable();
            adapter.Fill(ds);
            return ds;

        }
        public static void UserPwdValid(string User, string Pwd)
        {
            if (string.IsNullOrWhiteSpace(User))
            {
                Regex regex = new Regex(@"^(?=.*[A-Za-z])(?=.*\d)(?=.*[~?!@#$%^&*_-])[A-Za-z\d~?!@#$%^&*_-]{8,}$");
                //string rex = @"^(?=.*[A-Za-z])(?=.*\d)(?=.*[~?!@#$%^&*_-])[A-Za-z\d~?!@#$%^&*_-]{8,}$";
                if (!regex.IsMatch(Pwd))
                {
                    throw new Exception("密码不符合规则,至少8位,至少包含一个英文字母(区分大小写),至少包含一个数字,至少包含一个特殊字符.");
                }
            }
        }
        /// <summary>
        /// 获取配置文件信息
        /// </summary>
        /// <param name="F_EnCode"></param>
        /// <returns></returns>
        public static string GetItemsDetails(string F_EnCode, string WorkPoint)
        {
            string msg = string.Empty;
            string sqldetail = @"SELECT a.F_ItemName,a.F_Description FROM Sys_SRM_ItemsDetail a 
                           LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
                            WHERE b.F_EnCode = '{0}'  and a.F_EnabledMark='1' and a.F_ItemCode='{1}'";
            sqldetail = string.Format(sqldetail, F_EnCode, WorkPoint);
            DataTable dts = GetDataTableBySql(sqldetail);
            if (dts.Rows.Count > 0)
            {
                msg = dts.Rows[0]["F_Description"].ToString();
            }

            return msg;
        }
        /// <summary>
        /// 订单发布邮件内容
        /// </summary>
        /// <param name="F_EnCode"></param>
        /// <returns></returns>
        public static string GetItemsDetailsbySendMail(string F_EnCode)
        {
            string msg = string.Empty;
            string sqldetail = @"SELECT a.F_ItemName,a.F_Description FROM Sys_SRM_ItemsDetail a 
                           LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
                            WHERE b.F_EnCode = '{0}'  and a.F_EnabledMark='1'";
            sqldetail = string.Format(sqldetail, F_EnCode);
            DataTable dts = GetDataTableBySql(sqldetail);
            if (dts.Rows.Count > 0)
            {
                msg = dts.Rows[0]["F_Description"].ToString();
            }

            return msg;
        }

        /// <summary>
        /// 送货单审核配置
        /// </summary>
        /// <param name="F_EnCode"></param>
        /// <returns></returns>
        public static string GetSHDZDSHItemsDetails(string F_EnCode, string WorkPoint)
        {
            string msg = string.Empty;
            string sqldetail = @"SELECT a.F_ItemName,a.F_Description FROM Sys_SRM_ItemsDetail a 
                           LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
                            WHERE b.F_EnCode = '{0}'  and a.F_EnabledMark='1'";
            sqldetail = string.Format(sqldetail, F_EnCode, WorkPoint);
            DataTable dts = GetDataTableBySql(sqldetail);
            if (dts.Rows.Count > 0)
            {
                msg = dts.Rows[0]["F_ItemName"].ToString();
            }
            return msg;
        }
        /// <summary>
        /// 方法一：获取编号 返回Dictionary字典，调用该方法用Dictionary字典接收，只需要遍历Dictionary即可得到响应的值
        /// </summary>
        /// <param name="workPointCode"></param>
        /// <param name="tbName"></param>
        /// <param name="colName"></param>
        /// <param name="Pre"></param>
        /// <param name="numLen"></param>
        /// <returns>返回Dictionary字典</returns>
        public static Dictionary<string, int> GetAllCode(string cAcc_Id, string cVouchType, string iAmount)
        {
            string iFatherId = string.Empty, iChildId = string.Empty;
            Dictionary<string, int> dic = new Dictionary<string, int>();
            try
            {
                //SqlConnection conn = SqlHelper.GetDataCenterConn();
                SqlConnection conn = new SqlConnection(DataCenterU8ConnString);
                SqlCommand cmd = new SqlCommand("sp_GetIDWithoutRemote", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@cAcc_Id", cAcc_Id);　　//给输入参数赋值
                cmd.Parameters.AddWithValue("@cVouchType", cVouchType);　　//给输入参数赋值
                cmd.Parameters.AddWithValue("@iAmount", iAmount);　　//给输入参数赋值
                //cmd.Parameters.AddWithValue("@iFatherId", iFatherId);　　//给输入参数赋值
                //cmd.Parameters.AddWithValue("@iChildId", iChildId);　　//给输入参数赋值
                SqlParameter parOutput = cmd.Parameters.Add("@iFatherId", SqlDbType.NVarChar, 50);　　//定义输出参数
                cmd.Parameters["@iFatherId"].Direction = ParameterDirection.Output;
                SqlParameter parOutputs = cmd.Parameters.Add("@iChildId", SqlDbType.NVarChar, 50);　　//定义输出参数
                cmd.Parameters["@iChildId"].Direction = ParameterDirection.Output;
                SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Int);
                parReturn.Direction = ParameterDirection.ReturnValue; 　　//参数类型为ReturnValue
                cmd.Parameters.Add(parReturn);
                conn.Open();
                cmd.ExecuteNonQuery();
                iFatherId = cmd.Parameters["@iFatherId"].Value.ToString();
                iChildId = cmd.Parameters["@iChildId"].Value.ToString();
                if (!string.IsNullOrWhiteSpace(iFatherId))//判断iFatherId是否为空，不为空的话将值放入dictionary字典中，否则int.Parse()会抛出异常
                {
                    dic.Add("iFatherId", int.Parse(iFatherId));
                    dic.Add("iChildId", int.Parse(iChildId));
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return dic;
        }
        /// <summary>
        /// DataSet转Json字符串，主子结构
        /// 需要建立主子表关系，第一张表为主表
        /// 会排除关联列
        /// </summary>
        /// <param name="dataSet"></param>
        /// <param name="RelationName">关系名称</param>
        /// <returns></returns>
        public static string DataSetToJson(DataSet dataSet, string RelationName, string column)
        {
            DataRelation dataRelation = new DataRelation(RelationName, dataSet.Tables[0].Columns[column], dataSet.Tables[1].Columns[column]);
            dataSet.Relations.Add(dataRelation);

            StringBuilder jsonString = new StringBuilder();
            //foreach (DataTable table in dataSet.Tables)
            //{
            DataTable table = dataSet.Tables[0];

            jsonString.Append("[");
            DataRowCollection drc = table.Rows;
            for (int i = 0; i < drc.Count; i++)
            {
                DataRow dataRow = drc[i];
                jsonString.Append("{");
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    string strKey = table.Columns[j].ColumnName;
                    if (dataSet.Relations[RelationName].ParentColumns.Select(a => a.Caption).Contains(strKey))
                        continue;
                    string strValue = dataRow[j].ToString();
                    Type type = table.Columns[j].DataType;
                    jsonString.Append("\"" + strKey + "\":");
                    strValue = StringFormat(strValue, type);
                    if (j < table.Columns.Count - 1)
                    {
                        jsonString.Append(strValue + ",");
                    }
                    else
                    {
                        jsonString.Append(strValue);
                    }
                }
                jsonString.Append(",\"" + RelationName + "\":");
                DataRow[] drs = dataRow.GetChildRows(RelationName);
                jsonString.Append("[");
                foreach (DataRow dr in drs)
                {
                    DataTable dt = dr.Table;
                    jsonString.Append("{");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string strKey = dt.Columns[j].ColumnName;
                        if (dataSet.Relations[RelationName].ChildColumns.Select(a => a.Caption).Contains(strKey))
                            continue;
                        string strValue = dr[j].ToString();
                        Type type = dt.Columns[j].DataType;
                        jsonString.Append("\"" + strKey + "\":");
                        strValue = StringFormat(strValue, type);
                        if (j < dt.Columns.Count - 1)
                        {
                            jsonString.Append(strValue + ",");
                        }
                        else
                        {
                            jsonString.Append(strValue);
                        }
                    }
                    jsonString.Append("},");
                }
                if (drs.Length > 0)
                    jsonString.Remove(jsonString.Length - 1, 1);
                jsonString.Append("]");

                jsonString.Append("},");
            }
            jsonString.Remove(jsonString.Length - 1, 1);
            jsonString.Append("]");
            //}
            string res = jsonString.ToString();
            return res;
        }
        /// <summary>
        /// 格式化字符型、日期型、布尔型
        /// </summary>
        /// <param name="str"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private static string StringFormat(string str, Type type)
        {
            if (type == typeof(string))
            {
                str = String2Json(str);
                str = "\"" + str + "\"";
            }
            else if (type == typeof(DateTime))
            {
                str = "\"" + str + "\"";
            }
            else if (type == typeof(bool))
            {
                str = str.ToLower();
            }
            else if (type != typeof(string) && string.IsNullOrEmpty(str))
            {
                str = "\"" + str + "\"";
            }
            return str;
        }
        /// <summary>
        /// 过滤特殊字符
        /// </summary>
        /// <param name="s">字符串</param>
        /// <returns>json字符串</returns>
        private static string String2Json(String s)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < s.Length; i++)
            {
                char c = s.ToCharArray()[i];
                switch (c)
                {
                    case '\"':
                        sb.Append("\\\""); break;
                    case '\\':
                        sb.Append("\\\\"); break;
                    case '/':
                        sb.Append("\\/"); break;
                    case '\b':
                        sb.Append("\\b"); break;
                    case '\f':
                        sb.Append("\\f"); break;
                    case '\n':
                        sb.Append("\\n"); break;
                    case '\r':
                        sb.Append("\\r"); break;
                    case '\t':
                        sb.Append("\\t"); break;
                    default:
                        sb.Append(c); break;
                }
            }
            return sb.ToString();
        }

        public static string GetEATTRIBUTE(string Tabname, string TablenameJC)
        {
            string SqlKey = "";
            string sql = @"select TableCode, ColumnCode AS  Code,Name  from ICSSRMColumnEnable
                            where Enable=1 and  TableCode='" + Tabname + "' order by MTIME";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SqlKey += "" + TablenameJC + "." + dt.Rows[i]["Code"].ToString() + ",";
            }
            return SqlKey;
        }

        /// <summary>
        /// 根据sql语句和参数，返回DataRow
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ConnectStr">数据库链接名称</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataRow</returns>
        public static DataRow GetDataRowBySql_OtherConn(string sql, string ConnectStr, params SqlParameter[] spArr)
        {
            DataTable dt = GetDataTableBySql_OtherConn(sql, ConnectStr, spArr);
            if (dt == null || dt.Rows.Count == 0)
                return null;

            return dt.Rows[0];
        }
        /// <summary>
        /// 根据sql语句和参数，返回DataTable 分页显示
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ConnectStr">数据库链接名称</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableBySql_OtherConn(string sql, string ConnectStr, ref Pagination jqgridparam, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetConnByStr(ConnectStr))
            {

                string orderField = jqgridparam.sidx;
                string orderType = jqgridparam.sord;
                int pageIndex = jqgridparam.page;
                int pageSize = jqgridparam.rows;
                int totalRow = 0;
                string OrderBy = "";
                if (pageIndex == 0)
                {
                    pageIndex = 1;
                }
                int num = (pageIndex - 1) * pageSize;
                int num1 = (pageIndex) * pageSize;

                if (!string.IsNullOrEmpty(orderField))
                    OrderBy = orderField + " " + orderType + "";

                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr != null && spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                DataTable dt = cmd.ExecuteDataTable();
                DataTable dtNew = dt.Clone();
                if (dt != null && dt.Rows.Count > 0)
                {
                    jqgridparam.records = dt.Rows.Count;
                    DataView dv = dt.DefaultView;
                    dv.Sort = OrderBy;
                    dt = dv.ToTable();

                    if (dt.Rows.Count >= num)
                    {
                        for (int i = num; i < num1 && i < dt.Rows.Count; i++)
                        {
                            dtNew.ImportRow(dt.Rows[i]);
                        }
                    }
                }

                return dtNew;
            }
        }
        /// <summary>
        /// 根据sql语句和参数，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ConnectStr">数据库链接名称</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableBySql_OtherConn(string sql, string ConnectStr, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = SqlHelper.GetConnByStr(ConnectStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                if (spArr != null && spArr.Length > 0)
                    cmd.Parameters.AddRange(spArr.SetDBNull());
                DataTable dt = cmd.ExecuteDataTable();
                return dt;
            }
        }
    }//end of class

    #region [ SqlHelper 的扩展方法类 ]
    /// <summary>
    /// 扩展方法类
    /// </summary>
    public static class SqlHelperExtensionMethods
    {
        /// <summary>
        /// 新建SqlCommand对象时，　自动加上指定的 CommandTimeout. by ngye, on 2013-07-11.
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>SqlCommand对象</returns>
        public static SqlCommand AddTimeout(this SqlCommand cmd)
        {
            cmd.CommandTimeout = SqlHelper.CommandTimeout;
            return cmd;
        }

        /// <summary>
        /// 新建SqlBulkCopy对象时，　自动加上指定的 BulkCopyTimeout. by ngye, on 2013-08-30.
        /// </summary>
        /// <param name="cmd">SqlBulkCopy对象</param>
        /// <returns>SqlBulkCopy对象</returns>
        public static SqlBulkCopy AddTimeout(this SqlBulkCopy bulkCopy)
        {
            bulkCopy.BulkCopyTimeout = SqlHelper.CommandTimeout;
            return bulkCopy;
        }

        /// <summary>
        /// 执行cmd得到 DataTable. by ngye, on 2013-08-01
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(this SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 为SqlParameter设置参数. by ngye, on 2013-08-15.
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static SqlParameter SetValue(this SqlParameter sp, object value)
        {
            sp.Value = value;
            return sp;
        }

        /// <summary>
        /// 为SqlParameter设置SqlDbType. by ngye, on 2013-09-03.
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static SqlParameter SetSqlDbType(this SqlParameter sp, SqlDbType dbType)
        {
            sp.SqlDbType = dbType;
            return sp;
        }

        /// <summary>
        /// 对可以为空的值作这样的处理，一旦其为空，就设置为DBNull.value.
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static SqlParameter[] SetDBNull(this SqlParameter[] spArr)
        {
            if (spArr == null || spArr.Length == 0)
                return spArr;
            for (int i = 0; i < spArr.Length; i++)
            {
                SqlParameter sp = spArr[i];
                if (sp.Value == null)
                    sp.Value = DBNull.Value;
            }
            return spArr;
        }
        public static string HttpPost(string url, string body)
        {
            try
            {
                Encoding encoding = Encoding.UTF8;
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "POST";
                request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                request.ContentType = "application/json; charset=utf-8";

                byte[] buffer = encoding.GetBytes(body);
                request.ContentLength = buffer.Length;
                request.GetRequestStream().Write(buffer, 0, buffer.Length);
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
                {
                    return reader.ReadToEnd();
                }
            }
            catch (WebException ex)
            {
                throw new Exception(ex.Message);
            }
        }

    }

    #endregion
}