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();
}
}
}
No comments:
Post a Comment