ADO.NET with ASP .NET Core In Connected Environment

Level : Advanced

Mentor: Shailendra Chauhan

Duration : 00:03:00

ADO.NET Architecture

ADO.NET is a data access technology in ASP.NET Core that provides a set of libraries and components to interact with databases. It follows a connected architecture, allowing applications to establish connections, execute commands, and retrieve or modify data.

Example

// Import required namespaces
using System;
using System.Data;
using System.Data.SqlClient;
// Establish a database connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
  connection.Open();
  // Create and execute a SQL command
  using (SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection))
  {
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
      Console.WriteLine(reader["CustomerName"]);
    }
  }
}

Configuration

Configuration in ASP.NET Core involves setting up and managing application settings. This can be done using JSON files, environment variables, or other configuration providers.

Example

// Load configuration settings from appsettings.json
IConfiguration configuration = new ConfigurationBuilder()
  .SetBasePath(Directory.GetCurrentDirectory())
  .AddJsonFile("appsettings.json")
  .Build();
// Retrieve a configuration value
string connectionString = configuration.GetConnectionString("DefaultConnection");

.NET Data Providers

.NET Data Providers are libraries that allow .NET applications to interact with different databases like SQL Server, MySQL, or Oracle. They provide specific classes and methods for database operations.

Example

// Using SQL Server data provider
using System.Data.SqlClient;
// Create a SQL Server connection
SqlConnection connection = new SqlConnection(connectionString);
// Perform database operations with SqlConnection, SqlCommand, etc.

Connected-to-Database

Connecting to a database in ADO.NET involves creating a database connection and using it to perform operations.

Example

using System;
using System.Data;
using System.Data.SqlClient;
// Establish a connection to a SQL Server database
using (SqlConnection connection = new SqlConnection(connectionString))
{
  connection.Open();
  // Perform database operations here
}

Retrieving Data - Connected

To retrieve data from the database, you can use a DataReader to execute queries and retrieve results.

Example

using (SqlCommand command = new SqlCommand("SELECT FirstName, LastName FROM Employees", connection))
{
  using (SqlDataReader reader = command.ExecuteReader())
  {
    while (reader.Read())
    {
      Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
    }
  }
}

Inserting Data - Connected

To insert data into a database, you can use a SqlCommand with an INSERT statement.

Example

using (SqlCommand command = new SqlCommand("INSERT INTO Products (Name, Price) VALUES ('Widget', 10.99)", connection))
{
  int rowsAffected = command.ExecuteNonQuery();
  Console.WriteLine($"{rowsAffected} rows inserted.");
}

Updating Data - Connected

Updating data involves executing an UPDATE statement.

Example

using (SqlCommand command = new SqlCommand("UPDATE Products SET Price = 12.99 WHERE Name = 'Widget'", connection))
{
  int rowsAffected = command.ExecuteNonQuery();
  Console.WriteLine($"{rowsAffected} rows updated.");
}

Deleting Data - Connected

Deleting data involves executing a DELETE statement.

Example

using (SqlCommand command = new SqlCommand("DELETE FROM Products WHERE Name = 'Widget'", connection))
{
  int rowsAffected = command.ExecuteNonQuery();
  Console.WriteLine($"{rowsAffected} rows deleted.");
}

Configuring Connection String from App Settings

Store database connection strings in app settings for easier configuration.

Example

// Load connection string from appsettings.json
string connectionString = configuration.GetConnectionString("DefaultConnection");

Implementing Data Access Layer

Create a data access layer for managing database operations in a structured manner.

List, Create, and Details

Implement actions in your ASP.NET Core application to list, create, and view details of records from the database.

Edit and Delete

Implement actions in your ASP.NET Core application to edit and delete database records.

Handling Exceptions

Implement error handling to gracefully manage exceptions that may occur during database operations.

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.