利用TEXTCOPY.exe 上传一个目录中的所有文件到数据库中表

上传多个文件到数据库Image字段 (作者:Muthusamy Anantha Kumar aka The MAK)

/*
翻译:邹黎鹏(zlp321002)
有疑问联系：[e-mail](作者根据自己工作需要稍微更改了一点批处理文件代码)

主要处理同一个目录文件入库:可以包括(Word,Excel,影象文件),直接插入数据库中表的IMAGE字段中.
表记录信息包括:
            文件标识、文件名称、二进制文件、上传客户机器名称、上传日期

/

这篇文章主要是介绍如何上传多个文件到数据库中的表,这里有很多方式,我介绍的是OSQL.EXE
使用TEXTCOPY.exe的方法

准备:
a: SQL Server 2000客户端必须安装在进行批处理的文件上
b: SQL 登录至少有DBO的权限

步骤一:

在"C:/"建立 C:/Upload 文件夹

步骤二:
把TextCopy.exe 拷贝到C:/Upload 文件夹里
TextCopy.exe 文件地址: /Program Files/Microsoft SQL Server/MSSQL/Binn

 

步骤三:
拷贝代码如下代码:
@ECHO off
cls
REM ECHO on
REM Objective: To upload all the files in the folder to SQL server as a table
REM Created by: MAK
REM Date: Nov 3, 2004
REM Contact: [e-mail]
REM Check parameters
if "%1"=="" Goto noparmErr
if "%1"=="/?" Goto Help

REM assign variables
set Sourcefolder=%1
set servername=%2
set Databasename=%3
set loginname=%4
set password=%5
set LogFile=%6

Date/t > %6
time /t >> %6

REM Export DIR listing to C:/upload/Dirlist.txt
dir %1  /b > C:/upload/Dirlist.txt

OSQL /S%2 /U%4 /P%5 /d%3 /Q"if exists (select * from %3.dbo.sysobjects where id = object_id(N'[Myuploadtable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table %3.dbo.[Myuploadtable] " >> %6

OSQL /S%2 /U%4 /P%5 /d%3 /Q"create table %3.dbo.Myuploadtable (id int identity(1,1),filename varchar(500),ImageFiles Image,UploadPeople varchar(200) default host_name(),UploadTime datetime default Getdate() )" >> %6

FOR /F "usebackq delims==" %%i in (C:/upload/Dirlist.txt) do OSQL /S%2 /U%4 /P%5 /Q"insert into %3.dbo.Myuploadtable (filename,ImageFiles) values('%%i',' ') " >> %6

FOR /F "usebackq delims==" %%i in (C:/upload/Dirlist.txt) do c:/upload/textcopy.exe /S%2 /U%4 /P%5 /D%3 /Tmyuploadtable /CImagefiles /F"%1%%i" /I /W"where [filename]='%%i'" >> %6

GOTo END2

:noparmErr
Echo Usage help: example as follows
echo ...
Echo Objective: To upload all the files in the folder to SQL server Table
Echo Created by: MAK
Echo USAGE:
ECHO ....
ECHO ....
Echo %0 SourceFolder Servername Databasename Login Password LogFile
Goto END2

 

:Help
Echo Usage help: example as follows
echo ...
Echo Objective: To upload all the files in the folder to SQL server Table
Echo Created by: MAK
Echo USAGE:
ECHO ....
ECHO ....
Echo %0 SourceFolder Servername Databasename Login Password Logfile
Goto END2

:END
Echo "Compare Query results Completed" >>%LogFile%
date/t >> %LogFile%
time/t >> %LogFile%
goto END2

:END2

把代码另存在: C:/Upload/Upload.bat文件

步骤四:

执行批处理文件

步骤五:
使用正确的参数,然后执行这个批处理文件,如果正确执行了参数,这个批处理文件将被有效OSQL 建立[Myuploadtable] 表在给定数据库中,然后用同样的OSQL 有效的插入记录到表.记录数和源文件是一样的,表中文件名称是导入文件名称它将调用TEXTCOPY 有效和上传没一个文件在源文件夹到SQL Sever数据库表.

 

使用方法:
upload C:/sz/sz/ zehuadb SZ zlp zlp x.log

Parameters           Explained
Upload              - Batch File Name
C:/myfiles/         - Folder where all the source files are stored
SQL                 - SQL Server instance name where all the files are going to be uploaded
MyDB                - Database Name where all the files are going to be stored.
sa                  - Login name of SQL Server box
yeahright           - Password for the login sa
x.log               - Log file where all the process information is stored while executing this batch file

 

Select fileName from Myuploadtable.
==========================================
filename
0123StcdInfoQuery.htm
AddBaseData-1.htm
AddBaseData.htm
AddItemData.htm
AdminUserMentenlence.htm
default.html
default1.html
DeleteBaseData.htm
DeleteItemData.htm
DepartmentConfig-1.htm
.......

select * from Myuploadtable
=========================================
1 0123StcdInfoQuery.htm <Binary> ZLP 2005-11-23 20:20:43
2 AddBaseData-1.htm <Binary> ZLP 2005-11-23 20:20:44
3 AddBaseData.htm         <Binary> ZLP 2005-11-23 20:20:44
4 AddItemData.htm         <Binary> ZLP 2005-11-23 20:20:44
5 AdminUserMentenlence.htm<Binary> ZLP 2005-11-23 20:20:44
6 default.html         <Binary> ZLP 2005-11-23 20:20:44
.....................................................

步骤六:
更改表名:
sp_rename 'Myuploadtable','Prod_ImageTable'

当你再次运行导入时,它将删除[Myuploadtable]表,并重新建立该表,这就是我更表名的原因
导入文件的时候也将建立一个日志文件如下:
2005-11-23 星期三
20:30
（1 行受到影响）
（1 行受到影响）
（1 行受到影响）
..........
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'C:/sz/sz/0123StcdInfoQuery.htm'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'C:/sz/sz/AddBaseData-1.htm'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
..........
和一个Dirlist.txt文件,该文件主要记录导入文件记录名称
0123StcdInfoQuery.htm
AddBaseData-1.htm
AddBaseData.htm
AddItemData.htm
.........
结论:
这篇文章主要介绍如何把客户端某一个目录下的所有文件直接上传到数据库,不需要在服务器端生成文件