Data Mapper - Sample in C#

Create a simple entity class “Person” with the following properties and fields. I have also defined PersonCollection to keep track of list of persons.

namespace DataMapperPattern.entity
{
public class Person
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}

private string firstName;

public string FirstName
{
get { return firstName; }
set { firstName = value; }
}
private string lastName;

public string LastName
{
get { return lastName; }
set { lastName = value; }
}
private int age;

public int Age
{
get { return age; }
set { age = value; }
}
private string country;

public string Country
{
get { return country; }
set { country = value; }
}

public Person()
{
}

public Person(string fName, string lName, int age, string country)
{
this.firstName = fName;
this.lastName = lName;
this.age = age;
this.country = country;
}

public Person(int nId, string fName, string lName, int age, string country)
{
this.Id = nId;
this.firstName = fName;
this.lastName = lName;
this.age = age;
this.country = country;
}

public static explicit operator Person(DataRow dr)
{
Person p = new Person();
p.Id = System.Convert.ToInt32(dr[0].ToString());
p.FirstName = dr[1].ToString();
p.LastName = dr[2].ToString();
p.Age = System.Convert.ToInt32(dr[3].ToString());
p.Country = dr[4].ToString();
return p;
}

}

public class PersonCollection: List
{
public static explicit operator PersonCollection(DataTable dTable)
{
PersonCollection pCollection = new PersonCollection();
foreach(DataRow dRow in dTable.Rows)
{
Person p = (Person) dRow;
pCollection.Add(p);
}
return pCollection;
}
}
}


Here is a sample of my AbstractMapper. This class defines all genuine functions and waits for a polymorphic implementation of functions that will have a custom behavior. This way of development will have easy access to DB and will chanelize all operation with DB.


namespace DataMapperPattern.data
{
abstract class AbstractMapper
{
protected abstract SqlCommand GetStatement();
protected DataRow FindSingle()
{
DataTable dtResults = new DataTable("Person");
string sConnectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
using (SqlConnection conn = new SqlConnection(sConnectionString))
{
SqlCommand cmd = GetStatement();
cmd.Connection = conn;
SqlDataAdapter dtAdapter = new SqlDataAdapter();
dtAdapter.SelectCommand = cmd;
dtAdapter.Fill(dtResults);
}
if(dtResults != null && dtResults.Rows.Count > 0)
return dtResults.Rows[0];
return null;
}

protected DataTable Find()
{
DataTable dtResults = new DataTable("Person");
string sConnectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
using (SqlConnection conn = new SqlConnection(sConnectionString))
{
SqlCommand cmd = GetStatement();
cmd.Connection = conn;
SqlDataAdapter dtAdapter = new SqlDataAdapter();
dtAdapter.SelectCommand = cmd;
dtAdapter.Fill(dtResults);
}
return dtResults;
}

protected int Insert()
{
int nRetunValue = -1;
string sConnectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
using (SqlConnection conn = new SqlConnection(sConnectionString))
{
SqlCommand cmd = GetStatement();
cmd.Connection = conn;
conn.Open();
nRetunValue = cmd.ExecuteNonQuery();
}
return nRetunValue;
}

protected int Update()
{
return Insert();
}

protected int Delete()
{
return Insert();
}
}

}


Here is a sample of my implemntation of AbstractMapper – PersonMapper

namespace DataMapperPattern.data
{
class PersonMapper : AbstractMapper
{
private static string INSERT_STR = "insert into person(firstname, lastname, age, country) values(@FirstName, @LastName, @Age, @Country)";
private static string DELETE_STR = "delete person where id=@Id";
private static string UPDATE_STR = "update person set firstname=@FirstName, lastname=@LastName, age=@Age, country=@Country where id=@Id";
private static string SELECT_DEF_STR = "select * from person";
private static string SELECT_ID_STR = "select * from person where Id=@Id";
private static string SELECT_FNAME_STR = "select * from person where firstname=@FirstName";
private static string SELECT_LNAME_STR = "select * from person where lastname=@LastName";
private enum operationType { INSERT, DELETE, UPDATE, SELECT_DEF, SELECT_ID, SELECT_FNAME, SELECT_LNAME };
private operationType opType;
private Person person;

public PersonMapper()
{
}

public PersonMapper(Person p)
{
this.person = p;
}

private string GetConnectionString()
{
return null;
}

public int AddPerson()
{
opType = operationType.INSERT;
return Insert();
}

public int UpdatePerson()
{
opType = operationType.UPDATE;
return Update();
}

public int RemovePerson()
{
opType = operationType.DELETE;
return Delete();
}

public Person GetPersonbyId()
{
opType = operationType.SELECT_ID;
return (Person)FindSingle();
}

public PersonCollection GetPersonByFirstName()
{
opType = operationType.SELECT_FNAME;
return (PersonCollection)Find();
}

public PersonCollection GetPersonByLastName()
{
opType = operationType.SELECT_LNAME;
return (PersonCollection)Find();
}

protected override SqlCommand GetStatement()
{
SqlCommand cmd = null;
if (opType == operationType.SELECT_ID)
{
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SELECT_ID_STR;
cmd.Parameters.Add(new SqlParameter("Id", person.Id));
}
else if (opType == operationType.SELECT_FNAME)
{
cmd = new SqlCommand();
cmd.CommandText = SELECT_FNAME_STR;
cmd.Parameters.Add(new SqlParameter("FirstName", person.FirstName));
}
else if (opType == operationType.SELECT_LNAME)
{
cmd = new SqlCommand();
cmd.CommandText = SELECT_LNAME_STR;
cmd.Parameters.Add(new SqlParameter("LastName", person.LastName));
}
else if (opType == operationType.SELECT_DEF)
{
cmd = new SqlCommand();
cmd.CommandText = SELECT_DEF_STR;
}
else if (opType == operationType.DELETE)
{
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = DELETE_STR;
cmd.Parameters.Add(new SqlParameter("Id", person.Id));
}
else if (opType == operationType.INSERT)
{
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = INSERT_STR;
cmd.Parameters.Add(new SqlParameter("FirstName", person.FirstName));
cmd.Parameters.Add(new SqlParameter("LastName", person.LastName));
cmd.Parameters.Add(new SqlParameter("Age", person.Age));
cmd.Parameters.Add(new SqlParameter("Country", person.Country));
}
else if(opType == operationType.UPDATE)
{
cmd = new SqlCommand();
cmd.CommandText = UPDATE_STR;
cmd.Parameters.Add(new SqlParameter("Id", person.Id));
cmd.Parameters.Add(new SqlParameter("FirstName", person.FirstName));
cmd.Parameters.Add(new SqlParameter("LastName", person.LastName));
cmd.Parameters.Add(new SqlParameter("Age", person.Age));
cmd.Parameters.Add(new SqlParameter("Country", person.Country));
}
return cmd;
}
}
}

If I need any more implemntation all that I’ll do is extend from my AbstractMapper class and do the Db operation