C#中操作SQLite数据库实例

　　最近项目中有使用到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; ITPUB个人空间T?$PWQpKD'w
using System.Data;
Qg7@EUXtS\0 using System.Web.UI.WebControls; ITPUB个人空间3XF'vO}
ITPUB个人空间8NH/L2yV5we
using System.Data.SQLite; ITPUB个人空间sCt/At^k7i

&a:sjM%fN)Bg0
r ~ j(gqW%p0 public partial class _Default : System . Web . UI . Page ITPUB个人空间%khU&H{R S
{
qS(Q,O6Ez0   protected void Page_Load( object sender , EventArgs e) ITPUB个人空间Q:x3Fv:O.af9`L
   {
H)@4i?)P*jF }0
\qPA1A%HXnM%xr0   }
XV&KLi K0   protected void btnTest_Click( object sender , EventArgs e) ITPUB个人空间k3bA#Kh-T`
   { ITPUB个人空间 U3qIo0yFY
       SQLiteConnection . ClearAllPools(); ITPUB个人空间 s,sM V eC$sr8zl
       SQLiteConnection . CreateFile( Server . MapPath( "~") + "/UserData.dbx"); ITPUB个人空间5n5@hb|,@2T9^
       SQLiteConnection conn = new SQLiteConnection( "Data Source=" + Server . MapPath( "~" + "/UserData.dbx")); ITPUB个人空间)mU]xS*^zO5~
       conn . Open();
w-qt&LInr/oF`0       Response . Write( "打开数据库成功~~<br />");
+I5yh M)`0
hZ(B7H-v0       SQLiteCommand cmd = new SQLiteCommand(); ITPUB个人空间tT)K4kR l
       cmd . CommandText = "create table Users (UserID int primary key,UserName varchar(100) not null,UserPassword varchar(100) not null)"; ITPUB个人空间9d_-g-Ou
       cmd . Connection = conn; ITPUB个人空间R@Dqsk5Q~)B!E
       cmd . ExecuteNonQuery();
XpG-@l Pw@E%q3U.k0
!U6k|B,iw0       for( int i = 0; i < 100; i ++) ITPUB个人空间+x(_ P rm m {
       { ITPUB个人空间M M.h} y/UT%JsG
           cmd . CommandText = "insert into Users (UserID,UserName,UserPassword) values (" + i + ",'TestUser_" + i + "','" + DateTime . Now . ToString (). Replace( " " , "-" ). Replace( ":" , "-") + "')";          
] U l7gw0           cmd . ExecuteNonQuery(); ITPUB个人空间3y Gl#^,D
       } ITPUB个人空间!C W2R6I2q1tW yB8Z

^5LWC5N;W5KB0       Response . Write( "插入成功~~<br />");
-S"D5GTz{Ic"Y0 ITPUB个人空间~"t^ Itj)i
       cmd . CommandText = "select Username from Users where UserID=1";
bc)Z M!l K2r0       cmd . Connection = conn; ITPUB个人空间Lp7A5U7V
       string tempUserName = cmd . ExecuteScalar (). ToString(); ITPUB个人空间)B!Hgm7^
ITPUB个人空间~2j$`U4s%u
       Response . Write( "单个值查询结果为:" + tempUserName + "<br /><br />"); ITPUB个人空间G:AW5Ll`U

#V ~5fR f0 ITPUB个人空间/|/~0fB%q"?q*Du
       cmd . CommandText = "select * from Users "; ITPUB个人空间TBpa0W$`PX
       cmd . Connection = conn;
OlCB2F2a0       SQLiteDataReader sdrInfo = cmd . ExecuteReader();
(n B#VT)Jqw0       if( sdrInfo != null) ITPUB个人空间2l$j0dg2Xy
       { ITPUB个人空间|9r;S#w1v Lg+M;|
           int userID = 0;
@9U"ZMt(f|$gu0           string userName = string . Empty; ITPUB个人空间y)L[%|(Pg u+v `{
           string userPassword = string . Empty;          
"~\0RgWa(Da V0           while( sdrInfo . Read()) ITPUB个人空间PE}${ v&[?,Z.|}W
           {
i@[OxQ3{js~1K0               userID = Convert . ToInt32( sdrInfo [ "UserID" ]); ITPUB个人空间"^G T s7m
               userName = sdrInfo [ "UserName" ]. ToString(); ITPUB个人空间vskr2U/Z;C
               userPassword = sdrInfo [ "UserPassword" ]. ToString();
m,w~"Lp3Q;A-Pg0
b#j%Sp:oh&D&Q`dg@0               Response . Write( "UserID:" + userID + "<br />");
.h7ot"[0T%n3Z0               Response . Write( "UserName:" + userName + "<br />"); ITPUB个人空间!zn~/A4LV'k V
               Response . Write( "UserPassword:" + userPassword + "<br />"); ITPUB个人空间(C"X9Z |4o,p S
               Response . Write( "<br />");
d GD5M{wb0           }
JNp jI,Mt1p0           sdrInfo . Close(); ITPUB个人空间ag Ol R _?
           sdrInfo . Dispose();
.O9r1^[2C|0       } ITPUB个人空间g mA1PhQz

[Ya5S&kC0       cmd . CommandText = "update Users set UserPassword='linxiang'"; ITPUB个人空间 y0c;rtv6?
       cmd . Connection = conn; ITPUB个人空间by2g:bm R!y G
       cmd . ExecuteNonQuery(); ITPUB个人空间7?p1?2VPA
       Response . Write( "更新数据库中的数据成功.");
"a9dE-U _KS0
8QG1S6K%H(P}A2Zh0       Response . Write( "以下结果为查询从数据库中经过编辑过后的数据项<br /><br />"); ITPUB个人空间5AAY+lxz r:`
       cmd . CommandText = "select * from Users "; ITPUB个人空间7_.x v*?9f
       cmd . Connection = conn;
e!^#m)E9n8iA u0       sdrInfo = cmd . ExecuteReader(); ITPUB个人空间3G/A sFK~ ?gA
       if( sdrInfo != null)
6h*g/V#x R\0       {
2w*x{6f ?z8G0           int userID = 0;
d^wYl0q5N0           string userName = string . Empty; ITPUB个人空间h!ZpGWd)W9e
           string userPassword = string . Empty; ITPUB个人空间k9MSL4c&j
           while( sdrInfo . Read()) ITPUB个人空间/|Pka:j\3Q"W1q
           {
fCx;y8N0               userID = Convert . ToInt32( sdrInfo [ "UserID" ]); ITPUB个人空间%UWw3ty4H$d
               userName = sdrInfo [ "UserName" ]. ToString();
kie7D_%Z/d2E&t5F0               userPassword = sdrInfo [ "UserPassword" ]. ToString(); ITPUB个人空间?eh!Cz;lg*?|

p3}Z`8L3F:|.QE0               Response . Write( "UserID:" + userID + "<br />");
5P w4Y2J{ ts0               Response . Write( "UserName:" + userName + "<br />"); ITPUB个人空间dz.s l:@ q
               Response . Write( "UserPassword:" + userPassword + "<br />"); ITPUB个人空间(^+TZ+Ma+Wtd2s
               Response . Write( "<br />"); ITPUB个人空间M%{2F#r:v9K'l^ p-O7\"G
           } ITPUB个人空间goS Je2D t@
           sdrInfo . Close(); ITPUB个人空间8zW0?(tu$z.[
           sdrInfo . Dispose();
vA E$n Gx#?.j0       } ITPUB个人空间Fh"oJ Yk

k k.y"Lr5f0       conn . Clone(); ITPUB个人空间:gZKc5?.N'R2Qb,R6F G
       conn . Dispose(); ITPUB个人空间V(t:kKaU*x9x
   } ITPUB个人空间(}5^;|s6j1V rA g3A!^
}
F`,j+o8V?0