Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 36 ยท Files and Databases

ADO.NET and DataTables

Learn ADO.NET in VB.NET - connections, commands, parameters, DataReader, DataAdapter, DataTable, DataView, DataSet and relations. Real provider code is read-only, DataTable stand-ins run live.

ADO.NET is the layer of .NET that talks to relational databases. It is built from a small set of classes that every provider implements: a connection to the server, a command that carries SQL, a data reader that streams rows, and in-memory containers (DataTable, DataSet) that hold results offline. Whether the database is SQL Server, SQLite, PostgreSQL or MySQL, the shape of the code is the same.

Honest limits. No database server or provider package (such as Microsoft.Data.SqlClient or Microsoft.Data.Sqlite) is available on this site, so connection and command code is read-only and no query results are claimed. The in-memory classes DataTable, DataView and DataSet ship with .NET itself, so those parts run for real as C# stand-ins with the same API as in VB.

The provider classes

Role SQL Server SQLite Generic base
Connection SqlConnection SqliteConnection DbConnection
Command SqlCommand SqliteCommand DbCommand
Reader SqlDataReader SqliteDataReader DbDataReader
Adapter SqlDataAdapter (not provided) DbDataAdapter

Install the provider as a NuGet package (Microsoft.Data.SqlClient, Microsoft.Data.Sqlite), and import its namespace.

Reading rows with a DataReader

VB
Imports Microsoft.Data.SqlClient

Dim connStr = "Server=.;Database=Shop;Integrated Security=True;TrustServerCertificate=True"

Using conn As New SqlConnection(connStr)
    conn.Open()
    Using cmd As New SqlCommand("SELECT Id, Name, Price FROM Products WHERE Price > @min", conn)
        cmd.Parameters.AddWithValue("@min", 10D)
        Using reader = cmd.ExecuteReader()
            While reader.Read()
                Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)}")
            End While
        End Using
    End Using
End Using

A DataReader is forward-only and connected: it streams one row at a time while the connection stays open, which is the lightest way to read.

Always use parameters

Never build SQL by joining strings with user input. The text x' OR '1'='1 turns a lookup into "return everything", and worse input can delete tables. Parameters send the value separately from the command text so it can never be interpreted as SQL. The block below does not touch a database; it just shows why concatenation is dangerous.

C# Why string-built SQL is dangerous (no database involved)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Writing data

VB
Using cmd As New SqlCommand("INSERT INTO Products (Name, Price) VALUES (@n, @p)", conn)
    cmd.Parameters.Add("@n", SqlDbType.NVarChar, 100).Value = "Pen"
    cmd.Parameters.Add("@p", SqlDbType.Decimal).Value = 2.5D
    Dim rows As Integer = cmd.ExecuteNonQuery()       ' INSERT / UPDATE / DELETE
End Using

Dim count = CInt(New SqlCommand("SELECT COUNT(*) FROM Products", conn).ExecuteScalar())

ExecuteNonQuery returns the number of rows affected, ExecuteScalar returns the first column of the first row, ExecuteReader returns a stream of rows. Group several statements in a transaction (conn.BeginTransaction(), then Commit or Rollback) so they succeed or fail together.

DataTable: a table in memory

DataTable holds rows and typed columns without any database. A DataAdapter.Fill(table) loads a query result into one and adapter.Update(table) saves the changes back, but a DataTable is useful all by itself, and it binds directly to a DataGridView.

VB
Dim t As New DataTable("Products")
t.Columns.Add("Id", GetType(Integer))
t.Columns.Add("Name", GetType(String))
t.Columns.Add("Price", GetType(Decimal))
t.Rows.Add(1, "Pen", 2.5D)
t.Rows.Add(2, "Stapler", 12D)
grid.DataSource = t
C# DataTable: add, select, compute, find

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

DataView, DataSet and relations

A DataView is a sorted or filtered window onto a table (the grid shows the view, the table stays intact). A DataSet is a collection of tables plus DataRelation objects that mimic foreign keys, so you can walk from a parent row to its child rows.

C# DataView and a DataRelation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Connected versus disconnected

flowchart LR
    DB[("Database")] -->|"ExecuteReader (stream)"| R["DataReader: one row at a time, connection open"]
    DB -->|"DataAdapter.Fill"| T["DataTable / DataSet in memory, connection closed"]
    T -->|"adapter.Update"| DB

Use a reader for fast, forward-only reads; use a DataTable when you need to edit, sort or bind offline.

For larger applications, many teams replace hand-written ADO.NET with an object-relational mapper, covered in the next lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use parameters instead of concatenating user input into SQL?

    Parameters are sent separately from the command text and cannot change the statement.
  2. 2. Which Execute method returns the number of affected rows?

    ExecuteNonQuery is for INSERT, UPDATE and DELETE and returns the row count.
  3. 3. What is a DataReader?

    It reads one row at a time while the connection stays open.
  4. 4. What does a DataRelation provide?

    It models a foreign-key relationship, with methods like GetChildRows.

Summary

  • ADO.NET's core types: connection, command, reader, adapter; the provider supplies the concrete classes.
  • Always pass values as parameters; wrap connections and commands in Using.
  • ExecuteNonQuery, ExecuteScalar and ExecuteReader fit writes, single values and row streams.
  • DataTable, DataView and DataSet hold and navigate data in memory, and bind to grids.

Next: Database Access Patterns