1. ADO.NET Introduction

Introduction to ADO.NET

Overview

ADO.NET stands for ActiveX Data Objects .NET and is a technology for data access. It is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework.

Key Points:

• ADO.NET provides a bridge between the front end controls and back end databases.

• It provides a fast way to connect to databases and perform data transactions.

• It uses data providers to connect and execute commands on the database.

Data Providers

A data provider is a set of libraries that ADO.NET uses to interact with data sources. Data providers in ADO.NET include connection, command, data reader, and data adapter objects.

Example:

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}

Object-Relational Mapping (ORM)

ORM is a technique that lets you interact with your database, like SQL Server, as if it were an object. ADO.NET also supports ORM.

Key Points:

• In ORM, SQL queries are converted.

• However, in ADO.NET, SQL queries are executed directly in SQL.

Connection String

A connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection.

Key Points:

• It opens the database.

• It should include the server name, the name of the database we are connecting to, and the password if necessary.

SqlDataReader Class

The SqlDataReader class in ADO.NET is used for read-only, forward-only access to a SQL Server database.

Key Points:

• It is used to read the data.

• It returns tables.

• It does not return objects.

Error Handling

If there is an error in connection.Open(), there is a problem in the connection string.

ExecuteReader, ExecuteNonQuery, and ExecuteScalar

Depending on the operation, the following methods are used:

• reader.ExecuteReader(); is used for SELECT operations.

• reader.ExecuteNonQuery(); is used for UPDATE, DELETE, and INSERT operations.

• reader.ExecuteScalar(); is used when writing aggregate functions.

SqlDataReader Methods

• reader.FieldCount(); gives the number of columns.

• reader.GetName(index); gives the name of the column at the given index.

SqlParams

SqlParams is used to set values in SQL commands. It helps to prevent SQL injection attacks by parameterizing queries.

Example:

string query = "SELECT * FROM Customers WHERE City = @City";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@City", "London");
SqlDataReader reader = command.ExecuteReader();

This code selects all customers from London. The @City is a parameter, and its value is set by the AddWithValue method.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version