学生成绩管理系统_三层架构版（超详细）

 

    本人今年刚刚毕业，进一家公司开始实习，第一天就收获匪浅，果然是上班一天，自学一月。第一次接触vb，然后被要求做一个学生成绩管理系统，然后就做出了一个屎一般的系统，，，，大学白学好吧。前辈是个好人啊。手把手教了我怎么用三层架构做一个程序，话不多说，直接上干货了。

  三层架构，，说一说我的理解吧（萌新班门弄斧，要是说的不对，接受任何批评）。所有程序，都是给用户使用的吧，所以就有第一层-----UI层，说白了，我的理解就是什么按钮触发了什么事件，什么DataGridView显示了什么表格数据的。ok，双击按钮进入代码，然后把各个事件的接口写进去，成了！所以三层的第二层就是写各个事件的具体代码的地方，也就是BLL层。每个程序都需要连接数据库，各个事件比如查询事件，增加修改删除等事件都是对数据路进行操作的，所以第三层DLL层就是连接数据库的底层。其实这些都好理解，唯一一个我不理解的就是MODEL层，咱也不敢说，咱也不敢问的，吊用没有。三层结构图如图所示。

由于本人是萌新，一开始学习的时候超级希望有一个教程，能详细到头发丝一样那么细。所以，我写的博客希望能帮到和我有一个想法的萌新，所以大佬别训我。

首先就是新建一个windows窗体，命名“为学生成绩管理系统三层架构版”。

创建完成后，在资源管理器哪里，找到解决方案“学生管理系统三层架构版”（1个项目），然后右键--添加--新建项目--windows窗体应用。新建三个，加上最开始的，一共四个，然后重命名，分别叫，UI,BLL,DLL,MODEL,如第一张图。然后除了UI层的form外，其他三层的form1全部删除。首先，处理一下ui层的窗体，在ui层在添加一个窗体。右键UI层--添加--windows窗体,命名为Student（命名要有规律，我就用驼峰方法），同时把form1重命名为StudentMangent，接下来就是将两个窗体加上组件，完善布局。本人对每个按钮的命名方式为btn_**，比如查询按钮命名为btn_select，新增按钮为btn_add。StudentMangent窗体如图所示。

Student窗体如图所示：

ok，基本的业务界面做完了，现在就是代码的实现了，首先三个层之间是要互相引用的，引用后才可以使用相互的接口，添加引用非常简单，展开UI层，在两个窗体上面就是引用，右键引用--添加引用--项目，，你会发现有三个项目BLL,DLL,MODEL。在BLL和MODEL前面的方框打勾，然后确定。这样UI层就引用了BLL和MODEL层，同样的方法让BLL层引用DLL和MODEL，让DLL引用MODEL层，这样三层架构就相互引用了。

DLL代码实现：首先在DLL里新建两个类，右键DLL--添加--类，然后分别命名为SqlHelp和Student，我的理解就是，本着高内聚低耦合的思路，sqlhelp里写连接sql数据库的方法，student写操作数据库的方法。

sqlhelp代码：

Public Class SqlHelp
    '数据库要连接自己的，连接数据库，我用的方法在下面的图片中
    Dim Sqlconnstring As String = "Data Source=XXBYYQ;Initial Catalog=student;Integrated Security=True"
    ''' <summary>
    ''' 查询方法
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    Public Function GetSelectTable(sql As String) As DataTable
        Dim conn As SqlConnection = New SqlConnection(Sqlconnstring)
        conn.Open()
        Dim com As SqlCommand = New SqlCommand(sql, conn)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter(com)
        Dim ds As DataSet = New DataSet()
        adapter.Fill(ds)
        Return ds.Tables(0)
    End Function
    ''' <summary>
    ''' 添加/修改/删除
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    Public Function GetChangeNumber(sql As String) As Boolean
        Dim b As Boolean = False
        Dim conn As SqlConnection = New SqlConnection(Sqlconnstring)
        conn.Open()
        Dim com As SqlCommand = New SqlCommand(sql, conn)
        If com.ExecuteNonQuery() > 0 Then
            b = True
        End If
        Return b
    End Function



    Public Function GetExecuteScalar(sql As String) As Object
        Dim b As Boolean = False
        Dim conn As SqlConnection = New SqlConnection(Sqlconnstring)
        conn.Open()
        Dim com As SqlCommand = New SqlCommand(sql, conn)
        Return com.ExecuteScalar
    End Function

End Class

连接数据库：视图--服务器资源管理器--数据连接右键--添加连接，回弹出一个窗体，先点击一下刷新，然后点开下拉列表，找到你得服务器，然后在“连接到数据库”框里选则你为本学生系统创建的新的数据库（不会数据库的百度下，，，）然后测试链接，成功后确定，点击成功连接的数据库名称，右下角会显示属性，复制“连接字符串”里的字符串，找到上段代码中的Dim Sqlconnstring As String = "Data Source=XXBYYQ;Initial Catalog=student;Integrated Security=True"，把引号里的代码换成你刚才复制的。完成！！！

Student代码：

Public Class Student
    Dim sqlhelp As SqlHelp = New SqlHelp()
    Public Function GetSelectByWhere(where As String) As DataTable
        Dim sql As String = " select * from Student where IsDelete=0 "
        If Not String.IsNullOrEmpty(where) Then
            sql += " and " + where
        End If
        Return SqlHelp.GetSelectTable(sql)
    End Function
    Public Function InsertByModel(model As Model.Student) As Boolean
        Dim sql As String = " insert into Student(No, Name, Sex, Age,IsDelete) values('" + model.No + "','" + model.Name + "','" + model.Sex + "'," + model.Age.ToString() + "," + model.IsDelete.ToString() + ")"
        Return sqlhelp.GetChangeNumber(sql)
    End Function
    Public Function SelectMaxId() As Integer
        Dim sql As String = " select isnull( max(id),0) as id from Student "
        Return CInt(sqlhelp.GetExecuteScalar(sql))
    End Function

    Public Function UpdataByModel(model As Model.Student) As Boolean
        Dim sql As String = " update Student set No='" + model.No + "',Name='" + model.Name + "',Sex='" + model.Sex + "',Age=" + model.Age.ToString() + " where id=" + model.Id.ToString()
        Return sqlhelp.GetChangeNumber(sql)
    End Function
    Public Function DeleteByID(id As Integer) As Boolean
        Dim sql As String = " update Student set  IsDelete=1 where id=" + id.ToString()
        Return sqlhelp.GetChangeNumber(sql)
    End Function
End Class

这样，我们就完成了最底层，DLL层代码实现过程，接着就是BLL层代码实现。

BLL层：在BLL层也新建一个类，命名为Student，代码为

Imports DLL
Public Class Student
    Dim dll As DLL.Student = New DLL.Student()
    Public Function GetSelectByWhere(selectText As String) As DataTable
        Dim where As String = String.Empty
        If Not String.IsNullOrEmpty(selectText) Then  'IF在判断什么，不判断会怎么样
            where = GetSelectWhere(selectText)
        End If
        Return dll.GetSelectByWhere(where)
    End Function
    Public Function GetSelectWhere(selectText As String) As String
        Dim list As List(Of String) = New List(Of String)
        list.Add("No like'%" + selectText + "%'")
        list.Add(" Name like '%" + selectText + "%'")
        list.Add(" Sex like '%" + selectText + "%'")
        list.Add(" Age like '%" + selectText + "%'")
        Return "(" + String.Join(" or ", list.ToArray()) + ")"
    End Function

    Public Function InsertByModel(model As Model.Student) As Boolean
        Return dll.InsertByModel(model)
    End Function
    Public Function SelectMaxId() As Integer
        Return dll.SelectMaxId()
    End Function
    Public Function UpdataByModel(model As Model.Student) As Boolean
        Return dll.UpdataByModel(model)
    End Function
    Public Function DeleteByID(id As Integer) As Boolean
        Return dll.DeleteByID(id)
    End Function
End Class

接下来就是我到现在也不懂得MODEL层：

同理，新建一个类，命名为Student，代码如下：

Public Class Student
        ''' <summary>
        ''' ID
        ''' </summary>
        Public Id As Integer
        ''' <summary>
        ''' 学号
        ''' </summary>
        Public No As String
        ''' <summary>
        ''' 姓名
        ''' </summary>
        Public Name As String
        ''' <summary>
        ''' 性别
        ''' </summary>
        Public Sex As String
        ''' <summary>
        ''' 年龄
        ''' </summary>
        Public Age As Integer
        ''' <summary>
        ''' 是否删除 0：未删除 1：已删除
        ''' </summary>
        Public IsDelete As Integer
    End Class

现在说有业务处理得代码都已经实现了，接下来就是ui层引用了，

ui层---StudentManagent代码：

Imports Microsoft.Office.Interop
Public Class StudentManagent
    Dim bll As BLL.Student = New BLL.Student()
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BindSelectData()
    End Sub

    Public Sub BindSelectData()
        Dim selectText As String = txt_Select.Text.Trim()
        Dim dt As DataTable = bll.GetSelectByWhere(selectText)
        dgv_Data.DataSource = dt
        dgv_Data.ClearSelection()
        lbl_Count.Text = "查询1结果：" + dt.Rows.Count.ToString() + " 条"
    End Sub
    Private Sub btn_Select_Click(sender As Object, e As EventArgs) Handles btn_Select.Click
        BindSelectData()
    End Sub

    Private Sub btn_add_Click(sender As Object, e As EventArgs) Handles btn_add.Click
        Dim f As Student = New Student()
        f.ShowDialog()
        BindSelectData()
    End Sub
    Private Sub btn_Updata_Click(sender As Object, e As EventArgs) Handles btn_Updata.Click
        If dgv_Data.SelectedCells.Count > 0 Then
            Dim rowsIndex As Integer = dgv_Data.SelectedCells(0).RowIndex
            Dim f As Student = New Student
            f.model.Id = dgv_Data.Rows(rowsIndex).Cells("stu_Id").Value.ToString()
            f.model.No = dgv_Data.Rows(rowsIndex).Cells("stu_no").Value.ToString()
            f.model.Name = dgv_Data.Rows(rowsIndex).Cells("stu_name").Value.ToString()
            f.model.Sex = dgv_Data.Rows(rowsIndex).Cells("stu_sex").Value.ToString()
            f.model.Age = dgv_Data.Rows(rowsIndex).Cells("stu_age").Value.ToString()
            f.ShowDialog()
            BindSelectData()
        Else
            MessageBox.Show("请选择修改数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        If dgv_Data.SelectedCells.Count > 0 Then
            Dim rowsIndex As Integer = dgv_Data.SelectedCells(0).RowIndex
            If MessageBox.Show("是否删除数据！", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) Then
                Dim id As Integer = CInt(dgv_Data.Rows(rowsIndex).Cells("stu_Id").Value.ToString())
                If bll.DeleteByID(id) Then
                    MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                Else
                    MessageBox.Show("删除失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
                BindSelectData()
            End If
            BindSelectData()
        Else
            MessageBox.Show("请选择删除的数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
        End If
    End Sub

    Private Sub btn_Report_Click(sender As Object, e As EventArgs) Handles btn_Report.Click
        Dim dt As DataTable = dgv_Data.DataSource
        DgvToExcel("学生基本信息导出.xls", dt)
    End Sub



    Private Sub DgvToExcel(name As String, dt As DataTable)
        If dt.Rows.Count > 0 Then

            'Dim openExcel As OpenFileDialog = New OpenFileDialog()
            'openExcel.ShowDialog()

            Dim saveExcel As SaveFileDialog = New SaveFileDialog()

            saveExcel.Title = "Excel导出"
            saveExcel.FileName = name

            saveExcel.Filter = "Excel文件(.xls)|*.xls"
            Dim filename As String
            If saveExcel.ShowDialog = DialogResult.OK Then
                filename = saveExcel.FileName

                Dim excel As Excel.Application = New Excel.Application
                excel.DisplayAlerts = True
                excel.Workbooks.Add(True)
                excel.Visible = False

                For i = 0 To dt.Columns.Count - 1
                    excel.Cells(1, i + 1) = dt.Columns(i).ColumnName
                Next

                For j = 0 To dt.Rows.Count - 1
                    For i = 0 To dt.Columns.Count - 1
                        excel.Cells(j + 2, i + 1) = dt.Rows(j)(i).ToString()
                    Next
                Next

                excel.Workbooks(1).SaveCopyAs(filename)
                MessageBox.Show("数据导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

            End If
        Else
            MessageBox.Show("提示：没有记录不能导出数据")
        End If
    End Sub
End Class

ui层--Student代码：

Public Class Student
    Public model As Model.Student = New Model.Student()
    Dim bll As BLL.Student = New BLL.Student()
    Private Sub Student_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cbx_Sex.SelectedIndex = 0
        If model.Id > 0 Then '这个if判断的是什么
            BindUpdateData()
        Else
            Dim timeNow As DateTime = DateTime.Now()
            txt_NO.Text = timeNow.Year.ToString("D4") + timeNow.Month.ToString("D2") + (bll.SelectMaxId() + 1).ToString("d3")
        End If
    End Sub

    Private Sub BindUpdateData()
        txt_NO.Text = model.No
        txt_Name.Text = model.Name
        cbx_Sex.Text = model.Sex
        txt_Age.Text = model.Age.ToString()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbx_Sex.SelectedIndexChanged

    End Sub

    Private Sub Label2_Click(sender As Object, e As EventArgs) Handles Label2.Click

    End Sub

    Private Sub Label3_Click(sender As Object, e As EventArgs) Handles Label3.Click

    End Sub

    Private Sub Label4_Click(sender As Object, e As EventArgs) Handles Label4.Click

    End Sub

    Private Sub TextBox2_TextChanged(sender As Object, e As EventArgs) Handles txt_Name.TextChanged

    End Sub

    Private Sub btn_Save_Click(sender As Object, e As EventArgs) Handles btn_Save.Click
        model.No = txt_NO.Text
        model.Name = txt_Name.Text
        model.Sex = cbx_Sex.Text
        model.Age = txt_Age.Text.ToString()
        Try
            If IsOk() Then

                model.IsDelete = 0
                Dim b As Boolean = False
                If model.Id > 0 Then
                    b = bll.UpdataByModel(model)
                Else
                    b = bll.InsertByModel(model)
                End If
                If b Then
                    MessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                    Me.Close()
                Else
                    MessageBox.Show("保存失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Stop)

                End If
            Else
                MessageBox.Show("输入错误！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)

            End If

        Catch ex As Exception
            MessageBox.Show("出错了！" + ex.Message, "提示：", MessageBoxButtons.OK, MessageBoxIcon.Warning)

        End Try
    End Sub
    Public Function IsOk() As Boolean
        Dim b As Boolean = True
        If String.IsNullOrEmpty(txt_NO.Text.Trim()) Or String.IsNullOrEmpty(txt_Name.Text.Trim()) Or String.IsNullOrEmpty(txt_Age.Text.Trim()) Then
            b = False
        End If
        Return b
    End Function
End Class

到此，一个三层架构得学生成绩查询系统就实现了，，至于代码，你说你看不懂，我他娘的也没看懂呢，一起学习，一起进步吧！