using System.Collections.Generic;

namespace DataexchangeServer
{
    using System;
    using System.Collections;
    using System.Collections.Specialized;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Text.RegularExpressions;

    public class SQLHelper : ICloneable, IDisposable
    {
        private SqlConnection _connection4Tran;
        private readonly string _connectionString;
        private SqlTransaction _transaction;
        private readonly Queue<SqlTask> _transactionTaskList;

        public SQLHelper(string connectionString)
        {
            _transactionTaskList = new Queue<SqlTask>();
            this._connectionString = connectionString;
        }

        public SQLHelper(string server, string userID, string password, string database)
        {
            _transactionTaskList = new Queue<SqlTask>();
            if (database == string.Empty)
            {
                this._connectionString = "Data Source=" + server + ";User ID=" + userID + ";Password=" + password;
            }
            else
            {
                this._connectionString = "Data Source=" + server + ";User ID=" + userID + ";Password=" + password + ";Initial Catalog=" + database;
            }
        }

        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            if ((this._transaction != null) || (this._connection4Tran != null))
            {
                throw new Exception("要开始一个新的事务，请先完成当前事务!");
            }
            this._connection4Tran = new SqlConnection(this._connectionString);
            this._connection4Tran.Open();
            this._transaction = this._connection4Tran.BeginTransaction(isolationLevel);
            _transactionTaskList.Clear();
        }

        public void CancelTransaction()
        {
            if (this._transaction != null)
            {
                this._transaction.Dispose();
            }
            if (this._connection4Tran != null)
            {
                this._connection4Tran.Close();
            }
            if (this._connection4Tran != null)
            {
                this._connection4Tran.Dispose();
            }
            this._transaction = null;
            this._connection4Tran = null;
        }

        public object Clone()
        {
            return new SQLHelper(this._connectionString);
        }

        public void CommitTransaction()
        {
            try
            {
                if (_transactionTaskList.Count > 0)
                {
                    foreach (SqlTask sqlTask in _transactionTaskList)
                    {
                        using (var command = new SqlCommand(sqlTask.Text, _connection4Tran))
                        {
                            command.CommandType = sqlTask.CommandType;
                            if (sqlTask.Parameters != null)
                            {
                                foreach (SqlParameter parameter in sqlTask.Parameters)
                                {
                                    command.Parameters.Add(parameter);
                                }
                            }
                            command.Transaction = _transaction;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                this._transaction.Commit();
            }
            catch (Exception)
            {
                this._transaction.Rollback();
            }
            finally
            {
                if (this._transaction != null)
                {
                    this._transaction.Dispose();
                }
                if (this._connection4Tran != null)
                {
                    this._connection4Tran.Close();
                }
                if (this._connection4Tran != null)
                {
                    this._connection4Tran.Dispose();
                }
                this._transaction = null;
                this._connection4Tran = null;
            }
        }

        public DataSet ExecuteDataSet(string sql)
        {
            return this.ExecuteDataSet(sql, CommandType.Text, null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType)
        {
            return this.ExecuteDataSet(sql, commandType, null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            DataSet dataSet = new DataSet(Guid.NewGuid().ToString());
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    new SqlDataAdapter(command).Fill(dataSet);
                }
            }
            return dataSet;
        }

        public DataTable ExecuteDataTable(string sql)
        {
            return this.ExecuteDataTable(sql, CommandType.Text, null);
        }

        public DataTable ExecuteDataTable(string sql, CommandType commandType)
        {
            return this.ExecuteDataTable(sql, commandType, null);
        }

        public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    new SqlDataAdapter(command).Fill(dataTable);
                }
            }
            return dataTable;
        }

        public int ExecuteNonQuery(string sql)
        {
            return this.ExecuteNonQuery(sql, CommandType.Text, null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType)
        {
            return this.ExecuteNonQuery(sql, commandType, null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            return this.ExecuteNonQuery(sql, commandType, parameters, false);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters, bool joinTransaction)
        {
            int num;
            SqlCommand command;
            if (joinTransaction)
            {
                if ((this._transaction == null) || (this._connection4Tran == null))
                {
                    throw new Exception("事务未初始化！");
                }
               
                _transactionTaskList.Enqueue(new SqlTask(sql, commandType, parameters));
            }
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                using (command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    connection.Open();
                    num = command.ExecuteNonQuery();
                }
            }
            return num;
        }

        public SqlDataReader ExecuteReader(string sql)
        {
            return this.ExecuteReader(sql, CommandType.Text, null);
        }

        public SqlDataReader ExecuteReader(string sql, CommandType commandType)
        {
            return this.ExecuteReader(sql, commandType, null);
        }

        public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(this._connectionString);
            SqlCommand command = new SqlCommand(sql, connection)
            {
                CommandType = commandType
            };
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public object ExecuteScalar(string sql)
        {
            return this.ExecuteScalar(sql, CommandType.Text, null);
        }

        public object ExecuteScalar(string sql, CommandType commandType)
        {
            return this.ExecuteScalar(sql, commandType, null);
        }


        public object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            object obj2;
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    connection.Open();
                    obj2 = command.ExecuteScalar();
                }
            }
            return obj2;
        }

        public DataTable GetDatabases()
        {
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                connection.Open();
                return connection.GetSchema("Databases");
            }
        }

        public static ArrayList GetServerList()
        {
            ArrayList list = new ArrayList();
            foreach (DataRow row in SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
            {
                list.Add(row[0].ToString());
            }
            return list;
        }

        public DataTable GetTables()
        {
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                connection.Open();
                return connection.GetSchema("Tables");
            }
        }

        public bool SaveDataToDB(DataSet dataSet)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(this._connectionString))
                {
                    foreach (DataTable table in dataSet.Tables)
                    {
                        using (SqlCommand command = new SqlCommand("SELECT * FROM " + table.TableName + " WHERE 1<1", connection))
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                            adapter.UpdateBatchSize = 100;
                            DataTable tbchange = table.GetChanges();
                            if (tbchange != null)
                                adapter.Update(table.GetChanges());
                        }
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        public bool SaveDataToDB(DataTable dataTable)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(this._connectionString))
                {
                    using (SqlCommand command = new SqlCommand("select top 0 * from " + dataTable.TableName, connection))
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter(command);

                        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                        adapter.UpdateBatchSize = 100;
                        DataTable tbchange = dataTable.GetChanges();
                        if (tbchange != null)
                            adapter.Update(dataTable);
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }


        public bool SaveDataToDB(DataSet oldDataSet, DataSet newDataSet)
        {
            //bool flag;
            oldDataSet.Merge(newDataSet, false);
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    foreach (DataTable table in oldDataSet.Tables)
                    {
                        using (SqlCommand command = new SqlCommand("SELECT * FROM " + table.TableName + " WHERE 1<1", connection, transaction))
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                            adapter.UpdateBatchSize = 500;
                            adapter.Update(table);
                        }
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    if (transaction != null)
                    {
                        transaction.Dispose();
                    }
                }
            }
            //return flag;
        }

        public bool SaveDataToDB(DataTable oldDataTable, DataTable newDataTable)
        {
            bool flag;
            oldDataTable.Merge(newDataTable, false);
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                using (SqlCommand command = new SqlCommand("SELECT * FROM " + oldDataTable.TableName + " WHERE 1<1", connection))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                    adapter.UpdateBatchSize = 100;
                    adapter.Update(oldDataTable);
                    flag = true;
                }
            }
            return flag;
        }

        public bool TestConnection()
        {
            bool flag;
            try
            {
                using (SqlConnection connection = new SqlConnection(this._connectionString))
                {
                    connection.Open();
                    connection.Close();
                    flag = true;
                }
            }
            catch (Exception)
            {
                flag = false;
            }
            return flag;
        }

        #region IDisposable 成员

        public void Dispose()
        {
            _connection4Tran = null;
            _transaction = null;
        }

        #endregion

        private class SqlTask
        {
            public string Text { get; private set; }
            public CommandType CommandType { get; private set; }
            public SqlParameter[] Parameters { get; private set; }

            public SqlTask(string text, CommandType commandType, SqlParameter[] paras)
            {
                Text = text;
                CommandType = commandType;
                Parameters = paras;
            }
        }

        //public bool Save(DataTable Table, bool UpdateTableStatus, bool UseTransaction, bool ShowMsg)
        //{
        //    //如果不需要保存则退出。
        //    if (Table == null  )
        //        return true;

        //    SqlDataAdapter dataAdapter = new SqlDataAdapter();

        //        dataAdapter.UpdateCommand = GetUpdateCmd(Table);
        //        dataAdapter.InsertCommand = GetInsertCmd(Table);
        //        dataAdapter.DeleteCommand = GetDeleteCmd(Table);


        //    if (DBTransaction != null)
        //    {
        //        if (dataAdapter.SelectCommand != null) dataAdapter.SelectCommand.Transaction = DBTransaction;
        //        if (dataAdapter.DeleteCommand != null) dataAdapter.DeleteCommand.Transaction = DBTransaction;
        //        if (dataAdapter.InsertCommand != null) dataAdapter.InsertCommand.Transaction = DBTransaction;
        //        if (dataAdapter.UpdateCommand != null) dataAdapter.UpdateCommand.Transaction = DBTransaction;
        //    } 

        //    //更新数据
        //    try
        //    {
        //        //启动事务
        //        if (UseTransaction == true)
        //            StartTransaction();

        //        DataTable tb_changes = Table.GetChanges();
        //        if (tb_changes != null)
        //            dataAdapter.Update(tb_changes);
        //        else if (HandCreateCmd == true)
        //            dataAdapter.Update(Table);
        //        //提交事务
        //        if (UseTransaction == true)
        //            CommitTransaction();

        //        //修改表状态 如果前面的处理出错,那么这里也不会执行,Datatable将继续保留状态.
        //        if (UpdateTableStatus == true)
        //            Table.AcceptChanges();

        //        ExecuteExcetion = null;
        //    }
        //    catch (Exception e)
        //    {
        //        string errorMsg = "";
        //        //2627表示主键重复，才需要重取单号。
        //        if (e is SqlException)
        //        {
        //            SqlException sqlExcept = e as SqlException;
        //            SaveKeyExist = (sqlExcept.Number == 2627);
        //            if (sqlExcept.Number == 2627)
        //                errorMsg = "当前数据表已有此记录，请确认后再保存!";
        //            else
        //                errorMsg = ((SqlException)e).Message;
        //        }

        //        if (e is DBConcurrencyException)
        //        {
        //            DBConcurrencyException dbexception = (e as DBConcurrencyException);
        //            errorMsg = dbexception.Row.RowError;
        //            if (errorMsg.Contains("影响") == true && dbexception.Row.RowState == DataRowState.Deleted)
        //                errorMsg = "当前数据行已删除,请重新查询!";
        //            if (errorMsg.Contains("影响") == true && dbexception.Row.RowState == DataRowState.Modified)
        //                errorMsg = "当前数据行已修改,请重新查询!";
        //        }

        //        //
        //        ShowMsg = ShowMsg || ShowMsgWhenSaveChildTableOnError;
        //        ExecuteExcetion = e;

        //        //保存失败回滚事务
        //        if (UseTransaction == true)
        //            RollbackTransaction();

        //        errorMsg = (errorMsg == "") ? GetErrorMsg(e) : errorMsg;
        //        WriteLog(Format("保存{0}失败,错误信息如下:{1}", Table.TableName, errorMsg), ShowMsg);
        //        return false;
        //    }
        //    finally
        //    {
        //        Table.State = OptionState.Browse;
        //    }

        //    if (ShowMsg == true || MustSaveLog == true)
        //    {
        //        /* 关于ShowMsg参数的使用说明：
        //         * A方法：Save(bool UseTransaction, bool ShowMsg)；
        //         * B方法：Save(DataTableExt Table,bool UpdateTableStatus, bool UseTransaction, bool ShowMsg)；
        //         * 
        //         * A方法调用B方法时,是不需要提示"保存成功"的信息，所以A方法传的ShowMsg=false，但A希望在调用B方法出错时，
        //         * 提示错误信息"保存**失败.."，所以原来B方法Catch中硬编码 ShowMsg=true；在执行改方法时,不管是否需要提示,系统都会提示信息(这有违背方法本意)。
        //         * 
        //         * 现在希望能利正确使用ShowMsg参数，所以引入了ShowMsgWhenSaveChildTableOnError属性(默认True)。
        //         * 当ShowMsgWhenSaveChildTableOnError等于True或ShowMsg等于True时,Catch中就会提示错误信息。
        //         *
        //         * 单据类BillClass保存时，由于特殊处理，希望过程中不提示错误信息，所以在保存时Save(UseTransaction, ShowMsg==false)和设置ShowMsgWhenSaveChildTableOnError=false。                 
        //         */
        //        WriteLog(string.Format("保存{0}成功!", Table.TableName), ShowMsg);
        //    }
        //    return true;
        //}

        // <summary>
        // 得到更新命令
        // </summary>
        // <returns></returns>
        //public SqlCommand GetUpdateCmd(DataTable MyTable, SqlConnection DBConn)
        //{ 
        //    生成更新语句
        //    string strSQL = string.Format("Update {0} Set ", MyTable.TableName);
        //    foreach (DataColumn col in MyTable.Columns)
        //    { 
        //        strSQL = string.Format("{0} {1}=@{1},", strSQL, col.ColumnName);
        //    }
        //    strSQL = strSQL.Substring(0, strSQL.Length - 1);

        //    StringCollection KeyList = new StringCollection();
        //    KeyList.Add("taskid");
        //    根据主键设置Where条件
        //    string strWhere = "";
        //    foreach (string Key in KeyList)
        //    {
        //        strWhere = strWhere == "" ? string.Format("{0}=@{0}", Key) : string.Format("{0} and {1}=@{1}", strWhere, Key);
        //    }

        //    strSQL = string.Format("{0} where {1}", strSQL, strWhere);
        //    SqlCommand updateCmd = new SqlCommand(strSQL, DBConn);

        //    添加参数
        //    foreach (DataColumn col in MyTable.Columns)
        //    {
        //        排除不用保存字段。
        //        SqlDbType SQLType = DataTypeMap.CSTypeToSqlType(col.DataType.Name);
        //        int Size = (col.MaxLength > 0) ? col.MaxLength : 100;
        //        updateCmd.Parameters.Add("@" + col.ColumnName, SQLType, Size, col.ColumnName);
        //    }

        //    return updateCmd;
        //}


        ///// <summary>
        ///// 得到插入命令
        ///// </summary>
        ///// <returns></returns>
        //public SqlCommand GetInsertCmd(DataTable MyTable)
        //{
        //    //取得主键
        //    List<string> KeyList = new List<string>();
        //    KeyList.AddRange(MyTable.KeyField.Split(';'));
        //    if (KeyList.Count <= 0)
        //        return null;

        //    //取得不需要更新的字段列表
        //    StringCollection NotSaveFields = new StringCollection();
        //    NotSaveFields.AddRange(MyTable.NotSaveFields.Split(';'));

        //    //生成字段列表和值列表
        //    string strFieldList = "";
        //    string strValueList = "";
        //    foreach (DataColumn col in MyTable.Columns)
        //    {
        //        //排除主键和不用保存字段。
        //        if (NotSaveFields.Contains(col.ColumnName) == true)
        //            continue;
        //        strFieldList = strFieldList == "" ? col.ColumnName : string.Format("{0},{1}", strFieldList, col.ColumnName);
        //        strValueList = strValueList == "" ? "@" + col.ColumnName : string.Format("{0},@{1}", strValueList, col.ColumnName);
        //    }
        //    string strSQL = string.Format("Insert Into {0} ({1}) Values({2})", MyTable.UpdateTable, strFieldList, strValueList);

        //    //创建命令
        //    SqlCommand InsertCmd = new SqlCommand(strSQL, DBConn);

        //    //添加参数
        //    foreach (DataColumn col in MyTable.Columns)
        //    {
        //        //排除主键和不用保存字段。
        //        if (NotSaveFields.Contains(col.ColumnName) == true)
        //            continue;
        //        SqlDbType SQLType = DataTypeMap.CSTypeToSqlType(col.DataType.Name);
        //        int Size = (col.MaxLength > 0) ? col.MaxLength : 100;

        //        InsertCmd.Parameters.Add("@" + col.ColumnName, SQLType, Size, col.ColumnName);
        //    }
        //    return InsertCmd;
        //}

        ///// <summary>
        ///// 得到删除命令
        ///// </summary>
        ///// <returns></returns>
        //public SqlCommand GetDeleteCmd(DataTable MyTable)
        //{
        //    //取得主键
        //    List<string> KeyList = new List<string>();
        //    KeyList.AddRange(MyTable.KeyField.Split(';'));
        //    if (KeyList.Count <= 0)
        //        return null;

        //    //生成更新语句
        //    string strSQL = string.Format("Delete {0} ", MyTable.UpdateTable);

        //    //根据主键设置Where条件
        //    string strWhere = "";
        //    foreach (string Key in KeyList)
        //    {
        //        strWhere = strWhere == "" ? string.Format("{0}=@{0}", Key) : string.Format("{0} and {1}=@{1}", strWhere, Key);
        //    }
        //    strSQL = string.Format("{0} where {1}", strSQL, strWhere);
        //    SqlCommand DeleteCmd = new SqlCommand(strSQL, DBConn);

        //    //添加参数
        //    foreach (string Key in KeyList)
        //    {
        //        DataColumn col = MyTable.Columns[Key];
        //        SqlDbType SQLType = DataTypeMap.CSTypeToSqlType(col.DataType.Name);
        //        int Size = (col.MaxLength > 0) ? col.MaxLength : 100;
        //        DeleteCmd.Parameters.Add("@" + col.ColumnName, SQLType, Size, col.ColumnName);
        //    }

        //    return DeleteCmd;
        //} 
    }
}