/// <summary>
/// Returns a list of entites from table "Ts".
/// Id of T must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</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>Entity of T</returns>
public static IEnumerable <T> GetAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var cacheType = typeof(List <T>);
string sql;
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out sql))
{
GetSingleKey <T>(nameof(GetAll));
var name = GetTableName(type);
#region select field only need
var properties = "*";
if (TypePropertiesCache(type).Any())
{
var preSql = $"select * from {name} where rownum=1";
var temp = connection.Query(preSql, null, transaction, commandTimeout: commandTimeout);
if (temp.Any())
{
var t = temp.FirstOrDefault();
var props = ((IDictionary <string, object>)t).Keys.Select(c => c.ToLower());
properties = string.Join(",", TypePropertiesCache(type).Select(c => c.Name.ToLower()).Intersect(props));
}
if (string.IsNullOrEmpty(properties))
{
properties = "*";
}
}
#endregion
sql = $"select {properties} from " + name;
GetQueries[cacheType.TypeHandle] = sql;
}
if (!type.IsInterface)
{
return(connection.Query <T>(sql, null, transaction, commandTimeout: commandTimeout));
}
var result = connection.Query(sql);
var list = new List <T>();
foreach (IDictionary <string, object> res in result)
{
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
list.Add(obj);
}
return(list);
}
} // 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
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</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>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var key = GetSingleKey <T>(nameof(Get));
var name = GetTableName(type);
sql = $"select * from {name} where {key.Name} = @id";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@id", id);
T obj;
if (type.IsInterface())
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a list of entites from table "Ts".
/// Id of T must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</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>Entity of T</returns>
public static IEnumerable <T> GetAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var cacheType = typeof(List <T>);
string sql;
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out sql))
{
GetSingleKey <T>(nameof(GetAll));
var name = GetTableName(type);
sql = "select * from " + name;
GetQueries[cacheType.TypeHandle] = sql;
}
if (!type.IsInterface())
{
return(connection.Query <T>(sql, null, transaction, commandTimeout: commandTimeout));
}
var result = connection.Query(sql);
var list = new List <T>();
foreach (IDictionary <string, object> res in result)
{
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
list.Add(obj);
}
return(list);
}
public System.Collections.Generic.List <Db.T_projects> GetProjects()
{
System.Collections.Generic.List <Db.T_projects> projects = null;
string sql = "SELECT * FROM projects ";
// string sql = "SELECT * FROM projects WHERE id = @projid";
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
projects = connection.Query <Db.T_projects>(sql).ToList();
}
return(projects);
}
public static async System.Threading.Tasks.Task Test()
{
SqlFactory fac = new SqlClientFactory();
// DbConnection.ProviderFactory => DbProviderFactory;
using (System.Data.Common.DbConnection con = fac.Connection)
{
string sql = "SELECT * FROM T_BlogPost";
string sql_paged = sql += fac.PagingTemplate(3, 2);
string sql_limited = sql += fac.PagingTemplate(1);
IEnumerable <T_BlogPost> a = con.Query <T_BlogPost>(sql);
IEnumerable <T_BlogPost> aa = await con.QueryAsync <T_BlogPost>(sql_paged);
T_BlogPost b = con.QuerySingle <T_BlogPost>(sql_limited);
T_BlogPost ba = await con.QuerySingleAsync <T_BlogPost>(sql_limited);
} // End Using con
} // End Sub Test
public static IEnumerable <T> Find <T>(this IDbConnection conn, Expression <Func <T, bool> > predicate)
{
var pairs = ExpressionHelper.GetPredicatePairs(predicate);
// とりあえずテーブル名はクラス名で
var className = typeof(T).Name;
var condition = pairs.ToSqlString("@"); // とりま@に決めうってるけどDBによっては違いますなー
var query = string.Format("SELECT * FROM {0} WHERE {1}", className, condition);
// 匿名型でなく動的にパラメータ作る時はDynamicParameterを使う
var parameter = new DynamicParameters();
foreach (var pair in pairs)
{
parameter.Add(pair.MemberName, pair.Value);
}
// Dapperで実行. 勿論、FirstではないFindAllも別途用意するとヨシ。
return(conn.Query <T>(sql: query, param: parameter, buffered: false));
}
} // End Action Upload
//
// GET: /Blog/
public ActionResult Index()
{
BlogIndex bi = new BlogIndex();
// bi.lsBlogEntries = this.m_dal.GetList<T_BlogPost>(@"");
string sql = @"
SELECT
T_BlogPost.*
,ROW_NUMBER() OVER (ORDER BY BP_EntryDate DESC) AS rownum
FROM T_BlogPost
ORDER BY BP_EntryDate DESC
" + this.m_fac.PagingTemplate(100);
using (System.Data.Common.DbConnection con = this.m_fac.Connection)
{
bi.lsBlogEntries = con.Query <T_BlogPost>(sql).ToList();
}
// UpdateBlogStructure(bi.lsBlogEntries);
return(View(bi));
} // End Action Index
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <typeparam name="TType"></typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="ids">Id of the entity to get, must be marked with [Key] attribute</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>Entity of T</returns>
public static IList <T> GetBy <T, TType>(this IDbConnection connection, TType[] ids, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var key = GetSingleKey <T>(nameof(Get));
var name = GetTableName(type);
sql = $"select * from {name} where {key.Name} IN @ids";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@ids", ids);
var obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).ToList();
return(obj);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</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>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var key = GetSingleKey <T>(nameof(Get));
var name = GetTableName(type);
sql = $"SELECT * FROM {name} WHERE {key.Name} = @id";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@id", id);
var obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
return(obj);
}
public static void GenerateContext(string ns)
{
string table_prefix = "T_";
string cs = @"
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace " + ns + @"
{
public partial class RedmineContext : DbContext
{
";
List <TableDefinitions> allTables = null;
// LOOP Tables
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
System.Console.WriteLine(connection.ConnectionString);
allTables = connection.Query <TableDefinitions>("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME ")
.ToList();
}
for (int i = 0; i < allTables.Count; ++i)
{
cs += @" public virtual DbSet<" + table_prefix + allTables[i].TABLE_NAME + @"> " + allTables[i].TABLE_NAME + @" { get; set; }
";
}
// MSG from entity framwork on error:
// Unable to generate entity type for table 'dbo.groups_users'. Please see the warning messages.
cs += @"
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(
$@""Server ={ System.Environment.MachineName}\SQLEXPRESS; Database = Redmine; Integrated Security = true; "");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
";
string sql = SchemaGenerator.GetScript("columns.sql");
System.Collections.Generic.List <ColumnDefinition> allColumns = null;
System.Collections.Generic.List <IndexDefinition> allIndices = null;
System.Collections.Generic.List <PrimaryKeyDefinition> allPrimaryKeys = null;
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allColumns = connection.Query <ColumnDefinition>(sql).ToList();
}
sql = SchemaGenerator.GetScript("indices.sql");
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allIndices = connection.Query <IndexDefinition>(sql).ToList();
}
sql = SchemaGenerator.GetScript("primary_keys.sql");
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allPrimaryKeys = connection.Query <PrimaryKeyDefinition>(sql).ToList();
}
for (int i = 0; i < allTables.Count; ++i)
{
List <PrimaryKeyDefinition> primarykeyInfo = (
from x in allPrimaryKeys
where x.TABLE_NAME == allTables[i].TABLE_NAME
orderby x.PRIMARY_KEY_NAME ascending
select x
).ToList();
List <ColumnDefinition> columnInfo = (
from x in allColumns
where x.TABLE_NAME == allTables[i].TABLE_NAME
orderby x.ORDINAL_POSITION ascending
select x
).ToList();
List <IndexDefinition> indexInfo = (
from x in allIndices
where x.TABLE_NAME == allTables[i].TABLE_NAME
orderby x.INDEX_ORDINAL ascending
select x
).ToList();
cs += @" modelBuilder.Entity<" + table_prefix + allTables[i].TABLE_NAME + @">(entity =>
{
";
for (int l = 0; l < primarykeyInfo.Count; ++l)
{
string indcoldef = primarykeyInfo[l].PRIMARY_KEY;
if (primarykeyInfo[l].IS_MULTICOLUMN_KEY)
{
indcoldef = "new { " + primarykeyInfo[l].PRIMARY_KEY + " } ";
}
cs += @"
entity.HasKey(e => " + indcoldef + @")";
if (!string.IsNullOrEmpty(primarykeyInfo[l].PRIMARY_KEY_NAME))
{
cs += @"
.HasName(""" + primarykeyInfo[l].PRIMARY_KEY_NAME + @""")";
}
cs += @";
";
}
for (int k = 0; k < indexInfo.Count; ++k)
{
string indcoldef = indexInfo[k].KEY_COLUMNS;
if (indexInfo[k].IS_MULTICOLUMN_INDEX)
{
indcoldef = "new { " + indexInfo[k].KEY_COLUMNS + " } ";
}
cs += @"
entity.HasIndex(e => " + indcoldef + @")";
if (!string.IsNullOrEmpty(indexInfo[k].INDEX_NAME))
{
cs += @"
.HasName(""" + indexInfo[k].INDEX_NAME + @""")";
}
cs += @";
";
}
cs += @";
";
for (int j = 0; j < columnInfo.Count; ++j)
{
// Indices
//entity.HasIndex(e => e.NewStatusId)
//.HasName("index_workflows_on_new_status_id");
//entity.HasIndex(e => new { e.RootId, e.Lft, e.Rgt })
// .HasName("index_issues_on_root_id_and_lft_and_rgt");
// .IsUnique();
// Columns
cs += @"
entity.Property(e => e.";
if (!Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsValidIdentifier(columnInfo[j].COLUMN_NAME))
{
System.Console.WriteLine($@" ""{allTables[i].TABLE_NAME}"".""{columnInfo[j].COLUMN_NAME}"" is not a valid identifier.");
}
if (Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsReservedKeyword(
Microsoft.CodeAnalysis.CSharp.SyntaxFacts.GetKeywordKind(columnInfo[j].COLUMN_NAME)
))
{
cs += "@";
}
cs += columnInfo[j].COLUMN_NAME + @")";
if (columnInfo[j].IS_REQUIRED)
{
cs += @"
.IsRequired()";
}
cs += @"
.HasColumnName(""" + columnInfo[j].COLUMN_NAME + @""")";
if (columnInfo[j].COLUMN_TYPE != null)
{
cs += @"
.HasColumnType(""" + columnInfo[j].COLUMN_TYPE + @""")";
}
if (columnInfo[j].MAX_LENGTH.HasValue && columnInfo[j].MAX_LENGTH != -1)
{
cs += @"
.HasMaxLength(" + columnInfo[j].MAX_LENGTH.ToString() + @")";
}
if (!string.IsNullOrEmpty(columnInfo[j].COLUMN_DEFAULT))
{
cs += @"
.HasDefaultValueSql(""" + columnInfo[j].COLUMN_DEFAULT + @""")";
}
cs += @";
";
//columnInfo[j].COLUMN_NAME
//columnInfo[j].COLUMN_DEFAULT
// columnInfo[j].DOTNET_TYPE
//columnInfo[j].ORDINAL_POSITION
// columnInfo[j].SQL_TYPE
} // Next j
cs += @"
});
";
} // Next i
cs += @"
} // End Sub OnModelCreating
} // End partial class RedmineContext : DbContext
} // End namespace " + ns + @"
";
System.Console.WriteLine(cs);
System.IO.File.WriteAllText("SomeContext.cs", cs, System.Text.Encoding.UTF8);
}
public IEnumerable <Result> getAllResults()
{
//Get all results
return(conn.Query <Result>("Select * from speedtest"));
}
public void RunDapper()
{
var result = _connection.Query <Customer>(sql, null, buffered: false).ToList();
}
// https://www.developerfusion.com/article/143896/understanding-the-basics-of-roslyn/
public static void GenerateSchema()
{
string sql = GetScript("columns.sql");
System.Collections.Generic.List <ColumnDefinition> allColumns = null;
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allColumns = connection.Query <ColumnDefinition>(sql).ToList();
}
//var ls = allColumns.GroupBy(p => new { p.TABLE_NAME })
// .Select(g => g.First().TABLE_NAME)
//.ToList();
System.Collections.Generic.List <string> ls = null;
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
ls = connection.Query <string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ").ToList();
}
foreach (string table in ls)
{
var tableColumns = allColumns
.Where(p => p.TABLE_NAME.Equals(table, System.StringComparison.OrdinalIgnoreCase))
.ToList();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"
namespace BlueMine.Db
{
public partial class ");
sb.Append("T_"); // filed-name may not be equal to class name, rename class
sb.Append(table);
sb.Append(@"
{
");
foreach (ColumnDefinition cl in tableColumns)
{
sb.Append(@" public ");
sb.Append(cl.DOTNET_TYPE);
sb.Append(" ");
// for keywords
//if (!Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsValidIdentifier(cl.COLUMN_NAME))
if (Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsReservedKeyword(
Microsoft.CodeAnalysis.CSharp.SyntaxFacts.GetKeywordKind(cl.COLUMN_NAME)
))
{
sb.Append("@");
}
sb.Append(cl.COLUMN_NAME);
bool useProperties = true;
if (useProperties)
{
sb.Append(" { get; set; } // ");
}
else
{
sb.Append("; // ");
}
sb.AppendLine(cl.SQL_TYPE);
} // Next cl
sb.AppendLine(@" }");
sb.AppendLine(System.Environment.NewLine);
sb.AppendLine(@"}");
string fileContents = sb.ToString();
string dir = System.IO.Path.Combine("Db", "Redmine", "BlueMappings", table + ".cs");
string d = System.IO.Path.GetDirectoryName(dir);
if (!System.IO.Directory.Exists(d))
{
System.IO.Directory.CreateDirectory(d);
}
System.IO.File.WriteAllText(dir, fileContents, System.Text.Encoding.UTF8);
}
}
} // 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
public static void Test()
{
string table_schema = "dbo";
// string table_name = "T_VWS_Ref_Darstellung";
string table_name = "T_VWS_Ref_PdfLegendenKategorie";
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
string select_query = connection.QueryFirstEmbedded <string>("SELECT_Columns.sql", typeof(TestDapper), new
{
in_schema = table_schema,
in_table = table_name,
});
TableDeclaration td = connection.QueryFirstEmbedded <TableDeclaration>("Upsert_Table.sql", typeof(TestDapper), new
{
in_schema = table_schema,
in_table = table_name,
});
if (td == null)
{
return;
}
System.Collections.Generic.List <System.Collections.Generic.IDictionary <string, object> > data_to_insert =
connection.Query(select_query).Select(x => (System.Collections.Generic.IDictionary <string, object>)x).ToList();
System.Console.WriteLine(data_to_insert);
System.Collections.Generic.IEnumerable <TableColumnDefinition> lsColumns = connection.QueryEmbedded <TableColumnDefinition>("Upsert_Columns.sql", typeof(TestDapper), new
{
in_schema = table_schema,
in_table = table_name,
});
if (lsColumns == null || !System.Linq.Enumerable.Any(lsColumns))
{
return;
}
string sql = @"
SET NOCOUNT ON;
" + td.table_declaration + @"
INSERT INTO @" + td.variable_name + @"
(
";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " ";
}
else
{
sql += ",";
}
sql += cd.QUOTED_COLUMN_NAME;
sql += System.Environment.NewLine;
}
sql += @")
";
for (int i = 0; i < data_to_insert.Count; ++i)
{
if (i != 0)
{
sql += "UNION ALL ";
}
sql += "SELECT ";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " ";
}
else
{
sql += ",";
}
object value = data_to_insert[i][cd.COLUMN_NAME];
sql += ObjectToInsertStringValue(value);
sql += System.Environment.NewLine;
}
}
sql += @";
-- SELECT * FROM @" + td.variable_name + @"
MERGE INTO " + table_name + @" AS A
USING @" + td.variable_name + @" AS CTE ON CTE." + lsColumns.AsList()[0].QUOTED_COLUMN_NAME + " = A." + lsColumns.AsList()[0].QUOTED_COLUMN_NAME + @"
WHEN MATCHED
THEN UPDATE
";
foreach (TableColumnDefinition cd in lsColumns)
{
if (cd.ORDINAL_POSITION == 1)
{
continue;
}
sql += " ";
if (cd.ORDINAL_POSITION == 2)
{
sql += "SET ";
}
else
{
sql += " ,";
}
sql += "A." + cd.QUOTED_COLUMN_NAME + " = CTE." + cd.QUOTED_COLUMN_NAME + " ";
sql += System.Environment.NewLine;
}
sql += @"
WHEN NOT MATCHED THEN
INSERT
(
";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " ";
}
else
{
sql += ",";
}
sql += cd.QUOTED_COLUMN_NAME;
sql += System.Environment.NewLine;
}
sql += @"
)
VALUES
(
";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " CTE.";
}
else
{
sql += ",CTE.";
}
sql += cd.QUOTED_COLUMN_NAME;
sql += System.Environment.NewLine;
}
sql += @"
);
-- DELETE FROM " + table_name + @" WHERE xxx = '123';
-- SELECT * FROM " + table_name + @";
SET NOCOUNT OFF;
";
System.Console.WriteLine(sql);
}
}