--- Raul Miller <[EMAIL PROTECTED]> wrote: > On 2/27/07, Oleg Kobchenko <[EMAIL PROTECTED]> wrote: > > Below is a quick sketch of vertical arrays > > constructed from DataReader.
http://www.jsoftware.com/pipermail/general/2007-February/029104.html > Hmm... that is simpler than I thought. > > That said, while some of the complexity when compared > to my earlier implementation is eliminated by a better match > between .Net types and J types, much of the simplicity of > this approach comes because of issues that it ignores: > > Column names are ignored. > Nulls are ignored. > Passing the data into J is ignored. > Some (perhaps irrelevant?) data types are ignored. Of course that was a prototype and the requested features are just simpler additions. So in this new iteration, these issues are addressed and at the same time the generality is improved * switches are totally replaced with genericly typed lists so the convertion is totally pass through between database types and J types * initializing and traversing the reader are separated by means of a coroutine Please note, that the quirks in SetB treatment of BSTR (string) arrays had to be addressed again, as it was pointed out earlier: http://www.jsoftware.com/jwiki/Guides/.NET_Interop#strings It was also discovered that multidimentional arrays can be passed to SetB using Variant (object) boxing of arrays (not items). See passing of "nulls". See Also: Raul's and Alex's scripts http://www.jsoftware.com/pipermail/general/2007-February/029091.html http://www.jsoftware.com/pipermail/programming/2007-February/005290.html ============================================================[Program.cs]== using System; using System.Data.OleDb; using System.Data; using System.IO; using System.Collections.Generic; using System.Reflection; public class MyClass { public static void Main() { List<string> names = new List<string>(); List<System.Collections.IList> cols = new List<System.Collections.IList>(); List<List<bool>> nulls = new List<List<bool>>(); int totalCount = 0; try { /////////////////////////////////////// reader to vertical columns string path = Path.GetFullPath(Directory.GetCurrentDirectory() + @"\..\..\test"); string sql = string.Format("select * from test.csv where firstname <> '{0}'", "Test"); foreach (IDataReader dataReader in CsvReader(path, sql)) { if (totalCount == 0) { for (int i = 0; i < dataReader.FieldCount; i++) { cols.Add((System.Collections.IList)Activator.CreateInstance(ListType(dataReader.GetFieldType(i)))); nulls.Add(new List<bool>()); names.Add(dataReader.GetName(i)); } } for (int i = 0; i < dataReader.FieldCount; i++) { cols[i].Add(dataReader.IsDBNull(i) ? (dataReader.GetFieldType(i) == typeof(string) ? "" : Convert.ChangeType(0, dataReader.GetFieldType(i))) : dataReader.GetValue(i)); nulls[i].Add(dataReader.IsDBNull(i)); } totalCount++; } /////////////////////////////////////// test print out Console.WriteLine("\nColumns: {0}", string.Join("|", names.ToArray())); Console.WriteLine("\nVertical Table:"); cols.ForEach(delegate(System.Collections.IList col) { object[] arr = new object[col.Count]; col.CopyTo(arr, 0); Console.WriteLine(string.Join(",", Array.ConvertAll<object, string>(arr, delegate(object a) { return a.ToString(); }))); }); Console.WriteLine("\nNulls:"); nulls.ForEach(delegate(List<bool> col) { Console.WriteLine(string.Join(",", col.ConvertAll<string>(delegate(bool a) { return a ? "1" : "0"; }).ToArray())); }); /////////////////////////////////////// pass names, vertical columns and nulls to J SetB("Names", names.ConvertAll<object>(delegate(string s) { return s; }).ToArray()); Console.WriteLine("\n {0}\n{1} ", "Names", DoR("Names")); for (int i = 0; i < cols.Count; i++) { object a = ListType(cols[i][0].GetType()).InvokeMember("ToArray", BindingFlags.InvokeMethod, null, cols[i], new object[] { }); SetB(names[i], a); Console.WriteLine("\n {0}\n{1} ", names[i], DoR(names[i])); } SetB("nulls", nulls.ConvertAll<object>( delegate(List<bool> list) { return list.ToArray(); }).ToArray()); Console.WriteLine("\n {0}\n{1} ", "nulls", DoR(">nulls")); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); } Console.WriteLine("all done."); } static Type ListType(Type itemType) { if (itemType == typeof(string)) // J interface quirk to pass BSTR arrays as Variant arrays itemType = typeof(object); return typeof(List<>).MakeGenericType(itemType); } // J interface static JDLLServerLib.JDLLServerClass J = new JDLLServerLib.JDLLServerClass(); static void SetB(string name, object value) { assert(J.SetB(name, ref value)); } static object GetB(string name) { object result; assert(J.GetB(name, out result)); return result; } static object DoR(string expr) { object res; assert(J.DoR(expr, out res)); return res; } static void assert(int status) { if (status == 0) return; object result; J.ErrorTextB(status, out result); throw new ApplicationException(result as string); } public static IEnumerable<IDataReader> CsvReader(string path, string sql) { string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source={0};" + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\"", path); using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand myCommand = conn.CreateCommand(); myCommand.CommandType = CommandType.Text; myCommand.CommandText = sql; using (OleDbDataReader dataReader = myCommand.ExecuteReader()) { while (dataReader.Read()) { yield return dataReader; } } } } } ===========================================================[test.csv]== FirstName,LastName John,Doe,25,1.2 James,Fox,35,100 Adam,Smith,45,0.345 ==========================================================[schema.ini]== [test.csv] ColNameHeader=True Format=CSVDelimited MaxScanRows=25 CharacterSet=ANSI Col1=FirstName Char Width 20 Col2=LastName Char Width 20 Col3=Age Integer Col4=Value Double ======================================================================== Sample output: Columns: FirstName|LastName|Age|Value Vertical Table: John,James,Adam Doe,Fox, 25,0,45 0,100,0.345 Nulls: 0,0,0 0,0,1 0,1,0 1,0,0 Names +---------+--------+---+-----+ |FirstName|LastName|Age|Value| +---------+--------+---+-----+ FirstName +----+-----+----+ |John|James|Adam| +----+-----+----+ LastName +---+---++ |Doe|Fox|| +---+---++ Age 25 0 45 Value 0 100 0.345 nulls 0 0 0 0 0 1 0 1 0 1 0 0 all done. ____________________________________________________________________________________ Never Miss an Email Stay connected with Yahoo! Mail on your mobile. Get started! http://mobile.yahoo.com/services?promote=mail ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
