[大数据]c#Sqlite类库

字符串转Datetime 采坑

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);