public override bool Open(bool errorsExpected = false) { bool result = false; _connection.ConnectionStringBuilder.ForceDatabaseName = true; _connection.Open(); if (_connection.ConnectionStringBuilder.ForceDatabaseName) { var obj = _connection.CreateCommand("SELECT database()").ExecuteScalar(); if (obj == DBNull.Value) { try { _connection.ChangeDatabase(_connection.Database); } catch (Exception ex) { Debug.WriteLine(ex.Message); } } } result = _connection.State == System.Data.ConnectionState.Open; return(result); }
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); }
public override bool Open(bool errorsExpected = false) { _connection.Open(); _connection._nowdatabase = string.Empty; bool result = _connection.State == System.Data.ConnectionState.Open; _connection.ChangeDatabase(_connection.Database); return(result); }
public Task <List <TelemetryDataDto> > GetTelemetryLatest(Guid deviceId) { TaosConnection _taos = _taospool.Get(); _taos.ChangeDatabase(_taos.Database); if (_taos.State != System.Data.ConnectionState.Open) { _taos.Open(); } //https://github.com/taosdata/TDengine/issues/4269 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)); }
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(); } }
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(); } }