public DataTable GenDataTable(String tableName)
{
// Get all data via command and then fill data to table
string strCom = "Select * From [" + tableName + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, m_objConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[" + tableName + "$]");
try
{
// check to see whether the constant columns(defined in RoomsData class) exist in spread sheet.
// These columns are necessary when updating spread sheet
// define a flag variable to remember whether column is found
// duplicate column is not allowed in spreadsheet
bool[] bHasColumn = new bool[5];
Array.Clear(bHasColumn, 0, 5); // clear the variable to false
// five constant columns which must exist and to be checked
String[] constantNames = { RoomsData.RoomID, RoomsData.RoomName,
RoomsData.RoomNumber, RoomsData.RoomArea, RoomsData.RoomComments };
// remember all duplicate columns, used to pop up error message
String duplicateColumns = String.Empty;
for (int i = 0; i < myDataSet.Tables[0].Columns.Count; i++)
{
// get each column and check it
String columnName = myDataSet.Tables[0].Columns[i].ColumnName;
// check whether there are expected columns one by one
for (int col = 0; col < bHasColumn.Length; col++)
{
bool bDupliate = CheckSameColName(columnName, constantNames[col]);
if (bDupliate)
{
if (false == bHasColumn[col])
{
bHasColumn[col] = true;
}
else
{
// this column is duplicate, reserve it
duplicateColumns += String.Format("[{0}], ", constantNames[col]);
}
}
}
}
// check to see whether there are duplicate columns
if (duplicateColumns.Length > 0)
{
// duplicate columns are not allowed
String message = String.Format("There are duplicate column(s) in the spread sheet: {0}.", duplicateColumns);
throw new Exception(message);
}
// check whether all required columns are there.
String missingColumns = String.Empty; // reserve all column names which are missing.
for (int col = 0; col < bHasColumn.Length; col++)
{
if (bHasColumn[col] == false)
{
missingColumns += String.Format("[{0}], ", constantNames[col]);
}
}
// check to see whether any required columns are missing.
if (missingColumns.Length != 0)
{
// some columns are missing, pop up these column names
String message = String.Format("Required columns are missing: {0}.", missingColumns);
throw new Exception(message);
}
// if no exception occurs, return the table of dataset directly
return myDataSet.Tables[0];
}
catch (Exception ex)
{
// throw exception
throw new Exception(ex.Message);
}
}