C#应用程序中的SQLite数据库
您好，我已经构建了一个任务管理器应用程序，该应用程序允许用户存储任务，然后在任务到期时提醒他们，我需要能够在不使用程序时存储任务，因此我已经开始实现SQLite数据库
因此，我创建了一个名为task.s3db的SQLite数据库，其中包含字段TASKNAME，TASKTIME，TASKDATE和TASKNOTE
这是我添加的课程:
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();
}
问题是，我需要将数据库中的数据放入listview控件中，该怎么办?对于插入，更新和删除零件，我需要写些什么?
另外，第二个代码块中的单词"db"用红色下划线表示，显然在当前上下文中不存在
任何帮助将不胜感激，这让我整天都把头发扯掉了！
谢谢
Ben
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
首先，我将使用存储过程在服务器端完成所有数据库工作.然后，根据需要调用这些参数并传递所需的参数.
例如
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.
请注意，如果使用SQLite-SQL，此代码将无法在Windows 8上运行.净包.
It should be noted that this code will not work on Windows 8 if using the SQLite-Net package.