private string GenEntityClass(TableModel table)
{
var columns = table.Columns.Select(x => new
{
x.TableName,
x.ColumnName,
x.DataType,
x.IsNullable,
x.Description,
x.CharacterMaximumLength,
x.NumericPrecision,
x.NumericScale,
ColumnNameMap = x.ColumnName.ToPascalCase(),
TypeName = SqlTypeMap.ContainsKey(x.DataType) ? SqlTypeMap[x.DataType] : x.DataType,
IsNull = x.IsNullable == "YES" && NullableTypes.Contains(SqlTypeMap.ContainsKey(x.DataType) ? SqlTypeMap[x.DataType] : x.DataType),
Key = table.TableConstraints.FirstOrDefault(y => y.ConstraintType == "PRIMARY KEY").
KeyColumnUsages.FirstOrDefault(y => y.ColumnName == x.ColumnName)
});
var classTemplate = File.ReadAllText(@"Template/ClassTemplate.Scriban");
return(_templateService.Render(classTemplate, new
{
columns,
table.TableName,
TableNameMap = table.TableName.ToPascalCase()
}));
}
internal override PreparedDatabaseCommand Prepare(DocumentStore store, Guid etag, int uniqueParameterIdentifier)
{
var values = ConvertAnonymousToProjections(table, projections);
values[table.EtagColumn] = etag;
values[table.ModifiedAtColumn] = DateTimeOffset.Now;
var sql = new SqlBuilder()
.Append("update {0} set {1} where {2}=@Id{3}",
store.Database.FormatTableNameAndEscape(table.Name),
string.Join(", ", from column in values.Keys select column.Name + "=@" + column.Name + uniqueParameterIdentifier),
table.IdColumn.Name,
uniqueParameterIdentifier)
.Append(!lastWriteWins, "and {0}=@CurrentEtag{1}",
table.EtagColumn.Name,
uniqueParameterIdentifier)
.ToString();
var parameters = MapProjectionsToParameters(values, uniqueParameterIdentifier);
AddTo(parameters, "@Id" + uniqueParameterIdentifier, key, SqlTypeMap.Convert(table.IdColumn).DbType, null);
if (!lastWriteWins)
{
AddTo(parameters, "@CurrentEtag" + uniqueParameterIdentifier, currentEtag, SqlTypeMap.Convert(table.EtagColumn).DbType, null);
}
return(new PreparedDatabaseCommand
{
Sql = sql,
Parameters = parameters.Values.ToList(),
ExpectedRowCount = 1
});
}
public static Guid Execute(DocumentTransaction tx, UpdateCommand command)
{
var values = ConvertAnonymousToProjections(command.Table, command.Projections);
values[DocumentTable.EtagColumn] = tx.CommitId;
values[DocumentTable.ModifiedAtColumn] = DateTimeOffset.Now;
values[DocumentTable.LastOperationColumn] = Operation.Updated;
var sql = new SqlBuilder()
.Append($"update {tx.Store.Database.FormatTableNameAndEscape(command.Table.Name)}")
.Append($"set {string.Join(", ", from column in values.Keys select column.Name + " = @" + column.Name)}")
.Append($"where {DocumentTable.IdColumn.Name}=@Id")
.Append(!command.LastWriteWins, $"and {DocumentTable.EtagColumn.Name}=@ExpectedEtag")
.ToString();
var parameters = Parameters.FromProjections(values);
parameters.Add("@Id", command.Id, SqlTypeMap.Convert(DocumentTable.IdColumn).DbType, null);
if (!command.LastWriteWins)
{
parameters.Add("@ExpectedEtag", command.ExpectedEtag, SqlTypeMap.Convert(DocumentTable.EtagColumn).DbType, null);
}
DocumentWriteCommand.Execute(tx, new SqlDatabaseCommand
{
Sql = sql,
Parameters = parameters,
ExpectedRowCount = 1
});
return(tx.CommitId);
}
internal override PreparedDatabaseCommand Prepare(DocumentStore store, Guid etag, int uniqueParameterIdentifier)
{
var sql = new SqlBuilder()
.Append("delete from {0} where {1} = @Id{2}",
store.Database.FormatTableNameAndEscape(table.Name),
table.IdColumn.Name,
uniqueParameterIdentifier)
.Append(!lastWriteWins,
"and {0} = @CurrentEtag{1}",
table.EtagColumn.Name,
uniqueParameterIdentifier)
.ToString();
var parameters = new Dictionary <string, Parameter>();
AddTo(parameters, "@Id" + uniqueParameterIdentifier, key, SqlTypeMap.Convert(table.IdColumn).DbType, null);
if (!lastWriteWins)
{
AddTo(parameters, "@CurrentEtag" + uniqueParameterIdentifier, currentEtag, SqlTypeMap.Convert(table.EtagColumn).DbType, null);
}
return(new PreparedDatabaseCommand
{
Sql = sql,
Parameters = parameters.Values.ToList(),
ExpectedRowCount = 1
});
}
Type GetType(string sqlName, bool isNullable)
{
var firstMatchingType = SqlTypeMap.ForSqlType(sqlName).FirstOrDefault();
if (firstMatchingType == null)
{
throw new ArgumentOutOfRangeException($"Found no matching .NET type for typeName type '{sqlName}'.");
}
return(isNullable
? GetNullableType(firstMatchingType.NetType)
: firstMatchingType.NetType);
}
protected static Dictionary <string, Parameter> MapProjectionsToParameters(IDictionary <Column, object> projections, int i)
{
var parameters = new Dictionary <string, Parameter>();
foreach (var projection in projections)
{
var column = projection.Key;
var sqlColumn = SqlTypeMap.Convert(column);
AddTo(parameters, "@" + column.Name + i, projection.Value, sqlColumn.DbType, sqlColumn.Length);
}
return(parameters);
}
/// <summary>
/// Adds a dynamic member as a stored procedure parameter
/// </summary>
/// <param name="binder"></param>
/// <param name="value"></param>
/// <returns></returns>
public override bool TrySetMember(SetMemberBinder binder, object value)
{
SqlDbType sqlType = default(SqlDbType);
if (SqlTypeMap.TryGetSqlType(value.GetType(), ref sqlType))
{
_proc.AddParameter(binder.Name, value, sqlType, ParameterDirection.Input);
return(true);
}
else
{
Log.Error("DynamicSqlStoredProcedure.TrySetMember - Unable to add member name: {0}, object type: {1}", binder.Name, value.GetType());
return(false);
}
}
string GetAlterTableStatement(string formattedAndEscapedTableName, string escapedColumnName)
{
var builder = new SqlBuilder();
var sqlColumn = SqlTypeMap.Convert(Column);
builder
.Append("ALTER TABLE {0} ALTER COLUMN {1}", formattedAndEscapedTableName, escapedColumnName)
.Append(new SqlParameter {
DbType = sqlColumn.DbType
}.SqlDbType.ToString())
.Append(sqlColumn.Length != null, "(" + sqlColumn.Length + ")")
.Append(Column.Nullable, "NULL").Or("NOT NULL")
.Append(";");
return(builder.ToString());
}
public static SqlBuilder BuildColumnSql(Column column)
{
if (column.Type == null)
{
throw new ArgumentException($"Column {column.Name} must have a type");
}
var sql = new SqlBuilder();
var sqlColumn = SqlTypeMap.Convert(column);
sql.Append(column.DbType.ToString());
sql.Append(sqlColumn.Length != null, "(" + sqlColumn.Length + ")");
sql.Append(column.Nullable, " NULL", " NOT NULL");
sql.Append(column.DefaultValue != null, $" DEFAULT '{column.DefaultValue}'");
sql.Append(column.IsPrimaryKey, " PRIMARY KEY");
return(sql);
}
public static Guid Execute(DocumentTransaction tx, DeleteCommand command)
{
// Note that last write wins can actually still produce a ConcurrencyException if the
// row was already deleted, which would result in 0 resulting rows changed
var sql = new SqlBuilder();
var parameters = new Parameters();
if (tx.Store.Configuration.SoftDelete)
{
sql
.Append($"update {tx.Store.Database.FormatTableNameAndEscape(command.Table.Name)}")
.Append($"set {DocumentTable.IdColumn.Name} = @NewId")
.Append($", {DocumentTable.LastOperationColumn.Name} = {(byte) Operation.Deleted}")
.Append($"where {DocumentTable.IdColumn.Name} = @Id")
.Append(!command.LastWriteWins, $"and {DocumentTable.EtagColumn.Name} = @ExpectedEtag");
parameters.Add("@NewId", $"{command.Key}/{Guid.NewGuid()}", SqlTypeMap.Convert(DocumentTable.IdColumn).DbType, null);
}
else
{
sql
.Append($"delete from {tx.Store.Database.FormatTableNameAndEscape(command.Table.Name)}")
.Append($"where {DocumentTable.IdColumn.Name} = @Id")
.Append(!command.LastWriteWins, $"and {DocumentTable.EtagColumn.Name} = @ExpectedEtag");
}
parameters.Add("@Id", command.Key, SqlTypeMap.Convert(DocumentTable.IdColumn).DbType, null);
if (!command.LastWriteWins)
{
parameters.Add("@ExpectedEtag", command.ExpectedEtag, SqlTypeMap.Convert(DocumentTable.EtagColumn).DbType, null);
}
DocumentWriteCommand.Execute(tx, new SqlDatabaseCommand
{
Sql = sql.ToString(),
Parameters = parameters,
ExpectedRowCount = 1
});
return(tx.CommitId);
}
protected SqlBuilder GetColumnSqlType(Column column, string defaultValuePostfix = "")
{
if (column.Type == null)
{
throw new ArgumentException($"Column {column.Name} must have a type");
}
var sql = new SqlBuilder();
var sqlColumn = SqlTypeMap.Convert(column);
sql.Append(new SqlParameter {
DbType = sqlColumn.DbType
}.SqlDbType.ToString());
sql.Append(sqlColumn.Length != null, "(" + sqlColumn.Length + ")");
sql.Append(column.Nullable, "NULL").Or("NOT NULL");
sql.Append(column.DefaultValue != null, "DEFAULT '{0}'", column.DefaultValue);
sql.Append(column.IsPrimaryKey, " PRIMARY KEY");
return(sql);
}
public static string ToSQLType(this Type type)
{
string sqlType = $"varchar({int.MaxValue})";
if (SqlTypeMap.ContainsKey(type))
{
sqlType = SqlTypeMap[type];
}
if ((type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable <>)) ||
!type.IsValueType)
{
sqlType = $"{sqlType} NULL";
}
else
{
sqlType = $"{sqlType} NOT NULL";
}
return(sqlType);
}
//class SPCode
//{
//}
#if EmulateDeriveParameters
public void ThrowsIfUnknownType(Type columnType)
{
Should.Throw <ArgumentException>(() => SqlTypeMap.Convert(new Column("SomeColumn", columnType)));
}
public void ConvertGivesCorrectLength(Type columnType)
{
var sqlColumn = SqlTypeMap.Convert(new Column("SomeColumn", columnType, length: 42));
sqlColumn.Length.ShouldBe("42");
}
public void ConvertGivesCorrectType(Type columnType, DbType expectedType)
{
var sqlColumn = SqlTypeMap.Convert(new Column("SomeColumn", columnType, 42));
sqlColumn.DbType.ShouldBe(expectedType);
}
public void ConvertGivesCorrectDefaultLength(Type columnType, string expectedLenght)
{
var sqlColumn = SqlTypeMap.Convert(new Column("SomeColumn", columnType, null));
sqlColumn.Length.ShouldBe(expectedLenght);
}
