ExecuteCommnand() public method

Execute SQL command, such as: update and insert
public ExecuteCommnand ( String strCmd ) : int
strCmd String command to be executed
return int
Example #1
0
        /// <summary>
        /// Update mapped spread sheet when document is about to be saved or saved as
        /// This method will update spread sheet room data([Area] column) with actual area value of mapped Revit Room.
        /// or add Revit room to spreadsheet if it is not mapped to room of spreadsheet.        /// </summary>
        /// <param name="activeDocument">Current active document.</param>
        private void UpdateMappedSpreadsheet(Document activeDocument)
        {
            // Programming Routines:
            //
            // 1: Update spreadsheet when:
            //    a: there is room work sheet table;
            //    b: there is rooms data;
            //    c: shared parameter exists;
            // 2: Skip update and insert operations for below rooms:
            //    a: the rooms are not placed or located;
            //    b: the rooms whose shared parameter(defined by sample) are not retrieved,
            //       some rooms maybe don't have shared parameter at all, despite user create for Rooms category.
            // 3: Update spreadsheet rooms values by Revit room actual values.
            //    a: if shared parameter exists(is not null), update row by using this parameter's value;
            //    b: if shared parameter doesn't exist (is null), update row by Id value of room, which will avoid the duplicate
            //       ID columns occur in spreadsheet.
            // 4: Insert Revit rooms data to spreadsheet if:
            //    a: failed to update values of rooms (maybe there no matched ID value in spread sheet rows).
            //

            #region Check Whether Update Spreadsheet Data
            //
            // check which table to be updated.
            SheetInfo mappedXlsAndTable;
            bool      hasValue = m_docMapDict.TryGetValue(activeDocument.GetHashCode(), out mappedXlsAndTable);
            if (!hasValue || null == mappedXlsAndTable ||
                String.IsNullOrEmpty(mappedXlsAndTable.FileName) || String.IsNullOrEmpty(mappedXlsAndTable.SheetName))
            {
                DumpLog("This document isn't mapped to spreadsheet yet.");
                return;
            }

            // retrieve all rooms in project(maybe there are new rooms created manually by user)
            RoomsData roomData = new RoomsData(activeDocument);
            if (roomData.Rooms.Count <= 0)
            {
                DumpLog("This document doesn't have any room yet.");
                return;
            }
            #endregion

            // create a connection and update values of spread sheet
            int            updatedRows = 0; // number of rows which were updated
            int            newRows     = 0; // number of rows which were added into spread sheet
            XlsDBConnector dbConnector = new XlsDBConnector(mappedXlsAndTable.FileName);

            // check whether there is room table.
            // get all available rooms in current document once more
            int stepNo = -1;
            DumpLog(System.Environment.NewLine + "Start to update spreadsheet room......");
            foreach (Room room in roomData.Rooms)
            {
                // check Whether We Update This Room
                stepNo++;
                double roomArea   = 0.0f;
                String externalId = String.Empty;
                if (!ValidateRevitRoom(activeDocument, room, ref roomArea, ref externalId))
                {
                    DumpLog(String.Format("#{0}--> Room:{1} was skipped.", stepNo, room.Number));
                    continue;
                }

                // try to update
                try
                {
                    #region Update Spreadsheet Room
                    // flag used to indicate whether update is successful
                    bool bUpdateFailed = false; // reserve whether this room updated successfully.
                    // if room comment is empty, use <null> for mapped room, use <Added from Revit> for not mapped room in spread sheet.
                    bool bCommnetIsNull = false;

                    // get comments of room
                    String    comments;
                    Parameter param = room.get_Parameter(BuiltInParameter.ALL_MODEL_INSTANCE_COMMENTS);
                    comments = (null != param) ? (param.AsString()) : ("");
                    if (String.IsNullOrEmpty(comments))
                    {
                        // this room doesn't have comment value
                        bCommnetIsNull = true;
                        // use <null> for room with empty comment by default when updating spread sheet
                        comments = "<null>";
                    }

                    // create update SQL clause,
                    // when filtering row to be updated, use Room.Id.IntegerValue if "External Room ID" is null.
                    String updateStr = String.Format(
                        "Update [{0}$] SET [{1}] = '{2}', [{3}] = '{4}', [{5}] = '{6}', [{7}] = '{8:N3}' Where [{9}] = {10}",
                        mappedXlsAndTable.SheetName, // mapped table name
                        RoomsData.RoomName, room.Name,
                        RoomsData.RoomNumber, room.Number,
                        RoomsData.RoomComments, comments,
                        RoomsData.RoomArea, roomArea,
                        RoomsData.RoomID, String.IsNullOrEmpty(externalId) ? room.Id.IntegerValue.ToString() : externalId);

                    // execute the command and check the size of updated rows
                    int afftectedRows = dbConnector.ExecuteCommnand(updateStr);
                    if (afftectedRows == 0)
                    {
                        bUpdateFailed = true;
                    }
                    else
                    {
                        // count how many rows were updated
                        DumpLog(String.Format("#{0}--> {1}", stepNo, updateStr));
                        updatedRows += afftectedRows;

                        // if "External Room ID" is null but update successfully, which means:
                        // in spreadsheet there is existing row whose "ID" value equals to room.Id.IntegerValue, so we should
                        // set Revit room's "External Room ID" value to Room.Id.IntegerValue for consistence after update .
                        if (String.IsNullOrEmpty(externalId))
                        {
                            SetExternalRoomIdToRoomId(room);
                        }
                    }
                    #endregion


                    #region Insert Revit Room

                    // Add this new room to spread sheet if fail to update spreadsheet
                    if (bUpdateFailed)
                    {
                        // try to insert this new room to spread sheet, some rules:
                        // a: if the "External Room ID" exists, set ID column to this external id value,
                        //    if the "External Room ID" doesn't exist, use the actual Revit room id as the ID column value.
                        // b: use comments in room if room's description exists,
                        //    else, use constant string: "<Added from Revit>" for Comments column in spreadsheet.

                        String insertStr =
                            String.Format("Insert Into [{0}$] ([{1}], [{2}], [{3}], [{4}], [{5}]) Values('{6}', '{7}', '{8}', '{9}', '{10:N3}')",
                                          mappedXlsAndTable.SheetName,                                                           // mapped table name
                                          RoomsData.RoomID, RoomsData.RoomComments, RoomsData.RoomName, RoomsData.RoomNumber, RoomsData.RoomArea,
                                          (String.IsNullOrEmpty(externalId)) ? (room.Id.IntegerValue.ToString()) : (externalId), // Room id
                                          (bCommnetIsNull || String.IsNullOrEmpty(comments)) ? ("<Added from Revit>") : (comments),
                                          room.Name, room.Number, roomArea);

                        // try to insert it
                        afftectedRows = dbConnector.ExecuteCommnand(insertStr);
                        if (afftectedRows != 0)
                        {
                            // remember the number of new rows
                            String succeedMsg = String.Format("#{0}--> Succeeded to insert spreadsheet Room - Name:{1}, Number:{2}, Area:{3:N3}",
                                                              stepNo, room.Name, room.Number, roomArea);
                            DumpLog(succeedMsg);
                            newRows += afftectedRows;

                            // if the Revit room doesn't have external id value(may be a room created manually)
                            // set its "External Room ID" value to Room.Id.IntegerValue, because the room was added/mapped to spreadsheet,
                            // and the value of ID column in sheet is just the Room.Id.IntegerValue, we should keep this consistence.
                            if (String.IsNullOrEmpty(externalId))
                            {
                                SetExternalRoomIdToRoomId(room);
                            }
                        }
                        else
                        {
                            DumpLog(String.Format("#{0}--> Failed: {1}", stepNo, insertStr));
                        }
                    }
                    #endregion
                }
                catch (Exception ex)
                {
                    // close the connection
                    DumpLog(String.Format("#{0}--> Exception: {1}", stepNo, ex.Message));
                    dbConnector.Dispose();
                    RoomScheduleForm.MyMessageBox(ex.Message, MessageBoxIcon.Warning);
                    return;
                }
            }
            // close the connection
            dbConnector.Dispose();

            // output the affected result message
            String sumMsg = String.Format("{0}:[{1}]: {2} rows were updated and {3} rows were added into successfully.",
                                          Path.GetFileName(mappedXlsAndTable.FileName), mappedXlsAndTable.SheetName, updatedRows, newRows);
            DumpLog(sumMsg);
            DumpLog("Finish updating spreadsheet room." + System.Environment.NewLine);
        }
Example #2
0
        /// <summary>
        /// Update mapped spread sheet when document is about to be saved or saved as
        /// This method will update spread sheet room data([Area] column) with actual area value of mapped Revit Room.
        /// or add Revit room to spreadsheet if it is not mapped to room of spreadsheet.        /// </summary>
        /// <param name="activeDocument">Current active document.</param>
        private void UpdateMappedSpreadsheet(Document activeDocument)
        {
            // Programming Routines:
            //
            // 1: Update spreadsheet when:
            //    a: there is room work sheet table;
            //    b: there is rooms data;
            //    c: shared parameter exists;
            // 2: Skip update and insert operations for below rooms:
            //    a: the rooms are not placed or located;
            //    b: the rooms whose shared parameter(defined by sample) are not retrieved,
            //       some rooms maybe don't have shared parameter at all, despite user create for Rooms category.
            // 3: Update spreadsheet rooms values by Revit room actual values.
            //    a: if shared parameter exists(is not null), update row by using this parameter's value;
            //    b: if shared parameter doesn't exist (is null), update row by Id value of room, which will avoid the duplicate
            //       ID columns occur in spreadsheet.
            // 4: Insert Revit rooms data to spreadsheet if:
            //    a: failed to update values of rooms (maybe there no matched ID value in spread sheet rows).
            //

            #region Check Whether Update Spreadsheet Data
            //
            // check which table to be updated.
            SheetInfo mappedXlsAndTable;
            bool hasValue = m_docMapDict.TryGetValue(activeDocument.GetHashCode(), out mappedXlsAndTable);
            if (!hasValue || null == mappedXlsAndTable ||
                String.IsNullOrEmpty(mappedXlsAndTable.FileName) || String.IsNullOrEmpty(mappedXlsAndTable.SheetName))
            {
                DumpLog("This document isn't mapped to spreadsheet yet.");
                return;
            }

            // retrieve all rooms in project(maybe there are new rooms created manually by user)
            RoomsData roomData = new RoomsData(activeDocument);
            if (roomData.Rooms.Count <= 0)
            {
                DumpLog("This document doesn't have any room yet.");
                return;
            }
            #endregion

            // create a connection and update values of spread sheet
            int updatedRows = 0; // number of rows which were updated
            int newRows = 0; // number of rows which were added into spread sheet
            XlsDBConnector dbConnector = new XlsDBConnector(mappedXlsAndTable.FileName);

            // check whether there is room table.
            // get all available rooms in current document once more
            int stepNo = -1;
            DumpLog(System.Environment.NewLine + "Start to update spreadsheet room......");
            foreach (Room room in roomData.Rooms)
            {
                // check Whether We Update This Room
                stepNo++;
                double roomArea = 0.0f;
                String externalId = String.Empty;
                if (!ValidateRevitRoom(activeDocument, room, ref roomArea, ref externalId))
                {
                    DumpLog(String.Format("#{0}--> Room:{1} was skipped.", stepNo, room.Number));
                    continue;
                }

                // try to update
                try
                {

                    #region Update Spreadsheet Room
                    // flag used to indicate whether update is successful
                    bool bUpdateFailed = false; // reserve whether this room updated successfully.
                    // if room comment is empty, use <null> for mapped room, use <Added from Revit> for not mapped room in spread sheet.
                    bool bCommnetIsNull = false;

                    // get comments of room
                    String comments;
                    Parameter param = room.get_Parameter(BuiltInParameter.ALL_MODEL_INSTANCE_COMMENTS);
                    comments = (null != param) ? (param.AsString()) : ("");
                    if (String.IsNullOrEmpty(comments))
                    {
                        // this room doesn't have comment value
                        bCommnetIsNull = true;
                        // use <null> for room with empty comment by default when updating spread sheet
                        comments = "<null>";
                    }

                    // create update SQL clause,
                    // when filtering row to be updated, use Room.Id.IntegerValue if "External Room ID" is null.
                    String updateStr = String.Format(
                        "Update [{0}$] SET [{1}] = '{2}', [{3}] = '{4}', [{5}] = '{6}', [{7}] = '{8:N3}' Where [{9}] = {10}",
                        mappedXlsAndTable.SheetName, // mapped table name
                        RoomsData.RoomName, room.Name,
                        RoomsData.RoomNumber, room.Number,
                        RoomsData.RoomComments, comments,
                        RoomsData.RoomArea, roomArea,
                        RoomsData.RoomID, String.IsNullOrEmpty(externalId) ? room.Id.IntegerValue.ToString() : externalId);

                    // execute the command and check the size of updated rows
                    int afftectedRows = dbConnector.ExecuteCommnand(updateStr);
                    if (afftectedRows == 0)
                    {
                        bUpdateFailed = true;
                    }
                    else
                    {
                        // count how many rows were updated
                        DumpLog(String.Format("#{0}--> {1}", stepNo, updateStr));
                        updatedRows += afftectedRows;

                        // if "External Room ID" is null but update successfully, which means:
                        // in spreadsheet there is existing row whose "ID" value equals to room.Id.IntegerValue, so we should
                        // set Revit room's "External Room ID" value to Room.Id.IntegerValue for consistence after update .
                        if (String.IsNullOrEmpty(externalId))
                        {
                            SetExternalRoomIdToRoomId(room);
                        }
                    }
                    #endregion

                    #region Insert Revit Room

                    // Add this new room to spread sheet if fail to update spreadsheet
                    if (bUpdateFailed)
                    {
                        // try to insert this new room to spread sheet, some rules:
                        // a: if the "External Room ID" exists, set ID column to this external id value,
                        //    if the "External Room ID" doesn't exist, use the actual Revit room id as the ID column value.
                        // b: use comments in room if room's description exists,
                        //    else, use constant string: "<Added from Revit>" for Comments column in spreadsheet.

                        String insertStr =
                            String.Format("Insert Into [{0}$] ([{1}], [{2}], [{3}], [{4}], [{5}]) Values('{6}', '{7}', '{8}', '{9}', '{10:N3}')",
                            mappedXlsAndTable.SheetName, // mapped table name
                            RoomsData.RoomID, RoomsData.RoomComments, RoomsData.RoomName, RoomsData.RoomNumber, RoomsData.RoomArea,
                            (String.IsNullOrEmpty(externalId)) ? (room.Id.IntegerValue.ToString()) : (externalId), // Room id
                            (bCommnetIsNull || String.IsNullOrEmpty(comments)) ? ("<Added from Revit>") : (comments),
                            room.Name, room.Number, roomArea);

                        // try to insert it
                        afftectedRows = dbConnector.ExecuteCommnand(insertStr);
                        if (afftectedRows != 0)
                        {
                            // remember the number of new rows
                            String succeedMsg = String.Format("#{0}--> Succeeded to insert spreadsheet Room - Name:{1}, Number:{2}, Area:{3:N3}",
                                stepNo, room.Name, room.Number, roomArea);
                            DumpLog(succeedMsg);
                            newRows += afftectedRows;

                            // if the Revit room doesn't have external id value(may be a room created manually)
                            // set its "External Room ID" value to Room.Id.IntegerValue, because the room was added/mapped to spreadsheet,
                            // and the value of ID column in sheet is just the Room.Id.IntegerValue, we should keep this consistence.
                            if (String.IsNullOrEmpty(externalId))
                            {
                                SetExternalRoomIdToRoomId(room);
                            }
                        }
                        else
                        {
                            DumpLog(String.Format("#{0}--> Failed: {1}", stepNo, insertStr));
                        }
                    }
                    #endregion
                }
                catch (Exception ex)
                {
                    // close the connection
                    DumpLog(String.Format("#{0}--> Exception: {1}", stepNo, ex.Message));
                    dbConnector.Dispose();
                    RoomScheduleForm.MyMessageBox(ex.Message, MessageBoxIcon.Warning);
                    return;
                }
            }
            // close the connection
            dbConnector.Dispose();

            // output the affected result message
            String sumMsg = String.Format("{0}:[{1}]: {2} rows were updated and {3} rows were added into successfully.",
                Path.GetFileName(mappedXlsAndTable.FileName), mappedXlsAndTable.SheetName, updatedRows, newRows);
            DumpLog(sumMsg);
            DumpLog("Finish updating spreadsheet room." + System.Environment.NewLine);
        }