//----------------------------------------------------------------------------------------------------
private static void PrimaryKey__SeeIfIdentityChanged(PrimaryKey primaryKey)
{
bool saveIsNeeded = false;
var currentTableStructure = Tables__Get(primaryKey.TableId, true);
Identity currentIdentity = null;
foreach (var col in currentTableStructure.Columns)
{
if (col.Identity != null)
{
currentIdentity = col.Identity;
}
}
Identity newIdentity = null;
foreach (var col in primaryKey.Columns)
{
if (col.Identity != null)
{
newIdentity = col.Identity;
}
}
if (currentIdentity == null && newIdentity == null)
{
saveIsNeeded = false;
}
else if (currentIdentity != null && newIdentity == null)
{
saveIsNeeded = true;
}
else if (currentIdentity == null && newIdentity != null)
{
saveIsNeeded = true;
}
else
{
if (currentIdentity.ColumnName.ToLower() != newIdentity.ColumnName.ToLower())
{
saveIsNeeded = true;
}
else if (currentIdentity.Seed != newIdentity.Seed)
{
saveIsNeeded = true;
}
else if (currentIdentity.Increment != newIdentity.Increment)
{
saveIsNeeded = true;
}
}
if (saveIsNeeded)
{
var newTableStructure = Tables__Get(primaryKey.TableId, true);
foreach (var column in newTableStructure.Columns)
{
column.IsIdentity = false;
column.Identity = null;
if (newIdentity != null && column.ColumnName.ToLower() == newIdentity.ColumnName.ToLower())
{
column.IsIdentity = true;
column.Identity = newIdentity;
}
}
string sql = SQLServer_SQLBuilders.BuildSql__Table_UpdateWithRecreate(newTableStructure, currentTableStructure);
ASPdb.Framework.Debug.WriteLine(sql);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(currentTableStructure.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
}
//----------------------------------------------------------------------------------------------------
public static TableDesignResponse Table__Update(TableStructure newTableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new TableDesignResponse()
{
ResponseType = TableDesignResponse.ResponseTypesEnum.UpdateTable
};
var currentTableStructure = Tables__Get(newTableStructure.TableId, true);
var currentColumnsDict = new Dictionary <string, Column>();
foreach (var column in currentTableStructure.Columns)
{
if (!currentColumnsDict.ContainsKey(column.ColumnName.ToLower()))
{
currentColumnsDict.Add(column.ColumnName.ToLower(), column);
}
}
var columnsList_All = new List <Column>();
var columnsList_Delete = new List <Column>();
var columnsList_Add = new List <Column>();
var columnsList_Update = new List <Column>();
bool reorder_Needed = false;
int tmpOrdinalPos = 1;
//var cols = (from c in newTableStructure.Columns orderby c.OrdinalPosition, c.ColumnName select c)
//--------------------
foreach (var column in (from c in newTableStructure.Columns orderby c.OrdinalPosition, c.ColumnName select c))
{
if (column.HasValidContent())
{
column.Validate(Enums.ValidationTypes.NotSet);
column.ConnectionId = currentTableStructure.ConnectionId;
column.Schema = currentTableStructure.Schema;
column.TableName = currentTableStructure.TableName;
column.OrdinalPosition = tmpOrdinalPos++;
column.Fix_DefaultValue_ForSaving();
columnsList_All.Add(column);
switch (column.ChangeAction)
{
case Column.ChangeActionTypes.Delete:
columnsList_Delete.Add(column);
break;
case Column.ChangeActionTypes.Update:
if (Table__Update__IsColumnDifferent(column, currentColumnsDict))
{
columnsList_Update.Add(column);
}
break;
case Column.ChangeActionTypes.Add:
columnsList_Add.Add(column);
reorder_Needed = true;
break;
}
}
}
newTableStructure.Columns = columnsList_All.ToArray();
//--------------------
foreach (var column in columnsList_Delete)
{
Column__Delete(column);
}
foreach (var column in columnsList_Update)
{
Column__Update(column);
}
foreach (var column in columnsList_Add)
{
Column__Create(column);
}
//--------------------
if (!reorder_Needed)
{
foreach (var column in newTableStructure.Columns)
{
if (currentColumnsDict.ContainsKey(column.ColumnName_Original.ToLower()))
{
var columnCurrent = currentColumnsDict[column.ColumnName_Original.ToLower()];
if (columnCurrent.OrdinalPosition != column.OrdinalPosition)
{
reorder_Needed = true;
}
}
}
}
//--------------------
if (reorder_Needed)
{
string sql = SQLServer_SQLBuilders.BuildSql__Table_UpdateWithRecreate(newTableStructure, currentTableStructure);
ASPdb.Framework.Debug.WriteLine("------------------------------");
ASPdb.Framework.Debug.WriteLine("SQL: " + sql);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(currentTableStructure.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
Memory.AppCache.Reset();
var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(newTableStructure.TableId, false, true, true);
return(rtn);
}