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,

Please refer to MSDN for more details on Database Providers.

Create Data Access Project

code

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

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.

  1. public class TestDataAccess : BaseDataAccess
  2. {
  3. public TestDataAccess(string connectionString) : base(connectionString)
  4. {
  5. }
  7. public List<Test> GetTests()
  8. {
  9. List<Test> Tests = new List<Test>();
  10. Test TestItem = null;
  12. List<DbParameter> parameterList = new List<DbParameter>();
  14. using (DbDataReader dataReader = base.ExecuteReader("Test_GetAll", parameterList, CommandType.StoredProcedure))
  15. {
  16. if (dataReader != null && dataReader.HasRows)
  17. {
  18. while (dataReader.Read())
  19. {
  20. TestItem = new Test();
  21. TestItem.TestId = (int)dataReader["TestId"];
  22. TestItem.Name = (string)dataReader["Name"];
  24. Tests.Add(TestItem);
  25. }
  26. }
  27. }
  28. return Tests;
  29. }
  31. public Test CreateTest(Test Test)
  32. {
  33. List<DbParameter> parameterList = new List<DbParameter>();
  35. DbParameter TestIdParamter = base.GetParameterOut("TestId", SqlDbType.Int, Test.TestId);
  36. parameterList.Add(TestIdParamter);
  37. parameterList.Add(base.GetParameter("Name", Test.Name));
  39. base.ExecuteNonQuery("Test_Create", parameterList, CommandType.StoredProcedure);
  41. Test.TestId = (int)TestIdParamter.Value;
  43. return Test;
  44. }
  45. }
  47. public class Test
  48. {
  49. public object TestId { get; internal set; }
  50. public object Name { get; internal set; }
  51. }