DATA ACCESS WITH ADO.NET PART TWO

DATA ACCESS WITH ADO.NET

LOADING MULTIPLE RESULT TABLES INTO A DATASET

Let us see how it is possible to load multiple DataTables into a DataSet. In this example, two are loaded since there are two queries, But even more can be uploaded. This is the code:

The code has been commented on.

PROTECT APPLICATION FROM SQL INJECTION

When we select a course by requiring that the id be passed to us, that value is actually part of the query string or a form, and the user has the power to modify it. Suppose there is a malicious user. The reality is that it is able to do what the image shows, and that is a DROP TABLE on the Courses table.

The GetArguments() method of FormattableString returns the two ids present in the two queries. However, they are in a format not compatible with SqliteParameters, hence the need to insert the @ in the last statement of the for loop. Once all the parameters have been created, the AddRange method adds them to the SqliteCommand parameter collection.

ASYNCHRONOUS OPERATIONS WITH ASYNC AND AWAIT

Taking advantage of asynchronous operations means getting the best performance from our application. To explain asynchronous operations let’s assume that the application is receiving lots of simultaneous requests, this we could compare to a pizza restaurant that is packed with people one night, say more than a hundred. Of course, managers cannot keep 100 pizza makers, one for each person. We can compare pizza makers in a pizzeria to Threads in ASP.NET; you can’t create many of them because they are valuable resources like pizza makers.

OPERATIONS THAT DO NOT BLOCK ASP.NET CORE THREADS

Some operations are blocking, while others do not require the active intervention of the Thread or pizza maker, in the case of a Thread after submitted a query there is the database working, for the pizza maker it might be waiting for the pizza to be cooked. If at that time both the Thread and the pizza maker had to wait it would be an efficiency, the pizza maker does not get stuck but goes to work on another customer’s pizza, so too the Thread can free itself from this wait and go and serve another user’s request.

In order to use OpenAsync() we must first perform some operations. First put await in front of the method. await designates this instruction as an asynchronous one whose completion you must wait for before processing the next operation. A Thread that gets to process this instruction by finding the await keyword will make itself available so that ASP.NET Core can give it more work. The second step is to use the async keyword at the method signature. Thanks to this keyword we can use await within the method. The third step is to have the method return a Task. A Task is a class used to describe an asynchronous operation; it allows us to tell what its status is, that is, whether it is still running, has failed, or has completed.

SECTION SUMMARY

ACCESSING A RELATIONAL DATABASE WITH ADO.NET

ADO.NET is the name of the data access technology that allows us to connect to a relational database to read and write data using SQL language queries and commands. Within the database, data are organized on tables, and each table consists of rows and columns. Each row represents an entity (e.g., a course), while the columns are the attributes of that entity (e.g., title, description, author, …).

Although there are many database technologies, ADO.NET is very versatile because it consists of base classes on which then each producer can build his or her own provider. For example, Microsoft made the provider System.Data.SqlClient which is used to connect to a SQLServer database, but also Microsoft.Data.Sqlite which is used to connect to a Sqlite database instead.

For each database technology there is at least one provider, which we can install in the project as a NuGet package. Therefore, if we wanted to install the provider for Sqlite, we could run this command:

1. dotnet add packageData.Sqlite

At this point, in our application, we have several classes to interact with the database. Here are some of the most commonly used ones:

• SqliteConnection allows us to establish a connection to the database;
• SqliteCommand is used to send SQL queries and commands to the database;
• SqliteParameter to use user input safely in our queries and commands;
• SqliteDataReader to read the result table(s) returned by the database as a result of an SQL query;
• SqliteTransaction to execute queries and commands in an isolated context and in an atomic (all-or-nothing) manner, so that the data in the database always remain in a consistent state.

These classes are present in all ADO.NET providers, so if we learn to use them with Sqlite, then we will already know how to use them for any other database technology. What changes is just the prefix in the name of the classes (SqliteConnection for Sqlite, SqlConnection for SQL Server, etc…). However, we must always keep in mind that each database technology uses its own “dialect” of the SQL language, and so we will still need time to learn how to use it to its full potential.

CONNECTED AND DISCONNECTED CLASSES

The classes we have just seen are also called “connected” classes because they are designed to work with a database connection. Then there are also these other two classes, which are called “disconnected” instead because they can run without having to establish any connection. In fact, we can use them freely even if no database yet exists in our application.

• DataTable represents a data table, on rows and columns. Thanks to its Load method, it can load data from a SqliteDataReader (or any other type of data reader), so that we can store the results in the application’s memory even after closing the database connection;
• DataSet is a collection of DataTables and can also describe the relationships that exist between DataTables, such as the one-to-many relationship that exists between a course and its classes.

In short, the “disconnected” classes are able to mimic the same data structures that we have in a relational database, so that we can use them in the application even after closing the connection.

THE CONNECTION TO THE DATABASE

The database connection should be opened only when necessary and closed as soon as possible, just as we did in the previous example. We can also send two or more SQL queries with a single SqliteCommand object: we just separate them with semicolons. Here is an example in which we retrieve both the data of a course and the list of its classes. We use the do..while loop to create as many DataTables for as many result tables returned.

1. //Now we use a DataSet, which will contain multiple DataTables.
2. var dataSet = new DataSet();
3. using (var conn = new SqliteConnection(“Data Source=Data/MyCourse.db”))
4. {
5. Open();
6. string query = “SELECT * FROM Courses WHERE Id=5; SELECT * FROM Lessons WHERE IdCourse=5”;
7. using (var cmd = new SqliteCommand(query, conn))
8. {
9. using (var reader = cmd.ExecuteReader())
10. {
11. do
12. {
13. var dataTable = new DataTable();
14. Add(dataTable);
15. Load(reader);
16. } while (!reader.IsClosed); //Continue to cycle until the SqliteDataReader is closed
17. }

PREVENT SQL INJECTION

When we receive user input, we should always sanitize it, to prevent malicious parties from executing arbitrary SQL queries and commands. For this we use a SqliteParameter object, which we then add to the SqliteCommand.

CORRECTLY DESTROY IDISPOSABLE OBJECTS

In the previous examples we made extensive use of using blocks. In this way we ensure that the Dispose method of objects that implement IDisposable is always called, even if an exception should occur during their use. It is very important that such objects are destroyed properly, otherwise memory waste or other undesirable situations may occur.

In fact, if we did not destroy the SqliteConnection objects, the connection pool, which is the organ responsible for setting up connections, would soon become saturated with open connections and would not allow us to open any more, effectively preventing us from accessing the database.

ASYNCHRONOUS METHODS THE KEYWORDS ASYNC AND AWAIT

Many of ADO.NET’s class methods possess asynchronous variants. For example, the SqliteConnection, in addition to the Open method has OpenAsync.

Asynchronous methods exist for operations that make use of I/O devices, such as disk or network, which are subject to latencies.

Using asynchronous methods is ideal because it improves the performance of our application when there are several concurrent users. In fact, since opening a database connection or sending a query are operations that require waiting, the thread can make itself available during that wait to do something else, such as to start processing another user’s request. Then, when the asynchronous operation ends, execution can resume from the next instruction.

In order to take advantage of asynchronous methods, we had to make 3 changes.

1. Use the await operator before invoking the asynchronous method;
2. Append the keyword async to the signature of our method;
3. Have the method return a Task(or a Task, in case it returns void).

Optionally, we can add the suffix “Async” to the method name.

LINK TO CODE ON GITHUB

GITHUB

Download the section10 code or clone the GITHUB repository to have all sections available in your favorite editor.