mes-client/UAS-出货标签管理(吉利通)/PublicMethod/SqliteDBHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace UAS_LabelMachine
{
    class SqliteDBHelper
    {
        private string _fileName;
        private string _connectionString;
        private SQLiteConnection _odcConnection = null;
        private SQLiteCommand command = null;

        public SqliteDBHelper(string fileName)
        {
            this._fileName = fileName;
            this._connectionString = @"Data Source=" + fileName + ";";
            Open();
        }

        public void Open()
        {
            try
            {
                // 建立连接
                this._odcConnection = new SQLiteConnection(this._connectionString);
                // 打开连接
                this._odcConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void ExecuteZip()
        {
            command = new SQLiteCommand("Vacuum", this._odcConnection);
            command.ExecuteNonQuery();
        }

        public void Close()
        {
            this._odcConnection.Close();
        }


        public DataTable GetDataTable(string sql)
        {
            DataTable ds = new DataTable();
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception("sql語句： " + sql + " 執行失敗！" + ex.Message);
            }
            adapter.Dispose();
            return ds;
        }

        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception("sql語句： " + sql + " 執行失敗！" + ex.Message);
            }
            adapter.Dispose();
            return ds;
        }

        public void Reconnect(SQLiteCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
                //超时重连
            }
        }

        /// <summary>
        /// 根据表名获取该表字段数据类型
        /// </summary>
        public DataTable GetColumnDataType(string TableName)
        {
            DataTable dt = new DataTable();
            command = new SQLiteCommand("select Column_Name,Data_Type from cols where TABLE_name=upper('" + TableName + "')", this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            ad.Fill(dt);
            ad.Dispose();
            return dt;
        }

        /// <summary>
        /// 获取第一行第一列的信息
        /// </summary>
        public object getFieldDataByCondition(string TableName, string Field, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select " + Field + " from " + TableName + " where " + Condition;
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter();
            ad.SelectCommand = command;
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            else
            {
                return "";
            }
        }

        public void BatchInsertDataTable(string sql, string[] param, params object[][] param1)
        {
            command = new SQLiteCommand(sql, _odcConnection);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter();
            adapter.UpdateBatchSize = param1[0].Length;
            //因为第一个数组保存的是参数的名称，所以循环从1而不是0开始
            //将第一个数组的下标固定为0作为循环添加的参数的名称
            for (int i = 0; i < param.Length; i++)
            {
                command.Parameters.Add(new SQLiteParameter(param[i].ToString(), param1[i]));
            }
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
        }

        public void DeleleFile(string accessFile)
        {
            var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
            try
            {
                string tempFile = Path.Combine(Path.GetDirectoryName(accessFile), Path.GetRandomFileName() + Path.GetExtension(accessFile));
                dbe.CompactDatabase(accessFile, tempFile);
                FileInfo temp = new FileInfo(tempFile);
                temp.CopyTo(accessFile, true);
                temp.Delete();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }

        /// <summary>
        /// 插入DataTable到Access数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TableName"></param>
        public void SaveDataTable(DataTable dt, string TableName)
        {
            StringBuilder sb = new StringBuilder();
            //预防插入的DataTable中存在不属于该表的列，在进行下一步操作之前全部剔除
            //DataTable data = (DataTable)ExecuteSql("select top 1 * from " + TableName, "select");
            ////将所有的字段拼接起来
            //for (int i = 0; i < data.Columns.Count; i++)
            //{
            //    sb.Append("#" + data.Columns[i].ColumnName.ToString().ToUpper());
            //}
            ////移除掉所有不属于该表的列
            //for (int i = dt.Columns.Count - 1; i >= 0; i--)
            //{
            //    if (!sb.ToString().Contains(dt.Columns[i].ColumnName.ToUpper()))
            //    {
            //        dt.Columns.RemoveAt(i);
            //    }
            //}
            sb.Clear();
            sb.Append("insert into " + TableName + " (");
            string field = "";
            string valuefield = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (i != dt.Columns.Count - 1)
                {
                    field += dt.Columns[i].ColumnName + ",";
                    valuefield += "?,";
                }
                else
                {
                    field += dt.Columns[i].ColumnName + "";
                    valuefield += "?";
                }
            }
            sb.Append(field + ") values(" + valuefield + ")");
            SQLiteTransaction tx = this._odcConnection.BeginTransaction();
            command = new SQLiteCommand();
            command.Connection = this._odcConnection;
            command.Transaction = tx;
            string sql = sb.ToString();
            try
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    command.CommandText = sql;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        command.Parameters.AddWithValue(dt.Columns[j].ColumnName, dt.Rows[i][j]);
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message.ToString());
                    }
                }
                tx.Commit();
            }
            catch (Exception E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }

        public void BatchInsert(string tableName, DataTable dt)
        {
            List<string> columnList = new List<string>();
            foreach (DataColumn one in dt.Columns)
            {
                columnList.Add(one.ColumnName);
            }
            SQLiteDataAdapter adapter = new SQLiteDataAdapter();
            adapter.SelectCommand = new SQLiteCommand("select pib_id from " + tableName, this._odcConnection);
            using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter))
            {
                adapter.InsertCommand = builder.GetInsertCommand();
                foreach (string one in columnList)
                {
                    adapter.InsertCommand.Parameters.Add(new SQLiteParameter(one.ToLower(), "Test"));
                }
            }
        }

        /// <summary>
        /// 获取指定表的记录的条数 ,带条件
        /// </summary>
        /// <returns></returns>
        public int getRowCount(string TableName, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select count(1) from " + TableName + " where " + Condition;
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            ad.Fill(dt);
            ad.Dispose();
            return int.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取指定表的记录的条数 ,不带条件
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public int getRowCount(string TableName)
        {
            DataTable dt = new DataTable();
            string sql = "select count(1) from " + TableName;
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            ad.Fill(dt);
            ad.Dispose();
            return int.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 通过表名和获取单行的记录
        /// </summary>
        public DataTable getFieldsDataByCondition(string TableName, string[] Fields, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += AddField(Fields);
            sql += " from " + TableName + " where " + Condition + " and rownum=1";
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            return dt;
        }

        /// <summary>
        /// 按分页获取数据
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Fields">查询字段</param>
        /// <param name="CurrentPage">当前页面</param>
        /// <param name="PageSize">页面展示条数</param>
        /// <param name="Caller"></param>
        /// <returns></returns>
        // SELECT * FROM (SELECT   A.*  FROM (SELECT* FROM datalist) A WHERE ROWNUM <= 50) WHERE ROWNUM >= 21
        public DataTable getFieldsDatasByPageing(string TableName, string Fields, int CurrentPage, int PageSize, string Caller, params string[] condition)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            //先查询出配置出去的列
            //获取查询的列
            string[] caption = GetCaptionFromField(Fields);
            //获取对应列的描述
            string[] field = GetField(Fields);
            sql.Append(" select * from (select RowNum RN, A.* from (select ");
            sql.Append(AddField(caption));
            if (condition.Length > 0)
            {
                if (condition[0] != null && condition[0].Trim() != "")
                    sql.Append(" from " + TableName + " where " + condition[0] + " ) A where ROWNUM <=" + CurrentPage * PageSize + ") where RN>" + (CurrentPage - 1) * PageSize);
                else
                    sql.Append(" from " + TableName + ") A where ROWNUM <= " + CurrentPage * PageSize + ") where RN> " + (CurrentPage - 1) * PageSize);
            }
            command = new SQLiteCommand(sql.ToString(), this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            ad.Fill(dt);
            ad.Dispose();
            dt.Columns.RemoveAt(0);
            foreach (DataColumn dc in dt.Columns)
            {
                dc.ColumnName = field[dt.Columns.IndexOf(dc)];
                dc.Caption = caption[dt.Columns.IndexOf(dc)];
            }
            return dt;
        }

        /// <summary>
        /// 通过表名，字段和条件获取DataTable类型的数据
        /// </summary>
        public DataTable getFieldsDatasByCondition(string TableName, string[] Fields, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += AddField(Fields);
            sql += " from " + TableName + " where " + Condition;

            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            return dt;
        }

        /// <summary>
        /// 通过表名，字段获取DataTable类型的数据
        /// </summary>
        public DataTable getFieldsDatas(string TableName, string Fields)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += Fields;
            sql += " from " + TableName;
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            ad.SelectCommand = command;
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            return dt;
        }


        /// <summary>
        /// 获取DbFind的数据的DataTable的结构
        /// </summary>
        /// <param name="field"></param>
        /// <param name="caller"></param>
        /// <returns></returns>
        public DataTable GetDbFindDataTable(string field, string caller)
        {
            string sql = "select * from dbfindsetui where ds_caller='" + caller + "' and ds_whichui='" + field + "'";
            DataTable dt = (DataTable)ExecuteSql(sql, "select");
            if (dt.Rows.Count != 0)
            {
                //通过#号分割字段
                string[] dbfield = dt.Rows[0]["ds_findtoui"].ToString().Split('#');
                string[] cnfield = dt.Rows[0]["ds_dbcaption"].ToString().Split('#');
                //获取查询要查询的Table
                string dbtable = dt.Rows[0]["ds_tables"].ToString();
                //拼接查询的字段
                for (int i = 0; i < dbfield.Length; i++)
                {
                    dbfield[i] = dbfield[i].Split(',')[0];
                }
                //新建一个空的DataTable
                DataTable dt1 = new DataTable();
                //往空的DataTable添加结构,ColumnName是中文，Caption是实际的字段名称
                for (int i = 0; i < cnfield.Length; i++)
                {
                    dt1.Columns.Add(cnfield[i]);
                    dt1.Columns[i].Caption = dbfield[i];
                }
                //返回一个带有结构的空的DataTable
                //DbFind.BindTable1 = dbtable;
                return dt1;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 检测内容是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Condition"></param>
        /// <returns></returns>
        public bool CheckExist(string TableName, string Condition)
        {
            string sql = "select count(1) from " + TableName + " where " + Condition;
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            DataTable dt = new DataTable();
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            return int.Parse(dt.Rows[0][0].ToString()) > 0;
        }

        /// <summary>
        /// 直接执行SQL，同时传入SQL的类型
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public object ExecuteSql(string SQL, string Type)
        {
            object result = null;
            command = new SQLiteCommand(SQL, this._odcConnection);
            Reconnect(command);
            switch (Type.ToUpper())
            {
                case "SELECT":
                    SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
                    result = new DataTable();
                    try
                    {
                        ad.Fill((DataTable)result);
                    }
                    catch (Exception)
                    {
                        this._odcConnection = new SQLiteConnection(this._connectionString);
                        this._odcConnection.Open();
                        command = new SQLiteCommand(SQL, this._odcConnection);
                        ad = new SQLiteDataAdapter();
                        ad.SelectCommand = command;
                        ad.Fill((DataTable)result);
                    }
                    break;
                case "DELETE":
                    try
                    {
                        result = command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        command.Connection = new SQLiteConnection(this._connectionString);
                        command.Connection.Open();
                        result = command.ExecuteNonQuery();
                    }
                    break;
                case "UPDATE":
                    try
                    {
                        result = command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        command.Connection = new SQLiteConnection(this._connectionString);
                        command.Connection.Open();
                        result = command.ExecuteNonQuery();
                    }
                    break;
                case "INSERT":
                    try
                    {
                        result = command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        command.Connection = new SQLiteConnection(this._connectionString);
                        command.Connection.Open();
                        result = command.ExecuteNonQuery();
                    }
                    break;
            }
            return result;
        }

        /// <summary>
        /// 为了同步BS端的条码维护，检测时允许问号的存在，在检测时默认将问号换成:Param参数
        /// </summary>
        /// <param name="SQL"></param>
        public void CheckSQL(string SQL)
        {
            SQL = SQL.Replace("?", ":Param");
            command = new SQLiteCommand(SQL, this._odcConnection);
            command.ExecuteNonQuery();

        }

        public int GetDistinctRowCount(string TableName, string Field)
        {
            DataTable dt = new DataTable();
            string sql = "select distinct count('" + Field + "') from " + TableName;
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter(command);
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();

            return int.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 根据Caller获取流水号
        /// </summary>
        /// <param name="Caller"></param>
        /// <returns></returns>
        public string GetSerialNumberByCaller(string Caller)
        {
            string SerialNumber = getFieldDataByCondition("MaxNumbers", "mn_number", "mn_tablename='" + Caller + "'").ToString();
            UpdateByCondition("MaxNumbers", "mn_number=mn_number+1", "mn_tablename='" + Caller + "'");
            return SerialNumber;
        }

        /// <summary>
        /// 通过序列的名称获取序列
        /// </summary>
        /// <param name="SeqName"></param>
        /// <returns></returns>
        public string GetSEQ(string SeqName)
        {
            DataTable dt = new DataTable();
            dt = (DataTable)ExecuteSql("SELECT " + SeqName + ".NEXTVAL FROM DUAL", "select");
            return dt.Rows[0][0].ToString();
        }

        /// <summary>
        /// 通过序列的名称获取序列
        /// </summary>
        /// <param name="SeqName"></param>
        /// <returns></returns>
        public string[] GetSEQ(string SeqName, int Num)
        {
            DataTable dt = new DataTable();
            dt = (DataTable)ExecuteSql("SELECT " + SeqName + ".nextval  FROM DUAL CONNECT BY LEVEL<=" + Num, "select");
            string[] SerialNum = new string[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SerialNum[i] = dt.Rows[i][0].ToString();
            }
            return SerialNum;
        }

        /// <summary>
        /// 取Configs表中的配置，进行该客户是否执行某个操作
        /// </summary>
        /// <param name="Code"></param>
        /// <param name="Caller"></param>
        /// <returns></returns>
        public object GetConfig(string Code, string Caller)
        {
            DataTable dt = new DataTable();
            string sql = "select Data from configs where code='" + Code + "' and caller='" + Caller + "'";
            dt = (DataTable)ExecuteSql(sql, "select");
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                return dt.Rows[0]["Data"];
            }
        }


        //将数据类型的列类型转换为DataTable
        public DataTable DataTypeColumnToDataTable(DataTable dt)
        {
            DataTable dt1 = new DataTable();
            dt1.Rows.Add();
            foreach (DataRow dr in dt.Rows)
            {
                dt1.Columns.Add(dr[0].ToString());
                int index = dt.Rows.IndexOf(dr);
                if (dr[1].ToString() == "NUMBER")
                {
                    dt1.Rows[0][index] = 0;
                }
                if (dr[1].ToString() == "VARCHAR2")
                {
                    dt1.Rows[0][index] = "这是一段文字";
                }
                if (dr[1].ToString() == "DATE")
                {
                    dt1.Rows[0][index] = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (dr[1].ToString() == "FLOAT")
                {
                    dt1.Rows[0][index] = 1.0;
                }
                if (dr[1].ToString() == "CLOB")
                {
                    dt1.Rows[0][index] = "一段长文字";
                }
            }
            return dt1;
        }


        /// <summary>
        /// 通过条件更新
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="update"></param>
        /// <param name="condition"></param>
        public int UpdateByCondition(string TableName, string update, string condition)
        {
            string sql = "update " + TableName + " set " + update + " where " + condition;
            command = new SQLiteCommand(sql, this._odcConnection);
            int Rowcount = 0;
            Reconnect(command);
            try
            {
                Rowcount = command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                command.Connection = new SQLiteConnection(this._connectionString);
                command.Connection.Open();
                Rowcount = command.ExecuteNonQuery();
            }
            return Rowcount;
        }


        /// <summary>
        /// 出现异常进行回滚的执行方法
        /// </summary>
        /// <param name="SQL"></param>
        public void ExecuteSQLTran(params string[] SQL)
        {
            SQLiteTransaction tx = this._odcConnection.BeginTransaction();
            command = new SQLiteCommand();
            command.Connection = this._odcConnection;
            command.Transaction = tx;
            try
            {
                foreach (string sql in SQL)
                {
                    if (!String.IsNullOrEmpty(sql))
                    {
                        command.CommandText = sql;
                        try
                        {
                            command.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                            command.Connection = new SQLiteConnection(this._connectionString);
                            command.Connection.Open();
                            command.ExecuteNonQuery();
                        }
                    }
                }
                tx.Commit();
            }
            catch (Exception E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }

        /// <summary>
        /// 用于将string 的数组转换成SQL的查询内容
        /// </summary>
        /// <param name="Fields"></param>
        /// <returns></returns>
        private string AddField(string[] Fields)
        {
            string sql = " ";
            foreach (string field in Fields)
            {
                sql += field + ",";
            }
            return sql.Substring(0, sql.Length - 1);
        }
        /// <summary>
        /// 通过查询的内容获取到字段的描述
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        private static string[] GetCaptionFromField(string field)
        {
            string[] caption = field.Split(',');
            for (int i = 0; i < caption.Length; i++)
            {
                caption[i] = caption[i].Substring(0, caption[i].LastIndexOf("as")).Trim();
            }
            return caption;
        }

        /// <summary>
        /// 通过查询的语句获取查询的字段
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        private static string[] GetField(string field)
        {
            string[] fields = field.Split(',');
            for (int i = 0; i < fields.Length; i++)
            {
                fields[i] = fields[i].Substring(fields[i].LastIndexOf("as") + 2, fields[i].Length - fields[i].LastIndexOf("as") - 2).Trim();
            }
            return fields;
        }

        public object GetLabelParam(string sql)
        {
            DataTable dt = new DataTable();
            command = new SQLiteCommand(sql, this._odcConnection);
            Reconnect(command);
            SQLiteDataAdapter ad = new SQLiteDataAdapter();
            ad.SelectCommand = command;
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                this._odcConnection = new SQLiteConnection(this._connectionString);
                this._odcConnection.Open();
                command = new SQLiteCommand(sql, this._odcConnection);
                ad = new SQLiteDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            if (dt.Rows.Count > 0)
            {
                ad.Dispose();

                return dt.Rows[0][0];
            }
            else
            {
                return "";
            }
        }

        public static string[] GetParamFromSQL(string SQL)
        {
            string[] par = SQL.Split(':');
            //用来存参数的数组
            StringBuilder[] addpar = new StringBuilder[par.Length - 1];
            string[] param = new string[par.Length - 1];
            for (int i = 0; i < par.Length - 1; i++)
            {
                //新建一个char类型的数组用来存储每个字节的变量
                char[] c = par[i + 1].ToCharArray();
                addpar[i] = new StringBuilder();

                for (int j = 0; j < c.Length; j++)
                {
                    if (c[j] != ' ' && c[j] != ',' && c[j] != ')')
                    {
                        addpar[i].Append(c[j]);
                    }
                    else
                    {
                        break;
                    }
                }
            }
            for (int i = 0; i < par.Length - 1; i++)
            {
                param[i] = addpar[i].ToString();
            }
            return param;
        }


        public void AddColumFromDataTable(DataTable dt, string tablename)
        {
            DataTable dt2 = (DataTable)ExecuteSql("select * from " + tablename + "  limit 0,1", "select");
            foreach (DataColumn item in dt.Columns)
            {
                if (!dt2.Columns.Contains(item.ToString()))
                {
                    switch (item.DataType.ToString())
                    {
                        case "System.String":
                            ExecuteSql("alter table " + tablename + " add [" + item.ToString() + "] VARCHAR(200) NULL", "update");
                            break;
                        case "System.Decimal":
                            ExecuteSql("alter table " + tablename + " add [" + item.ToString() + "] NUMERIC NULL", "update");
                            break;
                        case "System.DateTime":
                            ExecuteSql("alter table " + tablename + " add [" + item.ToString() + "] DATE NULL", "update");
                            break;
                        default:
                            break;
                    }
                }
            }
        }
    }
}