/// <summary> /// 执行非查询SQL语句 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数集合</param> /// <returns>受影响的记录数</returns> public override int ExecuteNoQuery(string sql, KdtParameterCollection parameters) { try { int effected = 0; // 执行SQL命令 using (OracleCommand cmd = new OracleCommand(ReplaceSqlText(sql, parameters), _oracleCn)) { InitCommand(cmd); // 初始化 // 赋值参数 var hasConvertParams = ConvertToSqlParameter(parameters); foreach (var item in hasConvertParams) { cmd.Parameters.Add(item.Value); } effected = cmd.ExecuteNonQuery(); cmd.Cancel(); cmd.Dispose(); } return(effected); } catch (Exception ex) { KdtLoger.Instance.Error(ex); throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message)); } }
/// <summary> /// 创建插入语句 /// </summary> /// <param name="_table">表字段集合</param> /// <param name="_incrstr">自增字段名称</param> /// <param name="_params">返回参数</param> /// <returns>执行插入SQL语句</returns> protected virtual string CreateAddSql(KdtTableFeildEx _table, string _incrstr, out KdtParameterCollection _params) { StringBuilder sqlText = new StringBuilder(); _params = new KdtParameterCollection(); StringBuilder insertFields, insertValues; if (_table.HasIncr) { string _declare = this.Adapter.Declare(_incrstr, DbDataType.INT); if (!_declare.IsNullOrEmpty()) { sqlText.AppendLine(_declare); } sqlText.AppendLine(this.Adapter.Funcation(_incrstr, _table.IncrFeild.FeildName, _table.TableName, DbFunName.MAX)); sqlText.AppendLine(this.Adapter.Set(_incrstr, "{0} + 1".ToFormat(this.Adapter.ISNULL(_incrstr, "0", true)))); insertFields = new StringBuilder("{0},".ToFormat(_table.IncrFeild.FeildName)); insertValues = new StringBuilder("{0}{1},".ToFormat(this.Adapter.Prefix, _incrstr)); if (_table.HasRelation) { insertFields.AppendFormat("{0},".ToFormat(_table.RelFeild)); insertValues.AppendFormat("{0}{1},".ToFormat(this.Adapter.Prefix, "r0")); } foreach (var item in _table.Fields) { if (item.IsIncr) { continue; } insertFields.AppendFormat("{0},", item.FeildName); insertValues.AppendFormat("{0}{1},", this.Adapter.Prefix, item.SetName); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); } sqlText.AppendLine(this.Adapter.Insert(_table.TableName, insertFields.ToString().TrimEnd(','), insertValues.ToString().TrimEnd(','))); } else { insertFields = new StringBuilder(); insertValues = new StringBuilder(); if (_table.HasRelation) { insertFields.AppendFormat("{0},".ToFormat(_table.RelFeild)); insertValues.AppendFormat("{0}{1},".ToFormat(this.Adapter.Prefix, "r0")); } foreach (var item in _table.Fields) { insertFields.AppendFormat("{0},", item.FeildName); insertValues.AppendFormat("{0}{1},", this.Adapter.Prefix, item.SetName); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); } sqlText.AppendLine(this.Adapter.Insert(_table.TableName, insertFields.ToString().TrimEnd(','), insertValues.ToString().TrimEnd(','))); } return(sqlText.ToString()); }
/// <summary> /// 执行查询,返回查询结果的第一行第一列 /// </summary> /// <typeparam name="T">返回值类型</typeparam> /// <param name="sql">SQL查询语句</param> /// <param name="parameters">参数集合</param> /// <returns>查询结果的第一行第一列</returns> public override T ExecuteScalar <T>(string sql, KdtParameterCollection parameters) { try { T value = default(T); // 执行SQL命令 using (OracleCommand cmd = new OracleCommand(ReplaceSqlText(ReplaceSqlText(sql, parameters), parameters), _oracleCn)) { InitCommand(cmd); // 初始化 // 赋值参数 var hasConvertParams = ConvertToSqlParameter(parameters); foreach (var item in hasConvertParams) { cmd.Parameters.Add(item.Value); } value = cmd.ExecuteScalar().Convert <T>(); cmd.Cancel(); cmd.Dispose(); } return(value); } catch (Exception ex) { KdtLoger.Instance.Error(ex); throw new DataException(string.Format("执行查询,返回查询结果的第一行第一列错误,原因为:{0}", ex.Message)); } }
/// <summary> /// 转换成OracleParameter /// </summary> /// <param name="parameters">参数集合</param> /// <returns>返回转换成OracleParameter集合</returns> private Dictionary <string, OracleParameter> ConvertToSqlParameter(KdtParameterCollection parameters) { Dictionary <string, OracleParameter> result = new Dictionary <string, OracleParameter>(StringComparer.OrdinalIgnoreCase); if (parameters != null && parameters.Count > 0) { foreach (var item in parameters) { OracleParameter param = new OracleParameter("{0}{1}".ToFormat(ParamPrifix, item.Name), item.Value); switch (item._InPutType) { case ProcInPutEnum.OutPut: param.Direction = ParameterDirection.Output; break; case ProcInPutEnum.ReturnValue: param.Direction = ParameterDirection.ReturnValue; break; case ProcInPutEnum.InputOutPut: param.Direction = ParameterDirection.InputOutput; break; default: param.Direction = ParameterDirection.Input; break; } result[item.Name] = param; } } // 返回转换集合 return(result); }
/// <summary> /// 执行非查询SQL语句 /// </summary> /// <param name="sql"></param> /// <param name="mysqlParameters"></param> /// <returns></returns> public override List <string> ExecuteQuery(string sql, KdtParameterCollection parameters) { var strdata = new List <string>(); try { using (SqlCommand cmd = new SqlCommand(ReplaceSqlText(sql, parameters), _sqlCn)) { cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 6000; var r = cmd.ExecuteReader(); while (r.Read()) { var objstr = new List <string>(); for (int i = 0; i < r.FieldCount; i++) { string fildName = r.GetName(i).Trim(); string value = Convert.ToString(r[fildName]); string valueString = string.Empty; var limit = 30000; var num = (value.Length / limit); if (value.Length > 32766) { for (var k = 0; k <= num; k++) { if (k == num) { valueString += Uri.EscapeUriString(value.ToString().Substring(limit * k, value.Length - limit * k)); } else { valueString += Uri.EscapeUriString(value.ToString().Substring(limit * k, limit)); } } } else { valueString = Uri.EscapeUriString(value); } objstr.Add(String.Format("\"{0}\":\"{1}\"", Uri.EscapeUriString(fildName), valueString)); } string str = string.Format("{0}{1}{2}", "{", string.Join(",", objstr), "}"); strdata.Add(str); } cmd.Cancel(); cmd.Dispose(); return(strdata); } } catch (Exception ex) { KdtLoger.Instance.Error(ex); throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message)); } finally { } }
/// <summary> /// 执行数据库SQL语句 /// </summary> /// <param name="_sqltext"></param> public virtual void ExecuteNonQuery(string _sqltext, KdtParameterCollection parameters) { using (var adapter = LoadAdapter()) { adapter.Open(); // 打开执行数据库 adapter.ExecuteNoQuery(_sqltext, parameters); } }
/// <summary> /// 执行查询T-SQL语句 /// </summary> /// <param name="_selecttype">查询类型</param> /// <param name="_params">返回参数集合</param> /// <returns>返回查询T-SQL语句</returns> public virtual string Select(string _selecttype, out KdtParameterCollection _params) { if (this.Adapter == null) { throw new Exception("未启用适配器信息!"); } if (_SelectSql == null || !_SelectSql.ContainsKey(_selecttype)) { throw new Exception("不存在可以查询的表信息"); } _params = new KdtParameterCollection(); var _allfeilds = GetAllKdtFields(); List <KdtFeildEx> _allex = new List <KdtFeildEx>(); foreach (var item in _allfeilds) { _allex.AddRange(item.Fields); } string sqltext = _SelectSql[_selecttype]; foreach (var item in _allex) { if (sqltext.Contains("[{0}]".ToFormat(item.SetName))) { sqltext = sqltext.Replace("[{0}]".ToFormat(item.SetName), item.FeildValue.ToString()); } if (sqltext.Contains("[@{0}]".ToFormat(item.SetName))) { sqltext = sqltext.Replace("[@{0}]".ToFormat(item.SetName), "{0}{1}".ToFormat(Adapter.Prefix, item.SetName)); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); } } Dictionary <string, object> otherfields = GetFeilds(); foreach (var key in otherfields) { if (sqltext.Contains("[{0}]".ToFormat(key.Key))) { sqltext = sqltext.Replace("[{0}]".ToFormat(key.Key), key.Value.ToString()); } if (sqltext.Contains("[@{0}]".ToFormat(key.Key))) { sqltext = sqltext.Replace("[@{0}]".ToFormat(key.Key), "{0}{1}".ToFormat(Adapter.Prefix, key.Key)); _params.AddParameter(key.Key, key.Value, ProcInPutEnum.InPut); } } return(sqltext); }
/// <summary> /// 反射参数值信息 /// </summary> /// <param name="parameters">参数集合</param> /// <param name="oracleparams">转换后的参数集合</param> private void ReflectParamValue(KdtParameterCollection parameters, Dictionary <string, OracleParameter> oracleparams) { if (parameters != null && parameters.Count > 0) { foreach (var item in parameters) { // 非只输入项时,读取返回值 if (item._InPutType != ProcInPutEnum.InPut) { item.Value = oracleparams[item.Name].Value; } } } }
/// <summary> /// 替换SQL字符串信息值 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数集合</param> /// <returns>带参数SQL语句</returns> protected virtual string ReplaceSqlText(string sql, KdtParameterCollection parameters) { if (parameters == null || parameters.Count <= 0) { return(sql); } string[] orderParmas = new string[parameters.Count]; foreach (var item in parameters) { orderParmas[item.Idx] = BindFlag.script.ToFormat("{0}{1}".ToFormat(ParamPrifix, item.Name)); } return(sql.ToFormat(orderParmas)); }
/// <summary> /// 读取KEY VALUE值 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public override KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters) { try { KeyValueCollection entity = new KeyValueCollection(); // 执行SQL命令 using (OracleCommand cmd = new OracleCommand(ReplaceSqlText(sql, parameters), _oracleCn)) { InitCommand(cmd); // 初始化 // 赋值参数 var hasConvertParams = ConvertToSqlParameter(parameters); foreach (var item in hasConvertParams) { cmd.Parameters.Add(item.Value); } // 执行填充数据 using (OracleDataReader reader = cmd.ExecuteReader()) { entity = GetEntity(reader); // 反射参数值 ReflectParamValue(parameters, hasConvertParams); reader.Close(); reader.Dispose(); } cmd.Cancel(); cmd.Dispose(); } return(entity); } catch (OracleException me) { KdtLoger.Instance.Error(me); throw new DataException(me.Message); } catch (Exception ex) { KdtLoger.Instance.Error(ex); throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message)); } }
/// <summary> /// 执行分页查询(采用DataReader进行分页查询) /// </summary> /// <param name="sql">SQL查询语句</param> /// <param name="parameters">参数集合</param> /// <param name="offset">读取偏移量</param> /// <param name="limit">读取最大值</param> /// <returns>包含查询结果集合</returns> public override List <T> ExecuteQueryPage <T>(string sql, KdtParameterCollection parameters, int offset, int limit) { try { List <T> entitys = new List <T>(); // 执行SQL命令 using (SQLiteCommand cmd = new SQLiteCommand(ReplaceSqlText(sql, parameters), _sqliteCn)) { InitCommand(cmd); // 初始化 // 赋值参数 var hasConvertParams = ConvertToSqlParameter(parameters); foreach (var item in hasConvertParams) { cmd.Parameters.Add(item.Value); } // 执行填充数据 using (SQLiteDataReader reader = cmd.ExecuteReader()) { entitys = GetEntityPage <T>(reader, offset, limit); // 反射参数值 ReflectParamValue(parameters, hasConvertParams); reader.Close(); reader.Dispose(); } cmd.Cancel(); cmd.Dispose(); } return(entitys); } catch (SQLiteException me) { KdtLoger.Instance.Error(me); throw new DataException(me.Message); } catch (Exception ex) { KdtLoger.Instance.Error(ex); throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message)); } }
/// <summary> /// 删除数据库操作解析方法 /// </summary> /// <param name="_needTran">是否进行回滚插入</param> /// <param name="_params">参数值集合</param> /// <returns>删除T-SQL语句</returns> public virtual string Delete(out bool _needTran, out KdtParameterCollection _params) { if (this.Adapter == null) { throw new Exception("未启用适配器信息!"); } if (_DeleteWhere == null || _DeleteWhere.Count < 1) { throw new Exception("不存在可以删除的表信息"); } StringBuilder sqlText = new StringBuilder(); _needTran = false; _params = new KdtParameterCollection(); var _allfeilds = GetAllKdtFields(); List <KdtFeildEx> _allex = new List <KdtFeildEx>(); foreach (var item in _allfeilds) { _allex.AddRange(item.Fields); } _needTran = _DeleteWhere.Count > 1; // 组织T-SQL语句 foreach (var table in _DeleteWhere) { string wherestr = table.Value; foreach (var item in _allex) { if (wherestr.Contains("[{0}]".ToFormat(item.SetName))) { wherestr = wherestr.Replace("[{0}]".ToFormat(item.SetName), item.FeildValue.ToString()); } if (wherestr.Contains("[@{0}]".ToFormat(item.SetName))) { wherestr = wherestr.Replace("[@{0}]".ToFormat(item.SetName), "{0}{1}".ToFormat(Adapter.Prefix, item.SetName)); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); } } sqlText.AppendLine(Adapter.Delete(table.Key, wherestr)); } return(sqlText.ToString()); }
/// <summary> /// 插入或更新数据库操作解析方法 /// </summary> /// <param name="_needTran">是否进行回滚插入</param> /// <param name="_params">参数值集合</param> /// <returns>插入T-SQL语句</returns> public virtual string AddOrUpdate(out bool _needTran, out KdtParameterCollection _params) { if (this.Adapter == null) { throw new Exception("未启用适配器信息!"); } StringBuilder sqlText = new StringBuilder(); _needTran = false; _params = new KdtParameterCollection(); KdtParameterCollection tempparams; var _allfeilds = GetAllKdtFields(); List <KdtFeildEx> _allex = new List <KdtFeildEx>(); foreach (var table in _allfeilds) { _allex.AddRange(table.Fields); } if (_allfeilds != null && _allfeilds.Count > 0) { if (_relationFields != null || _allfeilds.Count > 1) { // 判断是否为多表情况 if (_relationFields.Count > 0) { _needTran = true; // 第一步,获取主表信息 var foundtable = _allfeilds.Find(t => t.TableName.Equals(_mainTable, StringComparison.OrdinalIgnoreCase)); if (foundtable == null) { throw new Exception("多级插入主表{0}错误!".ToFormat(_mainTable)); } sqlText.AppendLine(CreateAddOrUpdateSql(foundtable, _allex, "r0", out tempparams)); _params.AddRange(tempparams); _allfeilds.Remove(foundtable); for (int i = 0; i < _allfeilds.Count; i++) { var _table = _allfeilds[i]; if (_relationFields.ContainsKey(_table.TableName)) { _table.HasRelation = true; _table.RelFeild = _relationFields[_table.TableName]; } string _thisincrstr = "r{0}".ToFormat(i + 1); sqlText.AppendLine(CreateAddOrUpdateSql(_table, _allex, _thisincrstr, out tempparams)); _params.AddRange(tempparams); } return(sqlText.ToString()); } else { List <KdtTableFeildEx> myList = new List <KdtTableFeildEx>(_allfeilds); foreach (var item in myList) { if (item.Fields.Count < 2) { _allfeilds.Remove(item); } } if (_allfeilds.Count > 1) { _needTran = true; // 进行无关联的多表插入 for (int i = 0; i < _allfeilds.Count; i++) { var _thistable = _allfeilds[i]; string _thisincrstr = "r{0}".ToFormat(i); sqlText.AppendLine(CreateAddOrUpdateSql(_thistable, _allex, _thisincrstr, out tempparams)); _params.AddRange(tempparams); } return(sqlText.ToString()); } } } // 单表插入 var tempTable = _allfeilds.First(); _needTran = false; sqlText.AppendLine(CreateAddOrUpdateSql(tempTable, _allex, "r0", out tempparams)); _params.AddRange(tempparams); return(sqlText.ToString()); } throw new DataException("没有可以添加的字段列信息"); }
/// <summary> /// 执行非查询SQL语句 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数集合</param> /// <returns>受影响的记录数</returns> public abstract int ExecuteNoQuery(string sql, KdtParameterCollection parameters);
public virtual List <string> ExecuteQuery(string sql, KdtParameterCollection parameters) { return(null); }
/// <summary> /// 执行查询,返回查询结果的第一行第一列 /// </summary> /// <typeparam name="T">返回值类型</typeparam> /// <param name="sql">SQL查询语句</param> /// <param name="parameters">参数集合</param> /// <returns>查询结果的第一行第一列</returns> public abstract T ExecuteScalar <T>(string sql, KdtParameterCollection parameters);
public abstract KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters);
/// <summary> /// 创建插入或更新语句 /// </summary> /// <param name="_table">表字段集合</param> /// <param name="_allex">字段集合</param> /// <param name="_incrstr">自增字段名称</param> /// <param name="_params">返回参数</param> /// <returns>执行插入SQL语句</returns> protected virtual string CreateAddOrUpdateSql(KdtTableFeildEx _table, List <KdtFeildEx> _allex, string _incrstr, out KdtParameterCollection _params) { StringBuilder sqlText = new StringBuilder(); _params = new KdtParameterCollection(); StringBuilder insertFields, insertValues; // 处理WHERE语句 string wherestr = _AddOrUpdateWhere.ContainsKey(_table.TableName) ? _AddOrUpdateWhere[_table.TableName] : ""; List <KdtFeildEx> whereFields = new List <KdtFeildEx>(); foreach (var item in _allex) { if (wherestr.Contains("[{0}]".ToFormat(item.SetName))) { wherestr = wherestr.Replace("[{0}]".ToFormat(item.SetName), item.FeildValue.ToString()); whereFields.Add(item); } if (wherestr.Contains("[@{0}]".ToFormat(item.SetName))) { wherestr = wherestr.Replace("[@{0}]".ToFormat(item.SetName), "{0}{1}".ToFormat(Adapter.Prefix, item.SetName)); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); whereFields.Add(item); } } if (_table.HasIncr && _incrstr == "r0") { string _declare = this.Adapter.Declare(_incrstr, DbDataType.INT); if (!_declare.IsNullOrEmpty()) { sqlText.AppendLine(_declare); } sqlText.AppendLine(this.Adapter.Funcation(_incrstr, _table.IncrFeild.FeildName, _table.TableName, DbFunName.MAX)); } sqlText.AppendLine("if not exists(select 1 from {0} {1})".ToFormat(_table.TableName, wherestr)); sqlText.AppendLine("begin"); if (_table.HasIncr) { if (_incrstr != "r0") { string _declare = this.Adapter.Declare(_incrstr, DbDataType.INT); if (!_declare.IsNullOrEmpty()) { sqlText.AppendLine(_declare); } sqlText.AppendLine(this.Adapter.Funcation(_incrstr, _table.IncrFeild.FeildName, _table.TableName, DbFunName.MAX)); sqlText.AppendLine(this.Adapter.Set(_incrstr, "{0} + 1".ToFormat(this.Adapter.ISNULL(_incrstr, "0", true)))); } else { sqlText.AppendLine(this.Adapter.Set(_incrstr, "{0} + 1".ToFormat(this.Adapter.ISNULL(_incrstr, "0", true)))); } insertFields = new StringBuilder("{0},".ToFormat(_table.IncrFeild.FeildName)); insertValues = new StringBuilder("{0}{1},".ToFormat(this.Adapter.Prefix, _incrstr)); if (_table.HasRelation) { insertFields.AppendFormat("{0},".ToFormat(_table.RelFeild)); insertValues.AppendFormat("{0}{1},".ToFormat(this.Adapter.Prefix, "r0")); } foreach (var item in _table.Fields) { if (item.IsIncr) { continue; } insertFields.AppendFormat("{0},", item.FeildName); insertValues.AppendFormat("{0}{1},", this.Adapter.Prefix, item.SetName); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); } sqlText.AppendLine(this.Adapter.Insert(_table.TableName, insertFields.ToString().TrimEnd(','), insertValues.ToString().TrimEnd(','))); sqlText.AppendLine(this.Adapter.Select(_incrstr, "p{0}".ToFormat(_table.TableName), "", true, true)); } else { insertFields = new StringBuilder(); insertValues = new StringBuilder(); if (_table.HasRelation) { insertFields.AppendFormat("{0},".ToFormat(_table.RelFeild)); insertValues.AppendFormat("{0}{1},".ToFormat(this.Adapter.Prefix, "r0")); } foreach (var item in _table.Fields) { insertFields.AppendFormat("{0},", item.FeildName); insertValues.AppendFormat("{0}{1},", this.Adapter.Prefix, item.SetName); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); } sqlText.AppendLine(this.Adapter.Insert(_table.TableName, insertFields.ToString().TrimEnd(','), insertValues.ToString().TrimEnd(','))); } sqlText.AppendLine("end else begin"); sqlText.AppendFormat("update {0} set", _table.TableName); foreach (var item in whereFields) { _table.Fields.Remove(item); } foreach (var field in _table.Fields) { if (field.HasValue) { sqlText.AppendFormat(" {0}={1}{2},", field.FeildName, this.Adapter.Prefix, field.SetName); _params.AddParameter(field.SetName, field.FeildValue, ProcInPutEnum.InPut); } } if (sqlText.ToString().Contains("=")) { sqlText = sqlText.Replace(",", " ", sqlText.Length - 1, 1); sqlText.AppendLine(wherestr); } sqlText.AppendLine("end"); return(sqlText.ToString()); }
/// <summary> /// 执行SQL查询,返回数据集合 /// </summary> /// <param name="sql">SQL查询语句</param> /// <param name="parameters">参数集合</param> /// <returns>包含查询结果的集合</returns> public abstract List <T> ExecuteQuery <T>(string sql, KdtParameterCollection parameters) where T : class, new();
public abstract List <KeyValueCollection> ExecuteQueryKVCollection(string sql, KdtParameterCollection parameters);
/// <summary> /// 插入数据库操作解析方法 /// </summary> /// <param name="_needTran">是否进行回滚插入</param> /// <param name="_params">参数值集合</param> /// <returns>插入T-SQL语句</returns> public virtual string Update(out bool _needTran, out KdtParameterCollection _params) { if (this.Adapter == null) { throw new Exception("未启用适配器信息!"); } if (_UpdateWhere == null || _UpdateWhere.Count < 1) { throw new Exception("不存在可以更新的表"); } StringBuilder sqlText = new StringBuilder(); _needTran = false; _params = new KdtParameterCollection(); var _allfeilds = new List <KdtTableFeildEx>(); List <KdtTableFeildEx> temp = new List <KdtTableFeildEx>(GetAllKdtFields()); List <KdtFeildEx> _allex = new List <KdtFeildEx>(); foreach (var item in _UpdateWhere) // 清理非存在的更新表信息 { foreach (var table in temp) { if (table.TableName.Equals(item.Key, StringComparison.OrdinalIgnoreCase)) { _allex.AddRange(table.Fields); _allfeilds.Add(table); break; } } } // 组织T-SQL语句 _needTran = _allfeilds.Count > 1; foreach (var table in _allfeilds) { // 处理WHERE语句 string wherestr = _UpdateWhere[table.TableName]; foreach (var item in _allex) { if (wherestr.Contains("[{0}]".ToFormat(item.SetName))) { wherestr = wherestr.Replace("[{0}]".ToFormat(item.SetName), item.FeildValue.ToString()); table.Fields.Remove(item); } if (wherestr.Contains("[@{0}]".ToFormat(item.SetName))) { wherestr = wherestr.Replace("[@{0}]".ToFormat(item.SetName), "{0}{1}".ToFormat(Adapter.Prefix, item.SetName)); _params.AddParameter(item.SetName, item.FeildValue, ProcInPutEnum.InPut); table.Fields.Remove(item); } } sqlText.AppendFormat("update {0} set", table.TableName); foreach (var field in table.Fields) { if (field.HasValue) { sqlText.AppendFormat(" {0}={1}{2},", field.FeildName, this.Adapter.Prefix, field.SetName); _params.AddParameter(field.SetName, field.FeildValue, ProcInPutEnum.InPut); } } if (sqlText.ToString().Contains("=")) { sqlText = sqlText.Replace(",", " ", sqlText.Length - 1, 1); sqlText.AppendLine(wherestr); } } return(sqlText.ToString()); }
/// <summary> /// 插入数据库操作解析方法 /// </summary> /// <param name="_needTran">是否进行回滚插入</param> /// <param name="_isIncr">自定义参数</param> /// <param name="_params">参数值集合</param> /// <returns>插入T-SQL语句</returns> public virtual string Add(out bool _needTran, out bool _isIncr, out KdtParameterCollection _params) { if (this.Adapter == null) { throw new Exception("未启用适配器信息!"); } StringBuilder sqlText = new StringBuilder(); _needTran = false; _isIncr = false; _params = new KdtParameterCollection(); KdtParameterCollection tempparams; var _allfeilds = GetAllKdtFields(); if (_allfeilds != null && _allfeilds.Count > 0) { if (_relationFields != null || _allfeilds.Count > 1) { // 判断是否为多表情况 if (_relationFields.Count > 0) { _needTran = true; _isIncr = false; // 进行有关联的多表插入 StringBuilder _returnedsel = new StringBuilder("select"); // 第一步,获取主表信息 var foundtable = _allfeilds.Find(t => t.TableName.Equals(_mainTable, StringComparison.OrdinalIgnoreCase)); if (foundtable == null) { throw new Exception("多级插入主表{0}错误!".ToFormat(_mainTable)); } sqlText.AppendLine(CreateAddSql(foundtable, "r0", out tempparams)); _params.AddRange(tempparams); if (foundtable.HasIncr) { _returnedsel.AppendFormat(" {0}{1} as p{2},", this.Adapter.Prefix, "r0", foundtable.TableName); } _allfeilds.Remove(foundtable); for (int i = 0; i < _allfeilds.Count; i++) { var _table = _allfeilds[i]; if (_relationFields.ContainsKey(_table.TableName)) { _table.HasRelation = true; _table.RelFeild = _relationFields[_table.TableName]; } string _thisincrstr = "r{0}".ToFormat(i + 1); sqlText.AppendLine(CreateAddSql(_table, _thisincrstr, out tempparams)); _params.AddRange(tempparams); if (_table.HasIncr) { _returnedsel.AppendFormat(" {0}{1} as p{2},", this.Adapter.Prefix, _thisincrstr, _table.TableName); } } if (_returnedsel.Length > 6) { _isIncr = true; sqlText.Append(_returnedsel.ToString().TrimEnd(',')); } return(sqlText.ToString()); } else { List <KdtTableFeildEx> myList = new List <KdtTableFeildEx>(_allfeilds); foreach (var item in myList) { if (item.Fields.Count < 2) { _allfeilds.Remove(item); } } if (_allfeilds.Count > 1) { _needTran = true; _isIncr = false; // 进行无关联的多表插入 StringBuilder _returnedsel = new StringBuilder("select"); for (int i = 0; i < _allfeilds.Count; i++) { var _thistable = _allfeilds[i]; string _thisincrstr = "r{0}".ToFormat(i); sqlText.AppendLine(CreateAddSql(_thistable, _thisincrstr, out tempparams)); _params.AddRange(tempparams); if (_thistable.HasIncr) { _returnedsel.AppendFormat(" {0}{1} as p{2},", this.Adapter.Prefix, _thisincrstr, _thistable.TableName); } } if (_returnedsel.Length > 6) { _isIncr = true; sqlText.Append(_returnedsel.ToString().TrimEnd(',')); } return(sqlText.ToString()); } } } // 单表插入 var tempTable = _allfeilds.First(); _needTran = false; _isIncr = tempTable.HasIncr; sqlText.Append(CreateAddSql(tempTable, "r0", out tempparams)); _params.AddRange(tempparams); if (_isIncr) { sqlText.AppendFormat(this.Adapter.Select("r0", "p{0}".ToFormat(tempTable.TableName), "", true, true)); } return(sqlText.ToString()); } throw new DataException("没有可以添加的字段列信息"); }
public abstract List <KeyValueCollection> ExecuteQueryPageKVCollection(string sql, KdtParameterCollection parameters, int offset, int limit);
/// <summary> /// 执行分页查询(采用DataReader进行分页查询) /// </summary> /// <param name="sql">SQL查询语句</param> /// <param name="parameters">参数集合</param> /// <param name="offset">读取偏移量</param> /// <param name="limit">读取最大值</param> /// <returns>包含查询结果集合</returns> public abstract List <T> ExecuteQueryPage <T>(string sql, KdtParameterCollection parameters, int offset, int limit) where T : class, new();