在.NET C#中使用sqlite

时间: 2012-07-21 / 分类: C# / 浏览次数: 9,879 views / 0个评论 发表评论

1。从www.sqlite.org下载SQLite 3.3.4的版本
   为了方便,我把它解压了,就一个SQLite3.exe,放入Windows目录下。
   Cmd 进入命令行
   1)
   创建数据库文件:
   >SQLite3 d:\test.db 回车
   就生成了一个test.db在d盘。
   这样同时也SQLite3挂上了这个test.db
   2)
   用.help可以看看有什么命令
   >.help 回车即可
   3)可以在这里直接输入SQL语句创建表格 用;结束,然后回车就可以看到了
   4)看看有创建了多少表
   >.tables
   5)看表结构
   >.schema 表名
   6)看看目前挂的数据库
   >.database
   7)如果要把查询输出到文件
   >.output 文件名
   > 查询语句;
   查询结果就输出到了文件c:\query.txt

   把查询结果用屏幕输出
   >.output stdout

   8)把表结构输出,同时索引也会输出
     .dump 表名
   9)退出
   >.exit 或者.quit

2。从http://sqlite.phxsoftware.com/下载Ado.net驱动。
   下载了安装,在安装目录中存在System.Data.SQLite.dll
    我们只需要拷贝这个文件到引用目录,并添加引用即可对SQLite数据库操作了
   所有的Ado.net对象都是以SQLite开头的,比如SQLiteConnection
   连接串只需要如下方式
   Data Source=d:\test.db 或者DataSource=test.db–应用在和应用程序或者.net能够自动找到的目录
   剩下的就很简单了~~

3。SQL语法
   由于以前用SQLServer或者ISeries,所以DDL的语法很汗颜
   1)创建一个单个Primary Key的table
   CREATE TABLE [Admin] (
[UserName] [nvarchar] (20)   PRIMARY KEY NOT NULL ,
[Password] [nvarchar] (50)   NOT NULL ,
[Rank] [smallint] NOT NULL ,
[MailServer] [nvarchar] (50)   NOT NULL ,
[MailUser] [nvarchar] (50)   NOT NULL ,
[MailPassword] [nvarchar] (50)   NOT NULL ,
[Mail] [nvarchar] (50)   NOT NULL
   ) ;

CREATE TABLE [ESOA_User] (
    [UID] [integer] PRIMARY KEY NOT NULL ,
    [U_Account] [varchar] (50) NOT NULL ,
    [U_Password] [varchar] (50) NOT NULL ,
    [U_UserName] [varchar] (50) NOT NULL ,
    [U_Company] [varchar] (100) NULL ,
    [U_DepartmentID] [integer] NOT NULL ,
    [U_Tel] [varchar] (50) NULL ,
    [U_QQ] [varchar] (50) NULL ,
    [U_Address] [varchar] (100) NULL ,
    [U_Post] [varchar] (10) NULL ,
    [U_UserGroup] [integer] NOT NULL ,
    [U_Power] [varchar] (50) NULL ,
    [U_Lock] [tinyint] NOT NULL ,
    [U_Memo] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

   2)创建一个多个Primary Key的table
   CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext]   NOT NULL ,
[CdString2] [ntext]   NOT NULL ,
[CdString3] [ntext]   NOT NULL,
PRIMARY KEY (CdType,CdCode)
         
   ) ;
   3)创建索引
   CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
   
   还可以视图等等。
4.还有很有用的SQL
Select * from Sqlite_master
Select datetime('now')
Select date('now')
Select time('now')
以及很多函数,具体可以参考SQLite的wiki.

oh,还有就是看到有人说,好像成批插入的时候,启动事务,比不启动事务快n倍
还有就是尽量使用参数化的SQL,估计和商用DB一样能够自动Prepare.

===========

sqlite可以在shell/dos command底下直接执行命令:

sqlite3 film.db "select * from film;"

输出 HTML 表格:

sqlite3 -html film.db "select * from film;"

将数据库「倒出来」:

sqlite3 film.db ".dump" > output.sql

利用输出的资料,建立一个一模一样的数据库(加上以上指令,就是标准的SQL数据库备份了):

sqlite3 film.db < output.sql

在大量插入资料时,你可能会需要先打这个指令:

begin;

插入完资料后要记得打这个指令,资料才会写进数据库中:

commit;
;

————————————

System.Data.Sqlite入手。。。
首先import/using:

代码
using System.Data.SQLite;   
Connection和Command:

代码
private SQLiteConnection conn;   
           
        private SQLiteCommand cmd;   
连接db:

代码
conn = new SQLiteConnection("Data Source=c:\\test.db");   
                 conn.Open();   
INSERT/UPDATE:

代码
cmd = conn.CreateCommand();   
cmd.CommandText = "INSERT INTO user(email,name) VALUES ('email','name')";   
cmd.ExecuteNonQuery();   
   
cmd.CommandText = "UPDATE userSET name = 'Codelicious' WHERE ID = 1";   
cmd.ExecuteNonQuery();   
SELECT:

代码
cmd.CommandText = "SELECT ID, name FROM user";   
SQLiteDataReader reader = cmd.ExecuteReader();   
if (reader.HasRows)   
             {   
                while (reader.Read())   
                 {   
                     Console.WriteLine("ID: " + reader.GetInt16(0));   
                     Console.WriteLine("name: " + reader.GetString(1));   
                 }   
             }   

sqlite是最近比较火的一个小型embeddable RDBMS。 用C实现的,开源,memory footprint非常小,而且对不同的语言有很多wrapper支持。

在.NET中使用sqlite,其实很简单,主要是找个.NET的wrapper。看了几个,最终选了phpguru的SQLite.NET

把sqlite.dll和SQLiteClient.dll放在.NET的current path下面,在项目的references中添加SQLiteClient.dll

程序中引用

c# 代码
 
using SQLite.NET;  

使用方法,打开db:

c# 代码
 
try   
            {   
                Console.WriteLine("opening db…");   
                // Open database   
                SQLiteClient db = new SQLiteClient("c:\test.db");   
   
            }   
            catch (SQLiteException e)   
            {   
                Console.WriteLine("Fatal error: {0}", e.Message);   
                return;   
            }  
select应用:

c# 代码
 
ArrayList tables = db.GetColumn("SELECT name FROM sqlite_master WHERE type = 'table'");   
   
            foreach (string tableName in tables)   
            {   
                Console.WriteLine("\t" + tableName);   
            }  

其他update,insert,delete都支持得不错。 sqlite小巧玲珑,用起来十分方便。
Google Gears和Adobe AIR都在使用sqlite,看来必有其过人之处。。。

SQLite 作为一个轻量级嵌入式数据库,还是非常好用的。雨痕极力推荐~~~~~~  

今天有个朋友测试 SQLite,然后得出的结论是:SQLite 效率太低,批量插入1000条记录,居然耗时 2 分钟!
下面是他发给我的测试代码。我晕~~~~~~  

using System.Data;
using System.Data.Common;
using System.Data.SQLite;

// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");

DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
  // 连接数据库
  conn.ConnectionString = "Data Source=test1.db3";
  conn.Open();

  // 创建数据表
  string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
  DbCommand cmd = conn.CreateCommand();
  cmd.Connection = conn;
  cmd.CommandText = sql;
  cmd.ExecuteNonQuery();

  // 添加参数
  cmd.Parameters.Add(cmd.CreateParameter());
 
  // 开始计时
  Stopwatch watch = new Stopwatch();
  watch.Start();
 
  // 连续插入1000条记录
  for (int i = 0; i < 1000; i++)
  {
    cmd.CommandText = "insert into [test1] ([s]) values (?)";
    cmd.Parameters[0].Value = i.ToString();

    cmd.ExecuteNonQuery();
  }

  // 停止计时
  watch.Stop();
  Console.WriteLine(watch.Elapsed);
}

哎~~~~ 一个常识性的错误,我加几行代码 (新增代码标记 "// <——————-")。

using System.Data;
using System.Data.Common;
using System.Data.SQLite;

// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");

DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
  // 连接数据库
  conn.ConnectionString = "Data Source=test1.db3";
  conn.Open();

  // 创建数据表
  string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
  DbCommand cmd = conn.CreateCommand();
  cmd.Connection = conn;
  cmd.CommandText = sql;
  cmd.ExecuteNonQuery();

  // 添加参数
  cmd.Parameters.Add(cmd.CreateParameter());
 
  // 开始计时
  Stopwatch watch = new Stopwatch();
  watch.Start();
 
  DbTransaction trans = conn.BeginTransaction(); // <——————-
  try
  {
    // 连续插入1000条记录
    for (int i = 0; i < 1000; i++)
    {
      cmd.CommandText = "insert into [test1] ([s]) values (?)";
      cmd.Parameters[0].Value = i.ToString();

      cmd.ExecuteNonQuery();
    }

    trans.Commit(); // <——————-
  }
  catch
  {
    trans.Rollback(); // <——————-
    throw; // <——————-
  }

  // 停止计时
  watch.Stop();
  Console.WriteLine(watch.Elapsed);
}

执行一下,耗时 0.2 秒。这差距是不是太大了点?  

为什么只是简单启用了一个事务会有这么大的差距呢?
很简单,SQLite 缺省为每个操作启动一个事务,那么原代码 1000 次插入起码开启了 1000 个事务,"事务开启 + SQL 执行 + 事务关闭" 自然耗费了大量的时间,
这也是后面显示启动事务后为什么如此快的原因。其实这是数据库操作的基本常识,大家要紧记,不好的代码效率差的不是一点半点。

发表评论

你必须 登录后 才能留言!