Saturday, 10 September 2016

.NET Core 1.0 Connecting SQL Server Database

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 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 core features like Connection, Command, Paramter, DataReader and other related objects.

.NET Core Database Provider

A .NET Core application can connect to a database through Database Provider. Database Provider are database connectivity implementation for specific technology and are 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 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 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 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 initilize it throug 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 return 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 to use BaseDataAccess as base class of any other class, ideally your actual DataAccess component. If you think, you don't need full DataAccess layer, the you can make this concrete class by removing abstract keyword from 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<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; }  
  }  

Connection String from Configurations

If we are interested to read Connection String from configurations then we may add reference to Microsoft.Extensions.Configuration.Abstractions and define a Construct with IConfiguration type of parameter to get connection string from configuratuions.



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

8 comments:

  1. Thanks For the post! I have switched from EF to utilize this base class. However, I am getting a 500 error when calling the API. I am building a REST API in dotnet core.

    ReplyDelete
    Replies
    1. Also, I had to modify GetExecuteReader to be just ExecuteReader in order for this to build properly.

      Delete
    2. If you like you can take working sample from following post: http://www.ijz.today/2016/09/crud-operations-in-aspnet-core-10-mvc_8.html
      Furthermore, Error 500 means there is some internal error or unhandled exception. If you are using Visual then use following to break on exception through: Debug menu >> Windows >> Exception Settings >> Enable Common Language Runtime Exception

      Delete
    3. The line in the code section Using BaseDataAccess
      using (DbDataReader dataReader = base.ExecuteReader("Test_GetAll", parameterList, CommandType.StoredProcedure))
      should be
      using (DbDataReader dataReader = base.GetDataReader("Test_GetAll", parameterList, CommandType.StoredProcedure))

      Delete
  2. Thanks for the post..Since asp.net core do not datatable how to send table valued parameter (structured) to SQL server..Pls let me any solutions..Thanks

    ReplyDelete
  3. Thanks for this post. Why do you choose the EntityFrameworkCore package. If I have no intention of using EF, can't I just take the System.Data.SqlClient package?

    ReplyDelete
    Replies
    1. Any answer for this IJZ?

      Delete
    2. Simon, you are exactly right. "System.Data.SqlClient" is precise option, but I had plan to extend this topic and to include performance compression with EF.

      Delete