I am attempting to create an ASP.NET Core Web API without using EF Core. Instead, I would like to use System.Data.SQLClient to connect to SQL server, and query it using plain old SQL. That said, I'm having a bit of trouble figuring out how to establish the connection. I've found a lot of tutorials online that address how to do this with EF. But, I'm a bit perplexed as to how it's done without it.
Add a comment
|
2 Answers
You do it exactly the same way as you would in any .net application:
using (var conn = new System.Data.SqlClient.SqlConnection(connectionString)) {
conn.Open();
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = "SELECT 'hello world'";
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
...
}
}
}
}
You will find that some parts of asp.net core require EF, like the identity claims stuff, so you will need to handle your own authentication and won't be able to use their security attributes and the like. I've gone this route on several asp.net core projects with no regrets
7 Comments
toolshed
Thanks for the response, Tim. I was moreso referring to how I go about integrating that with the Web API framework. I noted that there is a Configure method in Startup.cs, but it seems to rely heavily on dependency injection, and this is where I'm struggling. My apologies for the confusion, I should have been more clear that the question had two parts.
Tim
Are you asking how to build and add a service to DI that would be a factory for SqlConnection objects to try and match the DI pattern of asp.net core? What specifically isn't working for you?
toolshed
My issue is that I have an existing database that I simply want to query, and push a result to a REST API. My understanding from the tutorials is that I am expected to initiate the DB connection in the
ConfigureServices method in Startup.cs and then execute my commands in a controller class. Am I misunderstanding how this paradigm has been designed?
user1234433222
Make sure you are using the latest version of dotnet core or the sqlclient wouldn't be available
Tim
The analog is would be to load your connection string in ConfigureServices, write your own service that implements an interface that creates a SqlConnection object based on that connection string, and have your controller method get a reference to that service via DI and use it to creat SqlConnections without having to be aware of the connection string... but you don't have to do it that way
|
You must use SqlConnection and SqlCommand classes. From the documentation
private static void CreateCommand(string queryString, string connectionString)
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
CreateCommand("insert into...", "[your connection string]");
Aditionally, Dapper is a good option for accessing data, is an object mapper that extends IDbConnection, so you don't have to worry about object creation. From dapper's docummentation
public class Dog
{
public int? Age { get; set; }
public Guid Id { get; set; }
public string Name { get; set; }
public float? Weight { get; set; }
public int IgnoredProperty { get { return 1; } }
}
var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });
Comments
