Share via

Exploring DataTable and SqlDBAdapter in ASP.NET Core 2.0

  • Article

.NET Core 2.0 Preview was released May 2017.  Great timing as one of my customer was inquiring re DataTable and SqlDBADapter support in ASP.NET Core.  Instructions to wire it up are below.

First, pull down my ASP .NET Core 1.1 SqlClientPlay11 project from https://github.com/jhealy/aspdotnetcore.  Instructions for building up this project are also at the previous post in this loosely strung together series at https://blogs.msdn.microsoft.com/devfish/?p=2846 .

We are going to upgrade this project to .NET Core 2.0 and use a DataTable and SqlDBAdapter to access Sql Server from ASP .NET Core.

To use a DataTable and SqlDBAdapter we must be on .NET Standard 2.0. Note .NET Standard 2.0 is in a preview state (warning) but is at a good place to start using it for development. If you take a peek at https://apisof.net/catalog/System.Data.DataSet you'll see the inclusion in this great api reference site.

VS2017 15.2 (aka release) does not support .NET Standard 2.0. We must use VS2017 Preview 15.3 to target DataTable - see 5/11 comment here - https://github.com/dotnet/core/issues/640 .

VS2017 Preview will run side by side with VS2017 15.2, so can keep our regular dev tools in 'release state'. You will wind up with a side by side install that looks like the following in the VS2017 Installer, as well as two separate icons to work with.

Visual Studio 2017 SxS

Once VS Preview 15.3 is installed your projects will have .NET CORE 2.0 available as a target platform in the Target Framework drop down of the Application tab under the project's Properties dialog.

Target Frameworks

We need to migrate our 1.1 project to .NET Core 2.0. Perform the following steps to migrate our previous 1.1 project to 2.0.

Set our Target Framework to 2.0 via the Target Framework drop down in Project Properties

Manage Nuget packaged, click on updates.  Take them all.
Turn on "include prerelease"
Take the updates.

nuget-update

Build the project, give it a run and a blank web project is good to go, and should work as it did before.

Note Intellisense for newly added methods is probably a bit messed up at this point. You may see things like DataTable not being highlighted as a keyword,  or missing types on compilation. Close Visual Studio and reopen it. Intellisense should be restored.

At this point I add a method to my SqlHelper to fetch customers via SqlDataAdapter, wire it into my CustomerRepository, and then call from my UI.   Below you can see my SqlDataAdapter and DataTabe functions.

 public static DataTable ExecuteDataTable(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
 {
 DataTable dt = new DataTable();
 // just doing this cause dr.load fails
 dt.Columns.Add("CustomerID");
 dt.Columns.Add("CustomerName");
 SqlDataReader dr = ExecuteReader(conn, cmdType, cmdText, cmdParms);
 // as of now dr.Load throws a big nasty exception saying its not supported. wip.
 // dt.Load(dr);
 while (dr.Read())
 {
 dt.Rows.Add(dr[0], dr[1]);
 }
 return dt;
 }

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;
 }

Again, code is available for download at https://github.com/jhealy/aspdotnetcore . Pull the samples down, and carpe diem.

Have fun!

Comments