DATA ACCESS WITH ADO.NET
CREATE A SQLLITE DATABASE
The time has come to use a relational database, and if we use a database it is because we want to persist our application data in a durable way. For this project we will use SQLite which is a very lean, file-based technology that does not require any prerequisites to be installed. We’ll use a database-first approach first we’ll create the database and tables then we’ll go in with the C# code.
THE MYCOURSE.DB DATABASE
The database should remain private, no one should download it so the best place to place it is in a new folder under the Root called Data. All the commands for creating tables and entering dummy data can be found under the docs/ddl directory. Under the folder Data creates a file and calls it MyCourse.db, get an extension if you use Visual Studio Code or a plugin that handles SQL scripts from SQLite or get a program that handles SQLite databases, you can find many on the net.
ORGANIZE THE MODEL INTO APPLICATION AND INFRASTRUCTURE SERVICES
Previously, we used a CourseService class that was an application service that returned course information to us. Now that we have data in the SQLite database the CourseService is no longer needed. Now we’re going to create a new application service for ourselves, which this time uses ADO.NET. An Application service is responsible for knowing what to extract, while instead how one connects to the Database and how one extracts information is a responsibility of the infrastructure service. Our application service will have a dependence on this infrastructure service.
Let’s look at the interface.
THE HIERARCHY OF COMPONENTS CREATED
We first created an ICourseService interface and did the injection of the concrete AdoNetCourseService implementation. This is in the application layer. In the infrastructure layer, we created the IDatabaseAccessor interface and injected the SqliteDatabaseAccessor dependency.
This is the Startup.cs file
LEARN ABOUT ADO.NET CLASSES
ADO.NET is a traditional technology that has many years on its back, but it was introduced in .NET CORE because of its stability and versatility. It is provider-based and can work with many DBMSs, just get the files from NuGet. If we download the provider for SQLite we will have a SqliteConnection class, changing providers for MySQL we will have MySQLConnection etc.
These are some classes, which we will learn more about later, provided by the provider for SQLite, a provider developed by Microsoft.
Let us look at the interfaces that provide weak coupling for the SQLite provider.
THE OPERATION OF ADO.NET
Let’s see with a video and explanation of each animation how ADO.NET works.
First we create ourselves a SqliteConnection object, and invoke its open method to open the connection. Actually the connection is not opened at this time, also because if we are using for example SQLServer the database might reside on another server, so opening the connection means, create a TCP socket, if the connection is encrypted there is the whole TLS protocol, there is the whole authentication phase. In short, there are some steps to be taken that overall take tens of milliseconds. ADO.NET introduces an organ called Connection Pool that creates a number of proactive connections first.
THE CONNECTION POOL
When the Open method is invoked it is actually the Connection Pool that provides the ready-made connection. This greatly increases efficiency. Once the connection has been opened we create our SqliteCommand object to, for example, pull all the courses from the database. Thanks to the ExecuteReader() method, it will send the SELECT command to the database. The database will extract rows that attention still is server-side, not in application memory. And this is where the ExecuteReader() method comes in, which will return us a SqliteDataReader object that, thanks to its Read() method, can extract from the database one row at a time and transfer the information into the application’s memory. Once we have read all the results, the Read() method will return false, we can use the Dispose() method on the connection to close it, even though it is actually returning that connection to the Connection Pool.
On the left we find all the objects that need an active connection with the database in order to function; on the right are two components that can work in disconnected mode.
USE ADO.NET CLASSES TO ACCESS THE DATABASE
Let’s finally see after giving an overview of ADO.NET how the theory is put into practice, first making sure that connections are closed after using them. If there is a mishandling of this aspect the active connections provided by the ADO.NET Connection Pool that are not infinite could run out and cause an error at runtime.
With using even if an exception occurs we are guaranteed to close the database connection.
STRUCTURE OF A DATASET AND USE IN CODE
Let us look at the structure of the dataset and how it was used in the code.
READ A DATASET AND MAP THE INFORMATION TO A VIEWMODEL
Let’s go back to the application service and see how to read information from a DataSet to map it to the ViewModel.
Let’s look at the ViewModel.
LINK TO CODE ON GITHUB
Download the section10 code or clone the GITHUB repository to have all sections available in your favorite editor. THE COURSE DETAIL STILL HASN’T BEEN IMPLEMENTED.
Leave A Comment