Let's discuss how to connect to databases. In this session, we will connect to SQL Server Database from .NET Core class library and we will use Microsoft SQL Server Database Provider named as "Microsoft.EntityFrameworkCore.SqlServer". Although, 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 related objects anymore as of writing. But we 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 Database Provider. Database Provider is database connectivity implementation for specific technology and are an extension of 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 Data Access Project

  • Open existing Solution in Visual Studio 2015.
  • Now add new Client Library .NET Core project in Solution.
    • Open Add New Project Screen through Solution Context Menu >> Add >> New Project Or File >> New >> Project.
    • Select Class Library (.NET Core) Template through Installed >> Templates >> Visual C# >> .NET Core.
    • Name project as “WebApplicationCore.NetCore.DataAccess”.
    • Set suitable location as “C:\ASP.NET Core\Welcome To .NET Core 1.0\ ASP.NET Core” (selected by default to solution root).
    • Click OK Button.
  • It will create a new class library project.
  • Add Reference to Microsoft.EntityFrameworkCore.SqlServer using one of following methods:
    • Open Package Manger Console through Tools >> NuGet Packet Manger >> Package Manger Console and run install command "Install-Package Microsoft.EntityFrameworkCore.SqlServer" for WebApplicationCore.NetCore.DataAccess project.
    • Open NuGet Manager through WebApplicationCore.NetCore.DataAccess Reference context menu >> References >> Manage NuGet  Packages. in Browse tab search for "Microsoft.EntityFrameworkCore.SqlServer" and install.
  • Rename Class1 as BaseDataAccess and add required implementation to connect to 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 the implementation to connect and fetch data. It will not only help us to maintain database connectivity related code separately but will also facilitate to easily replace SQL Database Provider with any other Data Provider as per requirements. We have explicitly returned bases classes DbConnection, DbCommand, DbParameter and DbDataReader instead of SqlConnection, SqlCommand, SqlParameter and SqlDataReader to abstract  database connectivity from implementer. In this way, we have to just 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 configurations by code or we can also initialize it through 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 SqlConnection type and returns it after opening.

GetCommand

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

GetParameter

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

GetParameterOut

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

ExecuteNonQuery

ExecuteNonQuery initializes the connection, command and executes 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, command and executes  ExecuteScalar method of the command object. 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, command and executes ExecuteReader method of the command object. Provides a way of reading a forward-only stream of rows from an SQL Server database. We have explicitly omitted using a block for connection as we need to return DataReader with open connection state. Now the question arises that how will we handle connection close open, for this, we have created DataReader with "CommandBehavior.CloseConnection", which means, a connection will be closed as related DataReader is closed.  Please refer to MSDN for more details about SqlCommand.ExecuteReader and SqlDataReader.

Using BaseDataAccess 


We may recommend to use BaseDataAccess as a base class of any other class, ideally your actual DataAccess component. If you think, you don't need full DataAccess layer, you can make this concrete class by removing an abstract keyword from the declaration  and also make its protected methods to public/internal as per requirements. 

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 to read Connection String from configurations then we may add the reference to Microsoft.Extensions.Configuration.Abstractions and define a Construct with IConfiguration type of parameter to get the connection string from the configuration.

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