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