The Inverted Software DataBlock Quick Start Tutorial

The Inverted Software DateBlock has been recently enhanced, so I thought I would take the opportunity to write a short quick start tutorial that would help you get started with incorporating it into your application:

Getting objects

CRUDHelper.GetObject<Category>(() => new Category(), "GetCategory", mainConnectionString, new SqlParameter("@categoryCode", SqlDbType.VarChar, 200) { Value = "myValue" });

Getting collections

CRUDHelper.GetObjectList<Category>(() => new Category(), "GetCategories", mainConnectionString);

For a paged list use:

CRUDHelper.GetObjectList<Category>(() => new Category(), 0, 10, "GetCategories", mainConnectionString, out virtualTotal);

Getting parent child collections

If you use a single stored procedure to retrieve parent / child objects you have two methods of populating a list of parents and children:

You can use an inner join query:

SELECT
   p.ProductID AS Product_ProductID,
   c.CategoryID AS Product_CategoryID,
   c.CategoryID AS Category_CategoryID,
   c.CategoryName AS Category_CategoryName,
   p.ProductName AS Product_ProductName

  FROM
   Product p INNER JOIN ProductCategory pc ON
   p.ProductID = pc.ProductID
   INNER JOIN Category c WITH(NOLOCK) ON
   pc.CategoryID = c.CategoryID

with the following method:

CRUDHelper.GetEagerLoadedObjectListFromInnerJoinQuery<Category, Product>(() => new Category(), () => new Product(), "GetCategoriesWithProducts", mainConnectionString);

Or multiple result sets:

 -- First select

  SELECT
 CategoryID,
 CategoryName
 FROM 
 Category WITH(NOLOCK)


-- second select

SELECT
   p.ProductID,
   c.CategoryID,
   p.ProductName

  FROM
   Product p INNER JOIN ProductCategory pc ON
   p.ProductID = pc.ProductID
   INNER JOIN Category c WITH(NOLOCK) ON
   pc.CategoryID = c.CategoryID 

with the following:

CRUDHelper.GetEagerLoadedObjectListFromMultipleResults<Category, Product>(() => new Category(), () => new Product(), "GetCategoriesWithProducts", mainConnectionString);

Getting collections of immutable objects

You can get a list of integers, strings or decimals.

CRUDHelper.GetIntList("GetCategoriesWithProducts", "CategoryID", mainConnectionString);

CRUDHelper.GetStringList("GetCategoriesWithProducts", "CategoryName", mainConnectionString);

Creating records from objects

CRUDHelper.AddObject<Category>(category, "AddCategory", mainConnectionString);

Updating objects

CRUDHelper.UpdateObject<Category>(category, "UpdateCutegory", mainConnectionString);

Deleting records

CRUDHelper.DeleteObject<Category>(category, "DeleteCategory", mainConnectionString);

Working with output parameters

public static List<T> GetObjectList<T>(Func<T> generator, int pageIndex, int rowsPerPage, string sprocName, string stringConnection, out int virtualTotal, params SqlParameter[] commandParameters)
       {
           List<T> objectList = new List<T>();

           SqlParameter[] paramArray = new SqlParameter[]{ 
               new SqlParameter("@PageIndex", SqlDbType.Int){ Value = pageIndex},
               new SqlParameter("@PageSize", SqlDbType.Int){ Value = rowsPerPage},
               new SqlParameter("@TotalRecords", SqlDbType.Int){ Direction = ParameterDirection.ReturnValue }
           };

           if (commandParameters != null)
               paramArray = paramArray.Concat(commandParameters).ToArray();

           SqlCommand cmd = SqlHelper.CommandPool.GetObject();
           using (SqlConnection conn = new SqlConnection(stringConnection))
           {
               try
               {
                   SqlHelper.PrepareCommand(cmd, conn, null, CommandType.StoredProcedure, sprocName, paramArray);
                   using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                   {
                       PropertyInfo[] props = ObjectHelper.GetDataObjectInfo<T>().Properties;
                       List<string> columnList = ObjectHelper.GetColumnNames(rdr, sprocName);
                       T newobject;
                       while (rdr.Read())
                       {
                           newobject = generator();
                           ObjectHelper.LoadAs<T>(rdr, newobject, props, columnList, sprocName);
                           objectList.Add(newobject);
                       }
                   }
                   virtualTotal = Convert.ToInt32(paramArray.Where(p => p.ParameterName == "@TotalRecords").First().Value);
                   cmd.Parameters.Clear();
               }
               catch (Exception e)
               {
                   throw new DataBlockException(String.Format("Error Getting object list {0}. Stored Procedure: {1}", typeof(T).FullName, sprocName), e);
               }
               finally
               {
                   SqlHelper.CommandPool.PutObject(cmd);
               }
           }

           return objectList;
       }

Transactions

There are two ways of using transactions with the DataBlock:

SqlTransaction

public static int AddCategoryWithSqlTransaction()
       {
           int newRecordID = 0;
           using (SqlConnection connection = new SqlConnection(mainConnectionString))
           {
               connection.Open();
               SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
               SqlParameter[] paramArray = new SqlParameter[]{
                   new SqlParameter("@ParentCategoryID", SqlDbType.Int){ Value = DBNull.Value},
                   new SqlParameter("@CategoryName", SqlDbType.VarChar, 50) { Value = "test1" },
                   new SqlParameter("@Active", SqlDbType.Bit) { Value = true }
               };

               try
               {
                   newRecordID = Convert.ToInt32(SqlHelper.ExecuteScalar(connection, transaction, CommandType.StoredProcedure, "AddCategory", paramArray));
                   transaction.Commit();
               }
               catch (Exception ex)
               {
                   transaction.Rollback();
               }
           }
           return newRecordID;
       }

And ScopeTransaction

public static int AddCategoryWithTransactionScope()
       {
           int newRecordID = 0;
           using (TransactionScope scope = new TransactionScope())
           {
               SqlParameter[] paramArray = new SqlParameter[]{
                   new SqlParameter("@ParentCategoryID", SqlDbType.Int){ Value = DBNull.Value},
                   new SqlParameter("@CategoryName", SqlDbType.VarChar, 50) { Value = "test2" },
                   new SqlParameter("@Active", SqlDbType.Bit) { Value = true }
               };

               try
               {
                   newRecordID = Convert.ToInt32(SqlHelper.ExecuteScalar(mainConnectionString, CommandType.StoredProcedure, "AddCategory", paramArray));
                   scope.Complete();
               }
               catch (Exception ex)
               {
                   throw ex;
               }
           }
           return newRecordID;
       }

Object attributes

The DataBlock uses property level attributes to perform mapping on CRUD operations as well as parent / child objects.

Here is an example of typical attribute mapping:

public class Category
  {
      [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
      [CrudField(UsedFor = CrudFieldType.Delete | CrudFieldType.Read | CrudFieldType.Update)]
      public int CategoryID { get; set; }
      public int? ParentCategoryID { get; set; }
      public string CategoryName { get; set; }
      [CrudField(UsedFor = CrudFieldType.DontUse)]
      public List<Product> CategoryProducts { get; set; }
  }

public class Product
  {
      [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
      [CrudField(UsedFor = CrudFieldType.Delete | CrudFieldType.Read | CrudFieldType.Update)]
      public int ProductID { get; set; }
      public string ProductName { get; set; }
      [ForeignKey("CategoryID")]
      [CrudField(UsedFor = CrudFieldType.DontUse)]
      public int CategoryID { get; set; }
  }

The DataBlock is available as a NuGet package and the source is provided on GitHub.

---