DB/ADO.NET Array Adapter
< DB
Jump to navigation
Jump to search
Array Adapter class returns query result from any data provider in a generic way; can represent the result as boxed ragged array and convert such ragged array to flat array ready for import to J. [{{#file: "ArrayAdapter.cs"}} Download script: ArrayAdapter.cs ]
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
namespace DbGeneric
{
public class ArrayAdapter
{
Query(dbStr, conStr, cmdStr) takes database provider ID, connection string and SQL query string and returns an enumerator over the result records.
[{{#file: "ArrayAdapter.cs"}} Download script: ArrayAdapter.cs ]
public static IEnumerable<IDataRecord> Query(string dbStr, string conStr, string cmdStr) {
DbProviderFactory db = DbProviderFactories.GetFactory(dbStr);
using (DbConnection con = db.CreateConnection()) {
con.ConnectionString = conStr;
con.Open();
using (DbCommand cmd = con.CreateCommand()) {
cmd.CommandText = cmdStr;
using (DbDataReader rdr = cmd.ExecuteReader()) {
if (rdr.HasRows) {
while (rdr.Read()) {
yield return rdr;
}
}
}
}
}
}
GetRows(recordEnumerator) takes a record enumerator returned by Query and fetches each row placing fields into object array cells.
[{{#file: "ArrayAdapter.cs"}} Download script: ArrayAdapter.cs ]
public static object[][] GetRows(IEnumerable<IDataRecord> q) {
List<object[]> rows = new List<object[]>();
foreach (IDataRecord r in q) {
object[] cols = new object[r.FieldCount];
rows.Add(cols);
r.GetValues(cols);
}
return rows.ToArray();
}
Flat(nestedArray), a helper function, takes nested array returned by GetRows and converts it to a flat array, necessary to work with .NET/COM interop.
[{{#file: "ArrayAdapter.cs"}} Download script: ArrayAdapter.cs ]
public static object[,] Flat(object[][] ragged) {
if (ragged.Length <= 0)
return new object[0, 0];
object[,] flat = new object[ragged.Length, ragged[0].Length];
for (int j = 0; j < ragged.Length; j++) {
for (int i = 0; i < ragged[0].Length; i++) {
flat[j, i] = ragged[j][i];
}
}
return flat;
}
GetColumns(recordEnumerator) takes a record enumerator returned by Query and the maximum number of rows to process, and returns columns placing each field in an array of corresponding type.
[{{#file: "ArrayAdapter.cs"}} Download script: ArrayAdapter.cs ]
public static Array[] GetColumns(IEnumerable<IDataRecord> q, int length) {
Array[] res = new Array[0];
int j = 0, fldCount = 0;
foreach (IDataRecord r in q) {
if (j == 0) {
fldCount = r.FieldCount;
res = new Array[fldCount];
for (int i = 0; i < fldCount; i++) {
Type t = r[i].GetType();
if (t == typeof(string))
t = typeof(object); // HACK: array of VARIANT instead of BSTR
res[i] = Array.CreateInstance(t, length);
}
}
for (int i = 0; i < fldCount; i++) {
res[i].SetValue(r[i], j);
}
if (++j >= length)
break;
}
if (res != null && j < length) {
for (int i = 0; i < fldCount; i++) {
Array tmp = res[i];
res[i] = Array.CreateInstance(tmp.GetType().GetElementType(), j);
Array.Copy(tmp, res[i], j);
}
}
return res;
}
}
To test the Array Adapter, we will use the Access file provided in J installation.
Change the path to your location.
[{{#file: "ArrayAdapter.cs"}} Download script: ArrayAdapter.cs ]
class Program
{
static void Main(string[] args) {
string dbStr = "System.Data.OleDb";
string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=d:\Math\j601\system\examples\data\jdata.mdb";
string cmdStr = "select * from tdata where SALARY>=80000";
Console.WriteLine("Query records iteration:");
foreach (IDataRecord r in ArrayAdapter.Query(dbStr, conStr, cmdStr)) {
for (int i = 0; i < r.FieldCount; i++) {
if (i > 0) Console.Write(",");
Console.Write(r[i]);
}
Console.WriteLine();
}
Console.WriteLine("\nQuery flat array:");
object[][] vals = ArrayAdapter.GetRows(ArrayAdapter.Query(dbStr, conStr, cmdStr));
for (int j = 0; j < vals.Length; j++) {
for (int i = 0; i < vals[j].Length; i++) {
if (i > 0) Console.Write(",");
Console.Write(vals[j][i]);
}
Console.WriteLine();
}
Console.WriteLine("\nJ flat array:");
JDLLServerLib.JDLLServerClass J = new JDLLServerLib.JDLLServerClass();
object tmp = ArrayAdapter.Flat(vals);
J.SetB("A", ref tmp);
J.DoR(",.&.:>\"1|:A", out tmp);
Console.WriteLine(tmp);
Console.WriteLine("Query columns:");
Array[] cols = ArrayAdapter.GetColumns(ArrayAdapter.Query(dbStr, conStr, cmdStr), 10);
for (int i = 0; i < cols.Length; i++) {
for (int j = 0; j < cols[0].Length; j++) {
if (j > 0)
Console.Write(",");
Console.Write(cols[i].GetValue(j));
}
Console.WriteLine();
}
Console.WriteLine("\nJ columns:");
for (int i = 0; i < cols.Length; i++) {
tmp = cols[i];
J.SetB("A", ref tmp);
J.DoR("A", out tmp);
Console.Write(tmp);
}
}
}
}
The result of the test run is:
Query records iteration: Genereaux S ,F,D103,19450300,19660200,95415 Rogerson G ,M,D101,19571200,19830200,108777 Cahill G ,M,D108,19320500,19671000,81358 Query flat array: Genereaux S ,F,D103,19450300,19660200,95415 Rogerson G ,M,D101,19571200,19830200,108777 Cahill G ,M,D108,19320500,19671000,81358 J flat array: +--------------+-+----+--------+--------+------+ |Genereaux S |F|D103|19450300|19660200| 95415| |Rogerson G |M|D101|19571200|19830200|108777| |Cahill G |M|D108|19320500|19671000| 81358| +--------------+-+----+--------+--------+------+ Query columns: Genereaux S ,Rogerson G ,Cahill G F,M,M D103,D101,D108 19450300,19571200,19320500 19660200,19830200,19671000 95415,108777,81358 J columns: +--------------+--------------+--------------+ |Genereaux S |Rogerson G |Cahill G | +--------------+--------------+--------------+ +-+-+-+ |F|M|M| +-+-+-+ +----+----+----+ |D103|D101|D108| +----+----+----+ 19450300 19571200 19320500 19660200 19830200 19671000 95415 108777 81358
See Also
Contributed by Oleg Kobchenko