How to Connect to a Database in ASP.NET Core Without Entity Framework

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