using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.Sql;
using System.IO;

namespace SqliteClassLibrary
{
    public class SqliteBaseClass
    {
        #region//字段及屬性
        private SQLiteConnection sqliteConn;  //操作數(shù)據(jù)集
        private SQLiteDataAdapter sqliteDa;  //操作數(shù)據(jù)集
        private SQLiteCommand cmd;  //操作數(shù)據(jù)集
        private DataSet sqliteDs;  //操作數(shù)據(jù)集
        private string strdatabasefilepath;  //數(shù)據(jù)庫文件地址
        private string strtablename;  //操作數(shù)據(jù)表名稱
        private string strerrormessage;  //錯誤信息

        public string Strdatabasefilepath
        {
          get { return strdatabasefilepath; }
          set { strdatabasefilepath = value; }
        }
        public SQLiteCommand Cmd
        {
            get { return cmd; }
            set { cmd = value; }
        }

        public string Strerrormessage
        {
            get { return strerrormessage; }
            set { strerrormessage = value; }
        }

        public string Strtablename
        {
            get { return strtablename; }
            set { strtablename = value; }
        }

        public SQLiteConnection SqliteConn
        {
            get { return sqliteConn; }
            set { sqliteConn = value; }
        }

        public SQLiteDataAdapter SqliteDa
        {
            get { return sqliteDa; }
            set { sqliteDa = value; }
        }

        public DataSet SqliteDs
        {
            get { return sqliteDs; }
            set { sqliteDs = value; }
        }
        #endregion
        /// <summary>
        /// 構(gòu)造函數(shù)
        /// </summary>
        /// <param name="strconnstringtemp">數(shù)據(jù)庫全路徑</param>
        /// <param name="strtablenametemp">操作數(shù)據(jù)表名稱</param>
        public SqliteBaseClass(string strdatabasefilepathtemp, string strtablenametemp)
        {
            strdatabasefilepath=strdatabasefilepathtemp;
            strtablename = strtablenametemp;
            sqliteConn = new SQLiteConnection("Data Source=" + @strdatabasefilepath + ";New=False;Compress=True;Version=3;");
            sqliteConn.Open();
        }
        /// <summary>
        /// 構(gòu)造函數(shù)
        /// </summary>
        /// <param name="strconnstringtemp">數(shù)據(jù)庫全路徑</param>
        /// <param name="strtablenametemp">操作數(shù)據(jù)表名稱</param>
        public SqliteBaseClass(string strdatabasefilepathtemp)
        {
            strdatabasefilepath = strdatabasefilepathtemp;
        }
        #region//數(shù)據(jù)庫操作方法
        /// <summary>
        /// 全數(shù)據(jù)查詢
        /// </summary>
        /// <param name="filter">帶where條件查詢語句（例如：where field='value'）</param>
        /// <returns>dataset</returns>
        public DataSet getDataSetQueryAll(string filter)
        {
            return setMessageQuery(@"select * from " + strtablename + filter);
        }
        /// <summary>
        /// 選擇字段查詢
        /// </summary>
        /// <param name="strdisplayfield">逗號分割的多字段字符串</param>
        /// <param name="filter">帶where條件查詢語句（例如：where field='value'）</param>
        /// <returns>dataset</returns>
        public DataSet getDataSetQueryDisplayField(string strdisplayfield, string filter)
        {
            return setMessageQuery(@"select " + "\'" + strdisplayfield + "\'" + " from " + strtablename + filter);
        }
        /// <summary>
        /// 自定義查詢
        /// </summary>
        /// <param name="strsql">sql全查詢語句</param>
        /// <returns>dataset</returns>
        public DataSet getDataSetQueryContSql(string strsql)
        {
            return setMessageQuery(strsql);
        }
        /// <summary>
        /// 插入操作
        /// </summary>
        /// <param name="strinsertfields">逗號分割的需要插入的多字段字符串</param>
        /// <param name="strinserfieldsvalues">逗號分割的需要插入的多字段值字符串</param>
        /// <returns>int</returns>
        public int insertFieldData(string strinsertfields, string strinserfieldsvalues)
        {
            return setErrorMessage("INSERT INTO " + strtablename + "(" + strinsertfields + ") VALUES (" + strinserfieldsvalues + ")", 0);
        }
        /// <summary>
        /// 修改操作
        /// </summary>
        /// <param name="strupdatefields">逗號分割的需要修改的多字段字符串</param>
        /// <param name="strupdatefieldsvalues">逗號分割的需要修改的多字段值字符串</param>
        /// <param name="filter">帶where條件查詢語句（例如：where field='value'）</param>
        /// <returns>int</returns>
        public int updateFieldData(string strupdatefields, string strupdatefieldsvalues, string filter)
        {
            string[] strfields = strupdatefields.Split(',');
            string[] strfieldsvalues = strupdatefieldsvalues.Split(',');
            string strsqltemp = null;
            for (int i = 0; i < strfields.Length; ++i)
            {
                if (i == strfields.Length - 1)
                    strsqltemp += strfields[i].ToString() + "=" + "\'" + strfieldsvalues[i].ToString() + "\'";
                else
                    strsqltemp += strfields[i].ToString() + "=" + "\'" + strfieldsvalues[i].ToString() + "\'" + ",";
            }
            //update test set 'text=''fsaefasefsa''' where id='24'
            return setErrorMessage("update " + strtablename + " set " + strsqltemp + " " + filter, 1);
        }
        /// <summary>
        /// 自定義插入或修改操作
        /// </summary>
        /// <param name="strsql">sql全操作語句</param>
        /// <returns>int</returns>
        public int executeContSql(string strsql)
        {
            return setErrorMessage(strsql, 3);
        }
        /// <summary>
        /// 全數(shù)據(jù)刪除操作
        /// </summary>
        /// <returns>int</returns>
        public int deleteDataAll()
        {
            return setErrorMessage("delete from " + strtablename, 2);
        }
        /// <summary>
        /// 條件數(shù)據(jù)刪除操作
        /// </summary>
        /// <param name="filter">帶where條件查詢語句（例如：where field='value'）</param>
        /// <returns>int</returns>
        public int deleteDataFilter(string filter)
        {
            return setErrorMessage("delete from " + strtablename + "\'" + filter + "\'", 2);
        }
        /// <summary>
        /// 建立數(shù)據(jù)表
        /// </summary>
        /// <param name="strcreatsql">建立數(shù)據(jù)表sql語句</param>
        /// <returns>int</returns>
        public int creatDataBaseTable(string strcreatsql)
        {
            return setErrorMessage(strcreatsql, 4);
        }
        /// <summary>
        /// 刪除數(shù)據(jù)表
        /// </summary>
        /// <param name="strcreatsql">刪除數(shù)據(jù)表sql語句</param>
        /// <returns>int</returns>
        public int deleteDataBaseTable(string strcreatsql)
        {
            return setErrorMessage(strcreatsql, 5);
        }
        /// <summary>
        /// 建立數(shù)據(jù)庫
        /// </summary>
        /// <returns></returns>
        public int creatDataBase()
        {
            try
            {
                //if (File.Exists(strdatabasefilepath) ==true)
                //{
                //    File.Delete(strdatabasefilepath);
                //}
                SQLiteConnection.CreateFile(strdatabasefilepath);
                strerrormessage = "數(shù)據(jù)庫建立執(zhí)行成功！";
                return 1;
            }
            catch(SQLiteException ex)
            {
                strerrormessage = "數(shù)據(jù)庫建立執(zhí)行失敗,失敗原因：" + ex.Message;
                return -1;
            }
        }
        public int deleteDataBase()
        {
            try
            {
                if (File.Exists(strdatabasefilepath) == true)
                {
                    File.Delete(strdatabasefilepath);
                }
                strerrormessage = "數(shù)據(jù)庫刪除執(zhí)行成功！";
                return 1;
            }
            catch (SQLiteException ex)
            {
                strerrormessage = "數(shù)據(jù)庫刪除執(zhí)行失敗,失敗原因：" + ex.Message;
                return -1;
            }
        }
        #endregion
        #region//內(nèi)部操作方法
        /// <summary>
        /// 設(shè)置操作錯誤信息
        /// </summary>
        /// <param name="strcommandtext">操作語句</param>
        /// <param name="inttype">操作類型</param>
        /// <returns>int</returns>
        private int setErrorMessage(string strcommandtext, int inttype)
        {
            SQLiteCommand cmd = sqliteConn.CreateCommand();
            cmd.CommandText = strcommandtext;
            int inttemp = 0;
            try
            {
                inttemp = cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                strerrormessage = ex.Message;
            }
            switch (inttype)
            {
                case 0:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄插入操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                                    break;
                                }
                            case 1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄插入操作執(zhí)行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 1:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄修改操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                                    break;
                                }
                            case 1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄修改操作執(zhí)行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 2:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄刪除操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                                    break;
                                }
                            case 0:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄刪除操作執(zhí)行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 3:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄自定義操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                                    break;
                                }
                            case 1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表記錄自定義操作執(zhí)行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 4:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表建立操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                                    break;
                                }
                            case 0:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表建立操作執(zhí)行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 5:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表刪除操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                                    break;
                                }
                            case 0:
                                {
                                    strerrormessage = "數(shù)據(jù)庫表刪除操作執(zhí)行成功！";
                                    break;
                                }
                        }
                        break;
                    }
            }

            return inttemp;
        }
        /// <summary>
        /// 設(shè)置操作錯誤信息
        /// </summary>
        /// <param name="strcommandtext">操作語句</param>
        /// <returns></returns>
        private DataSet setMessageQuery(string strcommandtext)
        {
            int inttemp = 0;
            try
            {
                sqliteDa = new SQLiteDataAdapter(strcommandtext, sqliteConn);
                sqliteDs = new DataSet();
                inttemp = sqliteDa.Fill(sqliteDs);
            }
            catch (SQLiteException ex)
            {
                strerrormessage = ex.Message;
            }
            switch (inttemp)
            {
                case -1:
                    {
                        strerrormessage = "數(shù)據(jù)庫查詢操作執(zhí)行失敗,失敗原因：" + strerrormessage;
                        break;
                    }
                case 1:
                    {
                        strerrormessage = "數(shù)據(jù)庫查詢操作執(zhí)行成功！";
                        break;
                    }
            }
            return sqliteDs;
        }
        #endregion
    }
}