For some projects where performance makes a difference we usually use classic ADO.NET approach instead of EntityFramework or some other modern way to communicate with the database.

In this post I will try to explain how to fix some common issues with using ADO.NET in ASP.NET vNext.

If you have created ASP.NET 5 or ASP.NET vNext Class Library project (.NET Core Class Library project) and want to use ADO.NET you can have small issues.

Instead of referencing System.Data and System.Data.SqlClient you need to grab from Nuget:

System.Data.Common and System.Data.SqlClient.

Currently this creates dependency in project.json –> aspnetcore50 section to these two libraries.

 "aspnetcore50": {
            "dependencies": {
                "System.Runtime": "4.0.20-beta-22523",
                "System.Data.Common": "4.0.0.0-beta-22605",
                "System.Data.SqlClient": "4.0.0.0-beta-22605"
            }
        }

In the current Visual Studio CTP6 after this your ADO.NET still will not compile.

You will need to add a reference to System.Data.Common 1.0.0-beta1 and System.Data.SqlClient into aspnet50 section of project.json like this:

 "aspnet50": {
            "dependencies": {
                "System.Data.Common": "1.0.0-beta1",
                "System.Data.SqlClient": "1.0.0-beta1"
            }
        }

I have also created little SqlHelper that works with ASP.NET 5 :

  public sealed class SqlHelper
    {
        //Since this class provides only static methods, make the default constructor private to prevent 
        //instances from being created with "new SqlHelper()".
        private SqlHelper()
        {

        }

        private static string connectionString;
        public static string GetConnectionString()
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                var config = new Configuration()
                      .AddJsonFile("config.json")
                      .AddEnvironmentVariables();
                connectionString = config.Get("Data:DefaultConnection:ConnectionString");
            }

            return connectionString;
        }

        public static int ExecuteNonQuery(SqlConnection conn, string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = conn.CreateCommand();
            using (conn)
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = conn.CreateCommand();
            using (conn)
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }


        public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = conn.CreateCommand();
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rdr;
        }


        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = conn.CreateCommand();
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(cmd, commandParameters);
            }
        }
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter p in commandParameters)
            {
                //check for derived output value with no value assigned
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }

                command.Parameters.Add(p);
            }
        }
    }

After this working with ADO.NET inside ASP.NET vNext is pretty straightforward:

 public class LogRepository
    {
        public void Add(Log log)
        {

            var parameters = new[]
                    {
                        new SqlParameter("@JobId", log.JobId),
                        new SqlParameter("@ErrorInfo", log.ErrorInfo),
                        new SqlParameter("@DateTimeProcessedUTC", log.DateTimeProcessedUTC)
                    };

            using (var conn = new SqlConnection(SqlHelper.GetConnectionString()))
            {
                     SqlHelper.ExecuteNonQuery(
                        conn,
                        CommandType.Text,
                        @" INSERT dbo.Log ([JobId],[ErrorInfo],[DateTimeProcessedUTC])
                            SELECT @JobId, @ErrorInfo, @DateTimeProcessedUTC"
                          ,
                        parameters);

            }

        }
    }
}

If you like this article don’t forget to subscribe to this blog and make sure you don’t miss new upcoming blog posts.