Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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
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;
}
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in