public virtual SqlStatement BuildCrudUpdateOne(DbTableInfo table, EntityRecord rec) { var placeHolders = new SqlPlaceHolderList(); // find modified columns var setExprs = new List <SqlFragment>(); foreach (var col in table.UpdatableColumns) { if (!rec.IsValueChanged(col.Member)) { continue; } var valueSql = new SqlColumnValuePlaceHolder(col); placeHolders.Add(valueSql); setExprs.Add(new CompositeSqlFragment(col.SqlColumnNameQuoted, SqlTerms.Equal, valueSql)); } var setList = SqlFragment.CreateList(SqlTerms.Comma, setExprs); var whereCond = BuildWhereConditonForUpdateDeleteOne(table, placeHolders); var whereClause = new CompositeSqlFragment(SqlTerms.Where, whereCond); var sql = SqlDialect.SqlCrudTemplateUpdate.Format(table.SqlFullName, setList, whereClause); var stmt = new SqlStatement(SqlKind.UpdateOne, sql, placeHolders, DbExecutionType.NonQuery, SqlDialect.PrecedenceHandler, QueryOptions.NoQueryCache); return(stmt); }
protected SqlFragment BuildWhereConditonForUpdateDeleteOne(DbTableInfo table, SqlPlaceHolderList placeHolders) { var pkCols = table.PrimaryKey.KeyColumns; var hasRowVersion = table.Entity.Flags.HasFlag(EntityFlags.HasRowVersion); SqlPlaceHolder colPh; // short way for one-column PK if (pkCols.Count == 1 && !hasRowVersion) { var pkCol = pkCols[0].Column; colPh = new SqlColumnValuePlaceHolder(pkCol); placeHolders.Add(colPh); return(new CompositeSqlFragment(pkCol.SqlColumnNameQuoted, SqlTerms.Equal, colPh)); } //general case: // add row version to column list if there's row version. We must compare row version in WHERE clause var allCols = pkCols.Select(kc => kc.Column).ToList(); if (hasRowVersion) { var rvCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.RowVersion)); allCols.Add(rvCol); } var conds = new List <SqlFragment>(); foreach (var col in allCols) { var colSql = new SqlColumnValuePlaceHolder(col); placeHolders.Add(colSql); conds.Add(new CompositeSqlFragment(col.SqlColumnNameQuoted, SqlTerms.Equal, colSql)); } return(SqlFragment.CreateList(SqlTerms.And, conds)); }
private void AppendIdentityReturn(SqlStatement sql, DbTableInfo table) { var idCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.Identity)); var dbType = idCol.Member.DataType.GetIntDbType(); var idPrmPh = new SqlColumnValuePlaceHolder(idCol, ParameterDirection.Output); sql.PlaceHolders.Add(idPrmPh); var getIdSql = _msDialect.SqlGetIdentityTemplate.Format(idPrmPh); sql.Append(getIdSql); sql.Append(SqlTerms.NewLine); }
private void AppendIdentityReturn(SqlStatement sql, DbTableInfo table) { sql.TrimEndingSemicolon(); // Append returning clause var idCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.Identity)); var dbType = idCol.Member.DataType.GetIntDbType(); // we create placeholder based on Id column, only with OUTPUT direction - this results in parameter to return value var idPrmPh = new SqlColumnValuePlaceHolder(idCol, ParameterDirection.Output); sql.PlaceHolders.Add(idPrmPh); var getIdSql = _pgDialect.SqlCrudTemplateReturningIdentity.Format(idCol.SqlColumnNameQuoted); sql.Append(getIdSql); sql.Append(SqlTerms.NewLine); }
private IDataParameter AddParameter(SqlColumnValuePlaceHolder cph, object colValue, EntityRecord record) { var prm = _sqlDialect.AddDbParameter(_dbCommand, cph, colValue); if (prm.Direction != ParameterDirection.Input) { record.DbCommandData = record.DbCommandData ?? new EntityRecordDBCommandData() { DbCommand = _dbCommand }; record.DbCommandData.OutputParameters.Add(new OutParamInfo() { Parameter = prm, Column = cph.Column }); } return(prm); }
public virtual SqlStatement BuildCrudInsertOne(DbTableInfo table, EntityRecord record) { // list of column names var insertCols = GetColumnsToInsert(table, record); var insertColsSqls = insertCols.Select(c => c.SqlColumnNameQuoted).ToList(); var colListSql = SqlFragment.CreateList(SqlTerms.Comma, insertColsSqls); // values and placeholders var placeHolders = new SqlPlaceHolderList(); var colSqls = new List <SqlFragment>(); foreach (var insertCol in insertCols) { var ph = new SqlColumnValuePlaceHolder(insertCol); placeHolders.Add(ph); colSqls.Add(ph); } var valuesFragm = CompositeSqlFragment.Parenthesize(SqlFragment.CreateList(SqlTerms.Comma, colSqls)); // format SQL var sql = SqlDialect.SqlCrudTemplateInsert.Format(table.SqlFullName, colListSql, valuesFragm); var stmt = new SqlStatement(SqlKind.InsertOne, sql, placeHolders, DbExecutionType.NonQuery); return(stmt); }
// identity/output parameters handling =========================================================================== // called only non-batch mode public bool CheckReferencesNewIdentity(EntityRecord rec, SqlColumnValuePlaceHolder cph, out string idParamName) { idParamName = null; if (!ReferencesNewIdentity(rec, cph.Column, out EntityRecord targetRec)) { return(false); } var targetCmdData = targetRec.DbCommandData; Util.Check(targetCmdData != null, "Fatal error: the target record of FK column {0} does not have {1} field set. " + "Fault in record sequencing.", cph.Column.ColumnName, nameof(EntityRecord.DbCommandData)); var idPrmInfo = targetCmdData.OutputParameters.First(op => op.Column.Flags.IsSet(DbColumnFlags.Identity)); Util.Check(idPrmInfo != null, "Fatal error: the identity parameter is not found in referenced target record. " + "Fault in record sequencing."); // Batch mode: check if identity parameter belongs to the same data command - the case for very large batches that contaim multiple // DbCommands (each containing mltiple update SQLs). It may happen that parameter returning identity is in different (prior) // DbCommand. Note: parameters cannot be reused accross DB commands if (targetRec.DbCommandData.DbCommand == this._dbCommand) { // use the same parameter idParamName = idPrmInfo.Parameter.ParameterName; return(true); } // different batch command. Create new parameter and copy action var newPrm = AddParameter(cph, 0, rec); idParamName = newPrm.ParameterName; _paramCopyList = _paramCopyList ?? new List <BatchParamCopy>(); _paramCopyList.Add(new BatchParamCopy() { From = idPrmInfo.Parameter, To = newPrm }); return(true); }
public string FormatColumnValuePlaceHolder(SqlColumnValuePlaceHolder cph, EntityRecord rec) { object memberValue = null; object colValue = null; // If it is output, it must be parameters if (cph.ParamDirection != ParameterDirection.Input) { if (cph.ParamDirection == ParameterDirection.InputOutput) //do we need initial value { memberValue = rec.GetValueDirect(cph.Column.Member); } else { memberValue = cph.Column.Member.DefaultValue; //this is important to setup prm.DbType for output parameter } colValue = cph.Column.Converter.PropertyToColumn(memberValue); return(AddParameter(cph, colValue, rec).ParameterName); } // special case: column references new identity value for record inserted in the same transaction if (_batchMode && CheckReferencesNewIdentity(rec, cph, out string prmName)) { return(prmName); } // get value and check if we can use literal memberValue = rec.GetValueDirect(cph.Column.Member); colValue = cph.Column.Converter.PropertyToColumn(memberValue); if (ShouldUseLiteral(memberValue, cph.Column)) { return(FormatAsLiteral(cph, colValue)); } else { return(AddParameter(cph, colValue, rec).ParameterName); } }
private void AppendRowVersionCheckReturn(SqlStatement sql, DbTableInfo table, EntityRecord record) { var rvCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.RowVersion)); // do row count check for update only, not for insert if (record.Status == EntityStatus.Modified) { var tag = new TextSqlFragment($"'ConcurrentUpdate/{table.Entity.Name}/{record.PrimaryKey.ValuesToString()}'"); var checkRowsSql = _msDialect.SqlCheckRowCountIsOne.Format(tag); sql.Append(checkRowsSql); } // return RowVersion in parameter var rvPrmPholder = new SqlColumnValuePlaceHolder(rvCol, ParameterDirection.InputOutput); sql.PlaceHolders.Add(rvPrmPholder); rvPrmPholder.PreviewParameter = (prm, ph) => { prm.DbType = DbType.Binary; prm.Size = 8; }; var getRvSql = _msDialect.SqlGetRowVersionTemplate.Format(rvPrmPholder); sql.Append(getRvSql); sql.Append(SqlTerms.NewLine); }