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