Sqlite
1.注意保存到数据库的日期格式DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
格式不统一日期查询的时候会出现搜索不准确问题.
2.表设计推荐使用 sqlitestudio 工具
using System;
using System.Collections.Generic;
using System.Text;
namespace System.Data.SQLite
{
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;
}
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
namespace System.Data.SQLite
{
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();
}
Collections.IEnumerator Collections.IEnumerable.GetEnumerator()
{
return _lst.GetEnumerator();
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
namespace System.Data.SQLite
{
public class SQLiteTable
{
public string TableName = "";
public SQLiteColumnList Columns = new SQLiteColumnList();
public SQLiteTable()
{ }
public SQLiteTable(string name)
{
TableName = name;
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Globalization;
using System.IO;
namespace System.Data.SQLite
{
public enum ColType
{
Text,
DateTime,
Integer,
Decimal,
BLOB
}
public class SQLiteHelper
{
public static string ConnectionString = "路径";
public string Error = "";
/// <summary>
/// 创建数据库
/// </summary>
public bool CreateDataBase()
{
string path = Path.GetDirectoryName(ConnectionString.Replace("data source=", ""));
if ((!string.IsNullOrWhiteSpace(path)) && (!Directory.Exists(path)))
{
Directory.CreateDirectory(path);
}
if (!File.Exists(ConnectionString))
{
SQLiteConnection.CreateFile(ConnectionString);
}
return true;
}
//创建表代码:
//SQLiteTable tb = new SQLiteTable("person");
//tb.Columns.Add(new SQLiteColumn("id", true));
//tb.Columns.Add(new SQLiteColumn("name"));
//tb.Columns.Add(new SQLiteColumn("membershipid", ColType.Integer));
//tb.Columns.Add(new SQLiteColumn("level", ColType.Decimal, false, false, "5.5"));
//sh.CreateTable(tb);
/// <summary>
/// 创建表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public bool CreateTable(SQLiteTable table)
{
StringBuilder sb = new 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(");");
try
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
SQLiteCommand comm = new SQLiteCommand();
comm.Connection = conn;
comm.CommandText = sb.ToString();
comm.CommandType = CommandType.Text;
comm.CommandTimeout = conn.ConnectionTimeout;
comm.ExecuteNonQuery();
conn.Close();
}
return true;
}
catch (Exception ex)
{
Error = "系统故障:GetDataSetBySQLString," + ex.ToString();
return false;
}
}
/// <summary>
/// 查询数据
/// </summary>
public DataTable GetDataTableBySql(string sql)
{
return GetDataSetBySQLString(sql).Tables[0];
}
public DataSet GetDataSetBySQLString(string SQLString)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
SQLiteCommand comm = new SQLiteCommand();
comm.Connection = conn;
comm.CommandText = SQLString;
comm.CommandType = CommandType.Text;
comm.CommandTimeout = conn.ConnectionTimeout;
DataSet ds = new DataSet("SQLDataSet");
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
adapter.SelectCommand = comm;
adapter.Fill(ds, "SQLDataSet");
conn.Close();
return ds;
}
}
catch (Exception ex)
{
Error = "系统故障:GetDataSetBySQLString," + ex.ToString();
return null;
}
}
/// <summary>
/// 添加或更新数据
/// </summary>
public int UpdateBySQL(string sql)
{
using (SQLiteConnection con = new SQLiteConnection(ConnectionString))
{
con.Open();
SQLiteCommand cmd = null;
try
{
cmd = new SQLiteCommand(sql, con);
object objResult = cmd.ExecuteScalar();
con.Close();
if (objResult == null)
{
return 0;
}
else
{
return 1;
}
}
catch (Exception ex)
{
Error = ex.ToString();
con.Close();
Error = "系统故障:UpdateBySQL," + ex.ToString();
return -1;
}
}
}
/// <summary>
/// 删除数据库数据
/// </summary>
public int DeltetBySQL(string sql)
{
using (SQLiteConnection con = new SQLiteConnection(ConnectionString))
{
con.Open();
SQLiteCommand cmd = null;
try
{
cmd = new SQLiteCommand(sql, con);
int objResult = cmd.ExecuteNonQuery();
con.Close();
return 0;
}
catch (Exception ex)
{
Error = ex.ToString();
con.Close();
Error = "系统故障:DeltetBySQL," + ex.ToString();
return -1;
}
}
}
}
}
调用方法
SQLiteHelper.ConnectionString ="data source=F:\db\Db_Phone";
//创建数据库
SQLiteHelper sh = new SQLiteHelper();
//创建表
SQLiteTable tb = new SQLiteTable("person");
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("membershipid", ColType.Integer));
tb.Columns.Add(new SQLiteColumn("level", ColType.Decimal, false, false, "5.5"));
sh.CreateTable(tb);
//调用语句
string sql = string.Format("INSERT INTO PicFiles (SN,Path,Createdate) VALUES ('{0}','{1}','{2}')", "fan", "wen", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int rel = sh.UpdateBySQL(sql);
浙公网安备 33010602011771号