/// <summary> /// Delete entity in table "Ts". /// </summary> /// <typeparam name="T">Type of entity</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="entityToDelete">Entity to delete</param> /// <param name="transaction">The transaction to run under, null (the default) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>true if deleted, false if not found</returns> public static bool Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { if (entityToDelete == null) { throw new ArgumentException("Cannot Delete null Object", nameof(entityToDelete)); } var type = typeof(T); if (type.IsArray) { type = type.GetElementType(); } else if (VaravuselavuStandard.Util.TypeExtensions.IsGenericType(type)) { type = type.GetGenericArguments()[0]; } var keyProperties = KeyPropertiesCache(type).ToList(); //added ToList() due to issue #418, must work on a list copy var explicitKeyProperties = ExplicitKeyPropertiesCache(type); if (!keyProperties.Any() && !explicitKeyProperties.Any()) { throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property"); } var name = GetTableName(type); keyProperties.AddRange(explicitKeyProperties); var sb = new StringBuilder(); sb.AppendFormat("delete from {0} where ", name); var quotedColumnMap = QuotedColumnCache(type); var adapter = GetFormatter(connection); for (var i = 0; i < keyProperties.Count; i++) { var property = keyProperties.ElementAt(i); var colName = property.Name; if (quotedColumnMap.TryGetValue(property.Name, out colName)) { adapter.AppendQuotedColumnNameEqualsValue(sb, colName); } else { adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336 } if (i < keyProperties.Count - 1) { sb.AppendFormat(" and "); } } var deleted = connection.Execute(sb.ToString(), entityToDelete, transaction, commandTimeout); return(deleted > 0); }
/// <summary> /// Delete all entities in the table related to the type T. /// </summary> /// <typeparam name="T">Type of entity</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="transaction">The transaction to run under, null (the defualt) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>true if deleted, false if none found</returns> public static bool DeleteAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { var type = typeof(T); var name = GetTableName(type); var statement = $"delete from {name}"; var deleted = connection.Execute(statement, null, transaction, commandTimeout); return(deleted > 0); }
/// <summary> /// Delete entity in table "Ts". /// </summary> /// <typeparam name="T">Type of entity</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="entityToDelete">Entity to delete</param> /// <param name="transaction">The transaction to run under, null (the defualt) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>true if deleted, false if not found</returns> public static bool Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { if (entityToDelete == null) { throw new ArgumentException("Cannot Delete null Object", nameof(entityToDelete)); } var type = typeof(T); if (type.IsArray || type.IsGenericType()) { type = type.GetGenericArguments()[0]; } var keyProperties = KeyPropertiesCache(type); var explicitKeyProperties = ExplicitKeyPropertiesCache(type); if (!keyProperties.Any() && !explicitKeyProperties.Any()) { throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property"); } var name = GetTableName(type); keyProperties.AddRange(explicitKeyProperties); var sb = new StringBuilder(); sb.AppendFormat("delete from {0} where ", name); var adapter = GetFormatter(connection); for (var i = 0; i < keyProperties.Count; i++) { var property = keyProperties.ElementAt(i); adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336 if (i < keyProperties.Count - 1) { sb.AppendFormat(" and "); } } var deleted = connection.Execute(sb.ToString(), entityToDelete, transaction, commandTimeout); return(deleted > 0); }
/// <summary> /// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension. /// </summary> /// <typeparam name="T">Type to be updated</typeparam> /// <param name="connection">Open SqlConnection</param> /// <param name="entityToUpdate">Entity to be updated</param> /// <param name="transaction">The transaction to run under, null (the defualt) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>true if updated, false if not found or not modified (tracked entities)</returns> public static bool Update <T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { var proxy = entityToUpdate as IProxy; if (proxy != null) { if (!proxy.IsDirty) { return(false); } } var type = typeof(T); if (type.IsArray) { type = type.GetElementType(); } else if (type.IsGenericType()) { type = type.GetGenericArguments()[0]; } var keyProperties = KeyPropertiesCache(type).ToList(); //added ToList() due to issue #418, must work on a list copy var explicitKeyProperties = ExplicitKeyPropertiesCache(type); if (!keyProperties.Any() && !explicitKeyProperties.Any()) { throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property"); } var name = GetTableName(type); var sb = new StringBuilder(); sb.AppendFormat("update {0} set ", name); var allProperties = TypePropertiesCache(type); keyProperties.AddRange(explicitKeyProperties); var computedProperties = ComputedPropertiesCache(type); var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList(); var adapter = GetFormatter(connection); for (var i = 0; i < nonIdProps.Count; i++) { var property = nonIdProps.ElementAt(i); adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336 if (i < nonIdProps.Count - 1) { sb.AppendFormat(", "); } } sb.Append(" where "); for (var i = 0; i < keyProperties.Count; i++) { var property = keyProperties.ElementAt(i); adapter.AppendColumnNameEqualsValue(sb, property.Name); //fix for issue #336 if (i < keyProperties.Count - 1) { sb.AppendFormat(" and "); } } var updated = connection.Execute(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction); return(updated > 0); }
/// <summary> /// Inserts an entity into table "Ts" and returns identity id or number if inserted rows if inserting a list. /// </summary> /// <param name="connection">Open SqlConnection</param> /// <param name="entityToInsert">Entity to insert, can be list of entities</param> /// <param name="transaction">The transaction to run under, null (the defualt) if none</param> /// <param name="commandTimeout">Number of seconds before command execution timeout</param> /// <returns>Identity of inserted entity, or number of inserted rows if inserting a list</returns> public static long Insert <T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { var isList = false; var type = typeof(T); if (type.IsArray) { isList = true; type = type.GetElementType(); } else if (type.IsGenericType()) { isList = true; type = type.GetGenericArguments()[0]; } var name = GetTableName(type); var sbColumnList = new StringBuilder(null); var allProperties = TypePropertiesCache(type); var keyProperties = KeyPropertiesCache(type); var computedProperties = ComputedPropertiesCache(type); var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList(); var adapter = GetFormatter(connection); for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++) { var property = allPropertiesExceptKeyAndComputed.ElementAt(i); adapter.AppendColumnName(sbColumnList, property.Name); //fix for issue #336 if (i < allPropertiesExceptKeyAndComputed.Count - 1) { sbColumnList.Append(", "); } } var sbParameterList = new StringBuilder(null); for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++) { var property = allPropertiesExceptKeyAndComputed.ElementAt(i); sbParameterList.AppendFormat("@{0}", property.Name); if (i < allPropertiesExceptKeyAndComputed.Count - 1) { sbParameterList.Append(", "); } } int returnVal; var wasClosed = connection.State == ConnectionState.Closed; if (wasClosed) { connection.Open(); } if (!isList) //single entity { returnVal = adapter.Insert(connection, transaction, commandTimeout, name, sbColumnList.ToString(), sbParameterList.ToString(), keyProperties, entityToInsert); } else { //insert list of entities var cmd = $"insert into {name} ({sbColumnList}) values ({sbParameterList})"; returnVal = connection.Execute(cmd, entityToInsert, transaction, commandTimeout); } if (wasClosed) { connection.Close(); } return(returnVal); }
} // End Sub public static void GetAndInsertBuildingPolygon() { string sql = @" INSERT INTO T_ZO_Objekt_Wgs84Polygon ( ZO_OBJ_WGS84_UID ,ZO_OBJ_WGS84_GB_UID ,ZO_OBJ_WGS84_SO_UID ,ZO_OBJ_WGS84_Sort ,ZO_OBJ_WGS84_GM_Lat ,ZO_OBJ_WGS84_GM_Lng ) SELECT NEWID() ZO_OBJ_WGS84_UID -- uniqueidentifier ,@gb_uid AS ZO_OBJ_WGS84_GB_UID -- uniqueidentifier ,NULL AS ZO_OBJ_WGS84_SO_UID -- uniqueidentifier ,@i ZO_OBJ_WGS84_Sort -- int ,@lat ZO_OBJ_WGS84_GM_Lat -- decimal(23,20) ,@lng ZO_OBJ_WGS84_GM_Lng -- decimal(23,20) ; "; System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); ConnectionFactory fac = new ConnectionFactory(GetConnectionString()); using (System.Data.Common.DbConnection connection = fac.Connection) { bool isZH = connection.ExecuteScalar <bool>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE (1=1) AND TABLE_TYPE= 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'T_GebaeudeIMMO' "); bool isRe = connection.ExecuteScalar <bool>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE (1=1) AND TABLE_TYPE= 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'T_Premises' "); string queryFile = "GetGbOsmPolygon.sql"; if (isZH) { queryFile = "GetGbOsmPolygon_STZH.sql"; } else if (isRe) { queryFile = "GetGbOsmPolygon_RE.sql"; throw new System.NotImplementedException("Query for RE not implemented."); } System.Collections.Generic.List <BuildingToGeoCode> ls = System.Linq.Enumerable.ToList( connection.Query <BuildingToGeoCode>(queryFile, typeof(OsmPolyonFinder)) ); foreach (BuildingToGeoCode building in ls) { System.Threading.Thread.Sleep(4000); GeoApis.Polygon nearestBuilding = GetNearestBuildingPolygon(building.GB_GM_Lat, building.GB_GM_Lng); if (nearestBuilding == null) { continue; } System.Console.WriteLine(nearestBuilding); System.Console.WriteLine(nearestBuilding.OsmId); // 218003784 GeoApis.LatLng[] msPoints = nearestBuilding.ToClockWiseLatLngPoints(); string createPolygon = CreateSqlPolygon(msPoints); System.Console.WriteLine(sql); //SELECT // geography::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326) // ,geometry::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326) // -- Geometry is BAD for area // ,geography::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326).STArea() AS geogArea // ,geometry::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326).STArea() AS geomArea //"; GeoApis.LatLng[] osmPoints = nearestBuilding.ToCounterClockWiseLatLngPoints(); string sql2 = "DELETE FROM T_ZO_Objekt_Wgs84Polygon WHERE ZO_OBJ_WGS84_GB_UID = @gb_uid; "; connection.Execute(sql2, new { gb_uid = building.GB_UID }); for (int i = 0; i < osmPoints.Length; ++i) { connection.Execute(sql, new { gb_uid = building.GB_UID, i = i, lat = osmPoints[i].lat, lng = osmPoints[i].lng } ); } // Next i } // Next building } // End Using connection } // End Sub GetAndInsertBuildingPolygon
} // 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