C#进阶ADO.NET基础五 三层架构之UI层、GUID

一、三层架构之UI层：

1、新建一个列表窗口ListUI，并设置为初始窗口

用XAML配置布局：

<Window x:Class="三层.ListUIWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="ListUIWindow" Height="300" Width="600" WindowStartupLocation="CenterScreen" Loaded="Window_Loaded">
    <Grid>
        <DockPanel>
            <ToolBar DockPanel.Dock="Top" Height="26">
                <Button x:Name="btnAdd" Click="btnAdd_Click">
                    <Image ToolTip="新增" Source="Images/add.ico"></Image>
                </Button>
                <Button x:Name="btnEdit" Click="btnEdit_Click">
                    <Image ToolTip="编辑" Source="Images/edit.ico"></Image>
                </Button>
                <Button x:Name="btnDelete" Click="btnDelete_Click">
                    <Image ToolTip="删除" Source="Images/delete.ico"></Image>
                </Button>
            </ToolBar>
            <DataGrid AutoGenerateColumns="False" Name="gridCustomers" IsReadOnly="True" DockPanel.Dock="Top">
                <DataGrid.Columns>
                    <DataGridTextColumn Header="姓名" Binding="{Binding Name}" Width="80">
                        </DataGridTextColumn>
                    <DataGridTextColumn Header="生日" Binding="{Binding Birthday}" Width="140">
                    </DataGridTextColumn>
                    <DataGridTextColumn Header="地址" Binding="{Binding Address}" Width="190">
                    </DataGridTextColumn>
                    <DataGridTextColumn Header="电话" Binding="{Binding TelNum}" Width="100">
                    </DataGridTextColumn>
                    <DataGridTextColumn Header="等级" Binding="{Binding CustLevel}" Width="80">
                    </DataGridTextColumn>
                </DataGrid.Columns>
            </DataGrid>
        </DockPanel>
    </Grid>
</Window>

2、新建一个编辑窗口，EditUI，并通过XAML配置布局：

<Window x:Class="三层.EditUIWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="EditUIWindow" Height="242.857" Width="450" Loaded="Window_Loaded">
    <Grid Name="grid">
        <Button x:Name="btnSave" Content="保存" HorizontalAlignment="Left" Margin="204,169,0,0" VerticalAlignment="Top" Width="75" Click="btnSave_Click"/>
        <TextBlock HorizontalAlignment="Left" Margin="24,27,0,0" TextWrapping="Wrap" Text="姓名：" VerticalAlignment="Top"/>
        <TextBlock HorizontalAlignment="Left" Margin="24,78,0,0" TextWrapping="Wrap" Text="电话：" VerticalAlignment="Top" RenderTransformOrigin="0.27,0.475"/>
        <TextBlock HorizontalAlignment="Left" Margin="24,127,0,0" TextWrapping="Wrap" Text="地址：" VerticalAlignment="Top" RenderTransformOrigin="-0.386,0.547"/>
        <TextBlock HorizontalAlignment="Left" Margin="243,29,0,0" TextWrapping="Wrap" Text="生日：" VerticalAlignment="Top" RenderTransformOrigin="0.633,1.415"/>
        <TextBlock HorizontalAlignment="Left" Margin="243,83,0,0" TextWrapping="Wrap" Text="等级：" VerticalAlignment="Top"/>
        <TextBox Text="{Binding Name}" x:Name="txtName" HorizontalAlignment="Left" Height="23" Margin="75,26,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="120" RenderTransformOrigin="0.596,0.54"/>
        <TextBox Text="{Binding TelNum}" x:Name="txtTelNum" HorizontalAlignment="Left" Height="23" Margin="75,76,0,0" TextWrapping="Wrap"  VerticalAlignment="Top" Width="120" RenderTransformOrigin="-0.18,-2.25"/>
        <TextBox Text="{Binding CustLevel}" x:Name="txtCustLevel" HorizontalAlignment="Left" Height="23" Margin="291,78,0,0" TextWrapping="Wrap"  VerticalAlignment="Top" Width="120"/>
        <Button x:Name="btnCancel" Content="取消" HorizontalAlignment="Left" Margin="336,169,0,0" VerticalAlignment="Top" Width="75" Click="btnCancel_Click"/>
        <TextBox Text="{Binding Address}" x:Name="txtAddress" HorizontalAlignment="Left" Height="23" Margin="75,127,0,0" TextWrapping="Wrap"  VerticalAlignment="Top" Width="336"/>
        <DatePicker SelectedDate="{Binding Birthday}" x:Name="dpBirthday" HorizontalAlignment="Left" Margin="291,25,0,0" VerticalAlignment="Top" Width="120"/>
    </Grid>
</Window>

3、给主窗口ListUI添加一个加载事件：将数据库中的数据加载到界面表格DataGrid中

private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            CustomerDAL dal = new CustomerDAL();
            gridCustomers.ItemsSource = dal.GetAll();
        }

因数据刷新需要多次调用，将其封装成一个方法：

 private void LoadData()  //刷新DataGrid数据
        {
            CustomerDAL dal = new CustomerDAL();
            gridCustomers.ItemsSource = dal.GetAll();
        }

4、给主窗口的新增按钮添加点击事件：实现功能，跳出编辑窗口，并设置该窗口对象的属性值，如果跳出的窗口对象返回true，刷新数据

private void btnAdd_Click(object sender, RoutedEventArgs e)
        {
            EditUIWindow editUI = new EditUIWindow();
            editUI.isInsert = true;
            if (editUI.ShowDialog() == true)
                LoadData();
        }

5、给主窗口的编辑按钮添加点击事件：实现功能，获取要编辑的行，跳出编辑窗口，并设置窗口对象的属性值，如果跳出的窗口对象返回true，刷新数据

private void btnEdit_Click(object sender, RoutedEventArgs e)
        {
            Customer ct = (Customer)gridCustomers.SelectedItem;
            if (ct == null)
            {
                MessageBox.Show("请选择要编辑的行！");
                return;
            }
            EditUIWindow editUI = new EditUIWindow();
            editUI.isInsert = false;
            editUI.EditingId = ct.Id; //传id值给编辑窗口
            if (editUI.ShowDialog() == true) 
                LoadData();
        }

6、给主窗口的删除按钮添加点击事件：提示选择行，获取选择行的对象，提示是否删除，如果确认，删除选择行数据，并刷新主窗口

private void btnDelete_Click(object sender, RoutedEventArgs e)
        {
            Customer ct = (Customer)gridCustomers.SelectedItem;
            if (ct == null)
            {
                MessageBox.Show("请选择要删除的行！");
                return;
            }

            MessageBoxResult result = MessageBox.Show("确认删除吗？", "提醒", MessageBoxButton.YesNo);
            if (result == MessageBoxResult.Yes)
            {
                //CustomerDAL dal = new CustomerDAL();
                //dal.DeleteById(ct.Id);
                new CustomerDAL().DeleteById(ct.Id);
                LoadData();
            }
        }

7、给编辑窗口添加窗体加载事件：功能--判断是否为插入数据，如是，控件不需要显示数据；如否，从属性id获取该对象，并将窗口的控件绑定指向该对象

private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            if(isInsert == true)
            {
                //插入数据,不需要显示数据，但可以设置一些初始默认值
                txtCustLevel.Text = "2";
            }
            else  //修改数据
            {
                //也可以选择从ListUI中的DataGrid取值，可以省去读取数据库的过程
                //但是这样就需要窗口传类的值，窗口传值效率低，尽量只传简单数据类型
                Customer ct = new CustomerDAL().GetById(EditingId);
                //txtName.Text = ct.Name;
                //txtAddress.Text = ct.Address;
                //txtTelNum.Text = ct.TelNum;
                //txtCustLevel.Text = ct.CustLevel.ToString();
                //dpBirthday.SelectedDate = (DateTime?)SqlHelper.FromDBValue(ct.Birthday);
                //通过绑定取值
                grid.DataContext = ct;
            }
        }

给控件的属性与指向对象的属性，一一绑定

<TextBox <strong><span style="font-size:14px;color:#ff0000;">Text="{Binding Address}"</span></strong> x:Name="txtAddress" HorizontalAlignment="Left" Height="23" Margin="75,127,0,0" TextWrapping="Wrap"  VerticalAlignment="Top" Width="336"/>
        <DatePicker <span style="font-size:14px;"><span style="color:#ff0000;"><strong>SelectedDate="{Binding Birthday}"</strong> </span></span>x:Name="dpBirthday" HorizontalAlignment="Left" Margin="291,25,0,0" VerticalAlignment="Top" Width="120"/>

 

8、给保存按钮添加点击事件：

 private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            if(isInsert) //新增，将窗口中的值插入数据库中
            {
                JudgedData();
                //Customer ct = new Customer();
                //try
                //{
                //    ct.Name = txtName.Text;
                //    ct.CustLevel = Convert.ToInt32(txtCustLevel.Text);
                //    ct.Address = txtAddress.Text;
                //    ct.TelNum = txtTelNum.Text;
                //    ct.Birthday = dpBirthday.SelectedDate;
                //}
                //catch (Exception ex)
                //{
                //    MessageBox.Show(ex.Message);
                //}

                //用数据绑定的方法做===>grid绑定的对象，界面修改数据时，数据会同步到后台对象
                Customer cust = (Customer)grid.DataContext;

                new CustomerDAL().Insert(cust);
            }
            else  //修改，将窗口中的值更新到数据库中的id行
            {
                //由属性“编辑id”获取客户对象，然后编辑对象，获取窗口值，在更新到数据库中
                CustomerDAL dal = new CustomerDAL();
                //Customer ctEdit = dal.GetById(EditingId);
                //try
                //{
                //    ctEdit.Name = txtName.Text;
                //    ctEdit.CustLevel = Convert.ToInt32(txtCustLevel.Text);
                //    ctEdit.Address = txtAddress.Text;
                //    ctEdit.TelNum = txtTelNum.Text;
                //    ctEdit.Birthday = dpBirthday.SelectedDate;
                //}
                //catch (Exception ex)
                //{
                //    MessageBox.Show(ex.Message);
                //}

                //用数据绑定的方法做===>grid绑定的对象，界面修改数据时，数据会同步到后台对象
                Customer ctEdit = (Customer)grid.DataContext;
                dal.Update(ctEdit);
            }
            DialogResult = true;
        }

JudgedData();为封装的一个方法：用来提示控件是否有值

 

9、取消按钮的事件：

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

 

二、GUID

SQLServer中两种常用的主键数据类型：int（或者bigint）+标识列（自动增长字段）；uniqueidentifier(别名Guid、UUID）

1、之前的项目用的都是标识列，设置标识列后，字段会字节自增，避免并发的问题，另外一个表只能有一个标识列

1）优点：占用空间小、无需开发人员干预、易读

2）缺点：效率低、数据导入导出合并维护时，可能会出问题

2、Guid：是一种算法，可以产生唯一标识的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的，这样保证每次生成的Guid永远不会重复，无论

是同一个计算机，还是在不同的计算机上。

获取方法：

SQLServer中： 通过函数获取：newid（）

.NET中： Guid guid =  Guid.NewGuid(); //生成一个Guid. 结构体，值类型，不能为null

优缺点：与标识列互补

 

实际业界倾向于多用Guid