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