How to configure a SQL Server connection?
Configuring a SQL Server connection is an essential step to efficiently manage databases. This process starts by configuring specific parameters within a protected method that is overwritten to suit our requirements. This method is known as OnConfiguring
.
What is the first step in establishing the connection?
The first step is to create an object that references our database context option configuration. This object will belong to the DbContextOptionsBuilder
class. Through this object, we will manage if a connection string is already configured. If not, we will proceed to configure it through code.
How is the connection string defined?
To define the connection string, the appropriate library must be used, such as Microsoft.EntityFrameworkCore.SqlServer
, which must be installed if it is not available. The connection string must include:
- Server name: Identified for example as
LM
, which is an example of the host name.
- Database name: Which can be
InventoryDB
or any name you wish to assign.
- Credentials: This includes the user name, usually
SA
, and its associated password.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){ if (!optionsBuilder.IsConfigured) { optionsBuilder.UseSqlServer("Server=LM;Database=InventoryDB;User ID=SA;Password=myPassword;"); } }}
How to ensure correct installation of dependencies?
If you encounter problems when compiling or running the project, you may need to install some additional packages from the NuGet package manager, such as Microsoft.EntityFrameworkCore.Design.
This is done through the package manager console.
Managing migrations for databases
Once the connection is established, it is crucial to manage migrations. These allow you to apply reflections of changes to the database schema from the source code.
How is a new migration created?
To register changes to the data model, you must create a new migration using the Add-Migration
command followed by the descriptive name of the migration. This should be done in the appropriate console ensuring that the correct project is selected as the start project.
Add-Migration InitialCreate
What to do if an error occurs when creating the migration?
Occasionally, generating a migration may throw errors related to configurations. Make sure that the DataAccess
project is set as the starting project temporarily to handle migrations, upgrades and database loading.
Database creation and loading
After preparing the migrations, the next step is to build and load the actual database. Even when the SQL code for the database structure is ready, its implementation on the SQL server includes some additional steps that will be covered in future classes. In addition, we will learn how to perform data pre-loads to ensure better management of the computing environment during development and testing. Continue exploring this interesting path of database management!
Want to see more contributions, questions and answers from the community?