How can I connect to a database in ASP.NET Core without Entity Framework?
ASP.Net Core works different than ASP.Net, so you need to map the connection strings defined in appsettings.json
to a class or variable to be accessed throughout the application. Try the following approach. Create appSettings.json
:
{
"ConnectionStrings": {
"DefaultParkingConnection": "Server=DESKTOP-CD0M0C3\\SQLEXPRESS;Database=ParkingSystem2;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=sa;Password=P@ssw0rd"
}
}
Create a new class ConnectionStrings.cs
to map the connection strings defined in appSettings.json
to it:
using System;
namespace Test
{
public class ConnectionStrings
{
public string DefaultParkingConnection{ get; set; }
}
}
In Startup.cs
, write the following code:
public class Startup
{
public IConfiguration Configuration { get; }
public Startup(IConfiguration configuration)
{
this.Configuration = configuration;
}
public void ConfigureServices(IServiceCollection services)
{
//Map the configuration
var connectionSection = Configuration.GetSection("ConnectionStrings");
services.Configure<ConnectionStrings>(connectionSection );
}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
// Configure
}
}
Now in controllers, you can easily use it without creating the instance of the class:
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
namespace Test.Controllers
{
[ApiController]
[Route("api/account")]
public class AccountController : ControllerBase
{
private readonly ConnectionStrings connectionStrings;
public AccountController(IOptions<ConnectionStrings> connectionStrings)
{
this.connectionStrings = connectionStrings.Value;
}
[HttpGet, Route("test")]
public IActionResult Test()
{
return Ok("test");
}
}
}
How to connect to SQL Server from .Net Core without using Entity Framework?
If you surprised with BaseDataAccess
class format in another answer and referenced article same as me, here is well formatted example... hopefully it will save you some time
public class BaseDataAccess
{
protected string ConnectionString { get; set; }
public BaseDataAccess()
{
}
public BaseDataAccess(string connectionString)
{
this.ConnectionString = connectionString;
}
private SqlConnection GetConnection()
{
SqlConnection connection = new SqlConnection(this.ConnectionString);
if (connection.State != ConnectionState.Open)
connection.Open();
return connection;
}
protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)
{
SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);
command.CommandType = commandType;
return command;
}
protected SqlParameter GetParameter(string parameter, object value)
{
SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);
parameterObject.Direction = ParameterDirection.Input;
return parameterObject;
}
protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)
{
SqlParameter parameterObject = new SqlParameter(parameter, type); ;
if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)
{
parameterObject.Size = -1;
}
parameterObject.Direction = parameterDirection;
if (value != null)
{
parameterObject.Value = value;
}
else
{
parameterObject.Value = DBNull.Value;
}
return parameterObject;
}
protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
{
int returnValue = -1;
try
{
using (SqlConnection connection = this.GetConnection())
{
DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
returnValue = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
//LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);
throw;
}
return returnValue;
}
protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)
{
object returnValue = null;
try
{
using (DbConnection connection = this.GetConnection())
{
DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
returnValue = cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
//LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);
throw;
}
return returnValue;
}
protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
{
DbDataReader ds;
try
{
DbConnection connection = this.GetConnection();
{
DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
//LogException("Failed to GetDataReader for " + procedureName, ex, parameters);
throw;
}
return ds;
}
}
how to get connection string in .net core to Azure SQL without Entity Framework?
After a couple of hours testing and I found one connection string which is working for me.
string connectionString = "Data Source=server.database.windows.net;Initial Catalog=dbname;User ID=abc@def.com;Password='testing password'; Authentication='Active Directory Password'";
Hope this one can help other people who use Azure Active Directory - Universal MFA Support
using SQLLite Database without entity Framework in ASP.NET Core Project
You have it in official repo:
https://github.com/aspnet/Microsoft.Data.Sqlite
And here is an example:
http://www.bricelam.net/2015/04/29/sqlite-on-corefx.html
EDIT: adding link content in case it disappears in the future.
The provider is built on top of the System.Data.Common contract. This contract is a very small subset of the ADO.NET provider model. Using the provider should feel very natural to anyone familiar with ADO.NET.
using (var connection = new SqliteConnection("" +
new SqliteConnectionStringBuilder
{
DataSource = "hello.db"
}))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var insertCommand = connection.CreateCommand();
insertCommand.Transaction = transaction;
insertCommand.CommandText = "INSERT INTO message ( text ) VALUES ( $text )";
insertCommand.Parameters.AddWithValue("$text", "Hello, World!");
insertCommand.ExecuteNonQuery();
var selectCommand = connection.CreateCommand();
selectCommand.Transaction = transaction;
selectCommand.CommandText = "SELECT text FROM message";
using (var reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
var message = reader.GetString(0);
Console.WriteLine(message);
}
}
transaction.Commit();
}
}
Batching
The only real feature that the library adds to the native SQLite interfaces is batching. The native interfaces only support compiling and executing one statement at a time. This library implements batching in a way that should feel completely transparent. Here is an example of using batching.
using (var connection = new SqliteConnection("Data Source=hello.db"))
{
var command = connection.CreateCommand();
command.CommandText =
"UPDATE message SET text = $text1 WHERE id = 1;" +
"UPDATE message SET text = $text2 WHERE id = 2";
command.Parameters.AddWithValue("$text1", "Hello");
command.Parameters.AddWithValue("$text2", "World");
connection.Open();
command.ExecuteNonQuery();
}
Platforms:
Currently, Microsoft.Data.Sqlite works on the following platforms.
- .NET Framework
- Mono
- .NET Core
- .NET Native
- CoreCLR
- Windows Universal
Related Topics
Count() a Specfic Attribute Within a List C#
Split a Comma-Separated String With Both Quoted and Unquoted Strings
How to Convert a String Containing Am/Pm to Datetime
How to Directly Print a Report Without Going Through Crystal Reports Viewer
Check If a File Is Real or a Symbolic Link
How to Upload a Excel File to SQL Database Table Using C# Windows Form Application
Asp.Net Core API Post Parameter Is Always Null
Microsoft Azure: How to Create Sub Directory in a Blob Container
Convert Dictionary Values into Array
How to Download File With ASP.NET on Buttton'S Onclick Event
How to Check If a Windows Form Is Already Open, and Close It If It Is
Repaired Records:Cell Information from Worksheet Created from Scratch
How to Change a Image on a Button Using Windows Forms
Regular Expression for Anything But an Empty String
How to Return a File (Filecontentresult) in ASP.NET Webapi