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