My objective is to connect to an mdb database (stored locally), go through it and get the following information:
-The names of all the tables present in the database
-The name of the columns AND their type in each table of the database.
While developing the code I used the code indicated in the answer to the following stackoverflow question
need to find the type of the column of DB
I implemented the code written in the accepted answer and tailored it to my needs. Here is what I have so far:
DataSet dtImportedTables = new DataSet();
Globals.strSQLQuery = "SELECT * FROM {0}";
//try
//{
Globals.conConnection = new OleDbConnection(Globals.strConnection);
Globals.conConnection.Open();
foreach (DataRow row in Globals.tblSchemaTable.Rows)
{
DataTable dt = new DataTable();
OleDbCommand command = new OleDbCommand(String.Format(Globals.strSQLQuery, row["TABLE_NAME"] as String), Globals.conConnection);
dt.Load(command.ExecuteReader(CommandBehavior.SchemaOnly));
dtImportedTables.Tables.Add(dt);
}
Globals.conConnection.Close();
string temp="";
int k;
foreach (DataTable dt in dtImportedTables.Tables)
{
k = 0;
foreach (DataColumn dc in dt.Columns)
{
Globals.arrColumnNamesList[k] = dc.ColumnName;
Globals.arrColumnTypesList[k] = dc.DataType.ToString();
temp += k+ ") "+Globals.arrColumnNamesList[k] + Environment.NewLine + Globals.arrColumnTypesList[k] + Environment.NewLine;
k++;
}
}
Note the following:
-I am using Visual Studio 2012 and this is a Windows Form App, programmed in C#
-The connection works fine (according to debug at least), and the database in question is made up of just one table containing several columns.
-The full debug description of the error is: $exception{"Object reference not set to an instance of an object."} System.Exception {System.NullReferenceException}
-Both Globals.arrColumnTypesList and Globals.arrColumnNamesList are arrays of type string.
-On the third instruction line I commented out the "try" section for debug purposes (i.e. if left intact the program would simply not execute the whole section because of the error I am inquiring about, thus not letting the debugger warn me of the error).
-I had to modify both foreach statements from the original version found in the stackoverflow question mentioned above. As it was written the compiler would give me an error.
-The problem occurs here:
Globals.arrColumnTypesList[k] = dc.DataType.ToString();
While looking through other questions about this exception, i found that it is thrown typically if the expression returns a NULL value (in this case, as I understand it, it would mean that dc.DataType is NULL). However, in the debug tool of VisualStudio, DataType is not NULL at all, it contains in fact the value {"System.String"}. I figured that the ToString() method could have been the cause of this strange behaviour so I changed the expression to the following:
Globals.arrColumnTypesList[k] = dc.DataType.Name;
It gave me the same error and this time dc.DataType.Name had a value of "String", clearly of type string.
Can anybody help me figure out what is going on? Is the debugger perhaps misleading me indicating the error is on that line while the real problem could be someplace else?
Most likely, you never initialize Globals.arrColumnTypesList. Try something like this before the loop:
Globals.arrColumnTypesList = new List<string>();
Or (depending on the type of arrColumnTypesList):
Globals.arrColumnTypesList = new string[dt.Columns.Count];