根据实体自动生成sql语句并且执行

这是封装的代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
namespace Tool
{
    public class Tool
    {
        /// <summary>
        /// 生成查询sql，并且执行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetList(model.Query Query)
        {
            Type type = Query.GetType();
            string tabname = type.ToString().Split('.')[type.ToString().Split('.').Length - 1];
            PropertyInfo[] propers = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
          
            foreach (PropertyInfo p in propers)
            {
                sb.Append(" ," + p.Name);
            }
            sb.Append(" from "+tabname);
            string strsql = sb.Remove(8,1).ToString();
            return DbHelperSQL.Query(strsql.ToString());
           
        }
      
    
          /// <summary>
        /// 生成生成删除sql,并且执行
        /// </summary>
        /// <param name="ADD"></param>
        /// <returns></returns>
        public static int  Delsql(model.Query   model)
        {
            Type type = model.GetType();
            string tabname = type.ToString().Split('.')[type.ToString().Split('.').Length - 1];
            PropertyInfo[] propers = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from");
            sb.Append(" "+tabname+" from ");
            foreach (PropertyInfo pi in propers)
            {
                if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(decimal))
                {
                    int i = (int)pi.GetValue(model, null);
                    if (i != 0)
                    {
                        sb.Append(" " + pi.Name + "=" + pi.GetValue(model, null));
                    }
                }

            }
           
            return DbHelperSQL.ExecuteSql(sb.ToString());
       
        }

         /// <summary>
        /// 生成添加sql语句，并且执行
        /// </summary>
        /// <param name="Query"></param>
        /// <returns></returns>
        public static int AddSQl(model.Query  model)
        {
            Type type = model.GetType();
            string tabname = type.ToString().Split('.')[type.ToString().Split('.').Length - 1];
            PropertyInfo[] propers = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into");
            sb.Append(" "+tabname+" (");
            foreach (PropertyInfo p in propers)
            {
                sb.Append(" ," + p.Name);
            }
            sb.Append(") values (");
            int strat=sb.Length;
            foreach (PropertyInfo p in propers)
            {
                sb.Append(" '" + p.GetValue(model, null) + "',");
            }
            sb.Remove(tabname.Length+15,1);
            sb.Remove(strat-1,1);
            sb.Append(")");
            return DbHelperSQL.ExecuteSql(sb.ToString());
        }
        //更新
        /// <summary>
        /// 更新sql，并且得执行
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int EditeSql(model.Query model)
        {
            Type type = model.GetType();
            string tabname = type.ToString().Split('.')[type.ToString().Split('.').Length - 1];
            PropertyInfo[] propers = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            sb.Append("update "+tabname+" set ");
            string str="";
            foreach (PropertyInfo p in propers)
            {
                str +=", "+p.Name + "='" + p.GetValue(model, null) + "' ";

            }
            str = str.Substring(1, str.Length - 1);
            sb.Append(str);
            sb.Append(" where ");
            foreach (PropertyInfo pi in propers)
            {
                if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(decimal))
                {
                    int i = (int)pi.GetValue(model, null);
                    if (i != 0)
                    {
                        sb.Append(" " + pi.Name + "=" + pi.GetValue(model, null));
                    }
                }

            }
            return DbHelperSQL.ExecuteSql(sb.ToString());
          
        }
        /// <summary>
        /// 根据某个id，获得详细信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static DataSet GetListByID(model.Query model)
        {
            Type type = model.GetType();
            string tabname = type.ToString().Split('.')[type.ToString().Split('.').Length - 1];
            PropertyInfo[] propers = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            string str = "";
            foreach (PropertyInfo p in propers)
            {
                str += ", " + p.Name;

            }
            str = str.Substring(1, str.Length - 1);
            sb.Append(str);
            sb.Append(" from "+tabname);
            sb.Append(" where ");
            foreach (PropertyInfo pi in propers)
            {
                if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(decimal))
                {
                    int i = (int)pi.GetValue(model, null);
                    if (i != 0)
                    {
                        sb.Append(" " + pi.Name + "=" + pi.GetValue(model, null));
                    }
                }

            }

            return DbHelperSQL.Query(sb.ToString());
        }

 

       
    }
}

这是实体

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

namespace model
{

    public class Query
    {
   
        public int id { get; set; }
        public int classID { get; set; }
        public string name { get; set; }
        public string address { get; set; }

    }
}

前台调用Tool里的方法，根据实体自动生成sql语句然并且的处理得到常用的数据集，这个做的还不完美里边还有很多的不足仅做一个参考，下次在发布完成代码
        
      

 

这是自己测试的一个可以正常的运行

 

交流群76650857