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

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

BMX version bumped to 2.3.
New dll
BMXNetConnectInfo: Event polling from RPMS/VISTA is now async. BMX Writer lock removed.
BMXNetLib:

  1. Application context changes are now suppressed. Developer must make sure that his/her application has all the needed BMX methods. This was done for performance enhancement as application context changes are very expensive in network time.
  2. Locks are implemented at the TransmitRPC with a very simple Lock(this) which works very well.

RPMSDb: See #1 for BMXNetLib. All context changes are now suppressed.
File size: 13.9 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
90 /* /* 3110109 -- smh Commented out for performance
91 string sOldContext = m_rpx.AppContext;
92
93 if (m_rpx.AppContext != "BMXRPC")
94 {
95 m_rpx.AppContext = "BMXRPC";
96 }
97 */
98
99 sRPC = "BMX UPDATE";
100 int nStart = 7;
101 m_sCmd = m_sCmd.Substring(nStart);
102 string sResult = m_rpx.TransmitRPC( sRPC, m_sCmd);
103
104 /* /* 3110109 -- smh Commented out for performance
105 if (sOldContext != m_rpx.AppContext)
106 {
107 m_rpx.AppContext = sOldContext;
108 }
109 */
110
111 resultset = new RPMSDbResultSet();
112 resultset.recordsAffected = 1; //Make this the return value of the call
113 }
114
115 private string CallRPX()
116 {
117 //Make rpx call
118 string sRPC;
119 string sParam;
120 /* /* 3110109 -- smh Commented out for performance issues.
121 string sOldContext = m_rpx.AppContext;
122 */
123 if (m_sQueryType == "SELECT")
124 {
125 /*/* 3110109 -- smh Commented out for performance issues.
126 if (m_rpx.AppContext != "BMXRPC")
127 {
128 m_rpx.AppContext = "BMXRPC";
129 }
130 */
131 sRPC = "BMX SQL";
132 sParam = m_sCmd;
133 }
134 else //it's an RPC (stored procedure)
135 {
136 sRPC = BMXNetLib.Piece(m_sCmd, "^", 1);
137 sParam = BMXNetLib.Piece(m_sCmd, "^", 2 , 99);
138 }
139
140 string sResult = m_rpx.TransmitRPC( sRPC, sParam);
141
142 /*
143 if (sOldContext != m_rpx.AppContext)
144 {
145 m_rpx.AppContext = sOldContext;
146 }
147 */
148
149 return sResult;
150 }
151
152 private void ProcessHeader(string sHeader, int nSet, out int numCols)
153 {
154 //Modifies m_aResultSet[nSet]
155 string sFldDelim = "^";
156 char[] cFldDelim = sFldDelim.ToCharArray();
157 string sZero = "0";
158 char[] cZero = sZero.ToCharArray();
159 string sBar = "|";
160 char[] cBar = sBar.ToCharArray();
161
162 int j;
163 if (sHeader.StartsWith("@@@meta@@@") == true)
164 {
165 //New style -- ColumnHeaderInfo in [1]
166 //Parse out data location or update logic here
167 //RecordInfo ^ Column1 info ^ column2 info ^ ... ^ column n info
168 string sType;
169 string sMaxSize;
170 string sFieldName;
171 string sReadOnly;
172 string sKeyField;
173 string sFileID;
174 string sSeed = "";
175 int nMaxSize;
176 Type tType;
177
178 int nTemp = 10; //length of @@@meta@@@
179 //actual header
180 sHeader = sHeader.Substring(10,(sHeader.Length - nTemp));
181 string[] sRecordSetInfo = sHeader.Split(cFldDelim);
182
183 //substract one because 1st item is RecordId|File# -- rest is columns
184 numCols = sRecordSetInfo.GetLength(0)-1;
185 m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];
186
187 //Set FileID
188 //First ^-Piece is recordset-level info: RecordIdentifier|File#
189 string[] sRecordInfo = sRecordSetInfo[0].Split(cBar);
190 m_aResultSets[nSet].fmFileID = sRecordInfo[1];
191
192 //What is the seed???
193 if (sRecordInfo.GetLength(0) > 2)
194 {
195 sSeed = sRecordInfo[2];
196 sSeed = (sSeed == "")?"0":sSeed;
197 try
198 {
199 m_aResultSets[nSet].fmSeed = Convert.ToInt32(sSeed);
200 }
201 catch (Exception ex)
202 {
203 Debug.Assert(false, "BMXNet.RMSPDb: 'Seed' value failed to convert to integer.");
204 Debug.Write(ex.Message + "\n");
205 }
206 }
207
208 // Foreign key is included
209 if (sRecordInfo.GetLength(0) > 3)
210 {
211 m_aResultSets[nSet].fmForeignKey = sRecordInfo[4];
212
213 }
214
215 m_aResultSets[nSet].fmKeyField = "";
216 //2nd through nth ^-Pieces are Column info: Fileman File#|FileMan Field#|DataType|Field Length|Column Name|IsReadOnly|IsKeyField|????
217 for (j=1; j < sRecordSetInfo.GetLength(0); j++)
218 {
219 string[] sColumnInfo = sRecordSetInfo[j].Split(cBar);
220 //Field 0 = FileID
221 //Field 1 = FieldID
222 //Field 2 = DataType
223 //Field 3 = Length
224 //Field 4 = ColumnName
225 //Field 5 = IsReadOnly
226 //Field 6 = IsKeyField
227 //Field 7 {MISSING}
228 sFileID = sColumnInfo[0];
229 string sFieldID = sColumnInfo[1];
230
231
232 switch (sFieldID)
233 {
234 case ".001":
235 m_aResultSets[nSet].fmKeyField += "," + sColumnInfo[4];
236 break;
237 case ".0001":
238 m_aResultSets[nSet].fmKeyField = sColumnInfo[4];
239 break;
240 default:
241 break;
242 }
243
244 sType = sColumnInfo[2];
245 switch (sType)
246 {
247 case "D":
248 tType = typeof(DateTime);
249 break;
250 case "I":
251 tType = typeof(Int32);
252 break;
253 case "N":
254 tType = typeof(System.Double);
255 break;
256 default:
257 tType = typeof(String);
258 break;
259 }
260 sMaxSize = sColumnInfo[3];
261 nMaxSize = Convert.ToInt16(sMaxSize);
262 sFieldName = sColumnInfo[4];
263
264 if (m_aResultSets[nSet].fmForeignKey == sFieldID)
265 {
266 m_aResultSets[nSet].fmForeignKey = sFieldName;
267 }
268
269 sReadOnly = sColumnInfo[5];
270 sKeyField = sColumnInfo[6];
271 _resultsetFillColumn(nSet, j-1, sFieldName, tType, nMaxSize, sFieldID, sReadOnly, sKeyField);
272 }
273 }
274 else
275 {
276 //Old style column header info
277
278 string sField;
279 string sType;
280 string sMaxSize;
281 string sFieldName;
282 int nMaxSize;
283 Type tType;
284
285 string[] saHeader = sHeader.Split(cFldDelim);
286 numCols = saHeader.GetLength(0);
287 m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];
288 for (j=0; j < numCols; j++)
289 {
290 sField = saHeader[j];
291 sType = sField.Substring(0,1);
292 if (sType == "D")
293 {
294 tType = typeof(DateTime);
295 }
296 else if(sType == "I")
297 {
298 tType = typeof(Int32);
299 }
300 else if(sType == "N")
301 {
302 tType = typeof(System.Double);
303 }
304 else
305 {
306 tType = typeof(String);
307 }
308 sMaxSize = sField.Substring(1,5);
309 sMaxSize = sMaxSize.TrimStart(cZero);
310 nMaxSize = Convert.ToInt16(sMaxSize);
311 sFieldName = sField.Substring(6, sField.Length - 6);
312 _resultsetFillColumn(nSet, j, sFieldName, tType, nMaxSize);
313 }
314 }
315 }
316
317 private void ProcessRecords(string[] sResultArray, int nRecords, int nSet, int numCols, int nStartIndex)
318 {
319
320 string sFldDelim = "^";
321 char[] cFldDelim = sFldDelim.ToCharArray();
322 // nRecords-1 because last record is empty (Where $C(31) (end of record) is)
323 m_aResultSets[nSet].data = new object[nRecords-1, numCols];
324 string[] saRecord;
325 int j;
326 for (j = nStartIndex; j < nRecords + nStartIndex -1; j++)
327 {
328 saRecord = sResultArray[j].Split(cFldDelim);
329 for (int k = 0; k< saRecord.GetLength(0); k++)
330 {
331 //Date Time validation
332 //TODO: Support Fileman DateTime
333 if (m_aResultSets[nSet].metaData[k].type == typeof(DateTime))
334 {
335 if (saRecord[k] == "")
336 {
337 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
338 }
339 else
340 {
341 try
342 {
343 m_aResultSets[nSet].data[j-nStartIndex, k] = Convert.ToDateTime(saRecord[k]);
344 }
345 catch (Exception ex)
346 {
347 Debug.Write(ex.Message);
348 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
349 }
350 }
351 }
352 else if (m_aResultSets[nSet].metaData[k].type == typeof(Int32))
353 {
354 if (saRecord[k] == "")
355 {
356 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
357 }
358 else
359 {
360 try
361 {
362 m_aResultSets[nSet].data[j-nStartIndex, k] = Convert.ToInt32(saRecord[k]);
363 }
364 catch (Exception ex)
365 {
366 Debug.Write(ex.Message);
367 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
368 }
369 }
370 }
371 //TODO: Double datatype here
372 else //it's a string
373 {
374 m_aResultSets[nSet].data[j-nStartIndex, k] = saRecord[k];
375 }
376 }
377 }
378 }
379
380
381
382 private void _resultsetCreate()
383 {
384 m_nCurrentSet = 0;
385 char[] cRecDelim = new char[1];
386 cRecDelim[0] = (char) 30;
387
388 char cEof = (char) 31;
389 string sEof = cEof.ToString();
390
391 string sFldDelim = "^";
392 char[] cFldDelim = sFldDelim.ToCharArray();
393 string sZero = "0";
394 char[] cZero = sZero.ToCharArray();
395 string sBar = "|";
396 char[] cBar = sBar.ToCharArray();
397
398 string sResult = this.CallRPX();
399
400 string[] sResultArray = sResult.Split(cRecDelim);
401 int nTotalRecords = sResultArray.GetLength(0);
402
403 //Get set count and record count
404 int[] naRecords; //Count of records for each set
405 int[] naHeaderIndex; //Location (index) of header records in sResultArray
406 int nRecordSetCount; //Count of recordsets
407
408 //Gets Records[sets] (val is number of records for each set), Headers[sets] (val is header location in array), and number of record sets.
409 IndexRecords(sResultArray, out naRecords, out naHeaderIndex, out nRecordSetCount);
410 //Create array of result sets
411 m_aResultSets = new RPMSDbResultSet[nRecordSetCount];
412
413 for (int nSet = 0; nSet < nRecordSetCount; nSet++)
414 {
415 int nHeaderIndex = naHeaderIndex[nSet];
416 int nRecords = naRecords[nSet];
417 int numCols = 0;
418 m_aResultSets[nSet] = new RPMSDbResultSet();
419 m_aResultSets[nSet].recordsAffected = 0;
420
421 //process header
422 string sHeader = sResultArray[nHeaderIndex];
423 ProcessHeader(sHeader, nSet, out numCols);
424
425 //process records
426 this.ProcessRecords(sResultArray, nRecords, nSet, numCols, nHeaderIndex+1);
427
428 }
429 }
430
431 private void IndexRecords(string[] sResultArray,
432 out int[] naRecordsOut,
433 out int[] naHeaderIndexOut,
434 out int nSets)
435 {
436
437 int[] naHeaderIndex = new int[999];
438 int nTotalRecords = sResultArray.GetLength(0);
439 nSets = 0;
440 int[] naRecords = new int[999];
441
442 if (sResultArray[0].StartsWith("@@@meta@@@") == false)
443 {
444 //this is an old-style header with only one record
445 nSets = 1;
446 naHeaderIndex[0] = 0;
447 naRecords[0]=nTotalRecords - 1;
448 }
449 else
450 {
451
452 //Count the number of record sets and record the index of each header record
453 for (int k = 0; k < nTotalRecords; k++)
454 {
455 if (sResultArray[k].StartsWith("@@@meta@@@") == true)
456 {
457 naHeaderIndex[nSets] = k;
458 nSets++;
459 }
460 }
461 //Calculate record count for each set
462 for (int k = 0; k < nSets - 1; k++)
463 {
464 naRecords[k] = naHeaderIndex[k+1] - naHeaderIndex[k] ;// - 1;
465 }
466 naRecords[nSets-1] = nTotalRecords - naHeaderIndex[nSets - 1] - 1;
467 }
468
469 naRecordsOut = new int[nSets];
470 naHeaderIndexOut = new int[nSets];
471 for (int k = 0; k < nSets; k++)
472 {
473 naRecordsOut[k] = naRecords[k];
474 naHeaderIndexOut[k] = naHeaderIndex[k];
475 }
476 }
477
478 private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize)
479 {
480 m_aResultSets[nSet].metaData[nIdx].name = name;
481 m_aResultSets[nSet].metaData[nIdx].type = type;
482 m_aResultSets[nSet].metaData[nIdx].maxSize = maxSize;
483 }
484
485 private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize,
486 string sFieldID, string sReadOnly, string sKeyField)
487 {
488 m_aResultSets[nSet].metaData[nIdx].name = name;
489 m_aResultSets[nSet].metaData[nIdx].type = type;
490 m_aResultSets[nSet].metaData[nIdx].maxSize = maxSize;
491
492 m_aResultSets[nSet].metaData[nIdx].fmFieldID = sFieldID;
493 m_aResultSets[nSet].metaData[nIdx].fmReadOnly = (sReadOnly == "FALSE")?false:true;
494 m_aResultSets[nSet].metaData[nIdx].fmKeyField = (sKeyField == "TRUE")?true:false;
495
496 }
497
498
499 /// <summary>
500 /// Returns the array of RMPSResultSets retrieved from RPMS
501 /// </summary>
502 public RPMSDbResultSet[] ResultSets
503 {
504 get
505 {
506 return m_aResultSets;
507 }
508 }
509
510 /// <summary>
511 /// Sets and Returns the current recordset
512 /// </summary>
513 public int CurrentRecordSet
514 {
515 get
516 {
517 return m_nCurrentSet;
518 }
519 set
520 {
521 m_nCurrentSet = value;
522 }
523 }
524
525
526 }
527}
Note: See TracBrowser for help on using the repository browser.