다음을 통해 공유

Data access via SqlDataReader in ASP.NET Core 1.1 and 2.0

  • 아티클

I'd been working with a client who wanted to access SqlServer directly from an
ASP.NET Core application. I'd been using Entity Framework for my ASP.NET Core access so I had to
dig in a bit on this one, especially for his DataTable, DataSet, and SqlDbAdapter questions ( see part2 of this article ).  Good news, if you know ADO, you know how to access SQL Server from ASP.NET Core.  Let's explore wiring this up below.

For the first challenge I simply wanted to execute a direct SQL against the database. SqlServer access is inASP.NET Core 1.1 via the System.Data.SqlClient nuget package.   Let's walk the process to spin up an ASP.NET Core app using SqlClient.

Code to access Sql Server form ASP.NET Core is fairly straight up. To access a SqlDbReader, for example, is the stock ADO statement, as shown below.

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

Full code for this is at https://github.com/jhealy/aspdotnetcore  . ASP.NET Core 1.1 and 2.0 versions are available.

STEP BY STEP

  • Build up a Sql Server and install Northwind. Yes, Northwind. Sql Server
    install steps, and configuring Northwind are a bit beyond the scope of this article. Old school!  But
    Northwind download is at https://www.microsoft.com/en-us/download/details.aspx?id=23654 .
    The .mdb didn't like Sql Express 2016, but the scripts to create Northwind ran fine via Sql Management Studio.
  • Create a connection string to your Sql Server.
    https://www.connectionstrings.com/sql-server/  is a great resource to figure this out.
    My connection string is
    "Server=DESKTOP-PPD5M0F\SQLEXPRESS;Database=northwind;User Id=donald_reader;Password=yessurethisismypassword;" .
  • Visual Studio 2017 Access - Open up Visual Studio 2017 15.2 or later. I strongly recommend you have all the updates
    installed. My version of VS2017 for this exercise is 15.2 (26430.6) release.
  • Create a new ASP.NET Core Project
    File > New > Templates > Visual C# > .NET Core > ASP.NET Core Web Application (.NET Core)
    I named mine sqlclientplay11, and the solution sqlclientplay11SLN. The "11" referencing framwwork 1.1.
  • Select "Empty" web project.
  • Leave docker support unchecked, and auth to "no authenticate".
  • Open Startup.cs, change "Hello World!" to "Hello devfish".
  • Give it a run via F5 (debug>go). You should see output with "hello devfish".  Woohoo.  We've got ASP.NET Core 1.1 up and running.
  • Add System.Data.Common and System.Data.SqlClient to the project's nuget packages. This add's direct Sql Server access to our project.
  • Add a file called SqlHelper.cs to your project. Direct file link : https://github.com/jhealy/aspdotnetcore/blob/master/SqlClientPlay11/SqlClientPlay11/SQLHelper.cs
  • Add Customer.cs to your project. Direct file link:  https://github.com/jhealy/aspdotnetcore/blob/master/SqlClientPlay11/SqlClientPlay11/Customer.cs
  • Add CustomersRepository.cs to your project.  Direct file link: https://github.com/jhealy/aspdotnetcore/blob/master/SqlClientPlay11/SqlClientPlay11/CustomersRepository.cs
  • Modify startup.cs as follows in the snippet at the end of this document.
  • Give the project a run and you should hopefully see ASP.NET Core using SqlClient.

aspdnc11-output

I'll show DataTables and SqlDbAdapter inside ASP.NET Core in my next post on this topic. Feedback welcome...

Startup.cs

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

app.Run(async (context) =>
 {
 await context.Response.WriteAsync("Hello devfish!");
 await context.Response.WriteAsync("SQLCLIENT DIRECT SQL");
 await context.Response.WriteAsync(CustomerTable());
 });
 }

public string CustomerTable()
 {
 string retval;
 string header = @"SqlDbReader Fetch";
 string footer = @"done...";

StringBuilder sbBody = new StringBuilder(1024);
 List list = CustomersRepository.GetAllCustomers();
 foreach (Customer c in list)
 {
 sbBody.AppendFormat(@"{0}::{1}
 ", c.CustomerId, c.CompanyName);
 }
 retval = $@"{header}
 {sbBody}
 {footer}";
 return retval;
 }