2
\$\begingroup\$

I am trying to implement the Repository Pattern with a Unit of work but i dont know if i am on the right direction. This is my Model

public class Resource
{
    public string RessourceUID { get; set; }
    public string RessourceName { get; set; }
    public List<Team> Teams { get; set; }
    public string Loginname { get; set; }
}

This is my Repository

public class ResourceRepository
{
    public static Resource GetResourceByLoginname(string loginname)
    {
        return RunWithElevatedPrivileges(() => GetResourceByLoginnameInternal(loginname));
    }

    private static Resource GetResourceByLoginnameInternal(string loginname)
    {
        return ExecuteSql(cmd =>
        {
            Resource resource;

            cmd.CommandText = @"SELECT convert(nvarchar(36), ResourceUID) AS ResourceUID, UserClaimsAccount, ResourceName FROM MSP_EpmResource_UserView WHERE UserClaimsAccount = @userclaimsaccount";
            cmd.Parameters.AddWithValue("@userclaimsaccount", SqlDbType.NVarChar).Value = loginname;

            using (var dr = cmd.ExecuteReader())
            {
                if (!dr.HasRows)
                    return null;

                resource = new Resource();

                while (dr.Read())
                {
                    MapResourceData(resource, dr);
                    resource.Teams = RunWithElevatedPrivileges(() => GetResourceTeams(resource));
                }
            }

            return resource;
        });

    }

     private static void MapResourceData(Resource resource, SqlDataReader dr)
    {
        resource.RessourceUID = SqlReaderHelper.GetValue<string>(dr, "ResourceUID");
        resource.RessourceName = SqlReaderHelper.GetValue<string>(dr, "ResourceName");
        resource.Loginname = SqlReaderHelper.GetValue<string>(dr, "UserClaimsAccount");
    }

    private static T RunWithElevatedPrivileges<T>(Func<T> action)
    {
        var result = default(T);

        Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(() => result = action());

        return result;
    }

    private static void ExecuteSql(Action<SqlCommand> action)
    {
        string connectionString = Connectivity.GetConnectionString();

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            var cmd = conn.CreateCommand();

            action(cmd);
            cmd.ExecuteNonQuery();
        }
    }

    private static T ExecuteSql<T>(Func<SqlCommand, T> action)
    {
        T result;

        string connectionString = Connectivity.GetConnectionString();

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            var cmd = conn.CreateCommand();

            result = action(cmd);
            cmd.ExecuteNonQuery();
        }

        return result;
    }
}
  • Is this a good design?
  • Is this the Repository Pattern?
  • What could i optimize?
  • I absolutely dont know how to implement the Unit of work. How could i do this?
  • Should i move the ExecuteSql methods out of the Repository?
  • Should i also move the MapXXXData methods out of this Repository?

Thanks for your help.

\$\endgroup\$
1
\$\begingroup\$

It's a good start. Here are the changes I suggest:

I think you should be able to execute the queries without sharepoint. To make it possible we'll remove the sharepoint stuff from the repository and put it in another one. We then combine them with a decorator pattern like this:

The first step is to extract an interface (or an abstract class):

public interface IResourceRepository 
{ 
    Resource GetByLoginname(string loginname);
}

Then we implement it in the first repository that will get resources from the database:

public class ResourceRepository : IResourceRepository
{
    public Resource GetByLoginname(string loginname)
    {
        return ExecuteSql(cmd =>
        {
            cmd.CommandText = @"SELECT convert(nvarchar(36), ResourceUID) AS ResourceUID, UserClaimsAccount, ResourceName FROM MSP_EpmResource_UserView WHERE UserClaimsAccount = @userclaimsaccount";
            cmd.Parameters.AddWithValue("@userclaimsaccount", SqlDbType.NVarChar).Value = loginname;

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var resource = Resource.From(reader);
                    if (resource != null)
                    {
                        resource.Teams = GetResourceTeams(resource);
                    }
                    return resource;
                }   
                return null;            
            }               
        });
    }   

    private static void ExecuteSql(Action<SqlCommand> action)
    {
        string connectionString = Connectivity.GetConnectionString();

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            var cmd = conn.CreateCommand();

            action(cmd);
            cmd.ExecuteNonQuery();
        }
    }

    private static T ExecuteSql<T>(Func<SqlCommand, T> action)
    {
        T result;

        string connectionString = Connectivity.GetConnectionString();

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            var cmd = conn.CreateCommand();

            result = action(cmd);
            cmd.ExecuteNonQuery();
        }

        return result;
    }
}

Now you need the elevated privilages for sharepoint. So lets create a decorator for this one:

public class ElevatedPrivilegesResourceRepository : IResourceRepository
{
    private readonly IResourceRepository _resourceRepository;

    public ElevatedPrivilegesResourceRepository(IResourceRepository resourceRepository)
    {
        _resourceRepository = resourceRepository;
    }

    public Resource GetByLoginname(string loginname)
    {
        var result = default(Resource);

        Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(
            () => result = _resourceRepository.GetByLoginname(loginname));

        return result;
    }
}

You will pass an instance of the first repository to this one and it will execute the query with sharepoint's elevated privilages.

var repo = new ElevatedPrivilegesResourceRepository(new ResourceRepository());

var resource = repo.GetByLoginname("foo");

There is one more change that I've made. I put the cretion of a resource into a factory method of this class:

public class Resource
{
    public string RessourceUID { get; set; }
    public string RessourceName { get; set; }
    public List<Team> Teams { get; set; }
    public string Loginname { get; set; }

    public static Resource From(SqlDataReader reader)
    {
        var resource = new Resource
        {
            RessourceUID = SqlReaderHelper.GetValue<string>(dr, "ResourceUID"),
            RessourceName = SqlReaderHelper.GetValue<string>(dr, "ResourceName"),
            Loginname = SqlReaderHelper.GetValue<string>(dr, "UserClaimsAccount")
        }

        return resource;
    }
}

and I also modified the query itself:

using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        var resource = Resource.From(reader);
        if (resource != null)
        {
            resource.Teams = GetResourceTeams(resource);
        }
        return resource;
    }
    return null;                
}

As you are looking for only one resource the reader won't proceed if there is none so you don't have to check whether it has any rows. You can just return the result after the first one.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.