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); }
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// private bool UpdateMenuInfo(Menus.Menu i_oMenu) { bool bRes = false; try { using (IDbConnection sqlConn = GetDbConnection()) { using (IDbCommand sqlCmd = sqlConn.CreateCommand()) { IDbDataParameter sqlParam; sqlCmd.CommandText = "UPDATE tblMenus SET sLanguageCode = @LanguageCode, bDtmfCanBeSpoken = @DtmfCanBeSpoken, bConfirmationEnabled = @ConfirmationEnabled WHERE iMenuID = @MenuId"; sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.String; sqlParam.ParameterName = "@LanguageCode"; sqlParam.Value = i_oMenu.LanguageCode; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.Boolean; sqlParam.ParameterName = "@DtmfCanBeSpoken"; sqlParam.Value = i_oMenu.DtmfCanBeSpoken; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.Boolean; sqlParam.ParameterName = "@ConfirmationEnabled"; sqlParam.Value = i_oMenu.ConfirmationEnabled; sqlCmd.Parameters.Add(sqlParam); sqlParam = sqlCmd.CreateParameter(); sqlParam.DbType = DbType.Int32; sqlParam.ParameterName = "@MenuId"; sqlParam.Value = i_oMenu.MenuId; sqlCmd.Parameters.Add(sqlParam); sqlConn.Open(); sqlCmd.ExecuteNonQuery(); sqlConn.Close(); bRes = true; } } } catch (Exception exc) { Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenuInfo caught exception: " + exc.ToString()); bRes = false; } return(bRes); } // UpdateMenuInfo
private Menus.Menu GetMenuFromSqlReader(IDataReader i_sqlReader) { Menus.Menu menu = new Menus.Menu(); menu.MenuId = Convert.ToInt32(i_sqlReader[m_csMenuIDColumn]); menu.MenuName = i_sqlReader[m_csMenuNameColumn].ToString(); menu.LanguageCode = i_sqlReader[m_csLanguageCode].ToString(); menu.Include = i_sqlReader[m_csIncludeColumn].ToString(); menu.GrammarUrl = i_sqlReader[m_csGrammarUrlColumn].ToString(); menu.Variables = ExtractVariables(i_sqlReader[m_csVariablesColumn].ToString()); menu.MinConfScore = Convert.ToInt32(i_sqlReader[m_csMinConfScoreColumn]); menu.HighConfScore = Convert.ToInt32(i_sqlReader[m_csHighConfScoreColumn]); menu.DtmfCanBeSpoken = Convert.ToBoolean(i_sqlReader[m_csDtmfCanBeSpokenColumn]); menu.ConfirmationEnabled = Convert.ToBoolean(i_sqlReader[m_csConfirmationEnabled]); menu.Enabled = Convert.ToBoolean(i_sqlReader[m_csEnabledColumn]); return(menu); }
private ErrorCode GetMenuUsingSqlQuery(string i_sQuery, out Menus.Menu o_oMenu) { ErrorCode ecRet = ErrorCode.Success; o_oMenu = null; try { using (IDbConnection sqlConn = GetDbConnection()) using (IDbCommand sqlCmd = sqlConn.CreateCommand()) { sqlCmd.CommandText = i_sQuery; sqlConn.Open(); using (IDataReader sqlReader = sqlCmd.ExecuteReader()) { try { while (sqlReader.Read()) { o_oMenu = GetMenuFromSqlReader(sqlReader); } } catch (Exception exc) { ecRet = ErrorCode.UnknownSql; Console.Error.WriteLine(DateTime.Now.ToString() + " SBConfigStore.DialogDesignerDAL.GetMenuUsingSqlQuery exception1: " + exc.ToString()); } } sqlConn.Close(); } } catch (Exception exc) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine(DateTime.Now.ToString() + " SBConfigStore.DialogDesignerDAL.GetMenuUsingSqlQuery exception2: " + exc.ToString()); } return(ecRet); } // GetMenuUsingSqlQuery
} // AddMenu ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public ErrorCode UpdateMenu(Menus.Menu i_oMenu, Commands i_oCommands) { ErrorCode ecRet = ErrorCode.Success; StringBuilder sbSql = null; string sSql = ""; bool bRes = true; int ii = 0, iNumCmds = 0; Database dbType = Database.unknown; try { //Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu IN."); dbType = RunningSystem.RunningDatabase; if (dbType == Database.unknown) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu Invalid DB type."); } else { // FIX!!! Do this in a stored procedure, this approach is way too inefficient // Delete previous commands and mapping if (dbType == Database.MsSql) { throw new Exception("Code for MS SQL not yet implemented."); } else if (dbType == Database.PostgreSql) { sSql = string.Format("DELETE FROM tblCommands USING tblMenuCommandsMap WHERE tblCommands.iCommandID = tblMenuCommandsMap.iCommandID AND tblMenuCommandsMap.iMenuID = {0}", i_oMenu.MenuId); bRes = RunSqlNonQuery(sSql); } if (!bRes) { ecRet = ErrorCode.UnknownSql; Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu DB error deleting previous commands."); } else { // Update Menu Info - currently user can change Language, whether DTMF commands can be spoken and if Confirmation is enabled for spoken commands. bRes = UpdateMenuInfo(i_oMenu); if (!bRes) { ecRet = ErrorCode.UnknownSql; Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu Error updating Menu table."); } else { sbSql = new StringBuilder(); // Save commands, hold on to IDs iNumCmds = i_oCommands.Count; //Console.Error.WriteLine(DateTime.Now.ToString() + " iNumCmds='{0}'.", iNumCmds); for (ii = 0; ii < iNumCmds; ii++) { i_oCommands[ii].CommandId = InsertCommand(i_oCommands[ii]); if (-1 == i_oCommands[ii].CommandId) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu Inserting Command ii={0} failed.", ii); } else { // Map command to menu if (dbType == Database.MsSql) { throw new Exception("Code for MS SQL not yet implemented."); } else if (dbType == Database.PostgreSql) { sSql = string.Format("INSERT INTO tblMenuCommandsMap(iMenuID, iCommandID) VALUES({0}, {1})", i_oMenu.MenuId, i_oCommands[ii].CommandId); } bRes = RunSqlNonQuery(sSql); if (!bRes) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu Error updating MenuCommandsMap ({0}, {1}), ii={2}.", i_oMenu.MenuId, i_oCommands[ii].CommandId, ii); } else { //Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu Inserted MenuCommandsMap({0}, {1}), ii={2}.", i_oMenu.MenuId, i_oCommands[ii].CommandId, ii); } } // else } // for } // else } // else } // else } catch (Exception exc) { ecRet = ErrorCode.Unknown; Console.Error.WriteLine(DateTime.Now.ToString() + " DialogDesignerDAL.UpdateMenu caught exception:" + exc.ToString()); } return(ecRet); } // UpdateMenu
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 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
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public ErrorCode GetMenu(string i_sMenuName, out Menus.Menu o_oMenu) { return(GetMenuUsingSqlQuery(String.Format("{0} WHERE sMenuName = '{1}'", BasicMenuSelectString(), i_sMenuName), out o_oMenu)); }
} // GetMenus ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public ErrorCode GetMenu(int i_iMenuid, out Menus.Menu o_oMenu) { return(GetMenuUsingSqlQuery(String.Format("{0} WHERE iMenuid = '{1}'", BasicMenuSelectString(), i_iMenuid), out o_oMenu)); }