// 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;
|
}
|
}
|
}
|
