static void Main(string[] args)
{
// ConnectionString
string connectionString = ConfigurationManager.ConnectionStrings["FDMOracle"].ToString();
IBrokerRepository brokerRepository = new OracleSqlBrokerRepository(connectionString);
brokerRepository.Refresh();
foreach(Broker broker in brokerRepository.GetAllBrokers())
{
Console.WriteLine("{0},{1},{2}",
broker.id,
broker.firstName,
broker.lastName);
}
Console.WriteLine("Now add some new ones ... ");
while (true)
{
Console.Write("ID: ");
string id = Console.ReadLine();
Console.Write("First Name: ");
string firstName = Console.ReadLine();
Console.Write("Last Name: ");
string lastName = Console.ReadLine();
Broker newBroker = new Broker()
{
id = int.Parse(id),
firstName = firstName,
lastName = lastName
};
brokerRepository.AddNewBroker(newBroker);
}
}
public void AddNewBroker(Broker brokerToAdd)
{
// disconnected version
string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)";
IDbConnection connection = new OracleConnection(connectionString);
OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection);
command.BindByName = true;
IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25);
param.Value = brokerToAdd.firstName;
command.Parameters.Add(param);
param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25);
param.Value = brokerToAdd.lastName;
command.Parameters.Add(param);
param = new OracleParameter(":broker_id", OracleDbType.Int16, 50);
param.Value = brokerToAdd.id;
command.Parameters.Add(param);
IDbDataAdapter da = new OracleDataAdapter();
da.InsertCommand = command;
DataRow newRow = dataSet.Tables[0].NewRow();
newRow["broker_id"] = brokerToAdd.id;
newRow["first_name"] = brokerToAdd.firstName;
newRow["last_name"] = brokerToAdd.lastName;
dataSet.Tables[0].Rows.Add(newRow);
// it's not clear from this code but we could make many changes to
// the dataset before submitting back to the database.
connection.Open();
da.Update(dataSet);
connection.Close();
}
public void Refresh()
{
OracleConnection connection = new OracleConnection(connectionString);
connection.Open();
OracleCommand command = GetRefreshCommand(connection);
IDbDataAdapter da = new OracleDataAdapter();
da.SelectCommand = command;
dataSet = new DataSet();
da.Fill(dataSet);
connection.Close(); // IMPORTANT!!!!!!
foreach (DataRow row in dataSet.Tables[0].Rows)
{
Broker broker = new Broker()
{
id = int.Parse(row["broker_id"].ToString()),
firstName = row["first_name"].ToString(),
lastName = row["last_name"].ToString()
};
allBrokers.Add(broker);
}
}
private void RefreshConnected()
{
OracleConnection connection = null;
try
{
// Connection
connection = new OracleConnection(connectionString);
connection.Open();
OracleCommand command = GetRefreshCommand(connection);
// Reader
OracleDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Broker broker = new Broker()
{
id = int.Parse(dataReader["broker_id"].ToString()),
firstName = dataReader["first_name"].ToString(),
lastName = dataReader["last_name"].ToString()
};
allBrokers.Add(broker);
}
}
catch (OracleException exception)
{
Console.WriteLine("Error: {0} Inner Exception: {1}",
exception.Message,
exception.InnerException);
}
finally
{
connection.Close();
}
}
private void AddNewBrokerConnected(Broker brokerToAdd)
{
string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)";
IDbConnection connection = new OracleConnection(connectionString);
OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection);
command.BindByName = true;
IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25);
param.Value = brokerToAdd.firstName;
command.Parameters.Add(param);
param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25);
param.Value = brokerToAdd.lastName;
command.Parameters.Add(param);
param = new OracleParameter(":broker_id", OracleDbType.Int16, 50);
param.Value = brokerToAdd.id;
command.Parameters.Add(param);
try {
connection.Open();
command.ExecuteNonQuery();
}
catch (OracleException exception)
{
Console.WriteLine("Error: {0} Inner Exception: {1}", exception.Message, exception.InnerException);
}
finally
{
connection.Close();
}
}
