Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Sqlite
Hi, I have built a task manager application, which allows users to store tasks and then reminding them when a task reaches its deadline, I need to be able to store tasks when the program is not in use, so I have started to implement an SQLite database
 
So I have made a SQLite database called tasks.s3db with fields TASKNAME, TASKTIME, TASKDATE AND TASKNOTE
 
Here is a class I added:
 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 
namespace TaskManager
{
    class SQLiteDatabase
    {
        SQLiteConnection _DBConnect = new SQLiteConnection();
        SQLiteCommand _SQLCom = null;
        SQLiteDataReader _SQLReader = null;
        string DataSource = "Data Source=tasks.s3db";
        string dbConnection;
 
        public SQLiteDatabase()
        {
            try
            {
                _DBConnect.ConnectionString = DataSource;
                _DBConnect.Open();
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            }
        }
 
        public SQLiteDatabase(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
        }
 
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }
 
        public int ExecuteNonQuery(string sql)
        {
            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(cnn);
            mycommand.CommandText = sql;
            int rowsUpdated = mycommand.ExecuteNonQuery();
            cnn.Close();
            return rowsUpdated;
        }
 
        public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception fail)
            {
                MessageBox.Show(fail.Message);
                returnCode = false;
            }
            return returnCode;
        }
 

        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }
 
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
            }
            catch (Exception fail)
            {
                MessageBox.Show(fail.Message);
                returnCode = false;
            }
            return returnCode;
        }
 
        public bool ClearTable(String table)
        {
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}
 
This contains code for queries and inserting, etc, as I need to be able to query, insert, update and delete from the database
 
I have started to add code for the query and immediately run into problems, and fear this could happen when doing the insert update and delete bits too, here is the query code I have added so far:
 
try
            {
                db = new SQLiteDatabase();
                DataTable task;
                String query = "select TASKNAME \"Name\", TASKTIME \"Time\",";
                query += "TASKDATE \"Date\", TASKNOTE \"Notes\"";
                query += "from TASK;";
                task = db.GetDataTable(query);
                foreach (DataRow r in task.Rows)
                {
                    MessageBox.Show(r["Name"].ToString());
                    MessageBox.Show(r["Time"].ToString());
                    MessageBox.Show(r["Date"].ToString());
                    MessageBox.Show(r["Notes"].ToString());
                }
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\n\n";
                MessageBox.Show(error);
                this.Close();
            }
 
The problem is, I need to get the data from the database into a listview control, how could I do this? And what would I need to write for the insert, update and delete parts?
 
Also, the word 'db' in the second code block is underlined red, apparently it doesn't exist in the current context
 
Any help would be much appreciated, its had me tearing my hair out all day!
 
Thanks
Ben
Posted 22-Apr-12 5:20am
Edited 22-Apr-12 5:46am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

It should be noted that this code will not work on Windows 8 if using the SQLite-Net package.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
First of all I would do all the database work on the server side using stored procedures. Then you call these as necessary and pass the required parameters.
 
e.g.
 
CREATE PROCEDURE [dbo].[ProcInsertTable1] 
	@value1 varchar(50),
	@value2 int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    insert into Table1 (Col1,Col2)
    Values(@value1,@value2)
END
 

To read and insert into a listview again create a select stored procedure. You can call this into the code and read the data into a list from where you populate the listview.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 540
1 Sergey Alexandrovich Kryukov 402
2 Yogesh Kumar Tyagi 384
3 Prakriti Goyal 280
4 Maciej Los 205
0 OriginalGriff 6,632
1 Sergey Alexandrovich Kryukov 5,404
2 Maciej Los 3,474
3 Peter Leow 3,299
4 DamithSL 2,495


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100