Row data gateway

Rowdata Gateway is more or less the way most of the VC++ code in mid (and late) 1990’s. If you remember Recordsets, this is more or less the way things were done.

Forms/web application will communicate with entity which is a row-wise representation of the table.



Row entity will serve as an adapter for front-end and back-end system. If there are some masked rows and you do some conversion for the masked rows from this, it becomes a Active Record design pattern

Let’s go with the same example of Person

Person.cs

namespace TableRowGateway.entity

{

public class Person

{

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 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;

}

private string GetConnectionString()

{

string sConnectionString = string.Empty;

sConnectionString = global::TableRowGateway.Settings.Default.TestDBConnectionString;

return sConnectionString;

}

public int AddPerson()

{

int nResult = 0;

using(SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = INSERT_STR;

sqlCmd.Parameters.Add(new SqlParameter("FirstName",FirstName));

sqlCmd.Parameters.Add(new SqlParameter("LastName",LastName));

sqlCmd.Parameters.Add(new SqlParameter("Age",Age));

sqlCmd.Parameters.Add(new SqlParameter("Country",Country));

conn.Open();

nResult = sqlCmd.ExecuteNonQuery();

}

return nResult;

}

public List<Person> GetPeople()

{

List<Person> lstPerson = null;

using(SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = SELECT_DEF_STR;

conn.Open();

SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

if(reader.HasRows)

lstPerson = new List<Person>();

while(reader.Read())

{

Person pers = new Person(reader.GetInt32(0),

reader.GetString(1), reader.GetString(2),

reader.GetInt32(3), reader.GetString(4));

lstPerson.Add(pers);

}

}

return lstPerson;

}

public int RemovePerson()

{

int nResult = 0;

using(SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = DELETE_STR;

sqlCmd.Parameters.Add(new SqlParameter("Id",Id));

conn.Open();

nResult = sqlCmd.ExecuteNonQuery();

conn.Close();

}

return 0;

}

public int UpdatePerson()

{

int nResult = 0;

using (SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = UPDATE_STR;

sqlCmd.Parameters.Add(new SqlParameter("Id", Id));

sqlCmd.Parameters.Add(new SqlParameter("FirstName", FirstName));

sqlCmd.Parameters.Add(new SqlParameter("LastName", LastName));

sqlCmd.Parameters.Add(new SqlParameter("Age", Age));

sqlCmd.Parameters.Add(new SqlParameter("Country", Country));

conn.Open();

nResult = sqlCmd.ExecuteNonQuery();

}

return nResult;

}

public List<Person> GetPersonByFirstName()

{

List<Person> lstPerson = null;

using (SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = SELECT_FNAME_STR;

sqlCmd.Parameters.Add(new SqlParameter("FirstName", FirstName));

conn.Open();

SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

if (reader.HasRows)

lstPerson = new List<Person>();

while (reader.Read())

{

Person pers = new Person(System.Convert.ToInt32(reader.GetDecimal(0)),

reader.GetString(1), reader.GetString(2),

System.Convert.ToInt32(reader.GetDecimal(3)), reader.GetString(4));

lstPerson.Add(pers);

}

}

return lstPerson;

}

public List<Person> GetPersonByLastName()

{

List<Person> lstPerson = null;

using (SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = SELECT_LNAME_STR;

sqlCmd.Parameters.Add(new SqlParameter("LastName", LastName));

conn.Open();

SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

if (reader.HasRows)

lstPerson = new List<Person>();

while (reader.Read())

{

Person pers = new Person(reader.GetInt32(0),

reader.GetString(1), reader.GetString(2),

reader.GetInt32(3), reader.GetString(4));

lstPerson.Add(pers);

}

}

return lstPerson;

}

public Person GetPersonbyId()

{

Person pers = null;

using (SqlConnection conn = new SqlConnection(GetConnectionString()))

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = conn;

sqlCmd.CommandText = SELECT_ID_STR;

sqlCmd.Parameters.Add(new SqlParameter("Id", Id));

conn.Open();

SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

while (reader.Read())

{

pers = new Person();

pers.Id = reader.GetInt32(0);

pers.firstName = reader.GetString(1);

pers.lastName = reader.GetString(2);

pers.Age = reader.GetInt32(3);

pers.Country = reader.GetString(4);

}

}

return pers;

}

}

}

Form1.cs

Code that communicates with the entiry for information is appended

namespace TableRowGateway

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void btnNew_Click(object sender, EventArgs e)

{

if(Controls.Count > 0)

{

System.Collections.IEnumerator ctrlEnumerator = this.Controls.GetEnumerator();

while (ctrlEnumerator.MoveNext())

{

Control ctrl = (Control)ctrlEnumerator.Current;

if (ctrl is System.Windows.Forms.TextBox)

{

ctrl.Text = "";

}

}

}

}

private void btnSave_Click(object sender, EventArgs e)

{

Person pers = new Person(txtFirstName.Text,

txtLastName.Text, System.Convert.ToInt32(txtAge.Text),

txtCountry.Text);

pers.AddPerson();

}

private void btnSearch_Click(object sender, EventArgs e)

{

Person pers = new Person();

Person resultPers = null;

List<Person> lstPersons = null;

if (txtFirstName.Text.Length > 0)

{

pers.FirstName = txtFirstName.Text;

lstPersons = pers.GetPersonByFirstName();

}

else if (txtLastName.Text.Length > 0)

{

pers.LastName = txtLastName.Text;

lstPersons = pers.GetPersonByLastName();

}

else if(txtId.Text.Length > 0)

{

pers.Id = System.Convert.ToInt32(txtId.Text);

resultPers = pers.GetPersonbyId();

}

if (lstPersons != null && lstPersons.Count >= 0)

{

resultPers = lstPersons[0];

}

//publish results

if (resultPers != null)

{

txtId.Text = System.Convert.ToInt32(resultPers.Id).ToString();

txtFirstName.Text = resultPers.FirstName;

txtLastName.Text = resultPers.LastName;

txtAge.Text = System.Convert.ToInt32(resultPers.Age).ToString();

txtCountry.Text = resultPers.Country;

}

}

private void btnDelete_Click(object sender, EventArgs e)

{

Person pers = new Person();

pers.Id = System.Convert.ToInt32(txtId.Text);

pers.RemovePerson();

}

private void btnUpdate_Click(object sender, EventArgs e)

{

Person pers = new Person(System.Convert.ToInt32(txtId.Text), txtFirstName.Text,

txtLastName.Text, System.Convert.ToInt32(txtAge.Text), txtCountry.Text);

pers.UpdatePerson();

}

}

}

TableData Gateway

This is one of the most common pattern used in today’s world, including Entity Framework.

This is very easy to use. You entity class will provide API’s to maintain data in Dataset/Data Table, constantly updating DB. All front end and business systems will communicate with the entity class for information. Entities will in turn query data table for information.

Here every table in the DB is represented completely in as a DataTable/Dataset object in front-end.

Let’s consider the same example we used for DataMapper. A “Person” entity communicates to DB through PersonGateway.



Person.cs

namespace TableDataGateway.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;

}

}

}

PersonGateway.cs

namespace TableDataGateway.data

{

public class PersonGateway

{

private DataTable data = null;

private Person person;

public PersonGateway()

{

}

public PersonGateway(Person pers)

{

this.person = pers;

}

public bool AddNewPerson(Person pers)

{

person = pers;

return AddNewPerson();

}

public List<Person> GetPeople()

{

List<Person> lstPerson = null;

if (data != null)

data = null;

data = CreateNewPersonTable();

using (SqlDataAdapter adap = GetdataAdapter())

{

adap.Fill(data);

}

if (data.Rows.Count > 0)

{

lstPerson = new List<Person>();

foreach (DataRow dr in data.Rows)

{

Person per = new Person();

per.Id = System.Convert.ToInt32(dr["Id"].ToString());

per.FirstName = dr["FirstName"].ToString();

per.LastName = dr["LastName"].ToString();

per.Age = System.Convert.ToInt32(dr["Age"].ToString());

per.Country = dr["country"].ToString();

lstPerson.Add(per);

}

}

return lstPerson;

}

public Person FindById(int nId)

{

Person pers = null;

if (data != null)

data = null;

data = CreateNewPersonTable();

using (SqlDataAdapter adap = GetdataAdapter())

{

adap.Fill(data);

}

data.DefaultView.Sort = "Id";

int nPos = data.DefaultView.Find(nId);

if (nPos >= 0)

{

pers = new Person();

pers.FirstName = data.Rows[nPos]["FirstName"].ToString();

pers.LastName = data.Rows[nPos]["LastName"].ToString();

pers.Age = System.Convert.ToInt32(data.Rows[nPos]["Age"].ToString());

pers.Country = data.Rows[nPos]["Country"].ToString();

pers.Id = System.Convert.ToInt32(data.Rows[nPos]["Id"].ToString());

}

return pers;

}

public Person FindByFirstName(string fName)

{

Person pers = null;

if (data != null)

data = null;

data = CreateNewPersonTable();

using (SqlDataAdapter adap = GetdataAdapter())

{

adap.SelectCommand.CommandText = "select * from person order by FirstName";

adap.Fill(data);

}

data.DefaultView.Sort = "FirstName";

Trace.WriteLine(data.DefaultView.FindRows(fName)[0][0].ToString());

int nPos = data.DefaultView.Find(fName);

if (nPos >= 0)

{

pers = new Person();

pers.FirstName = data.Rows[nPos]["FirstName"].ToString();

pers.LastName = data.Rows[nPos]["LastName"].ToString();

pers.Age = System.Convert.ToInt32(data.Rows[nPos]["Age"].ToString());

pers.Country = data.Rows[nPos]["Country"].ToString();

pers.Id = System.Convert.ToInt32(data.Rows[nPos]["Id"].ToString());

}

return pers;

}

public Person FindByLastName(string lName)

{

Person pers = null;

if (data != null)

data = null;

data = CreateNewPersonTable();

using (SqlDataAdapter adap = GetdataAdapter())

{

adap.SelectCommand.CommandText = "select * from person order by LastName";

adap.Fill(data);

}

data.DefaultView.Sort = "LastName";

int nPos = data.DefaultView.Find(lName);

if (nPos >= 0)

{

pers = new Person();

pers.FirstName = data.Rows[nPos]["FirstName"].ToString();

pers.LastName = data.Rows[nPos]["LastName"].ToString();

pers.Age = System.Convert.ToInt32(data.Rows[nPos]["Age"].ToString());

pers.Country = data.Rows[nPos]["Country"].ToString();

pers.Id = System.Convert.ToInt32(data.Rows[nPos]["Id"].ToString());

}

return pers;

}

public bool AddNewPerson()

{

DataRow drNewRow = data.NewRow();

drNewRow["FirstName"] = person.FirstName;

drNewRow["LastName"] = person.LastName;

drNewRow["Age"] = person.Age;

drNewRow["Country"] = person.Country;

data.Rows.Add(drNewRow);

using (SqlDataAdapter adap = GetdataAdapter())

{

adap.Update(data);

}

return true;

}

public int RemovePerson(int nId)

{

if (data != null)

data = null;

if (person != null)

person = null;

person = FindById(nId);

data.DefaultView.Sort = "Id";

int nPos = data.DefaultView.Find(nId);

if (nPos >= 0)

{

Trace.WriteLine("Count of Records in Table : " + data.Rows.Count);

using (SqlDataAdapter adap = GetdataAdapter())

{

data.Rows[nPos].Delete();

Trace.WriteLine("Count of Records in Table : " + data.Rows.Count);

int nDeleteCount = adap.Update(data);

Trace.WriteLine(nDeleteCount + " count of people have been removed");

}

}

return 0;

}

public int UpdatePerson(int nId, string fName, string lName,

int nAge, string sCountry)

{

if (data != null)

data = null;

if (person != null)

person = null;

person = FindById(nId);

data.DefaultView.Sort = "Id";

int nPos = data.DefaultView.Find(nId);

if (nPos >= 0)

{

this.person = new Person(fName, lName, nAge, sCountry);

data.Rows[nPos]["FirstName"] = fName;

data.Rows[nPos]["LastName"] = lName;

data.Rows[nPos]["Age"] = nAge;

data.Rows[nPos]["Country"] = sCountry;

using (SqlDataAdapter adap = GetdataAdapter())

{

adap.Update(data);

}

}

return 0;

}

private SqlDataAdapter GetdataAdapter()

{

string sConnectString = global::TableDataGateway.Settings1.Default.TestDBConnectionString;

SqlDataAdapter dataAdapter;

SqlCommandBuilder cmdBuilder;

dataAdapter = new SqlDataAdapter("SELECT * FROM PERSON",

sConnectString);

cmdBuilder = new SqlCommandBuilder(dataAdapter);

return dataAdapter;

}

private DataTable CreateNewPersonTable()

{

return new DataTable("Person");

}

}

}

Normally all connectionstring information are stored in app.config/web.config. Do have a variant, I stores information in .settings file.

We can retreive infomration stored in .settings file through the following mechanism. This has been illustrated in the above code too
string sConnectString = global::TableDataGateway.Settings1.Default.TestDBConnectionString;

where TableDataGateway is my default namespace for the project.

My windows form code is appended

namespace TableDataGateway

{

public partial class Form1 : Form

{

private PersonGateway pg;

public Form1()

{

InitializeComponent();

pg = new PersonGateway();

pg.GetPeople();

}

private void Form1_Load(object sender, EventArgs e)

{

}

private void btnNew_Click(object sender, EventArgs e)

{

txtFirstName.Text = "";

txtLastName.Text = "";

txtAge.Text = "0";

txtCountry.Text = "";

}

private void btnSave_Click(object sender, EventArgs e)

{

if(pg.AddNewPerson(new Person(txtFirstName.Text, txtLastName.Text,

System.Convert.ToInt32(txtAge.Text), txtCountry.Text)) == false)

{

PublishException("Failed to Save Person", "btnSave_Click");

}

}

private void btnSearch_Click(object sender, EventArgs e)

{

Person pers = null;

if (txtFirstName.Text.Length > 0)

pers = pg.FindByFirstName(txtFirstName.Text);

else if (txtLastName.Text.Length > 0)

pers = pg.FindByLastName(txtLastName.Text);

if (pers == null)

return;

txtId.Text = pers.Id.ToString();

txtFirstName.Text = pers.FirstName;

txtLastName.Text = pers.LastName;

txtCountry.Text = pers.Country;

txtAge.Text = pers.Age.ToString();

}

private void btnDelete_Click(object sender, EventArgs e)

{

pg.RemovePerson(System.Convert.ToInt32(txtId.Text));

}

private void btnUpdate_Click(object sender, EventArgs e)

{

pg.UpdatePerson(System.Convert.ToInt32(txtId.Text),

txtFirstName.Text, txtLastName.Text,

System.Convert.ToInt32(txtAge.Text), txtCountry.Text);

}

private void PublishException(String sMessage, string sModule)

{

ApplicationException ex = new ApplicationException(sMessage

+ " : " + sModule);

throw ex;

}

}

}