Sqlite & C#, why am I getting a null connection

Question

0.00/5 (No votes)

See more:

I seem to be getting a null connection when using the below Code

C#

private static string LoadConnectionString(string id = "Default")
{
    return ConfigurationManager.ConnectionStrings[id].ConnectionString;
}



using (IDbConnection cnn = new SqliteConnection(LoadConnectionString()))
{

        cnn.Execute("INSERT INTO Main (Member_Name, Member_ID, Member_Roles, Guilds, Points, Level, Exp) VALUES (@Member_Name, @Member_ID, @Member_Roles, @Guilds, @Points, @Level, @Exp)",
            new { Member_Name = username, Member_ID = id, Member_Roles = roles, Guilds = guild_id, Points = 0, Level = 0, Exp = 0 });
}

Here is my App.config "add" field below

<add name="Default" connectionstring="Data Source=.\Database\The_G_Database.sqlite;Version=3;" providername="System.Data.SqlClient">

What I have tried:

I have tried using a direct string to my directory and different types such as referencing the connection as its own type "

SqliteConnection cnn = new SqliteConnection(LoadConnectionString())

Posted 9-Aug-20 23:42pm

Jamie Head

Updated 10-Aug-20 12:23pm

Add a Solution

Comments

Richard Deeming 10-Aug-20 5:57am

Which bit is null?

Have you checked that your app.config file has been correctly copied to the output directory and renamed to AppName.exe.config?

Jamie Head 10-Aug-20 16:09pm

cnn, the connection itself is giving null. I have tried to input a connection string manually like "Data Source=C:\Database\The_G_Database.sqlite". but still fail to be able to create a connection.

Jamie Head 10-Aug-20 16:22pm

Oh. And to answer the App.config question, yes its in the correct directory. It was named AppName.dll.config because its a console app but I have also copied it as AppName.exe.config.

Richard Deeming 11-Aug-20 3:40am

Is this a .NET Core application? A .NET Framework console application should generate a .exe, but a .NET Core console app will generate a .dll instead.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2020-08-10T02:08:00

Solution 1

Please refer to the following MSDN article to understand how to make use of config file to set and access the connection string: Connection Strings and Configuration Files - ADO.NET | Microsoft Docs[^]

You would need to:
1. add something like below to App.Config:

XML

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="ConString" connectionString="Data Source=myPC\SQLServer;Initial Catalog=Northwind;Integrated Security=true"/>
    </connectionStrings>
</configuration>

2. Add System.Configuration.dll as reference to project.
3. Access connection string as:

C#

using System.Configuration;

// Later below code
string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

Posted 10-Aug-20 2:08am

Sandeep Mewara

Updated 10-Aug-20 2:09am

v2

Comments

Jamie Head 10-Aug-20 16:11pm

I have tried adding the Initial Catalog and Integrated Security tags to my connectionString but I still receive null as a connection. I am using a Console Application, maybe that has something to do with it? I have also tried just not using an App.Config and instead just hardcoding a path directly in the SqliteConnection(). Still no luck :/

Sandeep Mewara 10-Aug-20 16:26pm

If you say that hardcoding the connection string in your code directly has an issue, there is a problem with you code. Please post what you tried when you did this. Plain simple, connection string in code to use DB.

BTW, being on a console applications should not be the reason at all.

Jamie Head 10-Aug-20 16:41pm

using (IDbConnection cnn = new SqliteConnection("Data Source=.\Database\The_G_Database.sqlite;Version=3"))
            {
                
                cnn.Execute("INSERT INTO Main (Member_Name, Member_ID, Member_Roles, Guilds, Points, Level, Exp) VALUES (@Member_Name, @Member_ID, @Member_Roles, @Guilds, @Points, @Level, @Exp)", 
                    new { Member_Name = username, Member_ID = id, Member_Roles = roles, Guilds = guild_id, Points = 0, Level = 0, Exp = 0 });
            }

This is what I mean "hardcode". This using() is still throwing null as a cnn. This user seems to have the same issue -> App.Config file connection string but its connected and am getting the null value reference error[^]

Jamie Head 10-Aug-20 16:45pm

https://i.imgur.com/U1VtgAJ.png

Sandeep Mewara 10-Aug-20 16:55pm

Here, try this off and see. Not too clear but seems something not correctly done in code.

https://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application

Jamie Head 10-Aug-20 17:40pm

I have looked over that and am still getting this error, maybe its the NuGet packages I have? https://i.imgur.com/A6uzjsw.png

Sandeep Mewara 10-Aug-20 17:48pm

start fresh and go step wise. It has to be soem step you are doing that is going wrong as this is straight forward and many have done it.

Jamie Head · Answer 2 · 2020-08-10T12:22:00

I finally figured it out. It seems I was using the incorrect NuGet Packages all along. There is just too many haha. For future reference for anyone else looking to mess with SQLite on C#. Use the NuGet Packages made by the "SQLite Development Team" (System.Data.SQLite) I was using Microsoft's version.