source: BMXNET_RPMS_dotNET_UTILITIES-BMX/branch/IHS BMX Framework/IndianHealthService.BMXNet/Ado/RPMSDb.cs@ 1146

Last change on this file since 1146 was 1146, checked in by Sam Habiel, 13 years ago

Initial Import of BMX4
File size: 14.8 KB
Line 
1using System;
2using System.Data;
3using System.Text;
4using System.Diagnostics;
5using System.Globalization;
6
7namespace IndianHealthService.BMXNet.Ado
8{
9 /*
10 * This class executes a query or RPC against the connected
11 * RPMS database and formats the return value as a RPMSDbResultSet
12 */
13 internal class RPMSDb
14 {
15 private string m_sSelectCmd = "SELECT ";
16 private string m_sUpdateCmd = "UPDATE ";
17 private string m_sCmd = "";
18
19 private BMXNetSessionConnection m_sessionConnection;
20
21 public BMXNetSessionConnection SessionConnection
22 {
23 get { return m_sessionConnection; }
24 set { m_sessionConnection = value; }
25 }
26
27 private string m_sQueryType = "";
28 private RPMSDbResultSet[] m_aResultSets = null;
29 private int m_nCurrentSet = 0;
30
31 public class RPMSDbResultSet
32 {
33 public struct MetaData
34 {
35 public string name;
36 public Type type;
37 public int maxSize;
38
39 public string fmFieldID;
40 public bool fmReadOnly;
41 public bool fmKeyField;
42 }
43
44 public int recordsAffected;
45 public MetaData[] metaData;
46 public object[,] data;
47
48 public string fmKeyField;
49 public string fmFileID;
50 public string fmForeignKey = "";
51 public int fmSeed = 0;
52 public string fmParentPath = "";
53 }
54
55 //Constructor
56 public RPMSDb(BMXNetSessionConnection aConnection)
57 {
58 this.SessionConnection = aConnection;
59 }
60
61 public void Execute(string sCmd, out RPMSDbResultSet resultset)
62 {
63 /*
64 * SELECT, UPDATE and RPC operations.
65 */
66 if (0 == String.Compare(sCmd, 0, m_sSelectCmd, 0, m_sSelectCmd.Length, true))
67 {
68 m_sCmd = sCmd;
69 m_sQueryType = "SELECT";
70 _executeSelect(out resultset);
71 }
72 else if (0 == String.Compare(sCmd, 0, m_sUpdateCmd, 0, m_sUpdateCmd.Length, true))
73 {
74 m_sCmd = sCmd;
75 m_sQueryType = "UPDATE";
76 _executeUpdate(out resultset);
77 }
78 else //RPC Call
79 {
80 m_sQueryType = "RPC";
81 m_sCmd = sCmd;
82 _executeSelect(out resultset);
83 }
84 }
85
86 private void _executeSelect(out RPMSDbResultSet resultset)
87 {
88 if (m_nCurrentSet == 0)
89 _resultsetCreate();
90
91 // Return the current resultset.
92 resultset = m_aResultSets[m_nCurrentSet];
93 }
94
95 private void _executeUpdate(out RPMSDbResultSet resultset)
96 {
97 int nStart = 7;
98 m_sCmd = m_sCmd.Substring(nStart);
99
100 String result=this.SessionConnection.TransmitRPC("BMX UPDATE", m_sCmd);
101
102 this.AssertValidResult(result);
103
104 resultset = new RPMSDbResultSet();
105 resultset.recordsAffected = 1; //Make this the return value of the call
106 }
107
108 protected void AssertValidResult(String aResult)
109 {
110 string[] resultPeices = aResult.Split(new char[] { '|' });
111
112 if (resultPeices.Length >= 1)
113 {
114 if (!("OK".Equals(resultPeices[0]) || "Record deleted".Equals(resultPeices[0])))
115 {
116 throw new BMXNetException(aResult);
117 }
118 }
119 }
120
121 private string CallRPX()
122 {
123 //Make rpx call
124 string sRPC;
125 string sParam;
126
127 if (m_sQueryType == "SELECT")
128 {
129 sRPC = "BMX SQL";
130 sParam = m_sCmd;
131 }
132 else //it's an RPC (stored procedure)
133 {
134 sRPC = BMXNetBroker.Piece(m_sCmd, "^", 1);
135 sParam = BMXNetBroker.Piece(m_sCmd, "^", 2 , 99);
136 }
137
138 return this.SessionConnection.TransmitRPC(sRPC, sParam);
139 }
140
141 private void ProcessHeader(string sHeader, int nSet, out int numCols)
142 {
143 //Modifies m_aResultSet[nSet]
144 string sFldDelim = "^";
145 char[] cFldDelim = sFldDelim.ToCharArray();
146 string sZero = "0";
147 char[] cZero = sZero.ToCharArray();
148 string sBar = "|";
149 char[] cBar = sBar.ToCharArray();
150
151 int j;
152 if (sHeader.StartsWith("@@@meta@@@") == true)
153 {
154 //New style -- ColumnHeaderInfo in [1]
155 //Parse out data location or update logic here
156 //RecordInfo ^ Column1 info ^ column2 info ^ ... ^ column n info
157 string sType;
158 string sMaxSize;
159 string sFieldName;
160 string sReadOnly;
161 string sKeyField;
162 string sFileID;
163 string sSeed = "";
164 int nMaxSize;
165 Type tType;
166
167 int nTemp = 10;
168 sHeader = sHeader.Substring(10,(sHeader.Length - nTemp));
169 string[] sRecordSetInfo = sHeader.Split(cFldDelim);
170
171 numCols = sRecordSetInfo.GetLength(0)-1;
172 m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];
173
174 //First ^-Piece is recordset-level info: RecordIdentifier|File#
175 string[] sRecordInfo = sRecordSetInfo[0].Split(cBar);
176 m_aResultSets[nSet].fmFileID = sRecordInfo[1];
177
178 String parentPath = "";
179
180 if (sRecordInfo.GetLength(0) > 2)
181 {
182 String parentAndSeedInfo = sRecordInfo[2];
183 String seedText = parentAndSeedInfo;
184 int lastCommaIndex = parentAndSeedInfo.LastIndexOf(',');
185 if (lastCommaIndex != -1)
186 {
187 seedText = parentAndSeedInfo.Substring(lastCommaIndex + 1, seedText.Length - lastCommaIndex - 1);
188 parentPath = parentAndSeedInfo.Substring(0, lastCommaIndex);
189 }
190
191
192 //sSeed = sRecordInfo[2];
193 sSeed = (seedText == "")?"0":seedText;
194 try
195 {
196 m_aResultSets[nSet].fmSeed = Convert.ToInt32(sSeed);
197 m_aResultSets[nSet].fmParentPath= parentPath;
198 }
199 catch (Exception ex)
200 {
201 Debug.Assert(false, "BMXNet.RMSPDb: 'Seed' value failed to convert to integer.");
202 Debug.Write(ex.Message + "\n");
203 }
204 }
205
206 if (sRecordInfo.GetLength(0) > 3)
207 {
208 m_aResultSets[nSet].fmForeignKey = sRecordInfo[4];
209
210 }
211
212 m_aResultSets[nSet].fmKeyField = "";
213 //2nd through nth ^-Pieces are Column info: Fileman File#FileMan Field#|DataType|Field Length|Column Name|IsReadOnly|IsKeyField
214 for (j=1; j < sRecordSetInfo.GetLength(0); j++)
215 {
216 string[] sColumnInfo = sRecordSetInfo[j].Split(cBar);
217 //Field 0 = FileID
218 //Field 1 = FieldID
219 //Field 2 = DataType
220 //Field 3 = Length
221 //Field 4 = ColumnName
222 //Field 5 = IsReadOnly
223 //Field 6 = IsKeyField
224 sFileID = sColumnInfo[0];
225 string sFieldID = sColumnInfo[1];
226
227
228 switch (sFieldID)
229 {
230 case ".001":
231 m_aResultSets[nSet].fmKeyField += "," + sColumnInfo[4];
232 break;
233 case ".0001":
234 m_aResultSets[nSet].fmKeyField = sColumnInfo[4];
235 break;
236 default:
237 break;
238 }
239
240 sType = sColumnInfo[2];
241 switch (sType)
242 {
243 case "D":
244 tType = typeof(DateTime);
245 break;
246 case "I":
247 tType = typeof(Int32);
248 break;
249 case "N":
250 tType = typeof(System.Double);
251 break;
252 default:
253 tType = typeof(String);
254 break;
255 }
256 sMaxSize = sColumnInfo[3];
257 nMaxSize = Convert.ToInt32(sMaxSize);
258 sFieldName = sColumnInfo[4];
259
260 if (m_aResultSets[nSet].fmForeignKey == sFieldID)
261 {
262 m_aResultSets[nSet].fmForeignKey = sFieldName;
263 }
264
265 sReadOnly = sColumnInfo[5];
266 sKeyField = sColumnInfo[6];
267 _resultsetFillColumn(nSet, j-1, sFieldName, tType, nMaxSize, sFieldID, sReadOnly, sKeyField);
268 }
269 }
270 else
271 {
272 //Old style column header info
273
274 string sField;
275 string sType;
276 string sMaxSize;
277 string sFieldName;
278 int nMaxSize;
279 Type tType;
280
281 string[] saHeader = sHeader.Split(cFldDelim);
282 numCols = saHeader.GetLength(0);
283 m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];
284 for (j=0; j < numCols; j++)
285 {
286 sField = saHeader[j];
287 sType = sField.Substring(0,1);
288 if (sType == "D")
289 {
290 tType = typeof(DateTime);
291 }
292 else if(sType == "I")
293 {
294 tType = typeof(Int32);
295 }
296 else if(sType == "N")
297 {
298 tType = typeof(System.Double);
299 }
300 else
301 {
302 tType = typeof(String);
303 }
304 sMaxSize = sField.Substring(1,5);
305 sMaxSize = sMaxSize.TrimStart(cZero);
306 nMaxSize = Convert.ToInt16(sMaxSize);
307 sFieldName = sField.Substring(6, sField.Length - 6);
308 _resultsetFillColumn(nSet, j, sFieldName, tType, nMaxSize);
309 }
310 }
311 }
312
313 private void ProcessRecords(string[] sResultArray, int nRecords, int nSet, int numCols, int nStartIndex)
314 {
315 String[] formats = new string[] {"MMM d,yyyy","MMM dd,yyyy","MMM d, yyyy","MMM dd, yyyy", "MMM dd, yyyy@HH:mm", "MMM d, yyyy@HH:mm", "MMM d,yyyy@HH:mm", "MMM d, yyyy@H:mm", "MMM d,yyyy@H:mm", "MMM dd,yyyy@HH:mm" };
316 char[] cFldDelim = new char[] {'^'};
317 m_aResultSets[nSet].data = new object[nRecords-1, numCols];
318 string[] saRecord;
319 int j;
320 for (j = nStartIndex; j < nRecords + nStartIndex -1; j++)
321 {
322 saRecord = sResultArray[j].Split(cFldDelim);
323 for (int k = 0; k< saRecord.GetLength(0); k++)
324 {
325 if (m_aResultSets[nSet].metaData[k].type == typeof(DateTime))
326 {
327 DateTime value;
328 String dateText = saRecord[k].ToString();
329 if (DateTime.TryParseExact(dateText, formats, CultureInfo.InvariantCulture, DateTimeStyles.AllowWhiteSpaces, out value))
330 {
331 m_aResultSets[nSet].data[j - nStartIndex, k] = value;
332 }
333 else if (DateTime.TryParse(dateText, out value))
334 {
335 m_aResultSets[nSet].data[j - nStartIndex, k] = value;
336 }
337 }
338 else if (m_aResultSets[nSet].metaData[k].type == typeof(Int32))
339 {
340 int value = 0;
341 if (int.TryParse(saRecord[k], out value))
342 {
343 m_aResultSets[nSet].data[j - nStartIndex, k] = value;
344 }
345 }
346 else if (m_aResultSets[nSet].metaData[k].type == typeof(Double))
347 {
348 double value = 0.0;
349 if (double.TryParse(saRecord[k], out value))
350 {
351 m_aResultSets[nSet].data[j - nStartIndex, k] = value;
352 }
353 }
354 else //it's a string
355 {
356 m_aResultSets[nSet].data[j - nStartIndex, k] = saRecord[k];
357 }
358 }
359 }
360 }
361
362
363
364 private void _resultsetCreate()
365 {
366 m_nCurrentSet = 0;
367 char[] cRecDelim = new char[1];
368 cRecDelim[0] = (char) 30;
369
370 char cEof = (char) 31;
371 string sEof = cEof.ToString();
372
373 string sFldDelim = "^";
374 char[] cFldDelim = sFldDelim.ToCharArray();
375 string sZero = "0";
376 char[] cZero = sZero.ToCharArray();
377 string sBar = "|";
378 char[] cBar = sBar.ToCharArray();
379
380 string sResult = this.CallRPX();
381
382 string[] sResultArray = sResult.Split(cRecDelim);
383 int nTotalRecords = sResultArray.GetLength(0);
384
385 //Get set count and record count
386 int[] naRecords; //Count of records for each set
387 int[] naHeaderIndex; //Location (index) of header records in sResultArray
388 int nRecordSetCount; //Count of recordsets
389
390 IndexRecords(sResultArray, out naRecords, out naHeaderIndex, out nRecordSetCount);
391 m_aResultSets = new RPMSDbResultSet[nRecordSetCount];
392
393 for (int nSet = 0; nSet < nRecordSetCount; nSet++)
394 {
395 int nHeaderIndex = naHeaderIndex[nSet];
396 int nRecords = naRecords[nSet];
397 int numCols = 0;
398 m_aResultSets[nSet] = new RPMSDbResultSet();
399 m_aResultSets[nSet].recordsAffected = 0;
400
401 //process header
402 string sHeader = sResultArray[nHeaderIndex];
403 ProcessHeader(sHeader, nSet, out numCols);
404
405 //process records
406 this.ProcessRecords(sResultArray, nRecords, nSet, numCols, nHeaderIndex+1);
407
408 }
409 }
410
411 private void IndexRecords(string[] sResultArray,
412 out int[] naRecordsOut,
413 out int[] naHeaderIndexOut,
414 out int nSets)
415 {
416
417 int[] naHeaderIndex = new int[999];
418 int nTotalRecords = sResultArray.GetLength(0);
419 nSets = 0;
420 int[] naRecords = new int[999];
421
422 if (sResultArray[0].StartsWith("@@@meta@@@"))
423 {
424 //Count the number of record sets and record the index of each header record
425 for (int k = 0; k < nTotalRecords; k++)
426 {
427 if (sResultArray[k].StartsWith("@@@meta@@@"))
428 {
429 naHeaderIndex[nSets] = k;
430 nSets++;
431 }
432 }
433 //Calculate record count for each set
434 for (int k = 0; k < nSets - 1; k++)
435 {
436 naRecords[k] = naHeaderIndex[k + 1] - naHeaderIndex[k];// - 1;
437 }
438 naRecords[nSets - 1] = nTotalRecords - naHeaderIndex[nSets - 1] - 1;
439 }
440 else
441 {
442 //this is an old-style header with only one record
443 nSets = 1;
444 naHeaderIndex[0] = 0;
445 naRecords[0] = nTotalRecords - 1;
446 }
447
448 naRecordsOut = new int[nSets];
449 naHeaderIndexOut = new int[nSets];
450 for (int k = 0; k < nSets; k++)
451 {
452 naRecordsOut[k] = naRecords[k];
453 naHeaderIndexOut[k] = naHeaderIndex[k];
454 }
455 }
456
457 private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize)
458 {
459 m_aResultSets[nSet].metaData[nIdx].name = name;
460 m_aResultSets[nSet].metaData[nIdx].type = type;
461 m_aResultSets[nSet].metaData[nIdx].maxSize = maxSize;
462 }
463
464 private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize,
465 string sFieldID, string sReadOnly, string sKeyField)
466 {
467 m_aResultSets[nSet].metaData[nIdx].name = name;
468 m_aResultSets[nSet].metaData[nIdx].type = type;
469 m_aResultSets[nSet].metaData[nIdx].maxSize = maxSize;
470
471 m_aResultSets[nSet].metaData[nIdx].fmFieldID = sFieldID;
472 m_aResultSets[nSet].metaData[nIdx].fmReadOnly = !sReadOnly.Equals("FALSE",StringComparison.InvariantCultureIgnoreCase);
473 m_aResultSets[nSet].metaData[nIdx].fmKeyField = sKeyField.Equals("TRUE",StringComparison.InvariantCultureIgnoreCase);
474
475 }
476
477
478 /// <summary>
479 /// Returns the array of RMPSResultSets retrieved from RPMS
480 /// </summary>
481 public RPMSDbResultSet[] ResultSets
482 {
483 get
484 {
485 return m_aResultSets;
486 }
487 }
488
489 /// <summary>
490 /// Sets and Returns the current recordset
491 /// </summary>
492 public int CurrentRecordSet
493 {
494 get
495 {
496 return m_nCurrentSet;
497 }
498 set
499 {
500 m_nCurrentSet = value;
501 }
502 }
503
504
505 }
506}
Note: See TracBrowser for help on using the repository browser.