Today, I will introduce a way to use stored procedures to save and read the field ntext and image field. In ASP.net, how should we call the stored procedures?
Firstly, create the procedure:
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
‘textcopy /S ‘ + @srvname +
‘ /U ‘ + @login +
‘ /P ‘ + @password +
‘ /D ‘ + @dbname +
‘ /T ‘ + @tbname +
‘ /C ‘ + @colname +
‘ /W “‘ + @whereclause +
‘” /F ‘ + @filename +
‘ /’ + @direction
EXEC master..xp_cmdshell @exec_str
2. And then, create the table and initialize the data.
create table tablename (NO int,image columnname image)
go
insert tablename values(1,0x)
insert tablename values(2,0x)
go
3. Read in the data.
sp_textcopy ‘yourservername’,’sa’,'yourpassword’,'libname’,'tablename’,'imagecolumnname’,'c:\image.bmp’,'where NO=1′,’I’ –Attention is NO=1sp_textcopy ‘yourservername’,’sa’,'yourpassword’,'libname’,'tablename’,'imagecolumnname’,'c:\bb.doc’,'where NO=2′,’I’ –Attention is NO=2
go
4. Read out the data into a file:
sp_textcopy ‘yourservername’,’sa’,'yourpassword’,'libname’,'tablename’,'imagecolumnname’,'c:\image.bmp’,'where NO=1′,’I’ –Attention is NO=1sp_textcopy ‘yourservername’,’sa’,'yourpassword’,'libname’,'tablename’,'imagecolumnname’,'c:\bb.doc’,'where NO=2′,’I’ –Attention is NO=2
go