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(); } }