TDengine?

是國內(nèi)比較流的時序庫之一，支持群集并且免費，在.NET中資料比較少，這篇文章主要介紹SqlSugar ORM來操作TDengine

優(yōu)點：

1、SqlSugar支持ADO.NET操作來實現(xiàn)TDengine，并且支持了常用的時間函數(shù)、支持聯(lián)表、分頁和批量插入和刪等操作

2、使用了原生連接（非http連接 ）保證了TDengine性能

?

TDengine 連接字符串

Host=localhost;Port=6030;Username=root;Password=taosdata;Database=power

TDengine 客戶端SDK安裝

當(dāng)前程序需要安裝Sdk才能連接數(shù)據(jù)庫

https://docs.taosdata.com/connector

TDengine Nuegt安裝

下面是NUGET安裝

SqlSugar.TDengineCore
SqlSugarCore

執(zhí)行SQL

    //建庫db.Ado.ExecuteCommand("CREATE DATABASE IF NOT EXISTS power WAL_RETENTION_PERIOD 3600");//建超級表db.Ado.ExecuteCommand("CREATE STABLE IF NOT EXISTS  MyTable (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");//創(chuàng)建子表db.Ado.ExecuteCommand(@"create table IF NOT EXISTS  MyTable01 using MyTable tags('California.SanFrancisco',1)");//insert sql//db.Ado.ExecuteCommand(insrtSql);//查詢子表var dt = db.Ado.GetDataTable("select * from MyTable01");//查詢超級表var dt2 = db.Ado.GetDataTable("select * from MyTable");

單表查詢

var list2 = db.Queryable<MyTable02>().Where(it => it.name == "測試2").ToList();var list22 = db.Queryable<MyTable02>().Where(it => it.voltage == 222).ToList();var list222 = db.Queryable<MyTable02>().Where(it => it.phase == 1.2).ToList();var list2222 = db.Queryable<MyTable02>().Where(it => it.isdelete == true).ToList();

聯(lián)表查詢

  //聯(lián)表查詢:不支持left join只能這樣var list101= db.Queryable<MyTable02, MyTable02>((x, y) => x.ts == y.ts).Select((x,y) => new{xts=x.ts,yts=y.ts}).ToList();

函數(shù)用法

  //模糊查詢var list3 = db.Queryable<MyTable02>().Where(it => it.name.Contains("a")).ToList();//時間差函數(shù) var list31 = db.Queryable<MyTable02>().Select(it =>new{diff = SqlFunc.DateDiff(DateType.Day, it.ts, DateTime.Now),time=it.ts}).ToList();//時間加1天var list32 = db.Queryable<MyTable02>().Select(it =>new{addTime = SqlFunc.DateAdd(it.ts,1, DateType.Day),oldime = it.ts}).ToList();//自定義函數(shù):實現(xiàn)時間加1天var list33 = db.Queryable<MyTable02>().Select(it =>new{addTime =SqlFunc.MappingColumn<DateTime>(" `ts`+1d "),oldime = it.ts}).ToList();

分頁查詢

   //分頁var Count = 0;var list4 = db.Queryable<MyTable02>().Where(it => it.voltage == 111).ToPageList(1, 2, ref Count);

插入

超級表是不能插入的，我們只插入子表

//批量插入子表db.Insertable(GetInsertDatas()).ExecuteCommand();//子表實體public class MyTable02{[SugarColumn(IsPrimaryKey =true)]public DateTime ts { get; set; }public float current { get; set; }public bool isdelete { get; set; }public string name { get; set; }public int voltage { get; set; }public float phase { get; set; }[SugarColumn(IsOnlyIgnoreInsert =true,IsOnlyIgnoreUpdate =true)]//Tags字段禁止插入public string location { get; set; }[SugarColumn(IsOnlyIgnoreInsert = true, IsOnlyIgnoreUpdate = true)]//Tags字段禁止插入public int groupId { get; set; }}

刪除數(shù)據(jù)

只能范圍刪除不能直接等于刪除

var count = db.Deleteable<MyTable02>().Where(it => it.ts>time).ExecuteCommand();

源碼和DEMO

找到TDengineTests文件夾

https://github.com/DotNetNext/SqlSugar

?