internal static PreparedUpdateStatement PrepareUpdate(Database database, PocoData pd, string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable <string> columns)
{
var sb = new StringBuilder();
var index = 0;
var rawvalues = new List <object>();
string versionName = null;
object versionValue = null;
var versionColumnType = VersionColumnType.Number;
var primaryKeyValuePairs = GetPrimaryKeyValues(database, pd, primaryKeyName, primaryKeyValue ?? poco, primaryKeyValue == null);
foreach (var pocoColumn in pd.Columns.Values)
{
// Don't update the primary key, but grab the value if we don't have it
if (primaryKeyValuePairs.ContainsKey(pocoColumn.ColumnName))
{
if (primaryKeyValue == null)
{
primaryKeyValuePairs[pocoColumn.ColumnName] = database.ProcessMapper(pocoColumn, pocoColumn.GetValue(poco));
}
continue;
}
// Dont update result only columns
if (pocoColumn.ResultColumn ||
(pocoColumn.ComputedColumn && (pocoColumn.ComputedColumnType == ComputedColumnType.Always || pocoColumn.ComputedColumnType == ComputedColumnType.ComputedOnUpdate)))
{
continue;
}
if (!pocoColumn.VersionColumn && columns != null && !columns.Contains(pocoColumn.ColumnName, StringComparer.OrdinalIgnoreCase))
{
continue;
}
object value = pocoColumn.GetColumnValue(pd, poco, database.ProcessMapper);
if (pocoColumn.VersionColumn)
{
versionName = pocoColumn.ColumnName;
versionValue = value;
if (pocoColumn.VersionColumnType == VersionColumnType.Number)
{
versionColumnType = VersionColumnType.Number;
value = Convert.ToInt64(value) + 1;
}
else if (pocoColumn.VersionColumnType == VersionColumnType.RowVersion)
{
versionColumnType = VersionColumnType.RowVersion;
continue;
}
}
// Build the sql
if (index > 0)
{
sb.Append(", ");
}
sb.AppendFormat("{0} = @{1}", database.DatabaseType.EscapeSqlIdentifier(pocoColumn.ColumnName), index++);
rawvalues.Add(value);
}
if (sb.Length == 0)
{
return(new PreparedUpdateStatement());
}
var sql = $"UPDATE {database.DatabaseType.EscapeTableName(tableName)} SET {sb} WHERE {BuildPrimaryKeySql(database, primaryKeyValuePairs, ref index)}";
rawvalues.AddRange(primaryKeyValuePairs.Select(keyValue => keyValue.Value));
if (!string.IsNullOrEmpty(versionName))
{
sql += $" AND {database.DatabaseType.EscapeSqlIdentifier(versionName)} = @{index++}";
rawvalues.Add(versionValue);
}
return(new PreparedUpdateStatement
{
PocoData = pd,
Rawvalues = rawvalues,
Sql = sql,
VersionName = versionName,
VersionValue = versionValue,
VersionColumnType = versionColumnType,
PrimaryKeyValuePairs = primaryKeyValuePairs
});
}
public static PreparedInsertSql PrepareInsertSql <T>(Database database, PocoData pd, string tableName, string primaryKeyName, bool autoIncrement, T poco)
{
var names = new List <string>();
var values = new List <string>();
var rawvalues = new List <object>();
var index = 0;
var versionName = "";
foreach (var pocoColumn in pd.Columns.Values)
{
// Don't insert result columns
if (pocoColumn.ResultColumn ||
(pocoColumn.ComputedColumn && (pocoColumn.ComputedColumnType == ComputedColumnType.Always || pocoColumn.ComputedColumnType == ComputedColumnType.ComputedOnInsert)) ||
(pocoColumn.VersionColumn && pocoColumn.VersionColumnType == VersionColumnType.RowVersion))
{
continue;
}
// Don't insert the primary key (except under oracle where we need bring in the next sequence value)
if (autoIncrement && primaryKeyName != null && string.Compare(pocoColumn.ColumnName, primaryKeyName, true) == 0)
{
// Setup auto increment expression
string autoIncExpression = database.DatabaseType.GetAutoIncrementExpression(pd.TableInfo);
if (autoIncExpression != null)
{
names.Add(pocoColumn.ColumnName);
values.Add(autoIncExpression);
}
continue;
}
names.Add(database.DatabaseType.EscapeSqlIdentifier(pocoColumn.ColumnName));
values.Add(string.Format("{0}{1}", database._paramPrefix, index++));
object val;
if (pocoColumn.ReferenceType == ReferenceType.Foreign)
{
var member = pd.Members.Single(x => x.MemberInfoData == pocoColumn.MemberInfoData);
var column = member.PocoMemberChildren.Single(x => x.Name == member.ReferenceMemberName);
val = database.ProcessMapper(column.PocoColumn, column.PocoColumn.GetValue(poco));
}
else
{
val = database.ProcessMapper(pocoColumn, pocoColumn.GetValue(poco));
}
if (pocoColumn.VersionColumn && pocoColumn.VersionColumnType == VersionColumnType.Number)
{
val = Convert.ToInt64(val) > 0 ? val : 1;
versionName = pocoColumn.ColumnName;
}
rawvalues.Add(val);
}
var sql = string.Empty;
var outputClause = String.Empty;
if (autoIncrement || !string.IsNullOrEmpty(pd.TableInfo.SequenceName))
{
outputClause = database.DatabaseType.GetInsertOutputClause(primaryKeyName, pd.TableInfo.UseOutputClause);
}
if (names.Count != 0)
{
sql = string.Format("INSERT INTO {0} ({1}){2} VALUES ({3})",
database.DatabaseType.EscapeTableName(tableName),
string.Join(",", names.ToArray()),
outputClause,
string.Join(",", values.ToArray()));
}
else
{
sql = database.DatabaseType.GetDefaultInsertSql(tableName, primaryKeyName, pd.TableInfo.UseOutputClause, names.ToArray(), values.ToArray());
}
return(new PreparedInsertSql()
{
PocoData = pd,
Sql = sql,
Rawvalues = rawvalues,
VersionName = versionName
});
}
public static PreparedInsertSql PrepareInsertSql <T>(Database database, string tableName, string primaryKeyName, bool autoIncrement, T poco)
{
var pd = database.PocoDataFactory.ForObject(poco, primaryKeyName);
var names = new List <string>();
var values = new List <string>();
var rawvalues = new List <object>();
var index = 0;
var versionName = "";
foreach (var i in pd.Columns)
{
// Don't insert result columns
if (i.Value.ResultColumn ||
i.Value.ComputedColumn ||
(i.Value.VersionColumn && i.Value.VersionColumnType == VersionColumnType.RowVersion))
{
continue;
}
// Don't insert the primary key (except under oracle where we need bring in the next sequence value)
if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0)
{
// Setup auto increment expression
string autoIncExpression = database.DatabaseType.GetAutoIncrementExpression(pd.TableInfo);
if (autoIncExpression != null)
{
names.Add(i.Key);
values.Add(autoIncExpression);
}
continue;
}
names.Add(database.DatabaseType.EscapeSqlIdentifier(i.Key));
values.Add(string.Format("{0}{1}", database._paramPrefix, index++));
object val = database.ProcessMapper(i.Value, i.Value.GetValue(poco));
if (i.Value.VersionColumn && i.Value.VersionColumnType == VersionColumnType.Number)
{
val = Convert.ToInt64(val) > 0 ? val : 1;
versionName = i.Key;
}
rawvalues.Add(val);
}
var sql = string.Empty;
var outputClause = String.Empty;
if (autoIncrement)
{
outputClause = database.DatabaseType.GetInsertOutputClause(primaryKeyName);
}
if (names.Count != 0)
{
sql = string.Format("INSERT INTO {0} ({1}){2} VALUES ({3})",
database.DatabaseType.EscapeTableName(tableName),
string.Join(",", names.ToArray()),
outputClause,
string.Join(",", values.ToArray()));
}
else
{
sql = database.DatabaseType.GetDefaultInsertSql(tableName, names.ToArray(), values.ToArray());
}
return(new PreparedInsertSql()
{
PocoData = pd,
Sql = sql,
Rawvalues = rawvalues,
VersionName = versionName
});
}