初涉三层架构，UI层和DAL层的调用，ADO.NET对数据库的数据增删改

最新推荐文章于 2022-11-01 16:37:28 发布

Real_young

最新推荐文章于 2022-11-01 16:37:28 发布

阅读量4.9k

点赞数 1

分类专栏： C#

本文链接：https://blog.csdn.net/Real_young/article/details/45743481

版权

C# 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

UI层：可以说是界面层吧

<Window x:Class="三层架构.CustomerListUI"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="CustomerListUI" Height="600" Width="800" Loaded="Window_Loaded_1">
    <Grid>
        <DockPanel>
            <ToolBar DockPanel.Dock="Top" Height="70">
                <Button x:Name="btnInsert" Click="btnInsert_Click">
                    <Button.Content>
                        <StackPanel>
                            <Image Height="40" Source="Image/insert.ico"></Image>
                            <TextBlock HorizontalAlignment="Center" Text="添加"></TextBlock>
                        </StackPanel>
                    </Button.Content>
                </Button>
                <Button x:Name="btnEdit" Click="btnEdit_Click">
                    <Button.Content>
                        <StackPanel>
                            <Image Height="40" Source="Image/edit.ico"></Image>
                            <TextBlock HorizontalAlignment="Center" Text="编辑"></TextBlock>
                        </StackPanel>
                    </Button.Content>
                </Button>
                <Button x:Name="btnDelete" Click="btnDelete_Click">
                    <Button.Content>
                        <StackPanel>
                            <Image Height="40" Source="Image/delete.ico"></Image>
                            <TextBlock HorizontalAlignment="Center" Text="删除"></TextBlock>
                        </StackPanel>
                    </Button.Content>
                </Button>
                <Button x:Name="btnleading" Click="btnleading_Click">
                    <Button.Content>
                        <StackPanel>
                            <Image Height="40" Source="Image/leading.ico"></Image>
                            <TextBlock HorizontalAlignment="Center" Text="导入"></TextBlock>
                        </StackPanel>
                    </Button.Content>
                </Button>
            </ToolBar>
            <DataGrid AutoGenerateColumns="False" Name="gridCustomers" IsReadOnly="True" DockPanel.Dock="Top">
                <DataGrid.Columns>
                    <DataGridTextColumn Width="80" Header="姓名" Binding="{Binding Name}"></DataGridTextColumn>
                    <DataGridTextColumn Width="150" Header="生日" Binding="{Binding BirthDay}"></DataGridTextColumn>
                    <DataGridTextColumn Width="100" Header="电话" Binding="{Binding TelNum}"></DataGridTextColumn>
                    <DataGridTextColumn Width="80" Header="级别" Binding="{Binding CustLevel}"></DataGridTextColumn>
                    <DataGridTextColumn Width="250" Header="地址" Binding="{Binding Address}"></DataGridTextColumn>
                </DataGrid.Columns>
            </DataGrid>
        </DockPanel>
    </Grid>
</Window>

.cs代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using 三层架构.DAL;
using 三层架构.Model;

namespace 三层架构
{
    /// <summary>
    /// CustomerEditUI.xaml 的交互逻辑
    /// </summary>
    public partial class CustomerEditUI : Window
    {
        //是新增数据还是修改数据
        public bool IsInsert { get; set; }

        //是编辑的话被编辑行的ID
        public long EditingId { get; set; }

        public CustomerEditUI()
        {
            InitializeComponent();
        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            if (IsInsert)
            {
                Customer customer = new Customer();
                customer.Name = txtName.Text;
                customer.TelNum = txtTelNum.Text;
                customer.CustLevel = Convert.ToInt32(txtCustLevel.Text);
                customer.Address = txtAddress.Text;
                customer.BirthDay = dpBirthDay.SelectedDate;

                new CustomerDAL().Insert(customer);//插入数据库
            }
            else
            {
                //先从数据库中查询旧的值，然后把界面中的值设置到旧对象上
                //uodate更新
                CustomerDAL dal = new CustomerDAL();
                Customer customer = dal.GetById(EditingId);
                customer.Address = txtAddress.Text;
                customer.BirthDay = dpBirthDay.SelectedDate;
                customer.Name = txtName.Text;
                customer.CustLevel = Convert.ToInt32(txtCustLevel.Text);
                customer.TelNum = txtTelNum.Text;
                dal.Update(customer);
            }
            DialogResult = true;
        }

        private void btnCancel_Click(object sender, RoutedEventArgs e)
        {
            DialogResult = false;
        }

        private void Window_Loaded_1(object sender, RoutedEventArgs e)
        {
            if (IsInsert)
            {
                txtCustLevel.Text = "1";//默认是一级用户
            }
            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;
            }
        }
    }
}

添加和编辑使用同一个界面

<Window x:Class="三层架构.CustomerEditUI"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="CustomerEditUI" Height="400" Width="500" Loaded="Window_Loaded_1">
    <Grid>
        <TextBlock HorizontalAlignment="Left" Height="36" Margin="41,10,0,0" TextWrapping="Wrap" Text="姓名" VerticalAlignment="Top" Width="89"/>
        <TextBlock HorizontalAlignment="Left" Height="36" Margin="41,60,0,0" TextWrapping="Wrap" Text="电话" VerticalAlignment="Top" Width="89"/>
        <TextBlock HorizontalAlignment="Left" Height="36" Margin="41,110,0,0" TextWrapping="Wrap" Text="级别" VerticalAlignment="Top" Width="89"/>
        <TextBlock HorizontalAlignment="Left" Height="36" Margin="41,160,0,0" TextWrapping="Wrap" Text="生日" VerticalAlignment="Top" Width="89"/>
        <TextBlock HorizontalAlignment="Left" Height="36" Margin="41,210,0,0" TextWrapping="Wrap" Text="地址" VerticalAlignment="Top" Width="89"/>
        <TextBox x:Name="txtName" HorizontalAlignment="Left" Height="36" Margin="196,10,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="192"/>
        <TextBox x:Name="txtTelNum" HorizontalAlignment="Left" Height="36" Margin="196,60,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="192"/>
        <TextBox x:Name="txtCustLevel" HorizontalAlignment="Left" Height="36" Margin="196,110,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="192"/>
        <TextBox x:Name="txtAddress" HorizontalAlignment="Left" Height="94" Margin="196,210,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="192"/>
        <Button x:Name="btnSave" Content="保存" HorizontalAlignment="Left" Height="35" Margin="106,325,0,0" VerticalAlignment="Top" Width="90" Click="btnSave_Click"/>
        <Button x:Name="btnCancel" Content="取消" HorizontalAlignment="Left" Height="35" Margin="275,325,0,0" VerticalAlignment="Top" Width="90" Click="btnCancel_Click"/>
        <DatePicker x:Name="dpBirthDay" HorizontalAlignment="Left" Height="36" Margin="196,160,0,0" VerticalAlignment="Top" Width="192"/>
    </Grid>
</Window>

.cs代码

using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using 三层架构.DAL;
using 三层架构.Model;

namespace 三层架构
{
    /// <summary>
    /// CustomerListUI.xaml 的交互逻辑
    /// </summary>
    public partial class CustomerListUI : Window
    {
        public CustomerListUI()
        {
            InitializeComponent();
        }

        
        private void Window_Loaded_1(object sender, RoutedEventArgs e)
        {
            LoadData();
            
        }

        private void LoadData()
        {
            CustomerDAL dal = new CustomerDAL();
            gridCustomers.ItemsSource = dal.GetAll();
        }

        private void btnInsert_Click(object sender, RoutedEventArgs e)
        {
            CustomerEditUI editUI = new CustomerEditUI();
            editUI.IsInsert = true;
            if (editUI.ShowDialog() == true)
            {
                LoadData();
            }
        }

        private void btnEdit_Click(object sender, RoutedEventArgs e)
        {
            Customer customer = (Customer)gridCustomers.SelectedItem;
            if (customer == null)
            {
                MessageBox.Show("请选择要编辑的行");
                return;
            }
            CustomerEditUI editUI = new CustomerEditUI();
            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();//刷新数据
            }
        }

        private void btnleading_Click(object sender, RoutedEventArgs e)
        {
             OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "文本文件|*.txt|表格文件|*.xls";
            if (ofd.ShowDialog() != true)
            {
                return;
            }
            string filename = ofd.FileName;
            IEnumerable<string> lines = File.ReadLines(filename,Encoding.Default);
            foreach (string line in lines)
            {
                string[] segs = line.Split('\t');
                Customer customer = new Customer();
                customer.Name = segs[0];
                customer.Name = customer.Name.Trim(' ');
                customer.BirthDay = Convert.ToDateTime(segs[1]);
                customer.Address = segs[2];
                customer.Address = customer.Address.Trim(' ');
                customer.TelNum = segs[3];
                customer.TelNum = customer.TelNum.Trim(' ');
                customer.CustLevel = Convert.ToInt32(segs[4]); 
                
             SqlHelper.ExcuteNonQuery(@"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));
            
            }
            MessageBox.Show("导入成功，成功导入" + lines.Count() + "条数据");
            LoadData();
        }


    }
}

导入数据这块参照上一篇博文，不过在这一层用了SQL语句确实不好，待改进！

连接数据库字符串配置文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name ="database" connectionString="data source=.;initial catalog=Mytest;user id=sa;password=46332071"/>
  </connectionStrings>
</configuration>

DAL层

SqlHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 三层架构.DAL
{
    class SqlHelper
    {
        private static string dbStr = ConfigurationManager.ConnectionStrings["database"].ConnectionString;


        public static int  ExcuteNonQuery(string sql, params SqlParameter[] parameter)
        { 
            using (SqlConnection conn= new SqlConnection (dbStr))
            {
                conn.Open();
                using (SqlCommand cmd =conn.CreateCommand())
                {
                    cmd.CommandText=sql;
                    cmd.Parameters.AddRange(parameter);
                    return cmd.ExecuteNonQuery();

                }
            }
        }
        public static object  ExcuteScalar(string sql, params SqlParameter[] parameter)
        {
            using (SqlConnection conn = new SqlConnection(dbStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameter);
                    return cmd.ExecuteScalar();
                }
            }
        }
        public static DataTable ExcuteDataTable(string sql, params SqlParameter[] parameter)
        {
            using (SqlConnection conn = new SqlConnection(dbStr))
            {
                conn.Open();
                using (SqlCommand  cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql; 
                    cmd.Parameters.AddRange(parameter);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }
        public static object FromDBValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }
        public static object ToDBValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }
    }
}

CustomerDAL

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Documents;
using 三层架构.Model;

namespace 三层架构.DAL
{
    class CustomerDAL
    {
        //根据ID获取GetById、Update、DeleteById、GetAll、GetPagedData（分页数据）
        //insert （插入新数据）

        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        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.TelNum = (string)row["TelNum"];
            cust.CustLevel = (int)row["CustLevel"];
            return cust;
        }
        public Customer GetById(long id)
        {
            DataTable dt =SqlHelper.ExcuteDataTable("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];
                //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.TelNum = (string)row["TelNum"];
                //cust.CustLevel = (int)row["CustLevel"];
                //return cust;
                return ToCustomer(row);
            }
        }
        public void DeleteById(long id)
        {
            SqlHelper.ExcuteNonQuery("delete from T_Customer where Id = @Id",
                new SqlParameter("@Id", id));
        }
        public void Insert(Customer customer)
        {
            SqlHelper.ExcuteNonQuery(@"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.ExcuteNonQuery(@"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.ExcuteDataTable("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;
        }
    }
}

然后还有一个MODEL，注意MODEL不能算一层

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 三层架构.Model
{
    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; }
    }
}

心得：

初步设计三层架构还是有些头晕，毕竟调用的有点儿复杂，其实这不算是一个真正的三层，还缺一个BLL层，另外还有一个涉及数据库中NULL的问题，SqlParameter 如果值（第二个参数）为NULL，则表示没有提供参数的值，假设用户想保密自己的生日，没有填写生日，保存过数据库会出错，同理数据库中的NULL调用过来也一个道理，所以需要做转换，即DBNull.values。

关于配置文件：

1、app.config是要放到UI项中

2、DAL层通过ConfigurationManager可以读取到这项目中的app.config的配置信息

引用关系：

DAL项目引用MODEL

UI项目引用DAL和MODEL

Real_young

关注

1
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
初涉三层架构，UI层和DAL层的调用，ADO.NET对数据库的数据增删改

UI层：可以说是界面层吧<Window x:Class="三层架构.CustomerListUI" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Tit
复制链接

扫一扫