SQLite from C # SQLite is a database manager, which allows querying using SQL language, and that can be included as part of a program in C or C + +, just including an additional DLL file ... or even that, because it really is a library that can be "embedded" as part of the program.
Since C # can not use the SQLite DLL directly, but through others like System.Data.Sqlite.Dll, which can be found in http://sqlite.phxsoftware.com/
With the DLL, along with the original SQLite, we do programs in C # that allow us to access databases and are easy to distribute. SQLite can be found in http://www.sqlite.org/
We will see a small example of creating a database, how to enter data and display it. First we see the fragments of code, then a full source, then how to compile and test it, and eventually will include the full download, with source, executable and DLLs. To connect to the database, we would create an object of type "SQLiteConnection" connection = new SQLiteConnection ("Data Source = personal.sqlite, Version = 3; New = True; Compress = True , ");
conexion.Open ();
To create and modify queries, create an object of type SQLiteCommand and run it with" ExecuteNonQuery () ": creation string = "CREATE TABLE city" + "(code VARCHAR (2) PRIMARY KEY, name VARCHAR (30 ));"; SQLiteCommand cmd = new SQLiteCommand (creation, connection);
cmd.ExecuteNonQuery (); If it is a query that returns data itself (a "select"), use "ExecuteReader ()" and read data repeatedly with "while (datos.Read ())" string query = "select * from city"; SQLiteCommand cmd = new SQLiteCommand (query, conn); SQLiteDataReader data = cmd.ExecuteReader (); / / read data while repeatedly
(Datos.Read ()) { string code = Convert.ToString (data [0]); string name = Convert.ToString (data [1]);
/ / And show Console.WriteLine ("Code : {0}, Name: {1} ", code, name);} In the" ExecuteNonQuery ", we can check the amount of data involved, so we can help you discover errors:
quantity = cmd.ExecuteNonQuery ();
if (amount \u0026lt;1) Console.WriteLine ("Could not insert"); Still, many types of errors, and a duplicate key, may cause an exception and jumps to interrupt the program, it would be advisable to include insertion orders within a "try ... catch" In any case, at the end of the connection should close access to Database: conexion.Close (); The full source can be:
/ / Example of accessing SQLite from C # / / Nacho Cabanes, June 2010
using System;
using System. IO
;
using System
. Data
.
SQLite
;
//Utilizamos la DLL
public
class
pruebaSQLite01
{
static
SQLiteConnection conexion
;
private
static
void
CrearBBDDSiNoExiste
(
)
{if
(
! File
.
Exists (
"personal.sqlite" )
) {
/ / Create the connection to the database. / / The Source Data File contains the path of the database connection
= new
SQLiteConnection ( "Data Source = personal.sqlite, Version = 3; New = True; Compress = True;"
) ;
connection. Open
(
) ; / / Create the first table creation string = "CREATE TABLE city"
+ ( code VARCHAR (2) PRIMARY KEY, name VARCHAR (30)); "
; SQLiteCommand cmd = new SQLiteCommand ( creation, connection ) ;
cmd.
ExecuteNonQuery (
)
;
/ / Create the second table creation
= "CREATE TABLE person" + ( code VARCHAR (2) PRIMARY KEY, name VARCHAR (30), " + " codigoCiudad VARCHAR (2)); " ;
cmd = new SQLiteCommand ( creation, connection
)
; cmd.
ExecuteNonQuery ( )
; } else { / / Create the connection to the database.
/ / The Source Data File contains the path of the database connection = new SQLiteConnection
(
"Data Source = personal.sqlite, Version = 3, New = False; Compress = True; "
) ;
connection. Open
( )
; }}
private static void
InsertarDatos ( ) {
insertion string;
/ / Order of insertion, in SQL SQLiteCommand
cmd;
/ / SQLite Command
int amount ;
/ / Result: The amount of data
try { insertion
= "INSERT INTO city" + " VALUES ('t', 'Toledo'); " ; cmd = new
SQLiteCommand (
insertion, connection
)
; cmd = amount . ExecuteNonQuery ( )
;
if
( amount \u0026lt;
1)
Console. WriteLine ( "Could not insert" )
;
insertion
= "INSERT INTO city" +
"VALUES ('a', 'Alicante');" ;
cmd = new SQLiteCommand
( insertion, connection ) ; amount cmd = .
ExecuteNonQuery ( ) ; if (
amount \u0026lt; 1 ) Console. WriteLine (
"Could not insert" ) ; insertion = "INSERT INTO person"
+ "VALUES (' j ',' John ',' t '); " ;
cmd = new SQLiteCommand
( insertion, connection ) ; amount cmd = .
ExecuteNonQuery ( ) ; if (
amount \u0026lt; 1) Console. WriteLine (
"Could not insert" ) ; insertion = "INSERT INTO person"
+ "VALUES ('p', 'Pepe', 't');" ;
cmd = new SQLiteCommand
( insertion, connection ) ; amount cmd = .
ExecuteNonQuery ( ) ; if (
amount \u0026lt; 1) Console. WriteLine (
"Could not insert" ) ; }
catch (Exception e
) {
Console. WriteLine ( "Could not insert" ) ; Console.
WriteLine ( "Possibly a code is repeated" ) ; Console.
WriteLine ( "Error found: {0}" , and . Message )
; } }
private
static void MostrarCiudades ( )
{
/ / launch the consultation and prepare to read data structure string query = "select * from city" ; SQLiteCommand cmd = new
SQLiteCommand ( consultation, connection ) ;
SQLiteDataReader data = cmd . ExecuteReader ( ) ; / / read data repeatedly
while (
data.
Read
( ) ) {string code = Convert
.
ToString (
data [
0 ] )
; string name = Convert . ToString ( data [1
] ) ; / / And show
Console.
WriteLine ( "Code: {0}, Name: {1}" , code, name ) ; }
}
private static void MostrarPersonas ( ) {
/ / launch the consultation and prepare the structure for reading data query string = "select * from person" ; SQLiteCommand cmd = new SQLiteCommand ( consultation,
connection )
; SQLiteDataReader data = cmd . ExecuteReader (
) ; / / read data repeatedly
while
(
data.
Read
( ) ) {string code = Convert
. ToString (
data [0
] )
; string name = Convert . ToString ( data [1
] ) ; / / And show
Console.
WriteLine ( "Code: {0}, Name: {1}" , code, name ) ; }
}
private static void MostrarCiudadesYPersonas ( ) {
/ / launch the consultation and prepare to read data structure string query = "persona.nombre select a city. name " +" from person town " + " where persona.codigoCiudad = ciudad.Codigo " ; SQLiteCommand cmd = new
SQLiteCommand
( consultation , connection ) ; SQLiteDataReader data = cmd
. ExecuteReader ( )
;
/ / read data repeatedly
while ( data. Read ( ) )
{
nombrePersona string Convert
= .
ToString (
data [0
] )
; string Convert nombreCiudad = . ToString ( data [1
] ) ; / / And show
Console.
WriteLine ( "{0} lives at {1}" , nombrePersona , nombreCiudad ) ;
} }
private static void CerrarBBDD ( ) {
connection. Close ( ) ; } public
static
void Main ( ) {
Console . WriteLine ( "Accediendo..." )
;
CrearBBDDSiNoExiste (
) ; Console. WriteLine ( "Adding data ..." )
;
InsertarDatos ( ) ; Console.
WriteLine (
"city data"
) ; MostrarCiudades ( ) ;
Console.
WriteLine ( "data people" ) ; MostrarPersonas (
) ; Console.
WriteLine ( "data people and cities," ) ; MostrarCiudadesYPersonas (
) ; CerrarBBDD (
) ; } }
To compile this source, we include "System.Data . SqLite.dll "within the project references. This would be done from within the visual environment if we use tools like Visual Studio or SharpDevelop, or by adding the / r option if you compile from the command line, for example using Mono: gmcs pruebaSQLite.cs / r: System.Data. SqLite.dll
And here you can download the source, the DLL and executable files: csharp_sqlite.zip
; 
