Adding SQLite to Selenium Tests

Adding SQLite database functionality to your Selenium tests is easy to set up.

The TestManager class I wrote has various methods to write data to a SQLite database. I have been using the System.Data.SQLite NuGet package. You can use whichever one fits for your needs. I am not an expert on different packages for SQLite.

Adding SQLite to Your Visual Studio Project

After installing the package, you can start using SQLite in your project by adding the appropriate using statements, such as using System.Data.SQLite;.

Next, you can download and install a SQLite database. I am using DB Browser for SQLite. You can read their documentation on how to use the database to create the tables that are referenced in the TestManager class.

You can see from the TestManager class’s constructor, you need a SQLiteConnection object. The connection string will reference where your database resides. The constructor calls the OpenConnection() method.

        private TestManager()
        {
            string dbFileName = "SeleniumTest.db";
            string projectPath = AppDomain.CurrentDomain.BaseDirectory;
            string dbFilePath = Path.Combine(projectPath, dbFileName);

            string connectionString = $"Data Source={dbFilePath};Version=3;"; // Correct connection string format

            _connection = new SQLiteConnection(connectionString);
            OpenConnection();
            CreateNewTestRun();
        }

The TestManager.OpenConnection() method calls the SQLLiteConnection.Open() method. Now you are ready to make queries!

private bool OpenConnection()
        {
            try
            {
                _connection.Open();
                return true;
            }
            catch (Exception e)
            {
                throw new Exception("Cannot open connection.", e);
            }
        }

Executing Queries From Visual Studio

You can reference the AddTest method in the TestManager class to see how to perform a simple insert and get the Id of the record that was inserted.

        private void AddTest(Test test)
        {
            // Add the test to the Tests list
            _currentTestRun.Tests.Add(test);

            // Perform database insert or other operations related to the test
            using (var insertCommand = new SQLiteCommand("INSERT INTO Test (TestRunId, ExpectedValue, ActualValue, Result, Note) VALUES (@TestRunId, @ExpectedValue, @ActualValue, @Result, @Note); SELECT last_insert_rowid();", _connection))
            {
                insertCommand.Parameters.AddWithValue("@TestRunId", test.TestRunId);
                insertCommand.Parameters.AddWithValue("@ExpectedValue", test.ExpectedValue);
                insertCommand.Parameters.AddWithValue("@ActualValue", test.ActualValue);
                insertCommand.Parameters.AddWithValue("@Result", (int)test.Result);
                insertCommand.Parameters.AddWithValue("@Note", test.Note);
                test.Id = (int)(long)insertCommand.ExecuteScalar();
            }
        }

That’s pretty much it. Easy. You can write any SQL query that your application will need. You can see that getting SQLite added to your Selenium project is pretty simple.