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

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

Initial commit of C# Source Code. Now to try to get it to compile.
File size: 12.7 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;
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 if (sRecordInfo.GetLength(0) > 2)
178 {
179 sSeed = sRecordInfo[2];
180 sSeed = (sSeed == "")?"0":sSeed;
181 try
182 {
183 m_aResultSets[nSet].fmSeed = Convert.ToInt32(sSeed);
184 }
185 catch (Exception ex)
186 {
187 Debug.Assert(false, "BMXNet.RMSPDb: 'Seed' value failed to convert to integer.");
188 Debug.Write(ex.Message + "\n");
189 }
190 }
191
192 if (sRecordInfo.GetLength(0) > 3)
193 {
194 m_aResultSets[nSet].fmForeignKey = sRecordInfo[4];
195
196 }
197
198 m_aResultSets[nSet].fmKeyField = "";
199 //2nd through nth ^-Pieces are Column info: Fileman File#FileMan Field#|DataType|Field Length|Column Name|IsReadOnly|IsKeyField
200 for (j=1; j < sRecordSetInfo.GetLength(0); j++)
201 {
202 string[] sColumnInfo = sRecordSetInfo[j].Split(cBar);
203 //Field 0 = FileID
204 //Field 1 = FieldID
205 //Field 2 = DataType
206 //Field 3 = Length
207 //Field 4 = ColumnName
208 //Field 5 = IsReadOnly
209 //Field 6 = IsKeyField
210 sFileID = sColumnInfo[0];
211 string sFieldID = sColumnInfo[1];
212
213
214 switch (sFieldID)
215 {
216 case ".001":
217 m_aResultSets[nSet].fmKeyField += "," + sColumnInfo[4];
218 break;
219 case ".0001":
220 m_aResultSets[nSet].fmKeyField = sColumnInfo[4];
221 break;
222 default:
223 break;
224 }
225
226 sType = sColumnInfo[2];
227 switch (sType)
228 {
229 case "D":
230 tType = typeof(DateTime);
231 break;
232 case "I":
233 tType = typeof(Int32);
234 break;
235 case "N":
236 tType = typeof(System.Double);
237 break;
238 default:
239 tType = typeof(String);
240 break;
241 }
242 sMaxSize = sColumnInfo[3];
243 nMaxSize = Convert.ToInt16(sMaxSize);
244 sFieldName = sColumnInfo[4];
245
246 if (m_aResultSets[nSet].fmForeignKey == sFieldID)
247 {
248 m_aResultSets[nSet].fmForeignKey = sFieldName;
249 }
250
251 sReadOnly = sColumnInfo[5];
252 sKeyField = sColumnInfo[6];
253 _resultsetFillColumn(nSet, j-1, sFieldName, tType, nMaxSize, sFieldID, sReadOnly, sKeyField);
254 }
255 }
256 else
257 {
258 //Old style column header info
259
260 string sField;
261 string sType;
262 string sMaxSize;
263 string sFieldName;
264 int nMaxSize;
265 Type tType;
266
267 string[] saHeader = sHeader.Split(cFldDelim);
268 numCols = saHeader.GetLength(0);
269 m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];
270 for (j=0; j < numCols; j++)
271 {
272 sField = saHeader[j];
273 sType = sField.Substring(0,1);
274 if (sType == "D")
275 {
276 tType = typeof(DateTime);
277 }
278 else if(sType == "I")
279 {
280 tType = typeof(Int32);
281 }
282 else if(sType == "N")
283 {
284 tType = typeof(System.Double);
285 }
286 else
287 {
288 tType = typeof(String);
289 }
290 sMaxSize = sField.Substring(1,5);
291 sMaxSize = sMaxSize.TrimStart(cZero);
292 nMaxSize = Convert.ToInt16(sMaxSize);
293 sFieldName = sField.Substring(6, sField.Length - 6);
294 _resultsetFillColumn(nSet, j, sFieldName, tType, nMaxSize);
295 }
296 }
297 }
298
299 private void ProcessRecords(string[] sResultArray, int nRecords, int nSet, int numCols, int nStartIndex)
300 {
301
302 string sFldDelim = "^";
303 char[] cFldDelim = sFldDelim.ToCharArray();
304 m_aResultSets[nSet].data = new object[nRecords-1, numCols];
305 string[] saRecord;
306 int j;
307 for (j = nStartIndex; j < nRecords + nStartIndex -1; j++)
308 {
309 saRecord = sResultArray[j].Split(cFldDelim);
310 for (int k = 0; k< saRecord.GetLength(0); k++)
311 {
312 if (m_aResultSets[nSet].metaData[k].type == typeof(DateTime))
313 {
314 if (saRecord[k] == "")
315 {
316 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
317 }
318 else
319 {
320 try
321 {
322 m_aResultSets[nSet].data[j-nStartIndex, k] = Convert.ToDateTime(saRecord[k]);
323 }
324 catch (Exception ex)
325 {
326 Debug.Write(ex.Message);
327 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
328 }
329 }
330 }
331 else if (m_aResultSets[nSet].metaData[k].type == typeof(Int32))
332 {
333 if (saRecord[k] == "")
334 {
335 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
336 }
337 else
338 {
339 try
340 {
341 m_aResultSets[nSet].data[j-nStartIndex, k] = Convert.ToInt32(saRecord[k]);
342 }
343 catch (Exception ex)
344 {
345 Debug.Write(ex.Message);
346 m_aResultSets[nSet].data[j-nStartIndex, k] = null;
347 }
348 }
349 }
350 //TODO: Double datatype here
351 else //it's a string
352 {
353 m_aResultSets[nSet].data[j-nStartIndex, k] = saRecord[k];
354 }
355 }
356 }
357 }
358
359
360
361 private void _resultsetCreate()
362 {
363 m_nCurrentSet = 0;
364 char[] cRecDelim = new char[1];
365 cRecDelim[0] = (char) 30;
366
367 char cEof = (char) 31;
368 string sEof = cEof.ToString();
369
370 string sFldDelim = "^";
371 char[] cFldDelim = sFldDelim.ToCharArray();
372 string sZero = "0";
373 char[] cZero = sZero.ToCharArray();
374 string sBar = "|";
375 char[] cBar = sBar.ToCharArray();
376
377 string sResult = this.CallRPX();
378
379 string[] sResultArray = sResult.Split(cRecDelim);
380 int nTotalRecords = sResultArray.GetLength(0);
381
382 //Get set count and record count
383 int[] naRecords; //Count of records for each set
384 int[] naHeaderIndex; //Location (index) of header records in sResultArray
385 int nRecordSetCount; //Count of recordsets
386
387 IndexRecords(sResultArray, out naRecords, out naHeaderIndex, out nRecordSetCount);
388 m_aResultSets = new RPMSDbResultSet[nRecordSetCount];
389
390 for (int nSet = 0; nSet < nRecordSetCount; nSet++)
391 {
392 int nHeaderIndex = naHeaderIndex[nSet];
393 int nRecords = naRecords[nSet];
394 int numCols = 0;
395 m_aResultSets[nSet] = new RPMSDbResultSet();
396 m_aResultSets[nSet].recordsAffected = 0;
397
398 //process header
399 string sHeader = sResultArray[nHeaderIndex];
400 ProcessHeader(sHeader, nSet, out numCols);
401
402 //process records
403 this.ProcessRecords(sResultArray, nRecords, nSet, numCols, nHeaderIndex+1);
404
405 }
406 }
407
408 private void IndexRecords(string[] sResultArray,
409 out int[] naRecordsOut,
410 out int[] naHeaderIndexOut,
411 out int nSets)
412 {
413
414 int[] naHeaderIndex = new int[999];
415 int nTotalRecords = sResultArray.GetLength(0);
416 nSets = 0;
417 int[] naRecords = new int[999];
418
419 if (sResultArray[0].StartsWith("@@@meta@@@") == false)
420 {
421 //this is an old-style header with only one record
422 nSets = 1;
423 naHeaderIndex[0] = 0;
424 naRecords[0]=nTotalRecords - 1;
425 }
426 else
427 {
428
429 //Count the number of record sets and record the index of each header record
430 for (int k = 0; k < nTotalRecords; k++)
431 {
432 if (sResultArray[k].StartsWith("@@@meta@@@") == true)
433 {
434 naHeaderIndex[nSets] = k;
435 nSets++;
436 }
437 }
438 //Calculate record count for each set
439 for (int k = 0; k < nSets - 1; k++)
440 {
441 naRecords[k] = naHeaderIndex[k+1] - naHeaderIndex[k] ;// - 1;
442 }
443 naRecords[nSets-1] = nTotalRecords - naHeaderIndex[nSets - 1] - 1;
444 }
445
446 naRecordsOut = new int[nSets];
447 naHeaderIndexOut = new int[nSets];
448 for (int k = 0; k < nSets; k++)
449 {
450 naRecordsOut[k] = naRecords[k];
451 naHeaderIndexOut[k] = naHeaderIndex[k];
452 }
453 }
454
455 private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize)
456 {
457 m_aResultSets[nSet].metaData[nIdx].name = name;
458 m_aResultSets[nSet].metaData[nIdx].type = type;
459 m_aResultSets[nSet].metaData[nIdx].maxSize = maxSize;
460 }
461
462 private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize,
463 string sFieldID, string sReadOnly, string sKeyField)
464 {
465 m_aResultSets[nSet].metaData[nIdx].name = name;
466 m_aResultSets[nSet].metaData[nIdx].type = type;
467 m_aResultSets[nSet].metaData[nIdx].maxSize = maxSize;
468
469 m_aResultSets[nSet].metaData[nIdx].fmFieldID = sFieldID;
470 m_aResultSets[nSet].metaData[nIdx].fmReadOnly = (sReadOnly == "FALSE")?false:true;
471 m_aResultSets[nSet].metaData[nIdx].fmKeyField = (sKeyField == "TRUE")?true:false;
472
473 }
474
475
476 /// <summary>
477 /// Returns the array of RMPSResultSets retrieved from RPMS
478 /// </summary>
479 public RPMSDbResultSet[] ResultSets
480 {
481 get
482 {
483 return m_aResultSets;
484 }
485 }
486
487 /// <summary>
488 /// Sets and Returns the current recordset
489 /// </summary>
490 public int CurrentRecordSet
491 {
492 get
493 {
494 return m_nCurrentSet;
495 }
496 set
497 {
498 m_nCurrentSet = value;
499 }
500 }
501
502
503 }
504}
Note: See TracBrowser for help on using the repository browser.