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!