Resository Pattern and Unit of Work with ADO.net

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.