Tuesday, 29 November 2016

How can I create a generic data repository for my Entity Framework solution?

I recently created a quick console application that required CRUD activities on a SQL database. I had a few tables to update and did not want the headache of creating multiple items in my DAL.

Enter a generic data repository.

The code is pretty simple:

1. Create a base interface for the CRUD operations

public interface IGenericDataRepository<T> where T : class
    {
        List<T> GetAll(params Expression<Func<T, object>>[] navigationProperties);
        List<T> GetList(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties);
        T GetSingle(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties);
        void Add(params T[] items);
        void Update(params T[] items);
        void Remove(params T[] items);
        void RemoveAll(string tableName);
        void ExecuteSQLCommand(string sqlCommand, SqlParameter[] parameters);
    }

2. Create your Entity Framework data model. I called my ImportEntities.

3. Create a repository class that uses the interface

 public class GenericDataRepository<T> : IGenericDataRepository<T> where T : class
    {
        public virtual List<T> GetAll(params Expression<Func<T, object>>[] navigationProperties)
        {
            List<T> list;
            using (var context = new ImportEntities())
            {
                IQueryable<T> dbQuery = context.Set<T>();

                //Apply eager loading
                foreach (Expression<Func<T, object>> navigationProperty in navigationProperties)
                    dbQuery = dbQuery.Include<T, object>(navigationProperty);

                list = dbQuery
                    .AsNoTracking()
                    .ToList<T>();
            }
            return list;
        }

        public virtual List<T> GetList(Func<T, bool> where,
             params Expression<Func<T, object>>[] navigationProperties)
        {
            List<T> list;
            using (var context = new ImportEntities())
            {
                IQueryable<T> dbQuery = context.Set<T>();

                //Apply eager loading
                foreach (Expression<Func<T, object>> navigationProperty in navigationProperties)
                    dbQuery = dbQuery.Include<T, object>(navigationProperty);

                list = dbQuery
                    .AsNoTracking()
                    .Where(where)
                    .ToList<T>();
            }
            return list;
        }

        public virtual T GetSingle(Func<T, bool> where,
             params Expression<Func<T, object>>[] navigationProperties)
        {
            T item = null;
            using (var context = new ImportEntities())
            {
                IQueryable<T> dbQuery = context.Set<T>();

                //Apply eager loading
                foreach (Expression<Func<T, object>> navigationProperty in navigationProperties)
                    dbQuery = dbQuery.Include<T, object>(navigationProperty);

                item = dbQuery
                    .AsNoTracking() //Don't track any changes for the selected item
                    .FirstOrDefault(where); //Apply where clause
            }
            return item;
        }

        public virtual void Add(params T[] items)
        {
            try
            {
                using (var context = new ImportEntities())
                {
                    foreach (T item in items)
                    {
                        context.Entry(item).State = EntityState.Added;
                    }
                    context.SaveChanges();
                }
            }
            catch (DbEntityValidationException dbEx)
            {
                foreach (var validationErrors in dbEx.EntityValidationErrors)
                {
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                        Console.WriteLine("Property: {0} Error: {1}",
                                                validationError.PropertyName,
                                                validationError.ErrorMessage);
                    }
                    Console.ReadLine();
                }
            }

        }

        public virtual void Update(params T[] items)
        {
            using (var context = new ImportEntities())
            {
                foreach (T item in items)
                {
                    context.Entry(item).State = EntityState.Modified;
                }
                context.SaveChanges();
            }
        }

        public virtual void Remove(params T[] items)
        {
            using (var context = new ImportEntities())
            {
                foreach (T item in items)
                {
                    context.Entry(item).State = EntityState.Deleted;
                }
                context.SaveChanges();
            }
        }

        public virtual void RemoveAll(string tableName)
        {
            using (var context = new ImportEntities())
            {
                context.Database.ExecuteSqlCommand(string.Format("TRUNCATE TABLE [{0}]", tableName));
            }
        }

        public virtual void ExecuteSQLCommand(string sqlCommand, SqlParameter[] parameters)
        {
            using (var context = new ImportEntities())
            {
                context.Database.ExecuteSqlCommand(sqlCommand, parameters);
            }
        }
    }

4. Now, we can access the repository in code: For example, I have a table called 'TempTable' configured with my Entity Framework data model, I can then instantiate the repository like so:

var repository = new GenericDataRepository<TempTable>();

and use it as follows:
- clear the list: 
repository.RemoveAll("TempTable");
- add an item:
repository,add(new TempTable() { Id = 1, Name = "test" });
- execute a stored procedure:
var parameters = new SqlParameter[]
                 {
                    new SqlParameter("ExecutionInstance",Guid.NewGuid()),
                    new SqlParameter("Sequence", 1)
                 };
            repository.ExecuteSQLCommand("dbo.MyProc @ExecutionInstance, @Sequence", parameters);

Monday, 28 November 2016

Azure: How do I create an MVC site to load files to BLOB storage?

I recently created a simple POC site to load files to blob storage containers in Azure. Here is how I achieved it.

My code is based on the sample provided here

After creating a simple MVC web project, I added the following nuget packages:

DropZone
WindowsAzure.Storage
Microsoft.WindowsAzure.ConfigurationManager

The code is pretty simple:

Here is Index.cshtml:

@model List<string>

<script src="~/Scripts/dropzone/dropzone.min.js"></script>
<script src="~/Scripts/bootstrap.min.js"></script>
<link href="~/Scripts/dropzone/dropzone.min.css" rel="stylesheet" />

<div class="jumbotron">
    <h2>FILE UPLOAD</h2>
    <form action="~/Home/Upload"
          class="dropzone"
          id="dropzoneJsForm"
          style="background-color:#00BFFF"></form>
   
    <button id="refresh" onclick="window.location.reload();">
        Refresh
    </button>

    <table>
        @foreach (var i in Model)
        {
            <tr>
                <td>@i</td>
            </tr>
        }
    </table>
</div>

<script type="text/javascript">

    Dropzone.options.dropzoneForm =
    {
        init: function () {
            this.on("complete", function (data) {
                var res = JSON.parse(data.xhr.responseText);
            });
        }
    };
</script>

My HomeController is pretty simple as well:

 public class HomeController : Controller
    {
        public ActionResult Index()
        {
            var container = BLOBHelper.GetContainer("mycontainer", true);
            var items = BLOBHelper.GetBlobItems(container);
            return View(items);
        }

        public ActionResult Upload()
        {

            foreach (string f in Request.Files)
            {
                HttpPostedFileBase file = Request.Files[f];
                //fName = file.FileName;
                if (file != null && file.ContentLength > 0)
                {
                    var fileName = Path.GetFileName(file.FileName);
                    var container = BLOBHelper.GetContainer("mycontainer", true);
// optionally set access permissions                  
// container.SetPermissions(new BlobContainerPermissions { PublicAccess = BlobContainerPublicAccessType.Blob });
                    BLOBHelper.UploadFile(container, file,  fileName);
                }
            }
            return RedirectToAction("Index");
        }

But the core of the application is the BLOBHelper class:

  public class BLOBHelper
    {
       // lists all the items in the container at the root level
        public static List<string> GetBlobItems(CloudBlobContainer container)
        {
            List<string> items = new List<string>();

            foreach (IListBlobItem item in container.ListBlobs(null, false))
            {
                if (item.GetType() == typeof(CloudBlockBlob))
                {
                    CloudBlockBlob blob = (CloudBlockBlob)item;
                    items.Add(blob.Uri.ToString());
                }
                else if (item.GetType() == typeof(CloudPageBlob))
                {
                    CloudPageBlob pageBlob = (CloudPageBlob)item;
                    items.Add(pageBlob.Uri.ToString());
                }
                else if (item.GetType() == typeof(CloudBlobDirectory))
                {
                    CloudBlobDirectory directory = (CloudBlobDirectory)item;
                    items.Add(directory.Uri.ToString());
                }
            }

            return items;
        }

        // uploads a file to a container
        public static bool UploadFile(CloudBlobContainer container, HttpPostedFileBase file,  string fileName)
        {
            CloudBlockBlob blockBlob = container.GetBlockBlobReference(fileName);

            using (var reader = new BinaryReader(file.InputStream))
            {
                byte[] imgData = reader.ReadBytes(file.ContentLength);
                using (var fileStream = new MemoryStream(imgData))
                {
                    blockBlob.UploadFromStream(fileStream);
                }
            }

            return true;
        }

        // get / create a container
        public static CloudBlobContainer GetContainer(string name, bool createIfNotExists = false)
        {
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
  Microsoft.Azure.CloudConfigurationManager.GetSetting("StorageConnectionString"));
            CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
            CloudBlobContainer container = blobClient.GetContainerReference(name);
            if (createIfNotExists) {
                container.CreateIfNotExists();
            }
            return container;
        }

Dont forget to add the required web.config settings:

<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
 <appSettings>
      <add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=CHANGEME;AccountKey=CHANGEME" />
  </appSettings>
</configuration>