Revit.SDK.Samples.RoomSchedule.XlsDBConnector.GenDataTable C# (CSharp) Method

        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();
            }
        }