-2

I am trying to connect to a database for my application in .NET6. My connection string is as follows

"ConnectionStrings": {
        "Default": "Server=localhost;Database=Tim;User Id=SA;Password=*****;TrustServerCertificate=true"

    }

it is defined in appsettings.json.

In azure data studio, I have a database named Tim which I am trying to access

enter image description here

But when I run my application I get the following error

SqlException: Invalid object name 'Tim'.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__209_0(Task<SqlDataReader> result)
System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot, Thread threadPoolThread)
Dapper.SqlMapper.QueryAsync<T>(IDbConnection cnn, Type effectiveType, CommandDefinition command) in SqlMapper.Async.cs
DataLibrary.DB.SqlDb.LoadData<T, U>(string storedProcedure, U parameters, string connectionStringName)
RPDemoApp.Pages.Orders.CreateModel.OnGet() in Create.cshtml.cs
+
            var food = await _foodData.GetFood();
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory+NonGenericTaskHandlerMethod.Execute(object receiver, object[] arguments)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

What mistake am I making?

EDIT: Here is my Service collector code


builder.Services.AddSingleton(new ConnectionStringData
{
    SqlConnectionName= "DefaultConnection"
});

builder.Services.AddSingleton<IDataAccess, SqlDb>();
builder.Services.AddSingleton<IFoodData, FoodData>();
builder.Services.AddSingleton<IOrderData, OrderData>();

Here is GetFood method, It uses a store procedure to get data from database.

 public Task<List<FoodModel>> GetFood()
        {
            return _dataAccess.LoadData<FoodModel, dynamic>("dbo.spFood_All",
                                                            new { },
                                                            _connectionString.SqlConnectionName);

        }
    ```
Tallion 22
  • 141
  • 1
  • 11

1 Answers1

1

In your settings file change Connection to DefaultConnection, Source to Data Source and Database to Initial Catalog as follows:

"ConnectionStrings": {
    "DefaultConnection": "Data Source=localhost;Initial Catalog=Tim;User Id=sa;Password=Password;TrustServerCertificate=true;"
  },

UPDATE:

The error is 100% related to the stored procedure code. The select is referencing a database instead of a table.

Please change the stored procedure select statement to use the table name.

You can also use [DBNAME].[SCHEMA].[TABLENAME] so something like:

CREATE PROCEDURE [dbo].[spFood_All] 
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT
        [ID], [Title], [Descrip], [Price] 
    FROM
        Tim.dbo.[TABLENAME]; 
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17