Whenever we create web, windows or Console Application in .Net core framework may be we come across the situation we want store the data permanently for that database is the amazing choice, so in order to make communication of your application build in .Net core framework ,with any database we require ADO.Net.
ADO.Net stands for ActiveX Data Objects and gives access to data sources. It is a basic technology for talking with databases. Endow multiples provides for different databases. For implementation, we have a number of classes that we can use to easily communicate with databases.
When we talk about Data sources it may be XMl, JSON data or Database ,if particularly we talk about the database there could be any database for example MSSQLSERVER,MSACCESS,ORACLE and others. Microsoft .Net framework Providers provide namespaces and classes for accessing the different database.Root classes are found in System.Data Namespace.
Namespaces and classes for accessing various databases are provided by Microsoft.Net framework Providers. System.Data Namespace contains root classes for communicating Database with Frontend. ADO.Net is mostly used to link the Front End Application to the Backend. In our instance, we will communicate the ASP.NET MVC CORE with the Database, which is MSSQLSERVER.
ADO.NET Components
ADO.NET components perform specific database related tasks. These are 6 main components
- Data Providers – used for communication with the data source.
- Connection – maintains the location information of the data source.
- Command – executes a given command type.
- DataReader – reading data in connected environment.
- DataAdapter – reading data in disconnected environment.
- DataTable & DataSet – in-memory storage of data
Four Common Data Providers in ADO.NET
Data Provider for SQL Server : Provides data access for Microsoft SQL Server Database. It uses System.Data.SqlClient.
Data Provider for OLE DB : Provides data access for OLE DB data sources. These are used to access various COM objects and DBMS that does not have a specific .NET data provider. It uses the System.Data.OleDb namespace.
Data Provider for ODBC : The ODBC Data Providers are defined within the System.Data.Odbc namespace are typically useful only if we need to communicate with a given DBMS for which there is no custom .NET data provider.
Data Provider for Oracle : Provides data access for Oracle databases. Uses the System.Data.OracleClient namespace.
ADO.NET provides 2 types of architectural for developing database centric application.
Connected Architecture
ADO.NET’s Connected Architecture dependent on a consistent database connection to access data and perform any operations on the retrieved data. ADO.NET offers the following Connection,Command,DataReader, Parameter ,DataAdapter etc to help we build our application with a connected architecture.
Disconnected Architecture
ADO.NET’s connected architecture dependent on a consistent database connection to access data and perform any operations on the retrieved data. However, in today’s complex distributed application environments, it is not possible to dependent on a dedicated database connection to fetch and modify data.
ADO.NET offers the following Dataset,Datatable,DataColumn, DataRow etc to help we build our application with a Disconnected Architecture.
Now we can create a sample MVC web application with ASP.Net Core using ADO.NET and SQL server with the help of Visual Studio.We will be creating a sample Employee Record Management System and performing CRUD operations on it.
Step 1:First we will create a database Employee1 and then create Table tblEmployee
Open SQL Server and use the following script to create tblEmployee table.
Step 2:Create New Asp.net MVC Core Application
Adding the Controller to the Application
Right-click the Controllers folder and select Add >> New Item. An “Add New Item” dialog box will open. Select Web from the left panel, then select “MVC Controller Class” from templates panel, and put the name as EmployeeController.cs. Press OK.
Now our EmployeeController has been created. We will put all our business logic in this controller.
Adding the Model to the Application
Right-click Models folder and select Add >> Class. Name your class Employee.cs. This class will contain our Employee model properties.
Add one more class file to Models folder. Name it as EmployeeDataAccessLayer.cs . This class will contain our Database related operations.
Now, the Models folder has the following structure.
Open EmployeeDataAccessLayer.cs and put the following code to handle database operations. Make sure to put your connection string.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace MVCDemoApp1
{
public class Employee
{
public class EmployeeDataAccessLayer
{
string connectionString = “Put Your Connection string here”;
//To View all employees details
public IEnumerable<Employee> GetAllEmployees()
{
List<Employee> lstemployee = new List<Employee>();
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(“spGetAllEmployees”, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.ID = Convert.ToInt32(rdr[“EmployeeID”]);
employee.Name = rdr[“Name”].ToString();
employee.Gender = rdr[“Gender”].ToString();
employee.Department = rdr[“Department”].ToString();
employee.City = rdr[“City”].ToString();
lstemployee.Add(employee);
}
con.Close();
}
return lstemployee;
}
//To Add new employee record
public void AddEmployee(Employee employee)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(“spAddEmployee”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@Name”, employee.Name);
cmd.Parameters.AddWithValue(“@Gender”, employee.Gender);
cmd.Parameters.AddWithValue(“@Department”, employee.Department);
cmd.Parameters.AddWithValue(“@City”, employee.City);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
//To Update the records of a particluar employee
public void UpdateEmployee(Employee employee)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(“spUpdateEmployee”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@EmpId”, employee.ID);
cmd.Parameters.AddWithValue(“@Name”, employee.Name);
cmd.Parameters.AddWithValue(“@Gender”, employee.Gender);
cmd.Parameters.AddWithValue(“@Department”, employee.Department);
cmd.Parameters.AddWithValue(“@City”, employee.City);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
//Get the details of a particular employee
public Employee GetEmployeeData(int? id)
{
Employee employee = new Employee();
using (SqlConnection con = new SqlConnection(connectionString))
{
string sqlQuery = “SELECT * FROM tblEmployee WHERE EmployeeID= ” + id;
SqlCommand cmd = new SqlCommand(sqlQuery, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
employee.ID = Convert.ToInt32(rdr[“EmployeeID”]);
employee.Name = rdr[“Name”].ToString();
employee.Gender = rdr[“Gender”].ToString();
employee.Department = rdr[“Department”].ToString();
employee.City = rdr[“City”].ToString();
}
}
return employee;
}
//To Delete the record on a particular employee
public void DeleteEmployee(int? id)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(“spDeleteEmployee”, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@EmpId”, id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
CRUD operation in MVC is the basic operations, where CRUD denotes create, read, update, and delete.
CRUD (Create, Read, Update and Delete) application is the most important application for creating any project. It provides an idea to develop a large project.
Conclusion:
In order to access data and carry out any operations on the obtained data, ADO.NET depends on a reliable database connection. We have learned about creating a sample MVC web application with ASP.Net Core using ADO.NET and SQL server.
