In this project, you will be creating a data layer for a Video Library application. You will be working with a Visual Studio class library project VideoLibraryDataLayer that contains public interface IVideoLibraryDAL defined as follows: 

    public interface IVideoLibraryDAL

    {

        /// <summary>

        /// Search Videos table using the following rules:

        ///    a. if Title is not null or empty string, search by Title (partial match)

        ///    b. if Director is not null or empty string, search by Director (partial match)

        ///    c. if Year is positive, search by Year (full match)

        ///    d. if more than one condition applies, use AND logic

        ///    e. do not return videos marked as deleted

        ///    f. if no search criteria is provided, return all videos

        ///    g. if search criteria cannot be satisfied, return empty collection

        /// </summary>

        /// <returns>Collection containing search results</returns>

        Collection<VideoSearchResult> SearchVideoLibrary(VideoSearchCriteria criteria);

        /// <summary>

        /// Create new row in Checkouts table using the following rules:

        ///     a. throw exception if user already has this video checked out

        ///     b. throw exception if TotalCopies value is 0

        ///     c. throw exception if videoId does not exist

        ///     d. throw exception if userId does not exist

        ///     e. successful checkout should decrement TotalCopies value 

        ///     f. throw exception if video is deleted

        /// </summary>

        void CheckOutVideo(int videoId, Guid userId);

        /// <summary>

        /// Update row in Checkouts table that corresponds to the last checkout using the following rules:

        ///     a. successful check-in should increment TotalCopies value

        ///     b. throw exception if corresponding checkout is not found

        ///     c. throw exception if videoId does not exist

        ///     d. throw exception if userId does not exist

        /// </summary>

        void CheckInVideo(int videoId, Guid userId);

        /// <summary>

        /// Add new row to the Reviews table using the following rules:

        ///     a. user can create multiple reviews

        ///     b. throw exception if videoId does not exist

        ///     c. throw exception if userId does not exist

        ///     d. throw exception if reviewText is null or empty

        ///     e. throw exception if video is deleted

        /// </summary>

        /// <returns>Integer representing ReviewID</returns>

        int AddReview(int videoId, Guid userId, string reviewText);

        /// <summary>

        /// Update existing row in the Reviews table using the following rules:

        ///     a. throw exception if reviewId does not exist

        ///     b. throw exception if reviewText is null or empty

        /// </summary>

        void UpdateReview(int reviewId, string reviewText);

        /// <summary>

        /// Add new or update existing row in the Ratings table using the following rules:

        ///     a. user can only have one rating for a video

        ///     b. throw exception if videoId does not exist

        ///     c. throw exception if userId does not exist

        ///     d. throw exception if rating is less than 1 or greater than 5

        /// </summary>

        void AddUpdateRating(int videoId, Guid userId, int rating);

        /// <summary>

        /// Add new or update existing row in the Videos table using the following rules:

        ///     a. create new record if VideoId property value is 0

        ///     b. update existing record if VideoId property value is positive

        ///     c. throw exception if user is not a member of the Administrator role

        ///     d. throw exception if user does not exist

        ///     e. throw exception if VideoId is positive but video does not exist

        ///     f. throw exception if Title, Year, Director values are missing

        ///     g. throw exception if TotalCopies value is negative

        /// </summary>

        /// <returns>Integer representing VideoID</returns>

        int AddUpdateVideo(VideoInfo video, Guid userId);

        /// <summary>

        /// Mark video as deleted by setting IsDeleted column value to 1 using the following rules:

        ///     a. throw exception if video has pending checkouts

        ///     b. throw exception if user does not exist

        ///     c. throw exception if user is not a member of the Administrator role

        ///     d. throw exception if video does not exist

        /// </summary>

        void DeleteVideo(int videoId, Guid userId);

    }

Classes such as VideoSearchCriteria and VideoSearchResult are already declared within the project. Please download VideoLibraryDataLayer.zip from the Assignments section of the Blackboard. Your goal is to create two implementations of this interface: one using ADO.NET (either straight up or with Enterprise Library Data Access Application Block), and another using Entity Framework. I am going to use a suite of automated unit tests to verify the implementations. The fewer unit tests fail, the higher your grade is going to be. Maximum number of points for this project is 30.

Please add two new class library projects to the solution and have them both reference VideoLibraryDataLayer project. First class library should implement IVideoLibraryDAL using ADO.NET, and second class library should implement it using Entity Framework. For EF, I recommend to use code-first approach. Do not copy classes from VideoLibraryDataLayer to your new projects. For your own unit testing, you may add another project, e.g. Windows Forms, Console App, or Test Project.

The code must throw exceptions as specified in the requirements. Some conditions are automatically checked by SQL Server (e.g., invalid userId value) and violations will result in a SqlException. It is OK to let SqlException bubble up – test suite does not require specific exception type.

Database is provided as a backup file VideoLibraryDB.bak that you can download from the Assignments section of the Blackboard and restore on your SQL Server 2012 instance. Example of the RESTORE command (you may need to adjust file paths as necessary):

USE [master]

RESTORE DATABASE [VideoLibraryDB]

FROM  DISK = N'C:\UCSD\VideoLibraryDB.bak' 

WITH  FILE = 1,  

MOVE N'VideoLibrary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibraryDB.mdf',

MOVE N'VideoLibrary_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\VideoLibraryDB_Log.ldf',

NOUNLOAD, STATS = 5

This assignment has been answered 3 times in private sessions.