CODEWITHSUNDEEP
c#oracle-database
ktos1234
ktos1234

Reputation: 207

Oracle database and repository pattern in C#

I have database created in oracle. I usually connect to database in my app using this code:

OracleConnectionStringBuilder sb = new OracleConnectionStringBuilder();
    sb.DataSource = "localhost";
    sb.UserID = "user";
    sb.Password = "password";
    OracleConnection conn = new OracleConnection(sb.ToString());
    conn.Open();

I also use this code to do any statements:

OracleDataAdapter sd = new OracleDataAdapter("select, insert etc.", conn);

Let's say that I have 2 tables in my database, 1. People, 2. Places. And now I want to create repository for table - People.

So, what are steps to achieve it? , please correct me if I'm wrong.

I create a class which takes tables properties:

    public class People
{        
    public int ID { get; set; }
    public string first_name { get; set; }
    public string last_name { get; set; }
}

Then, I create Interface:

    public interface IPeople
{
    void Insert(People person);
    void Update(People person);
    void Delete(People person);
    People GetById(People person);
    IEnumerable<People> SelectAll();
}

And I don't know what's next and how to use it. I'm confused, where do I do connection to my database? I am sorry, maybe I ask stupid and simply questions, but I google repository patterns, and I still can't understand how to implement it and use it correctly. Thank you for your help.

Upvotes: 2

Views: 2464

Answers (1)

M. Wiśnicki
M. Wiśnicki

Reputation: 6203

You can implement it like this, but this way not so good. I try comment what you have to do. Use correctly name of your classes and propoerties, If you have problem with it you can use Resharper it is very helpful tool. Also as other wrote better idea will be use some ORM like EntityFramework or NHibernate.

 //change class name and property names , You need to use coding conventions 
public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

//this is a person repository interface
public interface IPersonRepository
{
    void Insert(People person);
    void Update(People person);
    void Delete(People person);
    People GetById(People person);
    IEnumerable<People> SelectAll();
}

//this class implement your interface
class PersonRepository : IPersonRepository
{
    private OracleConnection conn;

    //add constructor which create connection to DB
    public PersonRepository()
    {
        OracleConnectionStringBuilder sb = new OracleConnectionStringBuilder();
        sb.DataSource = "localhost";
        sb.UserID = "user";
        sb.Password = "password";
        conn = new OracleConnection(sb.ToString());
    }


    public void Delete(Person person)
    {
        throw new NotImplementedException();
    }

    public Person GetById(Person person)
    {
        //Use try finally block to open and close connection
        try
        {
            conn.Open();
            //example select by id
            OracleDataAdapter sd = new OracleDataAdapter("SELECT * FROM PERSON WHERE PERSON.Id == " + person.Id,
                conn);
            return new Person();
        }
        finally
        {
            conn.Dispose();
        }
    }

    public void Insert(Person person)
    {
        throw new NotImplementedException();
    }

    public IEnumerable<Person> SelectAll()
    {
        throw new NotImplementedException();
    }

    public void Update(Person person)
    {
        throw new NotImplementedException();
    }
}

Upvotes: 1

Related Questions