ADO.NET Overview
ADO.NET for database connection in C# programming is a built-in collection of ActiveX Data Objects classes for the .NET Framework. It provides a ready-made framework for programmers in C# programming to insert or integrate data into databases or applications from multiple sources such as SQL Server, MySQL, Oracle, XML as backend databases, or other backend data provider platforms. ADO.NET applications are a built-in portion of .NET Framework development, and the .NET Framework is used to connect data to data sources in a disconnected, client-server environment, retrieve stored databases, manipulate databases, and update existing databases.
Key Concepts in ADO.NET in C# Programming.
Data Provider – ADO.NET is a ready-made collection of classes in C# programming that provides communication features between ADO.NET and data sources such as a relational database.
Data Provider includes.
- Connection – Manages database connections to existing data sources.
- Command – Executes SQL queries or stored procedures in the database.
- Data Reader – Provides forward-only, read-only access to data retrieved from a data source.
- Data Adapter – This link acts as a bridge between DataSets and Data Sources, it feeds data to DataSets, and updates data sources when data modifications occur.
Disconnected Architecture – ADO.NET along with C# programming provides disconnected data model support, this means that data is retrieved when needed, this database can be manipulated offline, and custom changes or updates can be made to the existing database. This process is done using DataSets and DataAdapters.
Connected vs. Disconnected Data Access.
- Connected Data Access – In this, data is retrieved directly from the stored database, and it is actively maintained. Here, a data reader is present, where the programmer creates and maintains an open connection.
- Disconnected data access – Here the data is retrieved in a DataSet, which is disconnected from the database. Programmers can modify the data offline, and if needed, the modification can be synchronized back to the database using a DataAdapter.
ADO.NET Components in Database.
Data Provider.
ADO.NET is a collection of data provider classes in the database, it is used to communicate with a particular special data sources.
Here you find some of the most common data providers.
- SQL Server Data Provider (System.Data.SqlClient).
- Oracle Data Provider (System.Data.OracleClient).
- OLE DB Data Provider (System.Data.OleDb).
- ODBC Data Provider (System.Data.Odbc).
Each of the above data providers has the following.
- Connection – The connection component establishes a database connection to the data sources.
- Command – The command executes an existing SQL query or stored procedure.
- DataReader – It provides a forward-only stream of data from the data sources.
- DataAdapter – Acts as a bridge to retrieve data in the existing dataset and update modifications back to the data sources.
Database Connection.
In C# programming, the connection object is used to open and close connections to data sources.
Example of SqlConnection for SQL Server.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = “Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
Console.WriteLine(“\n Database Connection Successfully established”);
}
}
}
Database Command.
Here the command object represents a SQL statement or stored procedure that can be executed against the database.
There are mainly three types of database commands.
- ExecuteReader() – It receives data as a data reader.
- ExecuteScalar() – It receives a single value from the database.
- ExecuteNonQuery() – It executes a command in the current database that does not have any database result. For example, INSERT, UPDATE, DELETE are database commands.
Instance of SqlCommand for executing a query.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = “Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True”;
string query = “SELECT COUNT(*) FROM Employee”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
int userCount = (int)command.ExecuteScalar();
Console.WriteLine($”Employee count – {employeeCount}”);
}
}
}
DataReader.
Here DataReader provides you fast, forward-only access to data from data sources. Data readers are typically used when C# programmers want to read data in a fast, non-cached manner.
Instance of SqlDataReader.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = “Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True”;
string query = “SELECT dep_id, Employee_Name FROM Employee”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($”{reader[“dep_id”]}, {reader[“Employee_Name”]}”);
}
}
}
}
DataAdapter.
In C# programming DataAdapter is used to populate DataSet or DataTable with data from an existing database and to update or modify the data in the database again.
Example of SqlDataAdapter to populate DataSet.
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = “Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True”;
string query = “SELECT dep_id, Employee_Name FROM Employee”;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, “Employee”);
// it Display data from DataSet
foreach (DataRow row in dataSet.Tables[“Employee”].Rows)
{
Console.WriteLine($”{row[“dep_id”]}, {row[“Employee_Name”]}”);
}
}
}
}
DataSet and DataTable.
- DataSet – DataTable is a collection of objects, DataSet can be used to store data from multiple tables and their relationships in memory.
- DataTable – It represents a single table of data in-memory.
If the DataSet is disconnected, it means you can retrieve the data again, you can manipulate the data offline, and you can update or modify the data sources if needed.
Let Working with Sql DataTable.
DataTable dt = new DataTable();
dt.Columns.Add(“dep_Id”, typeof(int));
dt.Columns.Add(“Dep_Name”, typeof(string));
dt.Rows.Add(101, “Finance”);
dt.Rows.Add(104, “Marketing”);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine($”{row[“dep_Id”]}, {row[“Dep_Name”]}”);
}
Let Working with Sql DataSet
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add(“Employee”);
dt.Columns.Add(“Emp_Id”, typeof(int));
dt.Columns.Add(“Emp_Name”, typeof(string));
dt.Rows.Add(207, “Rock”);
dt.Rows.Add(408, “Robert”);
foreach (DataRow row in ds.Tables[“Employee”].Rows)
{
Console.WriteLine($”{row[“Emp_Id”]}, {row[“Emp_Name”]}”);
}
Conclusion of ADO.NET (ActiveX Data Objects for .NET) in C# programming.
ADO.NET in C# programming is a powerful and flexible method to communicate and interact with a database table or data in a .NET application in the backend. The primary concepts in database access in ADO.NET include data provider, command, data reader, and data adapter elements. With ADO.NET, you can apply both data access models using connected data reader features and disconnected datasets and data adapters. ADO.NET in C# programming supports database transactions.
