Пример #1
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 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);
        }
Пример #2
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);
        }
Пример #3
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);
        }
Пример #4
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);
        }
Пример #5
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);
        }
Пример #6
0
        } // 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
Пример #7
0
        } // 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