http://akiscode.com/articles/sqlite-csharp.shtml
There are a few libaries that every programmer loves and knows because they either:
- Saves you a ton of time coding
- Does something that you don't know how to do yourself
Examples include cURL, SQLite, or even the .NET library itself. Many of us have to create CRUD (Create, Read, Update, Delete) projects on a semi-regular routine. SQLite is nice in the fact that it handles all the CRUD-y stuff (bad pun I know) in a simple, small and standalone package. But because SQLite is so nice to use (when compared to say XML), the Gods have cursed SQLite to cause many headaches if you are a new to it on C# to balance the world karma (or something like that). Also note that i'm not going to explain what any of the code does as its pretty self explanatory. Anyway, here is a complete example on how to setup an embedded SQLite database, insert some data into it, and then update a listview with only certain columns from the database:
The Fun Stuff
*NOTE: I've made a demo project that you can download here. It includes the DLLs and everything that i've talked about below. *
Download the setup from these guys and install it.
Start a new Project in Visual C# Studio (I'm using version 2010 Express). Make it a Windows Forms Application. Save it anywhere. For this example, I will save it on the Desktop and call the project "Akiscode-SQLiteTest. At the time I was doing this, the SQLite dll only worked with a program targeted at .NET version 3.5. To learn how to change that go here.
Go to the Solutions Explorer on the top right of your VC setup. Right click references and select "Add Reference". Go to the ".NET" tab and find the entry named "System.Data.SQLite". Not "System.Data.Sql", not "System.Data.SqlXml", NOT "MySql.Data". Just find "System.Data.SQLite". If you can't find it, sort the list by name. If you still can't find it, restart VC and try this process again. Still can't find it? Run the installer and do it all over again. Still not there? Well I can't really help you, hand in your programming badge on the way out.
Once you added "System.Data.SQLite", right click it and select properties. Set "Copy Local" to true.
Ok type at the top "using "using System.Data.SQLite;" without the quotes.
Go back to the form designer. Add a listview component. In the properties window (bottom right) make sure the property view is set to "Details". Then right click on the listview and add columns. For this example I added two called "First Name" and "Last Name"
8.Ok now for some copy and paste coding (my favorite): Double click the form to make a "Form1_Load" function appear and paste the following in it:
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | /*
# http://akiscode.com/articles/sqlite-csharp.shtml
# Copyright (c) 2010 Stephen Akiki
# MIT License (Means you can do whatever you want with this)
# See http://www.opensource.org/licenses/mit-license.php
# Error Codes:
# None
*/
try
{
// This code segnment connects/creates the DB and creates a table in it called names
SQLiteConnection cnn = new SQLiteConnection("Data Source=SampleDB.s3db;Version=3;New=True;");
cnn.Open();
// The following command creates a table and inserts stuff in it.
// ... you can obviously seperate these into diff. functions.
string cmdstring = @"CREATE TABLE names
(pri_key INTEGER NOT NULL,
SSN TEXT,
f_name TEXT,
m_name TEXT,
l_name TEXT,
PRIMARY KEY (pri_key),
UNIQUE (SSN));
INSERT INTO names (SSN, f_name, m_name, l_name) VALUES ('NotRealSSN', 'Billy', 'Bob', 'Thornton');
";
SQLiteCommand mycommand = new SQLiteCommand(cmdstring, cnn);
mycommand.ExecuteNonQuery();
// The following fills a DataTable variable with the results from a command you specify
SQLiteDataAdapter DB;
DataSet DS = new DataSet();
DataTable dt = new DataTable();
// Specify command below
DB = new SQLiteDataAdapter("SELECT * FROM names", cnn);
DS.Reset();
DB.Fill(DS);
dt = DS.Tables[0];
// THe following segment updates the listview
DataTable dtable = new DataTable();
listView1.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow drow = dt.Rows[i];
// Only row that have not been deleted
if (drow.RowState != DataRowState.Deleted)
{
// Define the list items
ListViewItem lvi = new ListViewItem(drow["f_name"].ToString());
lvi.SubItems.Add(drow["l_name"].ToString());
// Notice how I left some stuff from the database out
// ... you can do that.
// Add the list items to the ListView
listView1.Items.Add(lvi);
}
}
cnn.Close();
}
catch (Exception fu)
{
// If an exception is thrown it is probably because the database already exists
// ... just delete it, or add a delete clause at the beginning of your program
System.Windows.Forms.MessageBox.Show(fu.Message.ToString());
}
|
