public ErrorCode AddCommand(Menus.Menu i_menu, Commands.Command i_command) { ErrorCode ecRet = ErrorCode.Success; try { i_command.CommandId = InsertCommand(i_command); if (-1 == i_command.CommandId) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine("{0} SBConfigStore.DialogDesignerDAL.AddCommand Inserting Command ('{1}') failed.", DateTime.Now, i_command.CommandOption); } else { // Map command to menu. string sSql = String.Format("INSERT INTO tblMenuCommandsMap(iMenuID, iCommandID) VALUES({0}, {1})", i_menu.MenuId, i_command.CommandId); bool bRes = RunSqlNonQuery(sSql); if (!bRes) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine("{0} SBConfigStore.DialogDesignerDAL.AddCommand Error updating MenuCommandsMap ({1}, {2}) ('{3}').", DateTime.Now, i_menu.MenuId, i_command.CommandId, i_command.CommandOption); } } } catch (Exception exc) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine("{0} SBConfigStore.DialogDesignerDAL.AddCommand exception: {1}", DateTime.Now, exc.ToString()); } return(ecRet); }
} // UpdateMenuInfo ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// private int InsertCommand(Commands.Command i_command) { int iCommandId = -1; try { StringBuilder sbSql = new StringBuilder(); sbSql.Append("INSERT INTO tblCommands(sCommandName, iCommandType, iOperationType, sCommandOption, sConfirmationText, sConfirmationWavUrl, sResponse) "); sbSql.Append("VALUES(@CommandName, @CommandType, @OperationType, @CommandOption, @ConfirmationText, @ConfirmationWavUrl, @Response); "); Database databaseType = RunningSystem.RunningDatabase; switch (databaseType) { case Database.MsSql: throw new Exception("Code for MS SQL not yet implemented."); case Database.PostgreSql: // According to http://www.programmingado.net/a-117/Insert-data-and-retrieve-serial-autonumber-id.aspx, this // query is session safe, no need for a transaction. sbSql.Append("SELECT currval('tblCommands_iCommandID_seq')"); break; case Database.unknown: default: throw new Exception(String.Format("Unknown Database Type ({0}).", databaseType)); } using (IDbConnection sqlConn = GetDbConnection()) { using (IDbCommand sqlCmd = sqlConn.CreateCommand()) { IDbDataParameter sqlParam; sqlCmd.CommandText = sbSql.ToString(); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.String; sqlParam.ParameterName = "@CommandName"; sqlParam.Value = i_command.CommandName; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.Int32; sqlParam.ParameterName = "@CommandType"; sqlParam.Value = (int)i_command.CommandType; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.Int32; sqlParam.ParameterName = "@OperationType"; sqlParam.Value = (int)i_command.OperationType; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.String; sqlParam.ParameterName = "@CommandOption"; sqlParam.Value = i_command.CommandOption; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.String; sqlParam.ParameterName = "@ConfirmationText"; sqlParam.Value = i_command.ConfirmationText; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.String; sqlParam.ParameterName = "@ConfirmationWavUrl"; sqlParam.Value = i_command.ConfirmationWavUrl; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.String; sqlParam.ParameterName = "@Response"; sqlParam.Value = i_command.Response; sqlCmd.Parameters.Add(sqlParam); sqlConn.Open(); iCommandId = (int)((long)sqlCmd.ExecuteScalar()); sqlConn.Close(); } } } catch (Exception exc) { Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.InsertCommand caught exception: " + exc.ToString()); iCommandId = -1; } return(iCommandId); } // InsertCommand
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public Commands GetCommandsForMenu(Menus.Menu i_Menu) { Commands commands = new Commands(); try { using (IDbConnection sqlConn = GetDbConnection()) { using (IDbCommand sqlCmd = sqlConn.CreateCommand()) { string sCmd = String.Format("SELECT c.{0}, c.{1}, c.{2}, c.{3}, c.{4}, c.{5}, c.{6}, c.{7} FROM tblCommands c, tblMenuCommandsMap mc WHERE c.iCommandID = mc.iCommandID AND mc.iMenuID = {8}", m_csCommandIDColumn, m_csCommandNameColumn, m_csCommandTypeColumn, m_csOperationTypeColumn, m_csCommandOptionColumn, m_csConfirmationTextColumn, m_csConfirmationWavUrlColumn, m_csResponseColumn, i_Menu.MenuId); sqlCmd.CommandText = sCmd; sqlConn.Open(); using (IDataReader sqlReader = sqlCmd.ExecuteReader()) { try { while (sqlReader.Read()) { Menus.Menu menu = new Menus.Menu(); Commands.Command command = new Commands.Command(); command.CommandId = Convert.ToInt32(sqlReader[m_csCommandIDColumn]); command.CommandName = sqlReader[m_csCommandNameColumn].ToString(); command.CommandType = (Commands.eCommandType)sqlReader[m_csCommandTypeColumn]; command.OperationType = (Commands.eOperationType)sqlReader[m_csOperationTypeColumn]; command.CommandOption = sqlReader[m_csCommandOptionColumn].ToString(); command.ConfirmationText = sqlReader[m_csConfirmationTextColumn].ToString(); command.ConfirmationWavUrl = sqlReader[m_csConfirmationWavUrlColumn].ToString(); command.Response = sqlReader[m_csResponseColumn].ToString(); commands.Add(command); } } catch (Exception exc) { Console.Error.WriteLine(DateTime.Now.ToString() + " SBConfigStore.DialogDesignerDAL.GetCommandsForMenu exception2: " + exc.ToString()); } } // using (IDataReader) sqlConn.Close(); } // using (IDbCommand) } // using (IDbConnection) } catch (Exception exc) { Console.Error.WriteLine(DateTime.Now.ToString() + " SBConfigStore.DialogDesignerDAL.GetCommandsForMenu exception1: " + exc.ToString()); } return(commands); } // GetCommandsForMenu