Csharp and Oracle

Create Table Category

CREATE TABLE Category
(
    CategoryId INTEGER NOT NULL PRIMARY KEY,
    CategoryName VARCHAR2(64) not null,
    Description VARCHAR2(128) NOT NULL
)

Insert Data To Table Category

INSERT INTO Category (CategoryId, CategoryName, Description) VALUES (1, 'Laptop', 'Danh mục máy tính xách tay');
INSERT INTO Category (CategoryId, CategoryName, Description) VALUES (2, 'Keyboard', 'Danh mục bàn phím máy vi tính');
INSERT INTO Category (CategoryId, CategoryName, Description) VALUES (2, 'Mouse', 'Danh mục Chuột máy tính');
commit;

Connection String

<connectionStrings>
	<add name="shop" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl))); User Id =sys;Password=123;DBA Privilege=SYSDBA;" providerName="Oracle.DataAccess.Client"/>
</connectionStrings>

Create abstract class BaseRepository

abstract class BaseRepository
{
    protected static string connectionString = ConfigurationManager.ConnectionStrings["shop"].ConnectionString;

}

Create class Category

class Category
{
    public decimal Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

Create class Category

using Oracle.DataAccess.Client;
using System.Collections.Generic;
class CategoryRepository : BaseRepository
{
    static Category Fetch(OracleDataReader reader)
    {
        return new Category
        {
            Id = (decimal)reader["CategoryId"],
            Name = (string)reader["CategoryName"],
            Description = (string)reader["Description"]
        };
    }
    static Category Fetch(OracleCommand command)
    {
        using (OracleDataReader reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                return Fetch(reader);
            }
            return null;
        }
    }
    static List<Category> FetchAll(OracleDataReader reader)
    {
        List<Category> list = new List<Category>();
        while (reader.Read())
        {
            list.Add(Fetch(reader));
        }
        return list;
    }
    static List<Category> FetchAll(OracleCommand command)
    {
        using (OracleDataReader reader = command.ExecuteReader())
        {
            return FetchAll(reader);
        }
    }
    public List<Category> GetCategories()
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand command = new OracleCommand("SELECT * FROM Category", connection))
            {
                connection.Open();
                return FetchAll(command);
            }

        }
    }
    public int Add(Category obj)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand command = new OracleCommand("INSERT INTO Category (CategoryId, CategoryName, Description) VALUES (:id, :name, :des)", connection))
            {
                command.Parameters.Add("id", obj.Id);
                command.Parameters.Add("name", obj.Name);
                command.Parameters.Add("des", obj.Description);
                connection.Open();
                return command.ExecuteNonQuery();
            }
        }
    }
    public Category GetCategoryById(decimal id)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand command = new OracleCommand("SELECT * FROM Category WHERE CategoryId = :id", connection))
            {
                command.Parameters.Add("id", id);
                connection.Open();
                return Fetch(command);
            }
        }
    }

}