使用C#对SQLLite进行操作

1.数据库连接（常用连接方法，示例）

1). 添加引用: System.Data.SQLite.DLL 。
2). 打开或创建数据库文件: SQLiteConnection.CreateFile(fileName);
3). 连接数据库: var connection = new SQLiteConnection(connectionString);
connectionString 中包含了数据库的一些配置信息，比如数据库文件路径，数据库密码等，可用 SQLiteConnectionStringBuilder 来创建 connectionString

 1 string dbPath = Environment.CurrentDirectory + "/test.db";/*指定数据库路径 */
 2 using(SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))
 3  {
 4      conn.Open();
 5      DbCommand comm = conn.CreateCommand();
 6      comm.CommandText = "select * from customer";
 7      comm.CommandType = CommandType.Text;
 8      using (IDataReader reader = comm.ExecuteReader())
 9      {
10         while (reader.Read())
11         {
12           Response.Write(reader[0]);
13         }
14       }
15   }

View Code


在web.config或者是app.config中添加如下配置,connectionstring配置节的db就是 SQLite的数据库文件，将它放在Web应用的App_Data目录，|DataDirectory|就代表这个目录的位置，

后面的就是文件名,剩下的就是我们使用企业库访问SQL Server是一样的了。

<configuration>    
    <configSections>    
      <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, 
Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null />    
    </configSections>    
    <dataConfiguration defaultDatabase="
       
  ">    
       <providerMappings>    
        <add databaseType="EntLibContrib.Data.SQLite.SQLiteDatabase, EntLibContrib.Data.SqLite, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null"    
          name="System.Data.SQLite" />    
      </providerMappings>    
    </dataConfiguration>    
    <connectionStrings>    
      <add name="sqlite" connectionString="Data Source=|DataDirectory|\db;Pooling=true;FailIfMissing=false"    
        providerName="System.Data.SQLite" />    
    </connectionStrings>    
  </configuration>

View Code

 在该工程上引入System.Data.SQLite.DLL（即wrapper），无需引入SQLite3.dll，因为前者已经包装了后者。 
    在代码中import下： "using System.Data.SQLite;"

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 using System.Data.SQLite;
 5 
 6 namespace TestSQLite
 7 {
 8     class MyCSharpSQLite
 9     {
10         private static string connStr;
11 
12         static void Main(string[] args)
13         {
14 
15            connStr = SQLiteConnectionString.GetConnectionString(".\\Data\\EasyDataBase.db");
16            Console.WriteLine("SQLite Connect String is : {0}", connStr);
17 
18            //连接测试
19            //testConnect(connStr);
20 
21            //执行查询
22            ExecQuery("select * from customers");
23            
24            Console.ReadKey();
25         }       
26 
27         private static void testConnect(string connStr) 
28         {
29             try
30            {
31                SQLiteConnection conn = new SQLiteConnection(connStr);
32 
33                conn.Open();
34                Console.WriteLine("SQLite Connect OK.");
35            }
36            catch (Exception ex)
37            {
38                Console.WriteLine("SQLite Connect fail: {0} ", ex.Message);
39            }
40         }
41 
42         private static void ExecQuery(string sqlStr)
43          {
44              Console.WriteLine(">> Start to query the database....");
45              using (SQLiteConnection conn = new SQLiteConnection(connStr))
46              {
47                  conn.Open();
48                  SQLiteCommand comm = conn.CreateCommand();
49                  comm.CommandText = sqlStr;
50                  //comm.CommandType = comm.CommandText;
51 
52                  using (SQLiteDataReader reader = comm.ExecuteReader())
53                  {
54                      while (reader.Read())
55                      {
56                          Console.WriteLine(reader[1].ToString());
57                      }
58                  }
59              }
60          }
61 
62 
63     }
64 
65     /// <summary>
66 
67     /// 生成SQLite连接字符串
68 
69     /// </summary>
70 
71     public static class SQLiteConnectionString
72     {
73 
74         public static string GetConnectionString(string path)
75         {
76               return GetConnectionString(path, null);
77         }
78 
79         public static string GetConnectionString(string path, string password)
80         {
81             if (string.IsNullOrEmpty(password))
82             {
83                 return "Data Source=" + path;
84             }
85             else
86             {
87                 return "Data Source=" + path + ";Password=" + password;
88             }
89         }
90 
91      }
92 }

View Code

示例1

 1 using System;
 2 using System.Reflection;
 3 using System.IO;  
 4 using System.Collections.Generic;
 5 using System.ComponentModel;
 6 using System.Data;
 7 using System.Drawing;
 8 using System.Text;
 9 using System.Windows.Forms;
10 using System.Data.SQLite; //需要安装sqlite for vs
11 namespace MobileTest
12 {
13     public partial class Form1 : Form
14     {
15         public Form1()
16         {
17             InitializeComponent();
18         }
19 
20         public DataSet GetData()
21         {
22             try
23             {
24                 //得到数据库位置
25                 string dbfile = string.Format("{0}\\SqlLiteTest", Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase));
26                 //连接字符串
27                 string connStr = string.Format("Data Source={0};Password=123456", dbfile);
28                 SQLiteConnection conn = new SQLiteConnection(connStr);
29                 string sql = "SELECT Id,Name,age FROM tbl_liteTest";
30                 SQLiteDataAdapter slda = new SQLiteDataAdapter(sql, conn);
31                 DataSet ds = new DataSet();
32                 slda.Fill(ds);
33                 return ds;
34             }
35             catch (Exception)
36             {
37                
38                 throw;
39             }
40         }
41         private void Form1_Load(object sender, EventArgs e)
42         {
43             DataSet ds = GetData();
44             foreach (DataRow item in ds.Tables[0].Rows)
45          {
46                 this.comboBox1.Items.Add(item["Name"].ToString());
47          }
48         }
49         }
50 }

View Code

示例2

 1 SQLiteConnection cnn = new SQLiteConnection();
 2             cnn.ConnectionString = @"Data Source=c:sqlite-3_5_0mytest.db";
 3             cnn.Open();
 4 
 5             SQLiteCommand cmd = new SQLiteCommand();
 6             cmd.Connection = cnn;
 7             cmd.CommandText = "SELECT * FROM mytable1";
 8             SQLiteDataAdapter da = new SQLiteDataAdapter();
 9             da.SelectCommand = cmd;
10             DataSet ds = new DataSet();
11             da.Fill(ds);

View Code

示例3

下载ADO.NET2.0 Provider for SQLite。下载binaries zip版就可以了。下载完后解压缩，可以在bin目录下找到System.Data.SQLite.DLL。在vs2008中用Add Reference功能把System.Data.SQLite.DLL加到工程里就可以了。

 string datasource = "d:/sqllite/sqlite.net/bin/test.db3";
            system.data.sqlite.sqliteconnection.createfile(datasource);
            //连接数据库
            system.data.sqlite.sqliteconnection conn = new system.data.sqlite.sqliteconnection();
            system.data.sqlite.sqliteconnectionstringbuilder connstr = new system.data.sqlite.sqliteconnectionstringbuilder();
            connstr.datasource = datasource;
            connstr.password = "admin";//设置密码，sqlite ado.net实现了数据库密码保护
            conn.connectionstring = connstr.tostring();
            conn.open();
            //创建表
            system.data.sqlite.sqlitecommand cmd = new system.data.sqlite.sqlitecommand();
            string sql = "create table test(username varchar(20),password varchar(20))";
            cmd.commandtext = sql;
            cmd.connection = conn;
            cmd.executenonquery();
            //插入数据
            sql = "insert into test values('a','b')";
            cmd.commandtext = sql;
            cmd.executenonquery();
            //取出数据
            sql = "select * from test";
            cmd.commandtext = sql;
            system.data.sqlite.sqlitedatareader reader = cmd.executereader();
            stringbuilder sb = new stringbuilder();
            while (reader.read())
            {
                sb.append("username:").append(reader.getstring(0)).append("\n")
                .append("password:").append(reader.getstring(1));
            }
            console.writeline(sb.tostring());
            console.read();

View Code

2.C#对SQLLite的DML操作示例

/*这里新建了一个HyData目录存放数据库*/
string connStr = @"Data Source=" + System.Environment.CurrentDirectory + @"\HyData\HyData.db3;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";


/*执行Sql语句
  创建一个表：  ExecuteSql("create table HyTest(TestID TEXT)");
  插入些数据：  ExecuteSql("insert into HyTest(TestID) values('1001')");
*/
private void ExecuteSql(string sqlStr)
{
    using (DbConnection conn = new SQLiteConnection(connStr))
    {
        conn.Open();
        DbCommand comm = conn.CreateCommand();
        comm.CommandText = sqlStr;
        comm.CommandType = CommandType.Text;
        comm.ExecuteNonQuery();
    }
}


/*执行查询 
  ExecQuery("select * from HyTest");
*/
private void ExecQuery(string sqlStr)
{
    using (DbConnection conn = new SQLiteConnection(connStr))
    {
        conn.Open();
        DbCommand comm = conn.CreateCommand();
        comm.CommandText = sqlStr;
        comm.CommandType = CommandType.Text;

        using (IDataReader reader = comm.ExecuteReader())
        {
            while (reader.Read())
            {
                MessageBox.Show(reader[0].ToString());
            }
        }
    }
}

 /* 执行查询返回DataSet */

private DataSet ExecDataSet(string sqlStr)
{
    using (SQLiteConnection conn = new SQLiteConnection(connStr))
    {
        conn.Open();
        SQLiteCommand cmd = conn.CreateCommand();
        cmd.CommandText = sqlStr;
        cmd.CommandType = CommandType.Text;

        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);

        return ds;
    }
}

View Code

System.Data.SQLite.SQLiteConnection conn =new System.Data.SQLite.SQLiteConnection(@"Data Source=d:\maindb.db;Version=3");

string datasource = "d:\\maindb.db";
            //连接数据库
            System.Data.SQLite.SQLiteConnection conn =new System.Data.SQLite.SQLiteConnection();
            System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;            
            conn.ConnectionString = connstr.ToString();            
            conn.Open();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            //取出数据
            string sql = "SELECT * FROM Source";
            cmd.CommandText = sql;
            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
            StringBuilder sb = new StringBuilder();
            while (reader.Read())
            {
                sb.Append("ID:").Append(reader.GetString(0)).Append("\n")
                .Append("NAME:").Append(reader.GetString(1));

            }
            MessageBox.Show(sb.ToString());

View Code

3.对SQLLite DML操作类封装

posted on 2016-08-09 22:18 noteswiki 阅读(1786) 评论(0) 编辑收藏举报

刷新页面返回顶部

noteswiki

公告