Cannot connect to sql connection string

[Raymond21775]

I'm unable to connect to BikeShop.DAL files sql connection string. See attachment. I am able to connect to accounting web app, but I need to be able to connect to BikeShop.DAL. A sample of the code is shown in customeradapter.txt file. I am running my project in visual studio 2022, to open a webpage. Any help in resolving this issue would be greatly appreciated.

 

[Skydiver]

Welcome to the forum. In the future, you should post your code in code blocks to make it easier for people to try to help you. Making them go through the extra step of downloading a separate help could easily dissuade someone and they'll just move on to the next easier post to read.

When you say that you are "unable to connect to the BikeShop DAL", what do you mean exactly? Are you getting an error or exception? If so what is the error?

Anyway, here's your code from the file attachment:

//using BikeShop1.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Dapper;


public class CustomerAdapter : ICustomerAdapter
{
    private string connectionString = @"Data Source=C:\Sqlite\BikeShop.db";
    public IEnumerable<Customer> GetAll()
    {
        string sql = "SELECT CustomerId, FirstName, LastName FROM Customer";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            return connection.Query<Customer>(sql);
        }
    }
    public Customer GetById(int id)
    {
        string sql = @"SELECT CustomerId, FirstName, LastName FROM Customer WHERE CustomerId = @CustomerId";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            return connection.QueryFirst<Customer>(sql, new { CustomerId = id });
        }
    }
    public bool InsertCustomer(Customer customer)
    {
        string sql = "INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName)";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            int rowsAffected = connection.Execute(sql, customer);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
    public bool UpdateCustomer(Customer customer)
    {
        string sql = @"UPDATE Customer SET FirstName = @FirstName, 
            LastName = @LastName WHERE CustomerId = @CustomerId";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            int rowsAffected = connection.Execute(sql, customer);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
    public bool DeleteCustomerById(int id)
    {
        string sql = "DELETE FROM Customer WHERE CustomerId = @CustomerId";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            int rowsAffected = connection.Execute(sql, new { CustomerId = id });
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

 

[Raymond21775]

Welcome to the forum. In the future, you should post your code in code blocks to make it easier for people to try to help you. Making them go through the extra step of downloading a separate help could easily dissuade someone and they'll just move on to the next easier post to read.

When you say that you are "unable to connect to the BikeShop DAL", what do you mean exactly? Are you getting an error or exception? If so what is the error?

Anyway, here's your code from the file attachment:

//using BikeShop1.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Dapper;


public class CustomerAdapter : ICustomerAdapter
{
    private string connectionString = @"Data Source=C:\Sqlite\BikeShop.db";
    public IEnumerable<Customer> GetAll()
    {
        string sql = "SELECT CustomerId, FirstName, LastName FROM Customer";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            return connection.Query<Customer>(sql);
        }
    }
    public Customer GetById(int id)
    {
        string sql = @"SELECT CustomerId, FirstName, LastName FROM Customer WHERE CustomerId = @CustomerId";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            return connection.QueryFirst<Customer>(sql, new { CustomerId = id });
        }
    }
    public bool InsertCustomer(Customer customer)
    {
        string sql = "INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName)";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            int rowsAffected = connection.Execute(sql, customer);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
    public bool UpdateCustomer(Customer customer)
    {
        string sql = @"UPDATE Customer SET FirstName = @FirstName,
            LastName = @LastName WHERE CustomerId = @CustomerId";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            int rowsAffected = connection.Execute(sql, customer);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
    public bool DeleteCustomerById(int id)
    {
        string sql = "DELETE FROM Customer WHERE CustomerId = @CustomerId";
        using (SqliteConnection connection = new SqliteConnection(connectionString))
        {
            int rowsAffected = connection.Execute(sql, new { CustomerId = id });
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

Thanks for the response and guidance you provided me. I'll be sure to follow them going forward.

The connection string in the files that are located within BikeShop.DAL section in screenshot is not connected to the sql database. For example, line 17 in the code.

Yes, Im getting these errors below:
Severity Code Description Project File Line Suppression State
Error CS0234 The type or namespace name 'Data' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?) BikeShop.DAL C:\Users\Raymond\source\repos\AccountingWebApp\BikeShop.DAL\CustomerAdapter.cs 7 Active

Severity Code Description Project File Line Suppression State
Error CS0234 The type or namespace name 'Data' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?) BikeShop.DAL C:\Users\Raymond\source\repos\AccountingWebApp\BikeShop.DAL\InvoiceAdapter.cs 6 Active

Severity Code Description Project File Line Suppression State
Error CS0246 The type or namespace name 'SqliteConnection' could not be found (are you missing a using directive or an assembly reference?) BikeShop.DAL C:\Users\Raymond\source\repos\AccountingWebApp\BikeShop.DAL\CustomerAdapter.cs 17 Active

 

[Skydiver]

There's no need to quote the entire post directly above yours. This is an old fashioned forum where all the messages are sequential. It's not like reddit or other modern social media platforms where you things are threaded into a tree like structure by hitting reply. If you want to mention someone, use the @ followed by the name. If there is something specific that you want to address, then quote just that sentence or paragraph.

Anyway, how do you know that line 17 is not able to make a connection when it looks like you can't even compile the DAL assembly to run it? Your problem seems more like you can't compile the DAL assembly because it can't find a reference to the "Microsoft.System.Data" assembly that your code says it is using according to line 7.

The way most Visual Studio projects work, is each project builds a single assembly. Each assembly has its own set or references. Did you ensure that you added "Microsoft.System.Data" as a reference with in the DAL project?

 

[Raymond21775]

Skydiver, I'm new to this website. I didn't quote the entire post. All I did was hit reply, add my comment, and click the post reply button. If you think I'm still doing it wrong, let me know.

Regarding line 17 error, I have similar lines in the other files underneath AccountingWebApp section as per screenshot, and the code does not show any errors inside those files, or on the console.

Apparently, the references are added in the AccountingWebApp section, but not in the BikeShop.DAL section. How can I add the references directly to the BikeShop.DAL section?

Thanks for your help, It's much appreciated.

 

[jmcilhinney]

Skydiver, I'm new to this website. I didn't quote the entire post. All I did was hit reply, add my comment, and click the post reply button. If you think I'm still doing it wrong, let me know.

When you hit Reply on another post, that entire post is quoted. As suggested, if you need to indicate something specific you're responding to, delete everything else from the quote. If you're just responding to the previous post, don't click Reply. Just type your reply directly and it will be added below the previous post that you're responding to. Basically, don't quote anything that doesn't need quoting. Quotes should be used to make reading the thread easier but, while it's not the end of the world, unnecessary quotes - especially long ones - have the opposite effect. Quoting the post above yours means that anyone reading the thread sequentially will read that same post twice.

 

[jmcilhinney]

How can I add the references directly to the BikeShop.DAL section?

Not sure exactly what you mean by "section". Presumably you are talking about separate projects within a solution. In the Solution Explorer window in VS, if you click the Collapse All button, you should be left with the solution node at the top and then one or more project nodes under that. You can then expand a project to see the sources files, etc, that it contains.

Ah, I just looked at your original screenshot and, yes, you do have three projects in your solution. As mentioned, each project has its own set of references. It appears that you are using .NET Core, which includes .NET 5 and later, so references are added using NuGet packages. There are a number of ways to manage those but I tend to use the provided GUI. If you right-click a project in the Solution Explorer, you can select Manage NuGet Packages. You can then add, remove and update packages for that project. If you right-click the solution and select Manage NuGet Packages, you can then manage packages for all projects. If you have already installed a package in one project, installing it in another is as simple as checking the box for that project and clicking Install.