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