Tutorial: Create a Microsoft SQL Server Database and Connect it to an ASP.NET Core MVC Project

This tutorial will show you how to create a Microsoft SQL Server database and incorporate it into an ASP.NET Core MVC project. Instead of using the file database generated by Visual Studio and Entity Framework, we will create a new database and connect it to our DemoProject. 

The Database Created by Visual Studio

Visual Studio automatically creates a database file when you create a new ASP.NET Core Web App project using Visual Studio and select a default authentication type for individual accounts. This is what we have done in the first tutorial of this Demo Project.

This database file is connected to our application in the appsettings.json file located in your project's main directory. Open this JSON file and find the connection string. We will replace this connection string with one to a database we create.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-DemoProject-9BF863CD-3CEB-4FA4-B29B-4EF09A581CD7;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Infor ma,"on",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Create the DemoProject Database

Open Microsoft SQL Server Management Studio.

Right-click on the Databases object and select New Database... in the popup window. 

In the New Database window, name this new database DemoProject and click OK.

The DemoProject database has been created.

This DemoProject database does not have tables. We will add some later. 

Now, let's go back to visual studio and connect this database to our project.

Connecting a SQL Server Database to a Project in Visual Studio

In Visual Studio, open this appsettings.json file if you have not done so already. Find the default connection string. Let's replace this connection string with one to a database we created.

Open the SQL Server Object Explorer in Visual Studio. This window may already be open and in a tab on the left side of your screen. If it is not there, click on View in the Visual Studio toolbar and select SQL Server Object Explorer.

Right-click on SQL Server and select Add SQL Server…

In the connect window, navigate to the server that contains the database you just created. You likely made your database on your local computer, so select the computer's name. For me, I will choose Arczis-Web-Tech, the name of my laptop.

For authentication, I am using Windows authentication on my local computer.

In the database name drop-down control, the database you just created is named DemoProject.

Click the Connect button.

The demo project database you created in SQL Server Management Studio will now display in Visual Studio’s SQL Server Object Explorer.

Right-click on your DemoProject in the Object Explorer. Select Properties in the popup window.

The properties associated with your new database will display in the Properties window of Visual Studio. This window will most likely be on the right side of your screen. We want to access the Connection string in this Properties window. If you do not see the properties of the DemoProject, click on one of the icons at the top of the Properties window to refresh the connection.

Navigate to the row containing the connection string. Double-click on the connection string field to the label's right and copy it.

Paste the copied connection string in the appsettings.json file, replacing the existing connection string.

{
  "ConnectionStrings": {
    "DefaultConnection": "Source=ARCZIS-WEB-TECH;Initial Catalog=DemoProject;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Save the appsetting.json file.

Build your application by right-clicking on the project's name in the Solution Explorer window.

Open Package Manager Console in Visual Studio.

Type Update-Database at the Package Manager Console prompt. This will update your new database with the Identity Schema from the original database created by Visual Studio. 

The migration data from the Data folder in your project has been used to create the database tables in your new table.

Your application and its new database are ready to use.

---