Click here to Skip to main content
15,867,957 members
Articles / All Topics

Retrieving Data as Objects using Enterprise Library 5.0 Database Application Block

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
14 Jan 2011CPOL3 min read 29.3K   3   5
How to retrieve data as objects using Enterprise Library 5.0 database application block

Enterprise library 5 provided a new extension which is called Accessors. Accessors execute the given query with parameters mapping and parameter values and also transform the result using output mapper you specified.

accessor

There are two types of accessors. SprocAccessor for stored procedure and SqlStringAccessor for SQL string. The most interesting part of accessors is mapping.

Here, I give an example with SqlStringAccessor. Let's see a simple example of using CreateSqlAccessor.

C#
public IEnumerable<Company> GetCompanies() 
      { 
          return _database.CreateSqlStringAccessor<Company>(GetCompaniesSQL()); 
      }

Here, company is my created DTO and column definition matches with properties of Company class. Here in this case, I have not given any custom output mapper and it used default mapper which matches property name and type with column of database and returns IEnumerable of Customer. There are two types of output mapper. Row mapper which takes each row and transform into object so that it returns sequence of these objects. Another one is Result set mappers, takes the entire result set and generates a complete object graph.

Now the problem is, I have a column in my Company table “Action” which stores value as Integer but in our code, this “Action” is defined as an Enum. So here, default mapping is not possible and we need to define a custom row mapper for converting the type of int to Enum type. Database application block provides a MapBuilder that makes it easy to create a custom output mapper. MapBuilder exposes a method BuildAllProperties which creates default output mapping . For details about output mapping, you can see this MSDN article. Now let's see the implementation of row mapping for “Action” column.

C#
public IRowMapper<Company> GetCompanyRowMapper() 
        { 
          return  MapBuilder<Company>.MapAllProperties().Map(m => m.Action).WithFunc( 
                    rec => (CompanyAction)Enum.ToObject(typeof(CompanyAction), 
                        rec.GetInt32(rec.GetOrdinal("Action")))). 
                    Build(); 
        }

When we call MappAllProperties ad, it gives IMapBuilderContext and after calling, build it create RowMapping. Here, after getting IMapBuildContext the property “Action” of Company class is mapped with a delegate function which works on IDataRecord and converts the value to enum. Here, database value 1 is converted with CompanyAction enum value. Now the GetCompanies function will look like this:

C#
public IEnumerable<Company> GetCompanies() 
      { 
          return _database.CreateSqlStringAccessor<Company>(GetCompaniesSQL(),
           GetCompanyRowMapper()).Execute(); 
      }

Accessors take rowmapper as input and it returns all companies. But if I need to get a company with company Id only which will return a single company then I also have to give company id as input parameter and create a parameter mapping. To create a custom parameter mapping, I have implemented IParameterMapper interface and mapping is assigned inside AssignParameters method body.

C#
private class CompanySelectParameterMapper : IParameterMapper 
       { 
           public void AssignParameters(DbCommand command, object[] parameterValues) 
           { 
               DbParameter parameter = null; 
               parameter = command.CreateParameter(); 
               parameter.ParameterName = "@Id"; 
               parameter.Value = parameterValues[0]; 
               command.Parameters.Add(parameter); 
           } 
       }

Here, it converts DbParameter for inputs and assigns this to command. I have shown here a simple implementation of this mapping.

So the function for getting a single company with company id is:

C#
public Company GetCompanyById(int id) 
       { 
           return _database.CreateSqlStringAccessor<Company>(GetCompanyById(), 
               new CompanySelectParameterMapper(),
               GetCompanyRowMapper()).Execute(id).SingleOrDefault(); 
       }

Here, you can see that I have created an object of parameter mapper and in the Execute() function, the values of parameters is defined. So the AssignParameter will be called when Accessor will call the Execute method and populate the command with parameter value. Here, you have seen how to retrieve data as object and how to define custom output and parameter mapping with Accessors. As it creates default output row mapping, so we do not need to give extra effort to create O/ R mapping all the time. SprocAccessor also provides the same set of features as SqlStringAccessor provides.

This article was originally posted at http://ciintelligence.blogspot.com/feeds/posts/default

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Bangladesh Bangladesh
Name SYED MD. ABUL BASHAR
Email ID: miltoncse00@gmail.com

I am now working as software engineer in Malaysia. I am from Bangladesh and I have completed my B.Sc (Engg.) in CSE from Rajshahi University of Engineering and Technology (RUET).I spend much time in learning latest technology.

My LinkedIn Profile : http://bd.linkedin.com/in/miltoncse00[^]

My blog :http://ciintelligence.blogspot.com/[^]

Comments and Discussions

 
QuestionGeneric Usage of Data Accessor Pin
Anil R8-Sep-11 19:15
Anil R8-Sep-11 19:15 
AnswerRe: Generic Usage of Data Accessor Pin
Syed BASHAR9-Sep-11 6:43
Syed BASHAR9-Sep-11 6:43 
GeneralRe: Generic Usage of Data Accessor Pin
Anil R11-Sep-11 4:30
Anil R11-Sep-11 4:30 
GeneralRe: Generic Usage of Data Accessor Pin
Anil R11-Sep-11 4:34
Anil R11-Sep-11 4:34 
GeneralRe: Generic Usage of Data Accessor Pin
Syed BASHAR11-Sep-11 5:16
Syed BASHAR11-Sep-11 5:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.