public virtual void DeleteFromOracle(UnitParameter p, string tablename, FrameDLRObject obj)
{
StringBuilder sbsql = new StringBuilder();
sbsql.Append("delete from " + tablename + " where 1=1 ");
var values = "";
var columns = "";
DBOParameterCollection dpc = new DBOParameterCollection();
foreach (var s in obj.Keys)
{
var pkey = s.Replace("(", "").Replace(")", "").Replace("'", "").Replace("\"", "").Replace("[", "").Replace("]", "").Replace(".", "");
dpc.SetValue(pkey, obj.GetValue(s));
sbsql.Append(string.Format(" and {0}=:{0}", pkey));
}
if (p.Dao == null)
{
p.Dao = _logic.CallContext_ResourceManage.CreateInstance <OracleAccess>(p.CurrentTransToken);
p.Dao.Open(ComFunc.nvl(_logic.Configs[ParameterKey.DBCONNECT_STRING]));
}
//目前只支持关系型数据库
if (p.Dao is ADBAccess)
{
((ADBAccess)p.Dao).ExecuteNoQuery(sbsql.ToString(), dpc);
}
}
public virtual void InsertIntoOracle(UnitParameter p, string tablename, FrameDLRObject obj)
{
StringBuilder sbsql = new StringBuilder();
sbsql.Append("insert into " + tablename + "(");
var values = "";
var columns = "";
DBOParameterCollection dpc = new DBOParameterCollection();
foreach (var s in obj.Keys)
{
var pkey = s.Replace("(", "").Replace(")", "").Replace("'", "").Replace("\"", "").Replace("[", "").Replace("]", "").Replace(".", "");
dpc.SetValue(pkey, obj.GetValue(s));
columns += columns.Length > 0 ? "," + s : s;
values += values.Length > 0 ? ",:" + pkey : ":" + pkey;
}
sbsql.Append(columns + ")values(");
sbsql.Append(values);
sbsql.Append(");");
if (p.Dao == null)
{
p.Dao = _logic.CallContext_ResourceManage.CreateInstance <OracleAccess>(p.CurrentTransToken);
p.Dao.Open(ComFunc.nvl(_logic.Configs[ParameterKey.DBCONNECT_STRING]));
}
//目前只支持关系型数据库
if (p.Dao is ADBAccess)
{
((ADBAccess)p.Dao).ExecuteNoQuery(sbsql.ToString(), dpc);
}
}
public static void Test()
{
//var d = FrameDLRObject.CreateInstance(ComFunc.Base64DeCode(@"PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiIHN0YW5kYWxvbmU9InllcyI/Pgo8bG9naW5SZXNwb25zZSBzZXNzaW9uSWQ9ImYyMGQ5OTA4LTJlMjQtNGUxYS04OWQxLWY0MzkyMjJjYzhhOCIvPgoKMjAxOC0wMy0xMiAxNToyODoxMCw4MzMgWzE0XTrmsZ/oi4/np7vliqjov5Tlm57nmoR4bWw6PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiIHN0YW5kYWxvbmU9InllcyI/Pgo8cmVxdWVzdD4KICAgIDxoZWFkZXI+CiAgICAgICAgPHJlc3BvbnNldGltZT4yMDE4MDMxMjE1Mjg1MzwvcmVzcG9uc2V0aW1lPgogICAgICAgIDxzdXBwbGllcl9udW0+OTkxMDAwMTU8L3N1cHBsaWVyX251bT4KICAgIDwvaGVhZGVyPgogICAgPGJvZHk+CiAgICAgICAgPHJlc3VsdD4wPC9yZXN1bHQ+CiAgICAgICAgPHJlc3VsdHJlbWFyaz7miafooYzmiJDlip88L3Jlc3VsdHJlbWFyaz4KICAgICAgICA8b3JkZXJfbnVtYmVyPjQ8L29yZGVyX251bWJlcj4KICAgICAgICA8b3JkZXJfbGlzdHM+CiAgICAgICAgICAgIDxvcmRlcj4KICAgICAgICAgICAgICAgIDxvcmRlcl9ubz5CMjAxODAzMTIxNTIyNDk3MjA2MDc8L29yZGVyX25vPgogICAgICAgICAgICAgICAgPG9yZGVyX3N0YXR1cz48L29yZGVyX3N0YXR1cz4KICAgICAgICAgICAgICAgIDxvcmRlcl9yZWdpb24+MTI8L29yZGVyX3JlZ2lvbj4KICAgICAgICAgICAgICAgIDxyZWdpb25fbmFtZT7mt67lrok8L3JlZ2lvbl9uYW1lPgogICAgICAgICAgICAgICAgPGFyZWFfbnVtPjEyMTI8L2FyZWFfbnVtPgogICAgICAgICAgICAgICAgPGFyZWFfbmFtZT7mtp/msLQ8L2FyZWFfbmFtZT4KICAgICAgICAgICAgICAgIDxidWlsZF90aW1lPjIwMTgwMzEyMTUyMjQ5Nzk2PC9idWlsZF90aW1lPgogICAgICAgICAgICAgICAgPG9yZGVyX2RlbGl2X3RpbWU+MjAxODAzMTIxNTIzMDQ8L29yZGVyX2RlbGl2X3RpbWU+CiAgICAgICAgICAgICAgICA8YnVpbGRfbmFtZT7okovlubPlubM8L2J1aWxkX25hbWU+CiAgICAgICAgICAgICAgICA8YnVpbGRfdGVsPjE4OTAwMDAwMDAwPC9idWlsZF90ZWw+CiAgICAgICAgICAgICAgICA8ZGVwdF9jb2RlPjEyMTY0ODI4PC9kZXB0X2NvZGU+CiAgICAgICAgICAgICAgICA8ZGVwdF9uYW1lPua2n+awtOWVhuS4muW5v+WcuuaMh+WumuS4k+iQpeW6lzwvZGVwdF9uYW1lPgogICAgICAgICAgICAgICAgPGRpc3RfY29kZT4xMjEwNDIzMDwvZGlzdF9jb2RlPgogICAgICAgICAgICAgICAgPGRpc3RfbmFtZT7mm77ljY7vvIjnu4/plIDllYbvvIl0ZXN0PC9kaXN0X25hbWU+CiAgICAgICAgICAgICAgICA8b3JkZXJfbW9uZXk+MjA8L29yZGVyX21vbmV5PgogICAgICAgICAgICAgICAgPGRlbGl2X25hbWU+5reu5a6J5ZCI5L2c5Y6FMTwvZGVsaXZfbmFtZT4KICAgICAgICAgICAgICAgIDxkZWxpdl9tb2JpbGU+MTM5MDUxOTQ1ODE8L2RlbGl2X21vYmlsZT4KICAgICAgICAgICAgICAgIDxkZWxpdl9hZGRyZXNzPuS4reWkrui3rzQw5Y+3MTA25Y2V5YWDPC9kZWxpdl9hZGRyZXNzPgogICAgICAgICAgICAgICAgPGlzX21lZXRpbmc+MDwvaXNfbWVldGluZz4KICAgICAgICAgICAgICAgIDxtZWV0aW5nX25hbWU+PC9tZWV0aW5nX25hbWU+CiAgICAgICAgICAgICAgICA8cGF5X21vZGU+MTwvcGF5X21vZGU+CiAgICAgICAgICAgICAgICA8YWxsX3BheV9tb25leT4yMDwvYWxsX3BheV9tb25leT4KICAgICAgICAgICAgICAgIDxtdWx0aXBseV9wYXk+CiAgICAgICAgICAgICAgICAgICAgPHBheT4KICAgICAgICAgICAgICAgICAgICAgICAgPHBheV90aW1lPjIwMTgwMzEyMTUyMzA0PC9wYXlfdGltZT4KICAgICAgICAgICAgICAgICAgICAgICAgPHBheV9tb25leT4yMDwvcGF5X21vbmV5PgogICAgICAgICAgICAgICAgICAgICAgICA8cGF5X3R5cGU+RkxLPC9wYXlfdHlwZT4KICAgICAgICAgICAgICAgICAgICA8L3BheT4KICAgICAgICAgICAgICAgIDwvbXVsdGlwbHlfcGF5PgogICAgICAgICAgICAgICAgPGl0ZW1fbGlzdD4KICAgICAgICAgICAgICAgICAgICA8aXRlbT4KICAgICAgICAgICAgICAgICAgICAgICAgPGRldGFpbF9udW0+UzIwMTgwMzEyMTUyMjQ5NzIwNjA4PC9kZXRhaWxfbnVtPgogICAgICAgICAgICAgICAgICAgICAgICA8Z29vZHNfdHlwZT5TUExYX1NKWkQ8L2dvb2RzX3R5cGU+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc19udW0+SlNZRC1ERFNDU1BQLUREU0NTWEg2LTAyPC9nb29kc19udW0+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc19wcmljZT4xMDwvZ29vZHNfcHJpY2U+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc19jb3VudD4yPC9nb29kc19jb3VudD4KICAgICAgICAgICAgICAgICAgICA8L2l0ZW0+CiAgICAgICAgICAgICAgICA8L2l0ZW1fbGlzdD4KICAgICAgICAgICAgICAgIDxnaWZ0X2xpc3QvPgogICAgICAgICAgICA8L29yZGVyPgogICAgICAgICAgICA8b3JkZXI+CiAgICAgICAgICAgICAgICA8b3JkZXJfbm8+QjIwMTgwMzEyMTUyNDQwNzIwNjE1PC9vcmRlcl9ubz4KICAgICAgICAgICAgICAgIDxvcmRlcl9zdGF0dXM+PC9vcmRlcl9zdGF0dXM+CiAgICAgICAgICAgICAgICA8b3JkZXJfcmVnaW9uPjEyPC9vcmRlcl9yZWdpb24+CiAgICAgICAgICAgICAgICA8cmVnaW9uX25hbWU+5reu5a6JPC9yZWdpb25fbmFtZT4KICAgICAgICAgICAgICAgIDxhcmVhX251bT4xMjEyPC9hcmVhX251bT4KICAgICAgICAgICAgICAgIDxhcmVhX25hbWU+5raf5rC0PC9hcmVhX25hbWU+CiAgICAgICAgICAgICAgICA8YnVpbGRfdGltZT4yMDE4MDMxMjE1MjQ0MDE5ODwvYnVpbGRfdGltZT4KICAgICAgICAgICAgICAgIDxvcmRlcl9kZWxpdl90aW1lPjIwMTgwMzEyMTUyNDU3PC9vcmRlcl9kZWxpdl90aW1lPgogICAgICAgICAgICAgICAgPGJ1aWxkX25hbWU+6JKL5bmz5bmzPC9idWlsZF9uYW1lPgogICAgICAgICAgICAgICAgPGJ1aWxkX3RlbD4xODkwMDAwMDAwMDwvYnVpbGRfdGVsPgogICAgICAgICAgICAgICAgPGRlcHRfY29kZT4xMjE2NDgyODwvZGVwdF9jb2RlPgogICAgICAgICAgICAgICAgPGRlcHRfbmFtZT7mtp/msLTllYbkuJrlub/lnLrmjIflrprkuJPokKXlupc8L2RlcHRfbmFtZT4KICAgICAgICAgICAgICAgIDxkaXN0X2NvZGU+MTIxMDQyMzA8L2Rpc3RfY29kZT4KICAgICAgICAgICAgICAgIDxkaXN0X25hbWU+5pu+5Y2O77yI57uP6ZSA5ZWG77yJdGVzdDwvZGlzdF9uYW1lPgogICAgICAgICAgICAgICAgPG9yZGVyX21vbmV5PjMwPC9vcmRlcl9tb25leT4KICAgICAgICAgICAgICAgIDxkZWxpdl9uYW1lPua3ruWuieWQiOS9nOWOhTE8L2RlbGl2X25hbWU+CiAgICAgICAgICAgICAgICA8ZGVsaXZfbW9iaWxlPjEzOTA1MTk0NTgxPC9kZWxpdl9tb2JpbGU+CiAgICAgICAgICAgICAgICA8ZGVsaXZfYWRkcmVzcz7kuK3lpK7ot680MOWPtzEwNuWNleWFgzwvZGVsaXZfYWRkcmVzcz4KICAgICAgICAgICAgICAgIDxpc19tZWV0aW5nPjA8L2lzX21lZXRpbmc+CiAgICAgICAgICAgICAgICA8bWVldGluZ19uYW1lPjwvbWVldGluZ19uYW1lPgogICAgICAgICAgICAgICAgPHBheV9tb2RlPjE8L3BheV9tb2RlPgogICAgICAgICAgICAgICAgPGFsbF9wYXlfbW9uZXk+MzA8L2FsbF9wYXlfbW9uZXk+CiAgICAgICAgICAgICAgICA8bXVsdGlwbHlfcGF5PgogICAgICAgICAgICAgICAgICAgIDxwYXk+CiAgICAgICAgICAgICAgICAgICAgICAgIDxwYXlfdGltZT4yMDE4MDMxMjE1MjQ1NzwvcGF5X3RpbWU+CiAgICAgICAgICAgICAgICAgICAgICAgIDxwYXlfbW9uZXk+MzA8L3BheV9tb25leT4KICAgICAgICAgICAgICAgICAgICAgICAgPHBheV90eXBlPkZMSzwvcGF5X3R5cGU+CiAgICAgICAgICAgICAgICAgICAgPC9wYXk+CiAgICAgICAgICAgICAgICA8L211bHRpcGx5X3BheT4KICAgICAgICAgICAgICAgIDxpdGVtX2xpc3Q+CiAgICAgICAgICAgICAgICAgICAgPGl0ZW0+CiAgICAgICAgICAgICAgICAgICAgICAgIDxkZXRhaWxfbnVtPlMyMDE4MDMxMjE1MjQ0MDcyMDYxNjwvZGV0YWlsX251bT4KICAgICAgICAgICAgICAgICAgICAgICAgPGdvb2RzX3R5cGU+U1BMWF9TSlpEPC9nb29kc190eXBlPgogICAgICAgICAgICAgICAgICAgICAgICA8Z29vZHNfbnVtPkpTWUQtRERTQ1NQUC1ERFNDU1hINi0wMjwvZ29vZHNfbnVtPgogICAgICAgICAgICAgICAgICAgICAgICA8Z29vZHNfcHJpY2U+MTA8L2dvb2RzX3ByaWNlPgogICAgICAgICAgICAgICAgICAgICAgICA8Z29vZHNfY291bnQ+MzwvZ29vZHNfY291bnQ+CiAgICAgICAgICAgICAgICAgICAgPC9pdGVtPgogICAgICAgICAgICAgICAgPC9pdGVtX2xpc3Q+CiAgICAgICAgICAgICAgICA8Z2lmdF9saXN0Lz4KICAgICAgICAgICAgPC9vcmRlcj4KICAgICAgICAgICAgPG9yZGVyPgogICAgICAgICAgICAgICAgPG9yZGVyX25vPkIyMDE4MDMxMjE1MjM1NzcyMDYxMTwvb3JkZXJfbm8+CiAgICAgICAgICAgICAgICA8b3JkZXJfc3RhdHVzPjwvb3JkZXJfc3RhdHVzPgogICAgICAgICAgICAgICAgPG9yZGVyX3JlZ2lvbj4xMjwvb3JkZXJfcmVnaW9uPgogICAgICAgICAgICAgICAgPHJlZ2lvbl9uYW1lPua3ruWuiTwvcmVnaW9uX25hbWU+CiAgICAgICAgICAgICAgICA8YXJlYV9udW0+MTIxMjwvYXJlYV9udW0+CiAgICAgICAgICAgICAgICA8YXJlYV9uYW1lPua2n+awtDwvYXJlYV9uYW1lPgogICAgICAgICAgICAgICAgPGJ1aWxkX3RpbWU+MjAxODAzMTIxNTIzNTc1ODg8L2J1aWxkX3RpbWU+CiAgICAgICAgICAgICAgICA8b3JkZXJfZGVsaXZfdGltZT4yMDE4MDMxMjE1MjQwNzwvb3JkZXJfZGVsaXZfdGltZT4KICAgICAgICAgICAgICAgIDxidWlsZF9uYW1lPuiSi+W5s+W5szwvYnVpbGRfbmFtZT4KICAgICAgICAgICAgICAgIDxidWlsZF90ZWw+MTg5MDAwMDAwMDA8L2J1aWxkX3RlbD4KICAgICAgICAgICAgICAgIDxkZXB0X2NvZGU+MTIxNjQ4Mjg8L2RlcHRfY29kZT4KICAgICAgICAgICAgICAgIDxkZXB0X25hbWU+5raf5rC05ZWG5Lia5bm/5Zy65oyH5a6a5LiT6JCl5bqXPC9kZXB0X25hbWU+CiAgICAgICAgICAgICAgICA8ZGlzdF9jb2RlPjEyMTA0MjMwPC9kaXN0X2NvZGU+CiAgICAgICAgICAgICAgICA8ZGlzdF9uYW1lPuabvuWNju+8iOe7j+mUgOWVhu+8iXRlc3Q8L2Rpc3RfbmFtZT4KICAgICAgICAgICAgICAgIDxvcmRlcl9tb25leT4zMDwvb3JkZXJfbW9uZXk+CiAgICAgICAgICAgICAgICA8ZGVsaXZfbmFtZT7mt67lronlkIjkvZzljoUxPC9kZWxpdl9uYW1lPgogICAgICAgICAgICAgICAgPGRlbGl2X21vYmlsZT4xMzkwNTE5NDU4MTwvZGVsaXZfbW9iaWxlPgogICAgICAgICAgICAgICAgPGRlbGl2X2FkZHJlc3M+5Lit5aSu6LevNDDlj7cxMDbljZXlhYM8L2RlbGl2X2FkZHJlc3M+CiAgICAgICAgICAgICAgICA8aXNfbWVldGluZz4wPC9pc19tZWV0aW5nPgogICAgICAgICAgICAgICAgPG1lZXRpbmdfbmFtZT48L21lZXRpbmdfbmFtZT4KICAgICAgICAgICAgICAgIDxwYXlfbW9kZT4xPC9wYXlfbW9kZT4KICAgICAgICAgICAgICAgIDxhbGxfcGF5X21vbmV5PjMwPC9hbGxfcGF5X21vbmV5PgogICAgICAgICAgICAgICAgPG11bHRpcGx5X3BheT4KICAgICAgICAgICAgICAgICAgICA8cGF5PgogICAgICAgICAgICAgICAgICAgICAgICA8cGF5X3RpbWU+MjAxODAzMTIxNTI0MDc8L3BheV90aW1lPgogICAgICAgICAgICAgICAgICAgICAgICA8cGF5X21vbmV5PjMwPC9wYXlfbW9uZXk+CiAgICAgICAgICAgICAgICAgICAgICAgIDxwYXlfdHlwZT5KQks8L3BheV90eXBlPgogICAgICAgICAgICAgICAgICAgIDwvcGF5PgogICAgICAgICAgICAgICAgPC9tdWx0aXBseV9wYXk+CiAgICAgICAgICAgICAgICA8aXRlbV9saXN0PgogICAgICAgICAgICAgICAgICAgIDxpdGVtPgogICAgICAgICAgICAgICAgICAgICAgICA8ZGV0YWlsX251bT5TMjAxODAzMTIxNTIzNTc3MjA2MTI8L2RldGFpbF9udW0+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc190eXBlPlNQTFhfU0paRDwvZ29vZHNfdHlwZT4KICAgICAgICAgICAgICAgICAgICAgICAgPGdvb2RzX251bT5KU1lELUREU0NTUFAtRERTQ1NYSDYtMDI8L2dvb2RzX251bT4KICAgICAgICAgICAgICAgICAgICAgICAgPGdvb2RzX3ByaWNlPjEwPC9nb29kc19wcmljZT4KICAgICAgICAgICAgICAgICAgICAgICAgPGdvb2RzX2NvdW50PjM8L2dvb2RzX2NvdW50PgogICAgICAgICAgICAgICAgICAgIDwvaXRlbT4KICAgICAgICAgICAgICAgIDwvaXRlbV9saXN0PgogICAgICAgICAgICAgICAgPGdpZnRfbGlzdC8+CiAgICAgICAgICAgIDwvb3JkZXI+CiAgICAgICAgICAgIDxvcmRlcj4KICAgICAgICAgICAgICAgIDxvcmRlcl9ubz5CMjAxODAzMTIxNTEzMjg3MjA2MDM8L29yZGVyX25vPgogICAgICAgICAgICAgICAgPG9yZGVyX3N0YXR1cz48L29yZGVyX3N0YXR1cz4KICAgICAgICAgICAgICAgIDxvcmRlcl9yZWdpb24+MTI8L29yZGVyX3JlZ2lvbj4KICAgICAgICAgICAgICAgIDxyZWdpb25fbmFtZT7mt67lrok8L3JlZ2lvbl9uYW1lPgogICAgICAgICAgICAgICAgPGFyZWFfbnVtPjEyMTI8L2FyZWFfbnVtPgogICAgICAgICAgICAgICAgPGFyZWFfbmFtZT7mtp/msLQ8L2FyZWFfbmFtZT4KICAgICAgICAgICAgICAgIDxidWlsZF90aW1lPjIwMTgwMzEyMTUxMzI4MjY3PC9idWlsZF90aW1lPgogICAgICAgICAgICAgICAgPG9yZGVyX2RlbGl2X3RpbWU+MjAxODAzMTIxNTIzMTg8L29yZGVyX2RlbGl2X3RpbWU+CiAgICAgICAgICAgICAgICA8YnVpbGRfbmFtZT7okovlubPlubM8L2J1aWxkX25hbWU+CiAgICAgICAgICAgICAgICA8YnVpbGRfdGVsPjE4OTAwMDAwMDAwPC9idWlsZF90ZWw+CiAgICAgICAgICAgICAgICA8ZGVwdF9jb2RlPjEyMTY0ODI4PC9kZXB0X2NvZGU+CiAgICAgICAgICAgICAgICA8ZGVwdF9uYW1lPua2n+awtOWVhuS4muW5v+WcuuaMh+WumuS4k+iQpeW6lzwvZGVwdF9uYW1lPgogICAgICAgICAgICAgICAgPGRpc3RfY29kZT4xMjEwNDIzMDwvZGlzdF9jb2RlPgogICAgICAgICAgICAgICAgPGRpc3RfbmFtZT7mm77ljY7vvIjnu4/plIDllYbvvIl0ZXN0PC9kaXN0X25hbWU+CiAgICAgICAgICAgICAgICA8b3JkZXJfbW9uZXk+MjA8L29yZGVyX21vbmV5PgogICAgICAgICAgICAgICAgPGRlbGl2X25hbWU+5reu5a6J5ZCI5L2c5Y6FMTwvZGVsaXZfbmFtZT4KICAgICAgICAgICAgICAgIDxkZWxpdl9tb2JpbGU+MTM5MDUxOTQ1ODE8L2RlbGl2X21vYmlsZT4KICAgICAgICAgICAgICAgIDxkZWxpdl9hZGRyZXNzPuS4reWkrui3rzQw5Y+3MTA25Y2V5YWDPC9kZWxpdl9hZGRyZXNzPgogICAgICAgICAgICAgICAgPGlzX21lZXRpbmc+MDwvaXNfbWVldGluZz4KICAgICAgICAgICAgICAgIDxtZWV0aW5nX25hbWU+PC9tZWV0aW5nX25hbWU+CiAgICAgICAgICAgICAgICA8cGF5X21vZGU+MTwvcGF5X21vZGU+CiAgICAgICAgICAgICAgICA8YWxsX3BheV9tb25leT4yMDwvYWxsX3BheV9tb25leT4KICAgICAgICAgICAgICAgIDxtdWx0aXBseV9wYXk+CiAgICAgICAgICAgICAgICAgICAgPHBheT4KICAgICAgICAgICAgICAgICAgICAgICAgPHBheV90aW1lPjIwMTgwMzEyMTUyMzE4PC9wYXlfdGltZT4KICAgICAgICAgICAgICAgICAgICAgICAgPHBheV9tb25leT4yMDwvcGF5X21vbmV5PgogICAgICAgICAgICAgICAgICAgICAgICA8cGF5X3R5cGU+RkxLPC9wYXlfdHlwZT4KICAgICAgICAgICAgICAgICAgICA8L3BheT4KICAgICAgICAgICAgICAgIDwvbXVsdGlwbHlfcGF5PgogICAgICAgICAgICAgICAgPGl0ZW1fbGlzdD4KICAgICAgICAgICAgICAgICAgICA8aXRlbT4KICAgICAgICAgICAgICAgICAgICAgICAgPGRldGFpbF9udW0+UzIwMTgwMzEyMTUxMzI4NzIwNjA0PC9kZXRhaWxfbnVtPgogICAgICAgICAgICAgICAgICAgICAgICA8Z29vZHNfdHlwZT5TUExYX1NKWkQ8L2dvb2RzX3R5cGU+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc19udW0+SlNZRC1ERFNDU1BQLUREU0NTWEg2LTAyPC9nb29kc19udW0+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc19wcmljZT4xMDwvZ29vZHNfcHJpY2U+CiAgICAgICAgICAgICAgICAgICAgICAgIDxnb29kc19jb3VudD4yPC9nb29kc19jb3VudD4KICAgICAgICAgICAgICAgICAgICA8L2l0ZW0+CiAgICAgICAgICAgICAgICA8L2l0ZW1fbGlzdD4KICAgICAgICAgICAgICAgIDxnaWZ0X2xpc3QvPgogICAgICAgICAgICA8L29yZGVyPgogICAgICAgIDwvb3JkZXJfbGlzdHM+CiAgICA8L2JvZHk+CjwvcmVxdWVzdD4="));
var dt = DateTime.Now;
//using (SQLServerAccess sq = new SQLServerAccess())
//{
// sq.Open("Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=ChuYuWang_UC;Data Source=.;pooling=true;connection lifetime=0;min pool size = 1;max pool size=2000");
// Console.WriteLine($"sqlserver open cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
// var sql = "select * from CodeDictionary";
// DBOParameterCollection dpc = new DBOParameterCollection();
// var result = sq.Query(sql, dpc);
// Console.WriteLine($"sqlserver cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
// result = sq.Query(sql, dpc);
// Console.WriteLine($"sqlserver2 cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
//}
//Console.WriteLine($"sqlserver cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
//using (SqliteAccess sq = new SqliteAccess())
//{
// sq.Open("Data Source=./AppData/db_jiangsu.db;");
// Console.WriteLine($"sqlite open cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
// var sql = "SELECT COUNT(1) FROM orders AS orders WHERE orders.build_time LIKE '%'||$4432";
// DBOParameterCollection dpc = new DBOParameterCollection();
// dpc.SetValue("4432", "20180309");
// var result = sq.Query(sql, dpc);
// Console.WriteLine($"sqlite cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
// result = sq.Query(sql, dpc);
// Console.WriteLine($"sqlite2 cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
//}
//Console.WriteLine($"sqlite cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
using (MySQLAccess sq = new MySQLAccess())
{
sq.Open("server=10.15.1.240;user id=root;password=111111;database=ptac_b2b;charset=utf8;Convert Zero Datetime=true;Allow Zero Datetime=true;");
Console.WriteLine($"1:open cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
var s = from t in sq.NewLinqTable("admin_user", "a")
select t;
var sql = s.ToSql();
DBOParameterCollection dpc = new DBOParameterCollection();
//dpc.SetValue("id", 1);
var result = sq.Query(sql, dpc);
Console.WriteLine($"1:query cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
dpc.SetValue("userid", "167");
result = sq.Query(sql + " where user_id=@userid", dpc);
Console.WriteLine($"1:query2 cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
}
//using (MySQLAccess sq = new MySQLAccess())
//{
// sq.Open("server=10.15.1.240;user id=root;password=111111;database=ptac_shop;charset=utf8;Convert Zero Datetime=true;Allow Zero Datetime=true;");
// Console.WriteLine($"2:open cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
// var sql = "SELECT operating_unit_id FROM ecs_admin_user ";
// DBOParameterCollection dpc = new DBOParameterCollection();
// //dpc.SetValue("id", 1);
// var result = sq.Query(sql, dpc);
// Console.WriteLine($"2:query cast time:{(DateTime.Now - dt).TotalMilliseconds}ms"); dt = DateTime.Now;
//}
}
/// <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 override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
if (sqlcomm == null)
{
sqlcomm = new SqlCommand(sql, this.sqlconn);
sqlcomm.CommandTimeout = 90;
}
else
{
sqlcomm.CommandText = sql;
}
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
if (_s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.trans;
}
try
{
sqlcomm.ExecuteNonQuery();
}
finally
{
sqlcomm.Cancel();
sqlcomm = null;
}
}
public UnitParameter()
{
Count_Of_OnePage = 5;
CurrentPage = 0;
ToPage = 0;
_spp = new DBOParameterCollection();
}
public override void Delete(object data, string toTable)
{
if (data == null)
{
return;
}
string sql = "delete from " + toTable;
string where = "";
DBOParameterCollection dpc = new DBOParameterCollection();
if (data is FrameDLRObject)
{
var dobj = (FrameDLRObject)data;
var keycols = dobj.Keys;
foreach (var key in keycols)
{
dpc.SetValue(key, dobj.GetValue(key));
where += (where == "" ? " where " : " and ") + $" {key}={ParameterFlagChar}{key}";
}
}
else
{
var fields = data.GetType().GetTypeInfo().GetFields(BindingFlags.Instance | BindingFlags.Public).ToList();
foreach (var f in fields)
{
dpc.SetValue(f.Name, f.GetValue(data));
where += (where == "" ? " where " : " and ") + $" {f.Name}={ParameterFlagChar}{f.Name}";
}
}
sql = sql + where;
this.ExecuteNoQuery(sql, dpc);
}
public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
{
var newsql = ConvertSQL(sql);
if (sqlcomm == null)
{
sqlcomm = new NpgsqlCommand(newsql, this.conn);
sqlcomm.CommandTimeout = CommandTimeOut;
}
else
{
sqlcomm.CommandText = newsql;
}
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
if (_s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.tran;
}
using (sqlcomm)
{
sqlcomm.ExecuteNonQuery();
}
}
public override DataSetStd Query(string sql, DBOParameterCollection dbp)
{
var newsql = ConvertSQL(sql);
if (sqlcomm == null)
{
sqlcomm = new NpgsqlCommand(newsql, this.conn);
sqlcomm.CommandTimeout = CommandTimeOut;
}
else
{
sqlcomm.CommandText = newsql;
}
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.tran;
}
DataSetStd ds = new DataSetStd();
NpgsqlDataReader ddr = null;
try
{
//如果有參數
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
ddr = sqlcomm.ExecuteReader();
ds = DataSetStd.FillData(ddr);
}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
// throw ex;
//}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
sqlcomm.Dispose();
sqlcomm = null;
}
return(ds);
}
public override DataSetStd Query(string sql, DBOParameterCollection dbp)
{
if (sqlcomm == null)
{
sqlcomm = new SqlCommand(sql, this.sqlconn);
sqlcomm.CommandTimeout = 90;
}
else
{
sqlcomm.CommandText = sql;
}
//如果事務開啟，則使用事務的方式
if (this._s == DBStatus.Begin_Trans)
{
sqlcomm.Transaction = this.trans;
}
DataSetStd ds = new DataSetStd();
SqlDataReader ddr = null;
try
{
//如果有參數
if (dbp != null)
{
FillParametersToCommand(sqlcomm, dbp);
}
ddr = sqlcomm.ExecuteReader();
ds = DataSetStd.FillData(ddr);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
sqlcomm.Cancel();
sqlcomm = null;
}
return(ds);
}
/// <summary>
/// 通過sql獲得相關的欄位列表
/// </summary>
/// <param name="sql"></param>
/// <param name="p"></param>
/// <returns></returns>
private string[] GetColumnsNameBySql(string sql, DBOParameterCollection p)
{
if (sql != sql4PageOrder || columnsName4PageOrder == null)
{
sql4PageOrder = sql;
DataSetStd dss = Query(sql, p);
if (dss != null)
{
columnsName4PageOrder = dss[0].ColumnNames;
}
}
return(columnsName4PageOrder);
}
/// <summary>
/// 依據Dictionary填充Command物件參數集
/// </summary>
/// <param name="command">命令物件</param>
/// <param name="parameters">參數</param>
private void FillParametersToCommand(DbCommand command, DBOParameterCollection parameters)
{
foreach (string parameterName in parameters.Keys)
{
DbParameter idparameter = null;
//PostgreSQL的参数符号为:,但因其:用法很多，故改为其它的符号替代，然后再做sql转换工作
if (!command.Parameters.Contains(ParameterFlagChar + parameters[parameterName].ParameterName))
{
idparameter = command.CreateParameter();
idparameter.ParameterName = string.Concat(":", parameters[parameterName].ParameterName);
command.Parameters.Add(idparameter);
}
else
{
idparameter = command.Parameters[":" + parameterName];
}
if (parameters[parameterName].DataLength > 0)
{
idparameter.Direction = parameters[parameterName].Direction;
idparameter.DbType = parameters[parameterName].DataType;
idparameter.Size = parameters[parameterName].DataLength;
}
if (parameters[parameterName].ParameterValue == null)
{
idparameter.Value = DBNull.Value;
}
else if (ComFunc.nvl(parameters[parameterName].ParameterValue) == "")
{
if (parameters[parameterName].DataType != DbType.String)
{
idparameter.Value = DBNull.Value;
}
else
{
idparameter.Value = parameters[parameterName].ParameterValue;
}
}
else
{
idparameter.Value = parameters[parameterName].ParameterValue;
}
}
}
/// <summary>
/// 設定翻頁條件，啟動翻頁機制
/// </summary>
/// <param name="p"></param>
public void StartPageByCondition(DBAPageP p)
{
this._Count_of_OnePage = 0;
this._current_page = 0;
this._source_sql_page = "";
this._source_sql_parameters_page = null;
this._to_Page = 0;
this._total_page = 0;
this._is_page = false;
this._rownumber_order_by = "";
this._row_count = 0;
this._Count_of_OnePage = p.Count_of_OnePage;
this._source_sql_page = p.SQL;
this._source_sql_parameters_page = p.SQL_Parameters;
if (!string.IsNullOrEmpty(p.OrderBy))
{
this._rownumber_order_by = p.OrderBy;
}
this._is_page = true;
if (p.CurrentPage >= 0)
{
this._current_page = p.CurrentPage;
}
if (this._source_sql_page.Trim().EndsWith(";"))
{
this._source_sql_page = this._source_sql_page.Trim().Substring(0, this._source_sql_page.Trim().Length - 1);
}
string sql = "select count(1) from (" + this._source_sql_page + ") t";
DBOParameterCollection SQL_Parameters = this._source_sql_parameters_page;
DataSetStd dss = Query(sql, SQL_Parameters);
IntStd totalcount = IntStd.ParseStd(dss.GetValue(0, 0));
if (totalcount != null && totalcount != 0)
{
_row_count = totalcount;
if (totalcount.Value % this._Count_of_OnePage == 0)
{
this._total_page = (int)(totalcount.Value / this._Count_of_OnePage);
}
else
{
this._total_page = (int)(totalcount.Value / this._Count_of_OnePage) + 1;
}
}
}
/// <summary>
/// 依據Dictionary填充Command物件參數集
/// </summary>
/// <param name="command">命令物件</param>
/// <param name="parameters">參數</param>
private void FillParametersToCommand(DbCommand command, DBOParameterCollection parameters)
{
foreach (string parameterName in parameters.Keys)
{
DbParameter idparameter = null;
if (!command.Parameters.Contains("@" + parameters[parameterName].ParameterName))
{
idparameter = command.CreateParameter();
idparameter.ParameterName = string.Concat("@", parameters[parameterName].ParameterName);
command.Parameters.Add(idparameter);
}
else
{
idparameter = command.Parameters["@" + parameterName];
}
if (parameters[parameterName].DataLength > 0)
{
idparameter.Direction = parameters[parameterName].Direction;
idparameter.DbType = parameters[parameterName].DataType;
idparameter.Size = parameters[parameterName].DataLength;
}
if (parameters[parameterName].ParameterValue == null)
{
idparameter.Value = DBNull.Value;
}
else if (ComFunc.nvl(parameters[parameterName].ParameterValue) == "")
{
if (parameters[parameterName].DataType != DbType.String)
{
idparameter.Value = DBNull.Value;
}
else
{
idparameter.Value = parameters[parameterName].ParameterValue;
}
}
else
{
idparameter.Value = parameters[parameterName].ParameterValue;
}
}
}
public virtual void InsertIntoSqlServer(UnitParameter p, string tablename, FrameDLRObject obj)
{
StringBuilder sbsql = new StringBuilder();
sbsql.Append("insert into " + tablename + "(");
var values = "";
var columns = "";
DBOParameterCollection dpc = new DBOParameterCollection();
foreach (var s in obj.Keys)
{
dpc.SetValue(s, obj.GetValue(s));
columns += columns.Length > 0 ? "," + s : s;
values += values.Length > 0 ? ",@" + s : "@" + s;
}
sbsql.Append(columns + ")values(");
sbsql.Append(values);
sbsql.Append(");");
if (p.Dao is ADBAccess)
{
((ADBAccess)p.Dao).ExecuteNoQuery(sbsql.ToString(), dpc);
}
}
/// <summary>
/// 根据obj的定义更新单表，obj定义格式如下
/// {
/// col1:value,
/// coln:value,
/// where:{
/// c1:{$op:value}
/// cn:{$op:value}
/// }
/// }
/// </summary>
/// <param name="p"></param>
/// <param name="table"></param>
/// <param name="obj"></param>
public virtual void UpdateSQLServer(UnitParameter p, string tablename, FrameDLRObject obj)
{
StringBuilder sbsql = new StringBuilder();
sbsql.Append("update " + tablename + " set ");
StringBuilder columns = new StringBuilder();
string where = "";
DBOParameterCollection dpc = new DBOParameterCollection();
foreach (var s in obj.Keys)
{
if (s.ToLower() != "where")
{
var pkey = s.Replace("(", "").Replace(")", "").Replace("'", "").Replace("\"", "").Replace("[", "").Replace("]", "").Replace(".", "");
dpc.SetValue(pkey, obj.GetValue(s));
columns.Append(columns.Length > 0 ? "," : "");
columns.AppendLine(string.Format("{0}=@{0}", s));
}
else
{
where = ParseWhereExpress(DBType.SqlServer, obj.GetValue(s), ref dpc);
}
}
sbsql.Append(columns);
sbsql.Append(where);
if (p.Dao == null)
{
p.Dao = _logic.CallContext_ResourceManage.CreateInstance <SQLServerAccess>(p.CurrentTransToken);
p.Dao.Open(ComFunc.nvl(_logic.Configs[ParameterKey.DBCONNECT_STRING]));
}
//目前只支持关系型数据库
if (p.Dao is ADBAccess)
{
((ADBAccess)p.Dao).ExecuteNoQuery(sbsql.ToString(), dpc);
}
}
public DataCollection DoOperate(ParameterStd p)
{
string flag = p.GetValue <string>("_unit_action_flag_");
UnitParameter up = (UnitParameter)p;
T t = (T)Activator.CreateInstance(typeof(T), true);
var sqlobj = t.GetSqlFunc(flag)(up);
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要的动态数据对象类型应该为FrameDLRObject");
}
IDBAccessInfo dba = up.Dao;
DBOParameterCollection dbc = up.SPParameter;
UnitDataCollection rtn = new UnitDataCollection();
if (dba is ADBAccess)
{
bool isrturnds = sqlobj.isreturnds != null ? sqlobj.isreturnds : false;
DBDataCollection dbrtn = ((ADBAccess)dba).ExcuteProcedure(ComFunc.nvl(sqlobj.spname), isrturnds, ref dbc);
if (dbrtn.IsSuccess)
{
foreach (string s in dbrtn.Keys)
{
if (dbrtn[s] is DataSetStd)
{
rtn.QueryDatas = dbrtn.ReturnDataSet;
}
else
{
rtn.SetValue(s, dbrtn[s]);
}
}
}
}
return(rtn);
}
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();
}
}
/// <summary>
/// 执行SP，连接串使用默认框架默认连接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="spname"></param>
/// <param name="isreturnds"></param>
/// <param name="dbp"></param>
/// <returns></returns>
protected UnitDataCollection ExcuteSP <T>(string spname, bool isreturnds, DBOParameterCollection dbp) where T : ADBAccess, IResourceEntity
{
return(ExcuteSP <T>(ComFunc.nvl(Configs[ParameterKey.DBCONNECT_STRING]), spname, isreturnds, dbp));
}
/// <summary>
/// 执行sp
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connstr"></param>
/// <param name="spname"></param>
/// <param name="isreturnds"></param>
/// <param name="dbp"></param>
/// <returns></returns>
protected virtual UnitDataCollection ExcuteSP <T>(string connstr, string spname, bool isreturnds, DBOParameterCollection dbp) where T : ADBAccess, IResourceEntity
{
T dao = GetDao <T>(connstr, null);
try
{
UnitDataCollection rtn = new UnitDataCollection();
DBDataCollection dbrtn = dao.ExcuteProcedure(ComFunc.nvl(spname), isreturnds, ref dbp);
if (dbrtn.IsSuccess)
{
foreach (string s in dbrtn.Keys)
{
if (dbrtn[s] is DataSetStd)
{
rtn.QueryDatas = dbrtn.ReturnDataSet;
}
else
{
rtn.SetValue(s, dbrtn[s]);
}
}
}
return(rtn);
}
finally
{
dao.Close();
}
}
protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
string newsql = @"WITH [__test] AS "
+ "(select *,ROW_NUMBER() OVER (order by " + orderby4page + " ) as RowNumber from (" + sql + ") table2)"
+ "SELECT TOP " + count_of_page + " * "
+ "FROM [__test] "
+ "WHERE (RowNumber > "
+ "(SELECT ISNULL(max(RowNumber),0) "
+ "FROM (SELECT TOP (" + (count_of_page * (toPage - 1)) + ") RowNumber "
+ "FROM [__test] "
+ "ORDER BY RowNumber) AS T)) ";
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
/// <summary> /// 执行非查询操作 /// </summary> /// <param name="sql"></param> /// <param name="dbp"></param> public abstract void ExecuteNoQuery(string sql, DBOParameterCollection dbp);
protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
string newsql = @"select *,NEWID() as _page_row_guid into #tmptt from(
" + sql + @")a
SELECT TOP " + count_of_page + @" *
FROM #tmptt
WHERE _page_row_guid NOT IN
(
SELECT TOP " + (count_of_page * (toPage - 1)) + @" _page_row_guid FROM #tmptt ORDER BY " + orderby4page + @"
)
ORDER BY " + orderby4page + @"
drop table #tmptt";
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
/// <summary> /// 執行存儲過程 /// </summary> /// <param name="sp_name"></param> /// <param name="isReturnDataSet"></param> /// <param name="dbp"></param> /// <returns></returns> public abstract DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp);
/// <summary> /// 執行翻頁查詢，V1.0.0.0新增 /// </summary> /// <param name="startRow"></param> /// <param name="endRow"></param> /// <param name="toPage"></param> /// <param name="count_of_page"></param> /// <param name="sql"></param> /// <param name="orderby"></param> /// <param name="p"></param> /// <returns></returns> protected abstract DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p);
protected override Net.Base.Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
{
string orderby4page = orderby;
if (orderby == null || orderby == "")
{
orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
}
string newsql = @"
select * from (" + sql + @") order by " + orderby4page + @" limit " + count_of_page + " offset " + (count_of_page * (toPage - 1));
DataSetStd dss = Query(newsql, p);
if (dss == null)
{
return(null);
}
else
{
return(dss[0]);
}
}
/// <summary> /// 查询接口 /// </summary> /// <param name="sql"></param> /// <param name="dbp"></param> /// <returns></returns> public abstract DataSetStd Query(string sql, DBOParameterCollection dbp);
public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
{
DBDataCollection rtn = new DBDataCollection();
rtn.IsSuccess = false;
DataSetStd ds = new DataSetStd();
SqliteCommand dc = null;//new SqlCommand(p.StoreProcureName, this.sqlconn);
if (this._s == DBStatus.Begin_Trans)
{
dc = new SqliteCommand(sp_name, this.sqlconn, this.trans);
}
else
{
dc = new SqliteCommand(sp_name, this.sqlconn);
}
//dc.CommandTimeout = 90;
dc.CommandType = CommandType.StoredProcedure;
FillParametersToCommand(dc, dbp);
SqliteDataReader ddr = null;
try
{
if (isReturnDataSet)
{
ddr = dc.ExecuteReader();
ds = DataSetStd.FillData(ddr);
rtn.ReturnDataSet = ds;
}
else
{
dc.ExecuteNonQuery();
}
//獲取返回值
foreach (SqliteParameter sp in dc.Parameters)
{
if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
{
rtn.SetValue(sp.ParameterName.Replace(ParameterFlagChar, ""), sp.Value);
}
}
rtn.IsSuccess = true;
}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
dc.Dispose();
dc = null;
}
return(rtn);
}
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);
}
