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!