Connect to a database in ASP.NET Core without Entity Framework? | Asp.net core connect to sql server without entity framework

by

Recently we are working on a project, in which we need to create a Rest Web API using ASP.NET Core but we don’t want to use the Entity Framework core. we are using an existing database that contains some custom views functions and stored procedures and we do not want to use EF Core.

So in this article we will learn How we can  connect to a database in ASP.NET Core without Entity Framework? and How we can read database connection string from appsetting.json and inject it in Controllers.


we google this topic and we can’t find anything related to it, so we decided to write an article on that topic so that other developers can take help from this post. who want to use ado .net instead of EF core.

We can inject IConfiguration in our API controller, then IConfiguration and then we can get the connection string.

Read full article 

Like below code:

[Route("api/[controller]")]
    [ApiController]
    public class TeamPlayerController : ControllerBase
    {
        private readonly string connectionstr;
        public IConfiguration _configuration { get; }
        public TeamPlayerController(IConfiguration configuration)
        {
            _configuration = configuration;
            connectionstr = _configuration.GetConnectionString("DefaultConnection");
        }
    }

Let’s take an example:

Let’s say we want to get all record from a table using ADO .Net and we have defined the connection string in appsetting.json.
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=SQLEXPRESS01;Initial Catalog=TestDb;User ID=sa;Password=sa@1234"
  },
  "AllowedHosts": "*"
}


Read full article 

we are not going to use the Entity Framework and we want to connect to the database using this connection string in appsetting.json. 

 
The first issue we face is getting the database connection string from the appsetting.json, we will import the configuration to archive this. 
 And then we will use System.Data and using System.Data.SqlClient install from NuGet and Microsoft.Extensions.Configuration to access database, basically, we are writing old ado .net code. 
 In a controller, it look like this:

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using RestAPI.Model;
using System.Data.SqlClient;


namespace RestAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TeamPlayerController : ControllerBase
    {
        private readonly string connectionstr;
        public IConfiguration _configuration { get; }
        public TeamPlayerController(IConfiguration configuration)
        {
            _configuration = configuration;
            connectionstr = _configuration.GetConnectionString("DefaultConnection");
        }

        // GET: api/<TeamPlayerController>
        [HttpGet]
        public async Task<ActionResult<IEnumerable<TeamPlayerModel>>> Get()
        {
            List<TeamPlayerModel> teamPlayers = new List<TeamPlayerModel>();
            try
            {
                string query = "SELECT * FROM TeamPlayers";
                using (SqlConnection con = new SqlConnection(connectionstr))
                {
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        await con.OpenAsync();
                        using (SqlDataReader sdr = await cmd.ExecuteReaderAsync())
                        {
                            while (await sdr.ReadAsync())
                            {
                                teamPlayers.Add(new TeamPlayerModel
                                {
                                    Id = Convert.ToInt32(sdr["Id"]),
                                    Name = Convert.ToString(sdr["Name"]),
                                    Email = Convert.ToString(sdr["Email"]),
                                    ContactNo = Convert.ToString(sdr["ContactNo"]),
                                    TeamName = Convert.ToString(sdr["TeamName"]),
                                    Address = Convert.ToString(sdr["Address"]),
                                });
                            }
                        }
                       await con.CloseAsync();
                    }
                }
            }
            catch (Exception ex)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, ex);
            }

            return Ok(teamPlayers);
        }
    }
}

Leave a comment