.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".

• 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.
  
  1. public class BaseDataAccess
  2. {
  3. protected string ConnectionString { get; set; }
  5. public BaseDataAccess()
  6. {
  7. }
  9. public BaseDataAccess(string connectionString)
  10. {
  11. this.ConnectionString = connectionString;
  12. }
  14. private SqlConnection GetConnection()
  15. {
  16. SqlConnection connection = new SqlConnection(this.ConnectionString);
  17. if (connection.State != ConnectionState.Open)
  18. connection.Open();
  19. return connection;
  20. }
  22. protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)
  23. {
  24. SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);
  25. command.CommandType = commandType;
  26. return command;
  27. }
  29. protected SqlParameter GetParameter(string parameter, object value)
  30. {
  31. SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);
  32. parameterObject.Direction = ParameterDirection.Input;
  33. return parameterObject;
  34. }
  36. protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)
  37. {
  38. SqlParameter parameterObject = new SqlParameter(parameter, type); ;
  40. if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)
  41. {
  42. parameterObject.Size = -1;
  43. }
  45. parameterObject.Direction = parameterDirection;
  47. if (value != null)
  48. {
  49. parameterObject.Value = value;
  50. }
  51. else
  52. {
  53. parameterObject.Value = DBNull.Value;
  54. }
  56. return parameterObject;
  57. }
  59. protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
  60. {
  61. int returnValue = -1;
  63. try
  64. {
  65. using (SqlConnection connection = this.GetConnection())
  66. {
  67. DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
  69. if (parameters != null && parameters.Count > 0)
  70. {
  71. cmd.Parameters.AddRange(parameters.ToArray());
  72. }
  74. returnValue = cmd.ExecuteNonQuery();
  75. }
  76. }
  77. catch (Exception ex)
  78. {
  79. //LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);
  80. throw;
  81. }
  83. return returnValue;
  84. }
  86. protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)
  87. {
  88. object returnValue = null;
  90. try
  91. {
  92. using (DbConnection connection = this.GetConnection())
  93. {
  94. DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);
  96. if (parameters != null && parameters.Count > 0)
  97. {
  98. cmd.Parameters.AddRange(parameters.ToArray());
  99. }
  101. returnValue = cmd.ExecuteScalar();
  102. }
  103. }
  104. catch (Exception ex)
  105. {
  106. //LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);
  107. throw;
  108. }
  110. return returnValue;
  111. }
  113. protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
  114. {
  115. DbDataReader ds;
  117. try
  118. {
  119. DbConnection connection = this.GetConnection();
  120. {
  121. DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
  122. if (parameters != null && parameters.Count > 0)
  123. {
  124. cmd.Parameters.AddRange(parameters.ToArray());
  125. }
  127. ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  128. }
  129. }
  130. catch (Exception ex)
  131. {
  132. //LogException("Failed to GetDataReader for " + procedureName, ex, parameters);
  133. throw;
  134. }
  136. return ds;
  137. }
  138. }