Connect with .NET and .NET Core

The SingleStore Connector for .NET and .NET Core is an ADO.NET data provider for SingleStore. It implements the following classes: DbConnection, DbCommand, DbDataReader, and DbTransaction. See the GitHub repository and SingleStoreConnector on NuGet for more information.

This library is licensed under the MIT license.

Prerequisites

Download and install the latest stable version of .NET Core.

Install SingleStoreConnector

To install the SingleStoreConnector in a new project, execute the following command:

dotnet add package SingleStoreConnector

You can also install the SingleStoreConnector using the NuGet Package Manager in Visual Studio. See SingleStoreConnector on NuGet for more information.

Configure the Connection

You need a connection string to connect your SingleStore cluster to .NET. The connection string uses the following format:

host=<hostname_or_ip_address>;port=<port>;userid=<username>;password=<password>;database=<database_name>;

Here's a sample connection string:

host=localhost;port=3306;userid=s2_sally;password=pass23key;database=s2_dbtest;

See Connection String Options for more options.

Example

The following example creates a new project in Visual Studio Code and performs CRUD operations in a SingleStore cluster using C#.

Create a SingleStore cluster. For this example, we'll use the following connection string:

"Server=localhost;port=3306;User ID=s2user;Password=tK_,mh&Hq-EnN;Database=dbtest"

In Visual Studio Code, open the terminal, and create a project template:

dotnet new console -o dbTestNet
cd dbTestNet

Install the required dependencies, for example SingleStoreConnector:

dotnet add package SingleStoreConnector

Create Operation Example

Add the following code to the Program.cs file of your project:

using SingleStoreConnector;
var connStr = "Server=localhost;port=3306;User ID=s2user;Password=tK_,mh&Hq-EnN;Database=dbtest";
var connection = new SingleStoreConnection(connStr);
connection.Open();
using var command = new SingleStoreCommand("CREATE TABLE testID (ID INT PRIMARY KEY, Code VARCHAR(4));INSERT INTO testID values(1, 'SamK');INSERT INTO testID values(2, 'JoeR');INSERT INTO testID values(3, 'BriA');",connection);
using var reader = command.ExecuteReader();
connection.Close();

In the terminal, run the following command:

dotnet run

On the SingleStore command line, execute the following command to verify that the testID table is created:

DESC testID;
+-------+------------+------+------+---------+-------+
| Field | Type       | Null | Key  | Default | Extra |
+-------+------------+------+------+---------+-------+
| id    | int(11)    | NO   | UNI  | NULL    |       |
| code  | varchar(4) | YES  |      | NULL    |       |
+-------+------------+------+------+---------+-------+

Read Operation Example

Add the following code to the Program.cs file of your project:

using SingleStoreConnector;
var connStr = "Server=localhost;port=3306;User ID=s2user;Password=tK_,mh&Hq-EnN;Database=dbtest";
var connection = new SingleStoreConnection(connStr);
connection.Open();
using var command = new SingleStoreCommand("SELECT * FROM testID",connection);
using var reader = command.ExecuteReader();
while (reader.Read())
{
Console.Write(reader.GetInt32(0));
Console.Write(" ");
Console.WriteLine(reader.GetString(1));
}
connection.Close();

In the terminal, run the following command:

dotnet run
1 SamK
2 JoeR
3 BriA

Update Operation Example

Add the following code to the Program.cs file of your project:

using SingleStoreConnector;
var connStr = "Server=localhost;port=3306;User ID=s2user;Password=tK_,mh&Hq-EnN;Database=dbtest";
var connection = new SingleStoreConnection(connStr);
connection.Open();
using var command = new SingleStoreCommand("UPDATE testID SET Code = 'BenW' WHERE ID =3;SELECT * FROM testID",connection);
using var reader = command.ExecuteReader();
while (reader.Read())
{
Console.Write(reader.GetInt32(0));
Console.Write(" ");
Console.WriteLine(reader.GetString(1));
}
connection.Close();

In the terminal, run the following command:

dotnet run
1 SamK
2 JoeR
3 BenW

Delete Operation Example

Add the following code to the Program.cs file of your project:

using SingleStoreConnector;
var connStr = "Server=localhost;port=3306;User ID=s2user;Password=tK_,mh&Hq-EnN;Database=dbtest";
var connection = new SingleStoreConnection(connStr);
connection.Open();
using var command = new SingleStoreCommand("DELETE FROM testID WHERE ID = 3;SELECT * FROM testID;",connection);
using var reader = command.ExecuteReader();
while (reader.Read())
{
Console.Write(reader.GetInt32(0));
Console.Write(" ");
Console.WriteLine(reader.GetString(1));
}
connection.Close();

In the terminal, run the following command:

dotnet run
1 SamK
2 JoeR

In this section

Last modified: April 24, 2023

Was this article helpful?