locked
How to determine the connection string to a SQLite database, in C# code RRS feed

  • Question

  • Hello. I'm trying to figure out how to specify the connection string to a SQLite database, I would like to access using the following code:

                string connectionString = null;
                SqlConnection connection;
                SqlCommand command;
    
                SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet ds1 = new DataSet();
                string sql = "SELECT DataName, Data, Id, UserId, DateLastUpdated FROM MainTable";
    
                connectionString = "Data Source=C:\\SQLITEDATABASES\\SQLITEDB1.sqlite;Version=3;";
                
    
                connection = new SqlConnection(connectionString);
                try
                {
                    connection.Open();
                    command = new SqlCommand(sql, connection);
                }
                catch
                {
                }

    The value I assigned to the variable connectionString, in the code above, I obtained somewhere from the Internet. It does not work. I'm using Visual Studio 2013, against the file sqlite-netFx451-setup-bundle-x86-2013-1.0.96.0.exe, which I installed, and got from here.

    My application's App.config file looks as follows:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="DC_Password_Saver.Properties.Settings.DC_Password_SaverConnectionString" connectionString="data source=&quot;C:\Users\patmo_000\Documents\Visual Studio 2013\Projects\DC Password Saver\DC Password Saver\DC Password Saver.db&quot;" providerName="System.Data.SQLite.EF6"/>
        </connectionStrings>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1"/>
        </startup>
    </configuration>

    I tried to use the value assigned to connectionString in the XML settings above, replacing single backslash characters with double backslash characters, because the Visual Studio editor flagged them as unrecognizable, and wound up with the following.

                SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet ds1 = new DataSet();
                string sql = "SELECT DataName, Data, Id, UserId, DateLastUpdated FROM MainTable";
    
                connectionString = "data source=&quot;C:\\Users\\patmo_000\\Documents\\Visual Studio 2013\\Projects\\DC Password Saver\\DC Password Saver\\DC Password Saver.db&quot;";
                
    
                connection = new SqlConnection(connectionString);
    The above code however does not work either. So again, does anyone know how I can specify in C# code, a connection string to access my SQLite database? Thanks in advance for your reply.
    Saturday, March 28, 2015 11:08 PM

Answers

  • What does SQLite connection strings has to do with WPF?

    You will find some valid connection strings here: https://www.connectionstrings.com/sqlite/

    But you cannot use the SqlConnection class to connect to a SQLite database. You will need to download and add a reference to the System.Data.SQLite library and then use the SQLiteConnection class:

                connection = new System.Data.SQLite.SQLiteConnection(connectionString);
                try
                {
                    connection.Open();
                    command = new System.Data.SQLite.SQLiteCommand(sql, connection);
                }
                catch
                {
                }


    Please refer to the following article for more information and an example of how to connect and read and write data from an SQLite database using C#: http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/.

    There is contains a link where you can download the required assemblies.

    Hope that helps.

    Please remember to mark helpful posts as answer to close your threads and then start a new thread in an approproate forum if you have a new question.

    • Marked as answer by PDoug Sunday, March 29, 2015 6:20 PM
    Sunday, March 29, 2015 12:41 PM
  • Regarding the connection string, I am now using the Standard string format, found here:

    "Data Source=c:\mydb.db;Version=3;"

    After I applied it to my situation, I used the following C# code:

                connectionString = "Data Source=C:\\Users\\patmo_000\\Documents\\Visual Studio 2013\\Projects\\DC Password Saver\\DC Password Saver\\DCPasswordSaver.db;Version=3;";
    Please note that I also changed the name of my SQLite database file from DC Password Saver.db, to DCPasswordSaver.db, to make coding simpler. 
    • Edited by PDoug Sunday, March 29, 2015 7:19 PM
    • Marked as answer by PDoug Saturday, April 4, 2015 4:44 PM
    Sunday, March 29, 2015 7:18 PM

All replies

  • What does SQLite connection strings has to do with WPF?

    You will find some valid connection strings here: https://www.connectionstrings.com/sqlite/

    But you cannot use the SqlConnection class to connect to a SQLite database. You will need to download and add a reference to the System.Data.SQLite library and then use the SQLiteConnection class:

                connection = new System.Data.SQLite.SQLiteConnection(connectionString);
                try
                {
                    connection.Open();
                    command = new System.Data.SQLite.SQLiteCommand(sql, connection);
                }
                catch
                {
                }


    Please refer to the following article for more information and an example of how to connect and read and write data from an SQLite database using C#: http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/.

    There is contains a link where you can download the required assemblies.

    Hope that helps.

    Please remember to mark helpful posts as answer to close your threads and then start a new thread in an approproate forum if you have a new question.

    • Marked as answer by PDoug Sunday, March 29, 2015 6:20 PM
    Sunday, March 29, 2015 12:41 PM
  • Thanks for your pointers about using classes in the System.Data.SQLite namespace, vs. classes in the System.Data.SqlClient namespace, and about using an appropriate connection string. FYI: I used the connection string used in the example found here.

    Sunday, March 29, 2015 6:31 PM
  • Regarding the connection string, I am now using the Standard string format, found here:

    "Data Source=c:\mydb.db;Version=3;"

    After I applied it to my situation, I used the following C# code:

                connectionString = "Data Source=C:\\Users\\patmo_000\\Documents\\Visual Studio 2013\\Projects\\DC Password Saver\\DC Password Saver\\DCPasswordSaver.db;Version=3;";
    Please note that I also changed the name of my SQLite database file from DC Password Saver.db, to DCPasswordSaver.db, to make coding simpler. 
    • Edited by PDoug Sunday, March 29, 2015 7:19 PM
    • Marked as answer by PDoug Saturday, April 4, 2015 4:44 PM
    Sunday, March 29, 2015 7:18 PM
  • The connection string I'm using doesn't work.

    I'm using SQLite & Entity Framework in a code-first approach. I downloaded System.Data.SQLite from NuGet.

    Is code-first approach of Entity Framework is supported by SQLite database?

    App config connection string:

    <connectionStrings>
        <add name="LaptopContext" connectionString="Data Source = C:\Users\anila\Documents\SampleDemo1.db; Version = 3;" providerName="System.Data.EntityClient" />
    </connectionStrings>

    Add-Migration command doesn't work.

    Error message: Keyword not supported: 'data source'.

    What connection string should I use?

    After making few changes to App.Config file, I'm getting following error:

    No MigrationSqlGenerator found for provider 'System.Data.SQLite'. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators.




    • Edited by Anil1986 Tuesday, October 6, 2015 9:04 PM New error message after changes.
    Tuesday, October 6, 2015 5:50 PM
  • It's a bad idea to use code first anyhow.

    You all too easily end up with a database which is complete junk.

    .

    And this is still the wrong place to ask a sql lite or entity framework question.


    Hope that helps.

    Technet articles: WPF: MVVM Step 1; All my Technet Articles


    • Edited by Andy ONeill Tuesday, October 6, 2015 6:19 PM
    Tuesday, October 6, 2015 6:18 PM
  • I'm using code-first approach so that I can focus more on business logic instead of database stuff.
    Tuesday, October 6, 2015 7:47 PM
  • It's a bad idea to use code first anyhow.

    You all too easily end up with a database which is complete junk


    I must say that I completely disagree with this... From what I have learned you have a great amount of control over what the resulting DB looks like using Fluent API and code first.

    Examples of things you can do are: Setting up and managing primary and foreign keys (including column names), data types, table names, nullability, indexes, etc.

    Check this page out: https://msdn.microsoft.com/en-us/data/jj591617.aspx

    I'm no expert, this is just my opinion.

    • Edited by Blown2Bytes Wednesday, September 14, 2016 11:52 PM
    Wednesday, September 14, 2016 11:51 PM