using System;
//using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
//using System.Data.OleDb;

//using SystemFramework;
//using BasicData;
using System.Text.RegularExpressions;
using BasicData;
using SystemFramework;

namespace DataLinkMesWork
{

    /// <summary>
    /// ���ݷ����ࡪ���ṩͨ�õ����ݷ��ʷ���
    /// </summary>
    public partial class SQLCommon
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="json"></param>
        /// <param name="thisParms"></param>
        /// <param name="isOutput"></param>
        /// <returns></returns>
        public static bool GetCmdParam(jsonobj json, ref SqlParameter[] thisParms,out bool isOutput )
        {
            //SqlParameter[] thisParms=null;
            
            isOutput = false;
            try
            {
                string[] parmas;
                if (json.Param.Contains("\\&"))
                {
                    parmas = Regex.Split(json.Param, "\\\\&", RegexOptions.IgnoreCase);
                }
                else
                {
                    parmas = json.Param.Split('&');
                }
                // �ж������ַ����Ƿ�Ϸ�������Ϸ������Ƴ�����������
                for (int i = parmas.Length-1; i >= 0; i--) {
                    string a = parmas[i];
                    string illegal1 = "==";
                    string illegal2 = "null";
                    string illegal3 = "undefined";
                    if (a.IndexOf(illegal1) > -1 || a.IndexOf(illegal2) > -1 || a.IndexOf(illegal3) > -1) {
                        ArrayList ar = new ArrayList(parmas);
                        ar.Remove(parmas[i]);
                        parmas = (string[])ar.ToArray(typeof(string));                 
                    }
                }
                // �Ƴ����Ϸ����������¶���thisParms����
                thisParms = new SqlParameter[parmas.Length];
                for (int i = 0; i < parmas.Length; i++)
                {
                    // ���κ� =
                    string[] pp;
                    if (parmas[i].Contains("\\="))
                    {
                        pp = Regex.Split(parmas[i], "\\\\=", RegexOptions.IgnoreCase);
                    }
                    else
                    {
                        pp = parmas[i].Split('=');
                    }
                    object inputValue = null;
                    if (pp.Length == 4)
                    {
                        if(pp[3]=="output")
                        {
                            inputValue = GetInputValue(pp);
                            thisParms[i] = new SqlParameter(pp[0], inputValue);
                            thisParms[i].Direction = ParameterDirection.Output;
                            isOutput = true;
                        }
                    }
                    else if (pp.Length == 3)
                    {
                        inputValue = GetInputValue(pp);

                        thisParms[i] = new SqlParameter(pp[0], inputValue);
                    }
                    else if (pp.Length == 2)
                    {
                        thisParms[i] = new SqlParameter(pp[0], pp[1]);
                    }

                }
            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }

            return true;
        }
        /// <summary>
        /// ���������������������Json�ַ���
        /// </summary>
        /// <param name="thisParms"></param>
        /// <returns></returns>
        public static string GetOutputValue(SqlParameter[] thisParms)
        {
            string resultOutput = "";
            //[{"name1":"value1","name2":"value2"}]
            resultOutput = "";
            int outputCount=0;
            for (int i = 0; i < thisParms.Length; i++)
            {
                if (thisParms[i].Direction == ParameterDirection.Output)
                {
                    if (outputCount == 0)
                    {
                        resultOutput = resultOutput + "\"" + thisParms[i].ParameterName + "\":" + "\"" + thisParms[i].Value.ToString() + "\"";
                    }
                    else
                    {
                        resultOutput = resultOutput + "," +  "\"" + thisParms[i].ParameterName + "\":" + "\"" + thisParms[i].Value.ToString() + "\"";
                    }
                    outputCount++;

                }
            }
            resultOutput = "[{" + resultOutput + "}]";
            return resultOutput;
        }

        /// <summary>
        /// �������ͺŴ���&1&Int|��λ��&ML050&String|PageCurrent&1&Int|PageSize&10&Int|PageCount&0&Int&Output|ItemCount&0&Int&Output
        /// </summary>
        /// <param name="pp"></param>
        /// <returns></returns>
        static object GetInputValue(string[] pp)
        {
            object inputValue = null;
            switch (pp[2])
            {
                case "int":
                    inputValue = Convert.ToInt32(pp[1]);
                    break;
                case "string":
                    inputValue = pp[1];
                    break;
                case "boolean":
                    if(pp[1]=="1")
                    {
                        inputValue = true;
                    }
                    else
                    {
                        inputValue = false;
                    }
                    break;
                case "datetime":
                    inputValue = Convert.ToDateTime(pp[1]);
                    break;
                default:
                    inputValue = pp[1];
                    break;
            }

            return inputValue;

        }
        /// <summary>
        /// SqlServer����״̬
        /// =true ��������
        /// =false ����ʧ��
        /// </summary>
        static bool sqlServerConnectionStatus=true;
        /// <summary>
        /// SqlServer����״̬
        /// =true ��������
        /// =false ����ʧ��
        /// </summary>
        public static bool SqlServerConnectionStatus
        {
            get
            {
                return sqlServerConnectionStatus;
            }
            set
            {
                sqlServerConnectionStatus = value;
            }
        }
        /// <summary>
        /// ���������ݵ�����
        /// SqlServer����״̬
        /// =true ��������
        /// =false ����ʧ��
        /// </summary>
        /// <returns></returns>
        public static void TestConnection()
        {
            string sql = "select top 1 uid from sysusers ";
            string connectionString = ApplicationConfig.ConnectionString_MES;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                SqlServerConnectionStatus = true;
                return;
            }
            catch (Exception e)
            {
                //SqlServerConnectionStatus = false;
                ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                // SqlServer ������ͨѶ״̬
                return;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        /// <summary>
        /// ���������ݵ�����
        /// SqlServer����״̬
        /// =true ��������
        /// =false ����ʧ��
        /// </summary>
        /// <returns></returns>
        public static bool TestConnectionWorkStart()
        {
            bool isOK = true;
            string sql = "select top 1 uid from sysusers ";
            string connectionString = ApplicationConfig.ConnectionString_MES;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                return isOK;
            }
            catch
            {
                isOK = false;
                return isOK;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        /// <summary>
        /// ִ�б�׼SQL��䣬��Ҫ�󷵻ؽ�����ʺϣ�����ɾ���ģ�
        /// </summary>
        /// <param name="sql">��׼SQL���</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteNonQuery(StringCollection sql, string connectionString, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            string sqlText="";
            //SqlCommand cmd;
        //    using (SqlConnection connection =
        //new SqlConnection(GetConnectionString()))
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = conn;

                        for (int i = 0; i < sql.Count; i++)
                        {
                            cmd.CommandText = sql[i].ToString();
                            sqlText = cmd.CommandText;
                            //SqlCommand cmd = new SqlCommand(sql[i].ToString(), conn);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sqlText + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
                return result;
            }
            //try
            //{
            //    if (!SqlServerConnectionStatus) return false;
            //    conn.Open();
            //    cmd = new SqlCommand();
            //    cmd.Connection = conn;

            //    for (int i = 0; i < sql.Count; i++)
            //    {
            //        cmd.CommandText = sql[i].ToString();
            //        sqlText = cmd.CommandText;
            //        //SqlCommand cmd = new SqlCommand(sql[i].ToString(), conn);
            //        cmd.ExecuteNonQuery();
            //    }
            //    SqlServerConnectionStatus = true;
            //    result = true;
            //}
            //catch (Exception e)
            //{
            //    errorMessage = e.ToString();
            //    TestConnection();
            //    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sqlText + "\r\n�����ַ�����\r\n" + connectionString);
            //}
            //finally
            //{
            //    if (conn.State == ConnectionState.Open)
            //        conn.Close();
            //}
            //return result;
        }
        /// <summary>
        /// ִ��SQL���ʵ��Insert Update Delete ����
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string sql, string connectionString, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }



        /// <summary>
        /// ִ��SQL�󣬷��ص�һ�У���һ�еĽ��
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="count"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteOutNum(string sql, string connectionString, out int count, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                count = 0;
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// ִ�б�׼SQL��ѯ��䣬���ؼ�¼��
        /// </summary>
        /// <param name="sql">��׼SQL��ѯ���</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="ds">��ѯ�����¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteDataset(string sql, string connectionString, out DataSet ds, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(sql, conn);
                        dsCommand.Fill(ds);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// ִ�б�׼SQL��ѯ��䣬���ؼ�¼��
        /// </summary>
        /// <param name="sql">��׼SQL��ѯ���</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="dt">��ѯ�����¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteDataTable(string sql, string connectionString, out DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(sql, conn);
                        dsCommand.Fill(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// ִ�б�׼SQL��ѯ��䣬���ؼ�¼��
        /// </summary>
        /// <param name="sql">��׼SQL��ѯ���</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="dr">��ѯ�����¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteDataReader(string sql, string connectionString, out SqlDataReader dr, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            dr = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
            }

            return result;
        }

        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ�������Ҫ���ز�ѯ�ļ�¼���ĵ��ã�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="sqlParameters">�洢���̲�������</param>
        /// <param name="ds">�洢�������淵�صļ�¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, out DataSet ds, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                        dsCommand.SelectCommand.CommandTimeout = 0;
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }

                        dsCommand.Fill(ds);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, out DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            //ds = new DataSet();
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                        dsCommand.SelectCommand.CommandTimeout = 0;
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }

                        dsCommand.Fill(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ�������Ҫ���ز�ѯ�ļ�¼���ĵ��ã�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="startRecord">���俪ʼ�Ĵ��㿪ʼ�ļ�¼��</param>
        /// <param name="maxRecords">Ҫ����������¼��</param>
        /// <param name="sqlParameters">�洢���̲�������</param>
        /// <param name="ds">�洢�������淵�صļ�¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, int startRecord, int maxRecords, ref SqlParameter[] sqlParameters, out DataSet ds, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                        if(sqlParameters!=null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        dsCommand.Fill(ds, startRecord, maxRecords, "srcTable");
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ���������Ҫ���ز�ѯ�ļ�¼���ĵ��ã�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="sqlParameters">�洢���̲�������</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procedureName, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.CommandType = CommandType.StoredProcedure;
                        if(sqlParameters!=null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                cmd.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters,out long index, out string errorMessage)
        {
            bool result = false;
            index = 0;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procedureName, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                cmd.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        index = Convert.ToInt64(  cmd.ExecuteScalar());
                        //cmd.ExecuteNonQuery();
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ�������Ҫ���ز�ѯ�ļ�¼���ĵ��ã�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// 
        /// <param name="ds">�洢�������淵�صļ�¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, out DataSet ds, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {

                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

                        dsCommand.Fill(ds);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ�������Ҫ���ز�ѯ�ļ�¼���ĵ��ã�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// 
        /// <param name="dt">�洢�������淵�صļ�¼��</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, out DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {

                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

                        dsCommand.Fill(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Insert Delete Update 带参数的Sql语句 1001
        /// 无返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool ExecuteInsertMesWork(string sql, ref SqlParameter[] sqlParameters, string connectionString,out string err)
        {
            bool result = false;
            err = "OK"+ sql;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = sql;
                    if (sqlParameters != null)
                    {
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            comm.Parameters.Add(sqlParameters[i]);
                        }
                    }
                    comm.ExecuteNonQuery();
                    result = true;
                }
                catch (Exception e)
                {
                    err = e.ToString();
                    ApplicationLog.WriteLog(e, "\r\nExecuteSqlMesWork\r\n" + sql + "\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }


        /// <summary>
        /// Insert Delete Update 带参数的Sql语句 1001
        /// 无返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="connectionString"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ExecuteInsertMesWork(string sql, ref SqlParameter[] sqlParameters, string connectionString,out long id,out string err)
        {
            bool result = false;
            err = "OK"+sql;
            id = -1;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = sql + " select @@IDENTITY ";
                    if (sqlParameters != null)
                    {
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            comm.Parameters.Add(sqlParameters[i]);
                        }
                    }
                    id = Convert.ToInt64(comm.ExecuteScalar());
                    result = true;
                }
                catch (Exception e)
                {
                    err = e.ToString();
                    ApplicationLog.WriteLog(e, "\r\nExecuteSqlMesWork\r\n" + sql + "\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Insert Delete Update 带参数的Sql语句 1003
        /// 无返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="connectionString"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static bool ExecuteInsertMesWork(string sql, ref SqlParameter[] sqlParameters, string connectionString,out object index)
        {
            bool result = false;
            index = -1;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = sql;
                    if (sqlParameters != null)
                    {
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            comm.Parameters.Add(sqlParameters[i]);
                        }
                    }
                    index = comm.ExecuteScalar();
                    result = true;
                }
                catch (Exception e)
                {
                    ApplicationLog.WriteLog(e, "\r\nExecuteSqlMesWork\r\n" + sql + "\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Select  带参数的Sql语句   1002
        /// 无返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="connectionString"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExecuteSelectMesWork(string sql, ref SqlParameter[] sqlParameters, string connectionString, out DataTable dt,out string err)
        {
            bool result = false;
            err = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter(sql, conn))
                    {
                        if(sqlParameters!=null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        dsCommand.Fill(dt);
                    }
                    result = true;
                }
                catch (Exception e)
                {
                    err = e.ToString();
                    ApplicationLog.WriteLog(e, "\r\nExecuteSelectMesWork\r\n" + sql + "\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 执行SQL语句，增、删、改，无返回
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string sql, string connectionString, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 执行SQL语句，查询，返回表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="dt"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string sql, string connectionString, out DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(sql, conn);
                        dsCommand.Fill(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// 执行带参数的SQL语句，完成增、删、改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string sql, string connectionString, ref SqlParameter[] sqlParameters, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                cmd.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// Select  带参数的Sql语句
        /// 无返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="connectionString"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string sql, ref SqlParameter[] sqlParameters, string connectionString, out DataTable dt, out string err)
        {
            bool result = false;
            err = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter(sql, conn))
                    {
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        dsCommand.Fill(dt);
                    }
                    result = true;
                }
                catch (Exception e)
                {
                    err = e.ToString();
                    ApplicationLog.WriteLog(e, "\r\nExecuteSelectMesWork\r\n" + sql + "\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// �Ա����ʽ�������ݵ����ݿ�
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="connectionString"></param>
        /// <param name="dt"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteSQL(string sql, ref SqlParameter[] sqlParameters, string connectionString, DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {


                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.InsertCommand = new SqlCommand(sql, conn);

                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            sqlParameters[i].IsNullable = true;
                            dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
                        }
                        if (dt == null) return false;
                        dt.AcceptChanges();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i].SetAdded();
                        }
                        dsCommand.Update(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    //ApplicationConfig.ConnectionStringPIS_NEW_Value = "0";
                    TestConnection();
                    errorMessage = e.ToString();
                    ApplicationLog.WriteLog(e, "\r\nExecuteSQL\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ������������ݵ����ݱ�ĵ����ݿ�ĵ��ã�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="sqlParameters">�洢���̲�������</param>
        /// <param name="dt">�������ݵ����ݱ�</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.InsertCommand = new SqlCommand(procedureName, conn);
                        dsCommand.InsertCommand.CommandType = CommandType.StoredProcedure;
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
                        }
                        if (dt == null) return false;
                        dt.AcceptChanges();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i].SetAdded();
                        }
                        dsCommand.Update(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        public static bool ExecuteSqlCmd(string sqlCmd, string connectionString, ref SqlParameter[] sqlParameters, DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.InsertCommand = new SqlCommand(sqlCmd, conn);
                        dsCommand.InsertCommand.CommandType = CommandType.Text;
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
                        }
                        if (dt == null) return false;
                        dt.AcceptChanges();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i].SetAdded();
                        }
                        dsCommand.Update(dt);
                    }
                    SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteSqlCmd\r\n�洢���̣�\r\n" + sqlCmd + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

       

        
        /// <summary>
        /// �ṩͨ�õĵ��ô洢���̵ķ������������ݣ�
        /// </summary>
        /// <param name="procedureName">�洢��������</param>
        /// <param name="connectionString">�����ַ���</param>
        /// <param name="sqlParameters">�洢���̲�������</param>
        /// <param name="dt">�������ݵ����ݱ�</param>
        /// <param name="errorMessage">������Ϣ</param>
        /// <returns>����ֵ��true��ʾ��ִ�гɹ���false��ʾִ��ʧ��</returns>
        public static bool ExecuteStoredProcedure_Update(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, DataTable dt, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.UpdateCommand = new SqlCommand(procedureName, conn);
                        dsCommand.UpdateCommand.CommandType = CommandType.StoredProcedure;
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            dsCommand.UpdateCommand.Parameters.Add(sqlParameters[i]);
                        }
                        if (dt == null) return false;
                        dt.AcceptChanges();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i].SetAdded();
                        }
                        dsCommand.Update(dt);
                    }
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// ���Ӽ�¼�󣬷����Զ����
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="newID"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery_newID(string sql, out long newID)
        {
            sql += ";select @@identity";
            string connectionString;
            connectionString = ApplicationConfig.ConnectionString_MES;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        conn.Open();
                        newID = Convert.ToInt64(cmd.ExecuteScalar());
                    }
                    return true;
                }
                catch (Exception e)
                {
                    newID = 0;
                    TestConnection();
                    ApplicationLog.WriteLog(e, "\r\n ExecuteNonQuery_newID\r\n�洢���̣�\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }
    }

}