.NET Core 1.0 Connecting SQL Server Database

Let's discuss how to connect to the 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, for simplicity and continuity of our work, we are going to use the project created in our discussion Getting Started With ASP.NET Core 1.0 MVC.

It is important to note that .NET Core does not have DataSet, DataTable, and related objects any more as of this 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 a Database Provider. Database providers are database connectivity implementation for specific technologies and are an extension of System.Data.Common package. At the moment, .NET Core provides the following Database providers,

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

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 the required implementation to connect to SQL Server Database.
  

  public class BaseDataAccess
  {
     protected string ConnectionString { get; set; }
  
     public BaseDataAccess()
     {
     }
  
     public BaseDataAccess(string connectionString)
     {
         this.ConnectionString = connectionString;
     }
  
     private SqlConnection GetConnection()
     {
         SqlConnection connection = new SqlConnection(this.ConnectionString);
         if (connection.State != ConnectionState.Open)
             connection.Open();
         return connection;
     }
  
     protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)
     {
         SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);
         command.CommandType = commandType;
         return command;
     }
  
     protected SqlParameter GetParameter(string parameter, object value)
     {
         SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);
         parameterObject.Direction = ParameterDirection.Input;
         return parameterObject;
     }
  
     protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)
     {
         SqlParameter parameterObject = new SqlParameter(parameter, type); ;
  
         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;
     }
  
     protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
     {
         int returnValue = -1;
  
         try
         {
             using (SqlConnection connection = this.GetConnection())
             {
                 DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
  
                 if (parameters != null && parameters.Count > 0)
                 {
                     cmd.Parameters.AddRange(parameters.ToArray());
                 }
  
                 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
         {
             using (DbConnection connection = this.GetConnection())
             {
                 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;
     }
  
     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());
                 }
  
                 ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);
             }
         }
         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 the data, it not only helps us to maintain the database connectivity-related code separately, but will also help to easily replace SQL Database Provider with any other Data Provider as per our requirements. We have explicitly returned base 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 the BaseDataAccess to target to some other database. We have the 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 initialize it through parameterized constructor. We will initialize it with the 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 initialize it with provided value.

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

ExecuteNonQuery

ExecuteNonQuery initializes connection, command, and executes ExecuteNonQuery method of 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 connection, command, and executes ExecuteScalar method of 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 connection, command and executes ExecuteReader method of command object. Provides a way of reading a forward-only stream of rows from a SQL Server database. We have explicitly omitted using block for connection as we need to return DataReader with open connection state. Now question raises that how will we handle connection close open, for this we have created DataReader with "CommandBehavior.CloseConnection", which means, 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 using BaseDataAccess as base class of any other class, ideally your actual DataAccess component. If you think, you don't need full DataAccess layer, then you can make this concrete class by removing abstract keyword from declaration and also make its protected methods to public/internal as per your requirements.

public class TestDataAccess : BaseDataAccess
 {
    public TestDataAccess(string connectionString) : base(connectionString)
    {
    }

    public List<Test> GetTests()
    {
        List<Test> Tests = new List<Test>();
        Test TestItem = null;

        List<DbParameter> parameterList = new List<DbParameter>();

        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;
    }

    public Test CreateTest(Test Test)
    {
        List<DbParameter> parameterList = new List<DbParameter>();

        DbParameter TestIdParamter = base.GetParameterOut("TestId", SqlDbType.Int, Test.TestId);
        parameterList.Add(TestIdParamter);
        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; }
  }