vb.net 教程 8-3 数据库操作11- VB

数据新增、删除和修改操作差不多。

无非就是新建sql语句，执行command.ExecuteNonQuery

本节例子使用的是NorthWind中的类别表。

有了前面查询和修改的知识，相信大家可以看懂下面的代码：

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
Dim odc As New OleDbConnection()
odc.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;data source=Northwind.mdb;"

Dim odcommand As New OleDbCommand()
odcommand.CommandText = "insert into 类别(类别名称,说明,图片) values(@typename,@typeinfo,@imgType)"
odcommand.Connection = odc
odc.Open()

odcommand.Parameters.Add("@typename", OleDbType.VarChar)
odcommand.Parameters("@typename").Value = txtName.Text
odcommand.Parameters.Add("@typeinfo", OleDbType.VarChar)
odcommand.Parameters("@typeinfo").Value = txtInfo.Text

Dim bmp As New Bitmap(200, 100)
Dim g As Graphics = Graphics.FromImage(bmp)
g.DrawImage(picType.Image, New Rectangle(0, 0, picType.Width, picType.Height), New Rectangle(0, 0, picType.Image.Width, picType.Image.Height), GraphicsUnit.Pixel)
g.Dispose()
Dim ms As New IO.MemoryStream()
bmp.Save(ms, Imaging.ImageFormat.Bmp)

PictureBox1.Image = bmp

Dim buff() As Byte
ReDim buff(ms.Length)

buff = ms.ToArray
ms.Close()

odcommand.Parameters.Add("@imgType", OleDbType.LongVarBinary)
odcommand.Parameters("@imgType").Value = buff

odcommand.ExecuteNonQuery()

odc.Close()
End Sub
以上代码将二进制数据写入类别表中的图片列中。但是由于northwind数据库中的图片保存的和一般的图片略有区别，需要增加部分数据

要加入的数据：

Private Function getoledata() As Byte()
Dim oledatastring As String = "151C2F00020000000D000E0014002100FFFFFFFF4269746D617020496D616765005061696E742E5069637475726500010500000200000007000000504272757368000000000000000000A0290000"
Dim datalength As Integer = oledatastring.Length

Dim buff() As Byte
ReDim buff(datalength \ 2 - 1)

For i As Integer = 0 To datalength - 1 Step 2
buff(i \ 2) = Convert.ToInt32(oledatastring.Substring(i, 2), 16)
Next
Return buff
End Function
修改后的代码：

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim odc As New OleDbConnection()
'odc.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;data source=D:\save\博客教程\08 数据库\Northwind1.mdb;jet oledb:database password=northwind;"
odc.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;data source=Northwind.mdb;"

Dim odcommand As New OleDbCommand()
odcommand.CommandText = "insert into 类别(类别名称,说明,图片) values(@typename,@typeinfo,@imgType)"
odcommand.Connection = odc
odc.Open()

odcommand.Parameters.Add("@typename", OleDbType.VarChar)
odcommand.Parameters("@typename").Value = txtName.Text
odcommand.Parameters.Add("@typeinfo", OleDbType.VarChar)
odcommand.Parameters("@typeinfo").Value = txtInfo.Text

Dim bmp As New Bitmap(172, 120)
Dim g As Graphics = Graphics.FromImage(bmp)
g.DrawImage(picType.Image, New Rectangle(0, 0, 172, 120), New Rectangle(0, 0, picType.Image.Width, picType.Image.Height), GraphicsUnit.Pixel)
g.Dispose()
Dim ms As New IO.MemoryStream()
bmp.Save(ms, Imaging.ImageFormat.Bmp)

Dim olebodybuff() As Byte
ReDim olebodybuff(ms.Length)

ms.Position = 0
'ms.Write(olebodybuff, 0, ms.Length)
olebodybuff = ms.ToArray
ms.Close()

Dim oleheadbuff() As Byte
oleheadbuff = getoledata()

Dim olebuff() As Byte
ReDim olebuff(olebodybuff.Length + oleheadbuff.Length - 1)
oleheadbuff.CopyTo(olebuff, 0)
olebodybuff.CopyTo(olebuff, oleheadbuff.Length)

odcommand.Parameters.Add("@imgType", OleDbType.LongVarBinary)
odcommand.Parameters("@imgType").Value = olebuff

odcommand.ExecuteNonQuery()

odc.Close()
End Sub