Visual Studio 2019 ASP.NET Core Web Application

Problem

With Visual Studio it’s easy to set up a .Net Core Web Application and have it running in minutes, but how do you connect to SQL Server or Oracle, and what are the differences in the configuration?

Solution

In this tip, we will show how to create a simple .Net Core Web Application, add the required libraries to connect to SQL Server or Oracle, the effects of bad coding practices and how to resolve them.

Create the .Net Core Web Application

Open Visual Studio and select “Create a new project”:

Select the C# “ASP.NET Core Web App” and click “Next”:

Enter a name, the files location, and click “Next”.

On the “Additional Information” page, select target framework “.NET Core 3.1 (Long-term support)”, authentication type “None” which is simpler, uncheck “Configure for HTTPS” so you don’t have to install a certificate, and leave the other options unchecked for “Enable Docker” and “Enable Razor runtime compilation”, then click “Create”:

In the “Solution Explorer” pane on the top right corner, right-click the solution and select “Manage NuGet Packages for Solution”:

In the “NuGet” tab, switch to the “Browse” tab and ensure the package source is “nuget.org”:

Microsoft.Data.SqlClient is the library used to connect to SQL Server, type “SqlClient” in the search box, select it, check your project name, select the version “Latest stable 5.0.1”, click “Install”. When prompted with changes click “OK” and when prompted with the license acceptance click “I accept”:

Oracle.ManagedDataAccess.Core is the library used to connect to Oracle, type “Oracle” in the search box, select it, check your project name, select the version “Latest stable 3.21.80”, click “Install” and when prompted with changes, click “OK”:

In the Solution Explorer, double-click the “appsettings.json” file and modify the code from this:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

To this for the connection strings to SQL Server and Oracle:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SqlServerConnection": "Server=.; Database=MyDb; User ID=MyUser; Password=MyPwd; Trust Server Certificate=true;",
    "OracleConnection": "User ID=MyUser; Password=MyPwd; Pooling=true; Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
  }
}

For SQL Server we specify the server as a single dot which means the local server. There must exist a database named MyDb, a user named “MyUser” with password “MyPwd”, and it must have a login created in the database and a user in the database with SELECT permission on an existing table named T1. The last parameter “Trust Server Certificate” is required in Microsoft.Data.SqlClient, otherwise an exception is raised with the message “The certificate chain was issued by an authority that is not trusted”.

For Oracle we specify the data source in the same way we do when connecting through the Oracle tools, the only important parts are the host which is specified as “localhost”, the port which is the default 1521, and the service name “ORCL”. There must be a user named “MyUser” with password “MyPwd”, it must have been granted the “connect” permission, and it must have created a table named T1. The other parameter “Pooling” is the default “true”, but it is explicitly specified for clarity.

In the Solution Explorer again, expand the “Pages” folder and the “Index.cshtml” file, then double click “Index.cshtml” and modify the code from this:

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page"}
<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
</div>

To this, note we added code to use Html.Raw to include HTML tags within a variable named “Message”:

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page"}
<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>
        @Html.Raw(@Model.Message)
    </p>
    <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
</div>

In the Solution Explorer, double click “Index.cshtml.cs” and modify the code from this:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication2.Pages
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        public IndexModel(ILogger<IndexModel> logger)
        {
            _logger = logger;
        }
        public void OnGet()
        {
        }
    }
}

To this, note the simplicity of the code but be aware there is a bug which we will discuss later:

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Configuration;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
namespace WebApplication2.Pages 
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        public object Message { getprivate set"PageModel in C#<br/>"private IConfiguration _config;
        public IndexModel(ILogger<IndexModel> logger, IConfiguration config)
        {
            _logger = logger;
            _config = config;
        }
        public void OnGet()
        {
            Message += $"Server time is{ DateTime.Now }<br/>"new SqlConnection(_config["ConnectionStrings:SqlServerConnection""select count(1) from T1"$"{ cmd.ExecuteScalar() } records found<br/>"

Lines 12 and 21 are for the variable used to display data, lines 3, 13, 14 and 17 are to be able to use the appsettings configuration inside the code, line 22 creates the SQL Server connection, line 23 opens the connection, line 24 creates a new command, line 25 sets the command text, and line 26 updates the displayed message in the page after executing the query.

For Oracle, line 22 needs to be changed from this, everything else is the same:

DbConnection connection = new SqlConnection(_config["ConnectionStrings:SqlServerConnection"

To this:

DbConnection connection = new OracleConnection(_config["ConnectionStrings:OracleConnection"

Now we can run the project and the home page will be displayed:

If we search the SQL Server sessions with the query below:

SELECT session_id, login_time, host_name, program_name, status
  FROM sys.dm_exec_sessions
 WHERE login_name='MyUser';

This is what we will see:

Each time we refresh the page, a new session appears:

If we search the Oracle sessions with the query below:

SELECT SID, SERIAL#, STATUS, OSUSER, MACHINE, PROGRAM, LOGON_TIME, LAST_CALL_ET, STATE
  FROM V$SESSION
 WHERE USERNAME='MYUSER';

This is what we will see:

If we refresh the page, initially no new sessions appear, but after a few refreshes a lot of sessions appear:

Managing Number of Sessions

To avoid multiple sessions, any object created needs to be properly disposed. You should not just call the "Dispose" method explicitly, instead you should change the code from this:

DbConnection connection = new SqlConnection(_config["ConnectionStrings:SqlServerConnection""select count(1) from T1"$"{ cmd.ExecuteScalar() } records found<br/>"

To this:

using (DbConnection connection = new SqlConnection(_config["ConnectionStrings:SqlServerConnection"]))
{
    connection.Open();
    DbCommand cmd = connection.CreateCommand();
    cmd.CommandText = "select count(1) from T1"$"{ cmd.ExecuteScalar() } records found<br/>"

This will dispose the connection no matter if there is any exception between the brackets and will also dispose any child object created from it, in this case the command. Once that is done, only one session is created for SQL Server and Oracle no matter how many times you refresh the page, and the session ID doesn't change unless the session expires as configured at the database side.

Next Steps

Here are additional tips regarding Visual Studio for the SQL Server DBA:

Resources

Community

Subscribe

Visual Studio 2019 ASP.NET Core Web Application – SQL Server vs Oracle

Create the .Net Core Web Application

Managing Number of Sessions

Resources

Community

Subscribe