ADO.NET 根据实体类自动生成添加修改语句仅限Oracle使用

╰半夏微凉° 2021-12-03 17:11 147阅读 0赞

话不多说直接上代码，新手上路，高手路过勿喷，请多多指教。

/// <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;
            }        
        }

原创不易，作者地址：[https://www.cnblogs.com/FGang/p/11179086.html][https_www.cnblogs.com_FGang_p_11179086.html]

转载于:https://www.cnblogs.com/FGang/p/11179086.html

[https_www.cnblogs.com_FGang_p_11179086.html]: https://www.cnblogs.com/FGang/p/11179086.html