话不多说直接上代码，新手上路，高手路过勿喷，请多多指教。
/// <summary>
/// 等于号
/// </summary>
private readonly static string eq = string.Format(string.Empty + Convert.ToChar() + Convert.ToChar() + Convert.ToChar()); /// <summary>
/// 条件变量
/// </summary>
private readonly static string where = string.Format(string.Empty + Convert.ToChar() + "WHERE 1" + eq + "" + Convert.ToChar()); /// <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() + Convert.ToChar());
sqlValue.AppendFormat(Convert.ToChar() + "VALUES" + Convert.ToChar());
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());
sqlValue.AppendFormat(string.Empty + Convert.ToChar());
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() + type.Name + Convert.ToChar() + "SET" + Convert.ToChar());
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 - );
sqlstr.Clear();
sqlstr.AppendFormat(sql);
sqlstr.AppendFormat(where + "AND" + Convert.ToChar() + 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[] = string.Empty;
foreach (var item in array)
tmpstr += item + Convert.ToChar();
}
else
tmpstr = condition; Type type = t.GetType();
PropertyInfo[] props = type.GetProperties();
sqlstr.AppendFormat("UPDATE" + Convert.ToChar() + type.Name + Convert.ToChar() + "SET" + Convert.ToChar());
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 - );
sqlstr.Clear();
sqlstr.AppendFormat(sql);
sqlstr.AppendFormat(where + "AND" + Convert.ToChar() + 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) > - || str.Contains(item))
{
itemstr = item;
result = false;
break;
}
} }
}
return result;
}
}