Exploring .NET Core | .NET Core 1.0 Connecting SQL Server Database

  • Article

Let's discuss how to connect to databases. In this session, we will connect to a SQL Server Database from a .NET Core class library and we will use a Microsoft SQL Server Database Provider named "Microsoft.EntityFrameworkCore.SqlServer". These are simple steps and can be performed in any project, but for simplicity and continuity of our work, we are going to use the project created in our discussion Welcome to ASP.NET Core 1.0 MVC.

It is important to note that .NET Core does not have DataSet, DataTable, and their related objects as of this writing. But we do have all of the core features like Connection, Command, Parameter, DataReader,  and other related objects.

.NET Core Database Provider

A .NET Core application can connect to a database through a Database Provider. Database Providers are database connectivity implementations for a specific technology, they and are extensions of the System.Data.Common package.  At the moment .NET Core provides following Database Providers:

  • Microsoft SQL Server
  • SQLite
  • PostgreSQL
  • Microsoft SQL Server Compact Edition
  • IBM Data Servers
  • InMemory
  • MySQL (Under Development)
  • Oracle (Under Development)

Please refer to MSDN for more details on Database Providers.

Create a Data Access Project

  • Open an existing Solution in Visual Studio 2015.
  • Now add a new Client Library .NET Core project in the Solution.
    • Open the Add New Project Screen through the Solution Context Menu >> Add >> New Project Or File >> New >> Project.
    • Select the Class Library (.NET Core) Template through Installed >> Templates >> Visual C# >> .NET Core.
    • Name the project “WebApplicationCore.NetCore.DataAccess”.
    • Set a suitable location such as “C:\ASP.NET Core\Welcome To .NET Core 1.0\ ASP.NET Core” (selected by default to solution root).
    • Click the OK Button.
  • This will create a new class library project.
  • Add a Reference to Microsoft.EntityFrameworkCore.SqlServer using one of following methods:
    • Open the Package Manger Console through Tools >> NuGet Packet Manger >> Package Manger Console and run the install command "Install-Package Microsoft.EntityFrameworkCore.SqlServer" for WebApplicationCore.NetCore.DataAccess project.
    • Open the NuGet Manager through WebApplicationCore.NetCore.DataAccess Reference context menu >> References >> Manage NuGet  Packages. in the Browse tab, search for "Microsoft.EntityFrameworkCore.SqlServer" and install.
  • Rename Class1 as BaseDataAccess and add the following implementation to connect to a SQL Server Database:
public class BaseDataAccess
 {
    protected string ConnectionString { get; set; }
 
    public BaseDataAccess()
    {
    }
 
    {
    public BaseDataAccess(string connectionString)
    private SqlConnection GetConnection()
        this.ConnectionString = connectionString;
    }
 
    {
        if (connection.State != ConnectionState.Open)
        SqlConnection connection = new SqlConnection(this.ConnectionString);
            connection.Open();
        return connection;
        SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);
    }
 
    protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)
    {
    protected SqlParameter GetParameter(string parameter, object value)
        command.CommandType = commandType;
        return command;
    }
 
    {
        parameterObject.Direction = ParameterDirection.Input;
        SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);
        return parameterObject;
    }
 
        SqlParameter parameterObject = new SqlParameter(parameter, type); ;
    protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)
    {
 
        if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)
        {
    }
            parameterObject.Size = -1;
        }
 
        parameterObject.Direction = parameterDirection;
 
        if (value != null)
        {
            parameterObject.Value = value;
        }
        else
        {
            parameterObject.Value = DBNull.Value;
        }
 
        return parameterObject;
 
                DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
    protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
    {
        int returnValue = -1;
 
        try
        {
            using (SqlConnection connection = this.GetConnection())
            {
 
                if (parameters != null && parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
 
            using (DbConnection connection = this.GetConnection())
                returnValue = cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            //LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);
            throw;
        }
 
        return returnValue;
    }
 
    protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)
    {
        object returnValue = null;
 
        try
        {
            {
        }
                DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);
 
                if (parameters != null && parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
 
                returnValue = cmd.ExecuteScalar();
            }
        }
        catch (Exception ex)
        {
            //LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);
            throw;
 
        return returnValue;
    }
 
                ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
    {
        DbDataReader ds;
 
        try
        {
            DbConnection connection = this.GetConnection();
            {
                DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
                if (parameters != null && parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
 
            }
        }
        catch (Exception ex)
        {
 }
            //LogException("Failed to GetDataReader for " + procedureName, ex, parameters);
            throw;
        }
 
        return ds;
    }

BaseDataAccess 

BaseDataAccess is a helper class which encapsulates all of the implementation details to connect and fetch data. It will not only help us to maintain database connectivity related code separately but will also facilitate the easy replacement of a  SQL Database Provider with any other Data Provider as may be required. We have explicitly returned bases classes DbConnection, DbCommand, DbParameter and DbDataReader instead of SqlConnection, SqlCommand, SqlParameter and SqlDataReader to abstract  database connectivity from implementation code. In this way we just need to change BaseDataAccess to target to some other database. We have following Components in this class:

  • ConnectionString
  • GetConnection
  • GetCommand
  • GetParameter
  • GetParameterOut
  • ExecuteNonQuery
  • ExecuteScalar
  • GetDataReader

ConnectionString

ConnectionString holds the connection string. We can either initialize directly from configuration via code or we can initialize it through a parameterized constructor. We will initialize it with following value: "Server=SqlServerInstanceName;Database=DatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true". 

GetConnection

GetConnection creates a new connection of type SqlConnection and returns it after opening.

GetCommand

GetCommand creates a new command of type SqlCommand according to specified parameters.

GetParameter

GetParameter creates a new parameter of type SqlParameter and initializes it with provided value.

GetParameterOut

GetParameterOut creates a new parameter of type SqlParameter with the parameter direct set to Output type.

ExecuteNonQuery

ExecuteNonQuery initializes the connection, creates a command, and then executes the ExecuteNonQuery method of the command object. Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. Please refer to MSDN for more details about SqlCommand.ExecuteNonQuery.

ExecuteScalar

ExecuteScalar initializes the connection, creates a command, and then executes the ExecuteScalar method of the command object. It executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. Please refer to MSDN for more details about SqlCommand.ExecuteScalar.

ExecuteReader

ExecuteReader initializes the connection, creates a command, and then executes ExecuteReader method of the command object. It provides a way of reading a forward-only stream of rows from a SQL Server database. We have explicitly omitted using a block for connection as we need to return a DataReader with an open connection state. Now the question arises as to how will we handle closing this connection. For this, we have created a DataReader with "CommandBehavior.CloseConnection." This means that the connection will be closed when the related DataReader is closed.  Please refer to MSDN for more details about SqlCommand.ExecuteReader and SqlDataReader.

Using BaseDataAccess 

We recommend that you use BaseDataAccess as a base class for other classes; ideally your actual DataAccess component. If you think you don't need a full DataAccess layer, you can make this a concrete class by removing the abstract keyword from the declaration, and also change its protected methods to public/internal necessary. 

public class TestDataAccess : BaseDataAccess
{
    public TestDataAccess(string connectionString) : base(connectionString)
    {
    }
 
    public List<Test> GetTests()
    {
        List<DbParameter> parameterList = new List<DbParameter>();
        List<Test> Tests = new List<Test>();
        Test TestItem = null;
 
             
        using (DbDataReader dataReader = base.ExecuteReader("Test_GetAll", parameterList, CommandType.StoredProcedure))
        {
            if (dataReader != null && dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    TestItem = new Test();
        }
                    TestItem.TestId = (int)dataReader["TestId"];
                    TestItem.Name = (string)dataReader["Name"];
 
                    Tests.Add(TestItem);
                }
            }
        return Tests;
    }
 
        parameterList.Add(TestIdParamter);
    public Test CreateTest(Test Test)
    {
        List<DbParameter> parameterList = new List<DbParameter>();
 
        DbParameter TestIdParamter = base.GetParameterOut("TestId", SqlDbType.Int, Test.TestId);
 }
        parameterList.Add(base.GetParameter("Name", Test.Name));
 
        base.ExecuteNonQuery("Test_Create", parameterList, CommandType.StoredProcedure);
 
        Test.TestId = (int)TestIdParamter.Value;
 
        return Test;
 }

public class Test  
{  
    public object TestId { get; internal set; }  
    public object Name { get; internal set; }  
}

Connection String from Configurations

If we are interested in reading our Connection String from configuration files then we may add a reference to Microsoft.Extensions.Configuration.Abstractions and define a Constructor with an IConfiguration parameter to get the connection string from the configuration.

public BaseDataAccess(IConfigurationRoot configuration)
{
    this.ConnectionString = configuration["ConnectionStrings:DefaultConnection"];
}