/// <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 (DB2Command cmd = new DB2Command(ReplaceSqlText(sql, parameters), _db2Cn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
// 执行填充数据
using (DB2DataReader reader = cmd.ExecuteReader())
{
entity = GetEntity(reader);
// 反射参数值
ReflectParamValue(parameters, hasConvertParams);
reader.Close();
reader.Dispose();
}
cmd.Cancel();
cmd.Dispose();
}
return(entity);
}
catch (DB2Exception 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));
}
}
public string Read(string jsonQuery)
{
string selectSQL = this._sqlParser.ReadSqlParser(jsonQuery);
string objName = this._sqlParser.ObjName;
bool isReadToList = this._sqlParser.IsReadToList;
string jsonDataSet = string.Empty;
if (!string.IsNullOrEmpty(selectSQL))
{
DB2Command dbCmd = new DB2Command(selectSQL, this._connection);
this.OpenConnection();
DB2DataReader dbReader = dbCmd.ExecuteReader();
while (dbReader.Read())
{
// add LIST seperator jsonDataSet
if (isReadToList && !string.IsNullOrEmpty(jsonDataSet))
{
jsonDataSet += ",";
}
int fieldCount = dbReader.FieldCount;
// open json row
jsonDataSet += "{";
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++)
{
// GET NAME
string fieldName = dbReader.GetName(fieldIndex);
string JOIN_PREFIX = "JOIN_";
bool isJoinField = false;
if (fieldName.ToUpper().StartsWith("JOIN_"))
{
isJoinField = true;
fieldName = fieldName.Substring(fieldName.IndexOf(JOIN_PREFIX) + JOIN_PREFIX.Length);
}
fieldName = $"\"{fieldName}\"";
// GET VALUE
string fieldValue = dbReader.IsDBNull(fieldIndex) ? "\"\"" : dbReader.GetString(fieldIndex);
if (!isJoinField)
{
fieldValue = $"\"{fieldValue}\"";
}
jsonDataSet += $"{fieldName}:{fieldValue}";
if (fieldIndex < fieldCount - 1)
{
jsonDataSet += ",";
}
}
// close json row
jsonDataSet += "}";
}
//close
dbReader.Close();
dbReader.Dispose();
this.CloseConnection();
}
return(StringHelper.Simpler(string.IsNullOrEmpty(jsonDataSet) ? null : ("{" + $"\"{objName}\"" + ":" + ((isReadToList) ? "[" + jsonDataSet + "]" : jsonDataSet) + "}"), Pattern.PATTERN_SPECIAL_CHARS));
}
