拼接sql语句

     很早以前的一个方法，对于一些比较简单的小项目经常使用，自己在这里有进行了下完善和修改，感觉这个方法不错，不用写那些烦人的Insert和Update语句，尤其是字段特别多的时候，写起来很费时，可以省写很多代码，发出来大家看看，有什么不好的地方请各位大虾们指教。
     假如我们要对表Users表进行新增和编辑
     首先定义一个实体User实体类:

/**/ /// <summary>
    /// 实体类Users
    /// </summary>
     public   class  Users
     {
        public Users()
        { }

        private int _id;
        private string _username;
        private int _usercode;
        private string _sex;
        private int _age;

        /**//// <summary>
        /// 系统ID，默认自增
        /// </summary>
        public int ID
        {
            set { _id = value; }
            get { return _id; }
        }
        /**//// <summary>
        /// 用户名
        /// </summary>
        public string UserName
        {
            set { _username = value; }
            get { return _username; }
        }
        /**//// <summary>
        /// 用户编号
        /// </summary>
        public int UserCode
        {
            set { _usercode = value; }
            get { return _usercode; }
        }
        /**//// <summary>
        /// 性别
        /// </summary>
        public string Sex
        {
            set { _sex = value; }
            get { return _sex; }
        }
        /**//// <summary>
        /// 年龄
        /// </summary>
        public int Age
        {
            set { _age = value; }
            get { return _age; }
        }
    }

    存储sql语句字段名、字段值和字段类型的类：

public   class  SqlText
     {
        private string _ColumnName;
        private string _ColumnValue;
        private string _ColumnType;

        public static string strType = "String";
        public static string intType = "Int";


        public SqlText(string ColumnName, string ColumnValue, string ColumnType)
        {
            this._ColumnName = ColumnName;
            this._ColumnValue = ColumnValue;
            this._ColumnType = ColumnType;
        }

        public string ColumnName
        {
            get
            {
                return _ColumnName;
            }
        }

        public string ColumnValue
        {
            get
            {
                return _ColumnValue;
            }
        }

        public string ColumnType
        {
            get
            {
                return _ColumnType;
            }
        }
    }

    生成sql语句的类：

public   class  CreateSql
     {
        /**//// <summary>
        /// 插入sql语句
        /// </summary>
        /// <param name="list">存放数据对象</param>
        /// <param name="TableName">插入表名称</param>
        public static void Insert(List<SqlText> list, string TableName)
        {
            string sql = CreateInsert(list, TableName);
        }
        
        /**//// <summary>
        /// 修改sql语句
        /// </summary>
        /// <param name="list">存放数据对象</param>
        /// <param name="TableName">插入表名称</param>
        /// <param name="strWhere">输入条件，例如：ID=1</param>
        public static void Update(List<SqlText> list, string TableName, string strWhere)
        {
            string sql = CreateUpdate(list, TableName, strWhere);
        }

        public static string CreateInsert(List<SqlText> list, string TableName)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbStart = new StringBuilder();
            StringBuilder sbEnd = new StringBuilder();
            sbStart.Append("INSERT INTO " + TableName + " (");
            sbEnd.Append(" VALUES (");
            for (int i = 0; i < list.Count; i++)
            {
                if (i == 0)
                {
                    sbStart.Append(list[i].ColumnName);
                    if (list[i].ColumnType == SqlText.strType)
                    {
                        sbEnd.Append("'" + list[i].ColumnValue + "'");
                    }
                    else
                    {
                        sbEnd.Append(list[i].ColumnValue);
                    }
                }
                else
                {
                    sbStart.Append("," + list[i].ColumnName);
                    if (list[i].ColumnType == SqlText.strType)
                    {
                        sbEnd.Append(",'" + list[i].ColumnValue + "'");
                    }
                    else
                    {
                        sbEnd.Append("," + list[i].ColumnValue);
                    }
                }
            }
            sbStart.Append(")");
            sbEnd.Append(")");

            sb.Append(sbStart.ToString() + sbEnd.ToString());
            return sb.ToString();
        }

        public static string CreateUpdate(List<SqlText> list, string TableName, string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE " + TableName + " SET ");
            for (int i = 0; i < list.Count; i++)
            {
                if (i == 0)
                {
                    sb.Append(list[i].ColumnName + " = ");
                }
                else
                {
                    sb.Append("," + list[i].ColumnName + " = ");
                }
                if (list[i].ColumnType == SqlText.strType)
                {
                    sb.Append("'" + list[i].ColumnValue + "'");
                }
                else
                {
                    sb.Append(list[i].ColumnValue);
                }
            }
            sb.Append(" WHERE " + strWhere);
            return sb.ToString();
        }
    }

    具体实现方法：

class  Program
     {
        static void Main(string[] args)
        {
            //如果是新增一位人员信息
            string type = "ADD";
            //如果是编辑一位人员信息
            //string type = "ADD";

            Users user = new Users();
            user.UserName = "OR";
            user.UserCode = 1;
            user.Sex = "男";
            user.Age = 24;

            List<SqlText> list = new List<SqlText>();
            list.Add(new SqlText("UserName", user.UserName.ToString(), SqlText.strType));
            list.Add(new SqlText("UserCode", user.UserCode.ToString(), SqlText.intType));
            list.Add(new SqlText("Sex", user.Sex.ToString(), SqlText.strType));
            list.Add(new SqlText("Age", user.Age.ToString(), SqlText.intType));
            try
            {
                if (type == "ADD")   //新增
                {
                    CreateSql.Insert(list, "Users");
                    list.Clear();
                }
                else if (type == "EDIT")  //修改
                {
                    CreateSql.Update(list, "Users", "ID=" + type);
                    list.Clear();
                }
            }
            catch
            {
                throw;
            }
        }
    }

转载于:https://www.cnblogs.com/lc329857895/archive/2008/03/06/1093624.html