Intro

Recently, there is an idea that if you want to do something similar to ORM without writing sql statements, you can parse the expression tree, generate the sql statements to be executed, and finally execute the sql statements to return the corresponding results.

Train of thought analysis

The common sql statements basically have a certain mode, that is, INSERT/DELETE/Update/SELECT. I take the common part out, point out the query / update / delete conditions and specified fields through the expression tree, and parse the incoming expression tree object to get the conditions and fields to be updated.

Source code

Source link

• Repository

• Expression tree parsing

Sample

Please see usage. Example:

Install nuget package WihanLi.Common

public static class RepositoryTest
    {
        public static void MainTest()
        {
            var connectionPool = new DbConnectionPool(new DbConnectionPoolPolicy(ConfigurationHelper.ConnectionString("TestDb")));

            var repo = new Repository<TestEntity>(() => connectionPool.Get());
            repo.Insert(new TestEntity
            {
                Token = "1233",
                CreatedTime = DateTime.UtcNow
            });

            var entity = repo.Fetch(t => t.PKID == 1);
            System.Console.WriteLine(entity.Token);

            repo.Update(t => t.PKID == 1, t => t.Token, 1);

            entity = repo.Fetch(t => t.PKID == 1);
            System.Console.WriteLine(entity.Token);

            repo.Delete(t => t.PKID == 1);
            entity = repo.Fetch(t => t.PKID == 1);
            System.Console.WriteLine($"delete operation {(entity == null ? "Success" : "Failed")}");

            repo.Execute("TRUNCATE TABLE dbo.tabTestEntity");

            Console.WriteLine("finished.");
        }

        public class DbConnectionPool : DefaultObjectPool<DbConnection>
        {
            public DbConnectionPool(IPooledObjectPolicy<DbConnection> policy) : base(policy)
            {
            }

            public DbConnectionPool(IPooledObjectPolicy<DbConnection> policy, int maximumRetained) : base(policy, maximumRetained)
            {
            }
        }

        public class DbConnectionPoolPolicy : IPooledObjectPolicy<DbConnection>
        {
            private readonly string _connString;

            public DbConnectionPoolPolicy(string connString)
            {
                _connString = connString;
            }

            public DbConnection Create()
            {
                return new SqlConnection(_connString);
            }

            public bool Return(DbConnection obj)
            {
                return obj.ConnectionString.IsNotNullOrWhiteSpace();
            }
        }
        [Table("tabTestEntity")]
        internal class TestEntity
        {
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int PKID { get; set; }

            public string Token { get; set; }

            public DateTime CreatedTime { get; set; }
        }
    }

In the example, ObjectPool is used to implement a database connection pool. The instantiation of Repository requires a delegation to obtain DbConnection objects, and database connection is obtained from this database connection pool.

Less than /TODO

• Support custom Column name through Column
• It supports index query. Now, the condition is directly spliced into a string, anti injection processing is done, and parameterized query is used instead
• Support more methods to parse into corresponding Sql statements