Resumé

Automatic CRUD Procedures using Reflection

Cristian Merighi () 0.00

This stub shows a solution of mine, designed to limit the number of code lines used to transfer informations (data) through the various tiers of an object oriented application.
This article is obsolete. Some functionalities might not work anymore. Comments are disabled.

This article is a stub which delines an architecture of mine which permits automatic insertion, update and deletion for BLL entities using Reflection.

The idea is to map every single property of a BLL object instance to a database column and to a specific (query/stored procedure) parameter in order to keep trace of the original bindings which caused the entity population.

My goal is to avoid hard coded parameters addition for insert and update procedure: I'm experiencing tedious moments handling classes with hundreds - yes, I said hundreds - of properties.
I have to map columns, write stored procedures for creating, retrieving, updating, deleting (let's say CRUDding) which envolve too many rows of C# code. The worst thing about this scenario is that the structure of these classes is under continuous evolution! I should modify my scripts at least four times (once each CRUD step)...

Enough!

I've started to think about a more automatized solution which could be easily reused...

Here's a schema to describe the concept architecture:

schema
  • The Pacem.Reflection.Accessible subclass is my Business Logic Entity.
  • The Pacem.Data.Adapter permits to translate data configuration between BLL tier and DAL tier and can be instantiated using an XML file with a precise XSD schema.
  • The Pacem.Data.QueryManager executes and retrieves data in strict connection with the underline database (it can handle Transactions).

That's the overview. Let's see a brief code sample to show how it works:
Here's the XML file we use to Deserialize the Adapter object:

<?xml version="1.0" encoding="utf-8" ?>
<Adapter SourceTable="tblTest" EntitiesTable="TestTable">
  <RefreshCommand>    
    SELECT * FROM tblTest WHERE ID = @ID
  </RefreshCommand>
  <InsertCommand CommandType="Text" RequiresTransaction="true">
    INSERT INTO tblTest(strString) VALUES(@String); 
    SELECT @NewID = SCOPE_IDENTITY();
  </InsertCommand>
  <UpdateCommand>
    UPDATE tblTest SET strString = @String WHERE ID = @ID
  </UpdateCommand>
  <DeleteCommand>
    DELETE FROM tblTest WHERE ID = @ID
  </DeleteCommand>
  <Columns>
    <ColumnMapper 
      SourceColumn="ID"
      EntityColumn="ID" 
      UpdateParameter="@ID" 
      DeleteParameter="@ID"
      InsertParameter="@NewID" 
      InsertParameterDirection="Output" 
      RefreshParameter="@ID"
      DbType="Int32"  />
    <ColumnMapper 
      SourceColumn="strString" 
      EntityColumn="TestString" 
      InsertParameter="@String" 
      UpdateParameter="@String" 
      DbType="String" />
  </Columns>
</Adapter>

Here's the BLL class used for testing:

public class TestBLL : Pacem.Reflection.Accessible
{
    [Pacem.Reflection.Field("ID", IsPrimaryKey = true, IsReadOnly = true, EmptyValue = 0)]
    private int _ID = 0;

    [Pacem.Reflection.Field("TestString", EmptyValue = "")]
    private string _Test = string.Empty;

    public int ID { get { return _ID; } }

    public string TestString
    {
        get { return _Test; }
        set { _Test = value; }
    }
}

Now, the code used to insert a new TestBLL row into a SQL database and to "refresh" it in order to retrieve its identity property value:

TestBLL test = new TestBLL();
test.Adapter = GetBLLAdapter();
// setting property value
test.TestString = string.Format("{0:dd/MM/yyyy HH':'mm':'ss}", DateTime.Now);
// "as it gets saved it gets also refreshed"
test.RefreshOnSaved = true;
// saving an Accessible instance means
// if the instance got populated using database data: updating
// otherwise: inserting
test.Save();
// retrieve the just inserted identity
Response.Write(test.ID);

// centralized Adapter persister
public static Pacem.Data.Adapter GetBLLAdapter()
{
    Pacem.Data.QueryManager q = new Pacem.Data.QueryManager(
        ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, 
        Pacem.Data.DataBaseType.Sql
    );
    StreamReader sr = new StreamReader(Server.MapPath("~/Adapter.xml"));
    Pacem.Data.Adapter adapter = Pacem.Data.Adapter.Create(sr.BaseStream);
    sr.Close();
    adapter.QueryManager = q;
    return adapter;
}

No code is provided for this article:

  1. because it has just been developed. I'll heavily test this micro-framework in my next job;
  2. it is designed on limited scenarios. I'll try to expand it and make it more flexible (in my next job);
  3. don't even know if I'll ever release it ;)

Take care. Bye.

feedback
 

Syndicate

Author

Cristian Merighi facebook twitter google+ youtube

Latest articles

Top rated

Archive

Where am I?

Author

Cristian Merighi facebook twitter google+ youtube

I'm now reading

Feeds