|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3 using System.Diagnostics;
|
|
|
4 using System.Globalization;
|
|
|
5 using System.Text.RegularExpressions;
|
|
|
6
|
|
|
7 namespace BLToolkit.Data.DataProvider
|
|
|
8 {
|
|
|
9 public static class OracleHelper
|
|
|
10 {
|
|
|
11 #region Text
|
|
|
12
|
|
|
13 /// <summary>
|
|
|
14 /// If value is null or empty, return NULL or the value converted for Oracle SQL query
|
|
|
15 /// </summary>
|
|
|
16 /// <param name="value">Text</param>
|
|
|
17 /// <returns>Text converted for oracle query</returns>
|
|
|
18 public static string SqlConvertString(string value)
|
|
|
19 {
|
|
|
20 if (!string.IsNullOrEmpty(value))
|
|
|
21 {
|
|
|
22 value = value.Replace("'", "''");
|
|
|
23 value = value.Replace("&", "' || '&' || '");
|
|
|
24
|
|
|
25 return "'" + value + "'";
|
|
|
26 }
|
|
|
27
|
|
|
28 return string.IsNullOrWhiteSpace(value) ? "NULL" : value;
|
|
|
29 }
|
|
|
30
|
|
|
31 #endregion
|
|
|
32
|
|
|
33 #region Date & Time
|
|
|
34
|
|
|
35 /// <summary>
|
|
|
36 /// Convert DateTime to TO_DATE('value','YYYYMMDD')
|
|
|
37 /// </summary>
|
|
|
38 /// <param name="value">Date</param>
|
|
|
39 /// <returns>Date converted for oracle query</returns>
|
|
|
40 public static string SqlConvertDate(DateTime value)
|
|
|
41 {
|
|
|
42 return string.Format("TO_DATE('{0}','YYYYMMDD')", value.ToString("yyyyMMdd"));
|
|
|
43 }
|
|
|
44
|
|
|
45 /// <summary>
|
|
|
46 /// Convert DateTime to TO_DATE('value','YYYYMMDDHH24MISS')
|
|
|
47 /// </summary>
|
|
|
48 /// <param name="value">DateTime</param>
|
|
|
49 /// <returns>DateTime converted for oracle query</returns>
|
|
|
50 public static string SqlConvertDateTime(DateTime value)
|
|
|
51 {
|
|
|
52 return string.Format("TO_DATE('{0}','YYYYMMDDHH24MISS')", value.ToString("yyyyMMddHHmmss"));
|
|
|
53 }
|
|
|
54
|
|
|
55 /// <summary>
|
|
|
56 /// Convert DateTime to TO_TIMESTAMP('value','YYYYMMDDHH24MISSFF3')
|
|
|
57 /// </summary>
|
|
|
58 /// <param name="value">DateTime</param>
|
|
|
59 /// <returns>DateTime converted for oracle query</returns>
|
|
|
60 public static string SqlConvertTimeStamp(DateTime value)
|
|
|
61 {
|
|
|
62 return string.Format("TO_TIMESTAMP('{0}','YYYYMMDDHH24MISSFF3')", value.ToString("yyyyMMddHHmmssfff"));
|
|
|
63 }
|
|
|
64
|
|
|
65 /// <summary>
|
|
|
66 /// Convert DateTime to TO_CHAR(TO_DATE('value','YYYYMMDD')))
|
|
|
67 /// </summary>
|
|
|
68 /// <param name="value">DateTime</param>
|
|
|
69 /// <returns>DateTime converted for oracle query</returns>
|
|
|
70 public static string SqlConvertDateToChar(DateTime value)
|
|
|
71 {
|
|
|
72 return string.Format("TO_CHAR(TO_DATE('{0}','YYYYMMDD'))", value.ToString("yyyyMMdd"));
|
|
|
73 }
|
|
|
74
|
|
|
75 #endregion
|
|
|
76
|
|
|
77 #region Connection string
|
|
|
78
|
|
|
79 /// <summary>
|
|
|
80 /// Generate the minimum connection string. The connection string looks like
|
|
|
81 /// Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = port)))(CONNECT_DATA = (SID = sid)));User Id=username;Password=password;
|
|
|
82 /// </summary>
|
|
|
83 /// <param name="userName">Username</param>
|
|
|
84 /// <param name="password">Password</param>
|
|
|
85 /// <param name="server">Server name</param>
|
|
|
86 /// <param name="sid">Database SID</param>
|
|
|
87 /// <param name="port">Port of the server. Default value is 1521</param>
|
|
|
88 /// <returns>Default connection string</returns>
|
|
|
89 public static string GetFullConnectionString(string userName, string password, string server, string sid, int port = 1521)
|
|
|
90 {
|
|
|
91 return
|
|
|
92 string.Format(
|
|
|
93 "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1})))(CONNECT_DATA = (SID = {2})));User Id={3};Password={4};",
|
|
|
94 server, port, sid, userName, password);
|
|
|
95 }
|
|
|
96
|
|
|
97 /// <summary>
|
|
|
98 /// Generate the minimum connection string. The connection string looks like
|
|
|
99 /// Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = port)))(CONNECT_DATA = (SID = sid)));User Id=username;Password=password;Pooling=False;
|
|
|
100 /// </summary>
|
|
|
101 /// <param name="userName">Username</param>
|
|
|
102 /// <param name="password">Password</param>
|
|
|
103 /// <param name="server">Server name</param>
|
|
|
104 /// <param name="sid">Database SID</param>
|
|
|
105 /// <param name="port">Port of the server. Default value is 1521</param>
|
|
|
106 /// <returns>Default connection string</returns>
|
|
|
107 public static string GetFullConnectionStringWithoutPooling(string userName, string password, string server, string sid, int port = 1521)
|
|
|
108 {
|
|
|
109 return
|
|
|
110 string.Format(
|
|
|
111 "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1})))(CONNECT_DATA = (SID = {2})));User Id={3};Password={4};Pooling=False;",
|
|
|
112 server, port, sid, userName, password);
|
|
|
113 }
|
|
|
114
|
|
|
115 /// <summary>
|
|
|
116 /// Generate the minimum connection string. The connection string looks like
|
|
|
117 /// Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = port)))(CONNECT_DATA = (SID = sid)));User Id=username;Password=password;Connection Timeout=timeout;
|
|
|
118 /// </summary>
|
|
|
119 /// <param name="userName">Username</param>
|
|
|
120 /// <param name="password">Password</param>
|
|
|
121 /// <param name="server">Server name</param>
|
|
|
122 /// <param name="sid">Database SID</param>
|
|
|
123 /// <param name="port">Port of the server. Default value is 1521</param>
|
|
|
124 /// <returns>Default connection string</returns>
|
|
|
125 public static string GetFullConnectionString(string userName, string password, string server, string sid,
|
|
|
126 TimeSpan timeOut, int port = 1521)
|
|
|
127 {
|
|
|
128 return
|
|
|
129 string.Format(
|
|
|
130 "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1})))(CONNECT_DATA = (SID = {2})));User Id={3};Password={4};Connection Timeout={5};",
|
|
|
131 server, port, sid, userName, password, (int)timeOut.TotalSeconds);
|
|
|
132 }
|
|
|
133
|
|
|
134 /// <summary>
|
|
|
135 /// Generate the minimum connection string. The connection string looks like
|
|
|
136 /// Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = port)))(CONNECT_DATA = (SID = sid)));User Id=username;Password=password;Connection Timeout=timeout;Pooling=False;
|
|
|
137 /// </summary>
|
|
|
138 /// <param name="userName">Username</param>
|
|
|
139 /// <param name="password">Password</param>
|
|
|
140 /// <param name="server">Server name</param>
|
|
|
141 /// <param name="sid">Database SID</param>
|
|
|
142 /// <param name="port">Port of the server. Default value is 1521</param>
|
|
|
143 /// <returns>Default connection string</returns>
|
|
|
144 public static string GetFullConnectionStringWithoutPooling(string userName, string password, string server, string sid,
|
|
|
145 TimeSpan timeOut, int port = 1521)
|
|
|
146 {
|
|
|
147 return
|
|
|
148 string.Format(
|
|
|
149 "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1})))(CONNECT_DATA = (SID = {2})));User Id={3};Password={4};Connection Timeout={5};Pooling=False;",
|
|
|
150 server, port, sid, userName, password, (int)timeOut.TotalSeconds);
|
|
|
151 }
|
|
|
152
|
|
|
153 /// <summary>
|
|
|
154 /// Generate the minimum connection string. The connection string looks like
|
|
|
155 /// Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = port)))(CONNECT_DATA = (SID = sid)));User Id=username;Password=password;Connection Timeout=timeout;
|
|
|
156 /// </summary>
|
|
|
157 /// <param name="userName">Username</param>
|
|
|
158 /// <param name="password">Password</param>
|
|
|
159 /// <param name="server">Server name</param>
|
|
|
160 /// <param name="sid">Database SID</param>
|
|
|
161 /// <param name="port">Port of the server. Default value is 1521</param>
|
|
|
162 /// <returns>Default connection string</returns>
|
|
|
163 public static string GetFullConnectionString(string userName, string password, string server, string sid, int timeOutInSecond, int port = 1521)
|
|
|
164 {
|
|
|
165 return GetFullConnectionString(userName, password, server, sid, TimeSpan.FromSeconds(timeOutInSecond), port);
|
|
|
166 }
|
|
|
167
|
|
|
168 #endregion
|
|
|
169
|
|
|
170 public static string Interpret(IDbCommand poCommand)
|
|
|
171 {
|
|
|
172 if (poCommand.Parameters.Count == 0)
|
|
|
173 return poCommand.CommandText;
|
|
|
174
|
|
|
175 var oRegex = new Regex(@"(?<string>'[^']+')|(?<Parameters>:[a-zA-Z0-9_]+)");
|
|
|
176 MatchCollection oMatchCollection = oRegex.Matches(poCommand.CommandText);
|
|
|
177
|
|
|
178 string strQuery = poCommand.CommandText + " ";
|
|
|
179 int matchCount = 0;
|
|
|
180
|
|
|
181 for (int i = 0; i < oMatchCollection.Count; i++)
|
|
|
182 {
|
|
|
183 if (oMatchCollection[i].Groups["string"].Success)
|
|
|
184 continue;
|
|
|
185
|
|
|
186 string strParameter = oMatchCollection[i].Groups["Parameters"].Captures[0].Value;
|
|
|
187
|
|
|
188 var param = (IDbDataParameter)poCommand.Parameters[matchCount];
|
|
|
189 if (param.Value is DateTime)
|
|
|
190 {
|
|
|
191 var dt = (DateTime)param.Value;
|
|
|
192
|
|
|
193 strQuery = strQuery.Replace(strParameter + " ",
|
|
|
194 dt.Date == dt
|
|
|
195 ? SqlConvertDate(dt) + " "
|
|
|
196 : SqlConvertDateTime(dt) + " ");
|
|
|
197 }
|
|
|
198 else if (param.Value is string)
|
|
|
199 strQuery = strQuery.Replace(strParameter, SqlConvertString(param.Value.ToString()) + " ");
|
|
|
200 else if (param.Value is Int16)
|
|
|
201 strQuery = strQuery.Replace(strParameter, ((Int16)param.Value).ToString(CultureInfo.InvariantCulture) + " ");
|
|
|
202 else if (param.Value is Int32)
|
|
|
203 strQuery = strQuery.Replace(strParameter, ((Int32)param.Value).ToString(CultureInfo.InvariantCulture) + " ");
|
|
|
204 else if (param.Value is Int64)
|
|
|
205 strQuery = strQuery.Replace(strParameter, ((Int64)param.Value).ToString(CultureInfo.InvariantCulture) + " ");
|
|
|
206 else if (param.Value is decimal)
|
|
|
207 strQuery = strQuery.Replace(strParameter, ((decimal)param.Value).ToString(CultureInfo.InvariantCulture) + " ");
|
|
|
208 else if (param.Value is float)
|
|
|
209 strQuery = strQuery.Replace(strParameter, ((float)param.Value).ToString(CultureInfo.InvariantCulture) + " ");
|
|
|
210 else if (param.Value is double)
|
|
|
211 strQuery = strQuery.Replace(strParameter, ((double)param.Value).ToString(CultureInfo.InvariantCulture) + " ");
|
|
|
212 else if (param.Value is TimeSpan)
|
|
|
213 strQuery = strQuery.Replace(strParameter, "'" + ((TimeSpan)param.Value).ToString() + "' ");
|
|
|
214 else
|
|
|
215 throw new NotImplementedException(param.Value.GetType() + " is not implemented yet.");
|
|
|
216
|
|
|
217 matchCount++;
|
|
|
218 }
|
|
|
219
|
|
|
220 if (matchCount != poCommand.Parameters.Count)
|
|
|
221 {
|
|
|
222 // ReSharper disable InvocationIsSkipped
|
|
|
223 Debug.WriteLine(
|
|
|
224 "Number of parameters in query is not equals to number of parameters set in the command object " +
|
|
|
225 poCommand.CommandText);
|
|
|
226 // ReSharper restore InvocationIsSkipped
|
|
|
227 var msg =
|
|
|
228 "Number of parameters in query is not equals to number of parameters set in the command object : " + poCommand.CommandText + "\r\n" +
|
|
|
229 "Query params :\r\n";
|
|
|
230
|
|
|
231 foreach (Match match in oMatchCollection)
|
|
|
232 {
|
|
|
233 msg += "\t" + match.Value + "\r\n";
|
|
|
234 }
|
|
|
235
|
|
|
236 msg += "\nCommand params :\r\n";
|
|
|
237
|
|
|
238 foreach (IDataParameter param in poCommand.Parameters)
|
|
|
239 {
|
|
|
240 msg += "\t" + param.ParameterName + " = " + Convert.ToString(param) + "\r\n";
|
|
|
241 }
|
|
|
242
|
|
|
243 throw new Exception(msg);
|
|
|
244 }
|
|
|
245
|
|
|
246 return strQuery;
|
|
|
247 }
|
|
|
248
|
|
|
249 }
|
|
|
250 } |
