// ////////////////////////////////////////////////////////////////////////
        // INITIAL EVENTS
        //
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                // Validate query string
                if (((string)Request.QueryString["source_page"] == null) || ((string)Request.QueryString["client_id"] == null) || ((string)Request.QueryString["project_id"] == null))
                {
                    Response.Redirect("./../../error_page.aspx?error=" + "Invalid query string in wincan_bulk_upload.aspx");
                }

                // Tag Page
                TagPage();

                // Prepare initial data
                Session.Remove("wincanBulkUpload");

                // Initialize viewstate variables
                ViewState["StepFrom"] = "Out";

                // ... Initialize tables
                wincanBulkUpload = new WincanBulkUploadTDS.WincanBulkUploadDataTable();

                // ... Store tables
                Session["wincanBulkUpload"] = wincanBulkUpload;

                // StepSection1In
                wzBulkUpload.ActiveStepIndex = 0;
                StepBeginIn();

            }
            else
            {
                // Restore tables
                wincanBulkUpload = (WincanBulkUploadTDS.WincanBulkUploadDataTable)Session["wincanBulkUpload"];
            }

            // control for postback
            hdfTag.Value = DateTime.Now.ToLongTimeString();
        }
        // /////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        // STEP1 - BEGIN
        //
        // ////////////////////////////////////////////////////////////////////////
        // STEP1 - BEGIN - METHODS
        //
        private void StepBeginIn()
        {
            // Set instruction
            Label instruction = (Label)this.Master.FindControl("lblInstruction");
            instruction.Text = "Please select a Microsoft Access file";

            //--- Initialize
            WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
            dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);
            WincanBulkUpload model = new WincanBulkUpload(dataSet);
            model.Table.Rows.Clear();
            wincanBulkUpload = dataSet.WincanBulkUpload;
            Session["wincanBulkUpload"] = dataSet.WincanBulkUpload;
        }
        private bool ProcessBulkUpload(string fName, out string wincanBulkUploadResultMessage)
        {
            bool wincanBulkUploadProccessed = true;
            wincanBulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();
            //string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Data Source=" + fName + ";";
            string accessConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + fName + ";";

            //OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbConnection connection = new OleDbConnection(accessConnectionString);
            string sql = "SELECT SI_T.SI_InspName AS SectionID, SO_T.SO_OpCode AS State, SI_T.SI_InspectionDir AS Direction, SI_T.SI_InspectedLengthFeet AS Distance, SO_T.SO_PositionFeet AS VideoDistance, SO_T.SO_ClockPosition1 AS ClockPosition "+
                            " , SO_T.SO_Dim1 AS DistanceToCentre, SO_T.SO_ToGoFeet AS ReverseSetup, SO_T.SO_Remark AS Comments" +
                            " FROM SI_T INNER JOIN SO_T ON SI_T.SI_ID = SO_T.SO_Inspecs_ID;";
            OleDbCommand command = null;

            OleDbDataReader dataReader = null;

            try
            {
                ArrayList validatedIds = new ArrayList();

                connection.Open();
                command = new OleDbCommand(sql, connection);
                dataReader = command.ExecuteReader();

                int id = 0;

                while (dataReader.Read())
                {
                    if (!IsEmptyRow(dataReader))
                    {
                        string sectionId = "";
                        string state = "";
                        string distance = "";
                        string direction = "";
                        string videoDistance = "";
                        string clockPosition = "";
                        string distanceToCentre = "";
                        string reverseSetup = "";
                        string comments = "";

                        string dataCell = null;
                        string dataCellToUpper = null;

                        //--- ... fill section row
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            dataCell = dataReader.GetValue(i).ToString().Trim();
                            dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper();

                            switch (dataReader.GetName(i).Trim())
                            {
                                case "SectionID":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        sectionId = dataCell;
                                    }
                                    else
                                    {
                                        sectionId = "";
                                        wincanBulkUploadProccessed = false;
                                    }
                                    break;

                                case "State":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        state = dataCell;
                                    }
                                    else
                                    {
                                        state = "Undefined";
                                    }
                                    break;

                                case "Direction":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        direction = dataCell;
                                    }
                                    else
                                    {
                                        direction = "U";
                                    }
                                    break;

                                case "Distance":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            distance = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'Distance' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        distance = "";
                                    }
                                    break;

                                case "VideoDistance":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            videoDistance = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'VideoDistance' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        videoDistance = "";
                                    }
                                    break;

                                case "ClockPosition":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        clockPosition = dataCell;
                                    }
                                    else
                                    {
                                        clockPosition = "";
                                    }
                                    break;

                                case "DistanceToCentre":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            distanceToCentre = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'DistanceToCentre' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        distanceToCentre = "";
                                    }
                                    break;

                                case "ReverseSetup":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            reverseSetup = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'ReverseSetup' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        reverseSetup = "";
                                    }
                                    break;

                                case "Comments":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        comments = dataCell;
                                    }
                                    else
                                    {
                                        comments = "";
                                    }
                                    break;

                                default:
                                    wincanBulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range.";
                                    wincanBulkUploadProccessed = false;
                                    break;
                            }

                            if (!wincanBulkUploadProccessed)
                            {
                                break;
                            }
                        }

                        if (wincanBulkUploadProccessed)
                        {
                            //--- Initialize
                            WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
                            dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);
                            WincanBulkUpload model = new WincanBulkUpload(dataSet);
                            id = id + 1;
                            model.Insert(id.ToString(), sectionId, state, direction, distance, videoDistance, clockPosition, distanceToCentre, reverseSetup, comments);
                            wincanBulkUpload = dataSet.WincanBulkUpload;
                            Session["wincanBulkUpload"] = dataSet.WincanBulkUpload;
                        }
                    }
                }

                dataReader.Close();
                connection.Close();
            }
            catch (Exception ex)
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                }

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                throw ex;
            }

            return (wincanBulkUploadProccessed) ? true : false;
        }