C#中操作SQLite数据库实例

ASP.NET C#中操作SQLite数据库实例

asp.net c# SQLite 操作实例,嵌入式数据库SQLite 在ASP.NET C# 平台的操作实例

　　

　　最近项目中有使用到SQLite数据库,于是查找资料,编写了一个ASP.NET基于C#语言的SQLite数据库操作实例.大家看代码就可以看懂了,和以往使用ADO.NET操作SQL数据库类似.

     要想在ASP.NET项目中使用SQLite数据库,先需下载一个ADO.NET 2.0 SQLite Data Provider,下载地址为:http://sourceforge.net/project/showfiles.php?group_id=132486&package_id=145568,下载后安装完毕后,该安装程序自动在在系统注册(即可在"添加引用"中看到所安装的Provider).    

    

     然后,在项目中添加上图所选项即可.

 

     aspx页面仅包含一按钮btnTest,在页面aspx.cs页面中,引入命名空间,贴入以下类似代码即可.    

 

 

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("打开数据库成功~~<br />");

        SQLiteCommand cmd = new SQLiteCommand();
        cmd.CommandText = "create table Users (UserID int primary key,UserName varchar(100) not null,UserPassword varchar(100) not null)";
        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.ToString().Replace(" ", "-").Replace(":", "-") + "')";           
            cmd.ExecuteNonQuery();
        }

        Response.Write("插入成功~~<br />");

        cmd.CommandText = "select Username from Users where UserID=1";
        cmd.Connection = conn;
        string tempUserName = cmd.ExecuteScalar().ToString();

        Response.Write("单个值查询结果为:" + 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("更新数据库中的数据成功.");

        Response.Write("以下结果为查询从数据库中经过编辑过后的数据项<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();
    }
}