C#使用反射组建动态sql

C#使用反射组建动态sql

        /// <summary>
        /// 使用反射组建动态查询SQL并查询
        /// </summary>
        /// <param name="tableName">查询表名</param>
        /// <param name="param">参数对象</param>
        /// <returns></returns>
        public static DataTable ExecuteReaderByObj(string tableName,object param) {
            DataTable dataTable;
            try
            {
                StringBuilder sb = new StringBuilder($"select * from {tableName} where 1=1 ");
                List<SqlParameter> paramList = new List<SqlParameter>();
                // 使用反射组建动态SQL
                foreach (PropertyInfo p in param.GetType().GetProperties())
                {
                    if (p.GetValue(param) != null && !string.IsNullOrEmpty(p.GetValue(param).ToString()))
                    {
                        // string默认使用模糊查询
                        if (p.PropertyType.Name == typeof(string).Name)
                        {
                            sb.Append($"and {p.Name} like @{p.Name} ");
                            paramList.Add(new SqlParameter(p.Name, $"%{p.GetValue(param)}%"));
                        }
                        else {
                            sb.Append($"and {p.Name}=@{p.Name} ");
                            paramList.Add(new SqlParameter(p.Name, p.GetValue(param)));
                        }
                    }
                }
                var sql = sb.ToString();
                dataTable = ExecuteReader(sql, paramList.ToArray()).Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception($"查询出现异常，原因为：{e.Message}");
            }
            return dataTable;
        }