ThinkDev.Data组件介绍

1、ThinkDev.Data组件是ThinkDev组件系列之数据组件，目前主要提供MsSqlBudiler以及一个指导性数据层基类实现。
2、目前支持原生SqlBuilder、Linq to SqlBuilder、Linq to ObjectQuery三种工作方式
3、本组件生成的Sql语句会在数据库做编译缓存，性能差距忽略不计
4、考虑目前大部分Sql注入情况，目前尚未发现注入漏洞
5、本组件无任何外部依赖。

组件基本使用方式：

1、Linq to SqlBuilder模式

首先定义实体类定义
   public class UserInfo
   {
       public int UserID { get; set; }
       public string UserName { get; set; }
       public string NickName { get; set; }
       public int Sex { get; set; }
       public string HeadPic { get; set; }
       public string Introduction { get; set; }
       public string Other { get   ; set; }
       public DateTime RegTime { get; set; }
   }
   1、执行Insert操作
   UserInfo user = new UserInfo();
   user.UserID = 1;
   user.UserName = "V_UserName";
   user.NickName = "V_NickName";
   user.Sex = 1;
   user.HeadPic = "V_HeadPic";
   user.Introduction = "V_Introduction";
   user.Other = "V_Other";
   user.RegTime = DateTime.Now();
   public string GetSql()
   {
      string sql = SqlBuilder<UserInfo>.From(user, "dbo.UserInfo")
               .Insert()
               .AddInsert(u => u.UserName, u => u.NickName, u => u.Sex, u => u.HeadPic, u => u.Introduction, u => u.Other, u => u.RegTime)
               .GetSql();
   }
   2、执行Select操作
   Public string GetSql()
   {
      string sql = SqlBuilder<UserInfo>.From("dbo.UserInfo")
               .Select(u => u.UserName, u => u.NickName, u => u.Sex, u => u.HeadPic, u => u.Introduction)
               .Where(u => u.UserID > 10)
               .GetSql();
   }
   3、执行Update操作
   UserInfo user = new UserInfo();
   user.UserID = 1;
   user.UserName = "V_UserName";
   user.NickName = "V_NickName";
   user.Sex = 1;
   user.HeadPic = "V_HeadPic";
   user.Introduction = "V_Introduction";
   user.Other = "V_Other";
   Public string GetSql()
   {
      string sql = SqlBuilder<UserInfo>.From(user, "dbo.UserInfo")
               .Update()
               .Set(u => u.UserName, u => u.NickName, u => u.Sex, u => u.HeadPic, u => u.Introduction)
               .Where(u => u.UserID)
               .And(u=>u.UserName, MatchType.In, "user1", "user2", "user3")
               .GetSql();
   }


   //特别的，如果需要生成语句：UPDATE dbo.UserInfo  SET UserName = UserName + '_old' WHERE UserID = 1

   Public string GetSql()
   {
      string sql = SqlBuilder<UserInfo>.From(user, "dbo.UserInfo")
               .Update()
               .Set(u => u.UserName, u => u.UserName, "+", "_old")
               .Where(u => u.UserID)
               .And(u=>u.UserName, MatchType.In, "user1", "user2", "user3")
               .GetSql();
   } 

 

2、原生SqlBuilder模式

* 单表查询及排序
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
                 .Select()
                 .Where("Para1", "value1", SqlDbType.VarChar, 50)
                 .And<int>("Para2", 5, SqlDbType.Int, 0, MatchType.GreaterThanOrEqual)
                 .Or<DateTime>("Para3", DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")), SqlDbType.DateTime)
                 .OrderBy("ParaID", SortType.Desc)
                 .GetSql();    
   }
   或者
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
                 .Select()
                 .Where("Para1", "value1")
                 .And<int>("Para2", MatchType.GreaterThanOrEqual, 5)
                 .Or<DateTime>("Para3", DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
                 .OrderBy("ParaID", SortType.Desc)
                 .GetSql();    
   }

 

单表查询，及分页
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
                 .Select()
                 .Where("Para1", SqlDbType.VarChar, 50, MatchType.Equal, "value1")
                 .Skip(20)
                 .Take(10)
                 .OrderBy("ParaID", SortType.Desc)
                 .GetSql();       
   }
   或者
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
                 .Select()
                 .Where("Para1", MatchType.Equal, "value1")
                 .Skip(20)
                 .Take(10)
                 .OrderBy("ParaID", SortType.Desc)
                 .GetSql();       
   }

 

多表查询
   public string GetSql()
   {
      string sql = SqlBuilder.From("Table1")
               .Select("t0.*, t1.para3")
               .InnerJoin(new JoinTable("Table2").On("para1", MatchType.Equal, "para3").And("para2", MatchType.Equal, "val2"))
               .Where<int>("para0", MatchType.Equal, 10)
               .And("para3", MatchType.Equal, "val3")
               .GetSql();    
   }

 

 Count查询
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .SelectCount()
               .Where<int>("para0", MatchType.Equal, 10)
               .And("para1", MatchType.Equal, "val1")
               .GetSql();
   }

Max\Min查询
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .SelectMax("ID")
               .Where<int>("para0", MatchType.Equal, 10)
               .And("para1", MatchType.Equal, "val1")
               .GetSql();
   }
   或
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .SelectMin("ID")
               .Where<int>("para0", MatchType.Equal, 10)
               .And("para1", MatchType.Equal, "val1")
               .GetSql();
   }

In\Not In查询
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .SelectCount()
               .Where<int>("para0", MatchType.In, 1, 2, 3, 4, 5, 6)
               .And("para1", MatchType.NotIn, new string[]{"val1", "val2", "val3"})
               .GetSql();
   }

Update操作
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .Update()
               .Set("para0", "para0", SqlDbType.VarChar, 1024)
               .Set<int>("para1", 0, SqlDbType.Int)
               .Set("para2", "update para2", SqlDbType.VarChar, 50)
               .Where<int>("para3", SqlDbType.Int, 0, MatchType.GreaterThan, 1)
               .GetSql();
   }
   或者
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .Update()
               .Set("para0", "para0")
               .Set<int>("para1", 0)
               .Set("para2", "update para2")
               .Where<int>("para3", MatchType.GreaterThan, 1)
               .GetSql();
   }

Delete操作
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .Delete()
               .Where<int>("para0", SqlDbType.Int, 0, MatchType.GreaterThan, 1)
               .GetSql();
   }
   或者
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .Delete()
               .Where<int>("para0", MatchType.GreaterThan, 1)
               .GetSql();
   }

Insert操作
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .Insert()
               .AddInsert<int>("para0", 1, SqlDbType.Int)
               .AddInsert("para1", "UserName from sqlbuilder", SqlDbType.VarChar, 50)
               .AddInsert("para2", "Title from sqlbuilder", SqlDbType.VarChar, 50)
               .AddInsert("para3", "Body from sqlbuilder", SqlDbType.VarChar, 50)
               .AddInsert<int>("para4", 1, SqlDbType.Int)
               .GetSql();
   }
   或者
   public string GetSql()
   {
      string sql = SqlBuilder.From("TableName")
               .Insert()
               .AddInsert<int>("para0", 1)
               .AddInsert("para1", "UserName from sqlbuilder")
               .AddInsert("para2", "Title from sqlbuilder")
               .AddInsert("para3", "Body from sqlbuilder")
               .AddInsert<int>("para4", 1)
               .GetSql();
   }