OTS/OTSIncAMeasureApp/ServiceCenter/DTLBase/CSQLiteTable.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OTSModelSharp.DTLBase
{
    public class CSQLiteTable: IDBTableBase
    {

        //表结构
        private TableModel table;
     
        /// <summary>
        /// 初始换
        /// </summary>
        /// <param name="conStr">数据库地址</param>
        /// <param name="_table">表结构</param>
        public  CSQLiteTable()
        {
          
           
                table = new TableModel();            
            
           
        }
        public CSQLiteTable(string tableName)
        {


            table = new TableModel();
            table.TableName = tableName;

        }
        public CSQLiteTable(TableModel t)
        {
            table = t;

        }
        public TableModel GetTableInfo()
        {
            return table;
        }
        public void AddColumn(ColumnDefine col)
        {
            table.columns.Add(col);
        }
        public int GetColumnCount()
        {
            return table.columns.Count;
        }

        public string GetTableName()
        {
            return table.TableName;
        }
        public void SetTableName(string a_sTableName)
        {
            table.TableName = a_sTableName;
        }

        public string GetColumnName(int a_nColId)
        {
            return table.columns[a_nColId].ColumName;
        }
        public string GetColumnFullName(int a_nColId)
        {
            return table.TableName+"."+ table.columns[a_nColId].ColumName;
        }
        public string GetColumnNames(bool a_bWithPrimary = true)
        {
            StringBuilder builder = new StringBuilder();
            int ind = 0;
            foreach (var item in table.columns)
            {
                ++ind;
                if (!a_bWithPrimary&&item.IsPrimarykey)
                {
                    continue;
                }
                builder.Append(item.ColumName);
                if (ind == table.columns.Count)
                {
                    builder.Append("");
                }
                else
                {
                    builder.Append(",");
                }
            }
            return builder.ToString();
          
        }
        public string GetColumnFullNames(bool a_bWithPrimary = true)
        {
            StringBuilder builder = new StringBuilder();
            int ind = 0;
            foreach (var item in table.columns)
            {
                ++ind;
                if (!a_bWithPrimary && item.IsPrimarykey)
                {
                    continue;
                }
                builder.Append(table.TableName + "." + item.ColumName);
                if (ind == table.columns.Count)
                {
                    builder.Append("");
                }
                else
                {
                    builder.Append(",");
                }
            }
            return builder.ToString();

        }

        public ColumnType GetColumnType(int a_nColId)
        {
            return table.columns[a_nColId].ColumType;
        }

        public string GetCreateTableCommandString()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("CREATE TABLE IF NOT EXISTS " + table.TableName + "(");
            int ind = 0;
            foreach (var item in table.columns)
            {
                builder.Append(item.ColumName + " " + item.ColumType.GetName() + " " + (item.IsPrimarykey ? "PRIMARY KEY" : ""));
                if (++ind == table.columns.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            return builder.ToString();
        }
        public string GetDeleteTableCommandString()
        {
            string queryString = "drop table " + table.TableName;
            return queryString;
        }
        public string GetRemoveAllRowsCommandString()
        {
            string queryString = "DELETE FROM  " + table.TableName;
            return queryString;
        }

        public string GetInsertCommandFormatString(bool a_bWithPrimary = false)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("INSERT INTO " + table.TableName + "(");
            int ind = 0;
            foreach (var item in table.columns)
            {
                ++ind;
                if (!a_bWithPrimary && item.IsPrimarykey)
                {
                    continue;
                }
                builder.Append(item.ColumName);
                if (ind == table.columns.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            ind = 0;
            builder.Append(" VALUES (");
            foreach (var item in table.columns)
            {
                ++ind;
                if (!a_bWithPrimary && item.IsPrimarykey)
                {
                    continue;
                }
                builder.Append(item.ColumType.GetFormat(ind-1));
               
                if (ind == table.columns.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            return builder.ToString();

        }
        public KeyValuePair<string, SQLiteParameter[]> GetInsertCommand(bool a_bWithPrimary = false)
        {
            StringBuilder builder = new StringBuilder();
            List<SQLiteParameter> paras = new List<SQLiteParameter>();
            builder.Append("INSERT INTO " + table.TableName + "(");
            int ind = 0;
            foreach (var item in table.columns)
            {
                ++ind;
                if (!a_bWithPrimary && item.IsPrimarykey)
                {
                    continue;
                }
                builder.Append(item.ColumName);
                if (ind == table.columns.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            ind = 0;
            builder.Append(" VALUES (");
            foreach (var item in table.columns)
            {
                ++ind;
                paras.Add(new SQLiteParameter(item.ColumName));
                if (!a_bWithPrimary && item.IsPrimarykey)
                {
                    continue;
                }
               
                builder.Append("@" + item.ColumName);
                if (ind == table.columns.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            return new KeyValuePair<string, SQLiteParameter[]>( builder.ToString(),paras.ToArray());

        }
        public string GetInsertCommandFormatString(List<int> a_colIndexes)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("INSERT INTO " + table.TableName + "(");
            int ind = 0;
            foreach (var item in a_colIndexes)
            {
                builder.Append(table.columns[item].ColumName);
                if (++ind == a_colIndexes.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            ind = 0;
            builder.Append(" VALUES ");
            foreach (var item in a_colIndexes)
            {
                builder.Append(table.columns[item].ColumType.GetFormat(ind));
                if (++ind == a_colIndexes.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
            return builder.ToString();

        }
        public string GetUpdateCommandFormatString(List<int> a_updateColIndexes, int a_nConditionColIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("UPDATE " + table.TableName + " SET ");
            int ind = 0;
            foreach (var item in a_updateColIndexes)
            {
                builder.Append(table.columns[item].ColumName+"="+ table.columns[(int)item].ColumType.GetFormat(ind));
                if (++ind == a_updateColIndexes.Count)
                {
                    builder.Append(" WHERE "+ table.columns[a_nConditionColIndex].ColumName+"="+  table.columns[a_nConditionColIndex].ColumType.GetFormat(ind));
                }
                else
                {
                    builder.Append(",");
                }
            }
            return builder.ToString();
        }

        public KeyValuePair<string, SQLiteParameter[]> GetUpdateCommand(List<int> a_updateColIndexes, int a_nConditionColIndex)
        {
            throw new NotImplementedException();
        }
    }
}