c# - 从文件中解析并执行Sql语句

Question

我正在编写一个 SQL 执行模块，它是医疗应用程序的一部分。

模块

1. 接受加密的 .sql 文件名作为输入。该文件将包含 dml 语句，例如插入行/更新行、注释(下面的示例)
2. 解密文件
3. 准备 SQL 语句列表并执行，忽略注释行(以 -- 开头的行)
4. 出现错误时弹出行号

采用的方法

1. 加载 sqlfile 和解密是 ok 和完成
   使用简单的 File.ReadAllText 和预建的解密模块

2. 准备列表是模块失败的地方。我使用了这段代码:

   string[] dmllines = dml.Split(new string[] { ";\r\n", ";\n" }, StringSplitOptions.RemoveEmptyEntries);
   

   它会导致执行忽略由于拆分方式而出现在评论之后的 SQL。

不能只从linebreak拆分数据，因为insert语句的column values中有linebreak。分号也是如此

• 数据库:Postgres9.0.4
• 代码:.NET 4.0、C#、Npgsql

欢迎任何解决方案的帮助

谢谢

EDIT1: I would like to add, There isn't any possibility of using third-party libraries , utilities , even if of postgres.
EDIT2: I am working on a possibility using regex or just while loop in which to parse char by char

下面的示例数据

---

-- Name: activitylog_recordid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('activitylog_recordid_seq', 1022, true); INSERT INTO posted (refnum, vdate, contact, totalamount, bankname, staffname, trnmode, trngroup, bookmark, patientid, postedon, statusdate, status, ourbank, chequecarddetail, comment, createdon, editedon, loginid, recordid, sourcetable, sourceid, tallyid, rounding, placed) VALUES (NULL, '2016-02-14 17:51:55', 'AHLCON PARENTERALS (INDIA) LTD', 232.00, '', '', 'Cheques', 'PATIENT RECEIPTS', false, 0, NULL, NULL, 'Cleared', '', '', '', '2016-02-14 17:52:03', NULL, 'PPPP', 59, NULL, 0, 63, NULL, NULL); --Insert Imported from DB:00319 INSERT INTO posted (refnum, vdate, contact, totalamount, bankname, staffname, trnmode, trngroup, bookmark, patientid, postedon, statusdate, status, ourbank, chequecarddetail, comment, createdon, editedon, loginid, recordid, sourcetable, sourceid, tallyid, rounding, placed) VALUES (NULL, '2016-02-14 18:04:48', 'AHLCON PARENTERALS (INDIA) LTD', 400.00, '', '', 'Cash', 'PATIENT RECEIPTS;', false, 0, NULL, NULL, 'Cleared', '', '', '', '2016-02-14 18:04:50', NULL, 'admin', 60, NULL, 0, 64, NULL, NULL); INSERT INTO printed (donorname, referencenum, baggageid, campname, receipttype, qty, units, receiptdetail, storestaff, verifiedby, verifiedon, isunsuitable, receiptdate, unsuitablecomment, comment, createdon, editedon, loginid, recordid, qtyissued, qtybalance, expiry, barcode, donorref, barcodedonor, bloodadditives, licenseref, source, attributes, category) VALUES ('', NULL, 'B31399040', 'C131 BLUD', 'Blood', 1200.0000, 'mg/Ml', 'AB_Positive', 'HELLOO', 'PALS', '2015-06-30 19:03:52', false, '2015-06-29 19:03:52', '', NULL, '2015-06-30 19:05:10', '2015-07-03 12:15:33', 'PPPP', 4, 200.0000, 1000.0000, '2019-06-30 19:03:52', NULL, 'd753', NULL, 'Sodium Citrate (dihydrate)....2.63g Citric Acid (monohydrate)...0.299g Dextrose (monohydrate).......2.55g Monobasic Sodium Biphosphate (monohydrate).0.222g', '', '', NULL, NULL);

Answer 1

你应该考虑产卵 psql外部从文件导入。您应该能够通过标准输入将信息发送到 psql，而不是写入磁盘上的实际文件 - 因为您提到输入数据已加密。

可以捕获 psql 的输出并将其返回给用户。

解析 SQL 应该由专门的解析器来完成。作为 psql 的替代方案，尝试使用可以为您执行此操作的库，例如 Entity Framework .参见 Parsing SQL code in C#