Data Layer - SqlServer

After having implemented a data layer in the Data project, it was time to make a real data implementation. A Sql Server 2000 implementation was the default data source, located in the Data.SqlServer project.

Enterprise Library was used to provide the data access to Sql Server. This contained a Data Access Application Block, which allows configuring the connection string through the Enterprise Library Configuration tool.

A reference to Microsoft.Practices.EnterpriseLibrary.Data was needed, together with the Configuration and Common assemblies of Enterprise Library.

Through the Enterprise Library Configuration tool, an existing App.config was loaded, where the Data Access Application Block was added. The database and server values had to be configured to the actual server being used, together with the database containing the data. Additional connection string properties could be added as well, for example, the Integrated Security property, which is set to True.



After saving this file, it was possible to create a data implementation for each Accessor interface previously defined in the Data project, as for example this code:

 

using System;

using System.Data;

using System.Collections;

 

using MediaService.Logging;

using MediaService.Objects;

using MediaService.Data.Accessors;

 

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.Logging;

 

namespace MediaService.Data.SqlServer {

  public class SongDataAccessor: ISongDataAccessor {

  } /* SongDataAccessor */

} /* MediaService.Data.SqlServer */


Thanks to the Enterprise Library Data Access Application Block, the Sql Server implementation used best practices from the Microsoft Patterns & Practices group, which followed Microsoft guidelines and were optimized for performance.

To get an array of objects from the database, a new Database object had to be created, after which a stored procedure was wrapped, called and read from to get for example Song objects. This was done with the following code:

 

public Song[] GetSongs() {

  Database db = DatabaseFactory.CreateDatabase("MediaServiceSqlServer");

 

  DBCommandWrapper dbCommandWrapper =

                        db.GetStoredProcCommandWrapper("GetSongs");

 

  Logger.Write("Retrieving songs.", Category.SqlServer,

                Priority.Lowest, 1, Severity.Information);

 

  ArrayList songs = new ArrayList();

  using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper)) {

    while (dataReader.Read()) {

      songs.Add(new Song(dataReader.GetInt32(0), dataReader.GetString(1),

                         dataReader.GetString(2), dataReader.GetString(3),

                         dataReader.GetString(4), dataReader.GetString(5),

                         dataReader.GetString(6), dataReader.GetInt32(7),

                         dataReader.GetInt32(8), dataReader.GetInt32(9)));

    }

  }

 

  Logger.Write(String.Format("Retrieved {0} {1}.", songs.Count,

                             (songs.Count == 1) ? "song" : "songs"),

               Category.SqlServer, Priority.Lowest, 1, Severity.Information);

 

  return (Song[])songs.ToArray(typeof(Song));

} /* GetSongs */


Updating an item by using a stored procedure which uses parameters, was done by using the following code:

 

public void UpdateSongPlayCount(Int32 songId) {

  Database db = DatabaseFactory.CreateDatabase("MediaServiceSqlServer");

 

  DBCommandWrapper dbCommandWrapper =

                        db.GetStoredProcCommandWrapper("UpdateSongPlayCount");

  dbCommandWrapper.AddInParameter("@songId", DbType.Int32, songId);

 

  Logger.Write(String.Format("Updating play count for song: {0}.", songId),

               Category.SqlServer, Priority.Lowest, 1, Severity.Information);

 

  try {

    db.ExecuteNonQuery(dbCommandWrapper);

  } catch (Exception ex) {

    Logger.Write(String.Format("Failed to update play count for song: {0}.

                                Error: {1}", songId, ex.ToString()),

                 Category.SqlServer, Priority.Highest, 1, Severity.Error);

  }

} /* UpdateSongPlayCount */


Using stored procedures made it possible to have another layer of abstraction. This made it easy changing an existing stored procedure to keep track of statistics, without having to change any code of the implementation. At the same time, using stored procedures also protected against Sql Injection attacks. After all Accessors were implemented, it was possible to use this implementation by deploying the SqlServer dll and selecting it as data source.

3 Comments

  • I don't understand why anyone would want to write this kind of code when there are several very good (free and non-free) data mapping tools available.



    Apart from that, why go through the trouble of making your data layer provider-agnostic when you do things like hardcode column ordinals? What's more likely - changing your database or changing your schema?



    Seriously, IMHO this kind of code is obsolete.



    Jim

  • Well,,, I think using index column ordinal are faster than "nameIndexing", ALWAYS...



    If you use mapping tools, why to avoid to use ordinals ??? if you change your dataBase, only thing you have to do is to run your template script again in your mapping tool... and.. IT´s DONE!!!!



    A good free tool is the "My Generation".. you can find it in the Shartoolbox too. I like it, because it´s free and you can make your own templates or get in the site, other users templates. There is all kind of templates (MS DAAP, Opf, Hibernate, etc...)



    Patrick

  • I like a lot about the DAAB but have issue with two aspects.

    The mapping from DBType to SqlDBType could do with more options. Mapping DBType.String to SqlType.VarChar doesn't feel right.

    Also, ExecuteDataSet expects a single table result whereas a DAL should be able to support SP's that return multiple tables...

Comments have been disabled for this content.