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.
[{{#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