Mercurial > pub > bltoolkit

comparison Source/Data/DataProvider/OracleHelper.cs @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
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 }