public P0010ListViewModel BusinessTestView()
{
P0010ListViewModel vm1 = new P0010ListViewModel();
List <T0010> listT1 = new T0010().ReadList(); // 來自資料庫的清單
List <P0010ViewModel> listBrowse1 = new List <P0010ViewModel>(); // 顯示在View上的清單
foreach (T0010 t1 in listT1)
{
P0010ViewModel row1 = new P0010ViewModel();
row1.ms1 = t1.ms1; // 學號
row1.ms2 = t1.ms2; // 姓名
row1.mi1 = t1.mi1; // 國文分數
row1.mi2 = t1.mi2; // 英文分數
row1.miSum = row1.mi1 + row1.mi2; // 計算總分
row1.mi1Extra = row1.miSum / 2; // 計算平均分數
if ((row1.mi1Extra) < 60)
{
row1.msColor = "red"; // 平均低於60分的話, 以紅色顯示
}
else
{
row1.msColor = "green";
}
listBrowse1.Add(row1);
}
vm1.msName = "3年2班";
vm1.mList = listBrowse1;
return(vm1);
}
public P0030ViewModel Read(string id)
{
// 單筆顯示
T0010 t1 = new T0010().Read1Record(id);
P0030ViewModel vm1 = ConvertModelToViewModel(t1);
return(vm1);
}
public P0030ListViewModel Index()
{
// 多筆清單顯示
P0030ListViewModel vm1 = new P0030ListViewModel();
List <T0010> listT1 = new T0010().ReadList(); // 來自資料庫的清單
List <P0030ViewModel> listBrowse1 = new List <P0030ViewModel>(); // 顯示在View上的清單
foreach (T0010 t1 in listT1)
{
P0030ViewModel row1 = ConvertModelToViewModel(t1);
listBrowse1.Add(row1);
}
vm1.msName = "3年2班";
vm1.mList = listBrowse1;
return(vm1);
}
public bool Update(T0010 t1)
{
// 修改
ClearBeforeAction();
if ((t1.mi1 < 30) && (t1.mi2 < 30))
{
msError = "國文跟英文不可能都低於30分";
return(false);
}
miAffected = new T0010().Update(t1);
if (miAffected < 1)
{
return(false);
}
return(true);
}
public int Create(T0010 t1)
{
int iAffected = 0;
using (SqlConnection cnn1 = new SqlConnection(GetConnectionsString()))
{
cnn1.Open();
using (SqlCommand cmd1 = new SqlCommand("insert into T0010 ([FS01], [FS02], [FI01], [FI02]) values (@FS01, @FS02, @FI01,@FI02)", cnn1))
{
cmd1.Parameters.Add("@FS01", SqlDbType.VarChar, 50).Value = t1.ms1;
cmd1.Parameters.Add("@FS02", SqlDbType.NVarChar, 50).Value = t1.ms2;
cmd1.Parameters.Add("@FI01", SqlDbType.Int).Value = t1.mi1;
cmd1.Parameters.Add("@FI02", SqlDbType.Int).Value = t1.mi2;
iAffected = cmd1.ExecuteNonQuery();
}
}
return(iAffected);
}
public int Update(T0010 t1)
{
int iAffected = 0;
using (SqlConnection cnn1 = new SqlConnection(GetConnectionsString()))
{
cnn1.Open();
using (SqlCommand cmd1 = new SqlCommand("update T0010 set [FS02]=@FS02, [FI01]=@FI01, [FI02]=@FI02 where [FS01]=@FS01", cnn1))
{
cmd1.Parameters.Add("@FS01", SqlDbType.VarChar, 50).Value = t1.ms1;
cmd1.Parameters.Add("@FS02", SqlDbType.NVarChar, 50).Value = t1.ms2;
cmd1.Parameters.Add("@FI01", SqlDbType.Int).Value = t1.mi1;
cmd1.Parameters.Add("@FI02", SqlDbType.Int).Value = t1.mi2;
iAffected = cmd1.ExecuteNonQuery();
}
}
return(iAffected);
}
public T0010 Read1Record(string sFS01)
{
T0010 t1 = new T0010();
using (SqlConnection cnn1 = new SqlConnection(GetConnectionsString())) {
cnn1.Open();
using (SqlCommand cmd1 = new SqlCommand("select FS01, FS02, FI01, FI02 from T0010 where FS01=@FS01", cnn1)) {
cmd1.Parameters.Add("@FS01", SqlDbType.VarChar, 50).Value = sFS01;
SqlDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
t1.ms1 = reader1.GetString(0);
t1.ms2 = reader1.GetString(1);
t1.mi1 = reader1.GetInt32(2);
t1.mi2 = reader1.GetInt32(3);
}
}
}
return(t1);
}
public P0030ViewModel ConvertModelToViewModel(T0010 t1)
{
// 從資料model轉為ViewModel都是同樣的邏輯, 可以轉為公用函數.
P0030ViewModel vm1 = new P0030ViewModel();
vm1.ms1 = t1.ms1; // 學號
vm1.ms2 = t1.ms2; // 姓名
vm1.mi1 = t1.mi1; // 國文分數
vm1.mi2 = t1.mi2; // 英文分數
vm1.miSum = vm1.mi1 + vm1.mi2; // 計算總分
vm1.mi1Extra = vm1.miSum / 2; // 計算平均分數
if ((vm1.mi1Extra) < 60)
{
vm1.msColor = "red"; // 平均低於60分的話, 以紅色顯示
}
else
{
vm1.msColor = "green";
}
return(vm1);
}
public P0010ViewModel BusinessTestView2(string id)
{
P0010ViewModel vm1 = new P0010ViewModel();
T0010 t1 = new T0010().Read1Record(id);
vm1.ms1 = t1.ms1; // 學號
vm1.ms2 = t1.ms2; // 姓名
vm1.mi1 = t1.mi1; // 國文分數, 同vm1.mi1 = t1.mi1 == null ? default(int) : t1.mi1;
vm1.mi2 = t1.mi2; // 英文分數
vm1.miSum = vm1.mi1 + vm1.mi2; // 計算總分
vm1.mi1Extra = vm1.miSum / 2; // 計算平均分數
if ((vm1.mi1Extra) < 60)
{
vm1.msColor = "red"; // 平均低於60分的話, 以紅色顯示
}
else
{
vm1.msColor = "green";
}
return(vm1);
}
public List <T0010> ReadList()
{
List <T0010> oList = new List <T0010>();
using (SqlConnection cnn1 = new SqlConnection(GetConnectionsString()))
{
cnn1.Open();
using (SqlCommand cmd1 = new SqlCommand("select FS01, FS02, FI01, FI02 from T0010 order by FS01", cnn1))
{
SqlDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
T0010 t1 = new T0010();
t1.ms1 = reader1.GetString(0);
t1.ms2 = reader1.GetString(1);
t1.mi1 = reader1.GetInt32(2);
t1.mi2 = reader1.GetInt32(3);
oList.Add(t1);
}
}
}
return(oList);
}
