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 falseString = factoryImpl.Dialect.ToBooleanValueString(false);
string expectedSql =
"INSERT INTO test_insert_builder (literalColumn, intColumn, stringColumn, longColumn) VALUES (" + falseString + ", ?, 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");
}
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 SqlCommandInfo 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.AddColumns(ElementColumnNames, elementColumnIsSettable, ElementType);
return insert.ToSqlCommandInfo();
}
public void InsertSqlStringTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory( );
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlInsertBuilder insert = new SqlInsertBuilder(factoryImpl);
insert.SetTableName("test_insert_builder");
insert.AddColumn(new string[] {"intColumn"}, NHibernateUtil.Int32);
insert.AddColumn(new string[] {"longColumn"}, NHibernateUtil.Int64);
insert.AddColumn("literalColumn", false, (Type.ILiteralType) NHibernateUtil.Boolean);
insert.AddColumn("stringColumn", 5.ToString());
SqlString sqlString = insert.ToSqlString();
Parameter[] actualParams = new Parameter[2];
int numOfParameters = 0;
string expectedSql = "INSERT INTO test_insert_builder (intColumn, longColumn, literalColumn, stringColumn) VALUES (:intColumn, :longColumn, 0, 5)";
Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String");
foreach(object part in sqlString.SqlParts)
{
if(part is Parameter)
{
actualParams[numOfParameters] = (Parameter)part;
numOfParameters++;
}
}
Assert.AreEqual(2, numOfParameters, "Two parameters");
Parameter firstParam = new Parameter( "intColumn", new SqlTypes.Int32SqlType() );
Parameter secondParam = new Parameter( "longColumn", new SqlTypes.Int64SqlType() );
Assert.AreEqual(firstParam.SqlType.DbType, actualParams[0].SqlType.DbType, "First Parameter Type");
Assert.AreEqual(firstParam.Name, actualParams[0].Name, "First Parameter Name");
Assert.AreEqual(secondParam.SqlType.DbType, actualParams[1].SqlType.DbType, "Second Parameter Type");
Assert.AreEqual(secondParam.Name, actualParams[1].Name, "Second Parameter Name");
}
/// <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();
}
/// <summary> Generate the SQL that inserts a row</summary>
protected virtual SqlCommandInfo GenerateInsertString(bool identityInsert, bool[] includeProperty, int j)
{
// todo : remove the identityInsert param and variations;
// identity-insert strings are now generated from generateIdentityInsertString()
SqlInsertBuilder builder = new SqlInsertBuilder(Factory).SetTableName(GetTableName(j));
// add normal properties
for (int i = 0; i < entityMetamodel.PropertySpan; i++)
{
if (includeProperty[i] && IsPropertyOfTable(i, j))
{
// this property belongs on the table and is to be inserted
builder.AddColumns(GetPropertyColumnNames(i), propertyColumnInsertable[i], PropertyTypes[i]);
}
}
// add the discriminator
if (j == 0)
{
AddDiscriminatorToInsert(builder);
}
// add the primary key
if (j == 0 && identityInsert)
{
builder.AddIdentityColumn(GetKeyColumns(0)[0]);
}
else
{
builder.AddColumns(GetKeyColumns(j), null, IdentifierType);
}
if (Factory.Settings.IsCommentsEnabled)
{
builder.SetComment("insert " + EntityName);
}
// append the SQL to return the generated identifier
if (j == 0 && identityInsert && UseInsertSelectIdentity())
{
SqlString sql = builder.ToSqlString();
return new SqlCommandInfo(Factory.Dialect.AppendIdentitySelectToInsert(sql), builder.GetParametersTypeArray());
}
return builder.ToSqlCommandInfo();
}
protected virtual void AddDiscriminatorToInsert(SqlInsertBuilder insert) { }
protected override void AddDiscriminatorToInsert(SqlInsertBuilder insert)
{
if (discriminatorInsertable)
insert.AddColumn(DiscriminatorColumnName, DiscriminatorSQLValue);
}
/// <summary>
/// Generate the SQL that inserts rows
/// </summary>
/// <param name="identityInsert"></param>
/// <param name="includeProperty"></param>
/// <returns>An array of SqlStrings</returns>
protected virtual SqlString[ ] GenerateInsertStrings( bool identityInsert, bool[ ] includeProperty )
{
SqlString[ ] insertStrings = new SqlString[naturalOrderTableNames.Length];
for( int j = 0; j < naturalOrderTableNames.Length; j++ )
{
SqlInsertBuilder builder = new SqlInsertBuilder( factory );
builder.SetTableName( naturalOrderTableNames[ j ] );
for( int i = 0; i < PropertyTypes.Length; i++ )
{
if( includeProperty[ i ] && naturalOrderPropertyTables[ i ] == j )
{
builder.AddColumn( propertyColumnNames[ i ], PropertyTypes[ i ] );
}
}
if( identityInsert && j == 0 )
{
// 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( naturalOrderTableKeyColumns[ j ][ 0 ], Dialect.IdentityInsertString );
}
}
else
{
builder.AddColumn( naturalOrderTableKeyColumns[ j ], IdentifierType );
}
insertStrings[ j ] = builder.ToSqlString();
}
return insertStrings;
}
protected override SqlCommandInfo GenerateInsertString(bool identityInsert, bool[] includeProperty, int j)
{
SqlInsertBuilder builder = new SqlInsertBuilder(Factory)
.SetTableName(GetTableName(j));
for (int i = 0; i < HydrateSpan; i++)
{
if (includeProperty[i])
{
builder.AddColumns(GetPropertyColumnNames(i), PropertyColumnInsertable[i], PropertyTypes[i]);
}
}
if (discriminatorInsertable)
{
builder.AddColumn(DiscriminatorColumnName, DiscriminatorSQLValue);
}
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.ToSqlCommandInfo();
}
