Using datasets to access binaries in SQL Server

server| binary uses datasets to access binaries in SQL Server



Author Zhu Yi


The dataset makes it easy to access and update binary files in SQL Server, and the following is a detailed code demonstration

Demo Environment:

Database machine Name: s_test
Login name: SA
Password: 7890
Database name Db_test

Set up a table below:
CREATE TABLE tb_test (ID int identity (1,1), photo image, constraint pk_tb_test key (ID))
First, save the files on the hard disk to the database (vb.net)


'----------------------------------------------------------
'----------------------------------------------------------
' The following example saves the C:\1.jpg file to the Tb_test table in the database
'----------------------------------------------------------
'----------------------------------------------------------
Imports System.IO
Imports System.Data.SqlClient

Public Class Image
Shared Sub Main ()

' Read file data
Dim fs = New FileStream ("C:\1.jpg", IO. FileMode.Open, IO. FileAccess.Read)
Dim Imgdata (fs. LENGTH-1) as Byte
Fs. Read (imgdata, 0, FS. LENGTH-1)
Fs.close ()

Dim Tempconnection as New SqlConnection
Dim Tempadapter as SqlDataAdapter
Dim Tempdataset as New DataSet
' Open a database connection
tempconnection.connectionstring = "Server=s_test;uid=sa;pwd=7890;database=db_test"
Tempconnection.open ()
Tempadapter = New SqlDataAdapter ("select * from Tb_test WHERE 1=0", tempconnection)
Dim CB as New SqlCommandBuilder (Tempadapter)
Tempadapter.fill (Tempdataset)
' Insert a record
Dim Tempdatarow as DataRow
Tempdatarow = tempdataset.tables (0). NewRow ()
Tempdatarow ("photo") = Imgdata
Tempdataset.tables (0). Rows.Add (Tempdatarow)
Tempadapter.update (Tempdataset)
Tempconnection.close ()
End Sub
End Class
Save the files from the database to the hard disk (vb.net)

'----------------------------------------------------------
'----------------------------------------------------------
' The following example saves the photo of the first record in the Tb_test table of the database to C:\2.jpg
'----------------------------------------------------------
'----------------------------------------------------------
Imports System.IO
Imports System.Data.SqlClient

Public Class Image
Shared Sub Main ()

Dim Tempconnection as New SqlConnection
Dim Tempadapter as SqlDataAdapter
Dim Tempdataset as New DataSet
' Open the database connection, remove the data
tempconnection.connectionstring = "Server=s_test;uid=sa;pwd=7890;database=db_test"
Tempconnection.open ()
Tempadapter = New SqlDataAdapter ("select top 1 * from Tb_test", tempconnection)
Tempadapter.fill (Tempdataset)
Tempconnection.close ()

If tempdataset.tables (0). Rows.Count > 0 Then
' Save file to hard disk file c:\2.jpg
Dim Imgdata () as Byte
Imgdata = tempdataset.tables (0). Rows (0). Item ("Photo")
Dim FS as FileStream
FS = File.create ("c:\2.jpg", imgdata.length-1)
Fs. Write (imgdata, 0, Imgdata.length-1)
Fs. Close ()
End If
End Sub
End Class


Iii. update the files saved in the database


'----------------------------------------------------------
'----------------------------------------------------------
' The following example updates the photo of the first record in the Tb_test table of the database to C:\2.jpg
'----------------------------------------------------------
'----------------------------------------------------------
Imports System.IO
Imports System.Data.SqlClient

Public Class Image
Shared Sub Main ()

' Read the file
Dim fs = New System.IO.FileStream ("C:\2.jpg", IO. FileMode.Open, IO. FileAccess.Read)
Dim Imgdata (fs. LENGTH-1) as Byte
Fs. Read (imgdata, 0, FS. LENGTH-1)
Fs.close ()

Dim Tempconnection as New SqlConnection
Dim Tempadapter as SqlDataAdapter
Dim Tempdataset as New DataSet
' Open the database connection, remove the data
tempconnection.connectionstring = "Server=s_test;uid=sa;pwd=7890;database=db_test"
Tempconnection.open ()
Tempadapter = New SqlDataAdapter ("select top 1 * from Tb_test", tempconnection)
Tempadapter.fill (Tempdataset)
' Update data
Dim CB as New SqlCommandBuilder (Tempadapter)
Tempdataset = New DataSet
Tempadapter.fill (Tempdataset)
Tempdataset.tables (0). Rows (0). Item ("photo") = Imgdata
Tempadapter.update (Tempdataset)
Tempconnection.close ()
End Sub
End Class

Summarize:

Using DataSet can easily access and update binary files in SQL Server, although not the most effective method, but through the introduction of text, can enable beginners to master a binary file in the database operation method, hope to help developers.
Vvvv