C# .NET - convert ArrayList to Array

The ultimate goal of this is to load a csv (two columns) into an array.

I ended up with this because I could not figure out how to re-dimension the size of the array at run time. The amount of items in the csv file will increase.

I ended up using an ArrayList. However I cannot get the data to go into an Array. Here is my code:

TextReader reader = new StreamReader(@"c:\clientbills\current accounts.csv");
            ArrayList accounts = new ArrayList();

            while (reader.Peek() != -1)
            {
                accounts.Add( reader.ReadLine().Split(','));
            }
            reader.Close();
            string[,] aAccounts = (string[(accounts.Count), 2]) accounts.ToArray(typeof(string));

Any suggestions??

What are you doing with the data once you've read it from the file?

I'm going to to match it to another array and end up with an array with three columns.

Instead of using a TextReader to read you CSV file, you can use OleDbConnection and OleDbCommand to load the CSV file into a DataTable.  With a DataTable, you will also know how many records are in the CSV file.  You can loop through the DataTable to convert it to an array"

System.Data.OleDb.OleDbConnection connection = null;

try {
   string CSVFile = @"c:\clientbills\current accounts.csv";

   connection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
System.IO.Path.GetDirectoryName( CSVFile ) + @";Extended
Properties=""Text;HDR=NO;FMT=Delimited""" );

   System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand( "SELECT * FROM " + CSVFile, connection );

   System.Data.OleDb.OleDbDataAdapter adapter = new
System.Data.OleDb.OleDbDataAdapter( command );

   System.Data.DataTable dataTable = new System.Data.DataTable("Accounts");

   connection.Open();
   adapter.Fill( dataTable);

}

From a dataTable, you should be able to declare your array and loop through the dataTable to copy the contents to your 2-dimensional array.

Regards,
http://www.sql-server-helper.com/functions/get-tree-node-level.aspx

If you are facing with the issue with dimension not known before hand you can simply use dynamic string array

string[] cols = reader.ReadLine().Split(',');

Depending on how you are going to be using the data, using a collection of objects would be more flexible.

Here is an example of using a collection/list that holds instances of an object.  You can bind Lists to forms, etc. so this approach is a practical one if you are going to be displaying the data from the txt file.

 

public class clsHouse
{
	public clsHouse() {	}
    public clsHouse(string address, decimal cost, string seller)
    {
        _address = address; _cost = cost; _seller = seller;
    }
    private string _address = string.Empty;
    private decimal _cost = 0;
    private string _seller = string.Empty;
    public string HouseAddress {
        get { return _address; }
        set { _address = value; }
    }
    public decimal HouseCost {
        get {return _cost;}
        set {_cost = value; }
    }
    public string ListingAgent
    {
        get { return _seller; }
        set { _seller = value; }
    }
}

Now you can populate a collection with "house" objects.

       //create the collection
        List houses = new List();
        clsHouse home = null;
        for (int i = 1; i < 50; i++)
        {
            home = new clsHouse();
            home.HouseAddress = (100 + i).ToString() + " Somestreet rd.";
            home.HouseCost = Convert.ToDecimal(500000 + ((i + 250) * 1.000));
            home.ListingAgent = "Some Person";
            houses.Add(home);
        }
        foreach (clsHouse house in houses)
        {
            Response.Write(house.HouseAddress);
        }

So, if you have a file that contains accounts, and you will have the account name, number, etc.. All of these can be assigned to an object that is type safe (once you convert from string to the proper type) and can access them, manipulate them, implement custom comparers for sorting etc.. Anyway, hope this helps.

Here is a breakdown of my process.

I have a access database that I will call ClientInfo.
I have a csv file that I will call csvAllClients.

Inside of ClientInfo is a pre-defined query called "qryPrintBills". It has over 20 columns of data and about 2500 rows. I only care about 3 of the columns. Column1 is a page number. Column2 is an account number. Column3 is the master account.
Inside of csvAllClients is two columns of data and about 160 rows of information. Column1 has an account number, and column2 is the master account that the account number belongs to.

ClientInfo is dynamically generated each month while csvAllClients is rather static. Occasionally things get added to it.

What I need to do is compare csvAllClients to ClientInfo so that I can find out what page number belongs to an account/master account combination.

I then use that data to export a specific page number from a crystal report that uses ClientInfo as its data source.

Does that make sense?

Sort of...

I guess i'm a bit confused by the process, and while I question it, I know you're looking for a solution, not a lecture.

Can you insert data into Access? Or is it a read-only db?

I ask because it would be far easier to create a temp table that is populated from your csv file, join the two tables and create one useable query with the information you need.

If this is not an option, I would populate a collection, actually I would probably use a datatable instead with 4 or 5 columns (depends on what the primary keys are made up of) and populate it from the access db.  Add one or two additional columns that match the type/name from the CSV file and default them to null.  Once you have this data table populated, you can loop through the text file (which your doing now anyway) and populating the empty columns by matching the account number from the csv file to the account number from the datatable.  Essentially making an in-memory table join based on both the access db and csv file.  This should give you all the info you need.

Is this an option for you?  Ideally all of this would reside in the same DB.  I'm not sure of a better way to do this reliably or more efficiently.  You can sort an do mini select's on the data table, and then use a reader or bind it to a control.

Either way, you will need to compare the keys from the csv and the access db to sync records, but putting it in a data table seems a bit more manageable and "normal" that doing a bunch of compares on string arrays.