Gal Ratner
Gal Ratner is a Techie who lives and works in Los Angeles CA and Austin TX. Follow galratner on Twitter Google
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<CategoryProduct>(() => 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<CategoryProduct>(() => 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, nullCommandType.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 { getset; }
       public int? ParentCategoryID { getset; }
       public string CategoryName { getset; }
       [CrudField(UsedFor = CrudFieldType.DontUse)]
       public List<Product> CategoryProducts { getset; }
   }

 

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

 

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

 


Posted 19 Mar 2014 3:54 PM by Gal Ratner
Filed under:

Powered by Community Server (Non-Commercial Edition), by Telligent Systems