Use the Sqlite database in the asp.net Program

This article describes how to use the Sqlite embedded database in the asp.net web program. The sqlite database can be stored as a file in the APP_DATA directory of the site. It is suitable for small websites and 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 the Sqlite code in the cs file of default. aspx. 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 the Sqlite Database
Using (SQLiteConnection conn = new SQLiteConnection ("Data Source =" + sqliteFilePath ))
{
// Create the sqlite command
Using (SQLiteCommand comm = conn. CreateCommand ())
{
// Open the 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 = new SQLiteParameter (name, type, size );
Parm. Value = value;
Return parm;
}
}
}

Finally, we hope MS SQL Server Compact can be improved to make it easy to use.