DB/ADO.NET Array Adapter

From J Wiki
< 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.

Information.png 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