手把手教你使用C#操作数据库SQLite，创建数据库，创建表，插入，查询

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.SQLite;
  6 using System.Data;
  7 using System.Xml;
  8 using System.Text.RegularExpressions;
  9 using System.IO;
 10 
 11 namespace CSharp_SQLite
 12 {
 13     public class CSQLiteHelper
 14     {
 15         private string _dbName = "";
 16         private SQLiteConnection _SQLiteCOnn= null;     //连接对象
 17         private SQLiteTransaction _SQLiteTrans = null;   //事务对象
 18         private bool _IsRunTrans = false;        //事务运行标识
 19         private string _SQLiteCOnnString= null; //连接字符串
 20         private bool _AutoCommit = false; //事务自动提交标识
 21 
 22         public string SQLiteConnString
 23         {
 24             set { this._SQLiteCOnnString= value; }
 25             get { return this._SQLiteConnString; }
 26         }
 27 
 28         public CSQLiteHelper(string dbPath)
 29         {
 30             this._dbName = dbPath;
 31             this._SQLiteCOnnString= "Data Source=" + dbPath;
 32         }
 33 
 34         /// 

 35         /// 新建数据库文件
 36         /// 
 37         /// 数据库文件路径及名称
 38         /// 新建成功，返回true，否则返回false
 39         static public Boolean NewDbFile(string dbPath)
 40         {
 41             try
 42             {
 43                 SQLiteConnection.CreateFile(dbPath);
 44                 return true;
 45             }
 46             catch (Exception ex)
 47             {
 48                 throw new Exception("新建数据库文件" + dbPath + "失败：" + ex.Message);
 49             }
 50         }
 51 
 52 
 53         /// 

 54         /// 创建表
 55         /// 
 56         /// 指定数据库文件
 57         /// 表名称
 58         static public void NewTable(string dbPath, string tableName)
 59         {
 60 
 61             SQLiteConnection sqliteCOnn= new SQLiteConnection("data source=" + dbPath);
 62             if (sqliteConn.State != System.Data.ConnectionState.Open)
 63             {
 64                 sqliteConn.Open();
 65                 SQLiteCommand cmd = new SQLiteCommand();
 66                 cmd.COnnection= sqliteConn;
 67                 cmd.CommandText = "CREATE TABLE " + tableName + "(Name varchar,Team varchar, Number varchar)";
 68                 cmd.ExecuteNonQuery();
 69             }
 70             sqliteConn.Close();
 71         }
 72         /// 

 73         /// 打开当前数据库的连接
 74         /// 
 75         /// 
 76         public Boolean OpenDb()
 77         {
 78             try
 79             {
 80                 this._SQLiteCOnn= new SQLiteConnection(this._SQLiteConnString);
 81                 this._SQLiteConn.Open();
 82                 return true;
 83             }
 84             catch (Exception ex)
 85             {
 86                 throw new Exception("打开数据库：" + _dbName + "的连接失败：" + ex.Message);
 87             }
 88         }
 89 
 90         /// 

 91         /// 打开指定数据库的连接
 92         /// 
 93         /// 数据库路径
 94         /// 
 95         public Boolean OpenDb(string dbPath)
 96         {
 97             try
 98             {
 99                 string sqliteCOnnString= "Data Source=" + dbPath;
100 
101                 this._SQLiteCOnn= new SQLiteConnection(sqliteConnString);
102                 this._dbName = dbPath;
103                 this._SQLiteCOnnString= sqliteConnString;
104                 this._SQLiteConn.Open();
105                 return true;
106             }
107             catch (Exception ex)
108             {
109                 throw new Exception("打开数据库：" + dbPath + "的连接失败：" + ex.Message);
110             }
111         }
112 
113         /// 

114         /// 关闭数据库连接
115         /// 
116         public void CloseDb()
117         {
118             if (this._SQLiteConn != null && this._SQLiteConn.State != ConnectionState.Closed)
119             {
120                 if (this._IsRunTrans && this._AutoCommit)
121                 {
122                     this.Commit();
123                 }
124                 this._SQLiteConn.Close();
125                 this._SQLiteCOnn= null;
126             }
127         }
128 
129         /// 

130         /// 开始数据库事务
131         /// 
132         public void BeginTransaction()
133         {
134             this._SQLiteConn.BeginTransaction();
135             this._IsRunTrans = true;
136         }
137 
138         /// 

139         /// 开始数据库事务
140         /// 
141         /// 事务锁级别
142         public void BeginTransaction(IsolationLevel isoLevel)
143         {
144             this._SQLiteConn.BeginTransaction(isoLevel);
145             this._IsRunTrans = true;
146         }
147 
148         /// 

149         /// 提交当前挂起的事务
150         /// 
151         public void Commit()
152         {
153             if (this._IsRunTrans)
154             {
155                 this._SQLiteTrans.Commit();
156                 this._IsRunTrans = false;
157             }
158         }
159 
160         
161     }
162 }