vb 利用ADO.Net实现数据库的连接，修改，删除，插入操作
代码如下：
View Code
1 Public Class 全部学生信息
2
3 Dim conn As New SqlConnection
4 Dim rs As New SqlDataAdapter
5 'Dim dt As New DataTable
6 Dim ds As New DataSet
7
8 Public Function connect_db() As SqlConnection
9 Dim constr As String
10 constr = "Server=PC-201105311336\SQLEXPRESS;uid=sa;pwd=123456;database=jiaxiaoai"
11 'rs.Fill(dataset, "student")
12 conn = New SqlConnection(constr)
13 conn.Open()
14 connect_db = conn
15 End Function
16 Private Sub 全部学生信息_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load '窗口初始化函数
17 Dim sql As String
18 Dim dt As New DataTable
19 sql = "select * from student"
20 conn = connect_db()
21 rs = New SqlDataAdapter(sql, conn)
22 conn.Close()
23 rs.Fill(dt) '填充表
24 DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
25 DataGridView1.Columns(0).HeaderText = "学号" '设置列名
26 DataGridView1.Columns(1).HeaderText = "姓名"
27 DataGridView1.Columns(2).HeaderText = "年龄"
28 DataGridView1.Columns(3).HeaderText = "成绩"
29 rownum.Text = Str(DataGridView1.RowCount) '获取行数
30 xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
31 nametext.Text = DataGridView1.Rows(0).Cells(1).Value
32 If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
33 agetext.Text = ""
34 Else
35 agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
36 End If
37 If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
38 scoretext.Text = ""
39 Else
40 scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
41 End If
42
43 End Sub
44
45
46 Private Sub selectbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles selectbtn.Click
47 Dim sql As String
48 Dim dt As New DataTable
49 sql = "select * from student where name like 'l%'"
50 conn = connect_db()
51 rs = New SqlDataAdapter(sql, conn)
52 conn.Close()
53 rs.Fill(dt) '填充表
54 DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
55 DataGridView1.Columns(0).HeaderText = "学号" '设置列名
56 DataGridView1.Columns(1).HeaderText = "姓名"
57 DataGridView1.Columns(2).HeaderText = "年龄"
58 DataGridView1.Columns(3).HeaderText = "成绩"
59 rownum.Text = Str(DataGridView1.RowCount) '获取行数
60 xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
61 nametext.Text = DataGridView1.Rows(0).Cells(1).Value
62 If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
63 agetext.Text = ""
64 Else
65 agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
66 End If
67 If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
68 scoretext.Text = ""
69 Else
70 scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
71 End If
72 End Sub
73
74
75 Private Sub DataGridView1_CellMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick '鼠标点击单元格触发事件
76 'DataGridView1.CurrentRow.Index获取当前单元格的行号
77 xuehaotext.Text = DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value '获取datagridview中的某个值
78 nametext.Text = DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(1).Value
79 If (DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value = 0) Then
80 agetext.Text = ""
81 Else
82 agetext.Text = Str(DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value)
83 End If
84 If (DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value = 0) Then
85 scoretext.Text = ""
86 Else
87 scoretext.Text = Str(DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value)
88 End If
89 End Sub
90
91 Private Sub updatebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updatebtn.Click
92 Dim sql As String
93 Dim row As String
94 Dim dc As New SqlCommand
95 Dim age
96 If agetext.Text = "" Then
97 age = 0
98 Else
99 age = Val(agetext.Text)
100 End If
101 sql = "update student set sid='" & xuehaotext.Text & "', name='" & nametext.Text & "',age='" & age & "',score='" & scoretext.Text & "' where sid='" & DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value & "'"
102 conn = connect_db()
103 dc = New SqlCommand(sql, conn)
104 row = dc.ExecuteNonQuery()
105 conn.Close()
106 If (row > 0) Then
107 MsgBox("修改成功")
108 Else
109 MsgBox("修改失败")
110 End If
111 DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value = xuehaotext.Text
112 DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(1).Value = nametext.Text
113 DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value = agetext.Text
114 DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(3).Value = scoretext.Text
115 End Sub
116
117 Private Sub insertbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insertbtn.Click
118 Dim sql As String
119 Dim row As String
120 Dim dc As New SqlCommand
121 Dim age
122 If agetext.Text = "" Then
123 age = 0
124 Else
125 age = Val(agetext.Text)
126 End If
127 sql = "insert into student values('" & xuehaotext.Text & "', '" & nametext.Text & "','" & age & "','" & scoretext.Text & "')"
128 conn = connect_db()
129 dc = New SqlCommand(sql, conn)
130 row = dc.ExecuteNonQuery()
131 'conn.Close()
132 If (row > 0) Then
133 MsgBox("插入成功")
134 Else
135 MsgBox("插入失败")
136 End If
137
138 Dim dt As New DataTable
139 sql = "select * from student"
140 'conn = connect_db()
141 rs = New SqlDataAdapter(sql, conn)
142 conn.Close()
143 rs.Fill(dt) '填充表
144 DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
145 DataGridView1.Columns(0).HeaderText = "学号" '设置列名
146 DataGridView1.Columns(1).HeaderText = "姓名"
147 DataGridView1.Columns(2).HeaderText = "年龄"
148 DataGridView1.Columns(3).HeaderText = "成绩"
149 rownum.Text = Str(DataGridView1.RowCount) '获取行数
150 xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
151 nametext.Text = DataGridView1.Rows(0).Cells(1).Value
152 If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
153 agetext.Text = ""
154 Else
155 agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
156 End If
157 If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
158 scoretext.Text = ""
159 Else
160 scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
161 End If
162 End Sub
163
164 Private Sub deletebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles deletebtn.Click
165 Dim sql As String
166 Dim row As String
167 Dim dc As New SqlCommand
168 Dim age
169 If agetext.Text = "" Then
170 age = 0
171 Else
172 age = Val(agetext.Text)
173 End If
174 sql = "delete student where sid='" & DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value & "'and name='" & DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(1).Value & "'"
175 conn = connect_db()
176 dc = New SqlCommand(sql, conn)
177 row = dc.ExecuteNonQuery()
178 'conn.Close()
179 If (row > 0) Then
180 MsgBox("删除成功")
181 Else
182 MsgBox("删除失败")
183 End If
184
185 Dim dt As New DataTable
186 sql = "select * from student"
187 conn = connect_db()
188 rs = New SqlDataAdapter(sql, conn)
189 conn.Close()
190 rs.Fill(dt) '填充表
191 DataGridView1.DataSource = dt '将表中的数据显示到datagridView控件中，绑定数据
192 DataGridView1.Columns(0).HeaderText = "学号" '设置列名
193 DataGridView1.Columns(1).HeaderText = "姓名"
194 DataGridView1.Columns(2).HeaderText = "年龄"
195 DataGridView1.Columns(3).HeaderText = "成绩"
196 rownum.Text = Str(DataGridView1.RowCount) '获取行数
197 xuehaotext.Text = DataGridView1.Rows(0).Cells(0).Value '获取datagridview中的某个值
198 nametext.Text = DataGridView1.Rows(0).Cells(1).Value
199 If (DataGridView1.Rows(0).Cells(2).Value = 0) Then
200 agetext.Text = ""
201 Else
202 agetext.Text = Str(DataGridView1.Rows(0).Cells(2).Value)
203 End If
204 If (DataGridView1.Rows(0).Cells(3).Value = 0) Then
205 scoretext.Text = ""
206 Else
207 scoretext.Text = Str(DataGridView1.Rows(0).Cells(3).Value)
208 End If
209
210 End Sub
211 End Class
