Dynamo – Database Sqlite Connection

A database is a collection of information that is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information. Data gets updated, expanded and deleted as new information is added.

SQLite is an in-process library that implements a self-contained, zero-configuration, serverless, transactional SQL database engine.

SQLite uses a dynamic SQL syntax and performs multitasking to do reads and writes at the same time. The reads and writes are done directly to ordinary disk files.

In this article I will show you how to set up your environment and on further articles I will present you detailed operations that you can do with databases.

.NET has a very easy to use Sqlite connection package which you can download using the NuGet package manager. In this article I will show you how to do that using Dynamo and C#.

Sqlite is self-contained, meaning you don’t need an SQL server, instead your database is an accessible file. You can use a free software to design your database and tables called “DB Browser for Sqlite”

To create a new database simply click “New Database”. After each step do not forget to press “write Changes” to push the changes to your database, then save the file on your drive.

In a database you store your information in tables and the you set up the relations between tables. A table is the basic organizational structure of a relational database. After your database is created you can design a table by pressing “Create table”.

We are going to create a Persons table, which will store the names of our friends. I will design my table to contain a unique auto-generated identifier, and the fields FirstName, LastName, Age

We add some data in our table by going to “Browse Data” tab. After we enter a couple of rows we have to push the changes in our database by pressing “Write Changes”

We will create a static method in C# to create the connection. The reason I chose C# over python is because of the NuGet package manager, it’s easier to load libraries in this way.

Open a new C# class library solution and make sure you have the following installed:

Our code is pretty simple, we just open a connection to our database by passing it’s file location. Then we are using a simple reader that sends a query and extracts the first column only:

 public static class DynamoDb
    {
        [MultiReturn(new[] { "Result" })]
        [IsVisibleInDynamoLibrary(true)]
        public static Dictionary<string, object> Connection(string filePath)
        {
            StringBuilder names = new StringBuilder();

            if (string.IsNullOrEmpty(filePath))
            {
                return new Dictionary<string, object>
                {
                    { "Result",  ""}
                };
            }   
            
            using (var connection = new SQLiteConnection(@"Data Source=" + filePath))
            {
                connection.Open();
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = "SELECT [FirstName], [LastName], [Age] FROM Persons;";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        names.AppendLine(reader.GetString(0));
                    }
                }
            }
            return new Dictionary<string, object>
            {
                { "Result",  names.ToString()}
            };
        }
    }
}