My initial problem was to extract from a SQL string generated by MySql, all fields. I had a string like

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
INSERT INTO `inventoryapp`.`inventory_keys`
(`Id`,
`PropertyId`,
`AppointmentId`,
`SectionType`,
`KeysDescription`,
`FobsWorking`,
`EntryCodes`,
`AlarmCodes`,
`Notes`,
`Version`,
`CreatedDate`,
`CreatedBy`,
`UpdatedDate`,
`UpdatedBy`,
`IsDeleted`)
VALUES
(<{Id: }>,
<{PropertyId: }>,
<{AppointmentId: }>,
<{SectionType: }>,
<{KeysDescription: }>,
<{FobsWorking: }>,
<{EntryCodes: }>,
<{AlarmCodes: }>,
<{Notes: }>,
<{Version: }>,
<{CreatedDate: }>,
<{CreatedBy: }>,
<{UpdatedDate: }>,
<{UpdatedBy: }>,
<{IsDeleted: }>);

With a bit of RegEx I can extract all fields with the following function:

1
2
3
4
5
6
7
public IEnumerable<string> GetSubStrings(string input, string start,
                                         string end) {
    Regex r = new Regex(Regex.Escape(start) + "(.*?)" + Regex.Escape(end));
    MatchCollection matches = r.Matches(input);
    foreach (Match match in matches)
        yield return match.Groups[1].Value;
}

Then if you want to have in a single line this query and generate the list of fields to have a list of parameters for a MySqlCommand for example, you can use the following function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
string strSQL = yoursql.Replace(Environment.NewLine, "")
                       .Replace("\r", " ");
string strParameters = "";
List<string> prm = GetSubStrings(strSQL, "<{", ": }>").ToList();
foreach(string s in prm) {
    string tmp = s.Replace("?", "");
    strParameters += Environment.NewLine +
        $"cmd.Parameters.Add(\"?{tmp}\", MySqlDbType.VarChar)" +
        $".Value = record.{tmp}"; ;
}
this.textBoxParams.Text = strParameters;
strSQL = strSQL.Replace("`inventoryapp`.", "");
strSQL = strSQL.Replace("<{", "?").Replace(": }>", "");

Happy coding!

By Enrico

My greatest passion is technology. I am interested in multiple fields and I have a lot of experience in software design and development. I started professional development when I was 6 years. Today I am a strong full-stack .NET developer (C#, Xamarin, Azure)

This site uses Akismet to reduce spam. Learn how your comment data is processed.