//----------------------------------------------------------------------------------------------------
public static string BuildSql__Table_UpdateWithRecreate(TableStructure newTable, TableStructure oldTable)
{
AjaxService.ASPdatabaseService.GetSetVal();
//tableStructure.Validate(Enums.ValidationTypes.Update);
// ------------------------ ENSURE COLUMNS MATCH
/* Steps
* 1) Foreach FK_Side Foreign Key:
* a) BEGIN TRANSACTION
* b) Drop Foreign Keys --> oldTable
* c) ALTER TABLE [Schema].[TableName] SET (LOCK_ESCALATION = TABLE)
* d) COMMIT
*
* 2) BEGIN TRANSACTION
* 3) CREATE TABLE --> Temp
* 4) ALTER TABLE [Schema].[Temp] SET (LOCK_ESCALATION = TABLE)
* 5) SET IDENTITY_INSERT [Schema].[Temp] ON
* 6) IF EXISTS(SELECT * FROM [Schema].[TableName]) ... EXEC('INSERT INTO ... [Schema].[Temp]
* 7) SET IDENTITY_INSERT [Schema].[Temp] OFF
*
* 8) DROP PK_Side Foreign Keys
* 9) DROP TABLE [Schema].[TableName]
* 10) EXECUTE sp_rename N'Schema.Temp', N'TableName', 'OBJECT'
*
* 11) Add Primary Key
* 12) Add Indexes
* 13) Add Foreign Keys
* 14) COMMIT
*/
string sql = "";
string schema = oldTable.Schema;
string tableName = oldTable.TableName;
string tempTableName = "ASPdb__TEMP__" + oldTable.TableName + "__1";
bool newTableHasIdentity = false;
foreach (var column in newTable.Columns)
{
if (column.Identity != null)
{
newTableHasIdentity = true;
}
}
newTable.SetTableName(tempTableName); // ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
sql += String.Format(@"
BEGIN TRANSACTION T0
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
");
sql += String.Format(@"
IF OBJECT_ID('{0}.{1}', 'U') IS NOT NULL
DROP TABLE [{0}].[{1}]
", schema, tempTableName);
if (oldTable.ForeignKeys_FK != null)
{
foreach (var foreignKey in oldTable.ForeignKeys_FK)
{
sql += String.Format(@"
ALTER TABLE [{0}].[{1}]
DROP CONSTRAINT [{2}]
ALTER TABLE [{3}].[{4}] SET (LOCK_ESCALATION = TABLE)
",
schema,
tableName,
foreignKey.ConstraintName,
foreignKey.PrimaryKey_Schema,
foreignKey.PrimaryKey_TableName);
}
}
sql += BuildSql__Table_Create(newTable);
var tmpPrimaryKey = new PrimaryKey()
{
ConnectionId = newTable.ConnectionId, Schema = newTable.Schema, TableId = newTable.TableId, TableName = newTable.TableName
};
tmpPrimaryKey.Columns = new PrimaryKeyColumn[oldTable.PrimaryKey.Columns.Length];
for (int i = 0; i < oldTable.PrimaryKey.Columns.Length; i++)
{
tmpPrimaryKey.Columns[i] = oldTable.PrimaryKey.Columns[i];
}
string tmpConstraintName = oldTable.PrimaryKey.ConstraintName;
if (!tmpConstraintName.Contains("__"))
{
tmpConstraintName += "__1";
}
else
{
string rightPart = tmpConstraintName.Split(new string[] { "__" }, StringSplitOptions.None).Last();
int numb = -1;
Int32.TryParse(rightPart, out numb);
if (numb < 0)
{
tmpConstraintName += "__1";
}
else
{
tmpConstraintName = tmpConstraintName.Substring(0, tmpConstraintName.Length - rightPart.Length) + (numb + 1).ToString();
}
}
tmpPrimaryKey.ConstraintName = tmpConstraintName;
if (oldTable.PrimaryKey != null)
{
sql += BuildSql__PrimaryKey_Create(tmpPrimaryKey, false, null, true) + @"
";
}
string[] arr_Columns = (from c in newTable.Columns select c.ColumnName).ToArray();
string sql_ColumnsCommaList = Str.Join(arr_Columns, "\n [", "], \n [", "]");
sql += String.Format(@"
ALTER TABLE [{0}].[{1}] SET (LOCK_ESCALATION = TABLE)
", schema, tempTableName);
if (newTableHasIdentity)
{
sql += String.Format(@"
SET IDENTITY_INSERT [{0}].[{1}] ON
", schema, tempTableName);
}
sql += String.Format(@"
IF EXISTS(SELECT * FROM [{0}].[{1}])
EXEC('INSERT INTO [{0}].[{2}] ( {3} )
SELECT {3}
FROM [{0}].[{1}] WITH (HOLDLOCK, TABLOCKX)')
", schema, tableName, tempTableName, sql_ColumnsCommaList);
if (newTableHasIdentity)
{
sql += String.Format(@"
SET IDENTITY_INSERT [{0}].[{1}] OFF
", schema, tempTableName);
}
if (oldTable.ForeignKeys_PK != null)
{
foreach (var foreignKey in oldTable.ForeignKeys_PK)
{
sql += String.Format(@"
ALTER TABLE [{0}].[{1}]
DROP CONSTRAINT [{2}]
", foreignKey.ForeignKey_Schema, foreignKey.ForeignKey_TableName, foreignKey.ConstraintName);
}
}
sql += String.Format(@"
DROP TABLE [{0}].[{1}]
EXECUTE sp_rename N'{0}.{2}', N'{1}', 'OBJECT'
", schema, tableName, tempTableName);
// if (oldTable.PrimaryKey != null)
// sql += BuildSql__PrimaryKey_Create(oldTable.PrimaryKey, false, null) + @"
// ";
if (oldTable.Indexes != null)
{
foreach (var index in oldTable.Indexes)
{
sql += @"
" + BuildSql__Index_Create(index, false, null) + @"
";
}
}
if (oldTable.ForeignKeys_FK != null)
{
foreach (var foreignKey in oldTable.ForeignKeys_FK)
{
sql += BuildSql__ForeignKey_Create(foreignKey, false, null) + @"
";
}
}
if (oldTable.ForeignKeys_PK != null)
{
foreach (var foreignKey in oldTable.ForeignKeys_PK)
{
sql += String.Format(@"
{0}
ALTER TABLE [{1}].[{2}] SET (LOCK_ESCALATION = TABLE)
",
BuildSql__ForeignKey_Create(foreignKey, false, null),
foreignKey.ForeignKey_Schema,
foreignKey.ForeignKey_TableName);
}
}
sql += @"
COMMIT TRANSACTION T0
";
return(sql);
}