/// <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>
/// 获取所有KdtFields
/// </summary>
/// <returns></returns>
private List <KdtTableFeildEx> GetAllKdtFields()
{
var _list = GetKdtFeilds();
if (_list != null)
{
List <KdtTableFeildEx> _ALL = new List <KdtTableFeildEx>();
foreach (var item in _list)
{
item.Value.SetName = item.Key;
var found = _ALL.Find(t => t.TableName.Equals(item.Value.TableName, StringComparison.OrdinalIgnoreCase));
if (found == null)
{
found = new KdtTableFeildEx()
{
TableName = item.Value.TableName
};
_ALL.Add(found);
}
if (item.Value.IsIncr)
{
found.Fields.Add(item.Value);
found.HasIncr = true;
found.IncrFeild = item.Value;
}
else if (item.Value.HasValue)
{
found.Fields.Add(item.Value);
}
else
{
// Nothing to do.
}
}
IncrVal.Clear();
foreach (var item in _ALL)
{
IncrVal.Add(item.TableName, 0);
}
return(_ALL);
}
return(null);
}
/// <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());
}