根据实体类生成SQL语句（增删改）
根据实体类生成SQL语句（增删改）
代码：
Skip to content Product Team Enterprise Explore Marketplace Pricing Search Sign in Sign up MaChuhao / MCHDAL Public Code Issues Pull requests Actions Projects Wiki Security Insights MCHDAL/CreateSQLStr.cs / @MaChuhao MaChuhao 第一次版本提交，该模块是在.NET环境下自动生成SQL语句，并封装了增删查改的方法。 Latest commit 33c3783 on 6 Mar 2017 History 1 contributor 259 lines (224 sloc) 9.07 KB using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Reflection; using System.Data.SqlClient; namespace MCHDAL { public class CreateSQLStr { /// <summary> /// 通用实体类存储新数据到数据库的方法 /// 调用此方法可获得SQL Insert语句 /// </summary> /// <typeparam name=T>模板T</typeparam> /// <param name=model>实体对象</param> /// <param name=tablename>表名</param> /// <returns></returns> public static string SaveSQLStr<T>(T model, string tablename) { //获得此模型的类型 Type type = typeof(T); string fieldsName = INSERT INTO + tablename+ (; string fieldsValue = VALUES(; PropertyInfo[] propertys = model.GetType().GetProperties(); //遍历该对象的所有属性 foreach (PropertyInfo pi in propertys) { string name = pi.Name; object value = pi.GetValue(model, null); if(value != null) { if(pi.PropertyType.Name.Equals(Int32)) { if(Int32.Parse(value.ToString()) != 0) { fieldsName = fieldsName + pi.Name + ','; fieldsValue = fieldsValue + Int32.Parse(value.ToString()) + ','; } } else if (pi.PropertyType.Name.Equals(DateTime)) { if (value.Equals(0001/1/1 0:00:000) || value.Equals(0001/1/1 0:00:00) || (DateTime)value == DateTime.MinValue) { continue; } else { fieldsName = fieldsName + pi.Name + ','; fieldsValue = fieldsValue + ' + value.ToString() + '\'' + ','; } } else if (pi.PropertyType.Name.Equals(String)|| pi.PropertyType.Name.Equals(Nullable`1)) { fieldsName = fieldsName + pi.Name + ','; fieldsValue = fieldsValue + ' + value.ToString() + '\'' + ','; } } } fieldsName = fieldsName.Substring(0, fieldsName.Length - 1) + ')' + ' '; //确保该语句返回值为主键ID fieldsValue = fieldsValue.Substring(0, fieldsValue.Length - 1) + ')' + SELECT @@IDENTITY; return fieldsName + fieldsValue; } /// <summary> /// 通用实体类更新数据库表数据的方法 /// 调用此方法可获得SQL Update语句 /// </summary> /// <typeparam name=T>模板T</typeparam> /// <param name=model>实体对象</param> /// <param name=tablename>表名</param> /// <returns></returns> public static string UpdateSQLStr<T>(T model, string tablename) { bool flag = false; //获得此模型的类型 Type type = typeof(T); string fields = Update + tablename + set ; string where = where ; PropertyInfo[] propertys = model.GetType().GetProperties(); //遍历该对象的所有属性 foreach (PropertyInfo pi in propertys) { string name = pi.Name; object value = pi.GetValue(model, null); if (value != null) { if (pi.PropertyType.Name.Equals(Int32)) { if (Int32.Parse(value.ToString()) != 0) { if (name.Equals(ID)) { where = where + pi.Name + = + Int32.Parse(value.ToString()); flag = true; } else { fields = fields + pi.Name + '=' + Int32.Parse(value.ToString()) + ','; } } } else if (pi.PropertyType.Name.Equals(DateTime)) { if (value.Equals(0001/1/1 0:00:000) || value.Equals(0001/1/1 0:00:00) || (DateTime)value == DateTime.MinValue) { continue; } else { fields = fields + pi.Name + '=' + ' + value.ToString() + '\'' + ','; } } else if (pi.PropertyType.Name.Equals(String)) { fields = fields + pi.Name + '=' + ' + value.ToString() + '\'' + ','; } } } fields = fields.Substring(0, fields.Length - 1) + ' '; if (flag) { return fields + where; } else { return false; } } /// <summary> /// 通用实体类删除数据库表数据的方法 /// 调用此方法可获得SQL Delete语句 /// </summary> /// <typeparam name=T>模板T</typeparam> /// <param name=model>实体对象</param> /// <param name=tablename>表名</param> /// <returns></returns> public static string DeleteSQLStr<T>(T model, string tablename) { bool flag = false; //获得此模型的类型 Type type = typeof(T); string fields = Delete from + tablename; string where = where ; PropertyInfo[] propertys = model.GetType().GetProperties(); //遍历该对象的所有属性 foreach (PropertyInfo pi in propertys) { string name = pi.Name; object value = pi.GetValue(model, null); if (name.Equals(ID) && Int32.Parse(value.ToString()) != 0) { where = where + pi.Name + = + Int32.Parse(value.ToString()); flag = true; } } if (flag) { return fields + where; } else { return false; } } /// <summary> /// 通用实体类查询数据库表数据的方法 /// 调用此方法可获得SQL Select语句 /// </summary> /// <typeparam name=T>模板T</typeparam> /// <param name=model>实体对象</param> /// <param name=tablename>表名</param> /// <returns></returns> public static string SelectSQLStr<T>(T model, string tableName) { string where = where ; bool flag = false; PropertyInfo[] propertys = model.GetType().GetProperties(); foreach(PropertyInfo pi in propertys) { string name = pi.Name; object value = pi.GetValue(model, null); if (value != null) { if (pi.PropertyType.Name.Equals(Int32)) { if (Int32.Parse(value.ToString()) != 0) { where = where + pi.Name + = + Int32.Parse(value.ToString()) + and ; flag = true; } }else if (pi.PropertyType.Name.Equals(DateTime)) { //datetime转换时报错，因此以0001/1/1 0:00:000标识时间为空 if (value.Equals(0001/1/1 0:00:000)||value.Equals(0001/1/1 0:00:00)||(DateTime)value == DateTime.MinValue) { flag = true; } else { where = where + pi.Name + '=' + ' + value.ToString() + '\'' + and ; flag = true; } } else if (pi.PropertyType.Name.Equals(String)) { where = where + pi.Name + '=' + ' + value.ToString() + '\'' + and ; flag = true; } } } where = where.Substring(0, where.Length - 4); if (flag) { return SELECT * FROM + tableName + where; } else { return SELECT * FROM + tableName; } } } } © 2022 GitHub, Inc. Terms Privacy Security Status Docs Contact GitHub Pricing API Training Blog About Loading complete View Code
详细见 Git