public IDbCommand CreateCommand(string cmdText, IDbConnection connection, IDbTransaction transaction) { IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = cmdText; cmd.Transaction = transaction; return cmd; }
//通过文章类别ID查找文章类别的根节点,并组成字符串拼接起来 public static B_OA_FileType GetFileTypeByFlayType(string flagType, IDbTransaction tran) { B_OA_FileType fileType = new B_OA_FileType(); fileType.Condition.Add("flagType = " + flagType); fileType = Utility.Database.QueryObject<B_OA_FileType>(fileType); return fileType; }
public IDictionary<string, object> Insert(AdoAdapter adapter, string tableName, IDictionary<string, object> data, IDbTransaction transaction, bool returnRequired) { var s = DatabaseSchema.Get(adapter.ConnectionProvider, new ProviderHelper()); var table = s.FindTable(tableName); var tuples = InitializeInsertion(table); foreach (var d in data) tuples[d.Key.Homogenize()].InsertedValue = d.Value; Func<IDbCommand> command = () => { var c = transaction != null ? transaction.Connection.CreateCommand() : adapter.CreateConnection().CreateCommand(); return c; }; IDbCommand cmd; using (cmd = ConstructCommand(tuples, table.QualifiedName, command)) { cmd.WriteTrace(); cmd.Connection.TryOpen(); cmd.ExecuteNonQuery(); var returnData = new DbDictionary(); foreach (var it in tuples.Values) returnData.Add(it.SimpleDataColumn, NormalizeReturningValue((IDbDataParameter)cmd.Parameters[it.ReturningParameterName])); data = returnData; } return data; }
public IDictionary<string, object> Insert(AdoAdapter adapter, string tableName, IDictionary<string, object> data, IDbTransaction transaction = null, bool resultRequired = false) { var table = adapter.GetSchema().FindTable(tableName); var dataDictionary = BuildDataDictionary(adapter, data, table); string columnList = dataDictionary.Keys.Select(c => c.QuotedName).Aggregate((agg, next) => agg + "," + next); string valueList = dataDictionary.Keys.Select(s => "?").Aggregate((agg, next) => agg + "," + next); var insertSql = new StringBuilder(); insertSql.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2});", table.QualifiedName, columnList, valueList); if (resultRequired) { var identityColumn = table.Columns.FirstOrDefault(c => c.IsIdentity); if (identityColumn != null) { insertSql.AppendFormat(" SELECT * FROM {0} WHERE {1} = LAST_INSERT_ID();", table.QualifiedName, identityColumn.QuotedName); return ExecuteSingletonQuery(adapter, insertSql.ToString(), dataDictionary.Keys, dataDictionary.Values, transaction); } } Execute(adapter, insertSql.ToString(), dataDictionary.Keys, dataDictionary.Values, transaction); return null; }
private void ConvertQualityModels(IDbConnection conn, IDbTransaction tran) { // Converts the QualityModel JSON objects to their new format (only storing the QualityId instead of the entire object) ConvertQualityModel(conn, tran, "Blacklist"); ConvertQualityModel(conn, tran, "EpisodeFiles"); ConvertQualityModel(conn, tran, "History"); }
public RuntimeContext(IDbConnection connection, IDbTransaction transaction, IDbCommandExecutor executor, IProviderMetadata providerMetadata, IMigrationStepMetadata stepMetadata) : base(providerMetadata, stepMetadata) { _connection = connection; _transaction = transaction; _executor = executor; }
public static ResponsePackage Save(RequestObjectPackage<EventTypeModel> request, IDbConnection connectionID, IDbTransaction transactionID) { EventTypeModel obj = request.requestData; string sql = string.Empty; if (obj.ID > 0) { sql = string.Format( " update EVENT_TYPES set NAME = {0} " + Environment.NewLine + " where ID = {1} returning ID", SQL.FromString(obj.name), SQL.FromNumber(obj.ID) ); } else { sql = string.Format( " insert into EVENT_TYPES (NAME) " + Environment.NewLine + " values ({0}) returning ID", SQL.FromString(obj.name) ); } ResponseTablePackage res = DBUtils.ExecuteSQL(sql, connectionID, true, transactionID); res.ThrowExceptionIfError(); return new ResponsePackage() { resultID = res.resultID }; }
public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams) { bool mustClose = false; if (myConn == null) { mustClose = true; myConn = clsConn.getConnOLE(); } if (myConn.State != ConnectionState.Open) myConn.Open(); OleDbCommand myCMD = new OleDbCommand(); // myCMD.Connection = myConn as OleDbConnection; if (myTrans != null) myCMD.Transaction = myTrans as OleDbTransaction; // myCMD.CommandType = CommandType.Text; myCMD.CommandText = TSQL; myCMD.CommandTimeout = 180000;//3 phut // if (myParams != null) AttachParameters(myCMD, myParams); int CMDResult = myCMD.ExecuteNonQuery(); // if (mustClose) myConn.Close(); return CMDResult; }
public void BeginTransaction() { if (IsInTransaction) throw new Exception("Already in transaction"); conn.Open(); currentTransaction = conn.BeginTransaction(); IsInTransaction = true; }
/// <summary> /// Проверить возможность удаления компонента /// </summary> /// <param name="request">бъект-оболочка RequestPackage, содержащая в поле requestID ID компонента</param> /// <param name="connectionID">Объект подключения к базе данных</param> /// <returns>Объект-оболочка ResponsePackagе</returns> public ResponsePackage CheckDeleteControl(RequestPackage request, IDbConnection connectionID, IDbTransaction transactionID) { string sql = string.Format( " select id from CONTROL_QUERY_MAPPING where control_id = {0} " + " union " + " select id from FORM_IN_PARAMETERS where control_id = {0} " + " union " + " select id from FORM_OUT_PARAMETERS where control_id = {0} " + " union " + " select id from ACTION_PARAMETERS where control_id = {0} " + " union " + " select id from QUERY_QUERY_IN_PARAMETER where control_id = {0} ", request.requestID ); ResponseTablePackage res = DBUtils.OpenSQL(sql, connectionID, transactionID); res.ThrowExceptionIfError(); if (res.resultData.Rows.Count > 0) { return new ResponsePackage() { resultCode = -1, resultMessage = "Удаление компонента невозможно. Проверьте все ссылки на него." }; } else { return new ResponsePackage() { resultCode = 0, resultMessage = "Удаление возможно." }; } }
/// Runs a stored procedure /// </summary> /// <param name="procedureName"></param> /// <param name="QueryParameters"></param> /// <param name="OutputParameters"></param> /// <param name="DB"></param> public virtual void RunStoredProcedure(IDbConnection connection, string procedureName, IEnumerable<IDataParameter> inputParameters, IEnumerable<IDataParameter> outputParameters, IDbTransaction transaction, CommandBehavior commandBehavior) { SqlCommand cmd = new SqlCommand(procedureName, (SqlConnection)connection); cmd.CommandType = CommandType.StoredProcedure; ProcessSql(procedureName, inputParameters); foreach (var parm in inputParameters) { cmd.Parameters.Add(parm); } if (outputParameters != null) { foreach (SqlParameter parm in outputParameters) { parm.Direction = ParameterDirection.Output; //OutputParameters[i].Value = -1; cmd.Parameters.Add(parm); } } ExecuteSqlFinal(new Action(() => { cmd.ExecuteScalar(); })); if (commandBehavior == CommandBehavior.CloseConnection) { connection.Close(); } OnQueryComplete(); }
public void ExecuteWithTransaction(IDbConnection db, IDbTransaction transaction) { db.Execute(@"IF @EndPointType = 3 BEGIN UPDATE dbo.Company SET QueueId = @QueueId WHERE Company.CompanyId = @EndPointId END ELSE IF @EndPointType = 0 BEGIN UPDATE dbo.Agent SET QueueId = @QueueId WHERE Agent.AgentId = @EndPointId END ELSE IF @EndPointType = 1 BEGIN UPDATE dbo.Team SET QueueId = @QueueId WHERE Team.TeamId = @EndPointId END ELSE IF @EndPointType = 2 BEGIN UPDATE dbo.Department SET QueueId = @QueueId WHERE Department.DepartmentId = @EndPointId END", new { EndPointType = _endPoint.EndPointType, EndPointId = _endPoint.EndpointId, QueueId = _endPoint.QueueId }, transaction); }
/// <summary> /// Adds a new record to the database. /// </summary> /// <param name="connection"></param> /// <param name="transaction"></param> /// <param name="log"></param> /// <param name="client"></param> public void Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogClient client) { SqlPreparedCommand command = PrepareInsert(connection, transaction, "Insert", "Id", "IpAddress", "ReverseDns", "ReverseDnsDate"); client.Id = Sql.ExecuteInsert(command, log, client.IpAddress, client.ReverseDns, client.ReverseDnsDate); }
public SqlServerFetchedJob( [NotNull] SqlServerStorage storage, [NotNull] IDbConnection connection, [NotNull] IDbTransaction transaction, string jobId, string queue) { if (storage == null) throw new ArgumentNullException(nameof(storage)); if (connection == null) throw new ArgumentNullException(nameof(connection)); if (transaction == null) throw new ArgumentNullException(nameof(transaction)); if (jobId == null) throw new ArgumentNullException(nameof(jobId)); if (queue == null) throw new ArgumentNullException(nameof(queue)); _storage = storage; _connection = connection; _transaction = transaction; JobId = jobId; Queue = queue; if (!_storage.IsExistingConnection(_connection)) { _timer = new Timer(ExecuteKeepAliveQuery, null, KeepAliveInterval, KeepAliveInterval); } }
public IDbCommand CreateCommand(string commandText, IDbConnection connection, IDbTransaction transaction) { var command = connection.CreateCommand(); command.CommandText = commandText; command.Transaction = transaction; return command; }
public override IDbStatement BuildStatement( IDbConnection connection, IDbTransaction transaction, params IDisposable[] resources) { return new FirebirdDbStatement(this, connection, transaction, resources); }
public void Dispose() { try { if (dbTransaction != null) { Log("TX: Rollback"); try { dbTransaction.Rollback(); } catch { } dbTransaction.Dispose(); dbTransaction = null; } } catch (Exception e) { Log("Error during TX Rollback: {0}", e); throw; } finally { if (dbConnectionIsOwned) { dbConnection.Close(); dbConnection.Dispose(); Log("Connection disposed"); } } }
private static Guid BeginConversationInternal(IDbTransaction transaction, string initiatorServiceName, string targetServiceName, string messageContractName, int? lifetime, bool? encryption) { EnsureSqlTransaction(transaction); var cmd = transaction.Connection.CreateCommand() as SqlCommand; var query = new StringBuilder(); query.Append("BEGIN DIALOG @ch FROM SERVICE " + initiatorServiceName + " TO SERVICE @ts ON CONTRACT @cn WITH ENCRYPTION = "); if (encryption.HasValue && encryption.Value) query.Append("ON "); else query.Append("OFF "); if (lifetime.HasValue && lifetime.Value > 0) { query.Append(", LIFETIME = "); query.Append(lifetime.Value); query.Append(' '); } var param = cmd.Parameters.Add("@ch", SqlDbType.UniqueIdentifier); param.Direction = ParameterDirection.Output; param = cmd.Parameters.Add("@ts", SqlDbType.NVarChar, 256); param.Value = targetServiceName; param = cmd.Parameters.Add("@cn", SqlDbType.NVarChar, 128); param.Value = messageContractName; cmd.CommandText = query.ToString(); cmd.Transaction = transaction as SqlTransaction; var count = cmd.ExecuteNonQuery(); var handleParam = cmd.Parameters["@ch"] as SqlParameter; return (Guid)handleParam.Value; }
internal IExecutor(string tableName, IDbConnection conn, IDbTransaction trans) { this.TableName = tableName; this.Connnection = conn; this.Transaction = trans; this.Command = this.Connnection.CreateCommand(); }
public UnitOfWork(IDatabaseContext context) { _context = context; _context.OpenConnection(); _transaction = _context.Connection.BeginTransaction(IsolationLevel.ReadCommitted); }
protected override bool RunOperation( SqlConnection sqlConnection, IDbTransaction dbTransaction, DbFunctionType dbfunctionType, string operationName, IEnumerable<KeyValuePair<string, object>> parameters, out object result) { var context = parameters.First().Value as ISearchContext; if (context != null) { var sql = new SearchBuilder().BuildSearchSql(context); using (var cmd = this.DbAccess.GetCommand(sql, CommandType.Text)) { result = this.DbAccess.GetReader(cmd, dbTransaction); return true; } } // failure... result = null; return false; }
public SqlDBTransaction(SqlDB db) : base(db.ConnectionString, db.SqlAnalyzer, db.StrictTables) { _conn = db._getConnection(); _conn.Open(); _transaction = _conn.BeginTransaction(); }
public static ResponsePackage Save(RequestObjectPackage<ActionTypePropertyModel> request, IDbConnection connectionID, IDbTransaction transactionID) { ActionTypePropertyModel obj = request.requestData; string sql = string.Empty; if (obj.ID > 0) { sql = string.Format( " update ACTION_TYPE_PROPERTIES set \"VALUE\" = {0}, ACTION_TYPE_ID = {1}, ACTION_KIND_PROPERTY_ID = {2} " + Environment.NewLine + " where ID = {3} returning ID", SQL.FromNumber(obj.ID), SQL.FromNumber(obj.actionTypeID), SQL.FromNumber(obj.actionKindPropertyID), SQL.FromNumber(obj.ID) ); } else { sql = string.Format( " insert into ACTION_TYPE_PROPERTIES (\"VALUE\", ACTION_TYPE_ID, ACTION_KIND_PROPERTY_ID) " + Environment.NewLine + " values ({0}, {1}, {2}) returning ID", SQL.FromNumber(obj.ID), SQL.FromNumber(obj.actionTypeID), SQL.FromNumber(obj.actionKindPropertyID) ); } ResponseTablePackage res = DBUtils.ExecuteSQL(sql, connectionID, true, transactionID); res.ThrowExceptionIfError(); return new ResponsePackage() { resultID = res.resultID }; }
public IEnumerable<ResultSet> Execute(IDictionary<string, object> suppliedParameters, IDbTransaction transaction) { var procedure = _adapter.GetSchema().FindProcedure(_procedureName); if (procedure == null) { throw new UnresolvableObjectException(_procedureName.ToString(), string.Format("Procedure '{0}' not found.", _procedureName)); } var cn = transaction == null ? _adapter.CreateConnection() : transaction.Connection; using (cn.MaybeDisposable()) using (var command = cn.CreateCommand(_adapter.AdoOptions)) { command.Transaction = transaction; command.CommandText = procedure.QualifiedName; command.CommandType = CommandType.StoredProcedure; SetParameters(procedure, command, suppliedParameters); try { var result = _executeImpl(command); if (command.Parameters.Contains(SimpleReturnParameterName)) suppliedParameters["__ReturnValue"] = command.Parameters.GetValue(SimpleReturnParameterName); RetrieveOutputParameterValues(procedure, command, suppliedParameters); return result; } catch (DbException ex) { throw new AdoAdapterException(ex.Message, command, ex); } } }
public IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, string tableName, IEnumerable<IDictionary<string, object>> data, IDbTransaction transaction, Func<IDictionary<string,object>, Exception, bool> onError, bool resultRequired) { var table = adapter.GetSchema().FindTable(tableName); var columns = table.Columns.Where(c => !c.IsIdentity).ToList(); string columnList = string.Join(",", columns.Select(c => c.QuotedName)); string valueList = string.Join(",", columns.Select(c => "?")); string insertSql = "insert into " + table.QualifiedName + " (" + columnList + ") values (" + valueList + ")"; var helper = transaction == null ? new BulkInserterHelper(adapter, data, table, columns) : new BulkInserterTransactionHelper(adapter, data, table, columns, transaction); if (resultRequired) { var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity); if (identityColumn != null) { var identityFunction = adapter.GetIdentityFunction(); if (!string.IsNullOrWhiteSpace(identityFunction)) { return InsertRowsAndReturn(adapter, identityFunction, helper, insertSql, table, onError); } } } helper.InsertRowsWithoutFetchBack(insertSql, onError); return null; }
/// <summary> /// /// </summary> /// <param name="dbConnection"></param> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <param name="dbTransaction"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns></returns> public IGridReader Create(IDbConnection dbConnection, string storedProcName, IList<IDbParameter> parameters = null, IDbTransaction dbTransaction = null, int? commandTimeout = null, CommandType? commandType = null) { var dynamicParameters = ConvertToDynamicParameters(parameters); return new DapperGridReader(dbConnection.QueryMultiple(storedProcName, dynamicParameters, dbTransaction, commandTimeout, commandType)); }
private void ConvertQualityProfiles(IDbConnection conn, IDbTransaction tran) { var qualityProfileItemConverter = new EmbeddedDocumentConverter(new QualityIntConverter()); // Convert 'Allowed' column in QualityProfiles from Json List<object> to Json List<int> (int = Quality) using (IDbCommand qualityProfileCmd = conn.CreateCommand()) { qualityProfileCmd.Transaction = tran; qualityProfileCmd.CommandText = @"SELECT Id, Allowed FROM QualityProfiles"; using (IDataReader qualityProfileReader = qualityProfileCmd.ExecuteReader()) { while (qualityProfileReader.Read()) { var id = qualityProfileReader.GetInt32(0); var allowedJson = qualityProfileReader.GetString(1); var allowed = Json.Deserialize<List<Quality>>(allowedJson); var items = Quality.DefaultQualityDefinitions.OrderBy(v => v.Weight).Select(v => new ProfileQualityItem { Quality = v.Quality, Allowed = allowed.Contains(v.Quality) }).ToList(); var allowedNewJson = qualityProfileItemConverter.ToDB(items); using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "UPDATE QualityProfiles SET Items = ? WHERE Id = ?"; updateCmd.AddParameter(allowedNewJson); updateCmd.AddParameter(id); updateCmd.ExecuteNonQuery(); } } } } }
public static IDbCommand CreateCommand(IDbConnection db, IDbTransaction transaction, string sql, params object[] parameters) { IDbCommand cmd; // handle named/numbered etc parameters, fixing sql if required. var new_parameters = new List<object>(); sql = ParseParameters(sql, parameters, new_parameters); parameters = new_parameters.ToArray(); sql = sql.Replace("@@", "@"); // remove double escaped cmd = db.CreateCommand(); cmd.CommandText = sql; //cmd.CommandTimeout = commandTimeout; //cmd.CommandType = commandType; if (transaction != null) { cmd.Transaction = transaction; } foreach (var param in parameters) { AddParameter(cmd, param); } return cmd; }
/// <summary> /// Inserts a new record. There is no unique ID column so nothing is returned. /// </summary> /// <param name="connection"></param> /// <param name="transaction"></param> /// <param name="log"></param> /// <param name="dbInfo"></param> public void Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationDBInfo dbInfo) { var preparedCommand = PrepareCommand(connection, transaction, "Insert", _InsertCommandText, 2); Sql.SetParameters(preparedCommand, dbInfo.OriginalVersion, dbInfo.CurrentVersion); Sql.LogCommand(log, preparedCommand.Command); preparedCommand.Command.ExecuteNonQuery(); }
public UnitOfWork(IDbTransaction transaction, Action<UnitOfWork> rolledBack, Action<UnitOfWork> committed) { Transaction = transaction; _transaction = transaction; _rolledBack = rolledBack; _committed = committed; }
/// <summary> /// 更新 /// </summary> /// <param name="pEntity">实体实例</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Update(EclubPageInfoEntity pEntity, IDbTransaction pTran) { Update(pEntity, true, pTran); }
public void Update(VipCardVipMappingEntity pEntity, bool pIsUpdateNullField, IDbTransaction pTran) { //参数校验 if (pEntity == null) { throw new ArgumentNullException("pEntity"); } if (pEntity.MappingID == null) { throw new ArgumentException("执行更新时,实体的主键属性值不能为null."); } //初始化固定字段 pEntity.LastUpdateTime = DateTime.Now; pEntity.LastUpdateBy = CurrentUserInfo.UserID; //组织参数化SQL StringBuilder strSql = new StringBuilder(); strSql.Append("update [VipCardVipMapping] set "); if (pIsUpdateNullField || pEntity.VIPID != null) { strSql.Append("[VIPID]=@VIPID,"); } if (pIsUpdateNullField || pEntity.VipCardID != null) { strSql.Append("[VipCardID]=@VipCardID,"); } if (pIsUpdateNullField || pEntity.LastUpdateTime != null) { strSql.Append("[LastUpdateTime]=@LastUpdateTime,"); } if (pIsUpdateNullField || pEntity.LastUpdateBy != null) { strSql.Append("[LastUpdateBy]=@LastUpdateBy,"); } if (pIsUpdateNullField || pEntity.CustomerID != null) { strSql.Append("[CustomerID]=@CustomerID"); } strSql.Append(" where MappingID=@MappingID "); SqlParameter[] parameters = { new SqlParameter("@VIPID", SqlDbType.NVarChar), new SqlParameter("@VipCardID", SqlDbType.NVarChar), new SqlParameter("@LastUpdateTime", SqlDbType.DateTime), new SqlParameter("@LastUpdateBy", SqlDbType.NVarChar), new SqlParameter("@CustomerID", SqlDbType.NVarChar), new SqlParameter("@MappingID", SqlDbType.NVarChar) }; parameters[0].Value = pEntity.VIPID; parameters[1].Value = pEntity.VipCardID; parameters[2].Value = pEntity.LastUpdateTime; parameters[3].Value = pEntity.LastUpdateBy; parameters[4].Value = pEntity.CustomerID; parameters[5].Value = pEntity.MappingID; //执行语句 int result = 0; if (pTran != null) { result = this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, strSql.ToString(), parameters); } else { result = this.SQLHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters); } }
public virtual IEnumerable <T> GetAll(IDbTransaction transaction = null, int?commandTimeout = null) { return(Conn.GetAll <T>(transaction: transaction, commandTimeout: commandTimeout)); }
public DapperUnitOfWork(string connectionString) { _connection = new SqlConnection(connectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); }
/// <summary> /// 更新 /// </summary> /// <param name="pEntity">实体实例</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Update(PanicbuyingEventOrderMappingEntity pEntity, IDbTransaction pTran) { _currentDAO.Update(pEntity, pTran); }
/// <summary> /// 批量删除 /// </summary> /// <param name="pIDs">标识符值数组</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Delete(object[] pIDs, IDbTransaction pTran) { _currentDAO.Delete(pIDs, pTran); }
/// <summary> /// 批量删除 /// </summary> /// <param name="pEntities">实体实例数组</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Delete(PanicbuyingEventOrderMappingEntity[] pEntities, IDbTransaction pTran) { _currentDAO.Delete(pEntities, pTran); }
public DataTable ExcecuteQuery(IDbCommand command, string commandText, CommandType commandType, IDbConnection connection, string connectionString, IDbTransaction transaction) { if (ValidateDbExcecutionContext(command, commandText, commandType, connection, connectionString, transaction)) { PrepareDbExcecutionContext(command, commandText, commandType, connection, connectionString, transaction); return(this.SystemCoreManager.DbContext.ExecuteQuery(command)); } throw new GenericFrameworkException(new GenericFrameworkError() { ErrorType = ErrorType.AppError, ErrorCode = ErrorCode.AppError_InvalidDbExcecutionContext }); }
private void PrepareDbExcecutionContext(IDbCommand command, string commandText, CommandType commandType, IDbConnection connection, string connectionString, IDbTransaction transaction) { PrepareDbExcecutionContext(command, commandText, commandType, connection, connectionString); command.Transaction = transaction; }
/// <summary> /// 更新 /// </summary> /// <param name="pEntity">实体实例</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Update(VipCardVipMappingEntity pEntity, IDbTransaction pTran) { Update(pEntity, true, pTran); }
/// <summary> /// Gets the cached list of <see cref="DbField"/> objects of the table based on the data entity mapped name. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="transaction">The transaction object that is currently in used.</param> /// <returns>The cached field definitions of the entity.</returns> public static IEnumerable <DbField> Get(IDbConnection connection, string tableName, IDbTransaction transaction) => Get(connection, tableName, transaction, true);
private bool ValidateDbExcecutionContext(IDbCommand command, string commandText, CommandType commandType, IDbConnection connection, string connectionString, IDbTransaction transaction) { if (ValidateDbExcecutionContext(command, commandText, commandType, connection, connectionString)) { if (transaction == null) { throw new GenericFrameworkException(new GenericFrameworkError() { ErrorType = ErrorType.AppError, ErrorCode = ErrorCode.AppError_InvalidTransactionObject }); } return(true); } return(false); }
/// <summary> /// Gets the cached list of <see cref="DbField"/> objects of the table based on the data entity mapped name in an asynchronous way. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="transaction">The transaction object that is currently in used.</param> /// <returns>The cached field definitions of the entity.</returns> public static Task <IEnumerable <DbField> > GetAsync(IDbConnection connection, string tableName, IDbTransaction transaction) => GetAsync(connection, tableName, transaction, true);
/// <summary> /// Gets the cached list of <see cref="DbField"/> objects of the table based on the data entity mapped name. /// </summary> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="transaction">The transaction object that is currently in used.</param> /// <param name="enableValidation">Enables the validation after retrieving the database fields.</param> /// <returns>The cached field definitions of the entity.</returns> public static IEnumerable <DbField> Get(IDbConnection connection, string tableName, IDbTransaction transaction, bool enableValidation) => GetInternal(connection, tableName, transaction, enableValidation);
/// <summary> /// 更新 /// </summary> /// <param name="pEntity">实体实例</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Update(R_WxO2OPanel_7DaysEntity pEntity, IDbTransaction pTran) { _currentDAO.Update(pEntity, pTran); }
/// <summary> /// Gets the cached field definitions of the entity in an asychronous way. /// </summary> /// <typeparam name="TDbConnection">The type of <see cref="IDbConnection"/> object.</typeparam> /// <param name="connection">The instance of the <see cref="IDbConnection"/> object.</param> /// <param name="tableName">The name of the target table.</param> /// <param name="transaction">The transaction object that is currently in used.</param> /// <param name="enableValidation">Enables the validation after retrieving the database fields.</param> /// <returns>The cached field definitions of the entity.</returns> internal static async Task <IEnumerable <DbField> > GetInternalAsync <TDbConnection>(TDbConnection connection, string tableName, IDbTransaction transaction, bool enableValidation) where TDbConnection : IDbConnection { var type = connection.GetType(); var key = (long)type.FullName.GetHashCode(); var result = (IEnumerable <DbField>)null; // Note: For SqlConnection, the ConnectionString is changing if the (Integrated Security=False). Actually for this isolation, the database name is enough. if (!string.IsNullOrWhiteSpace(connection?.Database)) { key += connection.Database.GetHashCode(); } // Add the hashcode of the table name if (string.IsNullOrWhiteSpace(tableName) == false) { key += tableName.GetHashCode(); } // Try get the value if (cache.TryGetValue(key, out result) == false) { // Get from DB var dbHelper = DbHelperMapper.Get(type); result = await dbHelper?.GetFieldsAsync(connection, tableName, transaction); // Validate if (enableValidation) { ValidateDbFields(tableName, result); } // Add to cache cache.TryAdd(key, result); } // Return the value return(result); }
/// <summary> /// Creates the command. /// </summary> /// <param name="connection">The connection.</param> /// <param name="commandText">The command text.</param> /// <param name="commandType">Type of the command.</param> /// <param name="transaction">The transaction.</param> /// <param name="commandTimeout">The command timeout.</param> /// <returns> /// An IDbCommand object. /// </returns> /// <exception cref="System.NullReferenceException">The IDbConnection is null.</exception> /// <exception cref="System.ArgumentException">commandText;The command text cannot be null, empty, or contain only whitespace.</exception> public static IDbCommand CreateCommand(this IDbConnection connection, string commandText, CommandType commandType = CommandType.Text, IDbTransaction transaction = null, int commandTimeout = 30) { if (connection == null) { throw new NullReferenceException(); } if (string.IsNullOrWhiteSpace(commandText)) { throw new ArgumentException("The command text cannot be null, empty, or contain only whitespace.", "Argument"); } IDbCommand command = connection.CreateCommand(); command.CommandText = commandText; command.CommandType = commandType; command.Transaction = transaction; command.CommandTimeout = commandTimeout; return(command); }
public virtual IDbStatement BuildStatement( TransactionScope scope, IDbConnection connection, IDbTransaction transaction) { return(new CommonDbStatement(this, scope, connection, transaction)); }
public void BeginTransaction(IsolationLevel level = IsolationLevel.ReadCommitted) { _transaction = _dbConnection.BeginTransaction(level); }
/// <summary> /// 批量删除 /// </summary> /// <param name="pEntities">实体实例数组</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Delete(R_WxO2OPanel_7DaysEntity[] pEntities, IDbTransaction pTran) { _currentDAO.Delete(pEntities, pTran); }
/// <summary> /// <para>Inserts a row into the database</para> /// <para>By default inserts into the table matching the class name</para> /// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para> /// <para>Insert filters out Id column and any columns with the [Key] attribute</para> /// <para>Properties marked with attribute [Editable(false)] and complex types are ignored</para> /// <para>Supports transaction and command timeout</para> /// <para>Returns the ID (primary key) of the newly inserted record</para> /// </summary> /// <param name="connection"></param> /// <param name="entityToInsert"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns>The ID (primary key) of the newly inserted record</returns> public static int Insert(this IDbConnection connection, object entityToInsert, IDbTransaction transaction = null, int?commandTimeout = null) { var name = GetTableName(entityToInsert); var sb = new StringBuilder(); sb.AppendFormat("insert into [{0}]", name); sb.Append(" ("); BuildInsertParameters(entityToInsert, sb); sb.Append(") values ("); BuildInsertValues(entityToInsert, sb); sb.Append(")"); //sqlce doesn't support scope_identity so we have to dumb it down //sb.Append("; select cast(scope_identity() as int)"); //var newId = connection.Query<int?>(sb.ToString(), entityToInsert).Single(); //return (newId == null) ? 0 : (int)newId; if (Debugger.IsAttached) { Trace.WriteLine(String.Format("Insert: {0}", sb)); } connection.Execute(sb.ToString(), entityToInsert, transaction, commandTimeout); var r = connection.Query("select @@IDENTITY id", null, transaction, true, commandTimeout); return((int)r.First().id); }
/// <summary> /// Bulk copies a set of objects to the server. /// </summary> /// <param name="connection">The connection to use.</param> /// <param name="tableName">The name of the table.</param> /// <param name="reader">The reader to read objects from.</param> /// <param name="configure">A callback method to configure the bulk copy object.</param> /// <param name="options">Options for initializing the bulk copy object.</param> /// <param name="transaction">An optional transaction to participate in.</param> public override void BulkCopy(IDbConnection connection, string tableName, IDataReader reader, Action <InsightBulkCopy> configure, InsightBulkCopyOptions options, IDbTransaction transaction) { if (reader == null) { throw new ArgumentNullException("reader"); } if (transaction != null) { throw new ArgumentException("OracleProvider does not support external transactions for bulk copy", "transaction"); } OracleBulkCopyOptions oracleOptions = OracleBulkCopyOptions.Default; if (options.HasFlag(InsightBulkCopyOptions.UseInternalTransaction)) { oracleOptions |= OracleBulkCopyOptions.UseInternalTransaction; } using (var bulk = new OracleBulkCopy((OracleConnection)connection, oracleOptions)) using (var insightBulk = new OracleInsightBulkCopy(bulk)) { bulk.DestinationTableName = tableName; // map the columns by name, in case we skipped a readonly column foreach (DataRow row in reader.GetSchemaTable().Rows) { bulk.ColumnMappings.Add((string)row["ColumnName"], (string)row["ColumnName"]); } if (configure != null) { configure(insightBulk); } bulk.WriteToServer(reader); } }
/// <summary> /// /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task UpdateAsync(Models.Product model) { IDbTransaction transaction = null; try { using (var conn = await _dbConnectionStrategy.CreateConnectionAsync(Models.DatabaseType.Sqlite)) { transaction = conn.BeginTransaction(); var query = @" update Products set Name = @Name, Description = @Description, Price = @Price, DeliveryPrice = @DeliveryPrice where Id = @Id collate nocase "; await conn.ExecuteAsync(query, new { Id = model.Id.ToString(), Name = model.Name, Description = model.Description, Price = model.Price, DeliveryPrice = model.DeliveryPrice }, transaction); foreach (var option in model.Options) { query = @" update ProductOptions set Name = @Name, Description = @Description where Id = @OptionId collate nocase "; await conn.ExecuteAsync(query, new { OptionId = option.Id, Name = option.Name, Description = option.Description }, transaction); } transaction.Commit(); } } catch (Exception ex) { _logger.LogError($"An error occurred when trying to update the product. Product: {JsonConvert.SerializeObject(model)}. Exception: {ex}"); _logger.LogInformation($"Roll back is being invoked..."); try { transaction?.Rollback(); _logger.LogInformation($"Roll back is complete."); } catch (SqliteException sqlEx) { if (transaction.Connection != null) { _logger.LogError($"Failed to roll back. Exception: {sqlEx}"); } } throw ex; } }
/// <summary> /// <para>Deletes a record or records in the database that match the object passed in</para> /// <para>-By default deletes records in the table matching the class name</para> /// <para>Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para> /// <para>Supports transaction and command timeout</para> /// <para>Returns the number of records effected</para> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="entityToDelete"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns>The number of records effected</returns> public static int Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) { var idProps = GetIdProperties(entityToDelete).ToList(); if (!idProps.Any()) { throw new ArgumentException("Entity must have at least one [Key] or Id property"); } var name = GetTableName(entityToDelete); var sb = new StringBuilder(); sb.AppendFormat("delete from [{0}]", name); sb.Append(" where "); BuildWhere(sb, idProps); if (Debugger.IsAttached) { Trace.WriteLine(String.Format("Delete: {0}", sb)); } return(connection.Execute(sb.ToString(), entityToDelete, transaction, commandTimeout)); }
/// <summary> /// 在事务内创建一个新实例 /// </summary> /// <param name="pEntity">实体实例</param> /// <param name="pTran">事务实例,可为null,如果为null,则不使用事务来更新</param> public void Create(EclubPageInfoEntity pEntity, IDbTransaction pTran) { //参数校验 if (pEntity == null) { throw new ArgumentNullException("pEntity"); } //初始化固定字段 pEntity.CreateTime = DateTime.Now; pEntity.CreateBy = CurrentUserInfo.UserID; pEntity.LastUpdateTime = pEntity.CreateTime; pEntity.LastUpdateBy = CurrentUserInfo.UserID; pEntity.IsDelete = 0; StringBuilder strSql = new StringBuilder(); strSql.Append("insert into [EclubPageInfo]("); strSql.Append("[PageCode],[PageName],[PageUrl],[Sequence],[BrowseCount],[CustomerId],[CreateBy],[CreateTime],[LastUpdateBy],[LastUpdateTime],[IsDelete],[PageInfoID])"); strSql.Append(" values ("); strSql.Append("@PageCode,@PageName,@PageUrl,@Sequence,@BrowseCount,@CustomerId,@CreateBy,@CreateTime,@LastUpdateBy,@LastUpdateTime,@IsDelete,@PageInfoID)"); Guid?pkGuid; if (pEntity.PageInfoID == null) { pkGuid = Guid.NewGuid(); } else { pkGuid = pEntity.PageInfoID; } SqlParameter[] parameters = { new SqlParameter("@PageCode", SqlDbType.NVarChar), new SqlParameter("@PageName", SqlDbType.NVarChar), new SqlParameter("@PageUrl", SqlDbType.NVarChar), new SqlParameter("@Sequence", SqlDbType.Int), new SqlParameter("@BrowseCount", SqlDbType.Int), new SqlParameter("@CustomerId", SqlDbType.NVarChar), new SqlParameter("@CreateBy", SqlDbType.NVarChar), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@LastUpdateBy", SqlDbType.NVarChar), new SqlParameter("@LastUpdateTime", SqlDbType.DateTime), new SqlParameter("@IsDelete", SqlDbType.Int), new SqlParameter("@PageInfoID", SqlDbType.UniqueIdentifier) }; parameters[0].Value = pEntity.PageCode; parameters[1].Value = pEntity.PageName; parameters[2].Value = pEntity.PageUrl; parameters[3].Value = pEntity.Sequence; parameters[4].Value = pEntity.BrowseCount; parameters[5].Value = pEntity.CustomerId; parameters[6].Value = pEntity.CreateBy; parameters[7].Value = pEntity.CreateTime; parameters[8].Value = pEntity.LastUpdateBy; parameters[9].Value = pEntity.LastUpdateTime; parameters[10].Value = pEntity.IsDelete; parameters[11].Value = pkGuid; //执行并将结果回写 int result; if (pTran != null) { result = this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, strSql.ToString(), parameters); } else { result = this.SQLHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters); } pEntity.PageInfoID = pkGuid; }
public FbvAdapter(System.Data.IDbConnection connection, IDbTransaction transaction) : base(connection, transaction) { }
/// <summary> /// Returns a list of entites from table "Ts". /// Id of T must be marked with [Key] attribute. /// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension /// for optimal performance. /// </summary> /// <typeparam name="T">Interface or type to create and populate</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="transaction">The transaction to run under, null (the default) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>Entity of T</returns> public static Task <IEnumerable <T> > GetAllAsync <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { var type = typeof(T); var cacheType = typeof(List <T>); string sql; if (!GetQueries.TryGetValue(cacheType.TypeHandle, out sql)) { GetSingleKey <T>(nameof(GetAll)); var name = GetTableName(type); sql = "SELECT * FROM " + name; GetQueries[cacheType.TypeHandle] = sql; } if (!type.IsInterface) { return(connection.QueryAsync <T>(sql, null, transaction, commandTimeout)); } return(GetAllAsyncImpl <T>(connection, transaction, commandTimeout, sql, type)); }
/// <summary> /// Create a new product /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task <Models.Product> CreateAsync(Models.Product model) { IDbTransaction transaction = null; try { using (var conn = await _dbConnectionStrategy.CreateConnectionAsync(Models.DatabaseType.Sqlite)) { transaction = conn.BeginTransaction(); var query = @" insert into Products (Id, Name, Description, Price, DeliveryPrice) values (@Id, @Name, @Description, @Price, @DeliveryPrice) "; var productId = Guid.NewGuid().ToString(); model.Id = productId; await conn.ExecuteAsync(query, new { Id = model.Id, Name = model.Name, Description = model.Description, Price = model.Price, DeliveryPrice = model.DeliveryPrice }, transaction); foreach (var option in model.Options) { query = @" insert into ProductOptions (Id, ProductId, Name, Description) values (@OptionId, @ProductId, @Name, @Description) "; await conn.ExecuteAsync(query, new { OptionId = Guid.NewGuid().ToString(), ProductId = productId, Name = option.Name, Description = option.Description }, transaction); } transaction.Commit(); return(model); } } catch (Exception ex) { _logger.LogError($"An error occurred when trying to create a product {JsonConvert.SerializeObject(model)}. Exception: {ex}"); _logger.LogInformation($"Roll back is being invoked..."); try { transaction?.Rollback(); _logger.LogInformation($"Roll back is complete."); } catch (SqliteException sqlEx) { if (transaction.Connection != null) { _logger.LogError($"Failed to roll back. Exception: {sqlEx}"); } } throw ex; } }
/// <summary> /// Delete all entities in the table related to the type T asynchronously using .NET 4.5 Task. /// </summary> /// <typeparam name="T">Type of entity</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="transaction">The transaction to run under, null (the default) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>true if deleted, false if none found</returns> public static async Task <bool> DeleteAllAsync <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { var type = typeof(T); var statement = "DELETE FROM " + GetTableName(type); var deleted = await connection.ExecuteAsync(statement, null, transaction, commandTimeout).ConfigureAwait(false); return(deleted > 0); }
public void Update(EclubPageInfoEntity pEntity, bool pIsUpdateNullField, IDbTransaction pTran) { //参数校验 if (pEntity == null) { throw new ArgumentNullException("pEntity"); } if (pEntity.PageInfoID == null) { throw new ArgumentException("执行更新时,实体的主键属性值不能为null."); } //初始化固定字段 pEntity.LastUpdateTime = DateTime.Now; pEntity.LastUpdateBy = CurrentUserInfo.UserID; //组织参数化SQL StringBuilder strSql = new StringBuilder(); strSql.Append("update [EclubPageInfo] set "); if (pIsUpdateNullField || pEntity.PageCode != null) { strSql.Append("[PageCode]=@PageCode,"); } if (pIsUpdateNullField || pEntity.PageName != null) { strSql.Append("[PageName]=@PageName,"); } if (pIsUpdateNullField || pEntity.PageUrl != null) { strSql.Append("[PageUrl]=@PageUrl,"); } if (pIsUpdateNullField || pEntity.Sequence != null) { strSql.Append("[Sequence]=@Sequence,"); } if (pIsUpdateNullField || pEntity.BrowseCount != null) { strSql.Append("[BrowseCount]=@BrowseCount,"); } if (pIsUpdateNullField || pEntity.CustomerId != null) { strSql.Append("[CustomerId]=@CustomerId,"); } if (pIsUpdateNullField || pEntity.LastUpdateBy != null) { strSql.Append("[LastUpdateBy]=@LastUpdateBy,"); } if (pIsUpdateNullField || pEntity.LastUpdateTime != null) { strSql.Append("[LastUpdateTime]=@LastUpdateTime"); } if (strSql.ToString().EndsWith(",")) { strSql.Remove(strSql.Length - 1, 1); } strSql.Append(" where PageInfoID=@PageInfoID "); SqlParameter[] parameters = { new SqlParameter("@PageCode", SqlDbType.NVarChar), new SqlParameter("@PageName", SqlDbType.NVarChar), new SqlParameter("@PageUrl", SqlDbType.NVarChar), new SqlParameter("@Sequence", SqlDbType.Int), new SqlParameter("@BrowseCount", SqlDbType.Int), new SqlParameter("@CustomerId", SqlDbType.NVarChar), new SqlParameter("@LastUpdateBy", SqlDbType.NVarChar), new SqlParameter("@LastUpdateTime", SqlDbType.DateTime), new SqlParameter("@PageInfoID", SqlDbType.UniqueIdentifier) }; parameters[0].Value = pEntity.PageCode; parameters[1].Value = pEntity.PageName; parameters[2].Value = pEntity.PageUrl; parameters[3].Value = pEntity.Sequence; parameters[4].Value = pEntity.BrowseCount; parameters[5].Value = pEntity.CustomerId; parameters[6].Value = pEntity.LastUpdateBy; parameters[7].Value = pEntity.LastUpdateTime; parameters[8].Value = pEntity.PageInfoID; //执行语句 int result = 0; if (pTran != null) { result = this.SQLHelper.ExecuteNonQuery((SqlTransaction)pTran, CommandType.Text, strSql.ToString(), parameters); } else { result = this.SQLHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters); } }