asp.net (C #) Operation SQLite Database instance _ Practical Tips

To use the SQLite database in the ASP.net project, you first need to download a ado.net 2.0 SQLite data Provider, download the address: http://sourceforge.net/project/showfiles.php group_id=132486&package_id=145568, after the download is installed, the installer is automatically registered in the system (you can see the installed provider in the Add Reference).

Then, add the options in the diagram above to the project.

The ASPX page contains only one button btntest, in the page Aspx.cs page, introduces the namespace, pastes the following similar code.

Copy Code code as follows:

Using System;
Using System.Data;
Using System.Web.UI.WebControls;
Using System.Data.SQLite;

public partial class _default:system.web.ui.page
{
protected void Page_Load (object sender, EventArgs e)
{
}
protected void btnTest_Click (object sender, EventArgs e)
{
Sqliteconnection.clearallpools ();
Sqliteconnection.createfile (Server.MapPath ("~") + "/userdata.dbx");
Sqliteconnection conn = new Sqliteconnection ("Data source=" + Server.MapPath ("~" + "/userdata.dbx"));
Conn. Open ();
Response.Write ("Open database successfully ~~<br/>");
Sqlitecommand cmd = new Sqlitecommand ();
Cmd. CommandText = "CREATE Table Users" (UserID int primary key,username varchar () not Null,userpassword varchar (MB) not nul L) ";
Cmd. Connection = conn;
Cmd. ExecuteNonQuery ();
for (int i = 0; i < 100;i++)
{
Cmd. CommandText = "INSERT into Users (Userid,username,userpassword) VALUES (" + i + ", ' testuser_" + i + "', '" + datetime.now.t Ostring (). Replace ("", "-"). Replace (":", "-") + "')";
Cmd. ExecuteNonQuery ();
}
Response.Write ("Insert success ~~<br/>");
Cmd.commandtext = "Select Username from Users where userid=1";
Cmd. Connection = conn;
String tempusername = cmd. ExecuteScalar (). ToString ();
Response.Write ("Single value query result is:" + tempusername + "<br/><br/>");

Cmd.commandtext = "SELECT * from Users";
Cmd. Connection = conn;
Sqlitedatareader sdrinfo = cmd. ExecuteReader ();
if (sdrinfo!= null)
{
int UserID = 0;
String userName = String. Empty;
String UserPassword = String. Empty;
while (Sdrinfo.read ())
{
UserID = Convert.ToInt32 (sdrinfo["UserID"));
UserName = sdrinfo["UserName"]. ToString ();
UserPassword = sdrinfo["UserPassword"]. ToString ();
Response.Write ("UserID:" +userid+ "<br/>");
Response.Write ("UserName:" + username+ "<br/>");
Response.Write ("UserPassword:" + userpassword + "<br/>");
Response.Write ("<br/>");
}
Sdrinfo.close ();
Sdrinfo.dispose ();
}
Cmd.commandtext = "Update Users set userpassword= ' Linxiang '";
Cmd. Connection = conn;
Cmd. ExecuteNonQuery ();
Response.Write ("Update the data in the database successfully.");
Response.Write ("The following result is a query from the database after the edited data item <br/><br/>");
Cmd.commandtext = "SELECT * from Users";
Cmd. Connection = conn;
Sdrinfo = cmd. ExecuteReader ();
if (sdrinfo!= null)
{
int UserID = 0;
String userName = String. Empty;
String UserPassword = String. Empty;
while (Sdrinfo.read ())
{
UserID = Convert.ToInt32 (sdrinfo["UserID"));
UserName = sdrinfo["UserName"]. ToString ();
UserPassword = sdrinfo["UserPassword"]. ToString ();
Response.Write ("UserID:" + UserID + "<br/>");
Response.Write ("UserName:" + UserName + "<br/>");
Response.Write ("UserPassword:" + userpassword + "<br/>");
Response.Write ("<br/>");
}
Sdrinfo.close ();
Sdrinfo.dispose ();
}
Conn. Clone ();
Conn. Dispose ();
}
}