|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3 using System.Data.OleDb;
|
|
|
4 using System.Text.RegularExpressions;
|
|
|
5
|
|
|
6 namespace BLToolkit.Data.DataProvider
|
|
|
7 {
|
|
|
8 using Mapping;
|
|
|
9 using Sql.SqlProvider;
|
|
|
10
|
|
|
11 public class AccessDataProvider : OleDbDataProvider
|
|
|
12 {
|
|
|
13 private static Regex _paramsExp;
|
|
|
14
|
|
|
15 // Based on idea from http://qapi.blogspot.com/2006/12/deriveparameters-oledbprovider-ii.html
|
|
|
16 //
|
|
|
17 public override bool DeriveParameters(IDbCommand command)
|
|
|
18 {
|
|
|
19 if (command == null)
|
|
|
20 throw new ArgumentNullException("command");
|
|
|
21
|
|
|
22 if (command.CommandType != CommandType.StoredProcedure)
|
|
|
23 throw new InvalidOperationException("command.CommandType must be CommandType.StoredProcedure");
|
|
|
24
|
|
|
25 var conn = command.Connection as OleDbConnection;
|
|
|
26
|
|
|
27 if (conn == null || conn.State != ConnectionState.Open)
|
|
|
28 throw new InvalidOperationException("Invalid connection state.");
|
|
|
29
|
|
|
30 command.Parameters.Clear();
|
|
|
31
|
|
|
32 var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, new object[]{null, null, command.CommandText});
|
|
|
33
|
|
|
34 if (dt.Rows.Count == 0)
|
|
|
35 {
|
|
|
36 // Jet does convert parameretless procedures to views.
|
|
|
37 //
|
|
|
38 dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Views, new object[]{null, null, command.CommandText});
|
|
|
39
|
|
|
40 if (dt.Rows.Count == 0)
|
|
|
41 throw new DataException(string.Format("Stored procedure '{0}' not found", command.CommandText));
|
|
|
42
|
|
|
43 // Do nothing. There is no parameters.
|
|
|
44 //
|
|
|
45 }
|
|
|
46 else
|
|
|
47 {
|
|
|
48 var col = dt.Columns["PROCEDURE_DEFINITION"];
|
|
|
49
|
|
|
50 if (col == null)
|
|
|
51 {
|
|
|
52 // Not really possible
|
|
|
53 //
|
|
|
54 return false;
|
|
|
55 }
|
|
|
56
|
|
|
57 if (_paramsExp == null)
|
|
|
58 _paramsExp = new Regex(@"PARAMETERS ((\[(?<name>[^\]]+)\]|(?<name>[^\s]+))\s(?<type>[^,;\s]+(\s\([^\)]+\))?)[,;]\s)*", RegexOptions.Compiled | RegexOptions.ExplicitCapture);
|
|
|
59
|
|
|
60 var match = _paramsExp.Match((string)dt.Rows[0][col.Ordinal]);
|
|
|
61 var names = match.Groups["name"].Captures;
|
|
|
62 var types = match.Groups["type"].Captures;
|
|
|
63
|
|
|
64 if (names.Count != types.Count)
|
|
|
65 {
|
|
|
66 // Not really possible
|
|
|
67 //
|
|
|
68 return false;
|
|
|
69 }
|
|
|
70
|
|
|
71 var separators = new[] {' ', '(', ',', ')'};
|
|
|
72
|
|
|
73 for (var i = 0; i < names.Count; ++i)
|
|
|
74 {
|
|
|
75 var paramName = names[i].Value;
|
|
|
76 var rawType = types[i].Value.Split(separators, StringSplitOptions.RemoveEmptyEntries);
|
|
|
77 var p = new OleDbParameter(paramName, GetOleDbType(rawType[0]));
|
|
|
78
|
|
|
79 if (rawType.Length > 2)
|
|
|
80 {
|
|
|
81 p.Precision = Common.Convert.ToByte(rawType[1]);
|
|
|
82 p.Scale = Common.Convert.ToByte(rawType[2]);
|
|
|
83 }
|
|
|
84 else if (rawType.Length > 1)
|
|
|
85 {
|
|
|
86 p.Size = Common.Convert.ToInt32(rawType[1]);
|
|
|
87 }
|
|
|
88
|
|
|
89 command.Parameters.Add(p);
|
|
|
90 }
|
|
|
91 }
|
|
|
92
|
|
|
93 return true;
|
|
|
94 }
|
|
|
95
|
|
|
96 private static OleDbType GetOleDbType(string jetType)
|
|
|
97 {
|
|
|
98 switch (jetType.ToLower())
|
|
|
99 {
|
|
|
100 case "byte":
|
|
|
101 case "tinyint":
|
|
|
102 case "integer1":
|
|
|
103 return OleDbType.TinyInt;
|
|
|
104
|
|
|
105 case "short":
|
|
|
106 case "smallint":
|
|
|
107 case "integer2":
|
|
|
108 return OleDbType.SmallInt;
|
|
|
109
|
|
|
110 case "int":
|
|
|
111 case "integer":
|
|
|
112 case "long":
|
|
|
113 case "integer4":
|
|
|
114 case "counter":
|
|
|
115 case "identity":
|
|
|
116 case "autoincrement":
|
|
|
117 return OleDbType.Integer;
|
|
|
118
|
|
|
119 case "single":
|
|
|
120 case "real":
|
|
|
121 case "float4":
|
|
|
122 case "ieeesingle":
|
|
|
123 return OleDbType.Single;
|
|
|
124
|
|
|
125
|
|
|
126 case "double":
|
|
|
127 case "number":
|
|
|
128 case "double precision":
|
|
|
129 case "float":
|
|
|
130 case "float8":
|
|
|
131 case "ieeedouble":
|
|
|
132 return OleDbType.Double;
|
|
|
133
|
|
|
134 case "currency":
|
|
|
135 case "money":
|
|
|
136 return OleDbType.Currency;
|
|
|
137
|
|
|
138 case "dec":
|
|
|
139 case "decimal":
|
|
|
140 case "numeric":
|
|
|
141 return OleDbType.Decimal;
|
|
|
142
|
|
|
143 case "bit":
|
|
|
144 case "yesno":
|
|
|
145 case "logical":
|
|
|
146 case "logical1":
|
|
|
147 return OleDbType.Boolean;
|
|
|
148
|
|
|
149 case "datetime":
|
|
|
150 case "date":
|
|
|
151 case "time":
|
|
|
152 return OleDbType.Date;
|
|
|
153
|
|
|
154 case "alphanumeric":
|
|
|
155 case "char":
|
|
|
156 case "character":
|
|
|
157 case "character varying":
|
|
|
158 case "national char":
|
|
|
159 case "national char varying":
|
|
|
160 case "national character":
|
|
|
161 case "national character varying":
|
|
|
162 case "nchar":
|
|
|
163 case "string":
|
|
|
164 case "text":
|
|
|
165 case "varchar":
|
|
|
166 return OleDbType.VarWChar;
|
|
|
167
|
|
|
168 case "longchar":
|
|
|
169 case "longtext":
|
|
|
170 case "memo":
|
|
|
171 case "note":
|
|
|
172 case "ntext":
|
|
|
173 return OleDbType.LongVarWChar;
|
|
|
174
|
|
|
175 case "binary":
|
|
|
176 case "varbinary":
|
|
|
177 case "binary varying":
|
|
|
178 case "bit varying":
|
|
|
179 return OleDbType.VarBinary;
|
|
|
180
|
|
|
181 case "longbinary":
|
|
|
182 case "image":
|
|
|
183 case "general":
|
|
|
184 case "oleobject":
|
|
|
185 return OleDbType.LongVarBinary;
|
|
|
186
|
|
|
187 case "guid":
|
|
|
188 case "uniqueidentifier":
|
|
|
189 return OleDbType.Guid;
|
|
|
190
|
|
|
191 default:
|
|
|
192 // Each release of Jet brings many new aliases to existing types.
|
|
|
193 // This list may be outdated, please send a report to us.
|
|
|
194 //
|
|
|
195 throw new NotSupportedException("Unknown DB type '" + jetType + "'");
|
|
|
196 }
|
|
|
197 }
|
|
|
198
|
|
|
199 public override void AttachParameter(IDbCommand command, IDbDataParameter parameter)
|
|
|
200 {
|
|
|
201 // Do some magic to workaround 'Data type mismatch in criteria expression' error
|
|
|
202 // in JET for some european locales.
|
|
|
203 //
|
|
|
204 if (parameter.Value is DateTime)
|
|
|
205 {
|
|
|
206 // OleDbType.DBTimeStamp is locale aware, OleDbType.Date is locale neutral.
|
|
|
207 //
|
|
|
208 ((OleDbParameter)parameter).OleDbType = OleDbType.Date;
|
|
|
209 }
|
|
|
210 else if (parameter.Value is decimal)
|
|
|
211 {
|
|
|
212 // OleDbType.Decimal is locale aware, OleDbType.Currency is locale neutral.
|
|
|
213 //
|
|
|
214 ((OleDbParameter)parameter).OleDbType = OleDbType.Currency;
|
|
|
215 }
|
|
|
216
|
|
|
217 base.AttachParameter(command, parameter);
|
|
|
218 }
|
|
|
219
|
|
|
220 public new const string NameString = DataProvider.ProviderName.Access;
|
|
|
221
|
|
|
222 public override string Name
|
|
|
223 {
|
|
|
224 get { return NameString; }
|
|
|
225 }
|
|
|
226
|
|
|
227 public override int MaxBatchSize
|
|
|
228 {
|
|
|
229 get { return 0; }
|
|
|
230 }
|
|
|
231
|
|
|
232 public override ISqlProvider CreateSqlProvider()
|
|
|
233 {
|
|
|
234 return new AccessSqlProvider();
|
|
|
235 }
|
|
|
236
|
|
|
237 public override object Convert(object value, ConvertType convertType)
|
|
|
238 {
|
|
|
239 switch (convertType)
|
|
|
240 {
|
|
|
241 case ConvertType.ExceptionToErrorNumber:
|
|
|
242 if (value is OleDbException)
|
|
|
243 {
|
|
|
244 var ex = (OleDbException)value;
|
|
|
245 if (ex.Errors.Count > 0)
|
|
|
246 return ex.Errors[0].NativeError;
|
|
|
247 }
|
|
|
248
|
|
|
249 break;
|
|
|
250 }
|
|
|
251
|
|
|
252 return SqlProvider.Convert(value, convertType);
|
|
|
253 }
|
|
|
254
|
|
|
255 #region DataReaderEx
|
|
|
256
|
|
|
257 public override IDataReader GetDataReader(MappingSchema schema, IDataReader dataReader)
|
|
|
258 {
|
|
|
259 return dataReader is OleDbDataReader?
|
|
|
260 new DataReaderEx((OleDbDataReader)dataReader):
|
|
|
261 base.GetDataReader(schema, dataReader);
|
|
|
262 }
|
|
|
263
|
|
|
264 class DataReaderEx : DataReaderBase<OleDbDataReader>, IDataReader
|
|
|
265 {
|
|
|
266 public DataReaderEx(OleDbDataReader rd): base(rd)
|
|
|
267 {
|
|
|
268 }
|
|
|
269
|
|
|
270 public new object GetValue(int i)
|
|
|
271 {
|
|
|
272 var value = DataReader.GetValue(i);
|
|
|
273
|
|
|
274 if (value is DateTime)
|
|
|
275 {
|
|
|
276 var dt = (DateTime)value;
|
|
|
277
|
|
|
278 if (dt.Year == 1899 && dt.Month == 12 && dt.Day == 30)
|
|
|
279 return new DateTime(1, 1, 1, dt.Hour, dt.Minute, dt.Second, dt.Millisecond);
|
|
|
280 }
|
|
|
281
|
|
|
282 return value;
|
|
|
283 }
|
|
|
284
|
|
|
285 public new DateTime GetDateTime(int i)
|
|
|
286 {
|
|
|
287 var dt = DataReader.GetDateTime(i);
|
|
|
288
|
|
|
289 if (dt.Year == 1899 && dt.Month == 12 && dt.Day == 30)
|
|
|
290 return new DateTime(1, 1, 1, dt.Hour, dt.Minute, dt.Second, dt.Millisecond);
|
|
|
291
|
|
|
292 return dt;
|
|
|
293 }
|
|
|
294 }
|
|
|
295
|
|
|
296 #endregion
|
|
|
297 }
|
|
|
298 }
|
