C# ADO.NET
last modified July 5, 2023
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.
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.
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.
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.
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.
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
Source
ADO.NET framework documentation
In this article we have worked with ADO.NET.
Author
List all C# tutorials.
