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