.NET使用TDengine时序数据库和SqlSugar操作TDengine

安装和启动服务

安装和配置

安装服务端程序“TDengine-server-3.0.7.1-Windows-x64.exe”

配置文件路径

"C:\TDengine\cfg\taos.cfg"

修改IP和端口

# The end point of the first dnode in the cluster to be connected to when this dnode or the CLI utility is started
# firstEp                   hostname:6030
firstEp                   192.168.1.212:6030# The FQDN of the host on which this dnode will be started. It can be IP address
# fqdn                      hostname
fqdn                      192.168.1.212

配置日志文件

# The directory for writing log files, if you are using Windows platform please change to Windows path
# logDir                    /var/log/taos
logDir                    C:\TDData\log

# All data files are stored in this directory, if you are using Windows platform please change to Windows path
# dataDir                   /var/lib/taos
dataDir                   C:\TDData\data

编码

# system charset
# charset                   UTF-8
charset                   UTF-8

启动

安装后，在C:\TDengine目录下，运行taosd.exe来启动TDengine服务进程。如需使用http/REST服务，运行taosadapter.exe来启动taosAdapter服务进程

taosd.exe
taosadapter.exe

使用客户端（可选）

安装“TDengine-client-3.0.7.1-Windows-x64.exe”

配置类似服务端

taos.exe
show databases;
USE test;

修改密码

taos.exe
show users; // 显示所有用户
alter user root pass '123456MM'; // 修改密码

DBeaver连接

DBeaver是一款流行的跨平台数据库管理工具，方便开发者、数据库管理员、数据分析师等用户管理数据。DBeaver从23.1.1版本开始内嵌支持TDengine。既支持独立部署的TDengine集群也支持TDengine Cloud

默认用户名和密码如下，如果修改默认密码，使用修改后的密码

host: 192.168.1.212
port: 6041
username: root
password: taosdata

新建.NET项目

新建.NET 8控制台项目

NuGet安装如下：

SqlSugarCore v5.1.4.170
SqlSugar.TDengineCore v4.18.0

// Program.cs
using SqlSugar.TDengine;
using SqlSugar;
using System;
using SqlSugar.DbConvert;
using TDengine.TMQ;
using System.Collections.Generic;
using System.Linq;namespace ConsoleApp
{[SugarTable("mytable02")]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)]public string location { get; set; }[SugarColumn(IsOnlyIgnoreInsert = true, IsOnlyIgnoreUpdate = true)]public int groupId { get; set; }}internal class Program{private static List<MyTable02> GetInsertDatas(){return new List<MyTable02>() {new MyTable02(){ts = DateTime.Now.AddDays(-1),current = Convert.ToSingle(1.1),groupId = 1,isdelete = false,name = "测试1",location = "false",phase = Convert.ToSingle(1.1),voltage = 222},new MyTable02(){ts = DateTime.Now.AddDays(-2),current = Convert.ToSingle(1.1),groupId = 1,isdelete = false,name = "测试2",location = "false",phase = Convert.ToSingle(1.1),voltage = 222},new MyTable02(){ts = DateTime.Now,current = Convert.ToSingle(1.1),groupId = 1,isdelete = true,name = "测试3",location = "true",phase = Convert.ToSingle(1.1),voltage = 111}};}static void Main(string[] args){//程序启动时加入（这个只要执行一次）InstanceFactory.CustomAssemblies =new System.Reflection.Assembly[] { typeof(TDengineProvider).Assembly };//创建 db对象    var db = new SqlSugarClient(new ConnectionConfig(){DbType = SqlSugar.DbType.TDengine,ConnectionString = "Host=192.168.1.212;Port=6030;Username=root;Password=123456MM;Database=demo",IsAutoCloseConnection = true,ConfigureExternalServices = new ConfigureExternalServices(){EntityService = (property, column) =>{if (column.SqlParameterDbType == null){//需要给列加上通用转换，这样实体就不需要一个一个转了 column.SqlParameterDbType = typeof(CommonPropertyConvert);}}}});//db.Insertable(new MyTestTable()//{//    ts = DateTime.Now,//    speed = 100,//}).ExecuteCommand();//建库db.DbMaintenance.CreateDatabase();//建超级表db.Ado.ExecuteCommand("CREATE STABLE IF NOT EXISTS  St01 (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT, isdelete BOOL, name BINARY(64)) TAGS (location BINARY(64), groupId INT)");//创建子表db.Ado.ExecuteCommand(@"create table IF NOT EXISTS  MyTable02 using St01 tags('California.SanFrancisco',1)");//建模//db.DbFirst.CreateClassFile("E:\\4-17测试\\TDengineSqlSugarDemo\\TDengineSqlSugarDemo\\Models", "TDengineSqlSugarDemo.Models");//查询子表var dt = db.Ado.GetDataTable("select * from MyTable02 ");//插入单条子表db.Insertable(new MyTable02(){ts = DateTime.Now,current = Convert.ToSingle(1.1),groupId = 1,isdelete = true,name = "haha",location = "aa",phase = Convert.ToSingle(1.2),voltage = 11}).ExecuteCommand();//批量插入子表db.Insertable(GetInsertDatas()).ExecuteCommand();//查询子表(主表字段也能查出来)var list = db.Queryable<MyTable02>().OrderBy(it => it.ts).ToList();var list1 = db.Queryable<MyTable02>().OrderBy(it => it.ts).Select(it => new {date = it.ts.Date,ts = it.ts}).ToList();//条件查询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();//模糊查询var list3 = db.Queryable<MyTable02>().Where(it => it.name.Contains("a")).ToList();//时间差函数 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();//自定义函数:实现时间加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);//删除子表var ts = list.First().ts;var de = DateTime.Now.AddYears(-1);var count = db.Deleteable<MyTable02>().Where(it => it.ts > de).ExecuteCommand();//异步db.Insertable(new MyTable02(){ts = DateTime.Now,current = Convert.ToSingle(1.1),groupId = 1,isdelete = true,name = "haha",location = "aa",phase = Convert.ToSingle(1.2),voltage = 11}).ExecuteCommandAsync().GetAwaiter().GetResult();var list100 = db.Queryable<MyTable02>().ToListAsync().GetAwaiter().GetResult();//联表查询:不支持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();//联表查询在分页var list102 = db.Queryable<MyTable02, MyTable02>((x, y) => x.ts == y.ts).Select((x, y) => new{xts = x.ts,yts = y.ts}).ToPageList(1, 2);Console.ReadKey();}}
}

启动项目，会提示缺失驱动dll，报错信息如下：

System.DllNotFoundExceptionHResult=0x80131524Message=Unable to load DLL 'taos' or one of its dependencies: 找不到指定的模块。 (0x8007007E)Source=TDengineStackTrace:在 TDengine.Driver.Impl.NativeMethods.NativeMethods.Connect(IntPtr ip, String user, String password, String db, UInt16 port)在 TDengine.Driver.Impl.NativeMethods.NativeMethods.Connect(String ip, String user, String password, String db, UInt16 port)在 TDengine.Driver.Client.Native.NativeClient..ctor(ConnectionStringBuilder builder)在 TDengine.Driver.Client.DbDriver.Open(ConnectionStringBuilder builder)在 SqlSugar.TDengineAdo.TDengineConnection..ctor(String connectionString)在 SqlSugar.TDengine.TDengineProvider.get_Connection()在 SqlSugar.DbMaintenanceProvider.CreateDatabase(String databaseDirectory)在 ConsoleApp1.Program.Main(String[] args) 在 C:\Users\***\Program.cs 中: 第 99 行

解决方法：

在“TDengine-client-3.0.7.1-Windows-x64.exe”安装目录找到driver文件夹C:\TDengine\driver，将driver文件夹内的所有dll和lib文件复制到程序运行目录