private bool CheckDataBase()
{
if (!dbisok)
{
dbisok = Retry.RetryOnAny(10, f =>
{
TaosConnection _taos = _taospool.Get();
{
var _taosBuilder = new TaosConnectionStringBuilder(_taos.ConnectionString);
if (_taos.State != System.Data.ConnectionState.Open)
{
_taos.Open();
}
_taos.CreateCommand($"CREATE DATABASE IF NOT EXISTS {_taosBuilder.DataBase} KEEP 365 DAYS 10 BLOCKS 4;").ExecuteNonQuery();
_taos.ChangeDatabase(_taosBuilder.DataBase);
_taos.CreateCommand("CREATE TABLE IF NOT EXISTS telemetrydata (ts timestamp,value_type tinyint, value_boolean bool, value_string binary(10240), value_long bigint,value_datetime timestamp,value_double double) TAGS (deviceid binary(32),keyname binary(64));")
.ExecuteNonQuery();
dbisok = true;
_taospool.Return(_taos);
}
return(true);
}, ef =>
{
_logger.LogError(ef.ex, $"CheckDataBase第{ef.current}次失败{ef.ex.Message} {ef.ex.InnerException?.Message} ");
});
}
return(dbisok);
}
static void UploadTelemetryData(TaosConnection connection, string devid, string keyname, int count)
{
for (int i = 0; i < count; i++)
{
connection.CreateCommand($"INSERT INTO device_{devid}_{keyname} USING telemetrydata TAGS(\"{devid}\",\"{keyname}\") values (now,2,true,'{i}',{i},now,{i});").ExecuteNonQuery();
}
}
static void Main(string[] args)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
///Specify the name of the database
string database = "db_" + DateTime.Now.ToString("yyyyMMddHHmmss");
var builder = new TaosConnectionStringBuilder()
{
DataSource = "114.116.231.247",
DataBase = database,
Username = "******",
Password = "******",
Port = 6060
};
//Example for ADO.Net
using (var connection = new TaosConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("create {0} {1}", database, connection.CreateCommand($"create database {database};").ExecuteNonQuery());
Console.WriteLine("create table t {0} {1}", database, connection.CreateCommand($"create table {database}.t (ts timestamp, cdata int);").ExecuteNonQuery());
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 10);").ExecuteNonQuery());
//Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.AddMonths(1).Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 20);").ExecuteNonQuery());
var cmd_select = connection.CreateCommand();
cmd_select.CommandText = $"select * from {database}.t";
var reader = cmd_select.ExecuteReader();
Console.WriteLine(cmd_select.CommandText);
Console.WriteLine("");
ConsoleTableBuilder.From(reader.ToDataTable()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
Console.WriteLine("");
Console.WriteLine("DROP TABLE {0} {1}", database, connection.CreateCommand($"DROP TABLE {database}.t;").ExecuteNonQuery());
Console.WriteLine("DROP DATABASE {0} {1}", database, connection.CreateCommand($"DROP DATABASE {database};").ExecuteNonQuery());
connection.Close();
}
//Example for Entity Framework Core
using (var context = new TaosContext(new DbContextOptionsBuilder()
.UseTaos(builder.ConnectionString).Options))
{
Console.WriteLine("EnsureCreated");
context.Database.EnsureCreated();
for (int i = 0; i < 10; i++)
{
var rd = new Random();
context.sensor.Add(new sensor()
{
ts = DateTime.Now.AddMilliseconds(i), degree = rd.NextDouble(), pm25 = rd.Next(0, 1000)
});
}
Console.WriteLine("Saveing");
context.SaveChanges();
Console.WriteLine("");
Console.WriteLine("from s in context.sensor where s.pm25 > 0 select s ");
Console.WriteLine("");
var f = from s in context.sensor where s.pm25 > 0 select s;
var ary = f.ToArray();
ConsoleTableBuilder.From(ary.ToList()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
context.Database.EnsureDeleted();
}
Console.WriteLine("");
}
public async Task <HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken = default)
{
try
{
using (var connection = new TaosConnection(_connectionString))
{
await connection.OpenAsync(cancellationToken);
using (var command = connection.CreateCommand())
{
command.CommandText = _sql;
var result = await command.ExecuteScalarAsync(cancellationToken);
if (_sql == TaosHealthCheckBuilderExtensions.HEALTH_QUERY)
{
var _result = Convert.ToInt32(result);
if (_result != 1)
{
return(new HealthCheckResult(context.Registration.FailureStatus, description: $"Server status:{_result}"));
}
}
}
return(HealthCheckResult.Healthy());
}
}
catch (Exception ex)
{
return(new HealthCheckResult(context.Registration.FailureStatus, exception: ex));
}
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected override DbConnection CreateDbConnection()
{
var connection = new TaosConnection(ConnectionString);
_connection = connection;
return(connection);
}
static void Main(string[] args)
{
///Specify the name of the database
string database = "db_" + DateTime.Now.ToString("yyyyMMddHHmmss");
var builder = new TaosConnectionStringBuilder()
{
DataSource = "http://td.gitclub.cn/rest/sql",
DataBase = database,
Username = "******",
Password = "******"
};
//Example for ADO.Net
using (var connection = new TaosConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("create {0} {1}", database, connection.CreateCommand($"create database {database};").ExecuteNonQuery());
Console.WriteLine("create table t {0} {1}", database, connection.CreateCommand($"create table {database}.t (ts timestamp, cdata int);").ExecuteNonQuery());
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ms")}', 10);").ExecuteNonQuery());
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ('{DateTime.Now.AddMonths(1).ToString("yyyy-MM-dd HH:mm:ss.ms")}', 20);").ExecuteNonQuery());
var cmd_select = connection.CreateCommand();
cmd_select.CommandText = $"select * from {database}.t";
var reader = cmd_select.ExecuteReader();
Console.WriteLine(cmd_select.CommandText);
Console.WriteLine("");
ConsoleTableBuilder.From(reader.ToDataTable()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
Console.WriteLine("");
Console.WriteLine("DROP TABLE {0} {1}", database, connection.CreateCommand($"DROP TABLE {database}.t;").ExecuteNonQuery());
Console.WriteLine("DROP DATABASE {0} {1}", database, connection.CreateCommand($"DROP DATABASE {database};").ExecuteNonQuery());
connection.Close();
}
//Example for Entity Framework Core
using (var context = new TaosContext(new DbContextOptionsBuilder()
.UseTaos(builder.ConnectionString).Options))
{
Console.WriteLine("EnsureCreated");
context.Database.EnsureCreated();
for (int i = 0; i < 10; i++)
{
var rd = new Random();
context.sensor.Add(new sensor()
{
ts = DateTime.Now.AddMilliseconds(i), degree = rd.NextDouble(), pm25 = rd.Next(0, 1000)
});
}
Console.WriteLine("Saveing");
context.SaveChanges();
Console.WriteLine("");
Console.WriteLine("from s in context.sensor where s.pm25 > 0 select s ");
Console.WriteLine("");
var f = from s in context.sensor where s.pm25 > 0 select s;
var ary = f.ToArray();
ConsoleTableBuilder.From(ary.ToList()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
context.Database.EnsureDeleted();
}
Console.WriteLine("");
Console.WriteLine("Pass any key to exit....");
Console.ReadKey();
}
private async Task <TaosConnection> AsyncConnect(TaosConnectionStringBuilder connectionString)
{
return(await Task.Run(() =>
{
var tc = new TaosConnection(connectionString.ToString());
tc.Open();
return tc;
}));
}
public Task <List <TelemetryDataDto> > GetTelemetryLatest(Guid deviceId)
{
using (TaosConnection db = new TaosConnection(_taosBuilder.ConnectionString))
{
string sql = $"select last_row(*) from telemetrydata where deviceid='{deviceId:N}' group by deviceid,keyname";
List <TelemetryDataDto> dt = SqlToTDD(db, sql, "last_row(", ")", string.Empty);
return(Task.FromResult(dt));
}
}
public Task <List <TelemetryDataDto> > LoadTelemetryAsync(Guid deviceId, DateTime begin, DateTime end)
{
using (TaosConnection db = new TaosConnection(_taosBuilder.ConnectionString))
{
string sql = $"select tbname,keyname from telemetrydata where deviceid='{deviceId:N}'";
List <TelemetryDataDto> dt = SQLToDTByDate(begin, end, db, sql);
return(Task.FromResult(dt));
}
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public override DatabaseModel Create(string connectionString, DatabaseModelFactoryOptions options)
{
Check.NotNull(connectionString, nameof(connectionString));
Check.NotNull(options, nameof(options));
using (var connection = new TaosConnection(connectionString))
{
return(Create(connection, options));
}
}
public void UpdateCodeCompletion(TaosConnection db)
{
_codeCompletionData = new List <ICompletionData>();
var item = new DefaultCompletionData(DateTime.Now.Second.ToString(), "segundos", 4);
item.Priority = double.MaxValue;
_codeCompletionData.Add(item);
// getting all BsonExpression methods
//foreach (var m in BsonExpression.Methods.OrderBy(x => x.Name))
//{
// var text = m.Name;
// var description = $"Method:\n- {text}({string.Join(", ", m.GetParameters().Select(x => x.Name))})";
// var icon = 0; // METHOD
// _codeCompletionData.Add(new DefaultCompletionData(text, description, icon));
//}
// get all keywords
var words = new List <string>();
using (var stream = typeof(TextAreaControl).Assembly.GetManifestResourceStream("ICSharpCode.TextEditor.Resources.SQL-Mode.xshd"))
{
using (var reader = new StreamReader(stream))
{
var content = reader.ReadToEnd();
var xml = new XmlDocument();
xml.LoadXml(content);
var nodes = xml.DocumentElement.SelectNodes("/SyntaxDefinition/RuleSets/RuleSet/KeyWords[@name=\"SqlKeywordsNormal\"]/Key");
words.AddRange(nodes.Cast <XmlNode>().Select(x => x.Attributes["word"].Value));
}
}
_codeCompletionData.AddRange(words.OrderBy(x => x).Select(x => new DefaultCompletionData(x, null, 3)));
if (db == null)
{
return;
}
// collections
var cols = new string[] { };// db.GetSchema("$cols").ToList().Query().ToArray();
//_codeCompletionData.AddRange(cols.Select(x => new DefaultCompletionData(x["name"].AsString,
// (x["type"] == "user" ? "User collection:\n- " : "System collection:\n- ") +
// x["name"].AsString,
// x["type"] == "user" ? 1 :
// x["type"] == "system" ? 5 : 4)));
}
public Task <List <TelemetryDataDto> > LoadTelemetryAsync(Guid deviceId, string keys, DateTime begin, DateTime end)
{
using (TaosConnection db = new TaosConnection(_taosBuilder.ConnectionString))
{
IEnumerable <string> kvs = from k in keys
select $" keyname = '{k}' ";
string sql = $"select tbname,keyname from telemetrydata where deviceid='{deviceId:N}' and ({string.Join("or", kvs) }) ";
List <TelemetryDataDto> dt = SQLToDTByDate(begin, end, db, sql);
return(Task.FromResult(dt));
}
}
public Task <List <TelemetryDataDto> > GetTelemetryLatest(Guid deviceId, string keys)
{
using (TaosConnection db = new TaosConnection(_taosBuilder.ConnectionString))
{
IEnumerable <string> kvs = from k in keys
select $" keyname = '{k}' ";
string sql = $"select last_row(*) from telemetrydata where deviceid='{deviceId:N}' and ({string.Join("or", kvs) }) group by deviceid,keyname";
List <TelemetryDataDto> dt = SqlToTDD(db, sql, "last_row(", ")", string.Empty);
return(Task.FromResult(dt));
}
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public virtual DatabaseModel Create(string connectionString, IEnumerable <string> tables, IEnumerable <string> schemas)
{
Check.NotNull(connectionString, nameof(connectionString));
Check.NotNull(tables, nameof(tables));
Check.NotNull(schemas, nameof(schemas));
using (var connection = new TaosConnection(connectionString))
{
return(Create(connection, tables, schemas));
}
}
private List <TelemetryDataDto> SqlToTDD(TaosConnection db, string sql, string prefix, string suffix, string keyname)
{
List <TelemetryDataDto> dt = new List <TelemetryDataDto>();
TaosDataReader dataReader = db.CreateCommand(sql).ExecuteReader();
while (dataReader.Read())
{
TelemetryDataDto telemetry = new TelemetryDataDto();
byte datatype = (byte)dataReader[dataReader.GetOrdinal($"{prefix}value_type{suffix}")];
if (string.IsNullOrEmpty(keyname))
{
telemetry.KeyName = dataReader.GetString(dataReader.GetOrdinal("keyname"));
}
else
{
telemetry.KeyName = keyname;
}
telemetry.DateTime = dataReader.GetDateTime(dataReader.GetOrdinal($"{prefix}ts{suffix}"));
switch ((DataType)datatype)
{
case DataType.Boolean:
telemetry.Value = dataReader.GetBoolean(dataReader.GetOrdinal($"{prefix}value_boolean{suffix}"));
break;
case DataType.String:
telemetry.Value = dataReader.GetString(dataReader.GetOrdinal($"{prefix}value_string{suffix}"));
break;
case DataType.Long:
telemetry.Value = dataReader.GetInt64(dataReader.GetOrdinal($"{prefix}value_long{suffix}"));
break;
case DataType.Double:
telemetry.Value = dataReader.GetDouble(dataReader.GetOrdinal($"{prefix}value_double{suffix}"));
break;
case DataType.Json:
case DataType.XML:
case DataType.Binary:
telemetry.Value = dataReader.GetString(dataReader.GetOrdinal($"{prefix}value_string{suffix}"));
break;
case DataType.DateTime:
telemetry.Value = dataReader.GetDateTime(dataReader.GetOrdinal($"{prefix}value_datetime{suffix}"));
break;
default:
break;
}
dt.Add(telemetry);
}
return(dt);
}
private List <TelemetryDataDto> SQLToDTByDate(DateTime begin, DateTime end, TaosConnection db, string sql)
{
List <TelemetryDataDto> dt = new List <TelemetryDataDto>();
List <(string tbname, string keyname)> list = db.CreateCommand(sql).ExecuteReader().ToList <(string tbname, string keyname)>();
foreach ((string tbname, string keyname)item in list)
{
string susql = $" select * from {item.tbname} where ts >={begin:yyyy-MM-dd HH:mm:ss.fff} and ts <={end:yyyy-MM-dd HH:mm:ss.fff}";
List <TelemetryDataDto> dtx = SqlToTDD(db, susql, "", "", item.keyname);
dt.AddRange(dtx);
}
return(dt);
}
public Task <List <TelemetryDataDto> > GetTelemetryLatest(Guid deviceId)
{
TaosConnection _taos = _taospool.Get();
if (_taos.State != System.Data.ConnectionState.Open)
{
_taos.Open();
}
string sql = $"select last_row(*) from telemetrydata where deviceid='{deviceId:N}' group by deviceid,keyname";
List <TelemetryDataDto> dt = SqlToTDD(_taos, sql, "last_row(", ")", string.Empty);
_taospool.Return(_taos);
return(Task.FromResult(dt));
}
public Task <List <TelemetryDataDto> > LoadTelemetryAsync(Guid deviceId, DateTime begin, DateTime end)
{
TaosConnection _taos = _taospool.Get();
if (_taos.State != System.Data.ConnectionState.Open)
{
_taos.Open();
}
string sql = $"select tbname,keyname from telemetrydata where deviceid='{deviceId:N}'";
List <TelemetryDataDto> dt = SQLToDTByDate(begin, end, _taos, sql);
_taospool.Return(_taos);
return(Task.FromResult(dt));
}
public Task <List <TelemetryDataDto> > LoadTelemetryAsync(Guid deviceId, string keys, DateTime begin, DateTime end)
{
TaosConnection _taos = _taospool.Get();
if (_taos.State != System.Data.ConnectionState.Open)
{
_taos.Open();
}
IEnumerable <string> kvs = from k in keys
select $" keyname = '{k}' ";
string sql = $"select tbname,keyname from telemetrydata where deviceid='{deviceId:N}' and ({string.Join("or", kvs) }) ";
List <TelemetryDataDto> dt = SQLToDTByDate(begin, end, _taos, sql);
_taospool.Return(_taos);
return(Task.FromResult(dt));
}
public Task <List <TelemetryDataDto> > GetTelemetryLatest(Guid deviceId, string keys)
{
TaosConnection _taos = _taospool.Get();
if (_taos.State != System.Data.ConnectionState.Open)
{
_taos.Open();
}
IEnumerable <string> kvs = from k in keys
select $" keyname = '{k}' ";
string sql = $"select last_row(*) from telemetrydata where deviceid='{deviceId:N}' and ({string.Join("or", kvs) }) group by deviceid,keyname";
List <TelemetryDataDto> dt = SqlToTDD(_taos, sql, "last_row(", ")", string.Empty);
_taospool.Return(_taos);
return(Task.FromResult(dt));
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public TaosRelationalConnection(
[NotNull] RelationalConnectionDependencies dependencies,
[NotNull] IRawSqlCommandBuilder rawSqlCommandBuilder)
: base(dependencies)
{
Check.NotNull(rawSqlCommandBuilder, nameof(rawSqlCommandBuilder));
_rawSqlCommandBuilder = rawSqlCommandBuilder;
var optionsExtension = dependencies.ContextOptions.Extensions.OfType <TaosOptionsExtension>().FirstOrDefault();
if (optionsExtension != null)
{
var relationalOptions = RelationalOptionsExtension.Extract(dependencies.ContextOptions);
_connection = new TaosConnection(relationalOptions.ConnectionString);
}
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public override bool Exists()
{
var count = 0;
try
{
using (var tc = new TaosConnection(Dependencies.Connection.ConnectionString))
{
tc.Open();
tc.Close();
}
}
catch (Exception)
{
}
return(count != 0);
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public override bool HasTables()
{
var count = 0;
try
{
using (var tc = new TaosConnection(Dependencies.Connection.ConnectionString))
{
tc.Open();
count = tc.CreateCommand("SHOW TABLES").ExecuteReader().ToJson().Count();
tc.Close();
}
}
catch (Exception)
{
}
return(count != 0);
}
internal static void FillTableToTree(this TreeNodeMouseClickEventArgs e, TaosConnection _db, ContextMenuStrip contextMenu, string rootkey, string title, string imgkey, string sql, string tablename)
{
TreeNode stable = null;
if (!e.Node.Nodes.ContainsKey(rootkey))
{
stable = e.Node.Nodes.Add(rootkey, title, imgkey);
stable.SelectedImageKey = "";
}
else
{
stable = e.Node.Nodes[rootkey];
}
JArray sjtable = _db.CreateCommand(sql).ExecuteReader().ToJson();
List <string> stlst = new List <string>();
sjtable.ToList().ForEach(a =>
{
string name = a.Value <string>(tablename).RemoveNull();
stlst.Add(name);
if (!stable.Nodes.ContainsKey(name))
{
TreeNode node = stable.Nodes.Add(name, name, imgkey);
node.Tag = a;
node.ContextMenuStrip = contextMenu;
}
});
foreach (TreeNode item in stable.Nodes)
{
if (stlst.Contains(item.Name))
{
stlst.Remove(item.Name);
}
}
stlst.ForEach(s =>
{
stable.Nodes.RemoveByKey(s);
});
}
/// <summary>
/// 转换获取到的值
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="prefix"></param>
/// <param name="suffix"></param>
/// <param name="keyname"></param>
/// <returns></returns>
/// <exception cref="https://github.com/taosdata/TDengine/issues/4269">务必注意此bug</exception>
private List <TelemetryDataDto> SqlToTDD(TaosConnection db, string sql, string prefix, string suffix, string keyname)
{
List <TelemetryDataDto> dt = new List <TelemetryDataDto>();
TaosDataReader dataReader = db.CreateCommand(sql).ExecuteReader();
while (dataReader.Read())
{
TelemetryDataDto telemetry = new TelemetryDataDto();
try
{
int idx = dataReader.GetOrdinal($"{prefix}value_type{suffix}");
byte datatype;
if (dataReader.FieldCount > idx && idx >= 0)
{
datatype = dataReader.GetByte(idx);
}
else
{
throw new Exception($"字段{prefix}value_type{suffix}的Index={idx}小于0或者大于FieldCount{dataReader.FieldCount},更多信息请访问 HelpLink")
{
HelpLink = "https://github.com/taosdata/TDengine/issues/4269"
};
}
if (string.IsNullOrEmpty(keyname))
{
telemetry.KeyName = dataReader.GetString(dataReader.GetOrdinal("keyname"));
}
else
{
telemetry.KeyName = keyname;
}
telemetry.DateTime = dataReader.GetDateTime(dataReader.GetOrdinal($"{prefix}ts{suffix}"));
switch ((DataType)datatype)
{
case DataType.Boolean:
telemetry.Value = dataReader.GetBoolean(dataReader.GetOrdinal($"{prefix}value_boolean{suffix}"));
break;
case DataType.String:
telemetry.Value = dataReader.GetString(dataReader.GetOrdinal($"{prefix}value_string{suffix}"));
break;
case DataType.Long:
telemetry.Value = dataReader.GetInt64(dataReader.GetOrdinal($"{prefix}value_long{suffix}"));
break;
case DataType.Double:
telemetry.Value = dataReader.GetDouble(dataReader.GetOrdinal($"{prefix}value_double{suffix}"));
break;
case DataType.Json:
case DataType.XML:
case DataType.Binary:
telemetry.Value = dataReader.GetString(dataReader.GetOrdinal($"{prefix}value_string{suffix}"));
break;
case DataType.DateTime:
telemetry.Value = dataReader.GetDateTime(dataReader.GetOrdinal($"{prefix}value_datetime{suffix}"));
break;
default:
break;
}
}
catch (Exception ex)
{
_logger.LogError(ex, $"{telemetry.KeyName}遇到{ex.Message}, sql:{sql}");
}
if (!string.IsNullOrEmpty(telemetry.KeyName))
{
dt.Add(telemetry);
}
}
return(dt);
}
static void Main(string[] args)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
///Specify the name of the database
string database = "db_" + DateTime.Now.ToString("yyyyMMddHHmmss");
var builder = new TaosConnectionStringBuilder()
{
DataSource = "taos",
DataBase = database,
Username = "******",
Password = "******",
Port = 6030
};
//Example for ADO.Net
using (var connection = new TaosConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("ServerVersion:{0}", connection.ServerVersion);
Console.WriteLine("create {0} {1}", database, connection.CreateCommand($"create database {database};").ExecuteNonQuery());
connection.ChangeDatabase(database);
Console.WriteLine("create table t {0} {1}", database, connection.CreateCommand($"create table {database}.t (ts timestamp, cdata int);").ExecuteNonQuery());
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 10);").ExecuteNonQuery());
var pmcmd = connection.CreateCommand($"insert into {database}.t values (@t, @c);");
Thread.Sleep(TimeSpan.FromSeconds(1));
pmcmd.Parameters.AddWithValue("@t", (long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds));
pmcmd.Parameters.AddWithValue("@c", 1111);
pmcmd.ExecuteNonQuery();
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 10);").ExecuteNonQuery());
//Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.AddMonths(1).Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 20);").ExecuteNonQuery());
var cmd_select = connection.CreateCommand();
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
cmd_select.CommandText = $"select * from {database}.t";
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
var reader = cmd_select.ExecuteReader();
Console.WriteLine(cmd_select.CommandText);
Console.WriteLine("");
ConsoleTableBuilder.From(reader.ToDataTable()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
Console.WriteLine("");
Console.WriteLine("CREATE TABLE meters ", connection.CreateCommand($"CREATE TABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location binary(64), groupdId int);").ExecuteNonQuery());
Console.WriteLine("CREATE TABLE d1001 ", connection.CreateCommand($"CREATE TABLE d1001 USING meters TAGS (\"Beijing.Chaoyang\", 2);").ExecuteNonQuery());
Console.WriteLine("INSERT INTO d1001 ", connection.CreateCommand($"INSERT INTO d1001 USING METERS TAGS(\"Beijng.Chaoyang\", 2) VALUES(now, 10.2, 219, 0.32);").ExecuteNonQuery());
Console.WriteLine("DROP TABLE {0} {1}", database, connection.CreateCommand($"DROP TABLE {database}.t;").ExecuteNonQuery());
Console.WriteLine("DROP DATABASE {0} {1}", database, connection.CreateCommand($"DROP DATABASE {database};").ExecuteNonQuery());
connection.Close();
}
//Example for Entity Framework Core
using (var context = new TaosContext(new DbContextOptionsBuilder()
.UseTaos(builder.ConnectionString).Options))
{
Console.WriteLine("EnsureCreated");
context.Database.EnsureCreated();
for (int i = 0; i < 10; i++)
{
var rd = new Random();
context.sensor.Add(new sensor()
{
ts = DateTime.Now.AddMilliseconds(i + 10), degree = rd.NextDouble(), pm25 = rd.Next(0, 1000)
});
Thread.Sleep(10);
}
Console.WriteLine("Saveing");
context.SaveChanges();
Console.WriteLine("");
Console.WriteLine("from s in context.sensor where s.pm25 > 0 select s ");
Console.WriteLine("");
var f = from s in context.sensor where s.pm25 > 0 select s;
var ary = f.ToArray();
if (ary.Any())
{
ConsoleTableBuilder.From(ary.ToList()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
}
context.Database.EnsureDeleted();
}
}
public async Task <bool> StoreTelemetryAsync(RawMsg msg)
{
bool result = false;
try
{
CheckDataBase();
List <string> lst = new List <string>();
msg.MsgBody.ToList().ForEach(kp =>
{
if (kp.Value != null)
{
TelemetryData tdata = new TelemetryData()
{
DateTime = DateTime.Now, DeviceId = msg.DeviceId, KeyName = kp.Key, Value_DateTime = new DateTime(1970, 1, 1)
};
tdata.FillKVToMe(kp);
string _type = "";
string _value = "";
// value_boolean bool, value_string binary(4096), value_long bigint,value_datetime timestamp,value_double double,value_json binary(4096) ,value_xml binary
switch (tdata.Type)
{
case DataType.Boolean:
_type = "value_boolean";
_value = tdata.Value_Boolean.ToString().ToLower();
break;
case DataType.String:
_type = "value_string";
_value = $"'{tdata.Value_String?.Replace("'", "\\'")}'";
break;
case DataType.Long:
_type = "value_long";
_value = $"{tdata.Value_Long}";
break;
case DataType.Double:
_type = "value_double";
_value = $"{tdata.Value_Double}";
break;
case DataType.Json: //td 一条记录16kb , 因此为了写更多数据， 我们json xml binary 全部使用 string
_type = "value_string";
_value = $"'{tdata.Value_Json?.Replace("'", "\\'")}'";
break;
case DataType.XML:
_type = "value_string";
_value = $"'{tdata.Value_XML?.Replace("'", "\\'")}'";
break;
case DataType.Binary:
_type = "value_string";
_value = $"\"{Hex.ToHexString(tdata.Value_Binary)}\"";
break;
case DataType.DateTime:
_type = "value_datetime";
_value = $"{tdata.Value_DateTime.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds}";
break;
default:
break;
}
string vals = $"device_{tdata.DeviceId:N}_{ Pinyin4Net.GetPinyin(tdata.KeyName, PinyinFormat.WITHOUT_TONE).Replace(" ", string.Empty).Replace("@", string.Empty)} USING telemetrydata TAGS('{tdata.DeviceId:N}','{tdata.KeyName}') (ts,value_type,{_type}) values (now,{(int)tdata.Type},{_value})";
lst.Add(vals);
}
});
TaosConnection _taos = _taospool.Get();
if (_taos.State != System.Data.ConnectionState.Open)
{
_taos.Open();
}
var cmd = _taos.CreateCommand($"INSERT INTO {string.Join("\r\n", lst)}");
_logger.LogInformation(cmd.CommandText);
int dt = await cmd.ExecuteNonQueryAsync();
_taospool.Return(_taos);
_logger.LogInformation($"数据入库完成,共数据{lst.Count}条，写入{dt}条");
}
catch (TaosException ex)
{
_logger.LogError(ex, $"{msg.DeviceId}数据处理失败{ex.ErrorCode}-{ex.Message} {ex.InnerException?.Message}");
}
catch (Exception ex)
{
_logger.LogError(ex, $"{msg.DeviceId}数据处理失败{ex.Message} {ex.InnerException?.Message} ");
}
return(result);
}
static void Main(string[] args)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
DbProviderFactories.RegisterFactory("TDengine", TaosFactory.Instance);
///Specify the name of the database
string database = "db_" + DateTime.Now.ToString("yyyyMMddHHmmss");
var builder = new TaosConnectionStringBuilder()
{
DataSource = "taos",
DataBase = database,
Username = "******",
Password = "******",
Port = 6030
};
//Example for ADO.Net
using (var connection = new TaosConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("ServerVersion:{0}", connection.ServerVersion);
Console.WriteLine("create {0} {1}", database, connection.CreateCommand($"create database {database};").ExecuteNonQuery());
Console.WriteLine("create table t {0} {1}", database, connection.CreateCommand($"create table {database}.t (ts timestamp, cdata int);").ExecuteNonQuery());
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 10);").ExecuteNonQuery());
var pmcmd = connection.CreateCommand($"insert into {database}.t values (@t, @c);");
Thread.Sleep(TimeSpan.FromSeconds(1));
pmcmd.Parameters.AddWithValue("@t", (long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds));
pmcmd.Parameters.AddWithValue("@c", 1111);
pmcmd.ExecuteNonQuery();
Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 10);").ExecuteNonQuery());
//Console.WriteLine("insert into t values {0} ", connection.CreateCommand($"insert into {database}.t values ({(long)(DateTime.Now.AddMonths(1).Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalMilliseconds)}, 20);").ExecuteNonQuery());
var cmd_select = connection.CreateCommand();
cmd_select.CommandText = $"select * from {database}.t;";
var reader = cmd_select.ExecuteReader();
int index = reader.GetOrdinal("cdata");
Console.WriteLine($"cdata index at {index}");
Console.WriteLine(cmd_select.CommandText);
Console.WriteLine("");
ConsoleTableBuilder.From(reader.ToDataTable()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
Console.WriteLine("");
connection.CreateCommand($"CREATE TABLE datas ('reportTime' timestamp, type int, 'bufferedEnd' bool, address nchar(64), parameter nchar(64), value nchar(64)) TAGS ('boxCode' nchar(64), 'machineId' int);").ExecuteNonQuery();
connection.CreateCommand($"INSERT INTO data_history_67 USING datas TAGS (mongo, 67) values ( 1608173534840 2 false 'Channel1.窑.烟囱温度' '烟囱温度' '122.00' );").ExecuteNonQuery();
var cmd_datas = connection.CreateCommand();
cmd_datas.CommandText = $"SELECT reportTime,type,bufferedEnd,address,parameter,value FROM {database}.data_history_67 LIMIT 100";
var readerdatas = cmd_datas.ExecuteReader();
Console.WriteLine(cmd_datas.CommandText);
Console.WriteLine("");
ConsoleTableBuilder.From(readerdatas.ToDataTable()).WithFormat(ConsoleTableBuilderFormat.Default).ExportAndWriteLine();
Console.WriteLine("");
Console.WriteLine("CREATE TABLE meters ", connection.CreateCommand($"CREATE TABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location binary(64), groupdId int);").ExecuteNonQuery());
Console.WriteLine("CREATE TABLE d1001 ", connection.CreateCommand($"CREATE TABLE d1001 USING meters TAGS (\"Beijing.Chaoyang\", 2);").ExecuteNonQuery());
Console.WriteLine("INSERT INTO d1001 ", connection.CreateCommand($"INSERT INTO d1001 USING METERS TAGS(\"Beijng.Chaoyang\", 2) VALUES(now, 10.2, 219, 0.32);").ExecuteNonQuery());
Console.WriteLine("DROP TABLE {0} {1}", database, connection.CreateCommand($"DROP TABLE {database}.t;").ExecuteNonQuery());
Console.WriteLine("DROP DATABASE {0} {1}", database, connection.CreateCommand($"DROP DATABASE {database};").ExecuteNonQuery());
connection.CreateCommand("DROP DATABASE IF EXISTS IoTSharp").ExecuteNonQuery();
connection.CreateCommand("CREATE DATABASE IoTSharp KEEP 365 DAYS 10 BLOCKS 4;").ExecuteNonQuery();
connection.ChangeDatabase("IoTSharp");
connection.CreateCommand("CREATE STABLE IF NOT EXISTS telemetrydata (ts timestamp,value_type tinyint, value_boolean bool, value_string binary(10240), value_long bigint,value_datetime timestamp,value_double double) TAGS (deviceid binary(32),keyname binary(64));").ExecuteNonQuery();
//connection.CreateCommand($"CREATE TABLE dev_Thermometer USING telemetrydata TAGS (\"Temperature\")").ExecuteNonQuery();
var devid = $"{Guid.NewGuid():N}";
UploadTelemetryData(connection, devid, "Temperature", 999);
UploadTelemetryData(connection, devid, "Humidity", 888);
var devid2 = $"{Guid.NewGuid():N}";
UploadTelemetryData(connection, devid2, "Temperature", 777);
UploadTelemetryData(connection, devid2, "Humidity", 666);
var reader2 = connection.CreateCommand("select last_row(*) from telemetrydata group by deviceid,keyname ;").ExecuteReader();
ConsoleTableBuilder.From(reader2.ToDataTable()).WithFormat(ConsoleTableBuilderFormat.Default).ExportAndWriteLine();
Console.WriteLine("DROP DATABASE IoTSharp", database, connection.CreateCommand($"DROP DATABASE IoTSharp;").ExecuteNonQuery());
connection.Close();
}
//Example for Entity Framework Core
using (var context = new TaosContext(new DbContextOptionsBuilder()
.UseTaos(builder.ConnectionString).Options))
{
Console.WriteLine("EnsureCreated");
context.Database.EnsureCreated();
for (int i = 0; i < 10; i++)
{
var rd = new Random();
context.sensor.Add(new sensor()
{
ts = DateTime.Now.AddMilliseconds(i + 10), degree = rd.NextDouble(), pm25 = rd.Next(0, 1000)
});
Thread.Sleep(10);
}
Console.WriteLine("Saveing");
context.SaveChanges();
Console.WriteLine("");
Console.WriteLine("from s in context.sensor where s.pm25 > 0 select s ");
Console.WriteLine("");
var f = from s in context.sensor where s.pm25 > 0 select s;
var ary = f.ToArray();
if (ary.Any())
{
ConsoleTableBuilder.From(ary.ToList()).WithFormat(ConsoleTableBuilderFormat.MarkDown).ExportAndWriteLine();
}
context.Database.EnsureDeleted();
}
}