ADO.NET 根据实体类自动生成添加修改语句仅限Oracle使用
话不多说直接上代码，新手上路，高手路过勿喷，请多多指教。
/// <summary> /// 等于号 /// </summary> private readonly static string eq = string.Format(string.Empty + Convert.ToChar(32) + Convert.ToChar(61) + Convert.ToChar(32)); /// <summary> /// 条件变量 /// </summary> private readonly static string where = string.Format(string.Empty + Convert.ToChar(32) + "WHERE 1" + eq + "1" + Convert.ToChar(32)); /// <summary> /// 异常信息 /// </summary> private readonly static string errInfo = string.Format("传入的key不存在"); /// <summary> /// 录入信息存在风险 /// </summary> private readonly static string injection = string.Format("录入信息存在风险");
/// <summary> /// 添加Sql语句函数 fg /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="key">主键名称，必须是实体对象中存在</param> /// <param name="value">主键值(序列)</param> /// <param name="outmsg"></param> /// <returns>fg: 添加根据实体类接收的数据进行拼接sql语句</returns> public static string Added<T>(T t, string key, string value, out string outmsg) { bool blo = false; outmsg = string.Empty; string sql = string.Empty, error = string.Empty; StringBuilder sqlField = new StringBuilder(); StringBuilder sqlValue = new StringBuilder(); try { if (t == null) return sql; if (string.IsNullOrEmpty(key) || string.IsNullOrEmpty(value)) throw new Exception("对象实例化失败，请检查主键是否正确"); Type type = t.GetType(); sqlField.AppendFormat("INSERT INTO " + type.Name + Convert.ToChar(32) + Convert.ToChar(40)); sqlValue.AppendFormat(Convert.ToChar(32) + "VALUES" + Convert.ToChar(40)); sqlField.AppendFormat(key); sqlValue.AppendFormat(value); PropertyInfo[] props = type.GetProperties(); blo = props.Any(o => o.Name.ToUpper() != key); if (!blo) throw new Exception(errInfo); Parallel.ForEach(props, p => { if (p.Name.Equals(key)) return; if (p.GetValue(t, null) == null) return; switch (p.PropertyType.Name) { case "String": var tmp = (string)p.GetValue(t, null); blo = VerificationHelper.VerificationByStr(tmp); if (!blo) error = string.Format(injection); sqlField.AppendFormat(", " + p.Name); sqlValue.AppendFormat(", '" + (string.IsNullOrEmpty(tmp) ? tmp : tmp.Contains("'") ? tmp.Replace("'", "''") : tmp.Trim()) + "'"); break; case "DateTime": sqlField.AppendFormat(", " + p.Name); sqlValue.AppendFormat(", TO_DATE('" + p.GetValue(t, null) + "','YYYY-MM-DD HH24:MI:SS')"); break; default: sqlField.AppendFormat(", " + p.Name); sqlValue.AppendFormat(", '" + p.GetValue(t, null) + "'"); break; } }); if (!string.IsNullOrEmpty(error)) throw new Exception(error); sqlField.AppendFormat(string.Empty + Convert.ToChar(41)); sqlValue.AppendFormat(string.Empty + Convert.ToChar(41)); sql = sqlField + sqlValue.ToString(); return sql; } catch (Exception ex) { outmsg = ex.Message; } return sql; } /// <summary> /// 修改 sql 方法函数 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="key">主键，必须是实体对象中存在的字段</param> /// <param name="value">主键值</param> /// <param name="outmsg"></param> /// <returns>fg: 修改函数封装根据接收的实体对象生成sql修改语句</returns> public static string Edited<T>(T t, string key, string value, out string outmsg) { bool blo = false; outmsg = string.Empty; string sql = string.Empty, error = string.Empty, fieldVars = string.Empty; StringBuilder sqlstr = new StringBuilder(); StringBuilder newSql = new StringBuilder(); try { if (t == null) return sql; if (string.IsNullOrEmpty(key) || string.IsNullOrEmpty(value)) throw new Exception("对象实例化失败，请检查主键是否正确"); key = key.ToUpper(); Type type = t.GetType(); PropertyInfo[] props = type.GetProperties(); sqlstr.AppendFormat("UPDATE" + Convert.ToChar(32) + type.Name + Convert.ToChar(32) + "SET" + Convert.ToChar(32)); blo = props.Any(o => o.Name.ToUpper() != key); if (!blo) throw new Exception(errInfo); Parallel.ForEach(props, p => { fieldVars = p.Name.ToUpper(); if (fieldVars.Equals(key)) return; if (p.GetValue(t, null) == null) return; switch (p.PropertyType.Name) { case "String": var tmp = (string)p.GetValue(t, null); tmp = tmp.ToUpper(); blo = VerificationHelper.VerificationByStr(tmp); if (!blo) error = string.Format(injection); sqlstr.AppendFormat(fieldVars + eq + "'" + (string.IsNullOrEmpty(tmp) ? tmp : tmp.Contains("'") ? tmp.Replace("'", "''") : tmp.Trim()) + "', "); break; case "DateTime": sqlstr.AppendFormat(fieldVars + eq + "TO_DATE('" + p.GetValue(t, null) + "','YYYY-MM-DD HH24:MI:SS'), "); break; default: sqlstr.AppendFormat(fieldVars + eq + "'" + p.GetValue(t, null) + "', "); break; } }); if (!string.IsNullOrEmpty(error)) throw new Exception(error); sql = sqlstr.ToString().Remove(sqlstr.Length - 2); sqlstr.Clear(); sqlstr.AppendFormat(sql); sqlstr.AppendFormat(where + "AND" + Convert.ToChar(32) + key + eq + value); sql = sqlstr.ToString(); return sql; } catch (Exception ex) { outmsg = ex.Message; } return sql; } /// <summary> /// 根据条件修改 sql 方法函数 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="condition">修改条件，直接写条件即可</param> /// <param name="outmsg"></param> /// <returns>fg: 修改函数封装根据接收的实体对象生成sql修改语句</returns> public static string Editeds<T>(T t, string key, string condition, out string outmsg) { bool blo = false; outmsg = string.Empty; string sql = string.Empty, error = string.Empty, tmpstr = string.Empty; StringBuilder sqlstr = new StringBuilder(); StringBuilder newSql = new StringBuilder(); try { if (t == null) return sql; if (string.IsNullOrEmpty(condition)) throw new Exception("请先完善条件后，再尝试"); else condition = condition.Trim(); string[] array = condition.Split(' '); string str = array.FirstOrDefault(); str = str.ToUpper(); if (str.Equals("OR")) throw new Exception("拼接条件部分开始不能使用\"OR\"关键字"); if (str.Equals("AND")) { array[0] = string.Empty; foreach (var item in array) tmpstr += item + Convert.ToChar(32); } else tmpstr = condition; Type type = t.GetType(); PropertyInfo[] props = type.GetProperties(); sqlstr.AppendFormat("UPDATE" + Convert.ToChar(32) + type.Name + Convert.ToChar(32) + "SET" + Convert.ToChar(32)); blo = props.Any(o => o.Name.ToUpper() != key); if (!blo) throw new Exception(errInfo); Parallel.ForEach(props, p => { if (p.Name.Equals(key)) return; if (p.GetValue(t, null) == null) return; switch (p.PropertyType.Name) { case "String": var tmp = (string)p.GetValue(t, null); blo = VerificationHelper.VerificationByStr(tmp); if (!blo) error = string.Format(injection); sqlstr.AppendFormat(p.Name + eq + "'" + (string.IsNullOrEmpty(tmp) ? tmp : tmp.Contains("'") ? tmp.Replace("'", "''") : tmp.Trim()) + "', "); break; case "DateTime": sqlstr.AppendFormat(p.Name + eq + "TO_DATE('" + p.GetValue(t, null) + "','YYYY-MM-DD HH24:MI:SS'), "); break; default: sqlstr.AppendFormat(p.Name + eq + "'" + p.GetValue(t, null) + "', "); break; } }); if (!string.IsNullOrEmpty(error)) throw new Exception(error); sql = sqlstr.ToString().Remove(sqlstr.Length - 2); sqlstr.Clear(); sqlstr.AppendFormat(sql); sqlstr.AppendFormat(where + "AND" + Convert.ToChar(32) + tmpstr); sql = sqlstr.ToString(); return sql; } catch (Exception ex) { outmsg = ex.Message; } return sql; }
/// <summary> /// 验证帮助类 /// </summary> public class VerificationHelper { private static string sqlinjectStr = "=;--;delete ;drop ;alert ;insert ;and ;or ;"; /// <summary> /// 验证字符串是否存在sql注入 /// </summary> /// <param name="str">验证字符串</param> /// <returns>不存在则验证通过，返回true,否则返回false</returns> public static bool VerificationByStr(string str) { string[] strarr = sqlinjectStr.Split(';'); bool result = true; string itemstr = string.Empty; if (string.IsNullOrEmpty(str)) { return true; } else { str = str.ToLower().Trim(); foreach (string item in strarr) { if (!string.IsNullOrEmpty(item)) { if (str.IndexOf(item) > -1 || str.Contains(item)) { itemstr = item; result = false; break; } } } } return result; } }
