Use the Sqlite database in the asp.net Program

Source: Internet
Author: User
Tags dot net
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.