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