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