} // 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