三層架構最初目的是不把數據庫操作語句放到界面里，雖然可以寫個公共類操作數據庫，但是SQL語句還是免不了。這樣做小項目沒有什么問題，但是項目做大了就會難以維護。三層架構把數據訪問、業務邏輯、界面分離，方便大項目維護。
為了減少難度，這里舉個簡單的三層架構雛形:只用DAL（Data access Layer）層，把數據訪問封裝到DAL層中，UI調用DAL，原則：UI不能出現SQL。
比如我們有個表：
第0列 | 第一列 | 第二列 | 第三列 |
Id | Name | Age | Hobby |
1 | 泰迪熊 | 18 | 勾搭妹子 |
2 | 王旭 | 30 | 勾搭妹子 |
圖 1 1 數據庫T_Student表
我們添加一個類StudentDAL，SqlHelper是我們上次講過的。
class StudentDAL
{
public static int GetCount()
{
return (int)SqlHelper.ExecuteScalar(“select count(*) from T_Student”);
}
}
然后在UI層上就可以代入
label1.Text= （StudentDAL.GetCount()）.ToString();
但是當列非常多，參數就會非常多，這時候就要把參數封裝到Model中。這里我們建一個類Student。
class Student
{
public long Id{get;set;}
public string Name{set;get;}
public int Age{set;get;}
public string Hobby{set;get;}//可空列注意int？問題
}
然后在class StudentDAL類中添加方法
public static void Insert（Student student）
{
SqlHelper.ExecuteNonQuery（@”Insert into T_Student(Name,Age,Hobby)Values(@Name,@Age,@Hobby)”,new SqlParameter(“@Name”,student.Name), new SqlParameter(“@Age”,student.Age), new SqlParameter(“@Hobby”,student.Hobby)）;
}
UI層就可以直接跟student打交道了。注意：DAL盡量不要返回DataTable和DataRow之類的ADO.NET類。
比如把界面上的數據插入到數據庫，這時候先把界面上的數據寫入一個Student對象，然后把Student對象作為參數調用StudentDAL類。
總結下基本步驟：
(1)寫實體類，實體類對應數據庫中的列。比如：
public class Customer
{
public long Id { get; set; }
public string Name { get; set; }
public DateTime? BirthDay { get; set; }
public string Address { get; set; }
public string TelNum { get; set; }
public int CustLevel { get; set; }
}
（2）寫DAL類，DAL類要實現把數據庫讀取到類中、把類實例化的數據放到數據庫里的多種方法。比如：
public class CustomerDAL
{
//把DataRow中數據放入實例化的類并返回
PRivate Customer ToCustomer(DataRow row)
{
Customer cust = new Customer();
cust.Id = (long)row["Id"];
cust.Name = (string)row["Name"];
cust.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
cust.Address = (string)row["Address"];
cust.CustLevel = (int)row["CustLevel"];
cust.TelNum = (string)row["TelNum"];
return cust;
}
//通過id查找數據庫，返回給實例化的實體類。
public Customer GetById(long id)
{
DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Id=@Id",
new SqlParameter("@Id", id));
if (dt.Rows.Count <= 0)
{
return null;
}
else if (dt.Rows.Count > 1)
{
throw new Exception("嚴重錯誤，查出多條數據！");
}
else
{
DataRow row = dt.Rows[0];
return ToCustomer(row);
}
}
//根據id刪除數據庫中的元素
public void DeleteById(long id)
{
SqlHelper.ExecuteNonQuery("delete from T_Customer where Id=@Id",
new SqlParameter("@Id", id));
}
//把實體類中的數據插入數據庫（新增數據）
public void Insert(Customer customer)
{
SqlHelper.ExecuteNonQuery(@"INSERT INTO [T_Customer]
([Name]
,[BirthDay]
,[Address]
,[TelNum]
,[CustLevel])
VALUES
(@Name, @BirthDay,@Address,@TelNum,@CustLevel)",
new SqlParameter("@Name",customer.Name),
new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay)),
new SqlParameter("@Address", customer.Address),
new SqlParameter("@TelNum", customer.TelNum),
new SqlParameter("@CustLevel", customer.CustLevel));
}
//把實體類中的數據插入數據庫（修改數據）
public void Update(Customer customer)
{
SqlHelper.ExecuteNonQuery(@"UPDATE [T_Customer]
SET [Name] = @Name
,[BirthDay] = @BirthDay
,[Address] = @Address
,[TelNum] = @TelNum
,[CustLevel] = @CustLevel
WHERE Id=@Id",
new SqlParameter("@Name",customer.Name),
new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay)),
new SqlParameter("@Address", customer.Address),
new SqlParameter("@TelNum", customer.TelNum),
new SqlParameter("@CustLevel", customer.CustLevel)
,new SqlParameter("@Id",customer.Id));
}
//獲得全部數據并返回實體類的一個數組
public Customer[] GetAll()
{
DataTable table = SqlHelper.ExecuteDataTable("select * from T_Customer");
Customer[] customers = new Customer[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
customers[i] = ToCustomer(row);
}
return customers;
}
}
接著做個實驗舉例：
首先設計WPF窗體，命名CustomerList，注意最上面三個圖標本質上是button控件，XAML代碼如下:
<Button Name="btnEdit" Click="btnEdit_Click">
<Image Source="Images/edit.ico"></Image>
</Button>
下面是DataGrid數據表格控件，用來顯示數據，同時綁定實體類的屬性，XAML代碼如下：
<DataGrid AutoGenerateColumns="False" Name="gridCustomers" IsReadOnly="True" DockPanel.Dock="Top">
<DataGrid.Columns>
<DataGridTextColumn Header="姓名" Width="100" Binding="{Binding Name}"></DataGridTextColumn>
<DataGridTextColumn Header="生日" Width="100" Binding="{Binding BirthDay}"></DataGridTextColumn>
<DataGridTextColumn Header="電話" Width="100" Binding="{Binding TelNum}"></DataGridTextColumn>
<DataGridTextColumn Header="地址" Width="150" Binding="{Binding Address}"></DataGridTextColumn>
<DataGridTextColumn Header="等級" Width="50" Binding="{Binding CustLevel}"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
編輯CustomerEdit窗體
進行數據綁定，XAML代碼如下:
<TextBox Text="{Binding Name}" Height="23" HorizontalAlignment="Left" Margin="92,9,0,0" Name="txtName" VerticalAlignment="Top" Width="120" TabIndex="1" />
<TextBox Text="{Binding TelNum}" Height="23" HorizontalAlignment="Left" Margin="368,8,0,0" Name="txtTelNum" VerticalAlignment="Top" Width="120" TabIndex="4" />
<DatePicker SelectedDate="{Binding BirthDay}" Height="25" HorizontalAlignment="Left" Margin="92,53,0,0" Name="dpBirthDay" VerticalAlignment="Top" Width="120" TabIndex="2" />
<TextBox Text="{Binding CustLevel}" Height="23" HorizontalAlignment="Left" Margin="370,50,0,0" Name="txtCustLevel" VerticalAlignment="Top" Width="120" TabIndex="5" />
<TextBox Text="{Binding Address}" Height="23" HorizontalAlignment="Left" Margin="92,106,0,0" Name="txtAddress" VerticalAlignment="Top" Width="474" TabIndex="3" />
在CustomerEdit窗體類中添加如下代碼：
//設置表示字段：是新增數據還是修改數據，由CustomerList賦值
public bool IsInsert { get; set; }
//設置標識字段，如果是編輯的話獲得被編輯行的Id，，由CustomerList賦值
public long EditingId { get; set; }
public CustomerEdit()
{
InitializeComponent();
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
//如果是標識字段是新增，則把控件中的數據插入數據庫
if (IsInsert)
{
Customer customer = new Customer();
customer.Address = txtAddress.Text;
customer.BirthDay = dpBirthDay.SelectedDate;
customer.CustLevel = Convert.ToInt32(txtCustLevel.Text);
customer.Name = txtName.Text;
customer.TelNum = txtTelNum.Text;
new CustomerDAL().Insert(customer);//插入數據庫
//有Bug，沒有判斷是否是空
}
else
{
//如果不是新增，是編輯，先從數據庫中查詢舊的值，然后把界面中的值設置到舊對象上，然后Update更新
CustomerDAL dal = new CustomerDAL();
Customer customer = dal.GetById(EditingId);
customer.Address = txtAddress.Text;
customer.BirthDay = dpBirthDay.SelectedDate;
customer.CustLevel = Convert.ToInt32(txtCustLevel.Text);
customer.Name = txtName.Text;
customer.TelNum = txtTelNum.Text;
dal.Update(customer);
}
DialogResult = true;
}
private void btnCancel_Click(object sender, RoutedEventArgs e)
{
DialogResult = false;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
//窗口打開的時候讓第一個輸入控件獲得焦點
txtName.Focus();
if (IsInsert)
{//給控件賦初始值
txtName.Text = ““；
txtTelNum.Text = “”;
txtAddress.Text =“”;
txtCustLevel.Text = “”;
dpBirthDay.SelectedDate =“”;
}
else//修改
{
////把要編輯的數據從數據庫中取出來
////可以把Customer直接在ListUI傳進來，這樣還省得查一次數據庫
Customer customer = new CustomerDAL().GetById(EditingId);
////但是，一個原則：窗口傳值、容器中存儲值盡量放簡單數據類型
////填充到界面上，顯示編輯前的值
txtName.Text = customer.Name;
txtTelNum.Text = customer.TelNum;
txtAddress.Text = customer.Address;
txtCustLevel.Text = customer.CustLevel.ToString();
dpBirthDay.SelectedDate = customer.BirthDay;
}
}
最后是完成CustomerList，代碼如下：
//加載時加載數據
private void Window_Loaded(object sender, RoutedEventArgs e)
{
LoadData();
}
private void LoadData()
{//獲得所有的數據
CustomerDAL dal = new CustomerDAL();
gridCustomers.ItemsSource = dal.GetAll();
}
private void btnAdd_Click(object sender, RoutedEventArgs e)
{
CustomerEdit editUI = new CustomerEdit();
edit.IsInsert = true;//插入，如果
if (edit.ShowDialog() == true)
//如果顯示Edit對話框，刷新所有數據
{
LoadData();
}
}
private void btnEdit_Click(object sender, RoutedEventArgs e)
{//把選擇的對象賦給實體類
Customer customer = (Customer)gridCustomers.SelectedItem;
if(customer==null)
{
MessageBox.Show("請選擇要編輯的行！");
return;
}
CustomerEdit editUI = new CustomerEdit();
editUI.IsInsert = false;
editUI.EditingId = customer.Id;
if (editUI.ShowDialog() == true)
{
LoadData();
}
}
//刪除
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
Customer customer = (Customer)gridCustomers.SelectedItem;
if (customer == null)
{
MessageBox.Show("請選擇要刪除的行！");
return;
}
if (MessageBox.Show("確認刪除此條數據嗎？", "提醒",
MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
new CustomerDAL().DeleteById(customer.Id);
LoadData();//刷新數據
}
}
新聞熱點
疑難解答
