VBNET操作数据库

Imports System.Data.SqlClientImports System.IOImports System.Drawing.ImagingPublic Class SQL Dim memorypath As String = Private Sub SQL_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load setkongjianfalse() FillListbox() End Sub Private Sub FillListbox() Dim cn As New SqlConnection(mycon) Dim cmd As New SqlCommand cmd.Connection = cn cmd.CommandText = select 身份证号码 from 虚拟工作室 cmd.Parameters.Add(id, SqlDbType.NVarChar, 15).Value = ListBox1.SelectedItems.ToString cn.Open() ListBox1.Items.Clear() Dim myreader As SqlDataReader = cmd.ExecuteReader() While myreader.Read ListBox1.Items.Add(myreader(0) End While myreader.Close() cn.Close() ListBox1.SelectedIndex = 0 End Sub Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged If ListBox1.SelectedIndex = -1 Then Exit Sub End If Dim cn As New SqlConnection(mycon) Dim cmd As New SqlCommand cmd.Connection = cn cmd.CommandText = select 员工编号,照片 from 虚拟工作室 where 身份证号码 = & ListBox1.SelectedItem.ToString & cmd.Parameters.Add(Id, SqlDbType.NVarChar, 10).Value = ListBox1.SelectedItem.ToString cmd.CommandText = SELECT * FROM dbo.虚拟工作室 WHERE 身份证号码 = Id cmd.Parameters.Add(Id, SqlDbType.NVarChar, 10).Value = ListBox1.SelectedItem.ToString cn.Open() Dim myreader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult Or CommandBehavior.SingleRow) Try If myreader.Read Then txtID.Text = myreader.GetInt32(0) txtID.Text = myreader.GetString(1) txtName.Text = myreader.GetString(2) txtgender.Text = myreader.GetString(3) Txtaddress.Text = myreader.GetString(4) txtzip.Text = myreader.GetString(5) DTPbirthday.Value = myreader.GetDateTime(6) txtmaritalstatus.Text = myreader.GetString(7) DTPhiredate.Value = myreader.GetDateTime(8) NUhiresalary.Value = myreader.GetSqlMoney(9).ToString nudcurrentsalary.Value = myreader.GetSqlMoney(10).ToString DtpRAISEdate.Value = myreader.GetDateTime(11) txtdepartment.Text = myreader.GetString(12) Dim bt(myreader.GetBytes(13, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte myreader.GetBytes(13, 0, bt, 0, bt.Length) Dim fs As New MemoryStream(bt) PictureBox1.Image = Image.FromStream(fs) End If Catch ex As Exception Finally myreader.Close() cn.Close() End Try End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim cn As New SqlConnection(mycon) Dim insertstr As String = insert into 虚拟工作室 values(id,name,gender,address,zip, & _ birthday,maritalstatus,hiredate,hiresalary,currentsalary,raisedate,department,photo) Dim cmd As New SqlCommand(insertstr, cn) cmd.Parameters.Add(id, SqlDbType.NVarChar, 18).Value = txtID.Text cmd.Parameters.Add(name, SqlDbType.NVarChar, 10).Value = txtName.Text cmd.Parameters.Add(gender, SqlDbType.NVarChar, 1).Value = txtgender.Text cmd.Parameters.Add(address, SqlDbType.NVarChar, 41).Value = Txtaddress.Text cmd.Parameters.Add(zip, SqlDbType.NVarChar, 6).Value = txtzip.Text cmd.Parameters.Add(birthday, SqlDbType.DateTime).Value = DTPbirthday.Value cmd.Parameters.Add(maritalstatus, SqlDbType.NVarChar, 2).Value = txtmaritalstatus.Text cmd.Parameters.Add(hiredate, SqlDbType.DateTime).Value = DTPhiredate.Value cmd.Parameters.Add(hiresalary, SqlDbType.Money).Value = NUhiresalary.Value cmd.Parameters.Add(currentsalary, SqlDbType.Money).Value = nudcurrentsalary.Value cmd.Parameters.Add(raisedate, SqlDbType.DateTime).Value = DtpRAISEdate.Value cmd.Parameters.Add(department, SqlDbType.NVarChar, 10).Value = txtdepartment.Text If memorypath.Length = 0 Then If MessageBox.Show(你没有选择图片将选择默认图片,是否确定?, Me.Text, MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.OK Then memorypath = Application.StartupPath & 大风车.jpg End If End If Dim fs As FileStream = New FileStream(memorypath, FileMode.Open, FileAccess.Read)方法一 Dim br As BinaryReader = New BinaryReader(fs) Dim photo() As Byte = br.ReadBytes(fs.Length) br.Close() fs.Close() cmd.Parameters.Add(photo, SqlDbType.Image, photo.Length).Value = photo Dim ms As MemoryStream = New MemoryStream PictureBox1.Image.Save(ms, ImageFormat.Jpeg) Dim dt(ms.Length - 1) As Byte ms.Position = 0 ms.Read(dt, 0, ms.Length) Dim prm As New SqlParameter(photo, SqlDbType.VarBinary, dt.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, dt) cmd.Parameters.Add(prm) cn.Open() Try cmd.ExecuteNonQuery() MessageBox.Show(你成功的添加了记录, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information) FillListbox() Catch ex As Exception MessageBox.Show(ex.Message) Finally cn.Close() End Try ListBox1.Focus() End Sub Private Sub Label15_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label15.Click Dim ofd As New OpenFileDialog With ofd .Filter = (图形文件*.jpg)|*.jpg .ShowHelp = True .InitialDirectory = F:娱乐 If .ShowDialog = Windows.Forms.DialogResult.OK Then memorypath = .FileName PictureBox1.Image = Image.FromFile(memorypath) End If End With End Sub Private Sub setkongjianfalse() Txtaddress.Enabled = False txtdepartment.Enabled = False txtgender.Enabled = False txtID.Enabled = False txtmaritalstatus.Enabled = False txtName.Enabled = False txtzip.Enabled = False nudcurrentsalary.Enabled = False NUhiresalary.Enabled = False DTPbirthday.Enabled = False DTPhiredate.Enabled = False DtpRAISEdate.Enabled = False PictureBox1.Enabled = False End Sub Private Sub setkongjiantrue() Txtaddress.Enabled = True txtdepartment.Enabled = True txtgender.Enabled = True txtID.Enabled = True txtmaritalstatus.Enabled = True txtName.Enabled = True txtzip.Enabled = True nudcurrentsalary.Enabled = True NUhiresalary.Enabled = True DTPbirthday.Enabled = True DTPhiredate.Enabled = True DtpRAISEdate.Enabled = True PictureBox1.Enabled = True End Sub Private Sub setkongjianempty() Txtaddress.Text = txtdepartment.Text = txtgender.Text = txtID.Text = txtmaritalstatus.Text = txtName.Text = txtzip.Text = End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim cn As New SqlConnection(mycon) Dim str As String = delete 虚拟工作室 where 身份证号码=id Dim cmd As New SqlCommand(str, cn) cmd.Parameters.Add(id, SqlDbType.NVarChar, 10).Value = ListBox1.SelectedItem.ToString Try cn.Open() cmd.ExecuteNonQuery() MessageBox.Show(你已经成功的删除了记录) FillListbox() Catch ex As Exception MessageBox.Show(ex.Message) Finally cn.Close() End Try ListBox1.Focus() 建立连接 Dim con As SqlConnection = New SqlConnection(mycon) 删除数据记录的 DELETE 语句 Dim deleteStr As String = _ DELETE dbo.虚拟工作室 & _ WHERE 身份证号码 = Id 使用 SqlCommand 类的第三个构造函数来建立 SqlCommand 对象 并将内含参数的 DELETE 语句赋给 SqlCommand 对象 Dim deleteCMD As New SqlCommand(deleteStr, con) 在参数集合中为参数加入一个参数对象并设定此参数的值 deleteCMD.Parameters.Add(Id, SqlDbType.NVarChar, 10).Value = ListBox1.SelectedItem.ToString Try 打开连接 con.Open() 执行数据命令来删除数据记录 deleteCMD.ExecuteNonQuery() MessageBox.Show(已经成功删除数据记录。, _ 恭喜您, MessageBoxButtons.OK, _ MessageBoxIcon.Information) 重新将所有员工的身份证号码填入 ListBox 控件 FillListbox() Catch ex As Exception MessageBox.Show(ex.ToString(), 请注意, MessageBoxButtons.OK, MessageBoxIcon.Stop) Finally 关闭连接 con.Close() End Try ListBox1.Focus() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 建立连接 Dim con As SqlConnection = New SqlConnection(mycon) 更新数据记录的 UPDATE 语句 Dim updateStr As String = _ UPDATE dbo.虚拟工作室 & _ SET 姓名= Name & _ ,性别= Gender & _ ,地址= Address & _ ,邮政编码= Zip & _ ,出生日期= Birthday & _ ,婚姻状况= MaritalStatus & _ ,雇用日期= HireDate & _ ,起薪= HireSalary & _ ,当前工资= CurrentSalary & _ ,加薪日期= RaiseDate & _ ,部门= Department & _ ,照片 = BLOBData & _ WHERE 身份证号码 = Id 使用 SqlCommand 类的第三个构造函数来建立 SqlCommand 对象 并将内含参数的 UPDATE 语句赋给 SqlCommand 对象 Dim updateCMD As New SqlCommand(updateStr, con) 在参数集合中为各个参数加入一个参数对象并设定这些参数的值 updateCMD.Parameters.Add(Name, SqlDbType.NVarChar, 10).Value = txtName.Text updateCMD.Parameters.Add(Gender, SqlDbType.NVarChar, 1).Value = txtgender.Text updateCMD.Parameters.Add(Address, SqlDbType.NVarChar, 41).Value = Txtaddress.Text updateCMD.Parameters.Add(Zip, SqlDbType.NVarChar, 5).Value = txtzip.Text updateCMD.Parameters.Add(Birthday, SqlDbType.DateTime).Value = DTPbirthday.Value updateCMD.Parameters.Add(MaritalStatus, SqlDbType.NVarChar, 2).Value = txtmaritalstatus.Text updateCMD.Parameters.Add(HireDate, SqlDbType.DateTime).Value = DTPhiredate.Value updateCMD.Parameters.Add(HireSalary, SqlDbType.Money).Value = NUhiresalary.Value updateCMD.Parameters.Add(CurrentSalary, SqlDbType.Money).Value = nudcurrent