同济大学数据库作业lab7,8

同济大学

《数据库技术及应用》

实验报告

实验报告题目：

建立与数据库的链接和基于绑定的数据库访问

姓名：学号：

年级：专业：

指导教师：

日期：2014 年11 月16 日

一．实验目的

1.掌握数据库访问模型的主要对象和功能

2.掌握SQL和数据库的链接方法

3.学习绑定对象的操作方法

二．实验内容

（实验题目+运行界面截图+实现代码）

1.掌握前台和SqlServer后台的链接方法

Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim myconn As New SqlConnection("database=university;data source=jd43\SQLEXPRESS; integrated security=true")

Dim mysql As String = "select * from student"

Dim myadapter As New SqlDataAdapter(mysql, myconn)

Dim mydataset As New DataSet

myadapter.Fill(mydataset, "student")

DataGridView1.DataSource = mydataset.Tables(0)

End Sub

End Class

2.在university上用完成如下界面

Imports System.Data.SqlClient

Public Class Form2

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim myconn As New SqlConnection("database=university;data source=jd43\SQLEXPRESS; integrated security=true")

Dim mysql As String= "select student.snum as '学号',sname as '姓名',count(secnum) as '选修课程门数',avg(score) as '平均成绩',sum(1-score/60) as '不及格门数' from student,sc where student.snum=sc.snum group by student.snum,sname"

Dim myadapter As New SqlDataAdapter(mysql, myconn)

Dim mydataset As New DataSet

myadapter.Fill(mydataset, "student")

DataGridView1.DataSource = mydataset.Tables(0)

End Sub

End Class

3.建立university和student的浏览界面

Imports System.Data.SqlClient

Public Class Form1

Dim mybind As New BindingSource

Dim mysql As String = "select * from student"

Dim myconn As New SqlConnection("database=university;data

source=jd43\SQLEXPRESS;integrated security=true")

Dim myadapter As New SqlDataAdapter(mysql, myconn)

Dim mydataset As New DataSet

Private Sub退出系统ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 退出系统ToolStripMenuItem.Click

End

End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

myadapter.Fill(mydataset, "student")

mybind.DataSource = mydataset

mybind.DataMember = "student"

TextBox1.DataBindings.Add(New Binding("text", mybind, "snum", True))

TextBox2.DataBindings.Add(New Binding("text", mybind, "sname", True))

TextBox3.DataBindings.Add(New Binding("text", mybind, "sex", True))

TextBox4.DataBindings.Add(New Binding("text", mybind, "dept", True))

TextBox5.DataBindings.Add(New Binding("text", mybind, "birthday", True))

TextBox6.DataBindings.Add(New Binding("text", mybind, "telephone", True)) End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click mybind.MoveFirst()

End Sub

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click mybind.MovePrevious()

End Sub

Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click mybind.MoveNext()

End Sub

Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click mybind.MoveLast()

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click mybind.AddNew()

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click mybind.RemoveCurrent()

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click Me.Validate()

mybind.EndEdit()

Dim cm As New SqlCommandBuilder(myadapter)

myadapter.Update(mydataset.Tables(0))

End Sub

End Class

4.完成学生成绩浏览功能的设计