public GenDataTable ( String tableName ) : |
||
tableName | String | Table name to be retrieved |
return |
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);
}
}
/// <summary> /// Select one table(work sheet) and display its data to DataGridView control. /// after selection, generate data table from data source /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tablesComboBox_SelectedIndexChanged(object sender, EventArgs e) { // update spread sheet based rooms sheetDataGridView.DataSource = null; m_roomTableName = tablesComboBox.SelectedValue as String; XlsDBConnector xlsCon = null; try { if (null != m_spreadRoomsTable) { m_spreadRoomsTable.Clear(); } // get all rooms table then close this connection immediately xlsCon = new XlsDBConnector(m_dataBaseName); // generate room data table from room work sheet. m_spreadRoomsTable = xlsCon.GenDataTable(m_roomTableName); newRoomButton.Enabled = (0 == m_spreadRoomsTable.Rows.Count) ? false : true; // close connection xlsCon.Dispose(); // update data source of DataGridView sheetDataGridView.DataSource = new DataView(m_spreadRoomsTable); } catch (Exception ex) { // close connection and update data source xlsCon.Dispose(); sheetDataGridView.DataSource = null; MyMessageBox(ex.Message, MessageBoxIcon.Warning); return; } // update the static s_DocMapDict variable when user changes the Excel and room table int hashCode = m_document.GetHashCode(); if (CrtlApplication.EventReactor.DocMonitored(hashCode)) { // update spread sheet to which document is being mapped. CrtlApplication.EventReactor.UpdateSheeInfo(hashCode, new SheetInfo(m_dataBaseName, m_roomTableName)); // update current mapped room sheet information, only show this when Revit rooms were mapped to Excel sheet. UpdateRoomMapSheetInfo(); } }