C#——SQLite速成

彭博 发布于 2012/03/09 12:23
阅读 3K+
收藏 3
为什么我需要 SQLite

* 轻巧灵活,基于单个数据库文件,方便创建、移动,能方便的进行数据的分类存储(按业务、按功能、按日期、按客户等),非常适合与灵活的系统搭配使用。

* 部署容易,.NET环境只需一个dll文件(Access可能需要安装MDAC)

* 开源。

 

SQLite开发环境与开发资料的准备

.Net SQLite数据库驱动:System.Data.SQLite.dll以及文档

SQLite GUI数据库工具:SQLite Expert PersonalEdition个人版免费,够用了

.Net SQLite数据库驱动源码:sqlite-netFx-source-1.0.76.0

 

C#动态创建SQLite数据库

调用SQLiteConnection. CreateFile静态方法创建

SQLiteConnection.CreateFile(path)

 

path测试路径:Data目录存在,Data2目录不存在

                var path1 = "dummy1.db";//成功

                var path2 = "Data/dummy2.db";//成功

                var path3 = "Data\\dummy3.db";//成功

                var path4 = "c:\\Data\\dummy4.db";//成功

                var path5 = "Data2//dummy5.db";//错误,Data2目录不存在

           var path6 = "C:\\Data2\\dummy6.db";//错误,Data2目录不存在

 

SQLiteConnection. CreateFile内部的实现源码:使用了File.Create创建字节文件

    /// <summary>

    /// Creates a database file.  This just creates a zero-byte file whichSQLite

    /// will turn into a database when the file is openedproperly.

    /// </summary>

    /// <paramname="databaseFileName">Thefile to create</param>

    static public void CreateFile(string databaseFileName)

    {

      FileStream fs = File.Create(databaseFileName);

      fs.Close();

    }

 

C#连接SQLite与数据库安全

常用的连接字符串生成

                /// <summary>

                /// 生成SQLite连接字符串

                /// </summary>

                public static class SQLiteConnectionString

                {

                                public static string GetConnectionString(stringpath)

                                {

                                                return GetConnectionString(path,null);

                                }

                                public static string GetConnectionString(stringpath, string password)

                                {

                                                if (string.IsNullOrEmpty(password))

                                                                return "Data Source=" + path;

                                                return "Data Source=" + path + ";Password=" + password;

                                }

                }

修改数据库密码

                /// <summary>

                /// 修改密码

                /// </summary>

                /// <paramname="path"></param>

                /// <param name="newPassword"></param>

                /// <param name="oldPassword">没有密码时为空</param>

                public static boolChangePassword(stringpath, string newPassword,string oldPassword = null)

                {

                                try

                                {

                                                varcon = new SQLiteConnection(SQLiteConnectionString.GetConnectionString(path, oldPassword));

                                                con.Open();

                                                con.ChangePassword(newPassword);

                                                con.Close();

                                }

                                catch (Exception ex)

                                {

                                                return false;

                                }

                                return true;

                }

 

修改密码测试:

                var path2 = "Data/dummy2.db";//数据库初始密码是空值

                ChangePassword(path2, "123");//成功,数据库密码修改为123

                ChangePassword(path2, "234","");//错误,当前密码是123

                ChangePassword(path2, "234","123");//成功,数据库密码修改为123

                ChangePassword(path2, "","234");//成功,数据库密码修改为空

 

C#常用类型与SQLite类型的映射

C#类型

SQLite类型

int

INT, INTEGER

string

CHAR, VARCHAR,VARCHAR2等字符类型

long

INT64

bool

BOOLEAN

double

DOUBLE

DateTime

DATETIME

decmial

DECIMAL

short

SMALLINT

float

FLOAT

*上述类型映射不是唯一的;已进行写读的测试。

 

C#SQLite数据库插入记录并返回自动递增ID

SQLite设置自动递增列:创建INTEGER数据类型的列,设置该列为主键,然后才能设置自动递增属性。

执行插入的sql后,调用last_insert_rowid()函数返回自动递增ID

                object result = null;

                IDbConnection conn = null;

                IDbCommand cmd = null;

                //

                inti = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                if (i> 0)

                {

                                cmd.CommandText = "SELECT last_insert_rowid()";

                                result = cmd.ExecuteScalar();

                }

                else

                {

                                result = -1;

                }

                //

 

C#下的SQLite事务控制

已在TransactionScope管道下测试事务,在内部引发异常,事务能够终止。

                try

                {

                                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())

                                {

                                                //引发异常

                                                scope.Complete();

                                }

                }

                catch (Exception)

                {

 

                }

 


原文链接:http://blog.csdn.net/hunkcai/article/details/6890968
返回顶部
顶部