If you want to make a project using .NET and MSSQL then this article is important. MSSQL or SQL server is also most compatible with C# or .NET. The information will help you to connect C# with SQL server using Code First Approach. In addition, you will also learn some commands and NuGet Packages that are important to connect a .net application to SQL Server.
How to Connect .net application to SQL Server
Follow these simple steps to Connect .net application to SQL Server.
Step 1 – Create Connection string in appsetting.json. It should look like this
“AllowedHosts”: “*”,
“ConnectionStrings”: {
“DefaultConnection”: “Server=USER\\SQLEXPRESS;Database=CMSPro;Trusted_Connection=True;TrustServerCertificate=True”
}
Step 2 – Install the NuGet Packages
Install all these NuGet Packages and make sure all of these are of the same version. In case of a version mismatch, you will keep getting error messages.
- Microsoft.EntityFrameWorkCore
- Microsoft.EntityFrameWorkCore.SqlServer
- Microsoft.EntityFrameWorkCore.Tools
You can right click on the project and select Edit Project File to see all the packages installed.
Step 3 – Create a folder named Data (you can also keep a different name if required) and add a class file as ApplicationDbContext.cs
Add a constructor like this after this the file should look like this
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
}
}
After this you also need to add the table name in the names.
For example: you want to add the table named categories using the Model name and table name as mentioned below. After this your code should look like this.
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
public DbSet<Categories> Categories { get; set; }
}
}
Step 4 – Add Service to the container in program.cs
builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString(“DefaultConnection”)));
Make sure it is before this line
var app = builder.Build();
The Final Step
Step 5 – Now EntityFramework NuGet package manager will do its magic
Click on Tools – Nuget Package Manager – Package Manager Console
This will open package manager console at the bottom
Run these commands
Update-database (this command will create a database)
If you get a message – No migrations were applied. The database is already up to date.
The above command must have created the database with at least one table – __EFMigrationsHistory
Check the ApplicationDbContext file whether you have added the database there or not.
Run the commands again mentioned below. In this AddCategoriesTable can be anything that helps you remember why this migration was done.
add-Migration AddCategoriesTable
update-database
The table will be added to the database and you can start inserting data.
If you need a custom software development you can post an inquiry for software development. For website development plans or ecommerce website development you can also visit our store.