SQLite介绍
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
SQLite是一个开源、免费的小型RDBMS(关系型数据库),能独立运行、无服务器、零配置、支持事物,用C实现,内存占用较小,支持绝大数的SQL92标准。
SQLite数据库官方主页:http://www.sqlite.org/index.html
C#操作SQLite Database
C#下SQLite操作驱动dll下载:System.Data.SQLite
C#使用SQLite步骤:
(1)新建一个project
(2)添加SQLite操作驱动dll引用
(3)使用API操作SQLite DataBase
以下写了几个类
Helper类
// Version 1.2
// Date: 2014-03-27
// http://sh.codeplex.com
// Dedicated to Public Domain
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
namespace SQLiteHelper
{
public enum ColType
{
Text,
DateTime,
Integer,
Decimal,
Blob
}
public class SqLiteHelper
{
private readonly SQLiteCommand _cmd;
public SqLiteHelper(SQLiteCommand command)
{
_cmd = command;
}
#region DB Info
public DataTable GetTableStatus()
{
return Select("SELECT * FROM sqlite_master;");
}
public DataTable GetTableList()
{
DataTable dt = GetTableStatus();
DataTable dt2 = new DataTable();
dt2.Columns.Add("Tables");
for (int i = 0; i < dt.Rows.Count; i++)
{
string t = dt.Rows[i]["name"] + "";
if (t != "sqlite_sequence")
dt2.Rows.Add(t);
}
return dt2;
}
public DataTable GetColumnStatus(string tableName)
{
return Select(string.Format("PRAGMA table_info(`{0}`);", tableName));
}
public DataTable ShowDatabase()
{
return Select("PRAGMA database_list;");
}
#endregion
#region Query
public void BeginTransaction()
{
_cmd.CommandText = "begin transaction;";
_cmd.ExecuteNonQuery();
}
public void Commit()
{
_cmd.CommandText = "commit;";
_cmd.ExecuteNonQuery();
}
public void Rollback()
{
_cmd.CommandText = "rollback";
_cmd.ExecuteNonQuery();
}
public DataTable Select(string sql)
{
return Select(sql, new List<SQLiteParameter>());
}
public DataTable Select(string sql, Dictionary<string, object> dicParameters = null)
{
List<SQLiteParameter> lst = GetParametersList(dicParameters);
return Select(sql, lst);
}
public DataTable Select(string sql, IEnumerable<SQLiteParameter> parameters = null)
{
_cmd.CommandText = sql;
if (parameters != null)
{
foreach (var param in parameters)
{
_cmd.Parameters.Add(param);
}
}
SQLiteDataAdapter da = new SQLiteDataAdapter(_cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public void Execute(string sql)
{
Execute(sql, new List<SQLiteParameter>());
}
public void Execute(string sql, Dictionary<string, object> dicParameters = null)
{
List<SQLiteParameter> lst = GetParametersList(dicParameters);
Execute(sql, lst);
}
public void Execute(string sql, IEnumerable<SQLiteParameter> parameters = null)
{
_cmd.CommandText = sql;
if (parameters != null)
{
foreach (var param in parameters)
{
_cmd.Parameters.Add(param);
}
}
_cmd.ExecuteNonQuery();
}
public object ExecuteScalar(string sql)
{
_cmd.CommandText = sql;
return _cmd.ExecuteScalar();
}
public object ExecuteScalar(string sql, Dictionary<string, object> dicParameters = null)
{
List<SQLiteParameter> lst = GetParametersList(dicParameters);
return ExecuteScalar(sql, lst);
}
public object ExecuteScalar(string sql, IEnumerable<SQLiteParameter> parameters = null)
{
_cmd.CommandText = sql;
if (parameters != null)
{
foreach (var parameter in parameters)
{
_cmd.Parameters.Add(parameter);
}
}
return _cmd.ExecuteScalar();
}
public dataType ExecuteScalar<dataType>(string sql, Dictionary<string, object> dicParameters = null)
{
List<SQLiteParameter> lst = null;
if (dicParameters != null)
{
lst = new List<SQLiteParameter>();
foreach (KeyValuePair<string, object> kv in dicParameters)
{
lst.Add(new SQLiteParameter(kv.Key, kv.Value));
}
}
return ExecuteScalar<dataType>(sql, lst);
}
public dataType ExecuteScalar<dataType>(string sql, IEnumerable<SQLiteParameter> parameters = null)
{
_cmd.CommandText = sql;
if (parameters != null)
{
foreach (var parameter in parameters)
{
_cmd.Parameters.Add(parameter);
}
}
return (dataType)Convert.ChangeType(_cmd.ExecuteScalar(), typeof(dataType));
}
public dataType ExecuteScalar<dataType>(string sql)
{
_cmd.CommandText = sql;
return (dataType)Convert.ChangeType(_cmd.ExecuteScalar(), typeof(dataType));
}
private List<SQLiteParameter> GetParametersList(Dictionary<string, object> dicParameters)
{
List<SQLiteParameter> lst = new List<SQLiteParameter>();
if (dicParameters != null)
{
foreach (KeyValuePair<string, object> kv in dicParameters)
{
lst.Add(new SQLiteParameter(kv.Key, kv.Value));
}
}
return lst;
}
public string Escape(string data)
{
data = data.Replace("'", "''");
data = data.Replace("\\", "\\\\");
return data;
}
public void Insert(string tableName, Dictionary<string, object> dic)
{
StringBuilder sbCol = new System.Text.StringBuilder();
StringBuilder sbVal = new System.Text.StringBuilder();
foreach (KeyValuePair<string, object> kv in dic)
{
if (sbCol.Length == 0)
{
sbCol.Append("insert into ");
sbCol.Append(tableName);
sbCol.Append("(");
}
else
{
sbCol.Append(",");
}
sbCol.Append("`");
sbCol.Append(kv.Key);
sbCol.Append("`");
if (sbVal.Length == 0)
{
sbVal.Append(" values(");
}
else
{
sbVal.Append(", ");
}
sbVal.Append("@v");
sbVal.Append(kv.Key);
}
sbCol.Append(") ");
sbVal.Append(");");
_cmd.CommandText = sbCol.ToString() + sbVal.ToString();
foreach (KeyValuePair<string, object> kv in dic)
{
_cmd.Parameters.AddWithValue("@v" + kv.Key, kv.Value);
}
_cmd.ExecuteNonQuery();
}
public void Update(string tableName, Dictionary<string, object> dicData, string colCond, object varCond)
{
Dictionary<string, object> dic = new Dictionary<string, object>();
dic[colCond] = varCond;
Update(tableName, dicData, dic);
}
public void Update(string tableName, Dictionary<string, object> dicData, Dictionary<string, object> dicCond)
{
if (dicData.Count == 0)
throw new Exception("dicData is empty.");
StringBuilder sbData = new System.Text.StringBuilder();
Dictionary<string, object> _dicTypeSource = new Dictionary<string, object>();
foreach (KeyValuePair<string, object> kv1 in dicData)
{
_dicTypeSource[kv1.Key] = null;
}
foreach (KeyValuePair<string, object> kv2 in dicCond)
{
if (!_dicTypeSource.ContainsKey(kv2.Key))
_dicTypeSource[kv2.Key] = null;
}
sbData.Append("update `");
sbData.Append(tableName);
sbData.Append("` set ");
bool firstRecord = true;
foreach (KeyValuePair<string, object> kv in dicData)
{
if (firstRecord)
firstRecord = false;
else
sbData.Append(",");
sbData.Append("`");
sbData.Append(kv.Key);
sbData.Append("` = ");
sbData.Append("@v");
sbData.Append(kv.Key);
}
sbData.Append(" where ");
firstRecord = true;
foreach (KeyValuePair<string, object> kv in dicCond)
{
if (firstRecord)
firstRecord = false;
else
{
sbData.Append(" and ");
}
sbData.Append("`");
sbData.Append(kv.Key);
sbData.Append("` = ");
sbData.Append("@c");
sbData.Append(kv.Key);
}
sbData.Append(";");
_cmd.CommandText = sbData.ToString();
foreach (KeyValuePair<string, object> kv in dicData)
{
_cmd.Parameters.AddWithValue("@v" + kv.Key, kv.Value);
}
foreach (KeyValuePair<string, object> kv in dicCond)
{
_cmd.Parameters.AddWithValue("@c" + kv.Key, kv.Value);
}
_cmd.ExecuteNonQuery();
}
public long LastInsertRowId()
{
return ExecuteScalar<long>("select last_insert_rowid();");
}
#endregion
#region Utilities
public void CreateTable(SqLiteTable table)
{
StringBuilder sb = new System.Text.StringBuilder();
sb.Append("create table if not exists `");
sb.Append(table.TableName);
sb.AppendLine("`(");
bool firstRecord = true;
foreach (SqLiteColumn col in table.Columns)
{
if (col.ColumnName.Trim().Length == 0)
{
throw new Exception("Column name cannot be blank.");
}
if (firstRecord)
firstRecord = false;
else
sb.AppendLine(",");
sb.Append(col.ColumnName);
sb.Append(" ");
if (col.AutoIncrement)
{
sb.Append("integer primary key autoincrement");
continue;
}
switch (col.ColDataType)
{
case ColType.Text:
sb.Append("text"); break;
case ColType.Integer:
sb.Append("integer"); break;
case ColType.Decimal:
sb.Append("decimal"); break;
case ColType.DateTime:
sb.Append("datetime"); break;
case ColType.Blob:
sb.Append("blob"); break;
}
if (col.PrimaryKey)
sb.Append(" primary key");
else if (col.NotNull)
sb.Append(" not null");
else if (col.DefaultValue.Length > 0)
{
sb.Append(" default ");
if (col.DefaultValue.Contains(" ") || col.ColDataType == ColType.Text || col.ColDataType == ColType.DateTime)
{
sb.Append("'");
sb.Append(col.DefaultValue);
sb.Append("'");
}
else
{
sb.Append(col.DefaultValue);
}
}
}
sb.AppendLine(");");
_cmd.CommandText = sb.ToString();
_cmd.ExecuteNonQuery();
}
public void RenameTable(string tableFrom, string tableTo)
{
_cmd.CommandText = string.Format("alter table `{0}` rename to `{1}`;", tableFrom, tableTo);
_cmd.ExecuteNonQuery();
}
public void CopyAllData(string tableFrom, string tableTo)
{
DataTable dt1 = Select(string.Format("select * from `{0}` where 1 = 2;", tableFrom));
DataTable dt2 = Select(string.Format("select * from `{0}` where 1 = 2;", tableTo));
Dictionary<string, bool> dic = new Dictionary<string, bool>();
foreach (DataColumn dc in dt1.Columns)
{
if (dt2.Columns.Contains(dc.ColumnName))
{
if (!dic.ContainsKey(dc.ColumnName))
{
dic[dc.ColumnName] = true;
}
}
}
foreach (DataColumn dc in dt2.Columns)
{
if (dt1.Columns.Contains(dc.ColumnName))
{
if (!dic.ContainsKey(dc.ColumnName))
{
dic[dc.ColumnName] = true;
}
}
}
StringBuilder sb = new System.Text.StringBuilder();
foreach (KeyValuePair<string, bool> kv in dic)
{
if (sb.Length > 0)
sb.Append(",");
sb.Append("`");
sb.Append(kv.Key);
sb.Append("`");
}
StringBuilder sb2 = new System.Text.StringBuilder();
sb2.Append("insert into `");
sb2.Append(tableTo);
sb2.Append("`(");
sb2.Append(sb.ToString());
sb2.Append(") select ");
sb2.Append(sb.ToString());
sb2.Append(" from `");
sb2.Append(tableFrom);
sb2.Append("`;");
_cmd.CommandText = sb2.ToString();
_cmd.ExecuteNonQuery();
}
public void DropTable(string table)
{
_cmd.CommandText = string.Format("drop table if exists `{0}`", table);
_cmd.ExecuteNonQuery();
}
public void UpdateTableStructure(string targetTable, SqLiteTable newStructure)
{
newStructure.TableName = targetTable + "_temp";
CreateTable(newStructure);
CopyAllData(targetTable, newStructure.TableName);
DropTable(targetTable);
RenameTable(newStructure.TableName, targetTable);
}
public void AttachDatabase(string database, string alias)
{
Execute(string.Format("attach '{0}' as '{1}';", database, alias));
}
public void DetachDatabase(string alias)
{
Execute(string.Format("detach {0};", alias));
}
#endregion
}
}
SqLiteColumnList类
using System;
using System.Collections.Generic;
using System.Data.SQLite;
namespace SQLiteHelper
{
public class SqLiteColumnList : IList<SqLiteColumn>
{
List<SqLiteColumn> _lst = new List<SqLiteColumn>();
private void CheckColumnName(string colName)
{
for (int i = 0; i < _lst.Count; i++)
{
if (_lst[i].ColumnName == colName)
throw new Exception("Column name of \"" + colName + "\" is already existed.");
}
}
public int IndexOf(SqLiteColumn item)
{
return _lst.IndexOf(item);
}
public void Insert(int index, SqLiteColumn item)
{
CheckColumnName(item.ColumnName);
_lst.Insert(index, item);
}
public void RemoveAt(int index)
{
_lst.RemoveAt(index);
}
public SqLiteColumn this[int index]
{
get
{
return _lst[index];
}
set
{
if (_lst[index].ColumnName != value.ColumnName)
{
CheckColumnName(value.ColumnName);
}
_lst[index] = value;
}
}
public void Add(SqLiteColumn item)
{
CheckColumnName(item.ColumnName);
_lst.Add(item);
}
public void Clear()
{
_lst.Clear();
}
public bool Contains(SqLiteColumn item)
{
return _lst.Contains(item);
}
public void CopyTo(SqLiteColumn[] array, int arrayIndex)
{
_lst.CopyTo(array, arrayIndex);
}
public int Count
{
get { return _lst.Count; }
}
public bool IsReadOnly
{
get { return false; }
}
public bool Remove(SqLiteColumn item)
{
return _lst.Remove(item);
}
public IEnumerator<SqLiteColumn> GetEnumerator()
{
return _lst.GetEnumerator();
}
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return _lst.GetEnumerator();
}
}
}
SqLiteColumn类
namespace SQLiteHelper
{
public class SqLiteColumn
{
public string ColumnName = "";
public bool PrimaryKey = false;
public ColType ColDataType = ColType.Text;
public bool AutoIncrement = false;
public bool NotNull = false;
public string DefaultValue = "";
public SqLiteColumn()
{ }
public SqLiteColumn(string colName)
{
ColumnName = colName;
PrimaryKey = false;
ColDataType = ColType.Text;
AutoIncrement = false;
}
public SqLiteColumn(string colName, ColType colDataType)
{
ColumnName = colName;
PrimaryKey = false;
ColDataType = colDataType;
AutoIncrement = false;
}
public SqLiteColumn(string colName, bool autoIncrement)
{
ColumnName = colName;
if (autoIncrement)
{
PrimaryKey = true;
ColDataType = ColType.Integer;
AutoIncrement = true;
}
else
{
PrimaryKey = false;
ColDataType = ColType.Text;
AutoIncrement = false;
}
}
public SqLiteColumn(string colName, ColType colDataType, bool primaryKey, bool autoIncrement, bool notNull, string defaultValue)
{
ColumnName = colName;
if (autoIncrement)
{
PrimaryKey = true;
ColDataType = ColType.Integer;
AutoIncrement = true;
}
else
{
PrimaryKey = primaryKey;
ColDataType = colDataType;
AutoIncrement = false;
NotNull = notNull;
DefaultValue = defaultValue;
}
}
}
}
SqLiteTable类
namespace SQLiteHelper
{
public class SqLiteTable
{
public string TableName = "";
public SqLiteColumnList Columns = new SqLiteColumnList();
public SqLiteTable()
{ }
public SqLiteTable(string name)
{
TableName = name;
}
}
}