2

I have a new project that requires me to connect to Oracle 9i. I want to use the repository pattern (which I am new at) to organise my code. I will use some stored procedures for some queries.

I want to write my code in such a way that there should be no duplication and also following the best practices.

Please check my code below and let me know if I am doing it right. I have a feeling that I am not.

I tried to read other posts on the same topic but no luck.

public interface IDeliveryRepository : IDisposable
{
    IEnumerable<Delivery> GetDeliveries();
    Task GetDelivery(int id);
    void Insert(Delivery delivery);
    void Delete(Delivery delivery);
    void Update(Delivery delivery);
}

Repository:

public class DeliveryRepository: IDeliveryRepository
{
        public Delivery GetDelivery(int id)
        {
            Delivery delivery = null;
            var sql = "SELECT d.id , o.owner_id, o.name FROM delivery d JOIN owner o on o.id = d.owner_id where id = :t";

            using (var con = new OracleConnection(AppConfig.CALL_CENTER_CONNECTION_STRING))
            {
                con.Open();

                using (var cmd = new OracleCommand(sql, con))
                {
                    cmd.BindByName = true;
                    cmd.Parameters.Add("t", id);

                    using (var oraReader = cmd.ExecuteReader())
                    {
                        while (oraReader.Read())
                        {
                            delivery = new Delivery
                            {
                                Id = oraReader.GetString(oraReader.GetOrdinal("id")),
                                Owner = new Owner
                                {
                                    Id = oraReader.GetString(oraReader.GetOrdinal("owner_id")),
                                    Name = oraReader.GetString(oraReader.GetOrdinal("name"))
                                }
                            };
                        }
                    }
                }
            }

            return delivery;
        }

. . . . .

2
  • 3
    Just use dapper and save your a lot of time Commented Feb 18, 2019 at 8:25
  • @MichaelRandall thank you a 1000s times, that is exactly what I was looking for. I really appreciate it. Commented Feb 18, 2019 at 10:14

1 Answer 1

1

You don't need to make your repository IDisposable, and to use Task you should implement your entire class with the Async programming model. I have updated your code below which should get you close to a compiling baseline that you can then extend.

 // Removed the IDisposable interface
 public interface IDeliveryRepository
 {
     IEnumerable<Delivery> GetDeliveries();

     // Changed the below from a Task to a Delivery as the return type. To use Task, 
     // your entire implementation should be asynchronous.
     Delivery GetDelivery(int id);
     void Insert(Delivery delivery);
     void Delete(Delivery delivery);
     void Update(Delivery delivery);
 }

 public class DeliveryRepository: IDeliveryRepository
 {
     public Delivery GetDelivery(int id)
     {
         Delivery delivery = null;
         var sql = "SELECT d.id , o.owner_id, o.name FROM delivery d JOIN owner o on o.id = d.owner_id where id = :t";
         using (var con = new OracleConnection(AppConfig.CALL_CENTER_CONNECTION_STRING))
         {
             con.Open();
             using (var cmd = new OracleCommand(sql, con))
             {
                 cmd.BindByName = true;
                 cmd.Parameters.Add("t", id);
                 using (var oraReader = cmd.ExecuteReader())
                 {
                     while (oraReader.Read())
                     {
                         delivery = new Delivery
                         {
                             Id = oraReader.GetString(oraReader.GetOrdinal("id")),
                             Owner = new Owner
                                {
                                 Id = oraReader.GetString(oraReader.GetOrdinal("owner_id")),
                                 Name = oraReader.GetString(oraReader.GetOrdinal("name"))
                             }
                         };
                     }
                 }
             }
         }
         return delivery;
     }

   public void Insert(Delivery delivery)
   {
       /// Add your code here 
       throw new NotImplementedException();
   }


   public void Delete(Delivery delivery);
   {
       /// Add your code here 
       throw new NotImplementedException();
   }

   public void Update(Delivery delivery);
   {
       /// Add your code here 
       throw new NotImplementedException();
    }

    public IEnumerable<Delivery> GetDeliveries();
    {
       /// Add your code here 
       throw new NotImplementedException();
    }
}
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for that. I really appreciate it. The tool that I was looking for was Dapper.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.