C#•7mo ago

Connection Error SQLite and C#

Hi ! I'm trying to make a URL shortener in C# (whoa super original) anyways, I wanna store all of that in my little SQLite db but I stumble across an issue when I am connecting / closing, it seems that multiple connections at the same time and I cannot for the life of me find how to properly away, my bad code below.

15 Replies

Yawnder•7mo ago

$nodetail

MODiX•7mo ago

https://cdn.discordapp.com/attachments/143867839282020352/580532098570518559/image0.png

IluvmeOP•7mo ago

it's coming it's coming

using System.Data.SQLite;
using System;
using System.IO;

namespace URL {

    class UrlShortener {
        private readonly string connectionString = "Data Source=UrlDB.sqlite;Version=3;";

        public  string Shorten(string url)
        {
            CreateDbIfNotExist();
            Guid id = Guid.NewGuid();
            string surl = $"http://localhost:3000/{id}";
            InsertIntoDb(url, surl);
            return surl;
        }

        // make sure connections are closed before opening them again !
        private async Task<object?> ExecuteQuery(string query)
        {

            try
            {

                SQLiteConnection udb_con = new SQLiteConnection(this.connectionString);
                await udb_con.OpenAsync();
                SQLiteCommand command = new SQLiteCommand(query, udb_con);

                object? temp = await command.ExecuteScalarAsync();
                await udb_con.CloseAsync();
                return temp;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return null;
        }

        private async void InsertIntoDb(string url, string shortened)
        {
            string record = $"INSERT INTO urls (dest, short) VALUES ('{url}', '{shortened}')";
           
           var result = await ExecuteQuery(record);
            if (result == null)
            {
                return;
            }
            return;
        }

        private string CreateDbIfNotExist()
        {
            var file = File.Exists("UrlDB.sqlite");
            if (!file)
            {
                SQLiteConnection.CreateFile("UrlDB.sqlite");

                SQLiteConnection udb_con = new SQLiteConnection(this.connectionString);
                udb_con.Open();
                string sql = "CREATE TABLE urls (dest varchar(512), short varchar(256))";

                SQLiteCommand command = new SQLiteCommand(sql, udb_con);
                command.ExecuteNonQueryAsync();
                udb_con.Close();
                return "Db has been correctly crated !";
            }
            return "Db already existed";
        }
    }

}

using System.Data.SQLite;
using System;
using System.IO;

namespace URL {

    class UrlShortener {
        private readonly string connectionString = "Data Source=UrlDB.sqlite;Version=3;";

        public  string Shorten(string url)
        {
            CreateDbIfNotExist();
            Guid id = Guid.NewGuid();
            string surl = $"http://localhost:3000/{id}";
            InsertIntoDb(url, surl);
            return surl;
        }

        // make sure connections are closed before opening them again !
        private async Task<object?> ExecuteQuery(string query)
        {

            try
            {

                SQLiteConnection udb_con = new SQLiteConnection(this.connectionString);
                await udb_con.OpenAsync();
                SQLiteCommand command = new SQLiteCommand(query, udb_con);

                object? temp = await command.ExecuteScalarAsync();
                await udb_con.CloseAsync();
                return temp;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return null;
        }

        private async void InsertIntoDb(string url, string shortened)
        {
            string record = $"INSERT INTO urls (dest, short) VALUES ('{url}', '{shortened}')";
           
           var result = await ExecuteQuery(record);
            if (result == null)
            {
                return;
            }
            return;
        }

        private string CreateDbIfNotExist()
        {
            var file = File.Exists("UrlDB.sqlite");
            if (!file)
            {
                SQLiteConnection.CreateFile("UrlDB.sqlite");

                SQLiteConnection udb_con = new SQLiteConnection(this.connectionString);
                udb_con.Open();
                string sql = "CREATE TABLE urls (dest varchar(512), short varchar(256))";

                SQLiteCommand command = new SQLiteCommand(sql, udb_con);
                command.ExecuteNonQueryAsync();
                udb_con.Close();
                return "Db has been correctly crated !";
            }
            return "Db already existed";
        }
    }

}

so basically, as I understand the error given which is !

code = Busy (5), message = System.Data.SQLite.SQLiteException (0x87AF00AA): database is locked
database is locked

code = Busy (5), message = System.Data.SQLite.SQLiteException (0x87AF00AA): database is locked
database is locked

I do open / close too fast, though, I tried awaiting it all before each "call" but it still doesn't "await"

Yawnder•7mo ago

Are you running your IDE in admin mode? Are you sure you have access to the file?

IluvmeOP•7mo ago

I am not running ide in Admin mode no

Yawnder•7mo ago

But a quick googling just indicates it's what happens if you have a connection left open.

IluvmeOP•7mo ago

yes, that's what I am asking

Yawnder•7mo ago

You should be using using when creating IDisposable objects.

IluvmeOP•7mo ago

I know I am opening / closing the db but it doesn't await between each call if that makes any sense

Yawnder•7mo ago

Are you reading what I write?

IluvmeOP•7mo ago

I am, but I don't get it

Buddy•7mo ago

$sqlinjection

MODiX•7mo ago

Always parametrize queries!⠀ Do not concatenate the query, like in this example:

// Do NOT do this
string query = "SELECT * FROM users WHERE username='" + UserName + "';";
...

// Do NOT do this
string query = "SELECT * FROM users WHERE username='" + UserName + "';";
...

Instead, always parameterize your queries. Look up the documentation for your database library. If you are using System.Data.SqlClient, refer to this example.

Imgur

Buddy•7mo ago

Please remember this

IluvmeOP•7mo ago

yup seen that, tbh, I'm busy trying to make the program even work before it being secure and it's only for my usage so (I know it is good practice though)

Gaming

Programming

Connection Error SQLite and C#