/// <summary>
/// Generate the SQL INSERT that creates a new row
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateInsertRowString()
{
SqlInsertBuilder insert = new SqlInsertBuilder(Factory)
.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, null, KeyType);
if (hasIdentifier)
{
insert.AddColumns(new string[] { IdentifierColumnName }, null, IdentifierType);
}
if (HasIndex)
{
insert.AddColumns(IndexColumnNames, indexColumnIsSettable, IndexType);
}
if (Factory.Settings.IsCommentsEnabled)
{
insert.SetComment("insert collection row " + Role);
}
insert.AddColumns(ElementColumnNames, elementColumnIsSettable, ElementType);
return(insert.ToSqlCommandInfo());
}
private void ConstructBody()
{
CSBlock usingBlock = new CSBlock("using (SqlCommand sqlCommand = new SqlCommand())");
string sqlString = $"{Quotes}{SqlInsertBuilder.CreateInsertSql(datatable)} SELECT SCOPE_IDENTITY() AS INT;{Quotes};";
string parameterName = string.Empty;
string propertyName = string.Empty;
string parameterValue = string.Empty;
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString}");
foreach (DataColumn clm in datatable.Columns)
{
if (clm.AutoIncrement)
{
continue;
}
parameterName = $"{Quotes}@{clm.ColumnName.Replace(" ", "")}{Quotes}";
propertyName = ProperVarName.Get(clm.ColumnName);
parameterValue = $"{ProperVarName.Get(datatable.TableName.ToLower())}.{propertyName}";
if (clm.AllowDBNull)
{
parameterValue += " ?? (object)DBNull.Value";
}
usingBlock.Statements.Add($"sqlCommand.Parameters.AddWithValue({parameterName}, {parameterValue});");
}
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand);");
this.Statements.Add(usingBlock.ToString());
}
protected override void AddDiscriminatorToInsert(SqlInsertBuilder insert)
{
if (discriminatorInsertable)
{
insert.AddColumn(DiscriminatorColumnName, DiscriminatorSQLValue);
}
}
public void MixingParametersAndValues()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlInsertBuilder insert = new SqlInsertBuilder(factoryImpl);
insert.SetTableName("test_insert_builder");
insert.AddColumn("literalColumn", false, (ILiteralType)NHibernateUtil.Boolean);
insert.AddColumn("intColumn", NHibernateUtil.Int32);
insert.AddColumn("stringColumn", 5.ToString());
insert.AddColumn("longColumn", NHibernateUtil.Int64);
SqlCommandInfo sqlCommand = insert.ToSqlCommandInfo();
SqlType[] actualParameterTypes = sqlCommand.ParameterTypes;
string expectedSql =
"INSERT INTO test_insert_builder (literalColumn, intColumn, stringColumn, longColumn) VALUES (0, ?, 5, ?)";
Assert.AreEqual(expectedSql, sqlCommand.Text.ToString(), "SQL String");
Assert.AreEqual(2, actualParameterTypes.Length);
Assert.AreEqual(SqlTypeFactory.Int32, actualParameterTypes[0], "First Parameter Type");
Assert.AreEqual(SqlTypeFactory.Int64, actualParameterTypes[1], "Second Parameter Type");
}
/// <summary>
/// Generate the SQL that inserts a row
/// </summary>
/// <param name="identityInsert"></param>
/// <param name="includeProperty"></param>
/// <returns>A SqlString for an Insert</returns>
protected virtual SqlString GenerateInsertString(bool identityInsert, bool[] includeProperty)
{
SqlInsertBuilder builder = new SqlInsertBuilder(factory)
.SetTableName(TableName);
for (int i = 0; i < HydrateSpan; i++)
{
if (includeProperty[i])
{
builder.AddColumn(propertyColumnNames[i], PropertyTypes[i]);
}
}
if (discriminatorInsertable)
{
builder.AddColumn(DiscriminatorColumnName, DiscriminatorSQLValue.ToString());
}
if (!identityInsert)
{
builder.AddColumn(IdentifierColumnNames, IdentifierType);
}
else
{
// make sure the Dialect has an identity insert string because we don't want
// to add the column when there is no value to supply the SqlBuilder
if (Dialect.IdentityInsertString != null)
{
// only 1 column if there is IdentityInsert enabled.
builder.AddColumn(IdentifierColumnNames[0], Dialect.IdentityInsertString);
}
}
return(builder.ToSqlString());
}
protected override SqlCommandInfo GenerateIdentityInsertRowString()
{
// NH specific to manage identity for id-bag (NH-364)
SqlInsertBuilder insert = identityDelegate.PrepareIdentifierGeneratingInsert();
insert.SetTableName(qualifiedTableName).AddColumns(KeyColumnNames, null, KeyType);
if (HasIndex)
insert.AddColumns(IndexColumnNames, null, IndexType);
insert.AddColumns(ElementColumnNames, elementColumnIsSettable, ElementType);
if (Factory.Settings.IsCommentsEnabled)
insert.SetComment("insert collection row " + Role);
return insert.ToSqlCommandInfo();
}
public void Insert()
{
var db = new FakeDbConnection();
var builder = new SqlInsertBuilder(new SqlFlavorizer(SqlFlavorizer.VendorKind.PostgreSQL), s => s + " (customized)");
builder.AddNonKey("FirstName", "string", "Jane");
builder.AddNonKey("MiddleName", "nstring", null);
builder.AddNonKey("LastName", "string", null);
var pk = builder.Execute(db, "Emp", "EmpId", true).Result;
Assert.AreEqual("Jane", db.TheCommand.TheParameters[0].Value);
Assert.IsInstanceOfType(db.TheCommand.TheParameters[1].Value, typeof(DBNull));
Assert.AreEqual("", db.TheCommand.TheParameters[2].Value);
Assert.AreEqual("insert into Emp (FirstName,MiddleName,LastName) values (@p0,@p1,@p2) returning EmpId (customized)", db.TheCommand.CommandText);
}
public void Commented()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlInsertBuilder insert = new SqlInsertBuilder(factoryImpl);
insert.SetTableName("test_insert_builder");
insert.AddColumn("stringColumn", "aSQLValue", (ILiteralType)NHibernateUtil.String);
insert.SetComment("Test insert");
string expectedSql =
"/* Test insert */ INSERT INTO test_insert_builder (stringColumn) VALUES ('aSQLValue')";
Assert.AreEqual(expectedSql, insert.ToSqlString().ToString(), "SQL String");
}
/// <summary>
/// Generate the SQL INSERT that creates a new row
/// </summary>
/// <returns></returns>
protected override SqlString GenerateInsertRowString()
{
SqlInsertBuilder insert = new SqlInsertBuilder(factory)
.SetTableName(qualifiedTableName)
.AddColumn(KeyColumnNames, KeyType);
if (HasIndex)
{
insert.AddColumn(IndexColumnNames, IndexType);
}
if (hasIdentifier)
{
insert.AddColumn(new string[] { identifierColumnName }, IdentifierType);
}
insert.AddColumn(ElementColumnNames, ElementType);
return(insert.ToSqlString());
}
public void BootStrapTheApplication()
{
EventStoreDatabaseBootStrapper.BootStrap();
ReportingDatabaseBootStrapper.BootStrap();
var bus = new FakeBus();
ServiceLocator.Bus = bus;
//var storage = new InMemoryEventStore(bus);
//var storage = new NHibernateSqliteEventStore(bus, NHibernateSessionHelper.CreateSessionFactory(), new BinaryFormatter());
var storage = new SqliteEventStore(bus, new BinaryFormatter(), "Data Source=eventStore.db3");
var rep = new DomainRepository<InventoryItem>(storage);
var commands = new InventoryCommandHandlers(rep);
bus.RegisterHandler<CheckInItemsToInventory>(commands.Handle);
bus.RegisterHandler<CreateInventoryItem>(commands.Handle);
bus.RegisterHandler<DeactivateInventoryItem>(commands.Handle);
bus.RegisterHandler<RemoveItemsFromInventory>(commands.Handle);
bus.RegisterHandler<RenameInventoryItem>(commands.Handle);
var sqlInsertBuilder = new SqlInsertBuilder();
var sqlSelectBuilder = new SqlSelectBuilder();
var sqlUpdateBuilder = new SqlUpdateBuilder();
var sqlDeleteBuilder = new SqlDeleteBuilder();
var reportingRepository = new SQLiteReportingRepository("Data Source=reportingDataBase.db3", sqlSelectBuilder,
sqlInsertBuilder, sqlUpdateBuilder, sqlDeleteBuilder);
var detail = new InventoryItemDetailViewHandler(reportingRepository);
ServiceLocator.ReportingRepository = reportingRepository;
bus.RegisterHandler<InventoryItemCreated>(detail.Handle);
bus.RegisterHandler<InventoryItemDeactivated>(detail.Handle);
bus.RegisterHandler<InventoryItemRenamed>(detail.Handle);
bus.RegisterHandler<ItemsCheckedInToInventory>(detail.Handle);
bus.RegisterHandler<ItemsRemovedFromInventory>(detail.Handle);
var list = new InventoryItemListViewHandler(reportingRepository);
bus.RegisterHandler<InventoryItemCreated>(list.Handle);
bus.RegisterHandler<InventoryItemRenamed>(list.Handle);
bus.RegisterHandler<InventoryItemDeactivated>(list.Handle);
}
public void SetUp()
{
_sqlInsertBuilder = new SqlInsertBuilder();
}
public void SetUp()
{
_sqlInsertBuilder = new SqlInsertBuilder();
}
public SqlInsertOperation(SqlDataSet <TEntity> dataSet, IQueryProvider queryProvider) : base(dataSet, queryProvider)
{
_queryBuilder = new SqlInsertBuilder <TEntity>(dataSet.DataModel);
}
