private void button3_Click(object sender, EventArgs e)
{
try
{
ServiceReference1.pisClient proxy = new ServiceReference1.pisClient();
TTTVService.TranferRecord param = new TTTVService.TranferRecord();
param.Id = int.Parse(textBox12.Text.Trim());
// delete infromation
proxy.DeleteInformation(param.Id);
MessageBox.Show("Xóa dữ liệu thành công!!");
// xoa du lieu textbox
clearData();
// load data again
DataSet myDs = new DataSet();
myDs = dataTTTV();
dataGridView1.DataSource = myDs.Tables[0].DefaultView;
}
catch (FaultException exp)
{
MessageBox.Show(exp.Reason.ToString());
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
ServiceReference1.pisClient proxy = new ServiceReference1.pisClient();
TTTVService.TranferRecord param = new TTTVService.TranferRecord();
param.FullName = textBox1.Text.Trim();
param.PhoneNumber = textBox2.Text.Trim();
param.Email = textBox3.Text.Trim();
param.Birthday = dateTimePicker1.Value;
param.Sex = textBox5.Text.Trim();
param.OriginalAddress = textBox4.Text.Trim();
param.IDNumber = textBox6.Text.Trim();
param.Occupation = textBox7.Text.Trim();
param.CurrentAddress = textBox8.Text.Trim();
param.FromDate = dateTimePicker2.Value;
param.ToDate = dateTimePicker3.Value;
param.Reason = textBox9.Text.Trim();
param.Description = textBox10.Text;
param.Type = textBox11.Text.Trim();
param.Id = int.Parse(textBox12.Text.Trim());
// update infromation
proxy.UpdatInformation(param.Id, param);
MessageBox.Show("Cập nhật dữ liệu thành công!!");
// xoa du lieu textbox
clearData();
// load data again
DataSet myDs = new DataSet();
myDs = dataTTTV();
dataGridView1.DataSource = myDs.Tables[0].DefaultView;
}
catch (FaultException exp)
{
MessageBox.Show(exp.Reason.ToString());
}
}
public TranferRecord[] CountSexByAddress(string Address, string type)
{
try
{
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Data Source=mavi-PC;Initial Catalog=cnweb;Integrated Security=True";
cn.Open();
string query = "SELECT Sex, COUNT(Sex) AS Total";
query += " FROM tamtrutamvang";
query += " WHERE CurrentAddress LIKE '%" + Address + "%' and type = '" + type + "'";
query += " GROUP BY Sex";
SqlCommand sql = new SqlCommand(query, cn);
SqlDataAdapter adt = new SqlDataAdapter(sql);
DataSet ds = new DataSet();
adt.Fill(ds);
int rows = ds.Tables[0].Rows.Count;
data = new TranferRecord[rows];
for (int i = 0; i < rows; i++)
{
data[i] = new TranferRecord();
data[i].Sex = ds.Tables[0].Rows[i].ItemArray[0].ToString();
data[i].Total = int.Parse(ds.Tables[0].Rows[i].ItemArray[1].ToString());
}
cn.Close();
return data;
}
catch (PisNotFoundException exp)
{
TrackedFault tf = new TrackedFault(
Guid.NewGuid(),
exp.Message,
DateTime.Now);
throw new FaultException<TrackedFault>(
tf,
new FaultReason("PisNotFoundException"),
FaultCode.CreateReceiverFaultCode(new FaultCode("GetListOccupationByAddress")));
}
catch (Exception exp)
{
FaultReasonText reason = new FaultReasonText(exp.Message);
throw new FaultException(new FaultReason(reason), FaultCode.CreateReceiverFaultCode(new FaultCode("GetListOccupationByAddress")));
}
}
public void UpdateData(int Id, TranferRecord data)
{
try
{
string connectionString = "Data Source=mavi-PC;Initial Catalog=cnweb;Integrated Security=True";
myConnection = new SqlConnection(connectionString);
// Open connection
myConnection.Open();
string updateCement = "UPDATE tamtrutamvang SET FullName=@FullName,PhoneNumber=@PhoneNumber,Email=@Email,Birthday=@Birthday,Sex=@Sex,OriginalAddress=@OriginalAddress,IDNumber=@IDNumber,Occupation=@Occupation,CurrentAddress=@CurrentAddress,FromDate=@FromDate,ToDate=@ToDate,Reason=@Reason,Description=@Description,Type=@Type WHERE Id = " + Id;
SqlCommand cmd = new SqlCommand(updateCement, myConnection);
cmd.Parameters.AddWithValue("@FullName", data.FullName);
cmd.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
cmd.Parameters.AddWithValue("@Email", data.Email);
cmd.Parameters.AddWithValue("@Birthday", data.Birthday.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@Sex", data.Sex);
cmd.Parameters.AddWithValue("@OriginalAddress", data.OriginalAddress);
cmd.Parameters.AddWithValue("@IDNumber", data.IDNumber);
cmd.Parameters.AddWithValue("@Occupation", data.Occupation);
cmd.Parameters.AddWithValue("@CurrentAddress", data.CurrentAddress);
cmd.Parameters.AddWithValue("@FromDate", data.FromDate.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ToDate", data.ToDate.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@Reason", data.Reason);
cmd.Parameters.AddWithValue("@Description", data.Description);
cmd.Parameters.AddWithValue("@Type", data.Type);
cmd.ExecuteNonQuery();
// close connection
myConnection.Close();
}
catch (PisNotFoundException exp)
{
TrackedFault tf = new TrackedFault(
Guid.NewGuid(),
exp.Message,
DateTime.Now);
throw new FaultException<TrackedFault>(
tf,
new FaultReason("PisNotFoundException"),
FaultCode.CreateReceiverFaultCode(new FaultCode("UpdatInformation")));
}
catch (Exception exp)
{
FaultReasonText reason = new FaultReasonText(exp.Message);
throw new FaultException(new FaultReason(reason), FaultCode.CreateReceiverFaultCode(new FaultCode("UpdatInformation")));
}
}
public TranferRecord[] GetListByDate(string Address, DateTime FromDate, DateTime ToDate, string type)
{
try
{
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Data Source=mavi-PC;Initial Catalog=cnweb;Integrated Security=True";
cn.Open();
string str1 = FromDate.ToString("yyyy-MM-dd");
string str2 = ToDate.ToString("yyyy-MM-dd");
string query = "SELECT * FROM tamtrutamvang WHERE CurrentAddress like '%" + Address + "%' and FromDate >= '" + str1 + "' or ToDate <= '" + str2 + "' and Type = '" + type + "'";
SqlCommand sql = new SqlCommand(query, cn);
SqlDataAdapter adt = new SqlDataAdapter(sql);
DataSet ds = new DataSet();
adt.Fill(ds);
int rows = ds.Tables[0].Rows.Count;
data = new TranferRecord[rows];
for (int i = 0; i < rows; i++)
{
data[i] = new TranferRecord();
data[i].Id = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0].ToString());
data[i].FullName = ds.Tables[0].Rows[i].ItemArray[1].ToString();
data[i].PhoneNumber = ds.Tables[0].Rows[i].ItemArray[2].ToString();
data[i].Email = ds.Tables[0].Rows[i].ItemArray[3].ToString();
data[i].Birthday = Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray[4]);
data[i].Sex = ds.Tables[0].Rows[i].ItemArray[5].ToString();
data[i].OriginalAddress = ds.Tables[0].Rows[i].ItemArray[6].ToString();
data[i].IDNumber = ds.Tables[0].Rows[i].ItemArray[7].ToString();
data[i].Occupation = ds.Tables[0].Rows[i].ItemArray[8].ToString();
data[i].CurrentAddress = ds.Tables[0].Rows[i].ItemArray[9].ToString();
data[i].FromDate = Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray[10]);
data[i].ToDate = Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray[11]);
data[i].Reason = ds.Tables[0].Rows[i].ItemArray[12].ToString();
data[i].Description = ds.Tables[0].Rows[i].ItemArray[13].ToString();
data[i].Type = ds.Tables[0].Rows[i].ItemArray[14].ToString();
}
cn.Close();
return data;
}
catch (PisNotFoundException exp)
{
TrackedFault tf = new TrackedFault(
Guid.NewGuid(),
exp.Message,
DateTime.Now);
throw new FaultException<TrackedFault>(
tf,
new FaultReason("PisNotFoundException"),
FaultCode.CreateReceiverFaultCode(new FaultCode("GetListByDate")));
}
catch (Exception exp)
{
FaultReasonText reason = new FaultReasonText(exp.Message);
throw new FaultException(new FaultReason(reason), FaultCode.CreateReceiverFaultCode(new FaultCode("GetListByDate")));
}
}