Data Interaction layer parameter Substitution Solution in C #

In the previous two articles, I tried to use Java as an example of a sudden whim? Replace the parameter. However, it is very troublesome to fill in the parameter again when the parameter is repeated.

Due to the recent study and use of nhib.pdf, the [: parameter name] Method in Hql can solve the problem of repeated parameters.

Because Hashtable is used when the key value is passed, we need to use the regular expression to match [": \ w +"] and obtain the corresponding key to obtain the corresponding value.

The Code is as follows:

1 Regex regMark = newRegex (@ ": \ w + ");
2sql = regMark. Replace (SQL, s =>
3 {
4 stringmark = s. Value. Substring (1), paramName = string. Empty;
5 if (columns. ContainsKey (mark ))
6 {
7 // obtain the parameter name and create DbParameter
8}
9 else
10 {
11 thrownewNullReferenceException ("the corresponding tag key value does not exist! ");
12}
13 returnparamName;
14 });

Because the parameters may be repeated, you must obtain the DbParameter and @ parameter names in SQL by using the corresponding [: parameter name, in addition, the value passed through Hashtable may be an array. Therefore, I chose to use the generic dictionary IDictionary <string, IDictionary <string, DbParameter> storage.

The Code is as follows:

1 /// <summary>
2 // conversion mark
3 /// </summary>
4 // <param name = "SQL"> SQL statement </param>
5 /// <param name = "columns"> tag value Hashtable </param>
6 /// <returns> </returns>
7 publicDbParameter [] CastMark (refstringsql, Hashtable columns)
8 {
9 Regex regMark = newRegex (@ ": \ w + ");
10 SQL = regMark. Replace (SQL, s =>
11 {
12 stringmark = s. Value. Substring (1), paramName = string. Empty;
13 if (this. dicMark. ContainsKey (mark ))
14 {
15 paramName = this. ExistMark (mark );
16}
17 else
18 {
19 if (columns. ContainsKey (mark ))
20 {
21 paramName = this. NotExistMark (mark, columns [mark]);
22}
23 else
24 {
25 thrownewNullReferenceException ("the corresponding tag key value does not exist! ");
26}
27}
28 returnparamName;
29 });
30 // returns the DbParameter Array
31}
32
33 // <summary>
34 // There are tag Parameters
35 /// </summary>
36 // <param name = "mark"> mark </param>
37 // <returns> </returns>
38 stringExistMark (stringmark)
39 {
40 IDictionary <string, T> dicParam = this. dicMark [mark];
41 stringparamName = string. Empty;
42 if (dicParam. Count> 1)
43 {
44 paramName = string. Join (",", dicParam. Keys. ToArray ());
45}
46 else
47 {
48 paramName = dicParam. First (). Key;
49}
50 returnparamName;
51}
52
53 /// <summary>
54 // The tag parameter does not exist
55 /// </summary>
56 // <param name = "mark"> mark </param>
57 // <param name = "value"> parameter value </param>
58 /// <returns> </returns>
59 stringNotExistMark (stringmark, objectvalue)
60 {
61 stringparamName = string. Empty;
62 IDictionary <string, DbParameter> dicParam = newDictionary <string, DbParameter> ();
63 if (value. GetType (). IsArray)
64 {
65 IList values = value asIList;
66 intlength = values. Count;
67 string [] names = newstring [length];
68 for (inti = 0; I <length; I ++)
69 {
70 names [I] = this. GetParamName ();
71 dicParam. Add (names [I], this. CreateDbParam (names [I], values [I]);
72}
73 paramName = string. Join (",", names );
74}
75 else
76 {
77 paramName = this. GetParamName ();
78 dicParam. Add (paramName, this. CreateDbParam (paramName, value ));
79}
80 dicMark. Add (mark, dicParam );
81 returnparamName;
82}
83
84 /// <summary>
85 // create Db Parameters
86 // </summary>
87 // <param name = "paramName"> parameter name </param>
88 // <param name = "value"> parameter value </param>
89 // <returns> </returns>
90 DbParameter CreateDbParam (stringparamName, objectvalue)
91 {
92 // factory mode. Different DbParameter Derived classes are created based on the corresponding parameters.
93}
94
95 /// <summary>
96 // get the parameter name
97 /// </summary>
98 /// <returns> </returns>
99 stringGetParamName ()
100 {
101 return "@ Param" + index ++;
102}

Most of the problems have been solved here, but it is quite troublesome to create the corresponding DbParameter derived class through the input parameters every time, I chose to use generics to solve this problem.

1 publicclassMarkParameter <T> whereT: DbParameter, new ()

In this way, you can use T to represent the derived class of DbParameter, so you need to modify some methods.

The Code is as follows:

1 /// <summary>
2 // mark the dictionary
3 /// </summary>
4 IDictionary <string, IDictionary <string, T> dicMark = newDictionary <string, IDictionary <string, T> ();
5
6
7 /// <summary>
8 // conversion mark
9 /// </summary>
10 /// <param name = "SQL"> SQL statement </param>
11 /// <param name = "columns"> tag value Hashtable </param>
12 /// <returns> </returns>
13 publicT [] CastMark (refstringsql, Hashtable columns)
14 {
15 // Code omitted
16}
17
18 /// <summary>
19 // create Db Parameters
20 /// </summary>
21 /// <param name = "paramName"> parameter name </param>
22 // <param name = "value"> parameter value </param>
23 // <returns> </returns>
24 T CreateDbParam (stringparamName, objectvalue)
25 {
26 T param = newT ();
27 param. ParameterName = paramName;
28 param. Value = value;
29 returnparam;
30}

At this point, we have solved some major problems and replaced our SQL parameters.

Author: "ahl5esoft upgrade record"