Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Retrieving data as objects using Enterprise library 5.0 database application block

, 14 Jan 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
CodeProjectEnterprise library 5 provided 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. There are two types of accessors. SprocAccessor for stored procedure and

Enterprise library 5 provided 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 example with SqlStringAccessor. Lets see an simple example of using CreateSqlAccessor.

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

Here company is my created DTO and column definition match 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 me IEnumarable of Customer. There are two types of output mapper. Row mapper which takes each rows and transform into object so that it returns sequence of these objects.Another one is Result set mappers, takes entire result set and generates a complete object graph.

Now 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 make it easy to create a custom output mapper. MapBuilder expose a method BuildAllProperties which creates default output mapping . For details about output mapping you can see the MSDN article http://msdn.microsoft.com/en-us/library/ff664486(v=pandp.50).aspx. Now lets see the implementation of row mapping for “Action” column.

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 convert the value to enum. Here database value 1 is converted with CompanyAction enum value. Now the GetCompanies function will look like this.

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

Accessors takes 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.

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 convert DbParameter for inputs and assign this to command. I have shown here simple implementation of this mapping.

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

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

Here you can see 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 create default output row mapping so we do not need to give extra effort to create O/ R mapping all time. SprocAccessor also provide same sets of feature as SqlStringAccessor provides.

License

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

Share

About the Author

Syed BASHAR
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 PinmemberAnil R8-Sep-11 20:15 
AnswerRe: Generic Usage of Data Accessor PinmemberSyed BASHAR9-Sep-11 7:43 
GeneralRe: Generic Usage of Data Accessor PinmemberAnil R11-Sep-11 5:30 
GeneralRe: Generic Usage of Data Accessor PinmemberAnil R11-Sep-11 5:34 
GeneralRe: Generic Usage of Data Accessor PinmemberSyed BASHAR11-Sep-11 6:16 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 15 Jan 2011
Article Copyright 2011 by Syed BASHAR
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid