/// <summary>
/// Recreates primary key or index.
/// </summary>
/// <param name="table">Current table <see cref="Table"/></param>
/// <param name="descriptor">Index descriptor</param>
private static void CreateNewPrimaryKey(Table table, IndexDbo descriptor)
{
var indexKeyType = (IndexKeyType)Enum.Parse(typeof(IndexKeyType), descriptor.IndexKeyType);
byte fillFactor = (byte)(descriptor.FillFactor ?? 0);
var primaryKeyIndex = new Index(table, descriptor.Name)
{
CompactLargeObjects = descriptor.CompactLargeObjects,
FillFactor = fillFactor > 0 ? fillFactor : (byte)50,
FilterDefinition = descriptor.FilterDefinition,
IgnoreDuplicateKeys = descriptor.IgnoreDuplicateKeys,
IndexKeyType = indexKeyType,
IsClustered = descriptor.IsClustered,
IsUnique = descriptor.IsUnique,
};
foreach (string columnName in descriptor.IndexedColumns)
{
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, columnName));
}
primaryKeyIndex.Create();
primaryKeyIndex.DisallowPageLocks = descriptor.DisallowPageLocks;
primaryKeyIndex.DisallowRowLocks = descriptor.DisallowRowLocks;
primaryKeyIndex.Alter();
//if (descriptor.IsDisabled)
//{
// primaryKeyIndex.Disable();
// table.Alter();
//}
}
public static void AddColumnToIndex(Table table, Column column, Index ind)
{
string oldName = ind.Name;
var idx = new Index(table, oldName)
{
BoundingBoxYMax = ind.BoundingBoxYMax,
BoundingBoxYMin = ind.BoundingBoxYMin,
BoundingBoxXMax = ind.BoundingBoxXMax,
BoundingBoxXMin = ind.BoundingBoxXMin,
CompactLargeObjects = ind.CompactLargeObjects,
DisallowRowLocks = ind.DisallowRowLocks,
FileGroup = ind.FileGroup,
FileStreamFileGroup = ind.FileStreamFileGroup,
FileStreamPartitionScheme = ind.FileStreamPartitionScheme,
FillFactor = ind.FillFactor,
FilterDefinition = ind.FilterDefinition,
IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys,
IndexKeyType = ind.IndexKeyType,
IsClustered = ind.IsClustered,
IsFullTextKey = ind.IsFullTextKey,
IsUnique = ind.IsUnique,
Level1Grid = ind.Level1Grid,
Level2Grid = ind.Level2Grid,
Level3Grid = ind.Level3Grid,
Level4Grid = ind.Level4Grid,
MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism,
NoAutomaticRecomputation = ind.NoAutomaticRecomputation,
OnlineIndexOperation = ind.OnlineIndexOperation,
PadIndex = ind.PadIndex,
ParentXmlIndex = ind.ParentXmlIndex,
PartitionScheme = ind.PartitionScheme,
SecondaryXmlIndexType = ind.SecondaryXmlIndexType,
SortInTempdb = ind.SortInTempdb,
SpatialIndexType = ind.SpatialIndexType,
};
foreach (IndexedColumn iColumn in ind.IndexedColumns)
{
var newIdxColumn = new IndexedColumn(idx, iColumn.Name)
{
Descending = iColumn.Descending,
IsIncluded = iColumn.IsIncluded,
};
idx.IndexedColumns.Add(newIdxColumn);
}
idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name));
bool oldDisallowPageLocks = ind.DisallowPageLocks;
ind.Drop();
idx.Create();
idx.DisallowPageLocks = oldDisallowPageLocks;
idx.Alter();
}
/// <summary>
/// Recreates primary key or index.
/// </summary>
/// <param name="table">Current table <see cref="Table"/></param>
/// <param name="indexName">Name of the index/primary key</param>
/// <param name="indexKeyType">Type of index</param>
/// <param name="columnNames">Columns in the index</param>
private static void CreateNewPrimaryKey(Table table, string indexName, IndexKeyType indexKeyType,
params string[] columnNames)
{
var primaryKeyIndex = new Index(table, indexName)
{
IndexKeyType = indexKeyType,
IsClustered = false,
FillFactor = 50
};
foreach (string columnName in columnNames)
{
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, columnName));
}
primaryKeyIndex.Create();
primaryKeyIndex.DisallowPageLocks = true;
primaryKeyIndex.Alter();
}
private static void RenameAllIndexes(Table aTable, string newTableName)
{
string pattern = String.Format(@"_{0}\b|_{0}_", aTable.Name);
var rg = new Regex(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
for (int i = aTable.Indexes.Count - 1; i >= 0; i--)
{
Index ind = aTable.Indexes[i];
string newIndexName = rg.Replace(ind.Name, delegate(Match m)
{
string mString = m.ToString();
return(String.Format("_{0}{1}", newTableName, mString.EndsWith("_") ? "_" : String.Empty));
});
if (String.Compare(ind.Name, newIndexName, true) != 0)
{
ind.Rename(newIndexName);
ind.Alter();
}
}
}
/// <summary>
/// Create indexes for WKB table DEPRECATED!!!!!
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
public static void CreateWkbIndexes(SqlConnection conn, string tableName)
{
Server srv = new Server(new ServerConnection(new SqlConnection((conn.ConnectionString))));
Database db = srv.Databases[conn.Database];
Table tbl = db.Tables[tableName];
var envelopeFields = new[] { WkbfieldsDblEnvelopeMinX, WkbfieldsDblEnvelopeMinY, WkbfieldsDblEnvelopeMaxX, WkbfieldsDblEnvelopeMaxY };
foreach (string envelopeField in envelopeFields)
{
Index idx = new Index(tbl, "IDX_" + envelopeField);
IndexedColumn indCol = new IndexedColumn(idx, envelopeField);
idx.IndexedColumns.Add(indCol);
idx.IndexKeyType = IndexKeyType.None;
idx.IsClustered = false;
idx.FillFactor = 50;
idx.Create();
idx.Alter();
}
}
public static void BuildIndexes(SqlConnectionStringBuilder csb, string dbName, params TableInfo[] tables)
{
if (string.IsNullOrWhiteSpace(dbName))
{
throw new InvalidArgumentException("dbName is missing");
}
ServerConnection connection = new ServerConnection(csb.DataSource, csb.UserID, csb.Password);
Server server = new Server(connection);
// Reference the AdventureWorks2012 database.
Database database = server.Databases[dbName];
foreach (var tab in tables)
{
Table table = database.Tables[tab.Name];
// Define an Index object variable by providing the parent table and index name in the constructor.
Index idx = new Index(table, tab.Name + "_PrimaryKey");
foreach (string name in tab.Values)
{
// Add indexed columns to the index.
IndexedColumn icol = new IndexedColumn(idx, name, true);
idx.IndexedColumns.Add(icol);
}
// Set the index properties.
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
idx.IsClustered = false;
idx.FillFactor = 90;
// Create the index on the instance of SQL Server.
idx.Create();
// Modify the page locks property.
idx.DisallowPageLocks = true;
// Run the Alter method to make the change on the instance of SQL Server.
idx.Alter();
}
}
/// <summary>
/// Renames index or primary key.
/// <param name="tableName">Table name</param>
/// <param name="oldName">Old name</param>
/// <param name="newName">New name</param>
/// </summary>
public static IndexDbo RenameTheIndex(string tableName, string oldName, string newName)
{
var srv = GetConnectedServer(SqlServerName, UserName, Password);
var db = srv.Databases[DatabaseName];
Table aTable = db.Tables[tableName];
if (aTable == null)
{
throw new Exception(String.Format("There is no table {0} in the {1} database.", tableName, DatabaseName));
}
Index ind = aTable.Indexes[oldName];
if (ind == null)
{
throw new Exception(String.Format("There is no index {0} in the {1} table.", oldName, tableName));
}
ind.Rename(newName);
ind.Alter();
return(GetIndexDescription(aTable, newName));;
}
public static void AddColumnToIndex(Table table, Column column, Index ind)
{
string oldName = ind.Name;
var idx = new Index(table, oldName)
{
BoundingBoxYMax = ind.BoundingBoxYMax,
BoundingBoxYMin = ind.BoundingBoxYMin,
BoundingBoxXMax = ind.BoundingBoxXMax,
BoundingBoxXMin = ind.BoundingBoxXMin,
CompactLargeObjects = ind.CompactLargeObjects,
DisallowRowLocks = ind.DisallowRowLocks,
FileGroup = ind.FileGroup,
FileStreamFileGroup = ind.FileStreamFileGroup,
FileStreamPartitionScheme = ind.FileStreamPartitionScheme,
FillFactor = ind.FillFactor,
FilterDefinition = ind.FilterDefinition,
IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys,
IndexKeyType = ind.IndexKeyType,
IsClustered = ind.IsClustered,
IsFullTextKey = ind.IsFullTextKey,
IsUnique = ind.IsUnique,
Level1Grid = ind.Level1Grid,
Level2Grid = ind.Level2Grid,
Level3Grid = ind.Level3Grid,
Level4Grid = ind.Level4Grid,
MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism,
NoAutomaticRecomputation = ind.NoAutomaticRecomputation,
OnlineIndexOperation = ind.OnlineIndexOperation,
PadIndex = ind.PadIndex,
ParentXmlIndex = ind.ParentXmlIndex,
PartitionScheme = ind.PartitionScheme,
SecondaryXmlIndexType = ind.SecondaryXmlIndexType,
SortInTempdb = ind.SortInTempdb,
SpatialIndexType = ind.SpatialIndexType,
};
foreach (IndexedColumn iColumn in ind.IndexedColumns)
{
var newIdxColumn = new IndexedColumn(idx, iColumn.Name)
{
Descending = iColumn.Descending,
IsIncluded = iColumn.IsIncluded,
};
idx.IndexedColumns.Add(newIdxColumn);
}
idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name));
bool oldDisallowPageLocks = ind.DisallowPageLocks;
ind.Drop();
idx.Create();
idx.DisallowPageLocks = oldDisallowPageLocks;
idx.Alter();
}
/// <summary>
/// Recreates primary key or index.
/// </summary>
/// <param name="table">Current table <see cref="Table"/></param>
/// <param name="descriptor">Index descriptor</param>
private static void CreateNewPrimaryKey(Table table, IndexDbo descriptor)
{
var indexKeyType = (IndexKeyType)Enum.Parse(typeof(IndexKeyType), descriptor.IndexKeyType);
byte fillFactor = (byte)(descriptor.FillFactor ?? 0);
var primaryKeyIndex = new Index(table, descriptor.Name)
{
CompactLargeObjects = descriptor.CompactLargeObjects,
FillFactor = fillFactor > 0 ? fillFactor : (byte)50,
FilterDefinition = descriptor.FilterDefinition,
IgnoreDuplicateKeys = descriptor.IgnoreDuplicateKeys,
IndexKeyType = indexKeyType,
IsClustered = descriptor.IsClustered,
IsUnique = descriptor.IsUnique,
};
foreach (string columnName in descriptor.IndexedColumns)
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, columnName));
primaryKeyIndex.Create();
primaryKeyIndex.DisallowPageLocks = descriptor.DisallowPageLocks;
primaryKeyIndex.DisallowRowLocks = descriptor.DisallowRowLocks;
primaryKeyIndex.Alter();
//if (descriptor.IsDisabled)
//{
// primaryKeyIndex.Disable();
// table.Alter();
//}
}
/// <summary>
/// Recreates primary key or index.
/// </summary>
/// <param name="table">Current table <see cref="Table"/></param>
/// <param name="indexName">Name of the index/primary key</param>
/// <param name="indexKeyType">Type of index</param>
/// <param name="columnNames">Columns in the index</param>
private static void CreateNewPrimaryKey(Table table, string indexName, IndexKeyType indexKeyType,
params string[] columnNames)
{
var primaryKeyIndex = new Index(table, indexName)
{
IndexKeyType = indexKeyType,
IsClustered = false,
FillFactor = 50
};
foreach (string columnName in columnNames)
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, columnName));
primaryKeyIndex.Create();
primaryKeyIndex.DisallowPageLocks = true;
primaryKeyIndex.Alter();
}