http://code.activestate.com/recipes/tags/akiscode/2010-12-15T01:24:06-08:00ActiveState Code Recipes2010-12-15T01:24:06-08:00Stephen Akikihttp://code.activestate.com/recipes/users/4172143/http://code.activestate.com/recipes/577500-using-sqlite-as-an-embedded-database-with-c-instan/

<p style="color: grey"> Text recipe 577500 by <a href="/recipes/users/4172143/">Stephen Akiki</a> (<a href="/recipes/tags/akiscode/">akiscode</a>, <a href="/recipes/tags/csharp/">csharp</a>, <a href="/recipes/tags/database/">database</a>, <a href="/recipes/tags/embedded/">embedded</a>, <a href="/recipes/tags/sqlite/">sqlite</a>). </p> <p><a href="http://akiscode.com/articles/sqlite-csharp.shtml" rel="nofollow">http://akiscode.com/articles/sqlite-csharp.shtml</a></p> <p>There are a few libaries that every programmer loves and knows because they either:</p> <ul> <li>Saves you a ton of time coding</li> <li>Does something that you don't know how to do yourself</li> </ul> <p>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:</p> <p><strong>The Fun Stuff</strong></p> <ol> <li><p><em>*NOTE: I've made a demo project that you can download <a href="http://akiscode.com/articles/Akiscode-SQLiteTest.zip">here</a>. It includes the DLLs and everything that i've talked about below. *</em></p></li> <li><p>Download the setup from <a href="http://sqlite.phxsoftware.com/">these guys</a> and install it.</p></li> <li><p>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 <a href="http://msdn.microsoft.com/en-us/library/bb772098(v=vs.90).aspx">here</a>.</p></li> <li><p>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.</p></li> <li><p>Once you added "System.Data.SQLite", right click it and select properties. Set "Copy Local" to true.</p></li> <li><p>Ok type at the top "using "using System.Data.SQLite;" without the quotes.</p></li> <li><p>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"</p></li> </ol> <p>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:</p>