C# SQlite操作方法小结

时间:2021-10-19 08:02:07

本文实例分析了C# SQlite操作方法。分享给大家供大家参考,具体如下:

最近项目需求用C#保存一些数据,如此先总结一下。需要下载Sqlite 库 SourceForge 链接网址http://sourceforge.net/projects/sqlite-dotnet2/或到官方网http://www.sqlite.org/download.html下载都可以,下载之后安装。在C#项目中添加引用 引入安装目录bin中的System.Data.SQLite.dll。添加命名空间using System.Data.SQLite;便可以在你的项目中

对扩平台的微型数据库SQlite 进行使用了,

主要注意一点是:

数据库若未创建则使用:

?
1
SQLiteConnection.CreateFile(databaseName);

数据库已经创建,并要进行访问:

复制代码 代码如下:
SQLiteConnection m_conn = new SQLiteConnection("DataSource="+m_dbName+";Version=3;New=False;Compress=True;");

下面是项目中封装的操作数据库代码,使用时可稍微修改便可在项目中使用。

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
namespace Toolbar
{
  public class CSPDatabase
  {
    protected string m_dbName;
    protected string m_tablename;
    protected string m_password;
    public CSPDatabase(string dbName)
    {
      m_dbName  = dbName;
      m_tablename = "MhtInfo";
      m_password = "";
    }
    //Create DataBase
    public virtual void Init() { }
    public virtual void CreateDataBase() { }
    public virtual void OpenDataBase() { }
    public virtual void SetPassWord(string password) { }
    //Connect DataBase
    public virtual void ConnectDataBase() { }
    //Create Table
    public virtual void CreateTable(string tableName) { }
    //Insert Data
    public virtual void Insert(string mhtlocation) { }
  }
}
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
namespace Toolbar
{
  class SqliteDatabase : CSPDatabase
  {
    private SQLiteConnection m_conn= null;
    private SQLiteCommand m_cmd=null;
    public SqliteDatabase(string dbName):base(dbName)
    {
    }
    public override void Init()
    {
      if(m_conn == null)
        m_conn = new SQLiteConnection();
      m_cmd = new SQLiteCommand();
      m_cmd.Connection = m_conn;
    }
    public override void CreateDataBase()
    {
      //Create Database
      try
      {
        SQLiteConnection.CreateFile(m_dbName);
        Init();
        ConnectDataBase();
      }
      catch (System.Exception e)
      {
        MessageBox.Show("Create DataBase Failed!");
      }
    }
    public override void OpenDataBase()
    {
      m_conn = new SQLiteConnection("Data Source="+m_dbName+";Version=3;New=False;Compress=True;");
      Init();
      ConnectDataBase();
    }
    public override void SetPassWord(string password)
    {
      m_password = password;
    }
    public override void ConnectDataBase()
    {
      //Connect to DataBase
      try
      {
        SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
        connstr.DataSource = m_dbName;
        if(m_password != "")
          connstr.Password = m_password;
        m_conn.ConnectionString = connstr.ToString();
      }
      catch (System.Exception e)
      {
        MessageBox.Show("Fail to Connect to the database");
      }
    }
    //Create Table
    public override void CreateTable(string tableName)
    {
      try
      {
        m_tablename = tableName;
        m_conn.Open();
        string sql = "CREATE TABLE " + tableName + "(mhtlocation varchar(20))";
        m_cmd.CommandText = sql;
        m_cmd.ExecuteNonQuery();
        m_conn.Close();
      }
      catch (System.Exception e)
      {
        MessageBox.Show("Create Table Failed!");
      }
    }
    public override void Insert(string mhtlocation)
    {
      try
      {
        //Insert Data
        m_conn.Open();
        string sql = "insert into [" + m_tablename + "] values('" + mhtlocation + "')";
        m_cmd.CommandText = sql;
        m_cmd.ExecuteNonQuery();
        m_conn.Close();
      }
      catch (System.Exception e)
      {
        MessageBox.Show(e.ToString());
      }
    }
  }
}

希望本文所述对大家C#程序设计有所帮助。