Hello everyone, today we will take a journey through data processing in the .NET world. In particular, we will focus on two topics: ADO.NET and Entity Framework Core.
This aims to be a good basic guide to using these libraries. Before delving into technical aspects, I'd like to share my opinion on the learning process and recommend what, of course, I believe to be the best path.
You should not dismiss the idea of using ADO.NET. I understand the allure of using an ORM (Object Relational Mapping), but having a deep understanding of ADO.NET provides a better foundation for transitioning to Entity Framework. In analogy, it would be like trying to learn CSS (Cascading Style Sheets) using Bootstrap.
Table of Contents:
- Overview of Libraries
- Let's Begin with ADO.NET
- Exploring Execute Commands
- Working with Parameters
- Mapping Techniques with ADO.NET
Overview of Libraries
ADO.NET and Entity Framework Core (from now on, EF Core) are two libraries available in .NET with the purpose of reading and processing data in our databases. Despite their common purpose, they achieve it in different ways.
The first is a native library in .NET, available in both .NET Framework and .NET Core, and it has a manual system for opening and closing the database connection. It receives queries in string format.
The second is an Object-Relational Mapper (ORM) that interfaces with our database(s) through a Database Context and precise mapping of entities and relationships using classes. Various database operations can be performed using LINQ extensions or, alternatively, by writing queries in string format.
Let's Begin with ADO.NET
As we mentioned, ADO.NET is a data access library that natively supports SQL Server but also offers extensions for other database providers. It is used to modify, create, and read data in our databases, and the results of data retrieval operations are then placed in an object called a DataSet, which serves as an in-memory cache of the freshly retrieved data.
Configuring ADO.NET can be a bit verbose, but what it certainly doesn't lack are:
- High Performance
- Complete control and management of SQL operations
Now, let's set up a classic Console application in .NET and use the AdventureWorks2022 database as our data source, which we configured in a previous article on stored procedures that you can find here.
Then, let's configure a very simple appsettings.json file. If you're not sure how to do it, no problem; I've written a guide for this as well Options and Configuration in .NET6. Finally, install the ADO.NET provider for MSSQL, Microsoft.Data.SqlClient.
Now, let's create a basic connection to our database:
using SqlConnection sqlConnection = new(connectionString);
For educational purposes, we won't centralize the management of various connection opening or closing operations. However, as a best practice, I recommend using this kind of approach. We use a 'using' statement to free up memory at the end of operations.
Now, in order to execute our queries on the database, it is necessary to create a SqlCommand. This can be done in two ways:
string query = "SELECT TOP 100 Name, ProductNumber FROM [Production].[Product]";
SqlCommand commandFluent = sqlConnection.CreateCommand();
commandFluent.CommandText = query;
/* **************************** otherwise *********************************/
SqlCommand command = new SqlCommand(query, sqlConnection);
Now, these two statements can be used to execute any SQL statement, from stored procedures to regular queries, whether they are simple or complex.
Next, let's open the actual connection to the database. To do this, we'll use this paradigm. We'll use a try/catch/finally block, where the first statement in the try block will open the database connection with:
try {
sqlConnection.Open();
And as the last statement in the finally block:
finally {
sqlConnection.Close();
}
This will ensure that we close the database connection even in the event of an error.
Note: Always close the database connections, or you risk blocking any subsequent connection attempts!
Once all of this is done, we are ready to execute our queries.
Exploring Execute Commands
To execute our query on the database under examination, we can use a series of commands that begin with the Execute construct. Primarily, we have the following commands:
- ExecuteNonQuery: Returns the number of rows affected by our query, mainly used for insert, update, and delete operations.
- ExecuteReader: Allows you to retrieve and read the query's results using a Data Reader.
- ExecuteScalar: Returns the first column of the first row of the results.
Each of the commands just mentioned has an asynchronous counterpart, and of course, whether you use one or the other command will vary depending on your needs. In this specific example, you will need to use the ExecuteReader command.
Let's see a small example of what was just mentioned:
using SqlDataReader reader = command.ExecuteReader();
Since the SqlDataReader class implements the IDisposable interface, it is possible and, I would say, necessary to use the 'using' statement here as well in order to release resources once the operation is executed.
Now, to read the result of our reader, we need to use the Read() method included in the SqlDataReader class, and with a simple 'while' loop, we can perform the reading (or mapping into an entity class) like this:
while (reader.Read())
{
Console.WriteLine($"{reader["Name"]} - {reader["ProductNumber"]}");
}
This will allow us to display the properties we've retrieved from our database on the screen.
Working with Parameters
Now, in the previous case, we saw how to retrieve data from our tables with a simple SELECT query on the database. But what if we need to insert, update, or remove data from our database? I would say the process is almost identical but with one small but crucial difference: SqlParameter.
The SqlParameter class allows us to write and execute all queries that require one or more parameters to be passed securely (preventing SQL injection attacks) and, in my opinion, it makes the code much more readable.
Now, let's set up the basic code:
query = $@"
INSERT INTO [Production].[Product] ([Name],
[ProductNumber],
[MakeFlag],
[FinishedGoodsFlag],
[Color],
[SafetyStockLevel],
[ReorderPoint],
[StandardCost],
[ListPrice],
[Size],
[SizeUnitMeasureCode],
[WeightUnitMeasureCode],
[Weight],
[DaysToManufacture],
[ProductLine],
[Class],
[Style],
[ProductSubcategoryID],
[ProductModelID],
[SellStartDate],
[SellEndDate],
[DiscontinuedDate],
[rowguid],
[ModifiedDate]
)
VALUES (@Name, @ProductNumber, @MakeFlag, @FinishedGoodsFlag, @Color, @SafetyStockLevel, @ReorderPoint, @StandardCost, @ListPrice, @Size, @SizeUnitMeasureCode, @WeightUnitMeasureCode, @Weight, @DaysToManufacture, @ProductLine, @Class, @Style, @ProductSubcategoryID, @ProductModelID, @SellStartDate, @SellEndDate, @DiscontinuedDate, @rowguid, @ModifiedDate);
";
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50)).Value = "Nuovo Prodotto";
sqlCommand.Parameters.Add(new SqlParameter("@ProductNumber", SqlDbType.NVarChar, 25)).Value = "N12345";
sqlCommand.Parameters.Add(new SqlParameter("@MakeFlag", SqlDbType.Bit)).Value = true;
sqlCommand.Parameters.Add(new SqlParameter("@FinishedGoodsFlag", SqlDbType.Bit)).Value = true;
... Others query parameter
try
{
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
sqlConnection.Close();
}
As you can see, we have used the SqlParameter class to parameterize our query, and we have also added other values in addition to the parameter name, such as the type, size, and value.
Mapping Techniques with ADO.NET
Now that we have a solid foundation in ADO.NET, we can address the last remaining point regarding entity mapping. If you recall, in the first example, we read properties from a data reader using indexing, like 'reader["property name"]'. Is there a different way to achieve this? Let's explore it together!
We can do this using implicit or explicit operators:
public class Employee
{
public int BusinessEntityID { get; set; }
public string Title { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public string Suffix { get; set; }
public string JobTitle { get; set; }
public string PhoneNumber { get; set; }
public string PhoneNumberType { get; set; }
public string EmailAddress { get; set; }
public int EmailPromotion { get; set; }
public string AddressLine1 { get; set; }
public string AddressLine2 { get; set; }
public string City { get; set; }
public string StateProvinceName { get; set; }
public string PostalCode { get; set; }
public string CountryRegionName { get; set; }
public string AdditionalContactInfo { get; set; }
public static implicit operator Employee(SqlDataReader reader)
{
Employee employee = new Employee();
if(reader.Read())
{
employee.BusinessEntityID = (int)reader["BusinessEntityID"];
employee.Title = reader["Title"] is DBNull ? string.Empty : (string)reader["Title"];
employee.FirstName = reader["FirstName"] is DBNull ? string.Empty : (string)reader["FirstName"];
employee.MiddleName = reader["MiddleName"] is DBNull ? string.Empty : (string)reader["MiddleName"];
employee.LastName = reader["LastName"] is DBNull ? string.Empty : (string)reader["LastName"];
employee.Suffix = reader["Suffix"] is DBNull ? string.Empty : (string)reader["Suffix"];
employee.JobTitle = reader["JobTitle"] is DBNull ? string.Empty : (string)reader["JobTitle"];
employee.PhoneNumber = reader["PhoneNumber"] is DBNull ? string.Empty : (string)reader["PhoneNumber"];
employee.PhoneNumberType = reader["PhoneNumberType"] is DBNull ? string.Empty : (string)reader["PhoneNumberType"];
employee.EmailAddress = reader["EmailAddress"] is DBNull ? string.Empty : (string)reader["EmailAddress"];
employee.EmailPromotion = (int)reader["EmailPromotion"];
employee.AddressLine1 = reader["AddressLine1"] is DBNull ? string.Empty : (string)reader["AddressLine1"];
employee.AddressLine2 = reader["AddressLine2"] is DBNull ? string.Empty : (string)reader["AddressLine2"];
employee.City = reader["City"] is DBNull ? string.Empty : (string)reader["City"];
employee.StateProvinceName = reader["StateProvinceName"] is DBNull ? string.Empty : (string)reader["StateProvinceName"];
employee.PostalCode = reader["PostalCode"] is DBNull ? string.Empty : (string)reader["PostalCode"];
employee.CountryRegionName = reader["CountryRegionName"] is DBNull ? string.Empty : (string)reader["CountryRegionName"];
employee.AdditionalContactInfo = reader["AdditionalContactInfo"] is DBNull ? string.Empty : (string)reader["AdditionalContactInfo"];
}
return employee;
}
public override string ToString()
{
return $"{Title} - {FirstName} - {LastName} - {Suffix} - {JobTitle} - {PhoneNumber} - {PhoneNumberType} - {EmailAddress} - {EmailPromotion} - {AddressLine1} - {AddressLine2} - {City} - {StateProvinceName} - {PostalCode} - {CountryRegionName} - {AdditionalContactInfo}.";
}
}
Or, alternatively, using explicit operators:
public class Employee
{
// all properties
public static explicit operator Employee(SqlDataReader reader) { Employee employee = new Employee();
if (reader.Read()) { employee.BusinessEntityID = (int)reader["BusinessEntityID"];
employee.Title = reader["Title"] is DBNull ? string.Empty : (string)reader["Title"];
employee.FirstName = reader["FirstName"] is DBNull ? string.Empty : (string)reader["FirstName"];
employee.MiddleName = reader["MiddleName"] is DBNull ? string.Empty : (string)reader["MiddleName"];
employee.LastName = reader["LastName"] is DBNull ? string.Empty : (string)reader["LastName"];
employee.Suffix = reader["Suffix"] is DBNull ? string.Empty : (string)reader["Suffix"];
employee.JobTitle = reader["JobTitle"] is DBNull ? string.Empty : (string)reader["JobTitle"];
employee.PhoneNumber = reader["PhoneNumber"] is DBNull ? string.Empty : (string)reader["PhoneNumber"];
employee.PhoneNumberType = reader["PhoneNumberType"] is DBNull ? string.Empty : (string)reader["PhoneNumberType"];
employee.EmailAddress = reader["EmailAddress"] is DBNull ? string.Empty : (string)reader["EmailAddress"];
employee.EmailPromotion = (int)reader["EmailPromotion"]; employee.AddressLine1 = reader["AddressLine1"] is DBNull ? string.Empty : (string)reader["AddressLine1"];
employee.AddressLine2 = reader["AddressLine2"] is DBNull ? string.Empty : (string)reader["AddressLine2"];
employee.City = reader["City"] is DBNull ? string.Empty : (string)reader["City"];
employee.StateProvinceName = reader["StateProvinceName"] is DBNull ? string.Empty : (string)reader["StateProvinceName"];
employee.PostalCode = reader["PostalCode"] is DBNull ? string.Empty : (string)reader["PostalCode"];
employee.CountryRegionName = reader["CountryRegionName"] is DBNull ? string.Empty : (string)reader["CountryRegionName"]; employee.AdditionalContactInfo = reader["AdditionalContactInfo"] is DBNull ? string.Empty : (string)reader["AdditionalContactInfo"]; } return employee; }
}
And then use them, respectively, like this:
Employee employee = reader;
// Or like this...
Employee employee = (Employee)reader;
Alternatively, we could create a generic extension method that allows us to convert the data reader into an entity object. Let's create the SqlDataReaderExtensions class:
public static class SqlDataReaderExtension {
}
And insert these two new methods:
public static T ConvertToEntities<T>(this SqlDataReader reader) where T : class, new()
{
ArgumentNullException.ThrowIfNull(nameof(reader));
Type type = typeof(T);
BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Public;
PropertyInfo[] propertyInfos = type.GetProperties(bindingFlags);
T entity = new T();
while(reader.Read())
{
foreach(PropertyInfo propertyInfo in propertyInfos)
{
if (reader.HasColumn(propertyInfo.Name))
{
object value = reader[propertyInfo.Name];
if (value != DBNull.Value)
{
propertyInfo.SetValue(entity, value);
}
}
}
}
return entity;
}
public static bool HasColumn(this SqlDataReader reader, string name)
{
for (int index = 0; index < reader.FieldCount; index++)
{
if (reader.GetName(index).Equals(name, StringComparison.InvariantCultureIgnoreCase))
{
return true;
}
}
return false;
}
What we are doing here is quite simple. Firstly, we check that the reader is not null. Then, we use reflection to retrieve the generic type used and obtain its public properties. We instantiate the generic entity with a constraint that it must have a constructor and then loop through the reader and properties. We check if that property is present in our reader and, if it is, we verify that its value is not of type DBNull.
These are, of course, basic examples. In our work, we have many ways to achieve the same thing, and it's up to us to understand which one best suits our project's needs.
With this, we conclude our first journey into data processing in .NET. In the next article, I will show you how Entity Framework Core eliminates all this "complexity," how we can map our elements to the database and obtain our entities, removing all the mapping code we saw at the end of this article. I will also explain how to use LINQ to execute our database queries and how it manages database connections.
If you've made it this far in the article, I sincerely thank you and hope you can give it a like and share it. Of course, I also hope you can share your experiences with me and contribute interesting points of discussion.
Happy Coding!!
Top comments (0)