提示:以下个人拙见,仅供参考
.NET使用TDengine
前言
提示:这里可以添加本文要记录的大概内容:
TDengine,可以高效地处理海量时序数据。本文将详细介绍如何在.NET项目中使用TDengine数据库,分别以NuGet包管理器安装TDengine.Connectorhe和SqlSugar.TDengineCore两种方式来演示。
提示:以下是本篇文章正文内容,下面案例可供参考
一、 TDengine安装
1.TDengine下载
- 下载地址
- 安装是一直选择下一步,客户端和服务端都要安装
- 找到路径“C:\TDengine\cfg”,编辑红色框中的内容为设备名称
- 打开“win+R”输入services.msc
- 找到taosd和taosadapter服务,设为开机自启
- 在桌面找到taos.Shell打开输入taos回车,看到这样的界面证明安装成功
2.TDengine查看工具
下载地址
安装查看工具后打开,就可以查看数据库对应信息,默认端口6041
二、 使用TDengine.Connectorhe
-
因为TDengine.Connectorhe提供的方法支持.net framework 4.5 和4.5.1,所以framewoke版本的程序只能用这两个版本,.net5以上的不影响,我们用.net framework做一个项目
-
打开vs新建名为TDengineDemo控制台项目,选择.net framewoke 4.5.1
-
添加程序包TDengine.Connectorhe
-
输入代码,运行之后报错
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TDengineDriver;
using TDengineWS.Impl;
// 定义程序入口类
namespace TDengineDemo
{
class Program
{
private static IntPtr conn; // TDengine连接指针
private static IntPtr wsConn; // Websocket连接指针
// 程序主入口方法
static void Main(string[] args)
{
IntPtr conn = GetConnection(); // 获取数据库连接
try
{
// 创建数据库
IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
CheckRes(conn, res, "failed to create database"); // 检查创建结果
res = TDengine.Query(conn, "USE power");
CheckRes(conn, res, "failed to change database"); // 切换到新创建的数据库
// 创建超级表
res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
CheckRes(conn, res, "failed to create stable"); // 检查创建超级表的结果
// 插入数据
var sql = "INSERT INTO d1001 USING meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000) " +
"d1002 USING power.meters TAGS('California.SanFrancisco', 3) VALUES('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000) " +
"d1003 USING power.meters TAGS('California.LosAngeles', 2) VALUES('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000)('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000) " +
"d1004 USING power.meters TAGS('California.LosAngeles', 3) VALUES('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000)('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)";
res = TDengine.Query(conn, sql);
CheckRes(conn, res, "failed to insert data"); // 检查插入数据的结果
int affectedRows = TDengine.AffectRows(res); // 获取影响行数
Console.WriteLine("affectedRows " + affectedRows); // 输出影响行数
TDengine.FreeResult(res); // 释放资源
}
finally
{
TDengine.Close(conn); // 关闭连接
}
}
// 获取TDengine数据库连接的方法
static IntPtr GetConnection()
{
string host = "localhost"; // 主机地址
short port = 6030; // 端口号
string username = "root"; // 用户名
string password = "taosdata"; // 密码
string dbname = ""; // 数据库名
var conn = TDengine.Connect(host, username, password, dbname, port); // 建立连接
if (conn == IntPtr.Zero)
{
throw new Exception("Connect to TDengine failed"); // 连接失败抛出异常
}
else
{
Console.WriteLine("Connect to TDengine success"); // 输出连接成功信息
}
return conn; // 返回连接指针
}
// 检查查询结果是否成功的辅助方法
static void CheckRes(IntPtr conn, IntPtr res, String errorMsg)
{
if (TDengine.ErrorNo(res) != 0)
{
throw new Exception($"{errorMsg} since: {TDengine.Error(res)}"); // 如果有错误则抛出异常
}
}
}
}
- 这个是因为该处使用的url网络请求的数据,taos.dll文件使用x64平台编译,所以.NET项目在生成.exe文件时,“解决方案”/“项目”的“平台”请均选择“x64”。右击解决方案,选择“属性”,点击“生成”,将目标平台改成“x64”,即可成功运行项目
- 修改完成,继续执行代码
二、使用SqlSugar.TDengineCore
- 因为SqlSugar.TDengineCore提供的方法支持.net 5net5以上的不影响,我们用.net6做一个项目,新建.一个名为TDengineSqlSugarDemo 的.net6控制台程序,新增Models文件夹 官网
- 添加程序包SqlSugarCore、SqlSugar.TDengineCore
新增SugarClient.cs,代码如下
using SqlSugar;
using SqlSugar.DbConvert;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TDengine.TMQ;
namespace TDengineSqlSugarDemo
{
public class SugarClient
{
// 初始化SqlSugarClient
public static SqlSugarClient Init()
{
string connectionString = "Host=localhost;Port=6030;Username=root;Password=taosdata;Database=Gather_db";
var db = new SqlSugarClient(new ConnectionConfig()
{
// 数据库类型
DbType = SqlSugar.DbType.TDengine,
// 连接字符串
ConnectionString = connectionString,
// 是否自动关闭连接
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
// 执行前
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(UtilMethods.GetNativeSql(sql, p));
}
},
ConfigureExternalServices = new ConfigureExternalServices()
{
EntityService = (property, column) =>
{
if (column.SqlParameterDbType == null)
{
column.SqlParameterDbType = typeof(CommonPropertyConvert);
}
}
}
});
return db;
}
}
}
- 新增DemoMain.cs,代码如下
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TDengineSqlSugarDemo
{
public class DemoMain
{
public static void Create()
{
SqlSugarClient db = SugarClient.Init();
//建库
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);
}
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
}
};
}
public class fc_data
{
public DateTime upload_time { get; set; }
public int voltage { get; set; }
public float temperature { get; set; }
public int data_id { get; set; }
public string speed_hex { get; set; }
public string gateway_mac { get; set; }
public int ruminate { get; set; }
public sbyte rssi { get; set; }
}
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; }
}
}
}
- 启动程序
- 使用TDengine查看数据
总结
提示:这里对文章进行总结:
以上就是今天要讲的内容,本文简单介绍了.net使用TDengine的两种方式,TDengine.Connectorhe可以适配低版本的.net,而sqlsugar操作更为方便,推荐使用SqlSugar版本的。