Puneet Ghanshani

Chief Architect at Microsoft

6 minute read

This is in continuation to my previous post Silverlight Best Practices – III, where the focus was what should be and what should not be done while developing a Silverlight Application. It was purely from a developer’s perspective.  In this post, we would consider designing the ‘Data Access Layer’

Designing Data Access Layer

In most of the web applications, data access layer comprises of 3 components:

  • Utilities – that help in accessing data components. Example, converters and alike
  • Data components – core components that access the data sources
  • Service Agents – this is an additional layer (proxies) over the services that are called from DAL. Mostly done to bring an additional isolation, entity mapping, or service-specific conversions.

Here, we will concentrate on ‘Data Components’ as the other two components are relatively easier to design and readily available as well or tools can generate them too. 

Managing Connections

Which connection remains open/closed should be monitored by a single interface. All calls should be redirected to a single function which would manage opening, closing of a connection and its timeout.  Connections can be cached for smallest duration of time to increase performance – however, security should of sensitive information like hostname, password should be taken care of as well.

The application should also take care that the fan-out does not reach.  This will ensure that no user gets a timeout error.

Design should enable switching between two replicas of data sources. This is most important during maintenance shutdown, or release of a newer version of application, or when database crashes. 

Exception Handling

DAL should ideally handle all exceptions and should not crash in any scenario; however all CRUD operations MUST be handled by DAL.

Exceptions concerning data access (data source unavailable, timeout, etc) should be handled by DAL; while other business related errors should be returned in an Error object in a serialized format.

Profile for best performance

In Internet applications DAL is the most accessed-by, making it prone to crashes and security risks.  Profiling of DAL is must to gauge the impact of high number of concurrent users.  Also, in such scenario DAL should be prevented against attacks from hackers.  Any other source of access should be give least privilege and only the web application should be at a higher privilege.

Consider using Batch Processes to reduce round-trip to database server.

Managing data

One of the common problems seen in many projects while doing profiling exercises is degraded performance due to improper data handling.  So let us see some important guidelines:-

  • If the system involves lot of documents and image storing – prefer to store them in BLOB, than on a file system
  • Avoid Outer Joins wherever possible
  • Use of cursors should not be preferred – use in-memory temporary tables instead.
  • Open connections as late as possible.
  • Use XML pameters for bulk inserts or updates – this will save execution time.
  • Use parameterized SQL statements and typed parameters to mitigate security issues and reduce the chance of SQL injection attacks succeeding.
  • Do not use string concatenation to build dynamic queries in the data layer.
  • Use typed parameters as input values to the procedure and output parameters to return single values.
  • Use optimistic concurrency with non-volatile data to mitigate the cost of locking data in the database

 

Dynamic Queries or Stored Procedure

 

  1. For a small footprint application with lesser clients and few business rules, prefer Dynamic Queries. IF NO, STEP 2
  2. Larger application, multiple clients – abstraction can be at
    1. Database level in Stored Proc – minimal code changes
    2. DAL using patterns  - best till the schema does not change, can be debugged
    3. DAL using ORM - best till the schema does not change, can be debugged

 

ADO.NET Services

I would suggest a very good tutorial on MSDN. Click here to read through.

This concludes the series of best practices articles on Silverlight. Thanks for reading them through!


You May Also Enjoy