source: BMXNET_RPMS_dotNET_UTILITIES-BMX/trunk/cs/bmx_0200scr/BMX2/BMXNet/RPMSDb.cs@ 916

Last change on this file since 916 was 822, checked in by Sam Habiel, 14 years ago

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