SqlSugar-ORM操作手册¶

SqlSuger 介绍¶

SqlSugar 是一款国产老牌 .NET 开源ORM框架，由果糖大数据科技团队维护和更新。

.NET 中唯一支持全自动分表组件，SAAS分库，大数据处理的ORM
.NET 百万级写入、更新性能最强框架
使用最简单的ORM
媲美原生的极限的性能
Github star 数仅次于 EF 和 Dapper

更多优点：简单易用、功能齐全、高性能、轻量级、服务齐全、官网教程文档

支持数据库：MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、人大金仓、神通数据库、Access 、MySqlConnector、自定义扩展。

使用神通数据库¶

1. 安装必要的依赖程序¶

在线安装 Nuget¶

Oscar.Data.SqlClient https://www.nuget.org/packages/Oscar.Data.SqlClient
SqlSugarCore https://www.nuget.org/packages/SqlSugarCore

$ dotnet add package Oscar.Data.SqlClient
$ dotnet add package SqlSugarCore

离线安装¶

从离线文件中分别引用 Oscar.Data.SqlClient.dll 和 SqlSugar.dll 动态库。

2. 快速使用¶

在 SqlSugar 中需要针对神通数据库做相应的配置，即创建一个ConnectionConfig对象，传给 SqlSugarClient 对象。如下示例1

示例1

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
    DbType = DbType.Oscar, // 设置神通数据库 DBTYPE (DbType.Oscar)
    ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=127.0.0.1;PASSWORD=szoscar55;USER ID=SYSDBA", // 配置神通数据库连接字符串
});

SqlSugarClient¶

示例2

private static void SqlSugarClient()
{
    //Create db
    Console.WriteLine("#### SqlSugarClient Start ####");
    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
    {
        DbType = DbType.Oscar,
        ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=127.0.0.1;PASSWORD=szoscar55;USER ID=SYSDBA",
    });

    //If no exist create datebase
    //db.DbMaintenance.CreateDatabase();

    //Use db query
    var dt = db.Ado.GetDataTable("select 1");

    //Create tables
    db.CodeFirst.InitTables(typeof(OrderItem),typeof(Order));
    var id = db.Insertable(new Order() { Name = "order1", CustomId = 1, Price = 0, CreateTime = DateTime.Now }).ExecuteReturnIdentity();

    //Insert data
    db.Insertable(new OrderItem() { OrderId = id, Price = 0, CreateTime=DateTime.Now }).ExecuteCommand();
    Console.WriteLine("#### SqlSugarClient End ####");

}

SimpleClient<T>¶

示例3

public class DbContext
{

    public SqlSugarClient Db;
    public DbContext()
    {
        Db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=127.0.0.1;PASSWORD=szoscar55;USER ID=SYSDBA",
            DbType = DbType.Oscar,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            AopEvents = new AopEvents()
            {
                OnLogExecuting = (sql, p) =>
                {
                    Console.WriteLine(sql);
                }
            }
        });
    }
    public SimpleClient<Order> OrderDb => new SimpleClient<Order>(Db);
    public SimpleClient<OrderItem> OrderItemDb => new SimpleClient<OrderItem>(Db);
}

public class OrderDal : DbContext<Order>
{

}

public class DbContext<T> where T : class, new()
{

    public SqlSugarClient Db;
    public DbContext()
    {
        Db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=127.0.0.1;PASSWORD=szoscar55;USER ID=SYSDBA",
            DbType = DbType.Oscar,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            AopEvents = new AopEvents()
            {
                OnLogExecuting = (sql, p) =>
                {
                    Console.WriteLine(sql);
                }
            }
        });
    }
    public SimpleClient<T> CurrentDb => new SimpleClient<T>(Db);
    public virtual T GetById(int id)
    {
        return CurrentDb.GetById(id);
    }
    public virtual List<T> GetList()
    {
        return CurrentDb.GetList();
    }
    public virtual bool Delete(int id)
    {
        return CurrentDb.DeleteById(id);
    }
}

private static void DbContext()
{
    Console.WriteLine("");
    Console.WriteLine("#### DbContext Start ####");
    var insertObj = new Order { Name = "jack", CreateTime = DateTime.Now };
    var InsertObjs = new Order[] { insertObj };

    DbContext context = new DbContext();

    context.Db.CodeFirst.InitTables<Order, OrderItem,Custom>();//Create Tables
    ;
    var orderDb = context.OrderDb;

    //Select
    var data1 = orderDb.GetById(1);
    var data2 = orderDb.GetList();
    var data3 = orderDb.GetList(it => it.Id == 1);
    var data4 = orderDb.GetSingle(it => it.Id == 1);
    var p = new PageModel() { PageIndex = 1, PageSize = 2 };
    var data5 = orderDb.GetPageList(it => it.Name == "xx", p);
    Console.Write(p.TotalCount);
    var data6 = orderDb.GetPageList(it => it.Name == "xx", p, it => it.Name, OrderByType.Asc);
    Console.Write(p.TotalCount);
    List<IConditionalModel> conModels = new List<IConditionalModel>();
    conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1", FieldValueConvertFunc=it=>Convert.ToInt32(it) });//id=1
    var data7 = orderDb.GetPageList(conModels, p, it => it.Name, OrderByType.Asc);
    orderDb.AsQueryable().Where(x => x.Id == 1).ToList();

    //Insert
    orderDb.Insert(insertObj);
    orderDb.InsertRange(InsertObjs);
    var id = orderDb.InsertReturnIdentity(insertObj);
    orderDb.AsInsertable(insertObj).ExecuteCommand();


    //Delete
    orderDb.Delete(insertObj);
    orderDb.DeleteById(11111);
    orderDb.DeleteById(new int[] { 1111, 2222 });
    orderDb.Delete(it => it.Id == 1111);
    orderDb.AsDeleteable().Where(it => it.Id == 1111).ExecuteCommand();

    //Update
    orderDb.Update(insertObj);
    orderDb.UpdateRange(InsertObjs);
    orderDb.Update(it => new Order() { Name = "a", }, it => it.Id == 1);
    orderDb.AsUpdateable(insertObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();

    //Use Inherit DbContext
    OrderDal dal = new OrderDal();
    var data = dal.GetById(1);
    var list = dal.GetList();

    Console.WriteLine("#### DbContext End ####");
}

主从节点¶

示例4

private static void MasterSlave()
{
    Console.WriteLine("");
    Console.WriteLine("#### MasterSlave Start ####");
    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=10.1.1.66;PASSWORD=szoscar55;USER ID=SYSDBA",//Master Connection
        DbType = DbType.Oscar,
        InitKeyType = InitKeyType.Attribute,
        IsAutoCloseConnection = true,
        SlaveConnectionConfigs = new List<SlaveConnectionConfig>() {
              new SlaveConnectionConfig() { HitRate=10, ConnectionString="PORT=2003;DATABASE=osrdb;HOST=10.1.1.67;PASSWORD=szoscar55;USER ID=SYSDBA" } , // 从机1
              new SlaveConnectionConfig() { HitRate=10, ConnectionString="PORT=2003;DATABASE=osrdb;HOST=10.1.1.67;PASSWORD=szoscar55;USER ID=SYSDBA" }   // 从机2
        }
    });
    db.Aop.OnLogExecuted = (s, p) =>
    {
        Console.WriteLine(db.Ado.Connection.ConnectionString);
    };
    Console.WriteLine("Master:");
    db.Insertable(new Order() { Name = "abc", CustomId = 1, CreateTime = DateTime.Now }).ExecuteCommand();
    Console.WriteLine("Slave:");
    db.Queryable<Order>().First();
    Console.WriteLine("#### MasterSlave End ####");
}

CustomAttributes¶

示例5

private static void CustomAttribute()
{
    Console.WriteLine("");
    Console.WriteLine("#### Custom Attribute Start ####");
    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=10.1.1.66;PASSWORD=szoscar55;USER ID=SYSDBA",
        DbType = DbType.Oscar,
        IsAutoCloseConnection = true,
        InitKeyType = InitKeyType.Attribute,
        ConfigureExternalServices = new ConfigureExternalServices()
        {
            EntityService = (property, column) =>
            {

                var attributes = property.GetCustomAttributes(true);//get all attributes

                if (attributes.Any(it => it is KeyAttribute))// by attribute set primarykey
                {
                    column.IsPrimarykey = true;
                }
            },
            EntityNameService = (type, entity) =>
            {
                var attributes = type.GetCustomAttributes(true);
                if (attributes.Any(it => it is TableAttribute))
                {
                    entity.DbTableName = (attributes.First(it => it is TableAttribute) as TableAttribute).Name;
                }
            }
        }
    });
    db.CodeFirst.InitTables<AttributeTable>();//Create Table

    db.Insertable(new AttributeTable() { Id = Guid.NewGuid().ToString(), Name = "Name" }).ExecuteCommand();
    var list = db.Queryable<AttributeTable>().ToList();

    Console.WriteLine("#### Custom Attribute End ####");
}

SqlSugarScope¶

示例6

static SqlSugarScope singleDb = new SqlSugarScope(
new ConnectionConfig()
{
    ConfigId = 1,
    DbType = DbType.Oscar,
    ConnectionString = "PORT=2003;DATABASE=osrdb;HOST=10.1.1.66;PASSWORD=szoscar55;USER ID=SYSDBA",
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    AopEvents = new AopEvents()
    {
        OnLogExecuting = (sql, p) => { Console.WriteLine(sql); }
    }
});

private static void SingletonPattern()
{
    Console.WriteLine("");
    Console.WriteLine("#### Singleton Pattern Start ####");
    Console.WriteLine("Db_Id:" + singleDb.ContextID);
    Console.WriteLine("Db_Id:" + singleDb.ContextID);
    var task = new Task(() =>
    {
        Console.WriteLine("Task DbId:" + singleDb.ContextID);
        new Task(() =>
        {
            Console.WriteLine("_Task_Task DbId:" + singleDb.ContextID);
            Console.WriteLine("_Task_Task DbId:" + singleDb.ContextID);

        }).Start();
        Console.WriteLine("Task DbId:" + singleDb.ContextID);
    });
    task.Start();
    task.Wait();
    System.Threading.Thread.Sleep(500);
    Console.WriteLine(string.Join(",", singleDb.TempItems.Keys));

    Console.WriteLine("#### Singleton Pattern end ####");
}