sqlitebaseclass.cs

简单的c# sqlite数据库操作类库

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;  //操作数据集
        private SQLiteDataAdapter sqliteDa;  //操作数据集
        private SQLiteCommand cmd;  //操作数据集
        private DataSet sqliteDs;  //操作数据集
        private string strdatabasefilepath;  //数据库文件地址
        private string strtablename;  //操作数据表名称
        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>
        /// 构造函数
        /// </summary>
        /// <param name="strconnstringtemp">数据库全路径</param>
        /// <param name="strtablenametemp">操作数据表名称</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>
        /// 构造函数
        /// </summary>
        /// <param name="strconnstringtemp">数据库全路径</param>
        /// <param name="strtablenametemp">操作数据表名称</param>
        public SqliteBaseClass(string strdatabasefilepathtemp)
        {
            strdatabasefilepath = strdatabasefilepathtemp;
        }
        #region//数据库操作方法
        /// <summary>
        /// 全数据查询
        /// </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>
        /// 全数据删除操作
        /// </summary>
        /// <returns>int</returns>
        public int deleteDataAll()
        {
            return setErrorMessage("delete from " + strtablename, 2);
        }
        /// <summary>
        /// 条件数据删除操作
        /// </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>
        /// 建立数据表
        /// </summary>
        /// <param name="strcreatsql">建立数据表sql语句</param>
        /// <returns>int</returns>
        public int creatDataBaseTable(string strcreatsql)
        {
            return setErrorMessage(strcreatsql, 4);
        }
        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="strcreatsql">删除数据表sql语句</param>
        /// <returns>int</returns>
        public int deleteDataBaseTable(string strcreatsql)
        {
            return setErrorMessage(strcreatsql, 5);
        }
        /// <summary>
        /// 建立数据库
        /// </summary>
        /// <returns></returns>
        public int creatDataBase()
        {
            try
            {
                //if (File.Exists(strdatabasefilepath) ==true)
                //{
                //    File.Delete(strdatabasefilepath);
                //}
                SQLiteConnection.CreateFile(strdatabasefilepath);
                strerrormessage = "数据库建立执行成功！";
                return 1;
            }
            catch(SQLiteException ex)
            {
                strerrormessage = "数据库建立执行失败,失败原因：" + ex.Message;
                return -1;
            }
        }
        public int deleteDataBase()
        {
            try
            {
                if (File.Exists(strdatabasefilepath) == true)
                {
                    File.Delete(strdatabasefilepath);
                }
                strerrormessage = "数据库删除执行成功！";
                return 1;
            }
            catch (SQLiteException ex)
            {
                strerrormessage = "数据库删除执行失败,失败原因：" + ex.Message;
                return -1;
            }
        }
        #endregion
        #region//内部操作方法
        /// <summary>
        /// 设置操作错误信息
        /// </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 = "数据库表记录插入操作执行失败,失败原因：" + strerrormessage;
                                    break;
                                }
                            case 1:
                                {
                                    strerrormessage = "数据库表记录插入操作执行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 1:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "数据库表记录修改操作执行失败,失败原因：" + strerrormessage;
                                    break;
                                }
                            case 1:
                                {
                                    strerrormessage = "数据库表记录修改操作执行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 2:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "数据库表记录删除操作执行失败,失败原因：" + strerrormessage;
                                    break;
                                }
                            case 0:
                                {
                                    strerrormessage = "数据库表记录删除操作执行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 3:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "数据库表记录自定义操作执行失败,失败原因：" + strerrormessage;
                                    break;
                                }
                            case 1:
                                {
                                    strerrormessage = "数据库表记录自定义操作执行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 4:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "数据库表建立操作执行失败,失败原因：" + strerrormessage;
                                    break;
                                }
                            case 0:
                                {
                                    strerrormessage = "数据库表建立操作执行成功！";
                                    break;
                                }
                        }
                        break;
                    }
                case 5:
                    {
                        switch (inttemp)
                        {
                            case -1:
                                {
                                    strerrormessage = "数据库表删除操作执行失败,失败原因：" + strerrormessage;
                                    break;
                                }
                            case 0:
                                {
                                    strerrormessage = "数据库表删除操作执行成功！";
                                    break;
                                }
                        }
                        break;
                    }
            }

            return inttemp;
        }
        /// <summary>
        /// 设置操作错误信息
        /// </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 = "数据库查询操作执行失败,失败原因：" + strerrormessage;
                        break;
                    }
                case 1:
                    {
                        strerrormessage = "数据库查询操作执行成功！";
                        break;
                    }
            }
            return sqliteDs;
        }
        #endregion
    }
}