SQLite在C#的使用
SQLite用于存取程序中固定的值
1 首先从官网上下载SQLite对于.Net的插件
地址：https://skydrive.live.com/?cid=ba8e07f137f7785d#cid=BA8E07F137F7785D&id=BA8E07F137F7785D%21129
2 添加SQLite的引用
3 使用。
4 如果成功，我们可以通过火狐浏览器的SQLite Manager进行查看表数据。
代码如下：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace OD.Frm { public partial class StartFrm : Form { delegate string ShowMSG();//定义委托获取线程内返回内容 public StartFrm() { InitializeComponent(); } public string CreateDefaultDatabase() { OD.BLL.LocalDataBaseBLL defaultdata = new BLL.LocalDataBaseBLL(); if (defaultdata.CreateDataBase())//如果创建数据库成功创建基础资料表 { OD.BLL.DefaultConfigBLL defaultconfig = new BLL.DefaultConfigBLL(defaultdata); defaultconfig.CreatePaymentTable(); defaultconfig.CreateRefundStateTable(); defaultconfig.CreateTradeStateTable(); defaultconfig.CreateTradeTypeTable(); defaultconfig.CreateTaskTypeTable(); defaultconfig.CreateTaskTable(); defaultconfig.CreateShopTable(); defaultconfig.ErrorLog(); defaultconfig.CreateTaskErrorStore(); defaultconfig.createDeliveryMethodType(); } return "success"; } private void StartFrm_Load(object sender, EventArgs e) { ShowMSG task= CreateDefaultDatabase; IAsyncResult asyncResult = task.BeginInvoke(MethodCompleted, task); } private void MethodCompleted(IAsyncResult asyncResult) { if (asyncResult == null) return; //textBox1.Text = "success";//(asyncResult.AsyncState as ShowMSG).EndInvoke(asyncResult).ToString(); DialogResult = System.Windows.Forms.DialogResult.OK; } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using OD.DAL; using System.Collections; namespace OD.BLL { public class LocalDataBaseBLL { private readonly OD.SqliteData.SqliteDataObject dal = new OD.SqliteData.SqliteDataObject(); private readonly OD.SqliteData.LocalShop localshop = new SqliteData.LocalShop(); /// <summary> /// 创建数据库 /// </summary> public bool CreateDataBase() { string errText = string.Empty; dal.CreateDataBase(ref errText); if (errText.Length > 0) { return false; } return true; } /// <summary> /// 创建表 /// </summary> /// <param name="CommandText">create table T(...</param> public bool CreateTable(string CommandText) { return dal.CreateTable(CommandText); } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public void ExecuteSqlTran(ArrayList SQLStringList) { dal.ExecuteSqlTran(SQLStringList); } /// <summary> /// 执行一条语句 /// </summary> public int Add(string strSql) { return dal.Add(strSql); } /// <summary> /// 返回一个DataSet结果集 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public DataSet GetList(string strSql) { return dal.GetList(strSql); } /// <summary> /// 返回首行首列 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public object GetScalar(string strSql) { return dal.GetScalar(strSql); } /// <summary> /// 是否存在该记录 /// </summary> public bool Exists(string strSql) { return dal.Exists(strSql); } /// <summary> /// 查询本地数据库是否存在 /// </summary> public static bool ExistsDataBase() { return OD.SqliteData.SqliteDataObject.ExistsDataBase(); } /// <summary> /// 插入或更新店铺 /// </summary> /// <param name="shopid">店铺编号</param> /// <param name="ptid">平台ID</param> /// <param name="title">店铺名</param> /// <param name="appkey">appkey</param> /// <param name="appsecret">appsecret</param> /// <param name="sessionkey">sessionkey/accesstoken</param> /// <param name="createtime">createtime</param> /// <returns></returns> public bool InsertShop(string shopid, int ptid, string title, string appkey, string appsecret, string sessionkey, string createtime) { return localshop.InsertShop(shopid, ptid, title, appkey, appsecret, sessionkey, createtime); } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; using System.IO; namespace OD.SqliteData { public class SqliteDataObject { /// <summary> /// 创建数据库 /// </summary> public void CreateDataBase(ref string errText) { SQLiteHelper.CreateDataBase(ref errText); } /// <summary> /// 创建表 /// </summary> /// <param name="CommandText">create table T(...</param> public bool CreateTable(string CommandText) { return SQLiteHelper.CreateTable(CommandText); } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public void ExecuteSqlTran(ArrayList SQLStringList) { SQLiteHelper.ExecuteSqlTran(SQLStringList); } /// <summary> /// 执行一条语句 /// </summary> public int Add(string strSql) { return SQLiteHelper.ExecuteSql(strSql); } /// <summary> /// 返回一个DataSet结果集 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public DataSet GetList(string strSql) { return SQLiteHelper.Query(strSql); } /// <summary> /// 返回首行首列 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public object GetScalar(string strSql) { return SQLiteHelper.GetSingle(strSql); } /// <summary> /// 是否存在该记录 /// </summary> public bool Exists(string strSql) { return SQLiteHelper.Exists(strSql); } /// <summary> /// 查询本地数据库是否存在 /// </summary> public static bool ExistsDataBase() { string datafile = SQLiteHelper.connectionString.Substring(12); return File.Exists(datafile); } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using OD.DAL; using System.Collections; namespace OD.BLL { public class DefaultConfigBLL { private LocalDataBaseBLL localdatabase; public DefaultConfigBLL(LocalDataBaseBLL Localdatabase) { this.localdatabase = Localdatabase; } /**************************Tyler start************************************/ #region 订单状态表 /// <summary> /// 创建订单状态表 /// </summary> public void CreateTradeStateTable() { //bool iscreated = localdatabase.CreateTable("create table TradeState(state_id NUMBER NOT NULL,state_name_en VARCHAR2(60),state_name_cn VARCHAR2(60),ptid NUMBER NOT NULL)"); bool iscreated = localdatabase.CreateTable("create table TradeState(state_id VARCHAR2(60) NOT NULL,state_name_en VARCHAR2(60),state_name_cn VARCHAR2(60),ptid NUMBER NOT NULL)"); if (iscreated) { //淘宝 AddTradeStateTable(0, "TRADE_NO_CREATE_PAY", "没有创建支付宝交易", 1); AddTradeStateTable(1, "WAIT_BUYER_PAY", "等待买家付款", 1); AddTradeStateTable(2, "WAIT_SELLER_SEND_GOODS", "等待卖家发货,即买家已付款", 1); AddTradeStateTable(3, "WAIT_BUYER_CONFIRM_GOODS", "等待买家确认收货,即卖家已发货", 1); AddTradeStateTable(4, "TRADE_FINISHED", "交易成功", 1); AddTradeStateTable(5, "TRADE_CLOSED", "付款以后用户退款成功，交易自动关闭", 1); AddTradeStateTable(6, "TRADE_CANCELED", "付款以前，卖家或买家主动关闭交易", 1); AddTradeStateTable(7, "TRADE_BUYER_SIGNED", "买家已签收,货到付款专用", 1); AddTradeStateTable(8, "TRADE_CLOSED_BY_TAOBAO","交易被淘宝关闭",1); AddTradeStateTable(9, "ALL_WAIT_PAY","所有未付款订单(包含：WAIT_BUYER_PAY、TRADE_NO_CREATE_PAY)",1); AddTradeStateTable(10, "ALL_CLOSED","所有关闭的订单(包含：TRADE_CLOSED、TRADE_CLOSED_BY_TAOBAO)",1); AddTradeStateTable(0, "TRADE_NO_CREATE_PAY", "没有创建支付交易", 3); AddTradeStateTable(1, "WAIT_BUYER_PAY", "等待买家付款", 3); AddTradeStateTable(2, "WAIT_SELLER_SEND_GOODS", "等待卖家发货,即买家已付款", 3); AddTradeStateTable(3, "WAIT_BUYER_CONFIRM_GOODS", "等待买家确认收货,即卖家已发货", 3); AddTradeStateTable(4, "TRADE_FINISHED", "交易成功", 3); AddTradeStateTable(5, "TRADE_CLOSED", "付款以后用户退款成功，交易自动关闭", 3); AddTradeStateTable(6, "TRADE_CANCELED", "付款以前，卖家或买家主动关闭交易", 3); AddTradeStateTable(7, "TRADE_BUYER_SIGNED", "买家已签收,货到付款专用", 3); AddTradeStateTable(8, "TRADE_CLOSED_BY_SHANGJIA", "交易被商家关闭", 3); //京东 AddTradeStateTable(2,"WAIT_SELLER_STOCK_OUT","等待出库" ,2); AddTradeStateTable(3, "WAIT_GOODS_RECEIVE_CONFIRM","等待确认收货",2); AddTradeStateTable(4, "FINISHED_L", "完成", 2); AddTradeStateTable(6, "TRADE_CANCELED", "取消", 2); AddTradeStateTable(7, "RECEIPTS_CONFIRM", "收款确认(服务完成)(只适用于LBP,SOPL商家)", 2); AddTradeStateTable(11, "SEND_TO_DISTRIBUTION_CENER", "发往配送中心(只适用于LBP,SOPL商家)", 2); AddTradeStateTable(12,"DISTRIBUTION_CENTER_RECEIVED","配送中心已收货(只适用于LBP,SOPL商家)",2); //拍拍 //一号店 AddTradeStateTable(1, "ORDER_WAIT_PAY", "已下单（货款未全收）", 7); AddTradeStateTable(2, "ORDER_PAYED", "已下单（货款已收）", 7); AddTradeStateTable(3, "ORDER_RECEIVED", "货物用户已收到", 7); AddTradeStateTable(4, "ORDER_FINISH", "订单完成", 7); AddTradeStateTable(6, "ORDER_CANCEL", "订单取消", 7); AddTradeStateTable(2, "ORDER_TRUNED_TO_DO", "可以发货（已送仓库）", 7); AddTradeStateTable(2, "ORDER_CAN_OUT_OF_WH", "可出库", 7); AddTradeStateTable(15, "ORDER_OUT_OF_WH", "已出库（货在途）", 7); AddTradeStateTable(16, "ORDER_SENDED_TO_LOGITSIC", "已发送物流", 7); AddTradeStateTable(17, "ORDER_CUSTOM_CALLTO_RETURN", "用户要求退货", 7); AddTradeStateTable(18, "ORDER_CUSTOM_CALLTO_CHANGE", "用户要求换货", 7); AddTradeStateTable(19, "ORDER_RETURNED", "退货完成", 7); AddTradeStateTable(20, "ORDER_CHANGE_FINISHED", "换货完成", 7); //买买茶 //云媒 //舒宁易购 /*AddTradeStateTable(8, "TRADE_CALLTO_RETURN", "用户要求退货", 1); AddTradeStateTable(9, "TRADE_CALLTO_CHANGE", "用户要求换货", 1); AddTradeStateTable(10, "TRADE_RETURNED", "退货完成", 1); AddTradeStateTable(11, "TRADE_CHANGE_FINISHED", "换货完成", 1);*/ } } } }
