コード例 #1
0
ファイル: SqlMapperExtensions.cs プロジェクト: zszqwe/CommonX
        /// <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);
        }
コード例 #2
0
        } // 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
コード例 #3
0
        /// <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);
        }
コード例 #4
0
        /// <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);
        }
コード例 #5
0
ファイル: CRUD.cs プロジェクト: bixiu/BlueMine
        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);
        }
コード例 #6
0
ファイル: DAL.cs プロジェクト: triozillion/MyBlog
        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
コード例 #7
0
ファイル: SqlMapperExtensions.cs プロジェクト: ichoukou/BM
        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));
        }
コード例 #8
0
        } // 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
コード例 #9
0
ファイル: SqlMapperExtensions.cs プロジェクト: ichoukou/BM
        /// <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);
        }
コード例 #10
0
ファイル: SqlMapperExtensions.cs プロジェクト: ichoukou/BM
        /// <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);
        }
コード例 #11
0
        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);
        }
コード例 #12
0
 public IEnumerable <Result> getAllResults()
 {
     //Get all results
     return(conn.Query <Result>("Select * from speedtest"));
 }
コード例 #13
0
ファイル: OrmBenchmark.cs プロジェクト: MingLu8/Nemo
 public void RunDapper()
 {
     var result = _connection.Query <Customer>(sql, null, buffered: false).ToList();
 }
コード例 #14
0
        // 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);
            }
        }
コード例 #15
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
コード例 #16
0
ファイル: TestDapper.cs プロジェクト: ststeiger/SchemaPorter
        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);
            }
        }