.NET使用TDengine時序資料庫和SqlSugar操作TDengine

.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.DllNotFoundException
  HResult=0x80131524
  Message=Unable to load DLL 'taos' or one of its dependencies: 找不到指定的模組。 (0x8007007E)
  Source=TDengine
  StackTrace:
   在 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檔案複製到程式執行目錄

參考

• 使用安裝包快速體驗 TDengine
• TDengine伺服器與客戶端安裝
• 與 DBeaver 的整合
• .NET使用TDengine實戰指南