ZetCode

C# ADO.NET

last modified June 30, 2022

C# ADO.NET tutorial shows how to do database programming in C# using ADO.NET technology.

ADO.NET

ADO.NET is a set of classes for database access. It is a specification that unifies access to relational databases, XML files, and other application data. The ADO.NET classes are found in System.Data namespace.

Each database must provide an implementation of the ADO.NET specification: MySQL has MySQL.Data, PostgreSQL has Npgsql, and SQLite has System.Data.SQLite or Microsoft.Data.Sqlite.

$ dotnet add package System.Data.SQLite

To work with SQLite database, we need to add the System.Data.SQLite nuget package.

The DataSet object is used for offline work with a mass of data. It is a disconnected data representation that can hold data from a variety of different sources.

ADO.NET SQLite example

In the first example, we work with an SQLite database.

Program.cs
using System.Data.SQLite;

string cs = "Data Source=:memory:";
string stm = "SELECT SQLITE_VERSION()";

using var con = new SQLiteConnection(cs);
con.Open();

using var cmd = new SQLiteCommand(stm, con);
string? version = cmd.ExecuteScalar().ToString();

Console.WriteLine($"SQLite version: {version}");

The example prints the version of SQLite.

string cs = "Data Source=:memory:";

We use an in-memory database.

string stm = "SELECT SQLITE_VERSION()";

This SQL statement determines the version of SQLite.

using var con = new SQLiteConnection(cs);
con.Open();

The SQLiteConnection creates a connection to a specific data source. The parameters are the SQL statement and the connection object.

using var cmd = new SQLiteCommand(stm, con);

The SQLiteCommand object executes an SQL statement against a data source. The using declaration disposes the con variable at the end of the enclosing scope.

string? version = cmd.ExecuteScalar().ToString();

The ExecuteScalar returns the first column of the first row of the resultset (if present), or null if no resultset was returned.

$ dotnet run 
SQLite version: 3.38.5.1

ADO.NET MySQL example

In the second example, we work with MySQL.

Program.cs
using MySql.Data.MySqlClient;

string cs = @"server=localhost;userid=user12;password=s$cret;database=testdb";

using var con = new MySqlConnection(cs);
con.Open();

var stm = "SELECT VERSION()";
var cmd = new MySqlCommand(stm, con);

var version = cmd.ExecuteScalar().ToString();
Console.WriteLine($"MySQL version: {version}");

There are a several changes.

using MySql.Data.MySqlClient;

We use MySQL namespace.

string cs = @"server=localhost;userid=user12;password=s$cret;database=testdb";

The connection strings is suited for MySQL.

using var con = new MySqlConnection(cs);

We use MySqlConnection instead of SQLiteConnection.

var stm = "SELECT VERSION()";

The SQL statement is specific to MySQL.

var cmd = new MySqlCommand(stm, con);

The command is called MySqlCommand.

$ dotnet run
MySQL version: 8.0.29-0ubuntu0.22.04.2

ADO.NET create table

In the following example, we create a database table and fill it with data.

Program.cs
using System.Data.SQLite;

string cs = @"URI=file:test.db";    

using var con = new SQLiteConnection(cs);
con.Open();

using var cmd = new SQLiteCommand(con);

cmd.CommandText = "DROP TABLE IF EXISTS cars";
cmd.ExecuteNonQuery();

cmd.CommandText = @"CREATE TABLE cars(id INTEGER PRIMARY KEY,
            name TEXT, price INT)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Audi',52642)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Mercedes',57127)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Skoda',9000)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Volvo',29000)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Bentley',350000)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Citroen',21000)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Hummer',41400)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Volkswagen',21600)";
cmd.ExecuteNonQuery();

Console.WriteLine("table cars created");

In the example, we create a cars table with eight rows.

cmd.CommandText = "DROP TABLE IF EXISTS cars";
cmd.ExecuteNonQuery();

First we drop the table if it already exists. We use the ExecuteNonQuery method if we do not want a result set, for example for DROP, INSERT, or DELETE statements.

cmd.CommandText = @"CREATE TABLE cars(id INTEGER PRIMARY KEY,
            name TEXT, price INT)";
cmd.ExecuteNonQuery();

The cars table is created. The INTEGER PRIMARY KEY column is auto-incremented in SQLite.

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Audi',52642)";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Mercedes',57127)";
cmd.ExecuteNonQuery();

We insert two rows into the table.

$ dotnet run
table cars created

We run the program.

$ sqlite3 test.db

We open the test.db database with the sqlite3 tool.

sqlite> SELECT * FROM cars;
1|Audi|52642
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600

We verify the data.

ADO.NET read data

In SQLite driver, we use the SQLiteDataReader to fetch data from the database. It is used with the SQLiteCommand class to execute a SELECT statement and then access the returned rows.

Program.cs
using System.Data.SQLite;

string cs = @"URI=file:test.db";

using var con = new SQLiteConnection(cs);
con.Open();

string stm = "SELECT * FROM cars LIMIT 5";

using var cmd = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
}

We get five rows from the cars table and print them to the terminal.

using SQLiteDataReader rdr = cmd.ExecuteReader();

To create an SQLiteDataReader object, we call the ExecuteReader method of the SQLiteCommand object.

while (rdr.Read())
{
    Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
}

The Read method advances the data reader to the next record. It returns true if there are more rows; otherwise false. We can retrieve the value using the array index notation, or use a specific method to access column values in their native data types. The latter is more efficient.

$ dotnet run
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

ADO.NET LINQ example

We can use LINQ in ADO.NET. SQLiteDataAdapter is a bridge between a System.Data.DataSet and a data source.

Program.cs
using System.Data.SQLite;
using System.Data;
string cs = @"URI=file:test.db";

using var ds = new DataSet();
using var con = new SQLiteConnection(cs);
using var sad = new SQLiteDataAdapter("SELECT * FROM cars", con);

sad.Fill(ds);

var res = from dt in ds?.Tables[0]?.AsEnumerable()
          select new
          {
              Id = dt.Field<long>("id"),
              Name = dt.Field<string>("name"),
              Price = dt.Field<int>("price"),
          };

foreach (var e in res)
{
    Console.WriteLine(e);
}

Console.WriteLine(res.Count());

SQLiteDataAdapter is used to fill a DataSet, from which we create an enumerable. A LINQ query is executed on that enumerable.

$ dotnet run
{ Id = 1, Name = Audi, Price = 52642 }
{ Id = 2, Name = Mercedes, Price = 57127 }
{ Id = 3, Name = Skoda, Price = 9000 }
{ Id = 4, Name = Volvo, Price = 29000 }
{ Id = 5, Name = Bentley, Price = 350000 }
{ Id = 6, Name = Citroen, Price = 21000 }
{ Id = 7, Name = Hummer, Price = 41400 }
{ Id = 8, Name = Volkswagen, Price = 21600 }
8

In this article, we have worked with ADO.NET.

List all C# tutorials.