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); }