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