c# populate array from dataset

[office politics]

im working on code to view a certain row from an access db. the db has one number and serveral text columns. I need to figure out a way to output the dataset to the console screen. This is what ive been working on.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Xml.Serialization;

namespace csamuels
{
    class Agents
    {
        /*private Agents() {

        }*/

        static void Main(string[] args)
        {
            string dbfile = "C:\\cdata.mdb";
            string dbtable = "Contact";
            string picked = "";
            Agents app = new Agents();

            while (picked != "4") {
                picked = menu();
                switch (picked) {
                    case "1":
                        string code = "";
                        Console.WriteLine("This is View Code");
                        Console.WriteLine("Enter a four digit agent code: ");
                        code = Console.ReadLine();
                        app.viewcode(code, dbfile, dbtable);
                        break;
                    case "2":
                        Console.WriteLine("This is Create Code");
                        Console.ReadLine();
                        break;
                    case "3":
                        Console.WriteLine("This is Modify Code");
                        Console.ReadLine();
                        break;
                    case "4":
                        Console.WriteLine("Terminating Program.");
                        break;
                    default:
                        Console.WriteLine("Invalid Selection, Press Enter To Return");
                        Console.ReadLine();
                        //Console.WriteLine("Enter Pressed.");
                        break;
                }
            }
        }

        static string menu() 
        {
            string pick = "";
            Console.Clear();
            Console.WriteLine("1: View Code\n2: Create Code\n3: Modify Code\n4: Exit");
            Console.Write("\nSelect Option: ");
            pick = Console.ReadLine();
            Console.WriteLine("You chose: {0}", pick);
            Console.Clear();
            return pick;
        }

        private void viewcode(string code, String dbfile, string dbtable) {
            DataSet dbdata = new DataSet();
            Console.Clear();
            string dbselect = "Select * from " + dbtable + " where code = " + code;
            dbdata = opendb(dbfile, dbtable, dbselect);
            DataColumnCollection tblcols = dbdata.Tables[dbtable].Columns;
            DataRowCollection tblrows = dbdata.Tables[dbtable].Rows;
            //ArrayList colsarr = new ArrayList();
            /*int i = 0;
            foreach (DataColumn cl in tblcols)
            {
                colsarr[i] = cl.ColumnName;
                i++;
            }*/
            //ArrayList rowarr = new ArrayList();
            //i = 0;
            //foreach (DataRow rw in tblrows) {
            //    rowarr[i] = rw[i];
            //    i++;
            //}
            string[] colsarr = new string[dbdata.Tables[dbtable].Columns.Count];
            tblcols.CopyTo(colsarr, 0);
            string[] rowsarr = new string[dbdata.Tables[dbtable].Rows.Count];
            tblrows.CopyTo(rowsarr, 0);
            for (int num = dbdata.Tables[dbtable].Columns.Count - 1; num > 0; num--)
            {
                //Debug
                //Console.WriteLine("Count: {0} \tnum: {1}\tcap: {2}", dbdata.Tables[dbtable].Columns.Count, num, );
                //Console.ReadLine();
                //Console.WriteLine("{0} = {1}", colsarr.RemoveAt(num), rowarr.RemoveAt(num));
                
                Console.WriteLine("{0} = {1}", colsarr[num], rowsarr[num]);
            }
            Console.ReadLine();
        }

        private DataSet opendb(string dbfile, string dbtable, string dbselect)
        { 
            string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbfile;

            string strAccessSelect = dbselect;

            // Create the dataset and add the Categories table to it:
            DataSet myDataSet = new DataSet();
            OleDbConnection myAccessConn = null;
            try
            {
                myAccessConn = new OleDbConnection(strAccessConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                return null;
            }

            try
            {

                OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

                myAccessConn.Open();
                myDataAdapter.Fill(myDataSet, dbtable);

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
                return null;
            }
            finally
            {
                myAccessConn.Close();                
            }
            return myDataSet;
        }
    }
}

im having trouble with (in viewcode method)

            string[] colsarr = new string[dbdata.Tables[dbtable].Columns.Count];
            tblcols.CopyTo(colsarr, 0);

because

Unhandled Exception: System.InvalidCastException: At least one element in the so
urce array could not be cast down to the destination array type.
   at System.Array.Copy(Array sourceArray, Int32 sourceIndex, Array destinationA
rray, Int32 destinationIndex, Int32 length, Boolean reliable)

its prolly that number column that screwing me over. anyone have a better way of dumping the info to the screen?

 

[office politics]

ima give this a go. seems logical.

http://weblogs.asp.net/erobillard/archive/2003/11/16/37832.aspx

Because of this:
"At least one element in the source array could not be cast down to the destination array type."

It happens on the: dc.CopyTo(columns,0);

The problem is that SQL Server allows nulls but .NET does not. I tried adjusting your suggestion to use a System.Data.SqlTypes.SqlString array instead, but that just stalls the problem until the Join, as SqlString doesn't provide a Join method.

If there were a way to convert the array from SqlString to String we might be in business, but then we're getting back into less efficient, less readable territory.

Which brings us back to the posted solution. It works fine here, but is not complete if the DataTable contains non-string types like number or dates. Really, there should be a switch (column.GetType()) involved to wrap strings in quotation marks, run numbers through .ToString() and format dates.

Another solution is not to use a dataset at all, but an object class with a ToStringArray method. That makes a Split / Join solution a cinch, and would be an easy thing to add for anyone using CodeSmith (which rocks!). ToStringArray would be a useful method to have around. But the queries I use to produce CDFs, like most reports, are the result of JOINs and I'm not about to generate a data access class for every query I write.

So, back to the posted solution. Any other ideas?

Take care,
Eli.