你的位置:首页 > ASP.net教程

[ASP.net教程]三层架构实践入门(1)


 

三层架构最初目的是不把数据库操作语句放到界面里,虽然可以写个公共类操作数据库,但是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();//刷新数据

            }

        }