none
How to Wrap my Unit of Work code in my repository class RRS feed

  • Question

  • See my repository code

    public abstract class AdoRepository<T> where T : class
        {
            private static SqlConnection _connection;
    
            public AdoRepository(string connectionString)
            {
                _connection = new SqlConnection(connectionString);
            }
    
            public virtual T PopulateRecord(SqlDataReader reader)
            {
                return null;
            }
    
            public virtual void GetDataCount(int count)
            {
               
            }
    
            protected IEnumerable<T> GetRecords(SqlCommand command)
            {
                var list = new List<T>();
                command.Connection = _connection;
                _connection.Open();
                try
                {
                    var reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            list.Add(PopulateRecord(reader));
                        }
    
                        reader.NextResult();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                GetDataCount(Convert.ToInt32(reader["Count"].ToString()));
                            }
                        }
                    }
                    finally
                    {
                        // Always call Close when done reading.
                        reader.Close();
                    }
                }
                catch(Exception ex)
                {
                    string Msg = ex.Message;
                }
                finally
                {
                    _connection.Close();
                }
                return list;
            }
    
            protected T GetRecord(SqlCommand command)
            {
                T record = null;
                command.Connection = _connection;
                _connection.Open();
                try
                {
                    var reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            record = PopulateRecord(reader);
                            break;
                        }
                    }
                    finally
                    {
                        // Always call Close when done reading.
                        reader.Close();
                    }
                }
                finally
                {
                    _connection.Close();
                }
                return record;
            }
           
            protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
            {
                var list = new List<T>();
                command.Connection = _connection;
                command.CommandType = CommandType.StoredProcedure;
                _connection.Open();
                try
                {
                    var reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            var record = PopulateRecord(reader);
                            if (record != null) list.Add(record);
                        }
                    }
                    finally
                    {
                        // Always call Close when done reading.
                        reader.Close();
                    }
                }
                finally
                {
                    _connection.Close();
                }
                return list;
            }
        }
    
       public class StudentRepository : AdoRepository<Student>
        {
            public int DataCounter { get; set; }
    
            public StudentRepository(string connectionString)
                : base(connectionString)
            {
            }
            public IEnumerable<Student> GetAll()
            {
                // DBAs across the country are having strokes 
                //  over this next command!
                using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents"))
                {
                    return GetRecords(command);
                }
            }
            public Student GetById(string id)
            {
                // PARAMETERIZED QUERIES!
                using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents WHERE Id = @id"))
                {
                    command.Parameters.Add(new ObjectParameter("id", id));
                    return GetRecord(command);
                }
            }
    
            public IEnumerable<Student> GetStudents(int StartIndex, int EndIndex, string sortCol, string sortOrder)
            {
                string strSQL = "SELECT * FROM vwListStudents WHERE ID >=" + StartIndex + " AND ID <=" + EndIndex;
                strSQL += " ORDER BY " + sortCol + " " + sortOrder;
                strSQL += ";SELECT COUNT(*) AS Count FROM vwListStudents";
                var command = new SqlCommand(strSQL);
                return GetRecords(command);
            }
    
            public override Student PopulateRecord(SqlDataReader reader)
            {
                return new Student
                {
                    ID = Convert.ToInt32(reader["ID"].ToString()),
                    FirstName = reader["FirstName"].ToString(),
                    LastName = reader["LastName"].ToString(),
                    IsActive = Convert.ToBoolean(reader["IsActive"]),
                    StateName = reader["StateName"].ToString(),
                    CityName = reader["CityName"].ToString()
                };
            }
    
            public override void GetDataCount(int count)
            {
                DataCounter = count;
            }
        }

    this way unit of work code added

    public class UnitOfWorkFactory
    {
    	public static IUnitOfWork Create()
    	{
    		var connection = new SqlConnection(ConfigurationManager.ConnectionStrings("MyDb").ConnectionString);
    		connection.Open();
    		
    		return new AdoNetUnitOfWork(connection, true);
    	}
    }
    
    public class AdoNetUnitOfWork : IUnitOfWork
    {
    	public AdoNetUnitOfWork(IDbConnection connection, bool ownsConnection)
    	{
    		_connection = connection;
    		_ownsConnection=ownsConnection;
    		_transaction = connection.BeginTransaction();
    	}
    	
    	public IDbCommand CreateCommand()
    	{
    		var command = _connection.CreateCommand();
    		command.Transaction = _transaction;
    		return command;
    	}
    	
    	public void SaveChanges()
    	{
    		if (_transaction == null)
    			throw new InvalidOperationException("Transaction have already been commited. Check your transaction handling.");
    			
    		_transaction.Commit();
    		_transaction = null;
    	}
    	
    	public void Dispose()
    	{
    		if (_transaction != null)
    		{
    			_transaction.Rollback();
    			_transaction = null;
    		}
    		
    		if (_connection != null && _ownsConnection)
    		{
    			_connection.Close();
    			_connection = null;
    		}
    	}
    }
    
    using (var uow = UnitOfWorkFactory.Create())
    {
    	var repos = new UserRepository(uow);
    	
    	uow.SaveChanges();
    }
    
    public class UserRepository
    {
    	private AdoNetUnitOfWork _unitOfWork;
    	
    	public UserRepository(IUnitOfWork uow)
    	{
    		if (uow == null)
    			throw new ArgumentNullException("uow");
    			
    		_unitOfWork = uow as AdoNetUnitOfWork;
    		if (_unitOfWork == null)
    			throw new NotSupportedException("Ohh my, change that UnitOfWorkFactory, will you?");
    	}
    	
    	public User Get(Guid id)
    	{
    		using (var cmd = _unitOfWork.CreateCommand())
    		{
    			cmd.CommandText = "SELECT * FROM Users WHERE Id = @id");
    			cmd.AddParameter("id", id);
    			
    			// uses an extension method which I will demonstrate in a 
    			// blog post in a couple of days
    			return cmd.FirstOrDefault<User>();
    		}
    	}
    }

    but i want to add unit of work code in my repository class. may be in AdoRepository class or StudentRepository class as a result i do not have to write the below code again and again.

    using (var uow = UnitOfWorkFactory.Create())
    {
    	var repos = new UserRepository(uow);
    	
    	uow.SaveChanges();
    }

    so when i will be working with any repository like UserRepository then i have to repeat again and again this lines of code UnitOfWorkFactory.Create() which i do not want rather i am looking for a way to embed unit of work code in some where centralize as a result i could reduce the repeated code.

    so looking for idea and suggestion. if possible give me modified version of code.

    thanks

    • Moved by DotNet Wang Friday, February 12, 2016 5:13 AM related to ado.net
    Thursday, February 11, 2016 1:11 PM

All replies

  • Hi Mou_kolkata,

    From my experience, we need to handle transactions by using unit of work for ado.net. the code snippet as below is in a transaction which handle a business logic. So we can find many code like this on your project because your project has many business logics. I would suggest you create a BLL layer, and write that code in the layer.

    using (var uow = UnitOfWorkFactory.Create())
    {
        var repos = new UserRepository(uow);
        //your business logic in a transaction
        uow.SaveChanges();
    }
    

    Best regards,

    Cole Wu

    Friday, February 12, 2016 7:02 AM
    Moderator
  • u do not understand my question. i want to know how to move the unit of work code into my repository class?

    if i could move it then repeatedly i do not have to use the below code when my BL would interact with DAL.

    using (var uow = UnitOfWorkFactory.Create())
    {
        var repos = new UserRepository(uow);
        //your business logic in a transaction
        uow.SaveChanges();
    }

    Friday, February 12, 2016 9:06 AM
  • Hi Mou_kolkata,

    >> if i could move it then repeatedly i do not have to use the below code when my BL would interact with DAL.

    From my experience, I need to use that code on my BL layer, if not, I can’t handle my business logic. Otherwise, you could create a Help class (by using delegate to make class method as parameter

    ) to handle your business logic.

    Best regards,

    Cole Wu

    Monday, February 15, 2016 9:34 AM
    Moderator