Use the SQLite database in the Asp.net web program

This article describes howProgramUsing SQLite embedded database, SQLite database can be placed in the app_data directory of the site as a file, suitable for small websites, it does not need to buy SQL server space to use it, and it is said that its performance is very good.

SQLite is embedded into a database. Similar to ms SQL Server compact, SQL Server compact is not used because it neither supports select top nor row_number () nor limit, that is to say, I cannot use it for paging. When retrieving data, you must obtain the data according to the conditions. N records cannot be obtained under the given conditions.

1. It is very convenient to install the SQLite database. Only one EXE file can be downloaded.

After downloading the executable file, run the "sqlite3 dbname" command to create a database.

You can also use the SQLite Developer Software for Development convenience. Download link

2. Install the SQLite database driver under dot net, which is actually a DLL, system. Data. SQLite. It is an open-source project and can be downloaded from SourceForge.

3. Create a web application in Vs and reference the DLL in 2.

4. Access SQLite in the CS file of default. aspxCode, The code and comments are as follows:

Using System;
Using System. Data;
Using System. configuration;
Using System. collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. webcontrols;
Using System. Web. UI. webcontrols. webparts;
Using System. Web. UI. htmlcontrols;
Using System. Data. SQLite;

Namespace Sqlitewebapp
{
Public   Partial   Class _ Default: system. Web. UI. Page
{
Protected   Void Page_load ( Object Sender, eventargs E)
{
// This file is a pre-generated database file
String Sqlitefilepath = Server. mappath ( " ~ /App_data/firstsqlite. DB " );
Dataset DS =   New Dataset ();
// Declare a link to an SQLite Database
Using (Sqliteconnection Conn =   New Sqliteconnection ( " Data Source = "   + Sqlitefilepath ))
{
// Create SQLite command
Using (Sqlitecommand comm = Conn. createcommand ())
{
// Open Database Link
Conn. open ();
// Insert data
Comm. commandtext =   " Insert into [T] values (10, 'Hello 9 ') " ;
Comm. executenonquery ();

// Update Data
Comm. commandtext =   " Update [T] Set Name = 'Hello 10' where id = 10 " ;
Comm. executenonquery ();

// Insert data using parameters
Comm. commandtext =   " Insert into [T] values (@ ID, @ name) " ;
Comm. Parameters. addrange (
New Sqliteparameter [] {
Createsqliteparameter ( " @ ID " , Dbtype. int32, 4 , 11 ),
Createsqliteparameter ( " @ Name " , Dbtype. string, 10 , " Hello 11 " )
} );
Comm. executenonquery ();

Comm. Parameters. Clear ();
// Use Limit to paging select data, which is convenient.
Comm. commandtext =   " Select * from Main. [T] " ;
Using (Sqlitedataadapter Adapter =   New Sqlitedataadapter (Comm ))
{
Adapter. Fill (DS );
}
}
}

Gv1.datasource = DS;
Gv1.databind ();
}

/**/ ///   <Summary>
/// Put back a sqliteparameter
///   </Summary>
///   <Param name = "name"> Parameter Name </Param>
///   <Param name = "type"> Parameter type </Param>
///   <Param name = "size"> Parameter size </Param>
///   <Param name = "value"> Parameter Value </Param>
///   <Returns> Sqliteparameter Value </Returns>
Static   Private Sqliteparameter createsqliteparameter ( String Name, dbtype type, Int Size, Object Value)
{
Sqliteparameter parm= NewSqliteparameter (name, type, size );
Parm. Value=Value;
ReturnParm;
}
}
}

finally, we hope ms SQL Server Compact can be improved to make it easy to use.