none
Connecting to SQLite error RRS feed

  • Question

  • I am trying to make a simple SQLite connection in a Winform. I have added SQLite NuGet Pack and reference to SQLite.

    The error i am getting (

    An unhandled exception of type 'Finisar.SQLite.SQLiteException' occurred in SQLite.NET.dll

    Additional information: near "VacTable": syntax error

    )

    I looked for a example program to no avail.

    This is my code

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    
    namespace Vacation_Time_3._3
    {
        public partial class Form1 : MaterialSkin.Controls.MaterialForm
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                string sql = "Select * from VacTable";
                DataAccess.ExecuteSQL(sql);
                DataTable dt = DataAccess.GetDataTable(sql);
                gridControl1.DataSource = dt;
    
            }
        }
        }

    My Class

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using Finisar.SQLite;
    //using System.Data.SQLite;
    
    namespace Vacation_Time_3._3
    {
        //////SQLite Edition
        class DataAccess
        {
    
            // Connection String for  SQlite Edition
            static string _ConnectionString = @"Data Source=VacDB;Version=3;New=False;Compress=True";
            //Data Source=DemoT.db;Version=3;New=False;Compress=True;
    
            // Use for ..exe.config file 
            //   static string _ConnectionString = Sqtlie_project_tutorial.Properties.Settings.Default.SqliteDBtestConnectionString1;
    
    
    
    
            static SQLiteConnection _Connection = null;
            public static SQLiteConnection Connection
            {
                get
                {
                    if (_Connection == null)
                    {
                        _Connection = new SQLiteConnection(_ConnectionString);
                        _Connection.Open();
    
                        return _Connection;
                    }
                    else if (_Connection.State != System.Data.ConnectionState.Open)
                    {
                        _Connection.Open();
    
                        return _Connection;
                    }
                    else
                    {
                        return _Connection;
                    }
                }
            }
    
            public static DataSet GetDataSet(string sql)
            {
                SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
                SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd);
    
                DataSet ds = new DataSet();
                adp.Fill(ds);
                Connection.Close();
    
                return ds;
            }
    
            public static DataTable GetDataTable(string sql)
            {
                Console.WriteLine(sql);
                DataSet ds = GetDataSet(sql);
    
                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                return null;
            }
    
            public static int ExecuteSQL(string sql)
            {
                SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
                return cmd.ExecuteNonQuery();
            }
        }
    
    }

     

    Booney440



    • Edited by Booney440 Friday, March 30, 2018 7:30 PM error
    Friday, March 30, 2018 2:19 PM

Answers

  • You really don't want to be exposing a Connection (a disposable object) through a public property. Connections should be created, used and destroyed within a single scope so they don't leak.

    Additionally your configuration info should really be in the config file. For demo purposes you might put a conn string in your code but it shouldn't be in anything you intend to release.

    Finally, I generally recommend against using static classes just to avoid having to create an instance of a type. Since you seem to be learning C# then you might be able to get away with this but I'd never recommend this approach going forward. Create, use and destroy instances as needed. If you, for some reason, want to centralize the connection information then, again, use a config file but you could use a static class if you really had to.

    Here's a rewritten version of your code, keeping with the static class just to avoid having to rewrite a lot of other code that uses it.

    public static class DataAccess
    {   
        //For lack of a better option given a static class but really this should be 
        //data to a constructor call and pulled from a config file
        public static string GlobalConnectionString { get; set; }
    
        public static DataSet GetDataSet ( string sql )
        {
            using (var conn = new SQLiteConnection(GlobalConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
    
                    var adapter = new SQLiteDataAdapter(cmd);
    
                    var ds = new DataSet();
    
                    conn.Open();
                    adapter.Fill(ds);
    
                    return ds;
                };
            };
        }
    
        public static DataTable GetDataTable ( string sql )
        {
            var ds = GetDataSet(sql);
    
            //Using LINQ here in combination with null conditional that is available in latest C# version
            return ds?.Tables.OfType<DataTable>().FirstOrDefault();
    
            //Old approach
            //return (ds != null) ? ds.Tables.OfType<DataTable>().FirstOrDefault() : null;
        }
    
        public static int ExecuteSQL ( string sql )
        {
            using (var conn = new SQLiteConnection(GlobalConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
    
                    conn.Open();
    
                    return cmd.ExecuteNonQuery();
                };
            };
        }
    }

    How to build SQLite connection strings - link.

    //Init early in your app but preference is to store in config file
    DataAccess.GlobalConnectionString = "Data Source=MyFile.db;Version=3;New=false";
    
    //Each time you want to use it
    var dt = DataAccess.GetDataTable("SELECT * FROM VacTable;");


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Booney440 Tuesday, April 10, 2018 8:13 PM
    Saturday, March 31, 2018 5:04 AM
    Moderator

All replies

  • The error is saying your SQL syntax is wrong. You should probably test your query in a SQLite query editor first.

    Please post questions related to using SQLite in their forums. These forums are for MS products only. 


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, March 30, 2018 2:33 PM
    Moderator
  • Hello Booney440,

    If the exception hits when you try to access the table (not connect to the database) try to put Table in single quotes like so:

    string sql = "Select * from 'Table'";
    That is my suggestion because of a thread posted some time ago which apperantly that was the problem. Here is the thread.

    Best Regards,

    Konstantinos Pap

    Friday, March 30, 2018 4:40 PM
  • I tried the single quotes still errors. I loaded the file in DB Browser from the dir and get return information. 

    Its not seeing the VacTable from the VacDB file. I had a typo earlier, i have corrected the above code. 


    Booney440

    Friday, March 30, 2018 7:27 PM
  • You're not using SQLite directly but rather some DataAccess type that appears to be defined in your code. Are you sure it is treating your query correctly? Trace into the code to the point where it is calling the DB directly. It might be trying to call a sproc when you're using text. Additionally it might require a semicolon at the end of the query to indicate end of batch but you're not doing that either.

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, March 30, 2018 7:44 PM
    Moderator
  • Thank you all, My database is in my Bin folder so I may be asking the query wrong. This is my first at sqlite.

    Booney440

    Friday, March 30, 2018 9:26 PM
  • I dont know how to trace the code I'm a newbi, what would be a cleaner wat to attach the DB?

    Booney440

    Friday, March 30, 2018 11:21 PM
  • You really don't want to be exposing a Connection (a disposable object) through a public property. Connections should be created, used and destroyed within a single scope so they don't leak.

    Additionally your configuration info should really be in the config file. For demo purposes you might put a conn string in your code but it shouldn't be in anything you intend to release.

    Finally, I generally recommend against using static classes just to avoid having to create an instance of a type. Since you seem to be learning C# then you might be able to get away with this but I'd never recommend this approach going forward. Create, use and destroy instances as needed. If you, for some reason, want to centralize the connection information then, again, use a config file but you could use a static class if you really had to.

    Here's a rewritten version of your code, keeping with the static class just to avoid having to rewrite a lot of other code that uses it.

    public static class DataAccess
    {   
        //For lack of a better option given a static class but really this should be 
        //data to a constructor call and pulled from a config file
        public static string GlobalConnectionString { get; set; }
    
        public static DataSet GetDataSet ( string sql )
        {
            using (var conn = new SQLiteConnection(GlobalConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
    
                    var adapter = new SQLiteDataAdapter(cmd);
    
                    var ds = new DataSet();
    
                    conn.Open();
                    adapter.Fill(ds);
    
                    return ds;
                };
            };
        }
    
        public static DataTable GetDataTable ( string sql )
        {
            var ds = GetDataSet(sql);
    
            //Using LINQ here in combination with null conditional that is available in latest C# version
            return ds?.Tables.OfType<DataTable>().FirstOrDefault();
    
            //Old approach
            //return (ds != null) ? ds.Tables.OfType<DataTable>().FirstOrDefault() : null;
        }
    
        public static int ExecuteSQL ( string sql )
        {
            using (var conn = new SQLiteConnection(GlobalConnectionString))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
    
                    conn.Open();
    
                    return cmd.ExecuteNonQuery();
                };
            };
        }
    }

    How to build SQLite connection strings - link.

    //Init early in your app but preference is to store in config file
    DataAccess.GlobalConnectionString = "Data Source=MyFile.db;Version=3;New=false";
    
    //Each time you want to use it
    var dt = DataAccess.GetDataTable("SELECT * FROM VacTable;");


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Booney440 Tuesday, April 10, 2018 8:13 PM
    Saturday, March 31, 2018 5:04 AM
    Moderator