C# OLEDB Table Number of Rows and Columns

C# OLEDB Table Number of Rows and Columns

While "translating" an old Visual Basic application to Visual C# there was the need to open particular tables of an Microsoft Access database.  So I had to determine how to open a selected database table.

Then I decided that I wanted to obtain the number of rows and columns in the selected table.  I did numerous internet searches trying to determine how to do this.  And found that getting the number of columns was "deemed" impossible - that there wasn't a way of doing so.

So I was left to find the way on my own. 

The code follows.  This code also includes using an OpenFileDialog with which the user can select the Access .mdb database to be opened.

Each time the database Reader for the EditActions table of the database was Read or Load-ed the reader had to be closed and a new reader used.  Otherwise an error resulted.  That is, while numRows and numCols compiled referencing the DataTable, the ea.Load(reader) statement would result in a run-time error that the reader was closed.  It turned out that this was because the previous reader.Read() loop had exhausted the table contents having read all the rows of the table (even though there wasn't an explicit Close).

If the ea.Load statement was deleted then the numRows  would be 0 due to all the rows having previously been read while numCols would correctly have a value of 7.

Adjusting for these problems, the code first reads through the EditActions table as a result of the query (of queryString) associated with the first reader using the reader.FieldCount statement to obtain the number of columns in the table.

Then that reader is closed so that a new one can be obtained for the DataTable and loaded (i.e., "ea.Load(reader2);").  Using the ea instantiation of the DataTable, the number of rows and columns are obtained with the correct values.

However, I didn't see anyway to use the number of rows.  When I attempted to do a loop over the number of rows I couldn't find a way to read the row so as to loop over the columns.  Therefore, I closed reader2 and created reader3 to be able to do the while reader3.Read() loop once more while using the number of columns obtained via the DataTable instantiation.

I have found that there is a Data Grid View that might be suitable.  It apparently creates a way of looking at the contents of the database table without the necessity of actually reading or loading the table multiple times.  But not the subject of this post since it was about ways to obtain the number of columns in a database table.

        private void openToolStripMenuItem_Click(object sender, EventArgs e)

        {

           // Get an instance of a FileDialog.

           OpenFileDialog openFileDialog = new OpenFileDialog();

           OleDbConnection Config = new OleDbConnection();

           // Use a filter to allow only mdb files.

           openFileDialog.InitialDirectory = "c:\\";

           openFileDialog.Filter = "Edit Actions Access Database (*.mdb)|*.mdb";

           openFileDialog.FilterIndex = 2;

           openFileDialog.RestoreDirectory = true;

           // Show the dialog and get entered database.

           try

           {

              DialogResult result = openFileDialog.ShowDialog();

              if (result == DialogResult.OK) // Test result for success

              {

                if (openFileDialog.FileName != "") // name selected

                {

                    // Open selected database via OLEDB

                    string name = "Provider=Microsoft.Jet.OLEDB.4.0;";

                    name += "Data Source=";

                    name += openFileDialog.FileName;

                    name += ";";

                    OleDbConnection conConfig = new OleDbConnection(name);

                    Config = conConfig;

                    Config.Open();

                }

                else // openFileDialog.FileName = ""

                {

                    MessageBox.Show("Blank.mdb Cannot be Selected");

                    return;

                } // openFileDialog.FileName != ""

           

              }

              else if (result == DialogResult.Cancel)

              { // Cancel button was pressed.

                  return;

              } // (result = DialogResult.OK)

              // Setup a reader of the EditActions table of the Config database

              string queryString = "SELECT * FROM EditActions";

              OleDbCommand command = new OleDbCommand(queryString,Config);

              command.CommandTimeout = 20;

              OleDbDataReader reader = command.ExecuteReader();

              // Read the contents of the EditActions table and allow to be

              // viewed via the debugger.  Shows getting number of columns.

              string valueRead;

              int colCount = reader.FieldCount;

              while (reader.Read())

              {

                  int i = 0;

                  while (i < colCount)

                  {

                      valueRead = reader[i].ToString();

                      i++;

                  }

              }

              // Obtain the EditActions data table of the Config database

              reader.Close();

              OleDbDataReader reader2 = command.ExecuteReader();

              DataTable ea = new DataTable("EditActions");

              ea.Load(reader2);

              // Obtain the number of rows and columns in the selected table

              // where can examine via the debugger

              DataRowCollection rows = ea.Rows;

              DataColumnCollection columns = ea.Columns;

              int numRows = rows.Count;

              int numCols = columns.Count;

              reader2.Close();

              // Read the database table and allow the data to be viewed

              // via the debugger

              OleDbDataReader reader3 = command.ExecuteReader();

              while (reader3.Read())

              {

                  int i = 0;

                  while (i < numCols)

                  {

                      valueRead = reader3[i].ToString();

                      i++;

                  }

              }

           } // try

           finally

           {

               Config.Close();

           }

        } // openToolStripMenuItem_Click

The DialogResult result = openFileDialog.ShowDialog(); statement results in the following Form1 window where the result of selecting the Open option and navigating to the location of the database is also shown.  The result of clicking on the Form1 File control is shown immediately below.

C# Example Using Access Database

C# Example Using Access Database

This exploratory project shows two methods of accessing a Microsoft Access database.  One is from a newer example from Microsoft, as modified, and one converted from an old, old Visual Basic example.

They each use a version of the canned Microsoft Northwind database.

The much older database is Nwind.mdb using the OleDb class while the second is the Northwnd.mdf database using different access methods.  (Notice that the 'i' was left out in Microsoft's naming of this database.)

Both databases are accessed from the Microsoft Visual C# 2010 Express that I got from Microsoft years ago and use whatever a recent version of Microsoft Visual Studio that might be necessary (if anything) to interface with the Windows 7 of my PC.

In each case the SQL query is a simple one to read the Customers table of the particular version of the database to return those with an address in London.  The old Visual Basic code that had a fancier query although I don't know as I ever used it since the project got reworked to update a different non-Northwind database to maintain a database of ARINC-661 widgets for a research project for a demo for an Airbus aircraft.

Besides forming the OleDb query after all this time another problem arose.  I initially bypassed the OleDb query and copied the code from the Northwnd.mdf example from an internet search.  It had been for a console application whereas I did a Windows Forms Application.  So the example put the query results to a Console log but I wanted to see them in the Windows form widget of my project.  So I would get the next query result and copy the text to a textbox widget and when nothing appeared, to a label widget.

In neither case did anything appear in the textbox or the label although with the first output to the label's text field the original text would be shortened to fewer characters.  This was a puzzlement since I had had no trouble with other C# applications displaying values to such widgets/controls.  (Note: Widgets is A661 terminology whereas Microsoft refers to a control.)

So to get the value displayed I added a MessageBox where I was going to display the query result – that is, Customer ID and customer City.  But I found all I had to do was request the MessageBox to display some text and the query results appeared in the labels and text boxes.  So again a head scratcher. 

I later found out that to get the screen (i.e., the form) updated a second thread was needed (which the activation of the MessageBox was supplying) or else the use of a Refresh statement for the control – in this case the form.  So I took out the use of the MessageBox and used Refresh instead.  Although the MessageBox had been helpful since the user got to see the query result displayed for each customer that matched the requested city. 

It then occurred to me that the reason I hadn't had such a problem before was because I was displaying data or changing the color of buttons and the like from inside a different event handler.  The event handler, of course, runs in a Windows thread transferring control to the handler when a control is clicked or characters entered into a text box.  In the current application, everything is being done within the same event handler so the form wasn't being redisplayed since the event handler isn't being exited.  Therefore, no other event can be treated.

Without the MessageBox, the query loop runs to completion so only the last result can be seen unless the loop is stepped thru via the debugger.  (In the Microsoft Northwnd example, all the results were to be seen in the Console log that it produced.)  Therefore, I've added a one second delay to allow the user to briefly see the query results as the query proceeds.

This, of course, is only useful for the example database with a small number of matches to be found and displayed.  For a more normal application, a report would be produced and if visual results were wanted, a special control could be used to allow the initial results to be inspected and then continue and produce the report.  Perhaps a text box in which to enter the number of results to display before dispensing with the delay.  Perhaps, two such boxes with one for the delay period desired.  Both to default to just produce the report.

Another problem that occurred was that the Microsoft instructions for using the Northwnd database had the inclusion of the System.Data.Linq.Mapping class.  However, when filling this into my new C# project, the auto selection resulted in my using System.Linq and then attempting to add System.Linq.Mapping.  This stymied me for quite some time attempting to get this unit added to my project when it didn't exist.  Finally the light dawned and I used the correct class.

Below is the form that I used showing the final query result.  It still shows the effects of trying to get the query results to show since I added the label controls to the left when the text boxes weren't showing the results.  The Start button is just to delay until ready.  The searches and the display of the results are done within the event handler of the Start button.

The C# code is as follows without showing the Initialize code within the Form1.Designer.cs file.

Program.cs

using System;

using System.Collections.Generic;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Windows.Forms;

namespace WindowsFormsApplication1

{

    [Table(Name = "Customers")]

    public class Customer

    {

      /*In this step, you accomplish several tasks.

        · You use the ColumnAttribute attribute to designate CustomerID

          and City properties on the entity class as representing columns

          in the database table.

        · You designate the CustomerID property as representing a primary key

          column in the database.

        · You designate _CustomerID and _City fields for private storage.

          LINQ to SQL can then store and retrieve values directly, instead

          of using public accessors that might include business logic.

        To represent characteristics of two database columns */

        private string _CustomerID;

        [Column(IsPrimaryKey = true, Storage = "_CustomerID")]

        public string CustomerID

        {

            get

            {

                return this._CustomerID;

            }

            set

            {

                this._CustomerID = value;

            }

        }

        private string _City;

        [Column(Storage = "_City")]

        public string City

        {

            get

            {

                return this._City;

            }

            set

            {

                this._City = value;

            }

        }

    }

    static class Program

    {

        /// <summary>

        /// The main entry point for the application.

        /// </summary>

        [STAThread]

        static void Main()

        {

            Application.EnableVisualStyles();

            Application.SetCompatibleTextRenderingDefault(false);

            Application.Run(new Form1());

        }

    }

}

Form1.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.OleDb;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        { // Start button

            /* This initial code queries the Nwind.mdb database of Microsoft

             * using OleDb methodology.

             */

            string con = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\\Source\\VB\\Nwind.mdb";

            OleDbConnection connection = new OleDbConnection(con);

            string queryString = "SELECT CustomerID, City FROM Customers";

            queryString += " WHERE City = 'London'";

            OleDbCommand command = new OleDbCommand(queryString,connection);

            command.CommandTimeout = 20;

            connection.Open();

            OleDbDataReader reader = command.ExecuteReader();

            string valueRead;

            while (reader.Read())

            {

                valueRead = reader[0].ToString();

                this.label3.Text = valueRead;

                valueRead = reader[1].ToString();

                this.label4.Text = valueRead;

                this.Refresh();

                System.Threading.Thread.Sleep(1000);

            }

//          reader.Close();

            /* This code queries the northwnd.mdf database of Microsoft.

             * A portion of code is in the Customer class in Program.cs.

             */

            // Use a connection string.

            DataContext db = new DataContext

                (@"c:\linqtest5\northwnd.mdf");

            // Get a typed table to run queries - the Customer class.

            Table<Customer> Customers = db.GetTable<Customer>();

            // Attach the log to show generated SQL.

            db.Log = Console.Out;

            // Query for customers in London.

            IQueryable<Customer> custQuery =

                from cust in Customers

                where cust.City == "London"

                select cust;

            foreach (Customer cust in custQuery)

            {

                string val1 = cust.CustomerID;

                this.textBox1.Text = val1;

                this.label3.Text = cust.CustomerID;

                val1 = cust.City;

                this.textBox2.Text = val1;

                this.label4.Text = cust.City;

                this.Refresh();

                System.Threading.Thread.Sleep(1000);

            }

        }

    }

}

Note: The query results could have been directly copied to the label and text box controls.  The use of the extra string values was when I was trying nonsense trying to get the values to display.