/// <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();
//}
}
private void ShowFields(string indexName)
{
this.currentIndexDbo = CfcWebService.GetIndexDescription(this.currentTable, this.currentIndex);
this.lstFieldList4.DataSource = this.currentIndexDbo.IndexedColumns;
this.lstFieldList4.DataBind();
this.chkCompactLargeObjects4.Checked = currentIndexDbo.CompactLargeObjects; // 1
this.chkDisallowPageLocks4.Checked = currentIndexDbo.DisallowPageLocks; // 2
this.chkDisallowRowLocks4.Checked = currentIndexDbo.DisallowRowLocks; // 3
this.txtFillFactor4.Text = currentIndexDbo.FillFactor.ToString(); // 4
this.txtFilterDefinition4.Text = currentIndexDbo.FilterDefinition; // 5
this.chkIgnoreDuplicateKeys4.Checked = currentIndexDbo.IgnoreDuplicateKeys; // 6
string tmp = currentIndexDbo.IndexKeyType.ToString();
int i = ddlIndexKeyType4.Items.Count - 1;
while (i > 0)
{
if (String.Compare(ddlIndexKeyType4.Items[i].Value, tmp, true) == 0)
{
break;
}
else
{
i--;
}
}
ddlIndexKeyType4.SelectedIndex = i; // 7
this.chkIsClustered4.Checked = currentIndexDbo.IsClustered; // 8
this.chkIsDisabled4.Checked = currentIndexDbo.IsDisabled; // 9
this.chkIsUnique4.Checked = currentIndexDbo.IsUnique; // 10
}
public static IndexDbo GetIndexDescription(Table table, string indexName)
{
Index ind = table.Indexes[indexName];
if (ind == null)
{
throw new Exception(String.Format("Table '{0}' has no index '{1}'.", table.Name, indexName));
}
;
var dbo = new IndexDbo()
{
CompactLargeObjects = ind.CompactLargeObjects,
DisallowPageLocks = ind.DisallowPageLocks,
DisallowRowLocks = ind.DisallowRowLocks,
FillFactor = ind.FillFactor,
FilterDefinition = ind.FilterDefinition,
IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys,
IndexKeyType = ind.IndexKeyType.ToString(),
IsClustered = ind.IsClustered,
IsDisabled = ind.IsDisabled,
IsUnique = ind.IsUnique,
Name = ind.Name,
};
foreach (IndexedColumn clmn in ind.IndexedColumns)
{
dbo.IndexedColumns.Add(clmn.Name);
}
return(dbo);
}
public GetIndexResponse GetIndex(string tableName, string indexName, bool withAllFields)
{
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
IndexDbo dbo = GetIndexDescription(tableName, indexName);
var rzlt = new GetIndexResponse()
{
IsSuccess = true, Dbo = dbo
};
if (withAllFields)
{
BuildListOfFields(tableName, dbo.IndexedColumns, rzlt.AllFields);
}
trScope.Complete();
return(rzlt);
}
}
catch (Exception ex)
{
return(new GetIndexResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
}
/// <summary>
/// Creates index or primary key.
/// <param name="tableName">Table name</param>
/// <param name="dbo">Index descriptor, <see cref="IndexDbo"/></param>
/// <param name="uniqueInTable">If the value is not empty - look for equal names in this table only </param>
/// </summary>
public static IndexDbo CreateTheIndex(string tableName, IndexDbo dbo, string uniqueInTable = null)
{
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));
}
return(CreateTheIndex(aTable, dbo, uniqueInTable));
}
public static IndexDbo CreateTheIndex(Table aTable, IndexDbo dbo, string uniqueInTable)
{
string errMessage = IsIndexUnique(dbo.Name, uniqueInTable);
if (!String.IsNullOrEmpty(errMessage))
{
throw new Exception(errMessage);
}
if (dbo.IsUnique && !AreValuesUnique(aTable, dbo.IndexedColumns.ToArray()))
{
throw new Exception(String.Format("Table '{0}' contains duplicates in selected set of columns.", aTable.Name));
}
// See CreateNewPrimaryKey in the Utilities_1
CreateNewPrimaryKey(aTable, dbo);
return(GetIndexDescription(aTable, dbo.Name));
}
/// <summary>
/// Modifies trhe index
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="dbo">Index description, <see cref="IndexDbo"/></param>
/// <param name="disableDependencies"><code>true</code> - drop dependencies</param>
/// <param name="newDbo">New, updated index</param>
/// <returns>List of dropped foreign keys</returns>
private static List <DroppedDependencyDbo> UpdateTheIndex(string tableName, IndexDbo dbo, bool disableDependencies,
out IndexDbo newDbo)
{
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));
}
IndexDbo oldIndex = GetIndexDescription(aTable, dbo.Name);
if (oldIndex == dbo)
{
throw new Exception("There is nothing to change.");
}
List <DroppedDependencyDbo> droppedDependencies = DeleteTheIndex(db, aTable, dbo.Name, disableDependencies);
newDbo = CreateTheIndex(aTable, dbo, aTable.Name);
return(droppedDependencies);
}
public UpdateIndexResponse UpdateIndex(UpdateIndexRequest request, bool singleUserMode)
{
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (singleUserMode)
{
SetSingleMode(DatabaseName);
}
var dependecies = new List <DroppedDependencyDbo>();
IndexDbo dbo = null;
string logMsg = null;
switch (request.Operation)
{
case UpdateColumnOperation.Rename:
dbo = RenameTheIndex(request.Table, request.OldIndexName, request.IndexName);
logMsg = String.Format("Table '{0}': index '{1}' was renamed to '{2}'.",
request.Table, request.OldIndexName, request.IndexName);
break;
case UpdateColumnOperation.Insert:
dbo = CreateTheIndex(request.Table, request.IndexDescriptor);
logMsg = String.Format("Table '{0}': index '{1}' was created.", request.Table, request.IndexDescriptor.Name);
break;
case UpdateColumnOperation.Delete:
dependecies = DeleteTheIndex(request.Table, request.IndexName, request.DisableDependencies);
dbo = new IndexDbo()
{
Name = request.IndexName, IsDisabled = true
};
logMsg = String.Format("Table '{0}': index '{1}' was deleted.", request.Table, request.IndexName);
break;
case UpdateColumnOperation.Modify:
dependecies = UpdateTheIndex(request.Table, request.IndexDescriptor, request.DisableDependencies, out dbo);
logMsg = String.Format("Table '{0}': index '{1}' was modified.", request.Table, request.IndexDescriptor.Name);
break;
}
int recordCount = CountRecords(request.Table);
Guid historyRecordId = LogTableOperation(request.Table, logMsg, request.CFC_DB_Major_Version,
request.CFC_DB_Minor_Version);
trScope.Complete();
return(new UpdateIndexResponse()
{
IsSuccess = true,
DroppedDependencies = dependecies,
Dbo = dbo,
RecordCount = recordCount
});
}
}
catch (Exception ex)
{
return(new UpdateIndexResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
finally
{
if (singleUserMode)
{
SetMultiUserMode(DatabaseName);
}
}
}