Use DataSet to access binary files in SQL Server
Author Zhu Erlin uses Dataset to access the binary in SQL Server to access and update the operation, the following is a detailed code presentation demo environment: database machine name: s_test login name: SA password: 7890 Database name DB_TEST Next : CREATE TABLE TB_TEST (ID Int Id Id Id Id Id Intity (1), Photo Image, Constraint PK_TB_TEST Primary Key (ID)) 1. Save the file on the hard disk to the database (VB.NET) --------- ------------------------------------------------- -------------------------------------------------- ------ 'The following example saves the C: /1.jpg file to the TB_TEST table of the database' ---------------------- ------------------------------------' -------------- ------------------------------------------- Imports System.IOMPORTS SYSTEM .DATA.SQLCLIENTPUBLIC CLASS ImageShared Sub Main () 'Read File Data DIM FS = New FileStream ("C: /1.jpg", IO.FILEMODE.OPEN, IO.FILAACCESS.READ) DIM IMGDATA (fs.length - 1 ) As Bytefs.Read (imgData, 0, fs.Length - 1) fs.close () Dim tempConnection As New SqlConnectionDim tempAdapter As SqlDataAdapterDim tempDataSet As New DataSet 'open database Connectivity tempConnection.ConnectionString = "server = s_Test; uid = sa; PWD = 7890; Database = db_test "TempConnection.Open () Tempadapter = new SqldataAdapter (" Select * from T b_test WHERE 1 = 0 ", tempConnection) Dim cb As New SqlCommandBuilder (tempAdapter) tempAdapter.Fill (tempDataSet) 'is inserted into a recording Dim tempDataRow As DataRowtempDataRow = tempDataSet.Tables (0) .NewRow () tempDataRow (" photo ") = imgDatatempDataSet .Tables (0) .Rows.add (TempdataroW) TempConnection.close () End Subend Class 2, save files in the database to hard disk (VB.NET) '-------- -------------------------------------------------- '------------------------------------- ------- 'Save the PHOTO of the first record in the TB_TEST table of the database to C: /2.jpg' ---------------------------------------------------------------------------------------------------- ------------------------------------------ '
-------------------------------------------------- -------- Imports System.IOImports System.Data.SqlClientPublic Class imageShared Sub Main () Dim tempConnection As New SqlConnectionDim tempAdapter As SqlDataAdapterDim tempDataSet As New DataSet 'open database Connectivity, extracted 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 the file to the hard disk file C: /2.jpgdim imgdata () as byteimgdata = tempDataSet.tables (0). Rows (0) .Item ("photo") DIM FS AS fileStreamfs = File.create ("c: /2.jpg", IMGData.Length - 1) fs.write (IMGDATA, 0, IMGDATA.LENGTH - 1) fs.close () end ifend sund class 3, updating the database saved in the database file'------------------------------------------------ ----------'-------------------------------------- ----------------- 'The following example is updated to C: /2.jpg' in the first record of the TB_TEST table in the database. -------------------------------------------------- ---'-- -------------------------------------------------- ----- Imports system.ioimports system.data.sqlclientpublic class imageshared sub main () 'Read file DIM fs = new system.io.filestream ("c: /2.jpg", IO.FILEMODE.OPEN, IO.FileAccess.Read) Dim imgData (fs.Length - 1) As Bytefs.Read (imgData, 0, fs.Length - 1) fs.close () Dim tempConnection As New SqlConnectionDim tempAdapter As SqlDataAdapterDim tempDataSet As New DataSet 'open database Connection, remove data TempConnection.connectionstring = "server = s_test; uid = sa; pwd = 7890; database = db_test" TempConnection.Open () Tempadapter = new SqlDataAdapter ("SELECT TOP 1 * from tb_test"
