Sql语句构造类,多字段新增或修改时,拼装sql语句比较方便

using System;
using System.Collections.Generic;
using System.Text;

namespace MSCL
{
    #region 使用示例
    /*
        List<Ftv> ftvlist = new List<Ftv>();
        ftvlist.Add(new Ftv("ErrorDetail", "这是个错误"));
        ftvlist.Add(new Ftv("ErrorFlag", "1"));
        ftvlist.Add(new Ftv("ErrorRemark","这是个错误，我还没有处理"));
        ftvlist.Add(new Ftv("ErrorTime", DateTime.Now.ToString()));
        ftvlist.Add(new Ftv("OprationTime", DateTime.Now.ToString()));
        //新增
        string sql = MSCL.BuilderSql.InsertSql("AFM_SysLog", ftvlist);
        MSCL.SqlHelper.ExecSql(sql);
        //修改
        string sql = MSCL.BuilderSql.UpdateSql("AFM_SysLog", ftvlist, "where ErrorID=166");
        MSCL.SqlHelper.ExecSql(sql);
        //删除
        string sql = MSCL.BuilderSql.DeleteSql("AFM_SysLog", "where ErrorID=166");
        MSCL.SqlHelper.ExecSql(sql);
    */
    #endregion

    #region 数据表字段类
    /// <summary>
    /// 数据表字段类
    /// </summary>
    public class Ftv
    {
        /// <summary>
        /// 字段容器
        /// </summary>
        /// <param name="fieldName">字段名</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="isNum">是否数字字段</param>
        public Ftv(string fieldName, string fieldValue, bool isNum)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
            this.isNum = isNum;
        }

        /// <summary>
        /// 字段容器
        /// </summary>
        /// <param name="fieldName">字段名</param>
        /// <param name="fieldValue">字段值</param>
        public Ftv(string fieldName, string fieldValue)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
        }

        private string fieldName;
        /// <summary>
        /// 字段名
        /// </summary>
        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }

        private bool isNum = false;
        /// <summary>
        /// 是否数字
        /// </summary>
        public bool IsNum
        {
            get { return isNum; }
            set { isNum = value; }
        }

        private string fieldValue;
        /// <summary>
        /// 字段值
        /// </summary>
        public string FieldValue
        {
            get { return fieldValue; }
            set { fieldValue = value; }
        }
    }
    #endregion

    #region SQL语句的构造类
    /// <summary>
    /// SQL语句的构造类
    /// </summary>
    public class BuilderSql
    {

        /// <summary>
        /// 构造新增Insert语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ftvlist">字段list</param>
        /// <returns></returns>
        public static string InsertSql(string tableName, List<Ftv> ftvlist)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" insert into ");
            sb.Append(tableName);
            sb.Append("(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                Ftv ftv = (Ftv)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    sb.Append(ftv.FieldName + ",");
                }
                else
                {
                    sb.Append(ftv.FieldName);
                }
            }
            sb.Append(") values(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                Ftv ftv = (Ftv)ftvlist[i];
                if (ftv.IsNum)
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append(ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(ftv.FieldValue);
                    }
                }
                else
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append("'" + ftv.FieldValue + "',");
                    }
                    else
                    {
                        sb.Append("'" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(")");
            return sb.ToString();
        }


        /// <summary>
        /// 构造更新Update语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ftvlist">字段list</param>
        /// <param name="whereCondition">条件带where</param>
        /// <returns></returns>
        public static string UpdateSql(string tableName, List<Ftv> ftvlist, string whereCondition)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" update ");
            sb.Append(tableName);
            sb.Append(" set");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                Ftv ftv = (Ftv)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',");
                    }
                }
                else
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + "");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(" " + whereCondition);
            return sb.ToString();
        }

        /// <summary>
        /// 构造删除Delete语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereCondition">条件带where</param>
        /// <returns></returns>
        public static string DeleteSql(string tableName, string whereCondition)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" delete from ");
            sb.Append(tableName);
            sb.Append(" " + whereCondition);
            return sb.ToString();
        }

        /// <summary>
        /// 构造Select语句
        /// </summary>
        /// <param name="tableName">表名或视图名</param>
        /// <param name="whereCondition">条件带where</param>
        /// <returns></returns>
        public static string SelectSql(string tableName, string whereCondition)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select * from ");
            sb.Append(tableName);
            sb.Append(" " + whereCondition);
            return sb.ToString();
        }

        /// <summary>
        /// 构造Select语句
        /// </summary>
        /// <param name="tableName">表名或视图名</param>
        /// <param name="fieldString">字段名 例 name,sex,age,(pay1+pay2) as totalpay</param>
        /// <param name="whereCondition">条件带where</param>
        /// <returns></returns>
        public static string SelectSql(string tableName, string fieldString, string whereCondition)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select " + fieldString + " from ");
            sb.Append(tableName);
            sb.Append(" " + whereCondition);
            return sb.ToString();
        }
    }
    #endregion
}