InvertedSoftware.DataBlock.Core 1.0.2

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; } }