locked
Repository pattern without ORM RRS feed

  • Question

  • User-1915127228 posted

    Hi ,

    trying to rebuild my Data access layer. can anyone give me an example to build Repository Pattern without ORM . I am building using standard ADO.Net.

    Thursday, September 4, 2014 12:03 PM

Answers

  • User459659623 posted

    Check following code (Hope it will help you)

    For your better understanding I gave entire code. Just copy and customize according to your requirement.

    Define Base Entity:

     public abstract class Entity
        {
            private List<BusinessRule> _brokenRules = new List<BusinessRule>();
    
            public int Id { get; set; }
    
            protected abstract void Validate();
    
            public IEnumerable<BusinessRule> GetBrokenRules()
            {
                _brokenRules.Clear();
                Validate();
                return _brokenRules;
            }
    
            protected void AddBrokenRule(BusinessRule businessRule)
            {
                _brokenRules.Add(businessRule);
            }
    
            public override bool Equals(object entity)
            {
                return entity != null
                   && entity is Entity
                   && this == (Entity)entity;
    
            }
    
            public override int GetHashCode()
            {
                return this.Id.GetHashCode();
            }
    
            public static bool operator ==(Entity entity1, Entity entity2)
            {
                if ((object)entity1 == null && (object)entity2 == null)
                {
                    return true;
                }
    
                if ((object)entity1 == null || (object)entity2 == null)
                {
                    return false;
                }
    
                if (entity1.Id.ToString() == entity2.Id.ToString())
                {
                    return true;
                }
    
                return false;
            }
    
            public static bool operator !=(Entity entity1,
                Entity entity2)
            {
                return (!(entity1 == entity2));
            }
        }

    Define AggregateRoot:

     public interface IAggregateRoot
     {
            
     }

    Define Database Context Interface

    public interface IDatabaseContext
    {
       SqlConnection Connection { get; }
       void Dispose();
    }

    Define Data context factory interface

    public interface IDatabaseContextFactory
    {
       IDatabaseContext Context();
    }

    Define Unit of work interface

     public interface IUnitOfWork
     {
            /// <summary>
            /// Gets the context.
            /// </summary>
            IDatabaseContext DataContext { get; }
            SqlTransaction BeginTransaction();
    
            /// <summary>
            /// The Commit.
            /// </summary>
            /// <returns>
            /// The <see cref="void"/>.
            /// </returns>
            void Commit();
    
     }

    Define Repository Interface:

    public interface IRepository<T> where T : Entity, IAggregateRoot
    {
        int Insert(T entity, string insertSql, SqlTransaction sqlTransaction);
        int Update(T entity, string updateSql, SqlTransaction sqlTransaction);
        int Delete(int id, string deleteSql, SqlTransaction sqlTransaction);
        T GetById(int id, string getByIdSql);
        IEnumerable<T> GetAll(string getAllSql);
    }

    Define the Domain Model:

     public class Person:Entity,IAggregateRoot
        {
            /// <summary>
            /// Last Name of a person
            /// </summary>
            public string LastName { get; set; }
    
            /// <summary>
            /// First Name of a person
            /// </summary>
            public string FirstName { get; set; }
    
            /// <summary>
            /// Age of a person
            /// </summary>
            public int Age { get; set; }
    
    
            /// <summary>
            /// Validation Check
            /// </summary>
            protected override void Validate()
            {
                Throw new NotImplementedException();
    
            }
        }

    Define Repository Interface for above model (This process will enhance ISP of SOLID)

    public interface IPersonRepository:IRepository<Person>
    {
            
    }

    Implement IDatabaseContext interface

    public class DatabaseContext:IDatabaseContext
        {
            private readonly string _connectionString;
            private SqlConnection _connection;
    
            /// <summary>
            /// Get connection string inside constructor.
            /// So when the class will be initialized then connection string will be automatically get from web.config
            /// </summary>
            public DatabaseContext()
            {
                _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; ;
            }
    
            /// <summary>
            /// Gets the connection.
            /// </summary>
            public SqlConnection Connection
            {
                get
                {
                    if (_connection == null)
                    {
                        _connection = new SqlConnection(_connectionString);
                    }
                    if (_connection.State != ConnectionState.Open)
                    {
                        _connection.Open();
                    }
                    return _connection;
                }
            }
    
            /// <summary>
            /// Dispose Connection
            /// </summary>
            public void Dispose()
            {
                if (_connection != null && _connection.State == ConnectionState.Open)
                    _connection.Close();
            }
        }

    Implement IDatabaseContextFactory interface

    public class DatabaseContextFactory:IDatabaseContextFactory
        {
            private IDatabaseContext dataContext;
    
            /// <summary>
            /// If data context remain null then initialize new context 
            /// </summary>
            /// <returns>dataContext</returns>
            public IDatabaseContext Context()
            {
                return dataContext ?? (dataContext = new DatabaseContext());
            }
    
            /// <summary>
            /// Dispose existing context
            /// </summary>
            public void Dispose()
            {
                if (dataContext != null)
                    dataContext.Dispose();
            }
        }

    Create Unit of work class by implementing IUnitOfWork interface

    public class UnitOfWork : IUnitOfWork, IDisposable
        {
            private IDatabaseContextFactory _factory;
            private IDatabaseContext _context;
            public SqlTransaction Transaction { get; private set; }
    
            /// <summary>
            /// Constructor which will initialize the datacontext factory
            /// </summary>
            /// <param name="factory">datacontext factory</param>
            public UnitOfWork(IDatabaseContextFactory factory)
            {
                _factory = factory;
            }
    
            /// <summary>
            /// Following method will use to Commit or Rollback memory data into database
            /// </summary>
            public void Commit()
            {
                if (Transaction != null)
                {
                    try
                    {
                        Transaction.Commit();
                    }
                    catch (Exception)
                    {
                        Transaction.Rollback();
                    }
                    Transaction.Dispose();
                    Transaction = null;
                }
                else
                {
                    throw new NullReferenceException("Tryed commit not opened transaction");
                }
            }
    
            /// <summary>
            /// Define a property of context class
            /// </summary>
            public IDatabaseContext DataContext
            {
                get { return _context ?? (_context = _factory.Context()); }
            }
    
            /// <summary>
            /// Begin a database transaction
            /// </summary>
            /// <returns>Transaction</returns>
            public SqlTransaction BeginTransaction()
            {
                if (Transaction != null)
                {
                    throw new NullReferenceException("Not finished previous transaction");
                }
                Transaction = _context.Connection.BeginTransaction();
                return Transaction;
            }
    
            /// <summary>
            /// dispose a Transaction.
            /// </summary>
            public void Dispose()
            {
                if (Transaction != null)
                {
                    Transaction.Dispose();
                }
                if (_context != null)
                {
                    _context.Dispose();
                }
            }
        }

    Define BaseRepository class by implementing IRepository

    public abstract class BaseRepository<T>:IRepository<T> where T:Entity,IAggregateRoot,new()
        {
            private SqlConnection _conn;
            protected readonly IUnitOfWork _uow;
    
            /// <summary>
            /// Initialize the connection
            /// </summary>
            /// <param name="uow">UnitOfWork</param>
            public BaseRepository(IUnitOfWork uow)
            {
                if (uow == null) throw new ArgumentNullException("unitOfWork");
                _uow = uow;
                _conn = _uow.DataContext.Connection;
            }
    
            /// <summary>
            /// Base Method for Insert Data
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="insertSql"></param>
            /// <param name="sqlTransaction"></param>
            /// <returns></returns>
            public int Insert(T entity, string insertSql, SqlTransaction sqlTransaction)
            {
                int i = 0;
                try
                {
                    using (var cmd = _conn.CreateCommand())
                    {
                        cmd.CommandText = insertSql;
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = sqlTransaction;
                        InsertCommandParameters(entity, cmd);
                        i = cmd.ExecuteNonQuery();
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            /// <summary>
            /// Base Method for Update Data
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="updateSql"></param>
            /// <param name="sqlTransaction"></param>
            /// <returns></returns>
            public int Update(T entity, string updateSql, SqlTransaction sqlTransaction)
            {
                int i = 0;
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = updateSql;
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = sqlTransaction;
                    UpdateCommandParameters(entity, cmd);
                    i = cmd.ExecuteNonQuery();
                }
                return i;
            }
    
            /// <summary>
            /// Base Method for Delete Data
            /// </summary>
            /// <param name="id"></param>
            /// <param name="deleteSql"></param>
            /// <param name="sqlTransaction"></param>
            /// <returns></returns>
            public int Delete(int id, string deleteSql, SqlTransaction sqlTransaction)
            {
                int i = 0;
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = deleteSql;
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = sqlTransaction;
                    DeleteCommandParameters(id, cmd);
                    i = cmd.ExecuteNonQuery();
                }
                return i;
            }
    
            /// <summary>
            /// Base Method for Populate Data by key
            /// </summary>
            /// <param name="id"></param>
            /// <param name="getByIdSql"></param>
            /// <returns></returns>
            public T GetById(int id, string getByIdSql)
            {
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = getByIdSql;
                    cmd.CommandType = CommandType.Text;
                    GetByIdCommandParameters(id, cmd);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        return Map(reader);
                    }
                    
                }
            }
    
            /// <summary>
            /// Base Method for Populate All Data
            /// </summary>
            /// <param name="getAllSql"></param>
            /// <returns></returns>
            public IEnumerable<T> GetAll(string getAllSql)
            {
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = getAllSql;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        return Maps(reader);
                    }
                }
            }
    
            protected abstract void InsertCommandParameters(T entity, SqlCommand cmd);
            protected abstract void UpdateCommandParameters(T entity, SqlCommand cmd);
            protected abstract void DeleteCommandParameters(int id, SqlCommand cmd);
            protected abstract void GetByIdCommandParameters(int id, SqlCommand cmd);
            protected abstract T Map(SqlDataReader reader);
            protected abstract List<T> Maps(SqlDataReader reader);
    
           
        }

    Then Create PersonRepository class

    public class PersonRepository:BaseRepository<Person>, IPersonRepository
        {
            public PersonRepository(IUnitOfWork uow) : base(uow) { }
            
            /// <summary>
            /// Passes the parameters for Insert Statement
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="cmd"></param>
            protected override void InsertCommandParameters(Person entity, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@LastName", entity.LastName);
                cmd.Parameters.AddWithValue("@FirstName", entity.FirstName);
                cmd.Parameters.AddWithValue("@Age", entity.Age);
            }
            /// <summary>
            /// Passes the parameters for Update Statement
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="cmd"></param>
            protected override void UpdateCommandParameters(Person entity, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@Id", entity.Id);
                cmd.Parameters.AddWithValue("@LastName", entity.LastName);
                cmd.Parameters.AddWithValue("@FirstName", entity.FirstName);
                cmd.Parameters.AddWithValue("@Age", entity.Age);
            }
    
            /// <summary>
            /// Passes the parameters to command for Delete Statement
            /// </summary>
            /// <param name="id"></param>
            /// <param name="cmd"></param>
            protected override void DeleteCommandParameters(int id, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@Id", id);
            }
    
            /// <summary>
            /// Passes the parameters to command for populate by key statement
            /// </summary>
            /// <param name="id"></param>
            /// <param name="cmd"></param>
            protected override void GetByIdCommandParameters(int id, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@Id", id);
            }
    
            /// <summary>
            /// Maps data for populate by key statement
            /// </summary>
            /// <param name="reader"></param>
            /// <returns></returns>
            protected override Person Map(SqlDataReader reader)
            {
                Person person = new Person();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        person.Id = Convert.ToInt32(reader["Id"].ToString());
                        person.LastName = reader["LastName"].ToString();
                        person.FirstName = reader["FirstName"].ToString();
                        person.Age = Convert.ToInt32(reader["Age"].ToString());
                    }
                }
                return person;
            }
    
            /// <summary>
            /// Maps data for populate all statement
            /// </summary>
            /// <param name="reader"></param>
            /// <returns></returns>
            protected override List<Person> Maps(SqlDataReader reader)
            {
                List<Person> persons = new List<Person>();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Person person = new Person();
                        person.Id = Convert.ToInt32(reader["Id"].ToString());
                        person.LastName = reader["LastName"].ToString();
                        person.FirstName = reader["FirstName"].ToString();
                        person.Age = Convert.ToInt32(reader["Age"].ToString());
                        persons.Add(person);
                    }
                }
                return persons;
            }
        }

    Call your Repository and Unit of work as follows:

    public class PersonFacade
        {
            private IPersonRepository _personRepository;
            private IUnitOfWork _unitOfWork;
    
            public PersonFacade(IPersonRepository repository, IUnitOfWork uow)
            {
                _personRepository = repository;
                _unitOfWork = uow;
            }
    
            public int Save(Person person)
            {
                int i = 0;
                try
                {
                   // _unitOfWork.BeginTransaction();
                    SqlTransaction sqlTransaction = _unitOfWork.BeginTransaction();
                    string strSql = "Insert into Person (LastName, FirstName, Age) VALUES (@LastName, @FirstName, @Age)";
                    i = _personRepository.Insert(person, strSql, sqlTransaction);
    
                    _unitOfWork.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            public int Update(Person person)
            {
                int i = 0;
                try
                {
                    SqlTransaction sqlTransaction = _unitOfWork.BeginTransaction();
                    string strSql = "Update Person set LastName = @LastName, FirstName = @FirstName, Age = @Age Where Id = @Id";
                    i = _personRepository.Update(person, strSql, sqlTransaction);
                    _unitOfWork.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            public int Delete(int id)
            {
                int i = 0;
                try
                {
                    SqlTransaction sqlTransaction = _unitOfWork.BeginTransaction();
                    string strSql = "Delete from Person Where Id = @Id";
                    i = _personRepository.Delete(id, strSql, sqlTransaction);
                    _unitOfWork.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            public Person GetById(int id)
            {
                string strSql = "select * from dbo.Person where id = @id";
                return _personRepository.GetById(id,strSql);
            }
    
            public IEnumerable<Person> GetAll()
            {
                return _personRepository.GetAll("SELECT * FROM Person");
            }
        }

     

    Hope this will help you.

    Thanks :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 24, 2014 5:33 AM

All replies

  • User-1611549905 posted

    Any particular reason why you don't want to use an ORM? Unless you have specific querying or performance needs, or your application is very simple, you'd just be making life harder for yourself.

    On the other hand, if you are using an ORM, they usually implement the Repository pattern already.

    Thursday, September 4, 2014 1:47 PM
  • User-1915127228 posted

    Yes, performance need and it is not a simple application .  there may be a case where we chang a DB schema . Thats the reason we would like not to use ORM . As I am learning to adopt new desing pattern , I would appreciate if you can transfer knowledge on this pattern  with example.

    Thursday, September 4, 2014 1:53 PM
  • User-1611549905 posted

    Changing a DB schema is one very good reason why you should be using an ORM. The whole point of an ORM is to eliminate the repetitive, error-prone code involved in writing raw SQL, especially for insert and update statements. Entity Framework also gives you a set of migration tools that allow you to write and roll out database schema changes in a repeatable, testable manner. An ORM should really be your default data access strategy if you're using a relational database, and should only be bypassed for very specific querying and performance needs on a case by case basis, not application-wide.

    The Repository pattern is defined as follows:

    Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.

    As such, it's one of the key design patterns implemented by an ORM itself, for example by Entity Framework's DbSet<T> collection classes. Trying to build a Repository without using an ORM effectively means either trying to build a large part of a bespoke ORM from scratch, or else building a very specialised set of classes that are much more tightly bound to your database schema than necessary, resistant to everyday changes, and not reusable.

    Friday, September 5, 2014 5:25 AM
  • User-1915127228 posted

    I am returnting large data set and that is the case where entity framework getting slow . I would not choose ORM  in this case. do you have any example where we can create mapper between db schema and repository class .

    Friday, September 5, 2014 11:38 AM
  • User-1611549905 posted

    As I said, what you're asking for here is, to all intents and purposes, an ORM anyway: that's exactly what a "mapper between db schema and repository class" is.

    For large data sets, there's no one-size-fits-all approach to handling performance issues: you really need to do some profiling first to see what's going wrong before you try and come up with a strategy. For example, is your data set a single table with hundreds of thousands of rows? If so, you need to consider whether you can break it up into chunks, for example with paging and sorting. Or does it consist of multiple tables with complex relationships between them? In that case, you need to check to see whether you have any select n+1 issues: your ORM can help here again with prefetch paths or future queries. Or do you have a lot of large text fields, for example a large knowledge base of articles? You may need to introduce lazy loading. And so on and so forth...

    Friday, September 5, 2014 5:19 PM
  • User538021195 posted

    I can suggest some options as recently went through the same cycle.  I think for any large projects there is no one shoe fits all size . We end up using ideas from different sources that give us the best possible solution.

    1. Mini-ORM like Dapper, NPoco that provide mapping functionality. An article on implementing repository pattern with dapper - http://www.contentedcoder.com/2012/12/creating-data-repository-using-dapper.html

    2. Other data access pattern like CQRS - you don't need to follow it exactly but it helped give us ideas on abstracting parts of our DAL . http://www.codeproject.com/Articles/555855/Introduction-to-CQRS

    3. Typed datasets - powerful feature but limits the clients that can use it.

    4. Use of mappers like AutoMapper for mapping data between layers.

    Saturday, September 6, 2014 10:11 PM
  • User-821857111 posted

    If you really want to write your own code to handle the mapping of DataReader results to business objects, you can look at the Data Access Layer code in this article: Building Layered Web Applications with Microsoft ASP.NET 2.0

    Thursday, September 11, 2014 9:23 AM
  • User-1915127228 posted

    Thanks for your reply guys.  I would like to make generic data mapper class which mapps from DataReader to any Objects .

    Thursday, September 11, 2014 9:46 AM
  • User-821857111 posted

    You can use reflection (and now you seriously are well on the way to writing your own ORM). Here's an example: http://stackoverflow.com/questions/4108872/generic-method-assigining-properties-from-a-datareader-to-generic-objects and there are loads more if you search for 'c# generic map from datareader to object'

    Thursday, September 11, 2014 10:18 AM
  • User-1915127228 posted

    Can you explain what is DTO and entity class  ?

    I am buiding 3 - layer application

    1. presentation layer - userlist.aspx  - reference to Business Layer DLL.

    2. Business layer ( Web API) - CategoryBAL.cs - referece to Data Layer DLL

    3. Data layer ( ADO.NET ) - CategoryDAL.cs  /  Category.cs (Entity class)

    In Data Layer , User.cs class is entity class to define only properties. and will be passed to business layer.

    Category.cs

    public class Category
    {
        private int _categoryId;
        public int CategoryID
        {
            get { return _categoryId; }
        }
        public string CategoryName { get; set; }
        public string Description { get; set; }
    
    }

    CategoryDAL.cs

    public class CategoryRepository
    {
    	IDbConnection _connection;
    	
    	public CategoryRepository(IDbConnection connection)
    	{
    		if (connection == null) throw new ArgumentNullException();
    		_connection = connection;
    	}
    
    	public IEnumerable<Category> FindUsers()
    	{
    		using (var command = _connection.CreateCommand())
    		{
    			command.CommandText = @"SELECT * FROM Users WHERE CompanyId = @companyId AND FirstName LIKE @firstName";
    			command.AddParameter("companyId", LoggedInUser.companyId);
    			command.AddParameter("firstName", firstName + "%");
    			using (var reader = command.ExecuteReader())
    			{
    				List<Category> users = new List<Category>();
    				while (reader.Read())
    				{
    					var Category= new Category();
    					Map(reader, Category);
    					Categories.Add(Category);
    				}
    				return Categories;
    			}
    		}
    	}
    
    
    	public void Map(IDataRecord record, Category category)
    	{
    		category.CategoryId= (string)record["CategoryId"];
    		category.CategoryName= (int)record["CategoryName"];
    	}
    }

    questions :

    1 . Category.cs is the entity class exists in Data layer . how to map any entity class from IDataReader ? do we have any auto mapper for that ?

    2. if category.cs is the entity class then what will be DTO class ? and where DTO class exists ? DTO class exists in Business layer ?

    Monday, September 15, 2014 10:49 AM
  • User-1611549905 posted

    DTO stands for Data Transfer Object. It is a representation of your domain model specifically optimised for communication over the wire. The requirements for communication between different tiers are totally different from requirements for communication between layers on the same server. When everything is on one box, communication between the different parts of your application are fast and reliable, so you can often get away with a very "chatty" and fine-grained interface. On the other hand, when you are communicating between different tiers (and that includes the boundary between your ORM and your database), communication is several orders of magnitude slower and can not be considered reliable, so you need a very "chunky" coarse-grained interface.

    Some people declare a set of "DTOs" that are identical to the entities returned from your ORM but that are only used within the business layer of a single-tier application and aren't used for inter-process communication, so the term "DTO" is a bit of a misnomer here. This is a bad practice because it just creates extra work and unnecessary restrictions without solving any problems.

    Tuesday, September 16, 2014 2:41 AM
  • User-1915127228 posted

    Can I have sample example of Project structure with DTO and Entity class ? 

    Tuesday, September 23, 2014 10:12 AM
  • User459659623 posted

    Check following code (Hope it will help you)

    For your better understanding I gave entire code. Just copy and customize according to your requirement.

    Define Base Entity:

     public abstract class Entity
        {
            private List<BusinessRule> _brokenRules = new List<BusinessRule>();
    
            public int Id { get; set; }
    
            protected abstract void Validate();
    
            public IEnumerable<BusinessRule> GetBrokenRules()
            {
                _brokenRules.Clear();
                Validate();
                return _brokenRules;
            }
    
            protected void AddBrokenRule(BusinessRule businessRule)
            {
                _brokenRules.Add(businessRule);
            }
    
            public override bool Equals(object entity)
            {
                return entity != null
                   && entity is Entity
                   && this == (Entity)entity;
    
            }
    
            public override int GetHashCode()
            {
                return this.Id.GetHashCode();
            }
    
            public static bool operator ==(Entity entity1, Entity entity2)
            {
                if ((object)entity1 == null && (object)entity2 == null)
                {
                    return true;
                }
    
                if ((object)entity1 == null || (object)entity2 == null)
                {
                    return false;
                }
    
                if (entity1.Id.ToString() == entity2.Id.ToString())
                {
                    return true;
                }
    
                return false;
            }
    
            public static bool operator !=(Entity entity1,
                Entity entity2)
            {
                return (!(entity1 == entity2));
            }
        }

    Define AggregateRoot:

     public interface IAggregateRoot
     {
            
     }

    Define Database Context Interface

    public interface IDatabaseContext
    {
       SqlConnection Connection { get; }
       void Dispose();
    }

    Define Data context factory interface

    public interface IDatabaseContextFactory
    {
       IDatabaseContext Context();
    }

    Define Unit of work interface

     public interface IUnitOfWork
     {
            /// <summary>
            /// Gets the context.
            /// </summary>
            IDatabaseContext DataContext { get; }
            SqlTransaction BeginTransaction();
    
            /// <summary>
            /// The Commit.
            /// </summary>
            /// <returns>
            /// The <see cref="void"/>.
            /// </returns>
            void Commit();
    
     }

    Define Repository Interface:

    public interface IRepository<T> where T : Entity, IAggregateRoot
    {
        int Insert(T entity, string insertSql, SqlTransaction sqlTransaction);
        int Update(T entity, string updateSql, SqlTransaction sqlTransaction);
        int Delete(int id, string deleteSql, SqlTransaction sqlTransaction);
        T GetById(int id, string getByIdSql);
        IEnumerable<T> GetAll(string getAllSql);
    }

    Define the Domain Model:

     public class Person:Entity,IAggregateRoot
        {
            /// <summary>
            /// Last Name of a person
            /// </summary>
            public string LastName { get; set; }
    
            /// <summary>
            /// First Name of a person
            /// </summary>
            public string FirstName { get; set; }
    
            /// <summary>
            /// Age of a person
            /// </summary>
            public int Age { get; set; }
    
    
            /// <summary>
            /// Validation Check
            /// </summary>
            protected override void Validate()
            {
                Throw new NotImplementedException();
    
            }
        }

    Define Repository Interface for above model (This process will enhance ISP of SOLID)

    public interface IPersonRepository:IRepository<Person>
    {
            
    }

    Implement IDatabaseContext interface

    public class DatabaseContext:IDatabaseContext
        {
            private readonly string _connectionString;
            private SqlConnection _connection;
    
            /// <summary>
            /// Get connection string inside constructor.
            /// So when the class will be initialized then connection string will be automatically get from web.config
            /// </summary>
            public DatabaseContext()
            {
                _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; ;
            }
    
            /// <summary>
            /// Gets the connection.
            /// </summary>
            public SqlConnection Connection
            {
                get
                {
                    if (_connection == null)
                    {
                        _connection = new SqlConnection(_connectionString);
                    }
                    if (_connection.State != ConnectionState.Open)
                    {
                        _connection.Open();
                    }
                    return _connection;
                }
            }
    
            /// <summary>
            /// Dispose Connection
            /// </summary>
            public void Dispose()
            {
                if (_connection != null && _connection.State == ConnectionState.Open)
                    _connection.Close();
            }
        }

    Implement IDatabaseContextFactory interface

    public class DatabaseContextFactory:IDatabaseContextFactory
        {
            private IDatabaseContext dataContext;
    
            /// <summary>
            /// If data context remain null then initialize new context 
            /// </summary>
            /// <returns>dataContext</returns>
            public IDatabaseContext Context()
            {
                return dataContext ?? (dataContext = new DatabaseContext());
            }
    
            /// <summary>
            /// Dispose existing context
            /// </summary>
            public void Dispose()
            {
                if (dataContext != null)
                    dataContext.Dispose();
            }
        }

    Create Unit of work class by implementing IUnitOfWork interface

    public class UnitOfWork : IUnitOfWork, IDisposable
        {
            private IDatabaseContextFactory _factory;
            private IDatabaseContext _context;
            public SqlTransaction Transaction { get; private set; }
    
            /// <summary>
            /// Constructor which will initialize the datacontext factory
            /// </summary>
            /// <param name="factory">datacontext factory</param>
            public UnitOfWork(IDatabaseContextFactory factory)
            {
                _factory = factory;
            }
    
            /// <summary>
            /// Following method will use to Commit or Rollback memory data into database
            /// </summary>
            public void Commit()
            {
                if (Transaction != null)
                {
                    try
                    {
                        Transaction.Commit();
                    }
                    catch (Exception)
                    {
                        Transaction.Rollback();
                    }
                    Transaction.Dispose();
                    Transaction = null;
                }
                else
                {
                    throw new NullReferenceException("Tryed commit not opened transaction");
                }
            }
    
            /// <summary>
            /// Define a property of context class
            /// </summary>
            public IDatabaseContext DataContext
            {
                get { return _context ?? (_context = _factory.Context()); }
            }
    
            /// <summary>
            /// Begin a database transaction
            /// </summary>
            /// <returns>Transaction</returns>
            public SqlTransaction BeginTransaction()
            {
                if (Transaction != null)
                {
                    throw new NullReferenceException("Not finished previous transaction");
                }
                Transaction = _context.Connection.BeginTransaction();
                return Transaction;
            }
    
            /// <summary>
            /// dispose a Transaction.
            /// </summary>
            public void Dispose()
            {
                if (Transaction != null)
                {
                    Transaction.Dispose();
                }
                if (_context != null)
                {
                    _context.Dispose();
                }
            }
        }

    Define BaseRepository class by implementing IRepository

    public abstract class BaseRepository<T>:IRepository<T> where T:Entity,IAggregateRoot,new()
        {
            private SqlConnection _conn;
            protected readonly IUnitOfWork _uow;
    
            /// <summary>
            /// Initialize the connection
            /// </summary>
            /// <param name="uow">UnitOfWork</param>
            public BaseRepository(IUnitOfWork uow)
            {
                if (uow == null) throw new ArgumentNullException("unitOfWork");
                _uow = uow;
                _conn = _uow.DataContext.Connection;
            }
    
            /// <summary>
            /// Base Method for Insert Data
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="insertSql"></param>
            /// <param name="sqlTransaction"></param>
            /// <returns></returns>
            public int Insert(T entity, string insertSql, SqlTransaction sqlTransaction)
            {
                int i = 0;
                try
                {
                    using (var cmd = _conn.CreateCommand())
                    {
                        cmd.CommandText = insertSql;
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = sqlTransaction;
                        InsertCommandParameters(entity, cmd);
                        i = cmd.ExecuteNonQuery();
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            /// <summary>
            /// Base Method for Update Data
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="updateSql"></param>
            /// <param name="sqlTransaction"></param>
            /// <returns></returns>
            public int Update(T entity, string updateSql, SqlTransaction sqlTransaction)
            {
                int i = 0;
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = updateSql;
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = sqlTransaction;
                    UpdateCommandParameters(entity, cmd);
                    i = cmd.ExecuteNonQuery();
                }
                return i;
            }
    
            /// <summary>
            /// Base Method for Delete Data
            /// </summary>
            /// <param name="id"></param>
            /// <param name="deleteSql"></param>
            /// <param name="sqlTransaction"></param>
            /// <returns></returns>
            public int Delete(int id, string deleteSql, SqlTransaction sqlTransaction)
            {
                int i = 0;
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = deleteSql;
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = sqlTransaction;
                    DeleteCommandParameters(id, cmd);
                    i = cmd.ExecuteNonQuery();
                }
                return i;
            }
    
            /// <summary>
            /// Base Method for Populate Data by key
            /// </summary>
            /// <param name="id"></param>
            /// <param name="getByIdSql"></param>
            /// <returns></returns>
            public T GetById(int id, string getByIdSql)
            {
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = getByIdSql;
                    cmd.CommandType = CommandType.Text;
                    GetByIdCommandParameters(id, cmd);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        return Map(reader);
                    }
                    
                }
            }
    
            /// <summary>
            /// Base Method for Populate All Data
            /// </summary>
            /// <param name="getAllSql"></param>
            /// <returns></returns>
            public IEnumerable<T> GetAll(string getAllSql)
            {
                using (var cmd = _conn.CreateCommand())
                {
                    cmd.CommandText = getAllSql;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        return Maps(reader);
                    }
                }
            }
    
            protected abstract void InsertCommandParameters(T entity, SqlCommand cmd);
            protected abstract void UpdateCommandParameters(T entity, SqlCommand cmd);
            protected abstract void DeleteCommandParameters(int id, SqlCommand cmd);
            protected abstract void GetByIdCommandParameters(int id, SqlCommand cmd);
            protected abstract T Map(SqlDataReader reader);
            protected abstract List<T> Maps(SqlDataReader reader);
    
           
        }

    Then Create PersonRepository class

    public class PersonRepository:BaseRepository<Person>, IPersonRepository
        {
            public PersonRepository(IUnitOfWork uow) : base(uow) { }
            
            /// <summary>
            /// Passes the parameters for Insert Statement
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="cmd"></param>
            protected override void InsertCommandParameters(Person entity, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@LastName", entity.LastName);
                cmd.Parameters.AddWithValue("@FirstName", entity.FirstName);
                cmd.Parameters.AddWithValue("@Age", entity.Age);
            }
            /// <summary>
            /// Passes the parameters for Update Statement
            /// </summary>
            /// <param name="entity"></param>
            /// <param name="cmd"></param>
            protected override void UpdateCommandParameters(Person entity, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@Id", entity.Id);
                cmd.Parameters.AddWithValue("@LastName", entity.LastName);
                cmd.Parameters.AddWithValue("@FirstName", entity.FirstName);
                cmd.Parameters.AddWithValue("@Age", entity.Age);
            }
    
            /// <summary>
            /// Passes the parameters to command for Delete Statement
            /// </summary>
            /// <param name="id"></param>
            /// <param name="cmd"></param>
            protected override void DeleteCommandParameters(int id, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@Id", id);
            }
    
            /// <summary>
            /// Passes the parameters to command for populate by key statement
            /// </summary>
            /// <param name="id"></param>
            /// <param name="cmd"></param>
            protected override void GetByIdCommandParameters(int id, SqlCommand cmd)
            {
                cmd.Parameters.AddWithValue("@Id", id);
            }
    
            /// <summary>
            /// Maps data for populate by key statement
            /// </summary>
            /// <param name="reader"></param>
            /// <returns></returns>
            protected override Person Map(SqlDataReader reader)
            {
                Person person = new Person();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        person.Id = Convert.ToInt32(reader["Id"].ToString());
                        person.LastName = reader["LastName"].ToString();
                        person.FirstName = reader["FirstName"].ToString();
                        person.Age = Convert.ToInt32(reader["Age"].ToString());
                    }
                }
                return person;
            }
    
            /// <summary>
            /// Maps data for populate all statement
            /// </summary>
            /// <param name="reader"></param>
            /// <returns></returns>
            protected override List<Person> Maps(SqlDataReader reader)
            {
                List<Person> persons = new List<Person>();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Person person = new Person();
                        person.Id = Convert.ToInt32(reader["Id"].ToString());
                        person.LastName = reader["LastName"].ToString();
                        person.FirstName = reader["FirstName"].ToString();
                        person.Age = Convert.ToInt32(reader["Age"].ToString());
                        persons.Add(person);
                    }
                }
                return persons;
            }
        }

    Call your Repository and Unit of work as follows:

    public class PersonFacade
        {
            private IPersonRepository _personRepository;
            private IUnitOfWork _unitOfWork;
    
            public PersonFacade(IPersonRepository repository, IUnitOfWork uow)
            {
                _personRepository = repository;
                _unitOfWork = uow;
            }
    
            public int Save(Person person)
            {
                int i = 0;
                try
                {
                   // _unitOfWork.BeginTransaction();
                    SqlTransaction sqlTransaction = _unitOfWork.BeginTransaction();
                    string strSql = "Insert into Person (LastName, FirstName, Age) VALUES (@LastName, @FirstName, @Age)";
                    i = _personRepository.Insert(person, strSql, sqlTransaction);
    
                    _unitOfWork.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            public int Update(Person person)
            {
                int i = 0;
                try
                {
                    SqlTransaction sqlTransaction = _unitOfWork.BeginTransaction();
                    string strSql = "Update Person set LastName = @LastName, FirstName = @FirstName, Age = @Age Where Id = @Id";
                    i = _personRepository.Update(person, strSql, sqlTransaction);
                    _unitOfWork.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            public int Delete(int id)
            {
                int i = 0;
                try
                {
                    SqlTransaction sqlTransaction = _unitOfWork.BeginTransaction();
                    string strSql = "Delete from Person Where Id = @Id";
                    i = _personRepository.Delete(id, strSql, sqlTransaction);
                    _unitOfWork.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return i;
            }
    
            public Person GetById(int id)
            {
                string strSql = "select * from dbo.Person where id = @id";
                return _personRepository.GetById(id,strSql);
            }
    
            public IEnumerable<Person> GetAll()
            {
                return _personRepository.GetAll("SELECT * FROM Person");
            }
        }

     

    Hope this will help you.

    Thanks :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 24, 2014 5:33 AM
  • User-1915127228 posted

    Hi,

    Thanks for your example. this really helps and what i was looking for. 

    Questions : What is DTO and how do you use in this example.

    Monday, September 29, 2014 3:11 PM
  • User-1611549905 posted

    You wouldn't use DTOs in this example.

    DTOs -- Data Transfer Objects -- are only appropriate when you're creating an external service (e.g. WCF or WebAPI), and only if the domain model entities are not suitable for this purpose. Two examples: (a) if your domain model has a very fine-grained "chatty" interface (as is usually the case when your entities have any significant logic on them) you would need to adapt this to a much more coarse-grained "chunky" interface suitable for much slower over-the-wire communication, or (b) your entities can not be serialised for whatever reason (e.g. NHibernate proxy classes).

    Tuesday, September 30, 2014 5:45 AM
  • User459659623 posted

    Hi

    Please check following:

    http://msdn.microsoft.com/en-us/library/ff649585.aspx

    http://martinfowler.com/eaaCatalog/dataTransferObject.html

    hope these will help you to learn much about DTO.

    Thanks :)

    Tuesday, September 30, 2014 6:57 AM
  • User-1915127228 posted

    Yes, I understand DTO will be used when we do a WebAPI implementation . I am also developing webAPI . I would like to see how DTO will be created . Can you give me a sample as a reference .

    Thanks,

    Ankit

    Tuesday, September 30, 2014 11:39 AM
  • User-821857111 posted
    This thread is drifting away from its subject. Please mark those posts that helped you and then start a new thread to discuss dtos and web api.
    Tuesday, September 30, 2014 2:43 PM