【开源框架】SqlSugarRepository 全库ORM 正式发布

SqlSugarRepository.dll 全库开发框架支持四种数据库：SqlServer、 MySql 、Oracle和Sqlite。

SqlSugarRepository是为全库开发而生的ORM框架，能够做到多种数据库之间的无间隙切换，在这一年多的时间里，园友提出的各种需求我都已经切切实实的去做了，做到了能力范围内的最好，一个不错的里程碑。同时也对未来有了更明确的规划，这只是开源的开始，Sugar ORM 还会有更多惊喜。

基于SqlSugar的开源CMS（百签软件出品）和SqlSugar官网本月将开源

主流ORM多库支持情况

Dapper 有多库访问组件，做不了同一个查询语句适用多种数据库。（select * from table这种除外）

EF 有多库访问组件，可以做到同一个查询适用多种数据库，但是命名空间引用不同需要自已用仓储实现多库

SqlSugarRepository 有多库访问组件，可以做到同一个查询适用多种数据库，已经实现内部工厂，比仓储模式的应变能力更强。

优点：

语法具有创新并且丰富

接近原生的性能

完整的学习文档

使用方便， Nuget直接搜索SqlSugarRepository下载便可，依赖dll都打包好了

Sugar ORM 所有成员

如果你只想操作一种数据库，可以针对不同数据库选择独立的dll

Asp.net 4.+	Asp.net Core	说明	依赖
SqlSugar.dll	SqlSugarCore.dll	SqlServer ORM	无
MysqlSugar.dll	MysqlSugarCore.dll	MySql ORM	MySql.Data.dll
SqliteSugar.dll	SqliteSugarCore.dll	Sqlite ORM	System.Data.SQLite.dll SQLite.Interop.dll（Core版不需要）
OracleSugar.dll	-	Oracle ORM	Oracle.ManagedDataAccess.dll
SqlSugarRepository.dll	-	SqlServer MySql Sqlite Oracle 四合一	MySql.Data.dll System.Data.SQLite.dll Oracle.ManagedDataAccess.dll SQLite.Interop.dll

数据库连接

普通模式

这种模式简单粗爆，适合一个产品支持多种数据库，但是中间没有两种数据库同时操作。

//普通连接

using (ISqlSugarClient idb = DbRepository.GetInstance(DbType.MySql, SugarDao.MySqlConnString))//DbRepository 框架现成的类，并非自定义

{ 
  var list = idb.Queryable<Student>().First(); 
}

灵活模式

适合多种数据库混用项目

//使用MyRepository连接数据库

using (MyRepository db = new MyRepository())

{

      //当前连接的sqlconn1

      var list = db.Database.Queryable<Student>().First();

      //切换mysqlconn1

      db.SetCurrent(db.MySqlConn1);

      var list2 = db.Database.Queryable<Student>().First(); ;

}

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using SqlSugarRepository;

namespace NewTest.Demos

{

    public class MyRepository:DbRepository

    {

        public ConnectionConfig SqlConn1 = new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = SugarDao.SqlConnString1 };

        public ConnectionConfig SqlConn2 = new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = SugarDao.SqlConnString2 };

        public ConnectionConfig MySqlConn1 = new ConnectionConfig() { DbType = DbType.MySql, ConnectionString = SugarDao.MySqlConnString };

    }

}

MyRepository为自定义类继承 DbRepository

多库支持

统一参数关键词和Sqlparameter

不管操作哪个数据库关键词都是@并且用到Sqlparameter地方不需要切换成MySqlparameter这种，如下写法：

 var student12 = db.Queryable<Student>().Where(c => c.name).Where("id>@id", new { id =  }).ToList();

db.SqlQuery<Student>("select * from Student where id=@id",new SqlParameter("@id","id"));

复杂SQL的选择器

当ORM语法不能满足的时候我们就需要用到原生SQL或者存储过程，sqlbuilder可以根据当前连接哪种数据库返回相应的SQL语句。

string sql = db.SqlBuilder()

.ToSqlServer("select top 1 id,name from student ")

.ToOracle("select id,name from student where rownum=1")

.ToOther("select id,name from student limit 0,1").ToString(); 

var list = db.SqlQuery<string[]>(sql).SingleOrDefault();

返回类型的支持

Queryable返回类型支持四种 List<T> Dynamic JsonString DataTable

var student = db.Queryable<Student>().ToList();

var studentDynamic = db.Queryable<Student>().ToDynamic();

var studentJson = db.Queryable<Student>().ToJson();

var studentTable = db.Queryable<Student>().ToDataTable();

还可以这么玩

var student = db.Queryable<Student>().Select<int>(it=>it.id).ToList();

var student2 = db.Queryable<Student>().Select<string[]>("*").ToList();

var student3 = db.Queryable<Student>().Select<Dictionary<string,object>>("id,name").ToList();

新类

var list1 = db.Queryable<Student>().Where(c => c.id < )

        .Select<V_Student>(c => new V_Student { id = c.id, AreaName = "默认地区" }).ToList();

匿名对象

var list2 = db.Queryable<Student>().Where(c => c.id < )

    .Select<V_Student>(c => new V_Student { id = c.id, AreaName = "默认地区" }).ToDynamic();

...

增删查改

查询

分页亮点

int pageCount = ;

var page = db.Queryable<Student>().Where(c => c.id > ).OrderBy(it => it.id).ToPageList(pageIndex,pageSize,ref pageCount);

//查询所有

var student = db.Queryable<Student>().ToList();

var studentDynamic = db.Queryable<Student>().ToDynamic();

var studentJson = db.Queryable<Student>().ToJson();

//查询单条

var single = db.Queryable<Student>().Single(c => c.id == );

//查询单条根据主键

var singleByPk = db.Queryable<Student>().InSingle();

//查询单条没有记录返回空对象

var singleOrDefault = db.Queryable<Student>().SingleOrDefault(c => c.id == );

//查询单条没有记录返回空对象

var single2 = db.Queryable<Student>().Where(c => c.id == ).SingleOrDefault();

//查询所有的Id

var singleFieldList = db.Queryable<Student>().Select<int>(it=>it.id).ToList();

//查询第一条

var first = db.Queryable<Student>().Where(c => c.id == ).First();

var first2 = db.Queryable<Student>().Where(c => c.id == ).FirstOrDefault();

//取11-20条

var page1 = db.Queryable<Student>().Where(c => c.id > ).OrderBy(it => it.id).Skip().Take().ToList();

//取11-20条  等于 Skip(pageIndex-1)*pageSize).Take(pageSize) 等于  between (pageIndex-1)*pageSize and  pageIndex*pageSize

var page2 = db.Queryable<Student>().Where(c => c.id > ).OrderBy(it => it.id).ToPageList(, );

//查询条数

var count = db.Queryable<Student>().Where(c => c.id > ).Count();

//从第2条开始以后取所有

var skip = db.Queryable<Student>().Where(c => c.id > ).OrderBy(it => it.id).Skip().ToList();

//取前2条

var take = db.Queryable<Student>().Where(c => c.id > ).OrderBy(it => it.id).Take().ToList();

//Not like

string conval = "a";

var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();

//Like

conval = "三";

var like = db.Queryable<Student>().Where(c => c.name.Contains(conval)).ToList();

//支持字符串Where 让你解决，更复杂的查询

var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>@id", new { id =  }).ToList();

var student13 = db.Queryable<Student>().Where(c => "a" == "a").Where(

            db.Sqlbuilder()

            .ToOracle("id>100 and id in( select 1 from dual)")

            .ToOther( "id>100 and id in( select 1)").ToString()

    ).ToList();

//存在记录反回true，则否返回false

bool isAny100 = db.Queryable<Student>().Any(c => c.id == );

bool isAny1 = db.Queryable<Student>().Any(c => c.id == );

//获取最大Id

object maxId = db.Queryable<Student>().Max(it => it.id);

int maxId1 = db.Queryable<Student>().Max(it => it.id).ObjToInt();//拉姆达

int maxId2 = db.Queryable<Student>().Max<int>("id"); //字符串写法

//获取最小

int minId1 = db.Queryable<Student>().Where(c => c.id > ).Min(it => it.id).ObjToInt();//拉姆达

int minId2 = db.Queryable<Student>().Where(c => c.id > ).Min<int>("id");//字符串写法

//order By

var orderList = db.Queryable<Student>().OrderBy("id desc,name asc").ToList();//字符串支持多个排序

//可以多个order by表达示

var order2List = db.Queryable<Student>().OrderBy(it => it.name).OrderBy(it => it.id, OrderByType.desc).ToList(); // order by name as ,order by id desc

//In

var intArray = new[] { "", "", "" };

var intList = intArray.ToList();

var listnew = db.Queryable<Student>().Where(it => intArray.Contains(it.name)).ToList();

var list0 = db.Queryable<Student>().In(it => it.id, , , ).ToList();

var list1 = db.Queryable<Student>().In(it => it.id, intArray).ToList();

var list2 = db.Queryable<Student>().In("id", intArray).ToList();

var list3 = db.Queryable<Student>().In(it => it.id, intList).ToList();

var list4 = db.Queryable<Student>().In("id", intList).ToList();

var list6 = db.Queryable<Student>().In(intList).ToList();//不设置字段默认主键

//分组查询

var list7 = db.Queryable<Student>().Where(c => c.id < ).GroupBy(it => it.sex).Select("sex,count(*) Count").ToDynamic();

var list8 = db.Queryable<Student>().Where(c => c.id < ).GroupBy(it => it.sex).GroupBy(it => it.id).Select("id,sex,count(*) Count").ToDynamic();

List<StudentGroup> list9 = db.Queryable<Student>().Where(c => c.id < ).GroupBy(it => it.sex).Select<StudentGroup>("Sex,count(*) Count").ToList();

List<StudentGroup> list10 = db.Queryable<Student>().Where(c => c.id < ).GroupBy("sex").Select<StudentGroup>("Sex,count(*) Count").ToList();

//SELECT Sex,Count=count(*)  FROM Student  WHERE 1=1  AND  (id < 20)    GROUP BY Sex --生成结果

//2表关联查询

var jList = db.Queryable<Student>()

.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join

.Where<School>((s1, s2) => s1.id == )

.Select("s1.*,s2.name as schName")

.ToDynamic();

/*等于同于

    SELECT s1.*,s2.name as schName

    FROM [Student]  s1

    LEFT JOIN [School]  s2 ON  s1.sch_id  = s2.id

    WHERE  s1.id  = 1 */

//2表关联查询并分页

var jList2 = db.Queryable<Student>()

.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join

    //如果要用inner join这么写

    //.JoinTable<School>((s1, s2) => s1.sch_id == s2.id ,JoinType.INNER)

.Where<School>((s1, s2) => s1.id > )

.OrderBy(s1 => s1.name)

.Skip()

.Take()

.Select("s1.*,s2.name as schName")

.ToDynamic();

//3表查询并分页

var jList3 = db.Queryable<Student>()

.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join  School s2  on s1.id=s2.id

.JoinTable<School>((s1, s3) => s1.sch_id == s3.id) // left join  School s3  on s1.id=s3.id

.Where<School>((s1, s2) => s1.id > )  // where s1.id>1

.Where(s1 => s1.id > )

.OrderBy<School>((s1, s2) => s1.id) //order by s1.id 多个order可以  .oderBy().orderby 叠加

.Skip()

.Take()

.Select("s1.*,s2.name as schName,s3.name as schName2")//select目前只支持这种写法

.ToDynamic();

//上面的方式都是与第一张表join，第三张表想与第二张表join写法如下

List<V_Student> jList4 =

    db.Queryable<Student>()

    .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join  School s2  on s1.id=s2.id

    .JoinTable<School, Area>((s1, s2, a1) => a1.id == s2.AreaId)// left join  Area a1  on a1.id=s2.AreaId  第三张表与第二张表关联

    .JoinTable<Area, School>((s1, a1, s3) => a1.id == s3.AreaId)// left join  School s3  on a1.id=s3.AreaId  第四第表第三张表关联

    .JoinTable<School>((s1, s4) => s1.sch_id == s4.id) // left join  School s2  on s1.id=s4.id

    .Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name }).ToList();

//等同于

//SELECT id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name

//FROM [Student]   s1

//LEFT JOIN School  s2 ON  ( s1.sch_id  = s2.id )

//LEFT JOIN Area  a1 ON  ( a1.id  = s2.AreaId )     //第三张表与第二张表关联

//LEFT JOIN School  s3 ON  ( a1.id  = s3.AreaId )   //第四张表与第三张表关联

//LEFT JOIN School  s4 ON  ( s1.sch_id  = s4.id )

//WHERE 1=1    

//Join子查询语句加分页的写法

var childQuery = db.Queryable<Area>().Where("id=@id").Select(it => new { id = it.id }).ToSql();//创建子查询SQL

//string childTableName =SqlSugarTool.PackagingSQL(childQuery.Key);//将SQL语句用()包成表

//var queryable = db.Queryable<Student>()

// .JoinTable<School>((s1, s2) => s1.sch_id == s2.id)  //LEFT JOIN School  s2 ON  ( s1.sch_id  = s2.id )

// .JoinTable(childTableName, "a1", "a1.id=s2.areaid", new { id = 1 }, JoinType.INNER) //INNER JOIN (SELECT *  FROM [Area]   WHERE 1=1  AND id=@id   ) a1 ON a1.id=s2.areaid

// .OrderBy(s1 => s1.id);

//var list = queryable.Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name })

//    .ToPageList(0, 200);

//var count2 = queryable.Count();

//拼接例子

var queryable2 = db.Queryable<Student>().Where(it => true);

if (maxId.ObjToInt() == )

{

    queryable2.Where(it => it.id == );

}

else

{

    queryable2.Where(it => it.id == );

}

var listJoin = queryable2.ToList();

//queryable和SqlSugarClient解耦

var par =DbRepository.GetISugarQueryableByType<Student>(type).Where(it => it.id == );//声名没有connection对象的Queryable

db.SetDB(par);

var listPar = par.ToList();

//查看生成的sql和参数

var id = ;

var sqlAndPars = db.Queryable<Student>().Where(it => it.id == id).OrderBy(it => it.id).ToSql();

//拉姆达支持的函数操作

var par1 = "2015-1-1"; var par2 = "   我 有空格A, ";

var r1 = db.Queryable<Student>().Where(it => it.name == par1.ObjToString()).ToList(); //ObjToString会将null转转成""

var r2 = db.Queryable<InsertTest>().Where(it => it.d1 == par1.ObjToDate()).ToList();

var r3 = db.Queryable<InsertTest>().Where(it => it.id == .ObjToInt()).ToList();//ObjToInt会将null转转成0

var r4 = db.Queryable<InsertTest>().Where(it => it.id == .ObjToDecimal()).ToList();

var r5 = db.Queryable<InsertTest>().Where(it => it.id == .ObjToMoney()).ToList();

var r6 = db.Queryable<InsertTest>().Where(it => it.v1 == par2.Trim()).ToList();

var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();

var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("")).ToList();

var convert3 = db.Queryable<Student>().Where(c => c.name == par2.ToLower()).ToList();

var convert4 = db.Queryable<Student>().Where(c => c.name == par2.ToUpper()).ToList();

var convert5= db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();

var c1 = db.Queryable<Student>().Where(c => c.name.Contains("a")).ToList();

var c2 = db.Queryable<Student>().Where(c => c.name.StartsWith("a")).ToList();

var c3 = db.Queryable<Student>().Where(c => c.name.EndsWith("a")).ToList();

var c4 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name)).ToList();

var c5 = db.Queryable<Student>().Where(c => c.name.Equals("小杰")).ToList();

var c6 = db.Queryable<Student>().Where(c => c.name.Length > ).ToList();

var time = db.Queryable<InsertTest>().Where(c => c.d1>DateTime.Now.AddDays()).ToList();

var time2 = db.Queryable<InsertTest>().Where(c => c.d1 > DateTime.Now.AddYears()).ToList();

var time3 = db.Queryable<InsertTest>().Where(c => c.d1 > DateTime.Now.AddMonths()).ToList();

插入

插入单条

db.Insert(GetInsertItem()); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)

插入多条

db.InsertRange(GetInsertList()); //批量插入 支持（别名表等功能）

db.SqlBulkCopy(GetInsertList()); //批量插入 适合海量数据插入

设置不插入列

db.DisableInsertColumns = new string[] { "sex" };//sex列将不会插入值

Student s = new Student()

{

name = "张" + new Random().Next(, int.MaxValue),

sex = "gril"

};var id = db.Insert(s); //插入

//查询刚插入的sex是否有值

var sex = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).sex;//无值

var name = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).name;//有值

//SqlBulkCopy同样支持不插入列设置

db.SqlBulkCopy(GetInsertList());

更新

//指定列更新

db.Update<School>(new { name = "蓝翔14" }, it => it.id == ); //只更新name列

db.Update<School, int>(new { name = "蓝翔11 23 12", areaId =  }, , , );

db.Update<School, string>(new { name = "蓝翔2" }, new string[] { "", "" });

db.Update<School>(new { name = "蓝翔2" }, it => it.id == );

var array=new int[]{,,};

db.Update<School>(new { name = "蓝翔2" }, it => array.Contains(it.id));// id in 1,2,3

//支持字典更新，适合动态权限

var dic = new Dictionary<string, string>();

dic.Add("name", "第十三条");

dic.Add("areaId", "");

db.Update<School, int>(dic, );

//整个实体更新

db.Update(new School { id = , name = "蓝翔16", AreaId =  });

db.Update<School>(new School { id = , name = "蓝翔12", AreaId =  }, it => it.id == );

db.Update<School>(new School() { id = , name = "青鸟11" });

//设置不更新列

db.DisableUpdateColumns = new string[] { "CreateTime" };//设置CreateTime不更新

TestUpdateColumns updObj = new TestUpdateColumns()

{

    VGUID = Guid.Parse("542b5a27-6984-47c7-a8ee-359e483c8470"),

    Name = "xx",

    Name2 = "xx2",

    IdentityField = ,

    CreateTime = null

};

//CreateTime将不会被更新

db.Update(updObj);

//以前实现这种更新需要用指定列的方式实现，现在就简单多了。

//批量更新   数据量小时建议使用

var updateResult = db.UpdateRange(GetUpdateList());

//批量更新  数据量大时建议使用

var updateResult2 = db.SqlBulkReplace(GetUpdateList2());

//更新字符串

db.Update<Student>("sch_id=sch_id+1", it => it.id == );

//清空禁止更新列

db.DisableUpdateColumns = null;

//新语法添加禁止更新列

db.AddDisableUpdateColumns("id", "name");//添加禁止更新列

删除

//删除根据主键

db.Delete<School, int>();

//删除根据表达示

db.Delete<School>(it => it.id > );//支持it=>array.contains(it.id)

//主键批量删除

db.Delete<School, string>(new string[] { "", "", "" });

//非主键批量删除

db.Delete<School, string>(it => it.name, new string[] { "" });

db.Delete<School, int>(it => it.id, new int[] { ,  });

//根据实体赋值实体一定要有主键，并且要有值。

db.Delete(new School() { id =  });

//根据字符串删除

db.Delete<School>("id=@id", new { id =  });

//假删除

//db.FalseDelete<school>("is_del", 100);

//等同于 update school set is_del=1 where id in(100)

//db.FalseDelete<school>("is_del", it=>it.id==100);

插入或者更新

//插入或者更新 NEW

//当主键值为 0 、sttring.Empty、NULL 或者 Guid.Empty时执行插操作，否则执行更新

db.AddDisableInsertColumns("UpdateTime");//禁止插入更新时间

db.AddDisableUpdateColumns("CreateTime");//禁止更新创建时间

db.InsertOrUpdate(GetInsertItem2());

SqlSugarORM更多用法

github 源码

秒客网

【开源框架】SqlSugarRepository 全库ORM 正式发布

SqlSugarRepository.dll 全库开发框架支持四种数据库：SqlServer、 MySql 、Oracle和Sqlite。

Sugar ORM 所有成员

数据库连接

多库支持

统一参数关键词和Sqlparameter

复杂SQL的选择器

返回类型的支持

增删查改

查询

插入

更新

删除

插入或者更新

喜欢就给个推荐或者一个Star！！

相关文章

【开源框架】SqlSugarRepository 全库ORM 正式发布

SqlSugarRepository.dll 全库开发框架支持 四种数据库：SqlServer、 MySql 、Oracle和Sqlite。

Sugar ORM 所有成员

数据库连接

多库支持

统一参数关键词和Sqlparameter

复杂SQL的选择器

返回类型的支持

增删查改

查询

插入

更新

删除

插入或者更新

喜欢就给个推荐或者一个Star！！

相关文章

SqlSugarRepository.dll 全库开发框架支持四种数据库：SqlServer、 MySql 、Oracle和Sqlite。