/// <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>
/// 执行查询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="_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="_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>
/// 插入数据库操作解析方法
/// </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());
}