Is ADO.NET in .NET Core possible?
Most tutorials are Entity Framework with no mention of Ado.Net in .Net Core projects. I have a "legacy" database, so a EF/"Code-First" approach is not an option.
For ADO.NET connections, is the System.Data.SqlClient available to an ASP.NET Core project?
It is available when I use a .NET Framework project template but is it still available in a .NET Core project?
The existing SqlConnection and other related connections still exists within the System.Data.SqlClient namespace and should work as expected using the full framework or .NET Core.
You'll just need to add the appropriate references and using statements to include it such as through the System.Data.SqlClient namespace as seen below in your project.json file :
and then call it via the syntax you are accustomed to :
using(var connection = new SqlConnection("{your-connection-string}"))
{
// Do work here
}
So as long as you have a valid connection string to connect to your existing legacy database, you should be just fine.
Regarding ORM Usage
I also found that some people are using Dapper, a Micro-ORM replacement for Entity Framework, apparenty more flexible. It is there any advantages of using it instead ADO.NET?
These ORMs (object-relational mappers) are handy and often powerful tools that can more easily map your existing database data to specific classes and objects, which can make them easier to use (as opposed to iterating through a data reader, parsing each of your rows and building each object manually).
As far as performance goes, it ultimately depends on what you are going to be doing with your queries. ADO.NET will generally be the fastest as it a bare-bones connection to the database, however in some scenarios Dapper can actually beat it out. Entity Framework, while very useful, generally trails behind in performance, simply because it is such a large ORM.
Again - it ultimately depends on what you are doing, but all are viable options.
.NET Core 2.0 has DataSet, DataTable and SQlDataAdapter. See my answer at https://blogs.msdn.microsoft.com/devfish/2017/05/15/exploring-datatable-and-sqldbadapter-in-asp-net-core-2-0/ .
Code below works fine
public static DataTable ExecuteDataTableSqlDA(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
System.Data.DataTable dt = new DataTable();
System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
da.Fill(dt);
return dt;
}
As Joe Healy mentioned in his answer in DotNet Core 2.0 it is possible to use all System.Data features.
Add nugets:
- Microsoft.Extensions.Configuration
- Microsoft.Extensions.Configuration.Json -- to read connection string from json
- System.Data.Common
- System.Data.SqlClient
config.json example:
{
"connectionString": "your-db-connection-settings"
}
Here is a full console app example.
class Program
{
static void Main(string[] args)
{
var configuration = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("config.json", false)
.Build();
var connectionString = configuration.GetSection("connectionString").Value;
if(string.IsNullOrEmpty(connectionString))
throw new ArgumentException("No connection string in config.json");
using (var conn = new SqlConnection(connectionString))
{
var sql = "SELECT * FROM ExampleTable";
using (var cmd = new SqlCommand(sql, conn))
{
using (var adapter = new SqlDataAdapter(cmd))
{
var resultTable = new DataTable();
adapter.Fill(resultTable);
}
}
}
}
}
It is important to note that .NET Core does not have DataSet, DataTable and related objects prior to version 2.0. But before 2.0, it has all of core features like Connection, Command, Parameter, DataReader and other related objects.
You can use following calls to simplify connectivity to SQL Server through SQL Server Database Provider.
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;
}
}
Please refer to following article for more details and examples: http://www.ijz.today/2016/09/net-core-10-connecting-sql-server.html
In ADO.NET Core I do NOT use System.Data.SqlClient but I use Microsoft.Data.SqlClient. Until now, I could use all my previous coding!.