public void CreateIndexBuilder_SqlServer_Unique_CreatesUniqueNonClusteredIndex()
{
SqlSyntaxContext.SqlSyntaxProvider = SqlServerSyntax.Provider;
var createExpression = new CreateIndexExpression { Index = { Name = "IX_A" } };
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("TheTable").OnColumn("A").Ascending().WithOptions().Unique();
Assert.AreEqual("CREATE UNIQUE NONCLUSTERED INDEX [IX_A] ON [TheTable] ([A])", createExpression.ToString());
}
public void CreateIndexBuilder_SqlServer_Clustered_CreatesClusteredIndex()
{
var sqlSyntax = new SqlServerSyntaxProvider();
var createExpression = new CreateIndexExpression(DatabaseProviders.SqlServer, new[] { DatabaseProviders.SqlServer }, sqlSyntax)
{
Index = { Name = "IX_A" }
};
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("TheTable").OnColumn("A").Ascending().WithOptions().Clustered();
Assert.AreEqual("CREATE CLUSTERED INDEX [IX_A] ON [TheTable] ([A])", createExpression.ToString());
}
public void CreateIndexBuilder_SqlServer_Unique_CreatesUniqueNonClusteredIndex_Multi_Columnn()
{
var sqlSyntax = new SqlServerSyntaxProvider();
var createExpression = new CreateIndexExpression(DatabaseProviders.SqlServer, new[] { DatabaseProviders.SqlServer }, sqlSyntax)
{
Index = { Name = "IX_AB" }
};
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("TheTable").OnColumn("A").Ascending().OnColumn("B").Ascending().WithOptions().Unique();
Assert.AreEqual("CREATE UNIQUE NONCLUSTERED INDEX [IX_AB] ON [TheTable] ([A],[B])", createExpression.ToString());
}
/// <summary>
/// Creates an index.
/// </summary>
/// <param name="dbIndexes">
/// The collection of database indexes.
/// </param>
/// <param name="indexName">
/// The index name.
/// </param>
/// <param name="columnName">
/// The column name.
/// </param>
private void CreateIndex(DbIndexDefinition[] dbIndexes, string indexName, string columnName)
{
//// make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals(indexName)) == false)
{
Logger.Info(typeof(AddIndexesToProductVariant), "Adding nonclustered index to " + columnName + " column on merchInvoice table.");
var sqlSyntax = ApplicationContext.Current.DatabaseContext.SqlSyntax;
var dbProvider = ApplicationContext.Current.DatabaseContext.DatabaseProvider;
var createExpression = new CreateIndexExpression(dbProvider, new[] { dbProvider }, sqlSyntax)
{
Index = { Name = indexName }
};
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("merchInvoice").OnColumn(columnName).Ascending().WithOptions().NonClustered();
ApplicationContext.Current.DatabaseContext.Database.Execute(createExpression.ToString());
}
}