Introduction and Connections

Introduction

ADO.NET is the collective term for database‑related modules under the .NET platform.
It lets us work with databases to perform basic CRUD operations. We can also run SQL statements, invoke SQL functions, and execute stored procedures.

Nearly all large‑scale business systems rely on databases. You will need a database whenever you require persistent data storage together with efficient query capabilities.

ADO.NET exposes a set of core objects that wrap methods for database operations:

• Connection: Establishes a connection to the database
• Command: Executes SQL‑related commands
• DataSet: An in‑memory data set serving as an abstraction of the database. Working with a DataSet is similar to working with an offline database. It contains DataTable (data table) and DataRow (single record inside a data table).
• DataReader: Forward‑only, read‑only data reader
• DataAdapter: Data adapter

A solid grasp of ADO.NET is essential for building fully‑featured business systems. Databases are widely used for websites and other applications, so it is worth putting in the effort to learn it well.

ORM frameworks such as EF, iBatis, NHibernate and Linq to Entities are all built on top of ADO.NET under the hood. You may also build your own custom ORM frameworks using ADO.NET.

Database Connections

The Connection object creates a link between your program and the database, acting as the gateway for all database‑related operations.

.NET provides the SqlConnection class for database connections. It lives in the namespace System.Data.SqlClient and is part of the assembly System.Data.dll.
Most database‑interaction types are encapsulated within this DLL.

Before opening a connection you must prepare a database connection string. Pass this string when instantiating your SqlConnection object.

SqlConnection conn = new SqlConnection(connectionString);Code language: JavaScript (javascript)

A connection string is just an ordinary string stored in a string variable.

Connection String Examples for Two Authentication Modes

1. SA account login (SQL Server authentication)

"Data Source=.;Initial Catalog=DatabaseName;User ID=sa;pwd=Password;"Code language: JSON / JSON with Comments (json)

2. Windows authentication login

Data Source=.;Initial Catalog=DatabaseName;Integrated Security=TrueCode language: PHP (php)

A connection string consists of multiple configuration entries separated by English semicolons following the key=value format.
Parameter breakdown:

• Data Source: Database server address. Use ., localhost or 127.0.0.1 for local databases. Supply an IP address or domain name for remote servers.
• Initial Catalog: Target database name
• User ID: Database login account
• pwd: Database login password

Note: User ID and pwd are not mandatory fields. When using Windows authentication, set Integrated Security=True to enable integrated security and skip supplying account credentials.

Connecting to a Database in C#

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = "Data Source=.;Initial Catalog=db;User ID=sa;pwd=123321;";
    // Instantiate connection inside using block. Resources get released automatically when block exits; manual Close() is unnecessary
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        // Open database connection
        conn.Open();
        // Place your database operation logic here
    }
}Code language: JavaScript (javascript)

Remarks

1. conn.Open(): Opens the database connection. This method must be called to establish the actual network connection.
2. Advantage of the using statement: Resources are automatically disposed and the database connection closes when execution leaves the using‑block scope, preventing connection leaks.
3. If you do not use using, you must manually call conn.Close() to close the connection once work completes.

Other Common Usages

//conn.Open();                // Open connection
//conn.Close();               // Manually close connection
//conn.ConnectionString = connectionString; // Assign connection string dynamically
//conn.ConnectionString = "";              // Clear connection stringCode language: JSON / JSON with Comments (json)

Introduction and Connections