protected override void EnableTableConstraints(DataTable dataTable, DbTransaction dbTransaction)
{
MySqlCommand sqlCommand = (MySqlCommand)CreateDbCommand("SET foreign_key_checks = 1;");
sqlCommand.Connection = (MySqlConnection)dbTransaction.Connection;
sqlCommand.Transaction = (MySqlTransaction)dbTransaction;
sqlCommand.ExecuteNonQuery();
}
/// <summary>
/// 在事务中执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
{
var cmd = new MySqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
var val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 在事务中执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
{
var cmd = new MySqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
var da = new MySqlDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
/// <summary>
/// 在事务中执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
#if MYSQL
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
#else
throw new SystemException("DBUtility未打开MYSQL编译开关。");
#endif
}
/// <summary>
/// 使用给定参数创建DbCommand对象
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="trans">数据库事务</param>
/// <param name="sqlText">含有问号占位符的SQL命令</param>
/// <param name="parameters">SQL命令中的参数值</param>
/// <returns>返回DbCommand对象</returns>
public override DbCommand PrepareCommand(DbConnection conn, DbTransaction trans, string sqlText, params object[] parameters)
{
DbCommand cmd = new MySqlCommand();
if (conn != null) cmd.Connection = conn;
if (trans != null) cmd.Transaction = trans;
cmd.CommandText = sqlText;
if (parameters != null && parameters.Length != 0)
{
AddParamsToCmd(cmd as MySqlCommand, parameters);
}
return cmd;
}
private static void PrepareCommand(
DbCommand command,
DbConnection connection,
DbTransaction transaction,
CommandType commandType,
string commandText,
DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
command.CommandType = commandType;
command.CommandText = commandText;
command.Connection = connection;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
if (commandParameters != null) { AttachParameters(command, commandParameters); }
}
protected static FR_L5US_GBPfToID_1419_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5US_GBPfToID_1419 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
var returnStatus = new FR_L5US_GBPfToID_1419_Array();
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
var commandLocation = "CL5_APOAdmin_User.Atomic.Manipulation.SQL.cls_Get_BusinessParticipant_for_Tenant_or_ID.sql";
command.CommandText = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(commandLocation)).ReadToEnd();
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ticket", securityTicket);
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ID", Parameter.ID);
List <L5US_GBPfToID_1419> results = new List <L5US_GBPfToID_1419>();
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
try
{
reader.SetOrdinals(new string[] { "CMN_BPT_BusinessParticipantID", "Tenant_RefID", "DisplayName", "IsNaturalPerson", "IsCompany", "IfNaturalPerson_CMN_PER_PersonInfo_RefID", "IfCompany_CMN_COM_CompanyInfo_RefID", "DisplayImage_RefID", "DefaultLanguage_RefID", "DefaultCurrency_RefID", "LastContacted_Date", "LastContacted_ByBusinessPartner_RefID", "Audit_UpdatedByAccount_RefID", "Audit_UpdatedOn", "Audit_CreatedByAccount_RefID" });
while (reader.Read())
{
L5US_GBPfToID_1419 resultItem = new L5US_GBPfToID_1419();
//0:Parameter CMN_BPT_BusinessParticipantID of type Guid
resultItem.CMN_BPT_BusinessParticipantID = reader.GetGuid(0);
//1:Parameter Tenant_RefID of type Guid
resultItem.Tenant_RefID = reader.GetGuid(1);
//2:Parameter DisplayName of type String
resultItem.DisplayName = reader.GetString(2);
//3:Parameter IsNaturalPerson of type bool
resultItem.IsNaturalPerson = reader.GetBoolean(3);
//4:Parameter IsCompany of type bool
resultItem.IsCompany = reader.GetBoolean(4);
//5:Parameter IfNaturalPerson_CMN_PER_PersonInfo_RefID of type Guid
resultItem.IfNaturalPerson_CMN_PER_PersonInfo_RefID = reader.GetGuid(5);
//6:Parameter IfCompany_CMN_COM_CompanyInfo_RefID of type Guid
resultItem.IfCompany_CMN_COM_CompanyInfo_RefID = reader.GetGuid(6);
//7:Parameter DisplayImage_RefID of type Guid
resultItem.DisplayImage_RefID = reader.GetGuid(7);
//8:Parameter DefaultLanguage_RefID of type Guid
resultItem.DefaultLanguage_RefID = reader.GetGuid(8);
//9:Parameter DefaultCurrency_RefID of type Guid
resultItem.DefaultCurrency_RefID = reader.GetGuid(9);
//10:Parameter LastContacted_Date of type DateTime
resultItem.LastContacted_Date = reader.GetDate(10);
//11:Parameter LastContacted_ByBusinessPartner_RefID of type Guid
resultItem.LastContacted_ByBusinessPartner_RefID = reader.GetGuid(11);
//12:Parameter Audit_UpdatedByAccount_RefID of type Guid
resultItem.Audit_UpdatedByAccount_RefID = reader.GetGuid(12);
//13:Parameter Audit_UpdatedOn of type String
resultItem.Audit_UpdatedOn = reader.GetString(13);
//14:Parameter Audit_CreatedByAccount_RefID of type Guid
resultItem.Audit_CreatedByAccount_RefID = reader.GetGuid(14);
results.Add(resultItem);
}
}
catch (Exception ex)
{
reader.Close();
throw new Exception("Exception occured durng data retrieval in method cls_Get_BusinessParticipant_for_Tenant_or_ID", ex);
}
reader.Close();
//Load all the dictionaries from the datatables
loader.Load();
returnStatus.Result = results.ToArray();
return(returnStatus);
}
public static DataConnection CreateDataConnection(DbTransaction transaction)
{
return(new DataConnection(_firebirdDataProvider.Value, transaction));
}
public void Save(DbConnection connection, DbTransaction transaction, Composite composite)
{
Save(connection, transaction, composite, true);
}
/// <summary>
/// 在事务中执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
{
return _dbHelper.ExecuteQuery(trans, cmdType, cmdText, cmdParms);
}
public IEnumerable <T> Load <T>(DbConnection connection, DbTransaction transaction, string query, IEnumerable <DbParameter> parameters, Func <T> newModelFunc)
{
return(OnLoad(connection, transaction, query, parameters, newModelFunc));
}
/// <summary>
///
/// </summary>
/// <param name="tran"></param>
/// <param name="commandType"></param>
/// <param name="query"></param>
/// <param name="page"></param>
/// <param name="srcTableName"></param>
/// <param name="dbParamCollection"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(DbTransaction tran, CommandType commandType, string query, Pager page, string srcTableName, DBParamCollection dbParamCollection)
{
DataSet result = new DataSet();
Database dbBase = GetDatabase(tran.Connection.ConnectionString);
DbCommand dbCommand = null;
if (commandType == CommandType.Text)
{
dbCommand = dbBase.GetSqlStringCommand(query);
}
else if (commandType == CommandType.StoredProcedure)
{
dbCommand = dbBase.GetStoredProcCommand(query);
}
AddParamToCommand(dbBase, dbCommand, dbParamCollection);
if (page != null)
{
using (DbDataAdapter dataAdapter = dbBase.GetDataAdapter())
{
dataAdapter.SelectCommand = dbCommand;
dbCommand.Connection = tran.Connection;
dbCommand.Transaction = tran;
dataAdapter.Fill(result, page.StartRecord, page.PageSize, srcTableName);
}
}
else
{
result = dbBase.ExecuteDataSet(dbCommand, tran);
}
return result;
}
/// <summary>
/// 执行SQL，并返回查询到的第一行第一列的值
/// </summary>
/// <param name="tran">当前执行所在事务的事务对象</param>
/// <param name="commandType">执行类型</param>
/// <param name="query">需要执行的SQL语句</param>
/// <returns>返回查询到的第一行第一列的值</returns>
public static object ExecuteScalar(DbTransaction tran, CommandType commandType, string query)
{
object result = ExecuteScalar(tran, commandType, query, null);
return result;
}
public FR_Base Remove(DbConnection Connection, DbTransaction Transaction)
{
return(Remove(Connection, Transaction, null));
}
public FR_Base Remove(DbConnection Connection, DbTransaction Transaction, string ConnectionString)
{
this.IsDeleted = true;
return(this.Save(Connection, Transaction, ConnectionString));
}
public FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID)
{
return(Load(Connection, Transaction, ObjectID, null));
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_TMS_PRO.TMS_PRO_Project_Note_2_Tag.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_AssignmentID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "AssignmentID", ObjectID);
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "AssignmentID", "Tag_RefID", "Project_Note_RefID", "Creation_Timestamp", "Tenant_RefID", "IsDeleted" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter AssignmentID of type Guid
_AssignmentID = reader.GetGuid(0);
//1:Parameter Tag_RefID of type Guid
_Tag_RefID = reader.GetGuid(1);
//2:Parameter Project_Note_RefID of type Guid
_Project_Note_RefID = reader.GetGuid(2);
//3:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(3);
//4:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(4);
//5:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(5);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if (_AssignmentID != Guid.Empty)
{
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
else
{
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
protected FR_Base Save(DbConnection Connection, DbTransaction Transaction, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
bool saveDictionary = false;
bool saveORMClass = !Status_IsAlreadySaved || Status_IsDirty;
//If Status Is Dirty (Meaning the data has been changed) or Status_IsAlreadySaved (Meaning the data is in the database, when loaded) just return
if (saveORMClass == false && saveDictionary == false)
{
return(FR_Base.Status_OK);
}
#region Verify/Create Connections
//Create Connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//Create Transaction if null
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
#region Dictionary Management
//Save dictionary management
if (saveDictionary == true)
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
//Save the dictionary or update based on if it has already been saved to the database
if (Status_IsAlreadySaved)
{
loader.Update();
}
else
{
loader.Save();
}
}
#endregion
#region Command Execution
if (saveORMClass == true)
{
//Retrieve Querry
string Query = "";
if (Status_IsAlreadySaved == true)
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_TMS_PRO.TMS_PRO_Project_Note_2_Tag.SQL.Update.sql")).ReadToEnd();
}
else
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_TMS_PRO.TMS_PRO_Project_Note_2_Tag.SQL.Insert.sql")).ReadToEnd();
}
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = Query;
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "AssignmentID", _AssignmentID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Tag_RefID", _Tag_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Project_Note_RefID", _Project_Note_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Creation_Timestamp", _Creation_Timestamp);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Tenant_RefID", _Tenant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsDeleted", _IsDeleted);
try
{
var dbChangeCount = command.ExecuteNonQuery();
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
}
#endregion
}
catch (Exception ex)
{
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
public int SqlExecute(string sql, DbTransaction trans)
{
return(baseDal.SqlExecute(sql, trans));
}
///<summary>
/// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured
///<summary>
public static FR_L5RS_GRSPfT_1636_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L5RS_GRSPfT_1636 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
return(Invoke(Connection, Transaction, null, Parameter, securityTicket));
}
public void InsertOrUpdateDetails(DbTransaction trans, List<FinDetails> list)
{
string InsSql = @"INSERT INTO FinDetails(FinDate,FinId,Description,EventType,Amount,Association,OrderNo,ItemType,Remark,Currency, Rate, CreatedTime,ModifiedTime)"
+ "values(@FinDate,@FinId,@Description,@EventType,@Amount,@Association,@OrderNo,@ItemType, @Remark, @Currency, @Rate, @CreatedTime, @ModifiedTime)";
string UpdSql = @"update FinDetails set FinDate=@FinDate,Description=@Description,EventType=@EventType,Amount=@Amount,Association=@Association,"
+ "OrderNo=@OrderNo,ItemType=@ItemType,Remark=@Remark,Currency = @Currency, Rate = @Rate, ModifiedTime=@ModifiedTime "
+ "where DetailId = @DetailId";
string ExistRecordSql = "SELECT count(1) FROM FinDetails WHERE DetailId = ";
List<MySqlParameter[]> InsertParameters = new List<MySqlParameter[]>();
List<MySqlParameter[]> UpdateParameters = new List<MySqlParameter[]>();
DateTime CurrentTime = DateTime.Now;
foreach(FinDetails item in list)
{
MySqlParameter[] parameter = new MySqlParameter[]
{
new MySqlParameter("@DetailId",item.DetailId),
new MySqlParameter("@FinId",item.FinId),
new MySqlParameter("@FinDate",item.FinDate),
new MySqlParameter("@Description",item.Description),
new MySqlParameter("@EventType",item.EventType),
new MySqlParameter("@Amount",item.Amount),
new MySqlParameter("@Association",item.Association),
new MySqlParameter("@OrderNo",item.OrderNo),
new MySqlParameter("@ItemType",item.ItemType),
new MySqlParameter("@Currency",item.Currency),
new MySqlParameter("@Rate",item.Rate),
new MySqlParameter("@Remark",item.Remark),
new MySqlParameter("@CreatedTime", CurrentTime),
new MySqlParameter("@ModifiedTime",CurrentTime)
};
int record = Convert.ToInt32(dbHelper.ExecuteScalar(trans, ExistRecordSql + item.DetailId, null));
if (record == 0)
{
InsertParameters.Add(parameter);
}
else
{
UpdateParameters.Add(parameter);
}
}
if (InsertParameters.Count > 0)
{
dbHelper.ExecuteNonQuery(trans, InsSql, InsertParameters);
}
if (UpdateParameters.Count > 0)
{
dbHelper.ExecuteNonQuery(trans, UpdSql, UpdateParameters);
}
}
public static bool Exists(DbConnection connection, DbTransaction transaction, Query query)
{
return(Query.Exists(query, null, connection, transaction));
}
/// <summary>
///
/// </summary>
/// <param name="tran"></param>
/// <param name="commandType"></param>
/// <param name="query"></param>
/// <param name="dbParamCollection"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(DbTransaction tran, CommandType commandType, string query, DBParamCollection dbParamCollection)
{
DataSet result = ExecuteDataSet(tran, commandType, query, null, null, dbParamCollection);
return result;
}
private static bool Exists(Query query, string connectionString, DbConnection connection, DbTransaction transaction)
{
CSV2Core.Core.Interfaces.IManagedConnection managedConnection = new CSV2Core_MySQL.MySQLManagedConnection();
try
{
managedConnection.set(connectionString, connection, transaction);
DbCommand command = managedConnection.manage(query.CreateExistsQuery(TableName));
query.SetParameters(command);
var reader = command.ExecuteReader();
reader.Read();
int resultCount = reader.GetInt32(0);
reader.Close();
managedConnection.commit();
return(resultCount == 1);
}
catch (Exception ex)
{
managedConnection.rollback();
throw;
}
}
/// <summary>
///
/// </summary>
/// <param name="tran"></param>
/// <param name="commandType"></param>
/// <param name="query"></param>
/// <param name="page"></param>
/// <param name="srcTableName"></param>
/// <param name="dbParamCollection"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(DbTransaction tran, CommandType commandType, string query, Pager page, string srcTableName, DBParamCollection dbParamCollection)
{
DataSet result = ExecuteDataSet(tran, commandType, query, page, srcTableName, dbParamCollection);
if (result == null)
{
return null;
}
return result.Tables[0];
}
public static List <ORM_TMS_PRO_Project_Note_2_Tag> Search(DbConnection connection, DbTransaction transaction, Query query)
{
return(Query.Search(query, null, connection, transaction));
}
/// <summary>
/// Invokes the intercept action.
/// </summary>
/// <param name="conn">The conn.</param>
/// <param name="tran">The tran.</param>
protected void InvokeInterceptAction(DbConnection conn, DbTransaction tran)
{
Action<DbConnection, DbTransaction> handler = InterceptAction;
if (handler != null)
handler(conn, tran);
}
/// <summary>
/// 对连接执行 Transact-SQL 语句并返回DataTable
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="CmdParameters">SqlCommand 的参数</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(CommandType CmdType, string CmdText, DbParameter[] CmdParameters, DbTransaction Transaction)
{
this.CreateCmd(CmdType, CmdText.SqlStrTopToLimit(), CmdParameters, (MySqlTransaction)Transaction);
MySqlDataAdapter adapter = new MySqlDataAdapter(this.Cmd);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
this.Dispose();
return dataTable;
}
/// <summary>
/// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
{
return _dbHelper.ExecuteScalar(trans, cmdType, cmdText, cmdParms);
}
/// <summary>
/// 对连接执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="CmdParameters">SqlCommand 的参数</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(CommandType CmdType, string CmdText, DbParameter[] CmdParameters, DbTransaction Transaction)
{
this.CreateCmd(CmdType, CmdText, CmdParameters, (MySqlTransaction)Transaction);
int num2 = 0;
if (Transaction != null)
{
try
{
num2 = this.Cmd.ExecuteNonQuery();
Transaction.Commit();
}
catch (Exception exception1)
{
Exception exception = exception1;
Transaction.Rollback();
}
}
else
{
num2 = this.Cmd.ExecuteNonQuery();
}
this.Close();
this.Dispose();
return num2;
}
public bool Save <T>(T item, DbTransaction parentTransaction) where T : IContract
{
currentTransaction = parentTransaction;
return(Save(item));
}
/// <summary>
/// 将CommandText发送到Connection，并使用CommandBehavior值之一生成一个SqlDataReader
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <param name="ConnOwnership">DataReader完成数据读取时是否关闭</param>
/// <returns>SqlDataReader</returns>
public DbDataReader ExecuteReader(CommandType CmdType, string CmdText, DbTransaction Transaction, bool ConnOwnership)
{
return this.ExecuteReader(CmdType, CmdText, null, (MySqlTransaction)Transaction, ConnOwnership);
}
public void Save(DbConnection connection, DbTransaction transaction, Composite composite, bool shouldUpdateInsertedIds)
{
var newComposites = new List <Composite>();
composite.TraverseDepthFirst((c) =>
{
CompositeContainerAttribute compositeContainerAttribute;
var compositeType = c.GetType();
CompositeModelAttribute compositeModelAttribute;
if ((compositeContainerAttribute = compositeType.FindCustomAttribute <CompositeContainerAttribute>()) != null)
{
var removedIdsProperty = compositeType
.GetProperty(compositeContainerAttribute.CompositeContainerDictionaryPropertyName)
.GetValue(c)
.GetType().GetProperty(nameof(CompositeDictionary <object, Composite> .RemovedIds));
var compositeDictionary = compositeType
.GetProperty(compositeContainerAttribute.CompositeContainerDictionaryPropertyName)
.GetValue(c);
dynamic deletedIds = removedIdsProperty.GetValue(compositeDictionary);
if (deletedIds.Count > 0)
{
var deletedCompositeType = compositeDictionary.GetType().GetGenericArguments()[1];
var deletedModelType = deletedCompositeType.GetField(deletedCompositeType.GetCustomAttribute <CompositeModelAttribute>().ModelFieldName, BindingFlags.Instance | BindingFlags.NonPublic).FieldType;
if (deletedModelType.GetCustomAttribute <NoDbAttribute>() == null)
{
var tableName = deletedModelType.GetCustomAttribute <DataContractAttribute>().Name ?? deletedModelType.Name;
var tableKeyPropertyName = deletedModelType.GetCustomAttribute <KeyPropertyAttribute>().KeyPropertyName;
OnDelete(connection, transaction, tableName, tableKeyPropertyName, deletedIds);
}
dynamic internalCompositeDictionary = compositeType.GetField(compositeType.GetCustomAttribute <CompositeContainerAttribute>().InternalCompositeContainerDictionaryPropertyName, BindingFlags.Instance | BindingFlags.NonPublic).GetValue(c);
internalCompositeDictionary.ClearRemovedIds();
}
}
if ((compositeModelAttribute = compositeType.FindCustomAttribute <CompositeModelAttribute>()) != null)
{
var modelFieldInfo = compositeType.GetField(compositeModelAttribute.ModelFieldName, BindingFlags.Instance | BindingFlags.NonPublic);
if (modelFieldInfo == null)
{
throw new MemberAccessException(Resources.CannotFindCompositeModelProperty);
}
switch (c.State)
{
case CompositeState.Modified:
if (modelFieldInfo.FieldType.GetCustomAttribute <NoDbAttribute>() == null)
{
var dataContractAttribute = modelFieldInfo.FieldType.GetCustomAttribute <DataContractAttribute>();
var modelKeyPropertyAttribute = modelFieldInfo.FieldType.GetCustomAttribute <KeyPropertyAttribute>();
var modelKeyProperty = modelFieldInfo.FieldType.GetProperty(modelKeyPropertyAttribute.KeyPropertyName);
var modelKeyDataMemberAttribute = modelKeyProperty?.GetCustomAttribute <DataMemberAttribute>();
if (dataContractAttribute == null)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, Resources.MustHaveDataContractAttribute, modelFieldInfo.FieldType.Name));
}
if (modelKeyPropertyAttribute == null)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, Resources.MustHaveKeyPropertyAttribute, modelFieldInfo.FieldType.Name));
}
if (modelKeyProperty == null)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, Resources.InvalidPropertyName, modelKeyPropertyAttribute.KeyPropertyName));
}
if (modelKeyDataMemberAttribute == null)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, Resources.MustHaveDataMemberAttribute, modelKeyPropertyAttribute.KeyPropertyName));
}
var dataRow = new Composite[] { c }.ToDataTable().Rows[0];
var columnValues = dataRow.Table.Columns.Cast <DataColumn>().Where(column => column.ColumnName != "__model").ToDictionary(column => column.ColumnName, column => dataRow[column]);
var keyColumnName = modelKeyDataMemberAttribute.Name ?? modelKeyProperty.Name;
var keyValue = dataRow[keyColumnName];
var tableName = dataContractAttribute.Name ?? modelFieldInfo.FieldType.Name;
var tableKeyPropertyName = modelKeyDataMemberAttribute.Name ?? modelKeyProperty.Name;
if (!Regex.IsMatch(tableName, @"^[A-Za-z0-9_]+$"))
{
throw new ArgumentException(string.Format(CultureInfo.CurrentCulture, Resources.InvalidTableName, tableName));
}
if (!Regex.IsMatch(tableKeyPropertyName, @"^[A-Za-z0-9_]+$"))
{
throw new ArgumentException(string.Format(CultureInfo.CurrentCulture, Resources.InvalidColumnName, tableKeyPropertyName));
}
string invalidColumnName = null;
if ((invalidColumnName = columnValues.Keys.FirstOrDefault(column => !Regex.IsMatch(column, @"^[A-Za-z0-9_]+$"))) != null)
{
throw new ArgumentException(string.Format(CultureInfo.CurrentCulture, Resources.InvalidColumnName, invalidColumnName));
}
OnUpdate(connection, transaction, tableName, tableKeyPropertyName, keyValue, columnValues);
c.State = CompositeState.Unchanged;
}
break;
case CompositeState.New:
if (modelFieldInfo.FieldType.GetCustomAttribute <NoDbAttribute>() == null)
{
newComposites.Add(c);
}
break;
default:
break;
}
}
});
if (newComposites.Count() > 0)
{
SaveNewComposites(connection, transaction, newComposites, shouldUpdateInsertedIds);
if (shouldUpdateInsertedIds)
{
UpdateNewKeyValues(composite);
}
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <returns>返回的结果集中第一行的第一列</returns>
public object ExecuteScalar(CommandType CmdType, string CmdText, DbTransaction Transaction)
{
return this.ExecuteScalar(CmdType, CmdText, null, (MySqlTransaction)Transaction);
}
private static List <ORM_TMS_PRO_Project_Note_2_Tag> Search(Query query, string connectionString, DbConnection connection, DbTransaction transaction)
{
CSV2Core.Core.Interfaces.IManagedConnection managedConnection = new CSV2Core_MySQL.MySQLManagedConnection();
List <ORM_TMS_PRO_Project_Note_2_Tag> items;
try
{
managedConnection.set(connectionString, connection, transaction);
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(managedConnection.getConnection(), managedConnection.getTransaction());
DbCommand command = managedConnection.manage(query.CreateSelectQuery(TableName));
query.SetParameters(command);
items = new List <ORM_TMS_PRO_Project_Note_2_Tag>();
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "AssignmentID", "Tag_RefID", "Project_Note_RefID", "Creation_Timestamp", "Tenant_RefID", "IsDeleted" });
while (reader.Read())
{
ORM_TMS_PRO_Project_Note_2_Tag item = new ORM_TMS_PRO_Project_Note_2_Tag();
//0:Parameter AssignmentID of type Guid
item.AssignmentID = reader.GetGuid(0);
//1:Parameter Tag_RefID of type Guid
item.Tag_RefID = reader.GetGuid(1);
//2:Parameter Project_Note_RefID of type Guid
item.Project_Note_RefID = reader.GetGuid(2);
//3:Parameter Creation_Timestamp of type DateTime
item.Creation_Timestamp = reader.GetDate(3);
//4:Parameter Tenant_RefID of type Guid
item.Tenant_RefID = reader.GetGuid(4);
//5:Parameter IsDeleted of type Boolean
item.IsDeleted = reader.GetBoolean(5);
item.Status_IsAlreadySaved = true;
item.Status_IsDirty = false;
items.Add(item);
}
reader.Close();
loader.Load();
managedConnection.commit();
}
catch (Exception ex)
{
managedConnection.rollback();
throw;
}
return(items);
}
///<summary>
/// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured
///<summary>
public static FR_L5US_GBPfToID_1419_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L5US_GBPfToID_1419 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
return(Invoke(Connection, Transaction, null, Parameter, securityTicket));
}
/// <summary>
/// 对连接执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(CommandType CmdType, string CmdText, DbTransaction Transaction)
{
return this.ExecuteNonQuery(CmdType, CmdText, null, (MySqlTransaction)Transaction);
}
public static int Update(DbConnection connection, DbTransaction transaction, Query query, Query values)
{
return(Query.Update(query, values, null, connection, transaction));
}
/// <summary>
/// 将CommandText发送到Connection，并使用CommandBehavior值之一生成一个SqlDataReader
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <returns>SqlDataReader</returns>
public DbDataReader ExecuteReader(CommandType CmdType, string CmdText, DbTransaction Transaction)
{
return this.ExecuteReader(CmdType, CmdText, null, null, false);
}
public static int SoftDelete(DbConnection connection, DbTransaction transaction, Query query)
{
return(Query.SoftDelete(query, null, connection, transaction));
}
/// <summary>
/// 将CommandText发送到Connection，并使用CommandBehavior值之一生成一个SqlDataReader
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="CmdParameters">SqlCommand 的参数</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <param name="ConnOwnership">DataReader完成数据读取时是否关闭</param>
/// <returns>SqlDataReader</returns>
public DbDataReader ExecuteReader(CommandType CmdType, string CmdText, DbParameter[] CmdParameters, DbTransaction Transaction, bool ConnOwnership)
{
this.CreateCmd(CmdType, CmdText.SqlStrTopToLimit(), CmdParameters, (MySqlTransaction)Transaction);
if (ConnOwnership == true)
{
return this.Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
return this.Cmd.ExecuteReader();
}
private static int SoftDelete(Query query, string connectionString, DbConnection connection, DbTransaction transaction)
{
CSV2Core.Core.Interfaces.IManagedConnection managedConnection = new CSV2Core_MySQL.MySQLManagedConnection();
try
{
managedConnection.set(connectionString, connection, transaction);
DbCommand command = managedConnection.manage(query.CreateSoftDeleteQuery(TableName, true));
query.SetParameters(command);
int result = command.ExecuteNonQuery();
managedConnection.commit();
return(result);
}
catch (Exception ex)
{
managedConnection.rollback();
throw;
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="CmdType">指定如何解释命令字符串</param>
/// <param name="CmdText">Transact-SQL 语句</param>
/// <param name="CmdParameters">SqlCommand 的参数</param>
/// <param name="Transaction">Transact-SQL 事务</param>
/// <returns>返回的结果集中第一行的第一列</returns>
public object ExecuteScalar(CommandType CmdType, string CmdText, DbParameter[] CmdParameters, DbTransaction Transaction)
{
this.CreateCmd(CmdType, CmdText.SqlStrTopToLimit(), CmdParameters, (MySqlTransaction)Transaction);
object objectValue = Cmd.ExecuteScalar();
this.Close();
this.Dispose();
return objectValue;
}
public static bool Delete(System.Int32 idx, System.Byte[] rowVersion, DbTransaction trans = null, string zoneId = "")
{
RevelationCheckpointProvider provider = new RevelationCheckpointProvider(zoneId);
return(provider.Delete(idx, rowVersion, trans));
}
/// <summary>
///
/// </summary>
/// <param name="tran"></param>
/// <param name="commandType"></param>
/// <param name="query"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(DbTransaction tran, CommandType commandType, string query)
{
DataSet result = ExecuteDataSet(tran, commandType, query, null, null, null);
return result;
}
public static bool C_RevelationTheGame(System.Guid managerId, System.Int32 mark, System.Int32 littleLevels, System.Int32 goals, System.Int32 toConcede, System.Boolean isGeneral, System.Int32 courage, System.Boolean isVictory, DbTransaction trans = null, string zoneId = "")
{
RevelationCheckpointProvider provider = new RevelationCheckpointProvider(zoneId);
return(provider.C_RevelationTheGame(managerId, mark, littleLevels, goals, toConcede, isGeneral, courage, isVictory, trans));
}
/// <summary>
///
/// </summary>
/// <param name="tran"></param>
/// <param name="commandType"></param>
/// <param name="query"></param>
/// <param name="page"></param>
/// <param name="srcTableName"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(DbTransaction tran, CommandType commandType, string query, Pager page, string srcTableName)
{
DataSet result = ExecuteDataSet(tran, commandType, query, page, srcTableName, null);
return result;
}
public static bool Insert(RevelationCheckpointEntity revelationCheckpointEntity, DbTransaction trans = null, string zoneId = "")
{
var provider = new RevelationCheckpointProvider(zoneId);
return(provider.Insert(revelationCheckpointEntity, trans));
}
/// <summary>
///
/// </summary>
/// <param name="tran"></param>
/// <param name="commandType"></param>
/// <param name="query"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(DbTransaction tran, CommandType commandType, string query)
{
DataSet result = ExecuteDataSet(tran, commandType, query);
if (result == null)
{
return null;
}
return result.Tables[0];
}
public override DataSet ExecuteDataSet(DbCommand command, DbTransaction transaction)
{
PrepareCWRefCursor(command);
return base.ExecuteDataSet(command, transaction);
}
/// <summary>
/// 执行SQL，并返回执行影响的行数
/// </summary>
/// <param name="tran">当前执行所在事务的事务对象</param>
/// <param name="commandType">执行类型</param>
/// <param name="query">需要执行的SQL语句</param>
/// <returns>返回执行影响的行数</returns>
public static int ExecuteNonQuery(DbTransaction tran, CommandType commandType, string query)
{
int result = ExecuteNonQuery(tran, commandType, query, null);
return result;
}
public override void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames, DbTransaction transaction)
{
PrepareCWRefCursor(command);
base.LoadDataSet(command, dataSet, tableNames, transaction);
}
/// <summary>
/// 执行SQL，并返回查询到的第一行第一列的值
/// </summary>
/// <param name="tran">当前执行所在事务的事务对象</param>
/// <param name="commandType">执行类型</param>
/// <param name="query">需要执行的SQL语句</param>
/// <param name="dbParamCollection">执行中需要用到的参数集合</param>
/// <returns>返回查询到的第一行第一列的值</returns>
public static object ExecuteScalar(DbTransaction tran, CommandType commandType, string query, DBParamCollection dbParamCollection)
{
object result = null;
Database dbBase = GetDatabase(tran.Connection.ConnectionString);
DbCommand dbCommand = null;
if (commandType == CommandType.Text)
{
dbCommand = dbBase.GetSqlStringCommand(query);
}
else if (commandType == CommandType.StoredProcedure)
{
dbCommand = dbBase.GetStoredProcCommand(query);
}
AddParamToCommand(dbBase, dbCommand, dbParamCollection);
result = dbBase.ExecuteScalar(dbCommand, tran);
return result;
}
public override IDataReader ExecuteReader(DbCommand command, DbTransaction transaction)
{
PrepareCWRefCursor(command);
return new OracleDataReaderWrapper((OracleDataReader)base.ExecuteReader(command, transaction));
}
public void SetTransaciton(DbTransaction transaction)
{
_command.Transaction = transaction;
}
public int ActualizaObjetoPersistenteByClavePrimaria <T>(T beanOriginal, T beanModificado, DbConnection connection, DbTransaction tr)
{
//calculamos el tipo de T para obtener información sobre los objetos que queremos contruir
//a partir de la base de datos
Type tipo = typeof(T);
//comprobamos que el tipo sea persistente
if (tipo.IsDefined(typeof(ObjetoPersistente), false))
{
//generamos el nombre de la tabla por defecto a partir del nombre del tipo
string nombreTabla = CalcularNombreTabla(tipo);
#region Calcular el comando de inserccion
DbCommandBuilder comandBuilder = CrearDBCommandBuilder(nombreTabla, connection, tr);
DbCommand comandoInsert = comandBuilder.GetUpdateCommand(true);
if (tr != null)
{
comandoInsert.Transaction = tr;
}
StringBuilder sql = new StringBuilder(comandoInsert.CommandText.Split("WHERE".ToCharArray())[0]);
AtributoPersistente k = GetFieldInfoPrimaryKey(beanOriginal);
if (k != null)
{
sql.AppendFormat(" WHERE {0}=:{0}", k.MapeadoPor);
}
#endregion
//connection.Close();
//connection.Open();
DbCommand updateCommand = _factoria.CreateCommand();
if (tr != null)
{
updateCommand.Transaction = tr;
}
updateCommand.CommandText = sql.ToString();
updateCommand.Connection = connection;
DbParameterCollection parametrosComando = updateCommand.Parameters;
//rellenamos las condiciones del comando para buscar el bean a modificar
RellenarParametrosFrom <T>(beanOriginal, parametrosComando, "Original_");
//rellenamos los valores que queremos modificar
RellenarParametrosFrom <T>(beanModificado, parametrosComando);
return(updateCommand.ExecuteNonQuery());
}
return(-1);
}
/// <summary>
/// 在事务中执行查询，返回DataReader
/// </summary>
public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
{
return _dbHelper.ExecuteReader(trans, cmdType, cmdText, cmdParms);
}
/// <summary>
/// Actualiza un objeto bean persistente en la base de datos
/// </summary>
/// <typeparam name="T">tipo objeto bean persistente</typeparam>
/// <param name="beanOriginal">objeto bean persistente de referencia a modificar</param>
/// <param name="beanModificado">objeto bean persistente con los valores a modificar</param>
/// <returns></returns>
public int ActualizaObjetoPersistente <T>(T beanOriginal, T beanModificado, DbConnection connection, DbTransaction tr)
{
//calculamos el tipo de T para obtener información sobre los objetos que queremos contruir
//a partir de la base de datos
Type tipo = typeof(T);
//comprobamos que el tipo sea persistente
if (tipo.IsDefined(typeof(ObjetoPersistente), false))
{
//generamos el nombre de la tabla por defecto a partir del nombre del tipo
string nombreTabla = CalcularNombreTabla(tipo);
#region Calcular el comando de inserccion
DbCommandBuilder comandBuilder = CrearDBCommandBuilder(nombreTabla, connection, tr);
DbCommand comandoInsert = comandBuilder.GetUpdateCommand(true);
if (tr != null)
{
comandoInsert.Transaction = tr;
}
#endregion
//connection.Close();
//connection.Open();
DbParameterCollection parametrosComando = comandoInsert.Parameters;
//rellenamos las condiciones del comando para buscar el bean a modificar
RellenarParametrosFrom <T>(beanOriginal, parametrosComando, "Original_");
//rellenamos los valores que queremos modificar
RellenarParametrosFrom <T>(beanModificado, parametrosComando);
return(comandoInsert.ExecuteNonQuery());
}
return(-1);
}
public DbTransaction OpenTransaction()
{
transaction = connDB.BeginTransaction();
return transaction;
}
/// <summary>
/// Calcula un DbCommandBuilder que nos ayudará a generar de forma automática los comandos mas comunes
/// </summary>
/// <param name="nombreTabla"></param>
/// <param name="connection"></param>
/// <param name="tr"></param>
/// <returns></returns>
private DbCommandBuilder CrearDBCommandBuilder(string nombreTabla, DbConnection connection, DbTransaction tr)
{
DbDataAdapter lo_Adapt = _factoria.CreateDataAdapter();
DbCommand comandoSelect = _factoria.CreateCommand();
comandoSelect.Connection = connection;
if (tr != null)
{
comandoSelect.Transaction = tr;
}
comandoSelect.CommandType = CommandType.Text;
comandoSelect.CommandText = string.Format("select * from {0}", nombreTabla);
lo_Adapt.SelectCommand = comandoSelect;
DbCommandBuilder comandBuilder = _factoria.CreateCommandBuilder();
comandBuilder.DataAdapter = lo_Adapt;
return(comandBuilder);
}