同济大学数据库作业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.完成学生成绩浏览功能的设计
Imports System.Data.SqlClient
Public Class Form2
Dim mybind As New BindingSource
Dim mysql As String = "select * from shiyan8"
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 Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myadapter.Fill(mydataset, "shiyan8")
mybind.DataSource = mydataset
mybind.DataMember = "shiyan8"
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, "cname", True))
TextBox4.DataBindings.Add(New Binding("text", mybind, "score", True)) End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click mybind.MoveFirst()
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
mybind.MovePrevious()
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
mybind.MoveNext()
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
mybind.MoveLast()
End Sub
End Class
三．实验体会或收获（有感而发，没有体会也可以不写）在用代码进行数据库的链接时发现data source后不能加（local），否则总是显示无法访问。

而如果写“jd43\SQLEXPRESS”则可以进行访问。

用直接拖动的方法做出的和数据库的链接在不同的电脑上就不能正常运行，也是显示无法访问，然而用代码编写的链接只需要改动一下data source就可以继续访问。

虽然麻烦，但是感觉比较好用。