} // End Function MergeStatementForTable
public static string MergeStatementForTable(
System.Data.Common.DbConnection conn
, string table_schema
, string table_name
, string dataSQL
, object param = null
, System.Data.IDbTransaction transaction = null
, int?commandTimeout = null
, System.Data.CommandType?commandType = null
)
{
string sql = System.IO.Path.Combine("SQL", "Schema.Merge.sql");
sql = System.IO.File.ReadAllText(sql, System.Text.Encoding.UTF8);
System.Collections.Generic.IEnumerable <MergeSchemaInfo> mis = conn.Query <MergeSchemaInfo>(sql, new { __table_schema = table_schema, __table_name = table_name });
System.Text.StringBuilder xmlBuilder = new System.Text.StringBuilder();
using (System.Xml.XmlWriter writer = CreateXmlWriter(xmlBuilder))
{
conn.AsXml(table_schema, table_name, writer, dataSQL, param, transaction, commandTimeout, commandType);
} // End Using writer
return(GetMergeScript(table_schema, table_name, dataSQL, true, mis, xmlBuilder));
} // End Sub MergeStatementForTable
} // End Sub SlickListBasedOnSample
internal static string SlickListBasedOnSample(string sampleId, string newSlickListUID)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string slickListTitle;
string slickList;
string slickListColumnNames;
string slickListColumns;
string table_schema = "dbo";
string table_name = null;
string sql = null;
sampleId = sampleId.Replace("'", "''");
newSlickListUID = newSlickListUID.Replace("'", "''");
System.Action <string> AppendOutput =
delegate(string mergeStatement)
{
sb.Append("-- Table: ");
sb.Append(table_schema);
sb.Append(".");
sb.Append(table_name);
sb.AppendLine(mergeStatement);
sb.AppendLine(System.Environment.NewLine);
sb.AppendLine("GO");
sb.AppendLine(System.Environment.NewLine);
sb.AppendLine(System.Environment.NewLine);
}
;
SqlService service = new SqlService();
using (System.Data.Common.DbConnection conn = service.Connection)
{
#if false
sql = @"
IF OBJECT_ID('tempdb..##tempSlickColumnInsertMapper') IS NOT NULL
EXECUTE('DROP TABLE ##tempSlickColumnInsertMapper; ');
";
conn.Execute(sql);
#endif
table_name = "T_SYS_Language_Forms";
sql = @"
SELECT TOP 1
'" + newSlickListUID + @"' AS LANG_UID
,LANG_Modul
,LANG_Object
,LANG_Register
,LANG_Position
,LANG_DE
,LANG_FR
,LANG_EN
,LANG_IT
,LANG_Fieldname
,LANG_FieldType
,LANG_IsRequired
,LANG_Validate
,LANG_Reftable
,LANG_CheckHistory
,LANG_LUT_UID
,LANG_IsValidity
,CURRENT_TIMESTAMP AS LANG_ErfDate
,LANG_Status
,LANG_Tooltip_DE
,LANG_Tooltip_EN
,LANG_Tooltip_FR
,LANG_Tooltip_IT
FROM T_SYS_Language_Forms
WHERE LANG_UID = ( SELECT TOP 1 SL_LANG_UID FROM T_COR_Slicklist WHERE SL_UID = '" + sampleId + @"' )
";
slickListTitle = MergeStatementForTable(conn, table_schema, table_name, sql);
AppendOutput(slickListTitle);
table_name = "T_COR_Slicklist";
sql = @"
SELECT
'" + newSlickListUID + @"' AS SL_UID
,SL_SQL
,SL_SQL_onChanges
,SL_asyncEditorLoading
,SL_autoEdit
,SL_autoHeight
,SL_defaultColumnWidth
,SL_defaultSortString
,SL_editable
,SL_enableAddRow
,SL_enableCellNavigation
,SL_enableColumnReorder
,SL_forceFitColumns
,SL_hasCheckbox
,SL_headerRowHeight
,SL_leaveSpaceForNewRows
,SL_multiSelect
,SL_rowHeight
,SL_showHeaderRow
,SL_showTopPanel
,SL_Lang_DE
,SL_Lang_EN
,SL_Lang_FR
,SL_Lang_IT
,SL_groupingKey
,'" + newSlickListUID + @"' AS SL_LANG_UID
FROM T_COR_Slicklist
WHERE SL_UID = '" + sampleId + @"'
";
slickList = MergeStatementForTable(conn, table_schema, table_name, sql);
AppendOutput(slickList);
sql = @"
SELECT
NEWID() AS SLCOL_UID
,NEWID() AS SLCOL_LANG_UID -- T_SYS_Language_Forms.LANG_UID
,SLCOL_UID AS old_SLCOL_UID
,SLCOL_LANG_UID AS old_SLCOL_LANG_UID
-- INTO ##tempSlickColumnInsertMapper
FROM T_COR_Ref_Slickcolumn
-- WHERE SLCOL_SL_UID = '8E5523E3-32D1-4018-0000-000000000000'
WHERE SLCOL_SL_UID = '" + sampleId + @"'
ORDER BY SLCOL_Sort
";
System.Text.StringBuilder xmlBuilder = new System.Text.StringBuilder();
using (System.Xml.XmlWriter writer = CreateXmlWriter(xmlBuilder))
{
conn.AsXml(null, null, writer, sql);
} // End Using writer
string xml = xmlBuilder.ToString();
xmlBuilder.Length = 0;
xmlBuilder = null;
/*
* DECLARE @foo xml
* SET @foo = CONVERT(xml, @xmlColumnMap)
*
*
* SELECT
* doc.col.value('SLCOL_UID[1]', 'uniqueidentifier') ponumber
* ,doc.col.value('SLCOL_LANG_UID[1]', 'uniqueidentifier') podate
*
* FROM @foo.nodes('//row/*') AS doc(col)
*
*/
table_name = "T_SYS_Language_Forms";
sql = @"
/*
SELECT
SLCOL_LANG_UID AS LANG_UID
,LANG_Modul
,LANG_Object
,LANG_Register
,LANG_Position
,LANG_DE
,LANG_FR
,LANG_EN
,LANG_IT
,LANG_Fieldname
,LANG_FieldType
,LANG_IsRequired
,LANG_Validate
,LANG_Reftable
,LANG_CheckHistory
,LANG_LUT_UID
,LANG_IsValidity
,CURRENT_TIMESTAMP AS LANG_ErfDate
,LANG_Status
,LANG_Tooltip_DE
,LANG_Tooltip_EN
,LANG_Tooltip_FR
,LANG_Tooltip_IT
FROM ##tempSlickColumnInsertMapper
LEFT JOIN T_SYS_Language_Forms
ON T_SYS_Language_Forms.LANG_UID = old_SLCOL_LANG_UID
*/
DECLARE @xmlColMap XML
SET @xmlColMap = CONVERT(XML, @xmlColumnMap)
;WITH CTE AS
(
SELECT
/*
CAST
(
REPLACE(
REPLACE(
REPLACE(
replace(doc.col.value('(./SLCOL_UID)[1]', 'varchar(MAX)'), char(9), '')
,CHAR(13),'')
,CHAR(10), '')
,' ', '')
AS uniqueidentifier
) AS SLCOL_UID
*/
doc.col.value('SLCOL_UID[1]', 'uniqueidentifier') AS SLCOL_UID
,doc.col.value('SLCOL_LANG_UID[1]', 'uniqueidentifier') AS SLCOL_LANG_UID
,doc.col.value('old_SLCOL_UID[1]', 'uniqueidentifier') AS old_SLCOL_UID
,doc.col.value('old_SLCOL_LANG_UID[1]', 'uniqueidentifier') AS old_SLCOL_LANG_UID
--,doc.col.value('SLCOL_LANG_UID[1]', 'uniqueidentifier') podate
FROM @xmlColMap.nodes('//row') AS doc(col)
)
SELECT
SLCOL_LANG_UID AS LANG_UID
,LANG_Modul
,LANG_Object
,LANG_Register
,LANG_Position
,LANG_DE
,LANG_FR
,LANG_EN
,LANG_IT
,LANG_Fieldname
,LANG_FieldType
,LANG_IsRequired
,LANG_Validate
,LANG_Reftable
,LANG_CheckHistory
,LANG_LUT_UID
,LANG_IsValidity
,CURRENT_TIMESTAMP AS LANG_ErfDate
,LANG_Status
,LANG_Tooltip_DE
,LANG_Tooltip_EN
,LANG_Tooltip_FR
,LANG_Tooltip_IT
FROM CTE
LEFT JOIN T_SYS_Language_Forms
ON T_SYS_Language_Forms.LANG_UID = old_SLCOL_LANG_UID
";
slickListColumnNames = MergeStatementForTable(conn, table_schema, table_name, sql, new { xmlColumnMap = xml });
AppendOutput(slickListColumnNames);
table_name = "T_COR_Ref_Slickcolumn";
sql = @"
/*
SELECT
tColumnMap.SLCOL_UID AS SLCOL_UID
,'" + newSlickListUID + @"' AS SLCOL_SL_UID
,SLCOL_MOD_UID
,SLCOL_LANG_DE
,SLCOL_LANG_EN
,SLCOL_LANG_FR
,SLCOL_LANG_IT
,SLCOL_Sort
,SLCOL_Status
,SLCOL_asyncPostRender
,SLCOL_backgroundcolorfield
,SLCOL_cannotTriggerInsert
,SLCOL_colorfield
,SLCOL_cssClass
,NULL AS SLCOL_displayfield
,SLCOL_editor
,SLCOL_field
,SLCOL_footer
,SLCOL_formatter
,SLCOL_headerCssClass
,SLCOL_minWidth
,SLCOL_maxWidth
,SLCOL_name
,NULL AS SLCOL_referenceTablename
,SLCOL_required
,SLCOL_requiredFieldRead
,SLCOL_requiredFieldWrite
,SLCOL_rerenderOnResize
,SLCOL_resizable
,SLCOL_show
,SLCOL_showInHeaderRow
,SLCOL_sortable
,NULL AS SLCOL_sorter
,SLCOL_tooltip
,SLCOL_unselectable
,SLCOL_width
,SLCOL_includeInExport
,SLCOL_export
,NULL AS SLCOL_referenceSQL
,tColumnMap.SLCOL_LANG_UID -- T_SYS_Language_Forms.LANG_UID
,0 AS SLCOL_multiple
,SLCOL_formatString
FROM ##tempSlickColumnInsertMapper AS tColumnMap
LEFT JOIN T_COR_Ref_Slickcolumn
ON T_COR_Ref_Slickcolumn.SLCOL_UID = tColumnMap.old_SLCOL_UID
WHERE SLCOL_SL_UID = '" + sampleId + @"'
-- WHERE SLCOL_SL_UID = '8E5523E3-32D1-4018-0000-000000000000'
ORDER BY SLCOL_Sort
*/
DECLARE @xmlColMap XML
SET @xmlColMap = CONVERT(XML, @xmlColumnMap)
;WITH CTE AS
(
SELECT
doc.col.value('SLCOL_UID[1]', 'uniqueidentifier') AS SLCOL_UID
,doc.col.value('SLCOL_LANG_UID[1]', 'uniqueidentifier') AS SLCOL_LANG_UID
,doc.col.value('old_SLCOL_UID[1]', 'uniqueidentifier') AS old_SLCOL_UID
,doc.col.value('old_SLCOL_LANG_UID[1]', 'uniqueidentifier') AS old_SLCOL_LANG_UID
FROM @xmlColMap.nodes('//row') AS doc(col)
)
SELECT
tColumnMap.SLCOL_UID AS SLCOL_UID
,'" + newSlickListUID + @"' AS SLCOL_SL_UID
,SLCOL_MOD_UID
,SLCOL_LANG_DE
,SLCOL_LANG_EN
,SLCOL_LANG_FR
,SLCOL_LANG_IT
,SLCOL_Sort
,SLCOL_Status
,SLCOL_asyncPostRender
,SLCOL_backgroundcolorfield
,SLCOL_cannotTriggerInsert
,SLCOL_colorfield
,SLCOL_cssClass
,NULL AS SLCOL_displayfield
,SLCOL_editor
,SLCOL_field
,SLCOL_footer
,SLCOL_formatter
,SLCOL_headerCssClass
,SLCOL_minWidth
,SLCOL_maxWidth
,SLCOL_name
,NULL AS SLCOL_referenceTablename
,SLCOL_required
,SLCOL_requiredFieldRead
,SLCOL_requiredFieldWrite
,SLCOL_rerenderOnResize
,SLCOL_resizable
,SLCOL_show
,SLCOL_showInHeaderRow
,SLCOL_sortable
,NULL AS SLCOL_sorter
,SLCOL_tooltip
,SLCOL_unselectable
,SLCOL_width
,SLCOL_includeInExport
,SLCOL_export
,NULL AS SLCOL_referenceSQL
,tColumnMap.SLCOL_LANG_UID -- T_SYS_Language_Forms.LANG_UID
,0 AS SLCOL_multiple
,SLCOL_formatString
FROM CTE AS tColumnMap
LEFT JOIN T_COR_Ref_Slickcolumn
ON T_COR_Ref_Slickcolumn.SLCOL_UID = tColumnMap.old_SLCOL_UID
WHERE SLCOL_SL_UID = '" + sampleId + @"'
-- WHERE SLCOL_SL_UID = '8E5523E3-32D1-4018-0000-000000000000'
-- WHERE SLCOL_SL_UID = '67ef86e5-64cf-4300-0000-000000000000'
ORDER BY SLCOL_Sort
";
slickListColumns = MergeStatementForTable(conn, table_schema, table_name, sql, new { xmlColumnMap = xml });
AppendOutput(slickListColumns);
#if false
sql = @"
IF OBJECT_ID('tempdb..##tempSlickColumnInsertMapper') IS NOT NULL
EXECUTE('DROP TABLE ##tempSlickColumnInsertMapper; ');
";
conn.Execute(sql);
#endif
} // End Using conn
System.Console.WriteLine(slickListTitle);
System.IO.File.WriteAllText(@"D:\01_slickListTitle.sql", slickListTitle, System.Text.Encoding.UTF8);
System.Console.WriteLine(slickList);
System.IO.File.WriteAllText(@"D:\02_slickList.sql", slickList, System.Text.Encoding.UTF8);
System.Console.WriteLine(slickListColumnNames);
System.IO.File.WriteAllText(@"D:\03_slickListColumnNames.sql", slickListColumnNames, System.Text.Encoding.UTF8);
System.Console.WriteLine(slickListColumns);
System.IO.File.WriteAllText(@"D:\04_slickListColumns.sql", slickListColumns, System.Text.Encoding.UTF8);
string allInOne = sb.ToString();
sb.Length = 0;
sb = null;
System.Console.WriteLine(allInOne);
return(allInOne);
} // End Function SlickListBasedOnSample
