/// <summary>
/// DB资料非查询
/// 自动识别sql中的参数，并从传入的参数集中找对应的参数，如果没有，则需补充自定义的参数定义
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connstr"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
protected virtual void NonQuery <T>(string connstr, string sql, params KeyValuePair <string, object>[] param) where T : ADBAccess, IResourceEntity
{
T dao = GetDao <T>(connstr, null);
try
{
DBOParameterCollection dbp = new DBOParameterCollection();
Dictionary <string, object> _dp = _p.Domain(DomainKey.INPUT_PARAMETER);
foreach (var v in param)
{
if (!_dp.ContainsKey(v.Key))
{
_dp.Add(v.Key, v.Value);
}
}
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
regstr = @"(?<=" + dao.ParameterFlagChar + @")[A-Za-z0-9_]+\d*";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql;
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (_dp[m.ToString()] is byte[])
{
dbp.Add(m.ToString(), _dp[m.ToString()], System.Data.DbType.Binary);
}
else
{
dbp.Add(m.ToString(), _dp[m.ToString()]);
}
}
}
UnitDataCollection rtn = new UnitDataCollection();
dao.ExecuteNoQuery(sql, dbp);
}
finally
{
dao.Close();
}
}
public DataCollection DoOperate(ParameterStd p)
{
UnitDataCollection rtn = new UnitDataCollection();
string flag = p.GetValue <string>("_unit_action_flag_");
UnitParameter up = (UnitParameter)p;
if (up.Dao is ADBAccess)
{
T t = (T)Activator.CreateInstance(typeof(T), true);
var sqlobj = t.GetSqlFunc(flag)(up);
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string sql = sqlobj.sql;
ADBAccess dba = (ADBAccess)up.Dao;
DBOParameterCollection dbc = new DBOParameterCollection();
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
regstr = @"(?<=" + (dba.ParameterFlagChar == "$"?"\\$": dba.ParameterFlagChar) + @")[A-Za-z0-9_]+\d*";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else if (up.GetValue(m.ToString()) is DateTime)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.DateTime);
}
else if (up.GetValue(m.ToString()) is int)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Int32);
}
else if (up.GetValue(m.ToString()) is double)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Double);
}
else
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
}
try
{
rtn.QueryDatas = dba.Query(sql, dbc);
}catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QuerySql={sql};\nParameters={dp.ToJSONString()}");
throw ex;
}
if (rtn.QueryDatas != null && rtn.QueryDatas.Tables.Count > 0)
{
rtn.QueryTable = rtn.QueryDatas[0];
}
}
return(rtn);
}
/// <summary>
/// DB资料查询
/// 自动识别sql中的参数，并从传入的参数集中找对应的参数，如果没有，则需补充自定义的参数定义
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connstr"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
protected virtual UnitDataCollection Query <T>(string connstr, string sql, params KeyValuePair <string, object>[] param) where T : ADBAccess, IResourceEntity
{
T dao = GetDao <T>(connstr, null);//_rm.CreateInstance<T>(_token);
try
{
//dao.Open(connstr);
DBOParameterCollection dbp = new DBOParameterCollection();
Dictionary <string, object> _dp = _p.Domain(DomainKey.INPUT_PARAMETER);
foreach (var v in param)
{
if (!_dp.ContainsKey(v.Key))
{
_dp.Add(v.Key, v.Value);
}
}
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
if (dao is OracleAccess)
{
regstr = @"(?<=:)[a-zA-Z0-9_]*\d*";
}
else
{
regstr = @"(?<=@)[A-Za-z0-9_]+\d*";
}
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql;
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (_dp[m.ToString()] is byte[])
{
dbp.Add(m.ToString(), _dp[m.ToString()], System.Data.DbType.Binary);
}
else
{
dbp.Add(m.ToString(), _dp[m.ToString()]);
}
}
}
UnitDataCollection rtn = new UnitDataCollection();
rtn.QueryDatas = dao.Query(sql, dbp);
if (rtn.QueryDatas != null)
{
if (rtn.QueryDatas.Tables.Count > 0)
{
rtn.QueryTable = rtn.QueryDatas[0];
}
}
return(rtn);
}
finally
{
dao.Close();
}
}
public DataCollection DoOperate(ParameterStd p)
{
UnitDataCollection rtn = new UnitDataCollection();
string flag = p.GetValue <string>("_unit_action_flag_");
UnitParameter up = (UnitParameter)p;
if (up.Dao is ADBAccess)
{
T t = (T)Activator.CreateInstance(typeof(T), true);
var sqlobj = t.GetSqlFunc(flag)(up);
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string sql = sqlobj.sql;
ADBAccess dba = (ADBAccess)up.Dao;
DBOParameterCollection dbc = new DBOParameterCollection();
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
if (dba is OracleAccess)
{
regstr = @"(?<=:)[a-zA-Z0-9_]*\d*";
}
else
{
regstr = @"(?<=@)[A-Za-z0-9_]+\d*";
}
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else if (up.GetValue(m.ToString()) is DateTime)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.DateTime);
}
else if (up.GetValue(m.ToString()) is int)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Int32);
}
else if (up.GetValue(m.ToString()) is double)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Double);
}
else
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
}
rtn.QueryDatas = dba.Query(sql, dbc);
if (rtn.QueryDatas != null && rtn.QueryDatas.Tables.Count > 0)
{
rtn.QueryTable = rtn.QueryDatas[0];
}
}
return(rtn);
}
