Quick Start: .NET and TimescaleDB

This quick start guide is designed to get the .NET developer up and running with TimescaleDB as their database.

In this Quick Start, you need to:

  1. Connect .NET to Timescale
  2. Create a relational table
  3. Create a hypertable
  4. Insert time-series data into TimescaleDB
  5. Query TimescaleDB

You build the application one step at a time, adding new methods to the TimescaleHelper class which are called from the Main method of the application. When you have finished, the application code provides a brief template for further experimentation as you learn more about TimescaleDB and .NET with Npgsql.

Prerequisites

Before you begin this Quick Start, make sure you have:

  • At least some knowledge of SQL (structured query language). The tutorial walks you through each SQL command, but it is helpful if you’ve seen SQL before.
  • The latest compatible .NET runtime installed and accessible
  • TimescaleDB installed, either in a self-hosted environment or in the cloud.
  • A PostgreSQL query tool like psql or any other PostgreSQL client (for example, DBeaver). You need this to explore the final TimescaleDB database.

Connect .NET to TimescaleDB

To connect a .NET application to TimescaleDB, you need to initiate the project using the dotnet CLI and install the Npgsql SDK for connecting to TimescaleDB. Npgsql is the officially supported PostgreSQL client library for .NET.

To complete this procedure, you need to have your database connection information available, including:

  • username
  • password
  • host URL
  • port
  • database name
important

Npgsql does not support the shorter URI-based form of a PostgreSQL connection string. Instead, the connection string must be in a supported key/value format as described in their documentation. Please see the documentation for additional parameters to enable features such as SSL.

Connecting .NET to TimescaleDB

  1. Create a working directory for the tutorial application and initiate a new application project using the “console” template included with the .NET tooling:

    1. mkdir dotnet-tutorial
    3. cd dotnet-tutorial
    5. dotnet new console

  2. Add the Npgsql package to your project which is used to connect to TimescaleDB:

    1. dotnet add package Npgsql

  3. Open the Program.cs file in your new project and replace the contents with this C# code:

    1. using System;
    3. using Npgsql;
    5. using System.Collections.Generic;
    7. namespace com.timescale.docs
    9. {
    11.     class Program
    13.     {
    15.         //
    17.         // This is the main method that is called
    19.         // by default when .NET builds this small application
    21.         static void Main(string[] args)
    23.         {
    25.             // Create a new instance of our helper class. This class
    27.             // contains all of the methods for interacting with
    29.             // TimescaleDB for this tutorial
    31.             TimescaleHelper ts = new TimescaleHelper();
    33.             // Procedure - Connecting .NET to TimescaleDB:
    35.             // Verify that the program can connect
    37.             // to the database and that TimescaleDB is installed!
    39.             ts.CheckDatabaseConnection();
    41.         }
    43.     }
    45.     // This class contains all of the methods needed to complete the
    47.     // quick-start, providing a sample of each database operation in total
    49.     // to refer to later.
    51.     public class TimescaleHelper
    53.     {
    55.         private static string Host = "";
    57.         private static string User = "";
    59.         private static string DBname = "";
    61.         private static string Password = "";
    63.         private static string Port = "";
    65.         private static string conn_str = "";
    67.         //
    69.         // This is the constructor for our TimescaleHelper class
    71.         //
    73.         public TimescaleHelper(string host="<HOSTNAME>", string user="<USERNAME>",
    75.             string dbname="<DATABASE_NAME>", string password="<PASSWORD>",string port="<PORT>")
    77.         {
    79.             Host=host;
    81.             User=user;
    83.             DBname=dbname;
    85.             Password=password;
    87.             Port=port;
    89.             // Build connection string using the parameters above
    91.             conn_str = String.Format("Server={0};Username={1};Database={2};Port={3};Password={4};SSLMode=Prefer",
    93.                                 Host,
    95.                                 User,
    97.                                 DBname,
    99.                                 Port,
    101.                                 Password);
    103.         }
    105.         // Helper method to get a connection for the execute function
    107.         NpgsqlConnection getConnection()
    109.         {
    111.             var Connection = new NpgsqlConnection(conn_str);
    113.             Connection.Open();
    115.             return Connection;
    117.         }
    119.         //
    121.         // Procedure - Connecting .NET to TimescaleDB:
    123.         // Check the connection TimescaleDB and verify that the extension
    125.         // is installed in this database
    127.         //
    129.         public void CheckDatabaseConnection() {
    131.             // get one connection for all SQL commands below
    133.             using (var conn = getConnection()){
    135.                 var sql = "SELECT default_version, comment FROM pg_available_extensions WHERE name = 'timescaledb';";
    137.                 using(var cmd = new NpgsqlCommand(sql, conn)) {
    139.                     using NpgsqlDataReader rdr = cmd.ExecuteReader();
    141.                     if (!rdr.HasRows) {
    143.                         Console.WriteLine("Missing TimescaleDB extension!");
    145.                         conn.Close();
    147.                         return;
    149.                     }
    151.                     while (rdr.Read())
    153.                     {
    155.                         Console.WriteLine("TimescaleDB Default Version: {0}\n{1}", rdr.GetString(0), rdr.GetString(1));
    157.                     }
    159.                     conn.Close();
    161.                 }
    163.             }
    165.         }
    167. }
    169. }
warning

This method of composing a connection string is for test or development purposes only. For production applications be sure to make sensitive details like your password, hostname, and port number accessible in a secure way as supported by .NET.

  1. Run Program.cs to verify that .NET can connect to your database and that the TimescaleDB extension is installed:

    1. dotnet run Program.cs
    3. TimescaleDB Default Version: 2.3.0
    5. Enables scalable inserts and complex queries for time-series data

If you don’t see the extension, check our troubleshooting section.

Create a relational table

When the application can successfully connect to TimescaleDB, you can create some relational data that your time-series data can reference when creating data and executing queries.

The new functionality to create the table and insert data is added as a method to the TimescaleHelper class and called from the Main method of the program.

Creating a relational table

  1. Add this method at the bottom of the TimescaleHelper class, below the CheckDatabaseConnection() method:

    1. //
    3.         // Procedure - Creating a relational table:
    5.         // Create a table for basic relational data and
    7.         // populate it with a few fake sensors
    9.         //
    11.         public void CreateRelationalData() {
    13.             //use one connection to use for all three commands below.
    15.             using (var conn = getConnection())
    17.             {
    19.                 using (var command = new NpgsqlCommand("DROP TABLE IF EXISTS sensors cascade", conn))
    21.                 {
    23.                     command.ExecuteNonQuery();
    25.                     Console.Out.WriteLine("Finished dropping table (if existed)");
    27.                 }
    29.                 using (var command = new NpgsqlCommand("CREATE TABLE sensors (id SERIAL PRIMARY KEY, type TEXT, location TEXT);", conn))
    31.                 {
    33.                     command.ExecuteNonQuery();
    35.                     Console.Out.WriteLine("Finished creating the sensors table");
    37.                 }
    39.                 // Create the list of sensors as key/value pairs to insert next
    41.                 var sensors = new List<KeyValuePair<string, string>>()
    43.                 {
    45.                     new KeyValuePair<string, string>("a","floor"),
    47.                     new KeyValuePair<string, string>("a","ceiling"),
    49.                     new KeyValuePair<string, string>("b","floor"),
    51.                     new KeyValuePair<string, string>("b","ceiling")
    53.                 };
    55.                 // Iterate over the list to insert it into the newly
    57.                 // created relational table using parameter substitution
    59.                 foreach(KeyValuePair<string,string> kvp in sensors)
    61.                 {
    63.                     using (var command = new NpgsqlCommand("INSERT INTO sensors (type, location) VALUES (@type, @location)", conn))
    65.                     {
    67.                         command.Parameters.AddWithValue("type", kvp.Key);
    69.                         command.Parameters.AddWithValue("location", kvp.Value);
    71.                         int nRows = command.ExecuteNonQuery();
    73.                         Console.Out.WriteLine(String.Format("Number of rows inserted={0}", nRows));
    75.                     }
    77.                 }
    79.             }
    81.         }

  2. Call this new method from the Main program after ts.CheckDatabaseConnection();:

    1. // Procedure - Creating a relational table
    3.             ts.CreateRelationalData();

  3. Save and run the application. You should see output like this:

    1. $ dotnet run
    3. TimescaleDB Default Version: 2.3.0
    5. Enables scalable inserts and complex queries for time-series data
    7. Finished dropping table (if existed)
    9. Finished creating the sensors table
    11. Number of rows inserted=1
    13. Number of rows inserted=1
    15. Number of rows inserted=1
    17. Number of rows inserted=1

Create the hypertable

When the relational table is created and populated, you can create a hypertable, the core features of TimescaleDB that most functionality relies on. A hypertable is first created as a regular PostgreSQL table with a date or timestamp column, and then converted into a hypertable with the create_hypertable() API.

A hypertable is the core architecture that many other TimescaleDB features is built upon, such as Compression, Continuous Aggregates, and Data Retention. Learn more about hypertables and other key features of TimescaleDB in our Core Concepts documentation.

Creating a hypertable

  1. Add a new method to the bottom of the TimescaleHelper class that creates a new table and convert it to a hypertable:

    1. //
    3.         // Procedure - Creating a hypertable:
    5.         // Create a new table to store time-series data and create
    7.         // a new TimescaleDB hypertable using the new table. It is
    9.         // partitioned on the 'time' column
    11.         public void CreateHypertable(){
    13.             //use one connection to use for all three commands below.
    15.             using (var conn = getConnection())
    17.             {
    19.                 using (var command = new NpgsqlCommand("DROP TABLE IF EXISTS sensor_data CASCADE;", conn))
    21.                 {
    23.                     command.ExecuteNonQuery();
    25.                     Console.Out.WriteLine("Dropped sensor_data table if it existed");
    27.                 }
    29.                 using (var command = new NpgsqlCommand(@"CREATE TABLE sensor_data (
    31.                                            time TIMESTAMPTZ NOT NULL,
    33.                                            sensor_id INTEGER,
    35.                                            temperature DOUBLE PRECISION,
    37.                                            cpu DOUBLE PRECISION,
    39.                                            FOREIGN KEY (sensor_id) REFERENCES sensors (id)
    41.                                            );", conn))
    43.                 {
    45.                     command.ExecuteNonQuery();
    47.                     Console.Out.WriteLine("Created sensor_data table to store time-series data");
    49.                 }
    51.                 using (var command = new NpgsqlCommand("SELECT create_hypertable('sensor_data', 'time');", conn))
    53.                 {
    55.                     command.ExecuteNonQuery();
    57.                     Console.Out.WriteLine("Converted the sensor_data table into a TimescaleDB hypertable!");
    59.                 }
    61.             }
    63.         }

  2. Call this new method from the Main program after ts.CreateRelationalData():

    1. // Procedure - Creating a hypertable
    3.             // Create a new table and make it a hypertable to store
    5.             // the generated time-series data
    7.             ts.CreateHypertable();

  3. Run the application again. It drops and recreates the sensors table, and then creates a new hypertable called sensor_data:

    1. $ dotnet run

    ```

TimescaleDB Default Version: 2.3.0

Enables scalable inserts and complex queries for time-series data

Finished dropping table (if existed)

Finished creating the sensors table

Number of rows inserted=1

Number of rows inserted=1

Number of rows inserted=1

Number of rows inserted=1

Dropped sensor_data table if it existed

Created sensor_data table to store time-series data

Converted the sensor_data table into a TimescaleDB hypertable!

  2.  `
  1. `

Your application is now ready to insert time-series data into the newly created TimescaleDB hypertable!

Insert time-series data into TimescaleDB

Your Timescale database has all of the components necessary to start creating and inserting time-series data. In this Quick Start, we use a built-in PostgreSQL function, generate_series(), to create a small set of row data with generated timestamps that mimics time-series data. For more information on how generate_series() works and ideas for creating more complex and diverse sample datasets, watch this YouTube playlist and our corresponding Blog series.

Inserting time-series data into TimescaleDB

  1. Add this new method to the bottom of the TimescaleHelper class. This code executes a SQL statement that uses generate_series() to insert 1 day of sample data into the sensor_data hypertable:

    1. //
    3.         // Procedure - Insert time-series data:
    5.         // With the hypertable in place, insert data using the PostgreSQL
    7.         // supplied 'generate_series()' function, iterating over our small list
    9.         // of sensors from Step 2.
    11.         public void InsertData()
    13.         {
    15.             using (var conn = getConnection())
    17.             {
    19.                 // This query creates one row of data every minute for each
    21.                 // sensor_id, for the last 24 hours ~= 1440 readings per sensor
    23.                 var sql = @"INSERT INTO sensor_data
    25.                             SELECT generate_series(now() - interval '24 hour',
    27.                                                     now(),
    29.                                                     interval '1 minute') AS time,
    31.                             @sid as sensor_id,
    33.                             random()*100 AS temperature,
    35.                             random() AS cpu";
    37.                 // We created four sensors in Step 2 and so we iterate over their
    39.                 // auto generated IDs to insert data. This could be modified
    41.                 // using a larger list or updating the SQL to JOIN on the 'sensors'
    43.                 // table to get the IDs for data creation.
    45.                 for(int i=1; i<=4; i++)
    47.                 {
    49.                     using (var command = new NpgsqlCommand(sql, conn))
    51.                         {
    53.                             command.Parameters.AddWithValue("sid", i);
    55.                             int nRows = command.ExecuteNonQuery();
    57.                             Console.Out.WriteLine(String.Format("Number of rows inserted={0}", nRows));
    59.                         }
    61.                 }
    63.             }
    65.         }

  2. Call this method from the Main program after the ts.CreateHypertable(); reference:

    1. // Procedure - Insert time-series data
    3.             // Insert time-series data using the built-in
    5.             // PostgreSQL function generate_series()
    7.             ts.InsertData();

  3. Save and run the program. If you have not commented out any method calls in the Main program, the application drops all tables, recreates them, and inserts the new sample time-series data. Your output should look similar to this:

    1. $ dotnet run
    3. TimescaleDB Default Version: 2.3.0
    5. Enables scalable inserts and complex queries for time-series data
    7. Finished dropping table (if existed)
    9. Finished creating the sensors table
    11. Number of rows inserted=1
    13. Number of rows inserted=1
    15. Number of rows inserted=1
    17. Number of rows inserted=1
    19. Dropped sensor_data table if it existed
    21. Created sensor_data table to store time-series data
    23. Converted the sensor_data table into a TimescaleDB hypertable!
    25. Number of rows inserted=1441
    27. Number of rows inserted=1441
    29. Number of rows inserted=1441
    31. Number of rows inserted=1441

Congratulations! You have successfully created and inserted time-series data into your hypertable. The last step to is to execute your first time_bucket() query against the hypertable data.

Query TimescaleDB

The final step of this Quick Start is to use the time_bucket function to analyze the average CPU reading in five minute buckets. As written, this query returns all data for all sensors. However, you could adjust the query to receive inputs that further filter data using a WHERE clause.

After executing the query, iterate the results using the NpgsqlDataReader and print the results to the console.

Querying TimescaleDB

  1. Create the final method at the bottom of the TimescaleHelper class for querying time-series data using the time_bucket() function provided by TimescaleDB:

    1. //
    3.         // Procedure - Query TimescaleDB
    5.         // With time-series data inserted, run a 'time_bucket()' query
    7.         // on the data in order to aggregate our 1-minute cpu data into buckets
    9.         // of 5-minute averages.
    11.         public void RunQueryExample(){
    13.             string sql = @"
    15.                 SELECT sensor_id, time_bucket('5 minutes', time) AS five_min, avg(cpu)
    17.                 FROM sensor_data
    19.                     INNER JOIN sensors ON sensors.id = sensor_data.sensor_id
    21.                 GROUP BY sensor_id, five_min
    23.                 ORDER BY sensor_id, five_min DESC;";
    25.             var conn = getConnection();
    27.             using(var cmd = new NpgsqlCommand(sql, conn))
    29.             {
    31.                 using(NpgsqlDataReader rdr = cmd.ExecuteReader()) {
    33.                     while (rdr.Read()) Console.WriteLine($"{rdr.GetDouble(0)} - {rdr.GetTimeStamp(1)} - {rdr.GetDouble(2)}");
    35.                 }
    37.             }
    39.             conn.Close();
    41.         }

  2. Call this method in the Main program directly after the ts.InsertData(); reference:

    1. // Procedure - Query TimescaleDB
    3.             // Query the data using the Timescale time_bucket() function
    5.             ts.RunQueryExample();

  3. Save and run the application again. As before, if you execute all of the methods in the Main program, your output should look similar to this. The values of the output are different because we used the random() function to generate them:

    1. $ dotnet run
    3. TimescaleDB Default Version: 2.3.0
    5. Enables scalable inserts and complex queries for time-series data
    7. Finished dropping table (if existed)
    9. Finished creating the sensors table
    11. Number of rows inserted=1
    13. Number of rows inserted=1
    15. Number of rows inserted=1
    17. Number of rows inserted=1
    19. Dropped sensor_data table if it existed
    21. Created sensor_data table to store time-series data
    23. Converted the sensor_data table into a TimescaleDB hypertable!
    25. Number of rows inserted=1441
    27. Number of rows inserted=1441
    29. Number of rows inserted=1441
    31. Number of rows inserted=1441
    33. 1 - 2021-09-16 02:40:00 - 0.4404128644957318
    35. 1 - 2021-09-16 02:35:00 - 0.4102604181563983
    37. 1 - 2021-09-16 02:30:00 - 0.6163615623169129
    39. 1 - 2021-09-16 02:25:00 - 0.6738216869256242
    41. 1 - 2021-09-16 02:20:00 - 0.5920387434668661
    43. 1 - 2021-09-16 02:15:00 - 0.3357799954217377
    45. 1 - 2021-09-16 02:10:00 - 0.523394970335945
    47. …
    49. 4 - 2021-09-15 14:40:00 - 0.43986085123294955
    51. 4 - 2021-09-15 14:35:00 - 0.4829260601553081
    53. 4 - 2021-09-15 14:30:00 - 0.257577961505887
    55. 4 - 2021-09-15 14:25:00 - 0.3431355979977731
    57. 4 - 2021-09-15 14:20:00 - 0.2339278514661025

Congratulations! You have successfully queried data from TimescaleDB usig the Npgsql SDK. For information on how to execute more complex queries or utilize advanced query functionality, please see the Npgsql documentation.

Next Steps

Now that you’re able to connect, read, and write to a TimescaleDB instance from your .NET application be sure to check out these advanced TimescaleDB tutorials: