9月 28

自从用过Dapper之后,我已深深不能自拔,太方便了,即可以自己控制sql,又有orm的特性,关键是因为使用emit的缘故效率还很高.

所以我在很多的小项目中都使用dapper了.

但是总会有一些更简单的项目,甚至可以不用写sql的情况,所以我就寻寻觅觅,找到了Dapper.SimpleCRUD这么一个扩展.

 

该扩展目前支持下面8个方法

This extension adds the following 8 helpers:

  • Get(id) – gets one record based on the primary key

  • GetList<Type>() – gets list of records all records from a table

  • GetList<Type>(anonymous object for where clause) – gets list of all records matching the where options

  • GetList<Type>(string for conditions) – gets list of all records matching the conditions

  • GetListPaged<Type>(string for conditions) – gets paged list of all records matching the conditions

  • Insert(entity) – Inserts a record and returns the new primary key

  • Update(entity) – Updates a record

  • Delete<Type>(id) – Deletes a record based on primary key

  • Delete(entity) – Deletes a record based on the typed entity

  • DeleteList<Type>(string for conditions) – deletes list of all records matching the conditions

  • RecordCount<Type>(string for conditions) -gets count of all records matching the conditions

通过Attrubte的方式,可以自动对应数据库表和字段

    [Table("Users")]
    public class User
    {
        [Key]
        public int UserId { get; set; }
        [Column("strFirstName"]
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
    }

最后上一段代码

这个是导入我的旧Blog数据到wordpress的时候用到的,其中包含了wordpress数据库表的意义和关系

    class Program
    {

        public static string ConnectionString = "Server=127.0.0.1;Database=db_blog;Uid=root;Pwd=root;CharSet=utf8;Allow Zero Datetime=True";

        static void Main(string[] args)
        {
            SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);


            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {


                Dictionary<long, CatagoryInfo> dicOld = new Dictionary<long, CatagoryInfo>();
                {
                    var list = conn.GetList<CatagoryInfo>();
                    foreach (var item in list)
                    {
                        TermInfo info = new TermInfo();
                        info.name = item.CategoryName;
                        info.slug = item.CategoryKey;
                        info.term_group = 0;
                        var id = conn.Insert<long>(info);
                        dicOld.Add(id, item);
                    }
                }
                //准备数据,分类
                Dictionary<string, TermInfo> dicCategory = new Dictionary<string, TermInfo>();


                {
                    var list = conn.GetList<TermInfo>();
                    foreach (var item in list)
                    {
                        dicCategory.Add(item.slug, item);
                    }
                }

                Dictionary<long, long> dicPosts = new Dictionary<long, long>();//前面是postid,后面是分类id
                //插入post
                {
                    var list = conn.GetList<ArticleInfo>();
                    foreach (var item in list)
                    {
                        PostInfo post = new PostInfo();
                        post.LL = item.Id;
                        post.post_author = 1;
                        post.post_date = item.AddDate;
                        post.post_date_gmt = item.AddDate;
                        post.post_modified = item.AddDate;
                        post.post_modified_gmt = item.AddDate;
                        post.post_content = item.Content;
                        post.post_title = item.Title;
                        post.post_excerpt = string.Empty;
                        post.post_password = string.Empty;
                        post.post_name = string.Empty;
                        post.to_ping = string.Empty;
                        post.pinged = string.Empty;
                        post.post_content_filtered = string.Empty;
                        post.post_mime_type = string.Empty;
                        post.post_status = "publish";
                        post.comment_status = "open";
                        post.ping_status = "closed";
                        post.post_type = "post";
                        post.menu_order = 0;
                        post.comment_count = 0;
                        var id = conn.Insert<long>(post);
                        dicPosts.Add(id, dicCategory[item.Category].term_id);
                    }
                }

                Dictionary<long, int> postCategoryCount = new Dictionary<long, int>();//文章对应的分类下的数量
                {

                    foreach (var item in dicPosts)
                    {
                        if (postCategoryCount.ContainsKey(item.Value))
                            postCategoryCount[item.Value]++;
                        else
                            postCategoryCount.Add(item.Value, 1);
                    }
                }

                {
                    int i = 0;
                    foreach (var item in dicCategory)
                    {
                        i++;
                        TermTaxonomyInfo tt = new TermTaxonomyInfo();
                        tt.count = postCategoryCount[item.Value.term_id];
                        tt.description = dicOld[item.Value.term_id].CategoryName;
                        tt.parent = 0;
                        tt.taxonomy = "category";
                        tt.term_id = dicCategory[item.Key].term_id;
                        var id = conn.Insert<long>(tt);
                    }

                    foreach (var item in dicPosts)
                    {
                        TeamRelationships tr = new TeamRelationships();
                        tr.object_id = item.Key;
                        tr.term_taxonomy_id = item.Value;
                        tr.term_order = 0;

                        conn.Insert<long>(tr);
                    }
                } 
            }
        }

         
    }

苦逼的entity代码,这两张是我的旧数据

    [Table("tb_article")]
    public class ArticleInfo
    { 
        [Key]
        public int Id { get; set; } 
        public string Title { get; set; } 
        public string Content { get; set; } 
        public string Category { get; set; }  
        public string Summary { get; set; } 
        public DateTime AddDate { get; set; }
        public int ArchiveKey { get; set; }  
    }
    [Table("tb_category")]
    public class CatagoryInfo
    { 
        public int Order { get; set; }
        [Key]
        public string CategoryKey { get; set; }
        public string CategoryName { get; set; } 
    }

以下四张是wordpress最重要的4张表

    [Table("wp_posts")]
    public class PostInfo
    { 
        [Key]
        public long ID { get; set; }
        public long LL { get; set; } 
        public long post_author { get; set; } 
        public DateTime post_date { get; set; }
        public DateTime post_date_gmt { get; set; }
        public string post_content { get; set; } 
        public string post_title { get; set; }
        public string post_excerpt { get; set; }
        public string post_status { get; set; }
        public string comment_status { get; set; }
        public string ping_status { get; set; }
        public string post_password { get; set; }
        public string post_name { get; set; } 
        public string to_ping { get; set; }
        public string pinged { get; set; } 
        public DateTime post_modified { get; set; }
        public DateTime post_modified_gmt { get; set; } 
        public string post_content_filtered { get; set; } 
        public long post_parent { get; set; }
        public int menu_order { get; set; }
        public string post_type { get; set; }
        public string post_mime_type { get; set; } 
        public long comment_count { get; set; } 
    }
    [Table("wp_term_relationships")]
    public class TeamRelationships
    { 
        [Key]
        public long LL { get; set; }
        public long object_id { get; set; } 
        public long term_taxonomy_id { get; set; }
        public int term_order { get; set; } 
    }
    [Table("wp_terms")]
    public class TermInfo
    {
        [Key]
        public long term_id { get; set; }
        public string name { get; set; }
        public string slug { get; set; }
        public long term_group { get; set; }
    }
    [Table("wp_term_taxonomy")]
    public class TermTaxonomyInfo
    {
        [Key]
        public long term_taxonomy_id { get; set; }
        public long term_id { get; set; }
        public long parent { get; set; }
        public long count { get; set; }
        public string description { get; set; }
        public string taxonomy { get; set; }
    }

wordpress表的关系

wp_terms   分类或者标签表

name是名字,slug则是友好的url里面的标识 

wp_term_taxonomy   记录的wp_terms表里标签或分类的属性

tearn_id  对应wp_terms里的id

taxonomy是分类,有两个值 category表示分类,post_tag表示标签

count则是固化的数值   

wp_term_relationships 

object_id  表示 wp_posts里的id  

tearm_taxonomy_id则表示的wp_term_taxonomy的id

written by ocean \\ tags: ,

9月 09

新建了一个网站 优惠汇总网 

用的asp.net mvc+mysql的方式,第一次用asp.net mvc写代码,难免有很多走弯路的地方

开发完了调试了一下,已经可以跑在mono下了.

 

 

现在想监控一下性能,最开始是直接放在 GLobal.asax.cs里面的

        protected void Application_BeginRequest(object sender, EventArgs e)
        { 
            HttpContext.Current.Items["ExecuteTime"] = DateTime.Now; 
        }

        protected void Application_EndRequest(object sender, EventArgs e)
        {
            if (HttpContext.Current.Items["ExecuteTime"] != null)
            {
                //todo
            }
        }

大概是这样写的

在Application_BeginRequest记录开始时间,在Application_EndRequest记录结束时间,两个一减几个日志,完事.

 

这样优点很明显,代码简单呀有木有,缺点也很明显,这样胡子眉毛一把抓,完全搞不清耗时在哪些地方.于是决定改进一下.

先看了一下miniprofiler的介绍,可惜这个只适合开发环境,我是要在生成环境部署,于是果断pass

再然后发现了nanoprofiler,发现挺好,于是添加进网站测试了一下,果然很OK,部署到mono环境,一直报404找不到.看来是不兼容呀,而且这个好像不是很流行,于是再换

这次是用的Glimpse,发现也很强大,所有东西都能查到,稍微控制下权限就能用的.但是太强大了,我又不愿意这么重,最后又走到自己开发的路子上.

 

因为我用的是Dapper,所以就做了一下修改

        public static int Execute(
#if CSHARP30
this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType
#else
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
#endif
)
        {   //add by ocean
            using (new TimeMonitorForDAL(sql, param))
            {
                var command = new CommandDefinition(sql, (object)param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
                return ExecuteImpl(cnn, ref command);
            }
        }
        public static T ExecuteScalar<T>(
#if CSHARP30
this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType
#else
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
#endif
)
        {
             //add by ocean
            using (new TimeMonitorForDAL(sql, param))
            {
                var command = new CommandDefinition(sql, (object)param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
                return ExecuteScalarImpl<T>(cnn, ref command);
            }
        } 
        public static IEnumerable<T> Query<T>(
#if CSHARP30
this IDbConnection cnn, string sql, object param, IDbTransaction transaction, bool buffered, int? commandTimeout, CommandType? commandType
#else
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null
#endif
)
        {
               //add by ocean
            using (new TimeMonitorForDAL(sql, param))
            {
                var command = new CommandDefinition(sql, (object)param, transaction, commandTimeout, commandType, buffered ? CommandFlags.Buffered : CommandFlags.None);
                var data = QueryImpl<T>(cnn, command, typeof(T));
                return command.Buffered ? data.ToList() : data;
            }
        }

代码太多就不贴了,其实就是在执行的时候添加TimeMonitorForDAL类调用就可以了

TimeMonitorForDAL.cs

    public class TimeMonitorForDAL:IDisposable
    {

         

        DateTime start = DateTime.Now;
         
        private string Sql = string.Empty;
        private string param = string.Empty;


        public TimeMonitorForDAL(string sql, object param)
        { 
            this.Sql = sql;
            if (param != null)
            {
                this.param = JSONHelper.Serialize(param);
            }
        }

        public void Dispose()
        {
            int total = (int)DateTime.Now.Subtract(start).TotalMilliseconds;



            try
            {
                using (MySqlConnection conn = new MySqlConnection(Config.ConnectionString))
                {
                    MySqlCommand cmd = new MySqlCommand(SQL, conn); 
                    cmd.Parameters.Add("SQL", this.Sql);
                    cmd.Parameters.Add("Params", this.param);
                    cmd.Parameters.Add("ElapsedTime", total);
                    cmd.Parameters.Add("TransactionId", "");
                    cmd.Parameters.Add("AddDate", DateTime.Now);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
            }
        } 
        private static string SQL = "insert into sys_timemonitorfordal(`SQL`,`Params`,`ElapsedTime`,`TransactionId`,`AddDate`)values(?SQL,?Params,?ElapsedTime,?TransactionId,?AddDate)";
         
    }

实现了IDisposable的接口,可以方便使用using的语法糖.

 

其实整个代码还是很简单的,这样就可以轻松实现监控sql执行耗时的功能了,而且,其它任何代码都不需要改变

当然,这个方式也是有缺点的,可能会对影响系统性能,废话,每次执行sql要多插入一次log,这个可以之后改成异步的或者一次性写入优化,不过大概的原型就这样了.

最后,贴一下sql的脚本方便备份

-- ----------------------------
-- Table structure for `sys_timemonitorfordal`
-- ----------------------------
DROP TABLE IF EXISTS `sys_timemonitorfordal`;
CREATE TABLE `sys_timemonitorfordal` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SQL` varchar(4000) DEFAULT NULL,
  `Params` varchar(2000) DEFAULT NULL,
  `ElapsedTime` int(11) DEFAULT NULL,
  `TransactionId` varchar(100) DEFAULT NULL,
  `AddDate` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=1096 DEFAULT CHARSET=utf8;

 

另外,之所以要把log存在数据库中而不是日志中,主要是统计方便,例如下面这样

SELECT `sql`,count(*),max(ElapsedTime),Min(ElapsedTime),avg(ElapsedTime) from sys_timemonitorfordal GROUP BY `sql`

这样就可以统计出来一段时间以来执行sql的次数,最大耗时,平均耗时,做到有目的的优化sql

written by ocean \\ tags:

7月 14

假如你喜欢原生的Sql语句,又喜欢ORM的简单,那你一定会喜欢上Dapper这款ROM. 点击下载 Dapper的优势:

1,Dapper是一个轻型的ORM类。代码就一个SqlMapper.cs文件,编译后就40K的一个很小的Dll.

2,Dapper很快。Dapper的速度接近与IDataReader,取列表的数据超过了DataTable。

3,Dapper支持什么数据库。Dapper支持Mysql,SqlLite,Mssql2000,Mssql2005,Oracle等一系列的数据库,当然如果你知道原理也可以让它支持Mongo db

4,Dapper的r支持多表并联的对象。支持一对多 多对多的关系。并且没侵入性,想用就用,不想用就不用。无XML无属性。代码以前怎么写现在还怎么写。 5,Dapper原理通过Emit反射IDataReader的序列队列,来快速的得到和产生对象。性能实在高高高。

 

下载地址 https://github.com/StackExchange/dapper-dot-net 

测试的数据结构为

    public class ReportInfo{
        public string UserName { get; set; }
        public string DataString { get; set; }
        public int ProjectCode { get; set; }
        public int Hour { get; set; }
    }

 

Dapper以扩展IDbConnection类的方式 提供三个扩展方法重载

 

第一种

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

调用示例

            using (IDbConnection connection = new SqlConnection(ConnectionString)) {
                var list = connection.Query<ReportInfo>("select * from Report where UserName=@UserName ",
                    new { UserName = "Ocean" });
            }

 

第二种

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

调用示例

            using (IDbConnection connection = new SqlConnection(ConnectionString)) {
                var list = connection.Query("select * from Report where UserName=@UserName ",
                    new { UserName = "Ocean" });

                foreach (var item in list)
                {
                    Console.WriteLine(item.UserName);
                } 
            }

 

第三种

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

调用示例

            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                var result = connection.Execute(@"insert Report(UserName, DataString,ProjectCode,Hour) values (@UserName, @DataString,@ProjectCode,@Hour)",
                      new { UserName = "OceanTest", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 });
            }

entity的话可以更简单

        public static void Insert(BookInfo bookInfo)
        {
            using (IDbConnection connection = new MySqlConnection(Config.ConnectionString))
            {
                var result = connection.Execute(@"insert tb_book(Title,Content,Volume) values (?Title,?Content,?Volume)",
                                                bookInfo);
            }
        }

另外还可以批量添加

            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                var result = connection.Execute(@"insert Report(UserName, DataString,ProjectCode,Hour) values (@UserName, @DataString,@ProjectCode,@Hour)",
                      new []{
                      new { UserName = "OceanTest2", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 },
                      new { UserName = "OceanTest3", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 },
                      new { UserName = "OceanTest4", DataString = "blog.wx6.org", ProjectCode = 1, Hour = 100 }
                      });
            }

 

多结果集,需要注意结果集大于1条数据则Single()会报错

            using (IDbConnection connection = new SqlConnection(ConnectionString))
            { 
                var sql = @"
                            select * from Report where UserName=@UserName;
                            select * from Report where ProjectCode=@ProjectCode;
                            select * from [User] where UserName=@UserName ;
                            select * from Report where Hour>@Hour";

                using (var multi = connection.QueryMultiple(sql,
                            new { UserName = "Ocean", ProjectCode = 3, Hour=100 }))
                {
                    var recordSet1 = multi.Read<ReportInfo>().ToList();
                    var recordSet2 = multi.Read<ReportInfo>().Single();
                    var recordSet3 = multi.Read<UserInfo>().ToList();
                    var recordSet4 = multi.Read<ReportInfo>().ToList();
                } 
            }

 

支持事务(未验证)

            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                const string deleteColumn = "delete from [Column] where ColumnCatid=@catid";
                const string deleteColumnCat = "delete from ColumnCat where id=@Id";

                IDbTransaction transaction = connection.BeginTransaction();
                int row = connection.Execute(deleteColumn, new { catid = 1}, transaction, null, null);
                row += connection.Execute(deleteColumnCat, new { id = 1 }, transaction, null, null);
                transaction.Commit(); 
            }

 

另外一种批量插入数据的方法(未测试)

    //批量插入数据
    List<School> schools = new List<School>()
    {      
            new School() {Address="China·BeiJing",Title="清华大学" },      
            new School() {Address="杭州",Title="浙江大学" },      
            new School() {Address="不知道,US?",Title="哈弗大学" }
    };    
    //在执行参数化的SQL时,SQL中的参数(如@title可以和数据表中的字段不一致,但要和实体类型的属性Title相对应)
    dbConnection.Execute("insert into t_schools(Address,Name) values(@address,@title);", schools);

     

 总结

Dapper通过Emit的方式组装语句,因此执行效率接近原生语句.

并且很轻,无配置,无属性.SQL语句完全可控,简直就是我最喜欢的东西啊.

相见恨晚,以后在项目中需要多用此ROM类库

written by ocean \\ tags: