Plip's Weblog

Part 3: Using Strongly Typed Objects and Collections to replace DataSet’s in your .NET applications.

Populating objects and collection from Databases

The function of populating your objects is relatively simple, all you basically need to do is retrieve the Data from it’s store and pass it in to your objects.

The best way of achieving this is by having all of your Data Access code in one place, then using that code throughout your applications. The Data access code can be project specific or more general and this will depend on your needs.

An example of this follows: -

public FooCollection PoshFoos()

{

          //Setup some Variables

          string ConnectionString = ("MySqlServer");

          string StoredProcedure = ("MyStoredProc");

          FooCollection CC = new FooCollection();

          //Set up the Parameters for the Database Code

          NameValueCollection Params = new NameValueCollection();

          Params.Add("@FoosExistInOtherTable","FooDatabase.dbo.PoshFoos");

          //Retreive the Data using our custom call (DAL)

          DataSet Foos = DataAccess.Out.GetDataSet(ConnectionString,StoredProcedure,Params);

          //Loop through the rows collected and populate the Foo Object

          foreach(DataRow DR in Foos.Tables[0].Rows)

          {

                   Foo C = new Foo();

                   C.Id = Convert.ToInt32(DR["FooId"].ToString());

                   C.Title = DR["Title"].ToString();

                   C.ExteriorColor = DR["ExteriorColour"].ToString();

                   C.InteriorColor = DR["InteriorColour"].ToString();

                   C.InteriorMaterial = DR["InteriorMaterial"].ToString();

                   C.Price = Convert.ToDouble(DR["Price"].ToString());

                   C.Miles = Convert.ToInt32(DR["Miles"].ToString());

                   C.Transmission = new Transmission(DR["TransmissionCode"].ToString(),DR["TransmissionName"].ToString());

                   C.Fuel = new Fuel(DR["FuelCode"].ToString(),DR["FuelName"].ToString());

                   C.Manufacturer = new Manufacturer(Convert.ToInt32(DR["ManufacturerId"].ToString()),DR["ManufacturerName"].ToString());

                   C.Model = new Model(Convert.ToInt32(DR["ModelId"].ToString()),DR["ModelName"].ToString());

                   C.Derivative = new Derivative(Convert.ToInt32(DR["DerivativeId"].ToString()),DR["DerivativeName"].ToString());

                   C.Description = DR["Description"].ToString();

                   C.HoldingCenter = new Center(Convert.ToInt32(DR["CenterId"].ToString()),DR["CenterName"].ToString());

                   CC.Add(C);

          }

          //Return the Foo object

          return CC;

}

Sometimes you will even want to encapsulate the above and add application logic such as Caching, and example of which follows: -

public BusinessObjects.FooCollection All()

{

          FooCollection CC = new FooCollection();

          if (System.Web.HttpContext.Current.Cache["PoshFoos_" + _Results.ToString()] != null)

          {

                   CC = ((FooCollection)System.Web.HttpContext.Current.Cache["PoshFoos_" + _Results.ToString()]);

          }

          else

          {

                   ReturnFoos RC = new ReturnFoos();

                   RC.NumberOfRowsToReturn = _Results;

                   CC = RC.PoshFoos();

System.Web.HttpContext.Current.Cache.Add("PoshFoos_" + _Results.ToString(),

CC,

null,

DateTime.Now.AddMinutes(Convert.ToInt32(60),

System.TimeSpan.Zero,

System.Web.Caching.CacheItemPriority.Normal,null);

          }

          return CC;

}

Another way, sometimes the best for persuading others to adopt this technique is by having methods on your objects that perform the data access, an example of which follows.

Foo.Select(123456);

Foo.Update();

Foo.Delete();

Whilst this moves away from a disconnected architecture it allows developers to get away without having to perform any manual Data Access.

It’s best to place all Data Access code that is used inside your objects in a separate Data Access class of some sort, this way you do not have to clutter your objects with a lot of methods to retrieve data, but instead instantiate your Data Access class, give it the Object you want Filling, and pass it the Criteria against which you wish the SQL Queries to be run. This is Similar to the way DataAdapter’s are currently used. 

There is no “right” was to populate your objects, all have their pitfalls.