/// <summary>
/// 提取SQL参数
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="manual"></param>
/// <returns></returns>
public virtual string ReplaceParameter(CoreHelper.DBHelper db, string sql, bool manual = false)
{
if (!SettingConfig.ReplaceSqlParameter && !manual)
{
return(sql);
}
//return sql;
var re = @"((\s|,)*)(\w+)\s*(>|<|=|!=|>=|<=)\s*('(.*?)'|([1-9]\d*.\d*|0.\d*[1-9]\d*))(\s|,|\))";
sql = sql + " ";
if (!Regex.IsMatch(sql, re, RegexOptions.IgnoreCase))
{
return(sql);
}
Regex r = new Regex(re, RegexOptions.IgnoreCase);
List <string> pars = new List <string>();
//int index = 1;
for (var m = r.Match(sql); m.Success; m = m.NextMatch())
{
var name = m.Groups[3];
var op = m.Groups[4];
var value1 = m.Groups[6];
var value2 = m.Groups[7];
var value = string.IsNullOrEmpty(value2.Value) ? value1 : value2;
var p = m.Groups[1];
var p2 = m.Groups[8];
var pName = GetParamName("_p", parIndex);
db.AddParam(pName, value.ToString());
sql = sql.Replace(m.ToString(), string.Format("{0}{1}{4}{2}{3} ", p, name, pName, p2, op));
parIndex += 1;
}
return(sql);
}
/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <param name="helper"></param>
/// <returns></returns>
public override int InsertObject(IModel obj, CoreHelper.DBHelper helper)
{
Type type = obj.GetType();
string table = TypeCache.GetTableName(type);
var typeArry = TypeCache.GetProperties(type, true).Values;
string sql = string.Format("insert into [{0}](", table);
string sql1 = "";
string sql2 = "";
foreach (Attribute.FieldAttribute info in typeArry)
{
string name = info.Name;
if (info.IsPrimaryKey)
{
continue;
}
if (!string.IsNullOrEmpty(info.VirtualField))
{
continue;
}
object value = info.GetValue(obj);
value = ObjectConvert.SetNullValue(value, info.PropertyType);
sql1 += string.Format("{0},", info.KeyWordName);
sql2 += string.Format("@{0},", name);
helper.AddParam(name, value);
}
sql1 = sql1.Substring(0, sql1.Length - 1);
sql2 = sql2.Substring(0, sql2.Length - 1);
sql += sql1 + ") values( " + sql2 + ") ; SELECT scope_identity() ;";
sql = SqlFormat(sql);
return(Convert.ToInt32(helper.ExecScalar(sql)));
}
/// <summary>
/// page
/// </summary>
/// <param name="query"></param>
/// <param name="fields"></param>
/// <param name="sort"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
internal virtual CallBackDataReader GetPageData(string query, string fields, string sort, int pageSize, int pageIndex)
{
helper.AddParam("query_", query);
helper.AddParam("fields_", fields);
helper.AddParam("sort_", sort);
helper.AddParam("pageSize_", pageSize);
helper.AddParam("pageIndex_", pageIndex);
helper.AddOutParam("count_", 1);
//var reader = helper.RunDataReader("sp_page");
var reader = new CallBackDataReader(helper.RunDataReader("sp_page"), () =>
{
return(Convert.ToInt32(helper.GetOutParam("count_")));
});
return(reader);
}
/// <summary>
/// 获取插入语法
/// </summary>
/// <param name="obj"></param>
/// <param name="helper"></param>
/// <returns></returns>
public override int InsertObject(IModel obj, CoreHelper.DBHelper helper)
{
Type type = obj.GetType();
string table = TypeCache.GetTableName(type);
var typeArry = TypeCache.GetProperties(type, true).Values;
string sql = string.Format("insert into {0}(", table);
string sql1 = "";
string sql2 = "";
string sequenceName = string.Format("{0}_sequence", table);
var sqlGetIndex = string.Format("select {0}.nextval from dual", sequenceName);//oracle不能同时执行多条语句
int id = Convert.ToInt32(helper.ExecScalar(sqlGetIndex));
foreach (Attribute.FieldAttribute info in typeArry)
{
string name = info.Name;
if (info.IsPrimaryKey)
{
//continue;//手动插入ID
}
if (!string.IsNullOrEmpty(info.VirtualField))
{
continue;
}
object value = info.GetValue(obj);
value = ObjectConvert.SetNullValue(value, info.PropertyType);
sql1 += string.Format("{0},", info.KeyWordName);
sql2 += string.Format("@{0},", info.KeyWordName);
helper.AddParam(info.KeyWordName, value);
}
sql1 = sql1.Substring(0, sql1.Length - 1);
sql2 = sql2.Substring(0, sql2.Length - 1);
sql += sql1 + ") values( " + sql2 + ")";
sql = SqlFormat(sql);
var primaryKey = TypeCache.GetTable(obj.GetType()).PrimaryKey;
helper.SetParam(primaryKey.Name, id);
helper.Execute(sql);
//var helper2 = helper as CoreHelper.OracleHelper;
//int id = helper2.Insert(sql,sequenceName);
return(id);
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public void AddParam(string name, object value)
{
value = ObjectConvert.SetNullValue(value);
helper.AddParam(name, value);
}