格式化查询字符串

 

 

    static string sqlFormat;
    static SQLiteHelper helper = new SQLiteHelper();
    static string tName;
      /// <summary>
      /// 批处理。。sql
      /// </summary>
       static List<string> sqls = new List<string>();
        public static string AppPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
        /// <summary>
        /// 初始化格式字符串
        /// </summary>
        /// <param name="mode">操作类型</param>
        /// <param name="types">值 true 为字符串,false 为整型 为null 表示Select</param>
        /// <param name="tableName">表名</param>
        /// <param name="fields">表字段 null表示所有Select</param>
        public static void IntFormat(OperationMode mode, bool[] types, string tableName, params string[] fields)
        {
            string[] tmp;
            tName = tableName;
            switch (mode)
            {
                case OperationMode.Insert:
                    sqlFormat = "insert into " + tableName + "(";
                    sqlFormat += string.Join(",", fields) + ") values(";

                    tmp = new string[types.Length];

                    for (int i = 0; i < types.Length; i++)
                    {
                        if (types[i])
                            tmp[i] = "'{" + i + "}'";
                        else
                            tmp[i] = "{" + i + "}";
                    }
                    sqlFormat += string.Join(",", tmp) + ")";
                    break;
                case OperationMode.Update:

                    sqlFormat = "update " + tableName + " set ";
                    tmp = new string[types.Length];

                    for (int i = 0; i < types.Length; i++)
                    {
                        if (types[i])
                            tmp[i] = fields[i].ToString() + "='{" + i + "}'";
                        else
                            tmp[i] = fields[i].ToString() + "={" + i + "}";
                    }

                    sqlFormat += string.Join(",", tmp) + " where 主键={" + types.Length + "}";
                    break;
                case OperationMode.Select:
                    if (fields == null)
                        sqlFormat = "select * from " + tableName;
                    else
                        sqlFormat = "select " + string.Join(",", fields) + " from " + tableName;
                    break;
                default:
                    sqlFormat = "delete from " + tableName + " ";
                    break;

            }
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="fieldContents"></param>
        /// <returns></returns>
        public static bool Insert(params object[] fieldContents)
        {
            return helper.DoExcute(string.Format(sqlFormat, fieldContents));
        }
     
   

      /// <summary>
      /// 返回自增列
      /// </summary>
      /// <returns></returns>
        private static object GetIdentity()
        {
           return helper.GetScalar("select LAST_INSERT_ROWID()");
        }
      /// <summary>
      /// 批处理SQL字符串集合
      /// </summary>
      /// <param name="fieldContents"></param>
        public static void BatAddSQL(params object[] fieldContents)
        {
            sqls.Add(string.Format(sqlFormat, fieldContents));
        }
      /// <summary>
      /// 清空sql字符集合
      /// </summary>
        public static void ClearBatSQL()
        {
            sqls.Clear();
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="fieldContents"></param>
        /// <returns></returns>
        public static bool Update(params object[] fieldContentAndKey)
        {
          
            return helper.DoExcute(string.Format(sqlFormat, fieldContentAndKey));
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="pkid">主键</param>
        /// <returns></returns>
        public static bool Delete(string pkid)
        {
         
            sqlFormat += "where 主键 in ({0})";
            return helper.DoExcute(string.Format(sqlFormat, pkid));
        }
      /// <summary>
      /// 删除多条记录
      /// </summary>
      /// <param name="pkids"></param>
      /// <returns></returns>
        public static bool Delete(params object[] pkids)
        {
            return true;
        }
          /// <summary>
          /// 批处理
          /// </summary>
          /// <returns></returns>
        public static bool DoExcuteBat()
        {
            helper.OpenConnect();
            OpenTransaction();
            foreach (string item in sqls)
            if (!helper.DoExcuteBat(item))
            {
                TransactionRollback();
                return false;
            }
            TransactionCommit();
            helper.CloseConnect();
            return true;
        }
        /// <summary>
        /// 开始打开事务
        /// </summary>
        private static void OpenTransaction()
        {
            helper.OpenTransaction();
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        private static void TransactionCommit()
        {
            helper.TransactionCommit();
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public static void TransactionRollback()
        {
            helper.TransactionRollback();
        }

        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="whereStr">条件字符串 不带where</param>
        /// <returns></returns>
        public static bool DeleteWhere(string whereStr)
        {
            return helper.DoExcute(sqlFormat + " where " + whereStr);
        }
        /// <summary>
        /// 删除所有记录
        /// </summary>
        /// <returns></returns>
        public static bool Delete()
        {
            return helper.DoExcute(sqlFormat);
        }

        /// <summary>
        /// 查询表不带条件
        /// </summary>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public static DataTable Select()
        {
            return helper.Getable(sqlFormat, tName);
        }

        /// <summary>
        /// 带条件查询
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="WhereStr">条件字符串 不带where</param>
        /// <returns></returns>
        public static DataTable Select(string WhereStr)
        {
         
            return helper.Getable(sqlFormat + " where " + WhereStr, tName);
        }
        /// <summary>
        /// 查询表sql语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns></returns>
        public static DataTable Select(string tableName,string sqlStr)
        {
           return helper.Getable(sqlStr, tableName);
        }
        /// <summary>
        /// 返回单项数据
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static object GetScalar(string cmd)
        {
            return helper.GetScalar(cmd);

        }

        /// <summary>
        /// 加序号
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DataTable TableToIndex(DataTable dt)
        {
            if (!dt.Columns.Contains("序号"))
                dt.Columns.Add("序号");
            dt.Columns["序号"].SetOrdinal(0);
            for (int i = 0; i < dt.Rows.Count; i++)
                dt.Rows[i][0] = i + 1;
            return dt;

        }
        /// <summary>
        /// 获取主键列表
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string PKIDS(DataTable dt)
        {
            string pkids = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i == dt.Rows.Count - 1)
                    pkids += dt.Rows[i][0].ToString();
                else
                    pkids += dt.Rows[i][0].ToString() + ",";
            }
            return pkids;
        }
        /// <summary>
        /// 获取日期
        /// </summary>
        /// <param name="dtime"></param>
        /// <returns></returns>
        public static string GetDateTimeFormat(DateTime dtime)
        {
            string hr = "";
            if (dtime.Hour >= 12)
                hr = "下午";
            else
                hr = "上午";
            string month = "";
            if (dtime.Month.ToString().Length == 1)
                month = "0" + dtime.Month.ToString();

            return dtime.Year + "-" + month + "-" + dtime.Day + ":" + hr;
        }
    }
    /// <summary>
    /// 操作类型
    /// </summary>
    public enum OperationMode
    {
        Insert,
        Update,
        Delete,
        Select
    }