I have a problem with SQL Server connection string

Question

When I run the api from Visual Studio without a debug, I always get an error when I enter the values ​​I want in swagger.io.

System.InvalidOperationException: The ConnectionString property has not been initialized.

at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 2876 at Dapper.SqlMapper.ExecuteScalar[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 493 at API.Data_Access.DataAccess.IsEmailAvailable(String email) in C:\Users\Admin\Desktop\rent-car\CarAPI\API\DataAccess\DataAccess.cs:line 52 at API.Controllers.CarsController.CreateAccount(User user) in C:\Users\Admin\Desktop\rent-car\CarAPI\API\Controllers\CarsController.cs:line 23 at lambda_method2(Closure , Object , Object[] ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync() at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS

Accept: */*
Host: localhost:7055
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36 Edg/112.0.1722.64
:method: POST
Accept-Encoding: gzip, deflate, br
Accept-Language: tr,en;q=0.9,en-GB;q=0.8,en-US;q=0.7
Content-Type: application/json
Origin: https://localhost:7055
Referer: https://localhost:7055/swagger/index.html
Content-Length: 220
sec-ch-ua: "Chromium";v="112", "Microsoft Edge";v="112", "Not:A-Brand";v="99"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
sec-fetch-site: same-origin
sec-fetch-mode: cors
sec-fetch-dest: empty

Here is my code:

Dataacces.cs:

using API.Models;
using Dapper;
using MongoDB.Driver.Core.Configuration;
using System.Data.SqlClient;

namespace API.Data_Access
{
  public class DataAccess : IDataAccess
  {
    private readonly IConfiguration configuration;
    private readonly string DbConnection;

    public DataAccess(IConfiguration _configuration)
    {
      configuration = _configuration;
      DbConnection = configuration["connectionStrings: DBConnect"] ?? "";
    }

    public int CreateUser(User user)
    {
      var result = 0;

      using (var connection = new SqlConnection(DbConnection))
      {
        var paramaters = new
        {
          fn = user.FirstName,
          ln = user.LastName,
          em = user.Email,
          mb = user.Mobile,
          pwd = user.Password,
          blk = user.Blocked,
          act = user.Active,
          con = user.CreatedOn,
          type = user.UserType.ToString(), 
        };

        var sql = "insert into Users (FirstName, LastName, Email, Mobile, Password, Blocked, Active, CreatedOn) values (@fn, @ln, @em, @mb, @pwd, @blk, @act, @con);";

        result = connection.Execute(sql, paramaters);
      }

      return result;
    }

    public bool IsEmailAvailable(string email)
    {
      var result = false;

      using (var connection = new SqlConnection(DbConnection))
      {
        result = connection.ExecuteScalar<bool>("select count(*) from Users where Email=@email;", new { email });
      }

      return !result;
    }
  }
}

User.cs

namespace API.Models
{
  public class User
  {
    public float Id { get; set; } = 0;
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Mobile { get; set; } = string.Empty;
    public string Password { get; set; } = string.Empty;
    public bool Blocked { get; set; } = false;
    public bool Active { get; set; } = true;
    public float Fine { get; set; } = 0;
    public UserType UserType { get; set; }
    public string CreatedOn { get; set; } = string.Empty;
  }
}

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DBConnect": "Data Source=DESKTOP-NI7D77J\\SQLEXPRESS01;User ID=ahmet; Password = ********;"
  }
}

IDataAccess.cs

using API.Controllers;
using API.Models;

namespace API.Data_Access
{
  public interface IDataAccess
  {
    int CreateUser(User user);
    bool IsEmailAvailable(string email);
  }
}

Carscontroller.cs

using API.Data_Access;
using API.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace API.Controllers
{
  [Route("api/[controller]")] 
  [ApiController]
  public class CarsController : ControllerBase
  {
    private readonly IDataAccess cars;

    public CarsController(IDataAccess cars)
    {
      this.cars = cars;
    }

    [HttpPost("CreateAccount")]
    public IActionResult CreateAccount(User user)
    {
      if (!cars.IsEmailAvailable(user.Email))
      {
        return Ok("Email uygun değil");
      }

      user.CreatedOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
      user.UserType = UserType.USER;
      cars.CreateUser(user);

      return Ok("Hesap başarılı bir şekilde oluşturuldu!");
    }
  }
}

Program.cs

using API.Data_Access;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// DI
builder.Services.AddSingleton<IDataAccess, DataAccess>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Modelbase.cs

namespace API.Models
{ 
   public class ModelBase
    {

    public int Id { get; set; }

     }

}

UserType.cs

namespace API.Models
{ 
  public enum UserType
{

  USER,
  ADMIN
}

}

When I asked ChatGPT, it told me to add a connection string, but even though I added it, it always gave the same error.

The values from swagger.io

Answer 1

Your first problem is here:

DbConnection = configuration["connectionStrings: DBConnect"] ?? "";

I would suggest you start by researching how to read properties from config. Add a breakpoint or some logging after this line so you can check that you are reading the connection string correctly.

You might find that something like the following works:

DbConnection = configuration.GetValue<string>("ConnectionStrings:DBConnect");

(The exact syntax required will depend on your environment and frameworks - you don't mention these in your question.)

Also, you should probably throw an exception if the result is null or empty, since none of the rest of your code will work if you don't have a connection string.