| |
This example contains two demos to demonstrate the difference between ADO.NET and BLToolkit.
The AdoDemo represents a typical data access method which takes one parameter and
returns a list of objects. All routine mapping work is done manually inside the method.
AdoDemo.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using NUnit.Framework;
namespace HowTo.Data
{
[TestFixture]
public class AdoDemo
{
// Typified definition of the Gender database field.
//
public enum Gender
{
Female,
Male,
Unknown,
Other
}
// Business object.
//
public class Person
{
public int ID { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public Gender Gender { get; set; }
}
// ADO.NET data access method.
//
public List<Person> GetList(Gender gender)
{
// Map the typified parameter value to its database representation.
//
string paramValue = "";
switch (gender)
{
case Gender.Female: paramValue = "F"; break;
case Gender.Male: paramValue = "M"; break;
case Gender.Unknown: paramValue = "U"; break;
case Gender.Other: paramValue = "O"; break;
}
// Read a database configuration string.
//
string cs = ConfigurationManager.ConnectionStrings["DemoConnection"].ConnectionString;
// Create and open a database connection.
//
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
// Create and initialize a Command object.
//
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Person WHERE Gender = @gender";
cmd.Parameters.AddWithValue("@gender", paramValue);
// Execute query.
//
using (SqlDataReader rd = cmd.ExecuteReader())
{
List<Person> list = new List<Person>();
while (rd.Read())
{
Person person = new Person();
// Map a data reader row to a business object.
//
person.ID = Convert.ToInt32 (rd["PersonID"]);
person.FirstName = Convert.ToString(rd["FirstName"]);
person.MiddleName = Convert.ToString(rd["MiddleName"]);
person.LastName = Convert.ToString(rd["LastName"]);
switch (rd["Gender"].ToString())
{
case "F": person.Gender = Gender.Female; break;
case "M": person.Gender = Gender.Male; break;
case "U": person.Gender = Gender.Unknown; break;
case "O": person.Gender = Gender.Other; break;
}
list.Add(person);
}
return list;
}
}
}
}
[Test]
public void Test()
{
List<Person> list = GetList(Gender.Male);
Assert.Greater(list.Count, 0);
}
}
} |
The DbManagerDemo does the same work performed by BLToolkit.DbManager.
DbManagerDemo.cs
using System;
using System.Collections.Generic;
using NUnit.Framework;
using BLToolkit.Data;
using BLToolkit.Mapping;
namespace HowTo.Data
{
[TestFixture]
public class DbManagerDemo
{
// The MapValue attribute is used by BLToolkit.
//
public enum Gender
{
[MapValue("F")] Female,
[MapValue("M")] Male,
[MapValue("U")] Unknown,
[MapValue("O")] Other
}
// Business object. Here we use C# 3.0 automatic properties,
// however it can be public fields, regular or abstract properties.
// The MapField attribute is used by BLToolkit to associate a database field
// with a business object property if they have different names.
//
public class Person
{
[MapField("PersonID")]
public int ID { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public Gender Gender { get; set; }
}
// BLToolkit data access method.
//
public List<Person> GetList(Gender gender)
{
using (DbManager db = new DbManager("DemoConnection"))
{
return db
.SetCommand(
"SELECT * FROM Person WHERE Gender = @gender",
db.Parameter("@gender", Map.EnumToValue(gender)))
.ExecuteList<Person>();
}
}
[Test]
public void Test()
{
List<Person> list = GetList(Gender.Male);
Assert.Greater(list.Count, 0);
}
}
} |
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add
name = "DemoConnection"
connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
providerName = "System.Data.SqlClient" />
</connectionStrings>
</configuration> |
Create.sql script
| |