// Decompiled with JetBrains decompiler
// Type: ChengZhongModbus.SQLHelper
// Assembly: ChengZhongModbusIQC, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
// MVID: 335383E6-9854-4BCE-8CD7-BF94F8E1A377
// Assembly location: C:\Users\Administrator\Desktop\ChengZhongModbusIQC\ChengZhongModbusIQC.exe

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

 

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

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

    public SQLHelper(string server, string userID, string password, string database)
    {
        this._transactionTaskList = new Queue<SQLHelper.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)
    {
        this._connection4Tran = this._transaction == null && this._connection4Tran == null ? new SqlConnection(this._connectionString) : throw new Exception("要开始一个新的事务，请先完成当前事务!");
        this._connection4Tran.Open();
        this._transaction = this._connection4Tran.BeginTransaction(isolationLevel);
        this._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 = (SqlTransaction)null;
        this._connection4Tran = (SqlConnection)null;
    }

    public object Clone() => (object)new SQLHelper(this._connectionString);

    public void CommitTransaction()
    {
        try
        {
            if (this._transactionTaskList.Count > 0)
            {
                foreach (SQLHelper.SqlTask transactionTask in this._transactionTaskList)
                {
                    using (SqlCommand sqlCommand = new SqlCommand(transactionTask.Text, this._connection4Tran))
                    {
                        sqlCommand.CommandType = transactionTask.CommandType;
                        if (transactionTask.Parameters != null)
                        {
                            foreach (SqlParameter parameter in transactionTask.Parameters)
                                sqlCommand.Parameters.Add(parameter);
                        }
                        sqlCommand.Transaction = this._transaction;
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            this._transaction.Commit();
        }
        catch (Exception ex)
        {
            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 = (SqlTransaction)null;
            this._connection4Tran = (SqlConnection)null;
        }
    }

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

    public DataSet ExecuteDataSet(string sql, CommandType commandType)
    {
        return this.ExecuteDataSet(sql, commandType, (SqlParameter[])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 selectCommand = new SqlCommand(sql, connection))
            {
                selectCommand.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                        selectCommand.Parameters.Add(parameter);
                }
                new SqlDataAdapter(selectCommand).Fill(dataSet);
            }
        }
        return dataSet;
    }

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

    public DataTable ExecuteDataTable(string sql, CommandType commandType)
    {
        return this.ExecuteDataTable(sql, commandType, (SqlParameter[])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 selectCommand = new SqlCommand(sql, connection))
            {
                selectCommand.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                        selectCommand.Parameters.Add(parameter);
                }
                new SqlDataAdapter(selectCommand).Fill(dataTable);
            }
        }
        return dataTable;
    }

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

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

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

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

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

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

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

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

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

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

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

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

    public bool SaveDataToDB(DataTable dataTable)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                using (SqlCommand selectCommand = new SqlCommand("select top 0 * from " + dataTable.TableName, connection))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                    SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(adapter);
                    adapter.UpdateBatchSize = 100;
                    if (dataTable.GetChanges() != null)
                        adapter.Update(dataTable);
                }
            }
        }
        catch (Exception ex)
        {
            return false;
        }
        return true;
    }

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

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

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

    public void Dispose()
    {
        this._connection4Tran = (SqlConnection)null;
        this._transaction = (SqlTransaction)null;
    }

    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)
        {
            this.Text = text;
            this.CommandType = commandType;
            this.Parameters = paras;
        }
    }
}