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

Does this answer your question? [How to read connection string in .NET Core?](https://stackoverflow.com/questions/39083372/how-to-read-connection-string-in-net-core) — Charlieface, May 01 '23 at 09:25

score 0 · Accepted Answer · answered May 01 '23 at 09:05

0

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.

answered May 01 '23 at 09:05

Neil T

1,794
1
12
21

I applied the code you suggested, but nothing changed. In fact, I tried many methods, but as I said, I could not prevent this problem in any way. – prawlercode May 01 '23 at 09:21
But this time error says Invalid object name 'Users'. – prawlercode May 01 '23 at 09:22
1

You say that "nothing changed" but then you say that the error has changed. This means something has changed! It sounds like the code I suggested has worked exactly as intended, but now you have a completely different problem, probably in the query `select count(*) from Users...`. If you'd like some help with that, I suggest you accept this answer and ask a new question. – Neil T May 01 '23 at 09:25
I'm so sorry I didn't read the error in detail. – prawlercode May 01 '23 at 09:32

I have a problem with SQL Server connection string

1 Answers1