//----------------------------------------------------------------------------------------------------
public void Set(TableDesignResponse tableDesignResponse)
{
this.ConnectionId = tableDesignResponse.ConnectionId;
this.TableId = tableDesignResponse.TableId;
this.TableStructure = tableDesignResponse.TableStructure;
this.AllTables_InDb = tableDesignResponse.AllTables_InDb;
}
//----------------------------------------------------------------------------------------------------
public static TableDesignResponse GetInfo_ForModify(int tableId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new TableDesignResponse()
{
ResponseType = TableDesignResponse.ResponseTypesEnum.GetInfo_ForModify
};
rtn.ConnectionId = -1;
rtn.TableId = tableId;
rtn.TableStructure = DbInterfaces.SQLServerInterface.Tables__Get(tableId, true, false, false);
if (rtn.TableStructure == null)
{
throw new Exception("rtn.TableStructure is null");
}
var connectionInfo = DataAccess.DatabaseConnectionsCRUD.Get(rtn.TableStructure.ConnectionId, false);
rtn.ConnectionName = connectionInfo.SingleConnectionInfo.ConnectionName;
rtn.TableStructure.ConnectionName = connectionInfo.SingleConnectionInfo.ConnectionName;
rtn.ConnectionId = rtn.TableStructure.ConnectionId;
rtn.Schema = rtn.TableStructure.Schema;
rtn.TableName = rtn.TableStructure.TableName;
rtn.AllTables_InDb = SQLObjectsCRUD.ASPdb_Table__GetAll(rtn.ConnectionId, true).ToArray();
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static TableDesignResponse Table__Create(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new TableDesignResponse()
{
ResponseType = TableDesignResponse.ResponseTypesEnum.CreateTable,
ConnectionId = tableStructure.ConnectionId, Schema = tableStructure.Schema, TableName = tableStructure.TableName
};
tableStructure.Columns = (from c in tableStructure.Columns
where c.ColumnName.Length > 0 &&
c.DataType.Length > 0
orderby c.OrdinalPosition
select c).ToArray();
var primaryKey = new PrimaryKey()
{
ConnectionId = tableStructure.ConnectionId, TableId = tableStructure.TableId, Schema = tableStructure.Schema, TableName = tableStructure.TableName
};
var tmp_PrimaryKeyColumns = new List <PrimaryKeyColumn>();
int i = 1;
foreach (var column in tableStructure.Columns)
{
if (column.IsPrimaryKey)
{
tmp_PrimaryKeyColumns.Add(new PrimaryKeyColumn()
{
ColumnName = column.ColumnName, OrdinalPosition = i++
});
}
column.Fix_DefaultValue_ForSaving();
}
primaryKey.Columns = tmp_PrimaryKeyColumns.ToArray();
string sql = SQLServer_SQLBuilders.BuildSql__Table_Create(tableStructure);
ASPdb.Framework.Debug.WriteLine(sql);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
PrimaryKey__Create(primaryKey, false);
rtn.TableId = -1;
Memory.AppCache.Reset();
var allTables = DataAccess.SQLObjectsCRUD.ASPdb_Table__GetAll(tableStructure.ConnectionId, true);
foreach (var table in allTables)
{
if (table.Schema.ToLower() == rtn.Schema.ToLower() &&
table.TableName.ToLower() == rtn.TableName.ToLower())
{
rtn.TableId = table.TableId;
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static TableDesignResponse Table__Delete(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new TableDesignResponse()
{
ResponseType = TableDesignResponse.ResponseTypesEnum.DeleteTable
};
string sql = SQLServer_SQLBuilders.BuildSql__Table_Drop(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
Memory.AppCache.Reset();
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static TableDesignResponse GetInfo_ForCreateNew(int connectionId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new TableDesignResponse()
{
ResponseType = TableDesignResponse.ResponseTypesEnum.GetInfo_ForCreateNew
};
rtn.ConnectionId = connectionId;
rtn.TableId = -1;
var connectionInfo = DataAccess.DatabaseConnectionsCRUD.Get(connectionId, false);
rtn.ConnectionName = connectionInfo.SingleConnectionInfo.ConnectionName;
var schemaInfos = DbInterfaces.GenericInterface.Schemas__GetAll(connectionId);
rtn.Schemas = (from s in schemaInfos orderby s.SchemaName select s.SchemaName).ToArray();
rtn.AllTables_InDb = SQLObjectsCRUD.ASPdb_Table__GetAll(rtn.ConnectionId, true).ToArray();
return(rtn);
}
//----------------------------------------------------------------------------------------------------
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);
}
