public void CreatePrimaryKey(IndexMetadata iTableIndex)
{
try
{
StringBuilder loSqlStatement = new StringBuilder();
string loIndexName = iTableIndex.Name; // "PK_" + iTableIndex.ParentTableDef.Name
loSqlStatement.Append("ALTER TABLE [" + iTableIndex.ParentTable.Name + "] ADD CONSTRAINT " + loIndexName + " PRIMARY KEY (");
// now add all the column names
for (int loNdx = 0; loNdx < iTableIndex.GetIndexFieldCnt(); loNdx++)
{
// ask MetadataMgr for the index columns for this table that include this column at this position
loSqlStatement.Append(iTableIndex.ColumnNames(loNdx) + ",");
}
// drop the last comma
loSqlStatement.Remove(loSqlStatement.Length - 1, 1);
// add a closing parenthesis
loSqlStatement.Append(')');
// now submit the statement
_DBAccess.ExecuteNonQuery(loSqlStatement.ToString(), new Dictionary <string, object>(), true);
LogMessage(TraceLevel.Verbose, " Created Primary Key '" + loIndexName + "'", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, System.Threading.Thread.CurrentThread.ManagedThreadId);
}
catch (Exception ex)
{
LogMessage(TraceLevel.Error, " Create Primary Key failed: " + ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, System.Threading.Thread.CurrentThread.ManagedThreadId);
}
}
public bool CheckTableIndex(IndexMetadata iTableIndex)
{
if (IndexExists(iTableIndex.Name, iTableIndex.ParentTable.Name) == false)
{
return(false);
}
else
{
LogMessage(TraceLevel.Verbose, " Verified Index '" + iTableIndex.Name + "' already exists.", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, System.Threading.Thread.CurrentThread.ManagedThreadId);
return(true);
}
}
public void CreateTableIndex(IndexMetadata iTableIndex)
{
string loIndexName = iTableIndex.Name;
if (iTableIndex.IsPrimaryKey)
{
CreatePrimaryKey(iTableIndex);
return;
}
// We are creating index because it doesn't exist, not because it might be different, so there is no existing index to drop...
/*
* // drop this index if it exists
* try
* {
* _DBAccess.ExecuteNonQuery("DROP INDEX " + loIndexName + " ON [" + iTableIndex.ParentTableDef.Name + "]", new Dictionary<string, object>(), true);
* }
* catch { }
*/
StringBuilder loSqlStatement = new StringBuilder();
loSqlStatement.Append("CREATE ");
if (iTableIndex.IsUniqueConstraint)
{
loSqlStatement.Append("UNIQUE ");
}
loSqlStatement.Append("INDEX " + loIndexName + " ON [" + iTableIndex.ParentTable.Name + "] (");
// now add all the column names
for (int loNdx = 0; loNdx < iTableIndex.GetIndexFieldCnt(); loNdx++)
{
// ask MetadataMgr for the index columns for this table that include this column at this position
loSqlStatement.Append(iTableIndex.ColumnNames(loNdx) + ",");
}
// drop the last comma
loSqlStatement.Remove(loSqlStatement.Length - 1, 1);
// add a closing parenthesis
loSqlStatement.Append(')');
// now submit the statement
try
{
_DBAccess.ExecuteNonQuery(loSqlStatement.ToString(), new Dictionary <string, object>(), true);
LogMessage(TraceLevel.Verbose, " Created Index '" + iTableIndex.Name + "'", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, System.Threading.Thread.CurrentThread.ManagedThreadId);
}
catch (Exception ex)
{
LogMessage(TraceLevel.Error, " Create index failed: " + ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, System.Threading.Thread.CurrentThread.ManagedThreadId);
}
}