Parse and execute Sql statements from file

Question

I am coding a SQL Execution Module, which is part of a medical application.

The module

1. accepts an encrypted .sql file name as input. The file will consist of dml statments like insert rows/update rows,comments (Sample below)
2. Decrypts the file
3. Prepares a list of SQL statements and executes, ignoring the comment lines (lines that start with --)
4. Pops up a linenumber in case of error

The method adopted

1. Loading sqlfile and descryption is ok and complete
   Using a simple File.ReadAllText and a pre-built decryption module

2. Preparing list is where the module fails. I used this code:

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

   It causes the execution to ignore the SQL that appears just after a comment due to way it has been split.

The data cannot be split just from linebreak, because there are linebreaks in column values of insert statements. Same goes for semi-colon

• DB: Postgres9.0.4
• Code: .NET 4.0, C#, Npgsql

Any help with a solution is welcome

Thanks

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

SAMPLEDATA BELOW

---

-- 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

You should consider spawning psql externally to import from file. You should be able to send the info via stdin to psql, rather than writing to an actual file on disk - since you mentioned the input data is encrypted.

The output from psql can be caught and given back to the user.

Parsing SQL should be done by a specialised parser. As an alternative to psql, try looking at using a library that can do this for you, such as the Entity Framework. See Parsing SQL code in C#

Answer 2

try this.If you have a query with one or more GO statements you will have to split the script to separate all queries and execute them one by one

var fileContent = File.ReadAllText("query.sql");
var sqlqueries = fileContent.Split(new[] {" GO "}, StringSplitOptions.RemoveEmptyEntries);

var con = new SqlConnection("connstring");
var cmd = new SqlCommand("query", con);
con.Open();
foreach (var query in sqlqueries)
{
    cmd.CommandText = query;
    cmd.ExecuteNonQuery();
}
con.Close();

Answer 3

This is kind of a hack, but for something simple (Select/insert/delete/update) could work:

string pattern = "(SELECT|INSERT|DELETE|UPDATE|--)"; //ADD HERE THE SQL VERBS YOU NEED
string[] result = Regex.Split(sqls, pattern).Where(s => s != String.Empty).ToArray<string>();
for (int i = 0; i < result.Count(); i += 2) 
{
     if (!result[i].StartsWith("--"))
     {
           Console.WriteLine(result[i] + result[i + 1]);
     }
}

I'm sure this can be improved, but you get the idea. And it works with your sample data

Answer 4

You may to try to execute your script using execute plpgsql command:

do $$
begin
  execute 'create table t (x int); insert into t values (1), (2), (3); drop table t;'; -- Change to your script here
end $$ language plpgsql;

or create stored function for it:

create or replace function execute_script(in p_script text) returns void as $$
begin
  execute p_script;
end $$ language plpgsql;