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.
No comments:
Post a Comment