在ASP.NET Core使用SqlClient

Tool：Visual Studio Enterprise 2017 15.3
.NET Core 2.0
OS:Windows Server 2016 Datacenter

.NET Core 2.0版已經可以使用許多以往在.NET Framework 4.X版System.Data.SqlClient命名空間下資料存取的相關類別了，例如DataTable、DataReader等等，可以讓你從ASP.NET Core直接透過System.Data.SqlClient命名空間下的類別存取資料庫資料。以往怎麼寫，現在就怎麼寫。

使用VS建立專案時，選擇ASP.NET Core Web Application:

選擇上方 ASP.NET Core 2.0，建立空白的網站專案:

然後在專案中，把你記得的ADO.NET 程式加上來:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;

namespace DemoSql {
  public class Startup {
    public void ConfigureServices( IServiceCollection services ) {
    }

    public void Configure( IApplicationBuilder app , IHostingEnvironment env ) {
      if ( env.IsDevelopment( ) ) {
        app.UseDeveloperExceptionPage( );
      }

      app.Run( async ( context ) => {
        string result = "";
        using ( SqlConnection connection = new SqlConnection( @"Server=.\sqlexpress;database=northwind;integrated security=sspi;" ) ) {
          connection.Open( );
          SqlCommand command = new SqlCommand( $"SELECT * FROM Region " , connection );
          DataTable dt = new DataTable( );
          dt.Load( command.ExecuteReader( ) );
          result = $@"Total Count : {dt.Rows.Count}
                      First : {dt.Rows[0][0].ToString( )} - {dt.Rows[0][1].ToString( )}";
        }
        await context.Response.WriteAsync( result );
      } );
    }
  }
}

執行結果 :