悲观并发控制

 

    public abstract class EntityBase
    {
        private int Version { get; set; }
    }

 

    /// <summary>
    /// 当从数据库中检索出Person实体时设置Version属性。
    /// </summary>
    public class Person : EntityBase
    {
        public Guid Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Version { get; set; }
    }

 

    public interface IPersonRepository
    {
        void Add(Person person);
        void Save(Person person);
        Person FindBy(Guid Id);
    }

 

    public class PersionRepository : IPersonRepository
    {
        private string _connectionString;
        private string _findByIdSql = "select * from people where PersonId=@PersonId";
        private string _insertSql = "insert people (FristName,LastName,PersonId,Version) values (@FirstName,@LastName,@PersonId,@Version)";
        private string _updateSql = "update people SET FirstName=@FirstName,LastName=@LastName,Version=@Version+1 where PersonId=@PersonId AND Version=@Version";

        public PersionRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Add(Person person)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = _insertSql;
                command.Parameters.Add(new SqlParameter("@PersonId", person.Id));
                command.Parameters.Add(new SqlParameter("@Version", person.Version));
                command.Parameters.Add(new SqlParameter("@FirstName", person.FirstName));
                command.Parameters.Add(new SqlParameter("@LastName", person.LastName));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Save(Person person)
        {
            int numberOfRecordsAffected = 0;
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = _updateSql;
                command.Parameters.Add(new SqlParameter("@PersonId", person.Id));
                command.Parameters.Add(new SqlParameter("@Version", person.Version));
                command.Parameters.Add(new SqlParameter("@FirstName", person.FirstName));
                command.Parameters.Add(new SqlParameter("@LastName", person.LastName));
                connection.Open();
                numberOfRecordsAffected = command.ExecuteNonQuery();
            }
            if (numberOfRecordsAffected == 0)
                throw new ApplicationException("No changes....");
            else
            {
                person.Version++;
            }
        }

        public Person FindBy(Guid Id)
        {
            Person person = default(Person);
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = _findByIdSql;
                command.Parameters.Add(new SqlParameter("@PersonId", Id));
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        person = new Person
                        {
                            FirstName = reader["FristName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            Id = new Guid(reader["PersonId"].ToString()),
                            Version = int.Parse(reader["Version"].ToString())
                        };
                    }
                }
            }
            return person;
        }
    }