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