Question 134219 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – C# add reference to sqlite.dll in class library project from another project

Here are the steps I used to build these two Projects ADBTest and DBControl.
ADBTest was built first it implements SQLite DB CRUD functions I added System.Data.SQLite with NuGet.
Ran the project everything functions great!
Next I built the DBControl it is a Windows Forms Class Library.I added this to the ADBTest project.
Added Existing Project and Added Reference to DBControl to ADBTest.
Now I tried to add Reference to the SQLite.dll in ADBTest the DBContro.csproj says it there I think
Here is were I start getting LOST. Will Post some code and a screen shot.
I did various using declaration but that does not work nothing is found to USE.
So where did I make the mistake or is the code design all off?
Also not sure how to call this code from frmStart? Work on that later one question RULE sucks!
DBControl has NO form so when it is done with makeFriendsTable will try to send it back to frmStart round trip flight I hope.

public class DBControl : UserControl
{
    public static string dbName = "APeople.db";
    // Class1

    private void UserControl_Load(object sender, EventArgs e)
    {
        makeDB();
        makeFriendsTable();
    }

    private void makeDB()
    {
        throw new NotImplementedException();
    }

    private void makeFriendsTable()
    {
        throw new NotImplementedException();
    }

    public class MakeDB
    {
        public void makeDB()
        {
            using (SQLiteConnection conn = new SQLiteConnection($"Data Source = '{dbName}';Version=3;"))
                if (!File.Exists(dbName))
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                }
        }

Here is the frmStart Call

        private void btnCreateDB_Click(object sender, System.EventArgs e)
    {
        Hide();
        using (var form = new DBControl.DBControl())
        Show();

        //DBControl dB = new DBControl();
        //dB.Show();
    }

The Weird Part after putting the SQLite in after the two projects were joined in Visual Studio
First NuGet insisted they be installed in BOTH Projects
Then the DB and Table were created but I could not find the DB in the Solution Explorer
Went looking in the source folder on C Drive it was in DBCall Debug folder

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    First I had to create the First C# .net framework project
    Then I created the Class Library project that would handle the CRUD work for the SQLite DB.
    Then I attached the Class Library to the First Project
    Then I used NuGet to add the System Data SQLite to BOTH projects.
    This seemed counter intuitive but it works.
    OK how to pass an integer ID into the Class Library most of the code I found on SO people we passing variables OUT of the CL NOT into it.
    Here is the little method I used to pass in udID and assign it to a local variable newData which I can use to make SQL calls to the DB in the Class Library

        public void UTBW(int udID)
    {
        newData = udID;
        return;
    }

    Here is the code in the CL that uses newData

            public void readUDV()
    {
        using (SQLiteConnection conn = new SQLiteConnection($"Data Source = '{dbName}';Version=3;"))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand($"SELECT * FROM FriendsData WHERE FID = " + newData , conn))
            {
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {  
                        fn = rdr["fxFirstName"].ToString().Trim();
                        ln = rdr["fxLastName"].ToString().Trim();
                        rt = rdr["fxInfo"].ToString().Trim();
                    }
                }
            }
        }
    }

    One other issue I faced was how to populate a Data Grid View that was not in the Class Library enter stage left the trusty Generic List.Here is the code that populates the list it is in the Class Library.

      public List<object> readData(List<object> list)
    {
        using (SQLiteConnection conn = new SQLiteConnection($"Data Source = '{dbName}';Version=3;"))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand($"SELECT * FROM FriendsData", conn))
            {
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        gv_parentInt = rdr["FID"].ToString().Trim();
                        list.Add(gv_parentInt);
                        gv_firstName = rdr["fxFirstName"].ToString().Trim();
                        list.Add(gv_firstName);
                        gv_lastName = rdr["fxLastName"].ToString().Trim();
                        list.Add(gv_lastName);
                    }
                }
            }

        }
        return list;
    }

    Great it returns the list to the form in the First project that needs to populate the DataGridView with the list data.

        public void loadDGV()
    {
        frmHDB form = new frmHDB();
        {
            form.readData(list);
            foreach (string str in list)
            {
                dgvPerson.Rows.Add(list[i], list[i + 1], list[i + 2]);
                i = i + 3;
                dgvPerson.Sort(dgvPerson.Columns[2], ListSortDirection.Ascending);
            }  
        }
    }

    I am not too sure about my counter i = i + 3 IT works
    Don't Forget you need to add the Class Library dll to the First Project.
    As for portability and Class Library's NOT SO MUCH
    I tried to attache this CL to another and project and I changed the name of the database in the CL. Went back to my original two projects and WHAT it quit working YEP the name of the database gets set to the new project DB name. Two hours later I discovered the cause of the ERROR.
    Just create a new Class Library they are NOT portable as advertised.


  2. 0

    I think that can be a design problem. Is not so simple as seems to be, and even working with a correct design can bring some problems, so the question for me is useful.

    The common work is made layers of work, one with the interface, one with the logic, one with the data handle…. that’s the three layers model. There are several designs to work, but this is the common one.

    The layer that handles the data is often called Data Layer (DAL) and in microservices or SQL servers is often called Data Service (DAS)

    The topic indeed is very large to explain but you can get a reference with the following image. Of course you can see this model in a lot of different ways but you will find the same principle:

    enter image description here

    As I understand from your case, you have a DBControl that handle the data work. So this is the one who carried the reference to the database and SQLite.dll. By the way you can use Entity Framework to work with SQLite and make an scaffolding to the archive.

    In any case, this DBControl will carry the "repositories" with the methods where you make the calls to the database, being ADO or EF. This is the Persistance and can handle also the Domain Model

    Those methods will return not the EF or DB models, instead your own models that will help and work in your "business logic". Those are the classes, objects, models, etc that the DBControl will export for other libraries or the interface layer where the apps will work.

    Since the DBControl will returns your own models, the interface won’t need the SQLite reference, but the DBControl reference in order to get the models you work there

    The image is a design to follow, but is not a rule. You can have all the layers in the same project and every layer is a .cs file, or in the same file and every layer is a method, the trick is the organization. And maybe you don’t have all the layers, it depends on your design.

    If you require more detail in this answer please comment, I’ll glad to help you

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search