Upload and download files using VB and SQL Server

 
VB uploads and downloads file data to SQL Server

Preface

Many organizations, especially manufacturing and design institutes, started their computer applications earlier. In these organizations, the issuance of various laws and regulations and a large amount of data generated in daily work form a wide variety of archives (various files ). In the face of massive data volumes, most of the original file management systems are insufficient to back up, restore, update, and maintain data. The traditional file-based management method brings potential risks to the confidentiality of various archives. To this end, more and more organizations are developing database-based file and file management systems to overcome these problems.

Combined with the technology file management system developed by the author, this article takes the database in the Visual Basic6.0 and SQL Server 2000 environments as an example to introduce the file data upload and download of SQL Server 2000 databases.

Database Connection

Database operations must be performed on the basis of establishing connections with local databases or network databases. This requirement can be achieved in two ways.

For ease of understanding, assume that the server is named data_server and the archive database is named science_file. One of the data tables is named office, and a field named office and image is designed to save files. To store files of different sizes in SQL Server data tables, you must create an image-type field in the data table. The maximum file size of this field can be 2 GB.

1. Use the adodc control to connect

Visual Basic provides an adodc control. It sets the adodc attribute and completes the database connection according to the wizard prompts. The specific process is as follows:

First, select the generate button on the properties page to go to the Data Link Properties dialog box. Then select the connection properties page in the dialog box and select or enter important information such as the server name and database. Finally, test the connection, after the connection is successful, click OK to return to the properties page dialog box to obtain the connection string, as shown in the following example:

Provider = sqloledb.1; Integrated Security = sspi; persist; security info = false; initial catalog = science_file; Data Source = data_server

Use the following statements to connect to the specified database:

Dim odbcstr as string, adocon as new ADODB. Connection
Odbcstr = "provider = sqloledb.1; Integrated Security = sspi; persist Security info = false ;_
Initial catalog = science_file; Data Source = data_server"
Adocon. Open odbcstr 'connect to the database

2. Use the netserverenum Function

This function is an API function that provides a list of servers installed with the SQL Server database management system. The method is as follows:

Private declare function netserverenum lib "netapi32 "_
(Lpserver as any, byval Llevel as long, vbuffer as any ,_
Lpreferedmaxlen as long, lentriesread as long, ltotalentries as long ,_
Byval lservertype as long, byval sdomain $, vresume as any) as long
Private declare sub copymemory lib "Kernel32" alias "rtlmovememory" (destination _
As any, source as any, byval length as long)
Private declare function lstrlenw lib "Kernel32" (byval lpstring as long) as long

Private type sv_100
Platform as long
Name as long
End type

Dim sv100 as sv_100, nret as long, I as long, lserverinfo as long
Dim lserverinfo as long, lpreferedmaxlen as long, lentriesread as long
Dim ltotalentries as long, sdomain as string, vresume as Variant
Dim buffer () as byte, nlen as long
Lpreferedmaxlename = 65536
Nret = netserverenum (0,101, lserverinfo, lpreferedmaxlen, lentriesread, ltotalentries, 4, sdomain, vresume)
If nret = 0 or nret = 234 & then
For I = 0 to lentriesread-1
Copymemory sv100, byval lserverinfo, Len (sv100)
Nlen = lstrlenw (sv100.name) * 2
If nlen then
Redim buffer (0 to (nlen-1) as byte
Copymemory buffer (0), byval sv100.name, nlen
End if
Combo1.list (I) = buffer 'server name
Lserverinfo = lserverinfo + 24
Next I
End if

The server name is displayed in the combo1 control. You can select the server name from which the archive data is stored, and then connect to the database on the selected server using the following statement:

Odbcstr = "provider = sqloledb.1; Integrated Security = sspi; persist Security info = false ;_
Initial catalog = science_file; Data Source = "& form2.combo1. Text
Adocon. Open odbcstr 'connect to the database

The meanings of the variables are the same as described above. The content of form2.combol. Text is the name of the selected server.

The above two methods can achieve the purpose of connection. The former is concise but has great limitations. when the name of the Data Server changes, it must be modified.Source codeIs inconvenient. The latter is both highly operational and intuitive. The second method is adopted in the technical file management system developed by the author.

Data upload

Data upload stores archive files in data tables. During data upload, there are two methods for different situations: single upload and batch upload. The former refers to uploading a file at a time (adding a record to the data table ), the latter means to upload all files in a folder to the database at a time. The two are essentially unified. during batch upload, you only need to use a loop statement. The following describes the process of a single upload.

1. Open a data table

Open the data table using the following statement:

Dim office_rst as new ADODB. recordset
Adocon. cursorlocation = aduseclient
Office_rst.open "office", adocon, adopendynamic, adlockoptimistic, adcmdtable

2. Add new records and upload files

Assume that you have obtained the path and name of the file to be uploaded through some operations (for example, you can use the commomdialog control) and saved it in the string variable filepath. The key statement for uploading is as follows:

Dim adotify as ADODB. Field, dataarr () as byte, filelen as long, file_num as long
Office_rst.addnew
Set adofld = office_rst ("Office ")
Adorst ("FILENAME"). value = filepath
File_num = freefile returns an integer that represents the next file number that can be used by an open statement.
Open filepath for binary access read as file_num 'Open the disk file
Filelen = lof (file_num) 'evaluate the file length
Redim dataarr (filelen) 'defines the dynamic array size based on the file length
Get sourcefile, and dataarr' read an opened disk file into an array variable.
Adotify. AppendChunk dataarr () 'stores the array content in an image field
Close file_num 'close Disk Files
Adorst. Update

Filename is a string field in the table office, used to store the name of the file.

Data download

After the database connection is completed, you can not only upload files to the database, but also download (Browse) files conveniently ). You can use the web browser control to browse various types of files.

1. webbrowser Control

Webbrowser control to applyProgramAdded the browsing function, which allows you to browse common file formats, such as office, CAD, BMP, and JEG. Applications that contain the webbrowser control can not only browse files on the local machine, but also files on the local area network or even on the World Network.

The webbrowse control has many methods, but only the "navigate" method is used here. The navigate method is to access this file through the full path of a file. The specific usage is:
Webbrowoff. navigate URL

Specifically, webbrowoff is a webbrowser control, and URL is a required string-type parameter. It can be a string used to specify the information location on the WWW Service Program on the Internet, it can also be a full path or the location and name of the file to be browsed using the general Naming Standard.

2. Download file data

In the program, you can use the datalist control to list the names of files saved in the database. The download idea is: first locate the record of the file to be downloaded, and then assign the file content to an array, and write a binary file. Finally, pass the file path and file name to the webbrowser control.

KeyCodeAs follows:

Dim filearr () as byte, current as string
Filelen = office_rst.fields ("Office"). actualsize 'to get the file data size in the office Field
Redim filearr (filelen)
Filearr () = office_rst.fields ("Office"). getchunk (filelen) 'writes data in the field to a dynamic array.
Bufferfile = freefile
Open "tempfile" for binary access read write as bufferfile 'open a temporary file
Put bufferfile, and filearr' write data in the dynamic array filearr to the temporary file tempfile.
Current = curdir & "\ tempfile" 'get the full path of the temporary file
Webbrowoff. navigate current 'display file content in the webbrowser Control

The undefined variables in the above Code are the same as those described above.

Conclusion

Currently, many organizations have a wide range of archive data types, including office documents, plain text, image materials, and AutoCAD files that contain basic topographic maps, current situation maps, and planning and design results maps, in addition, the data volume is huge and the number of files is very large. The methods described in this article can be used to conveniently view, upload, and download various database data, this makes it easier for organizations with large volumes of archival data to manage their data.