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: ,