How to store/retrieve image/file in sql db

Hi Guys,
I want to employee details in one table along with his photograph and resume
.
How can I achieve this? I'm fresher and I don't have any basic idea about th
is.
Can somebody lead me to a worked out example.
If appropriate how to use WRITETEXT/READTEXT to achieve this task?
thanks in advance.
M.L.SrinivasYou can start here:
http://support.microsoft.com/kb/308042
or
You can use TextCopy.exe in the \bin folder.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
news:9fa72100.0502102147.1a18787@.posting.google.com...
> Hi Guys,
> I want to employee details in one table along with his photograph and
> resume.
> How can I achieve this? I'm fresher and I don't have any basic idea about
> this.
> Can somebody lead me to a worked out example.
> If appropriate how to use WRITETEXT/READTEXT to achieve this task?
> thanks in advance.
> M.L.Srinivas|||I have gone threw this tutorial. But when I retrieve the image from the DB.
The image is only 1B, and I cannot open it. The filename is correct.
Whats wrong here?
-- Kenneth
"oj" wrote:

> You can start here:
> http://support.microsoft.com/kb/308042
> or
> You can use TextCopy.exe in the \bin folder.
> -- OJ: TEXTCOPY example
> -- Loading files into db &
> -- exporting files out to folder
> --
> --
> --TEXTCOPY IN
> --
> --create tb to hold data
> create table tmp(fname varchar(100),img image default '0x0')
> go
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass'
> --specify desired folder
> set @.path='c:\winnt'
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> --insert filenames into tb
> insert tmp(fname)
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> go
> select * from tmp
> go
> --
> --TEXTCOPY OUT
> --
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass,'
> --specify desired output folder
> set @.path='c:\tmp'
> set @.sql='md ' + @.path
> --create output folder
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> exec master..xp_cmdshell @.sql
> go
> drop table tmp
> go
>
> --
> -oj
>
> "M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
> news:9fa72100.0502102147.1a18787@.posting.google.com...
>
>

How to store Resume and cover letter into SQL Server?

Hi,

I am trying to create an asp.net web recruiting application for HR, which will give the users the ability to both copy/paste the Resume and cover letter in textbox and upload resume and cover letter, then submit it (which will be saved into SQL Server 2000 table).

I am thinking to save Resume and cover_letter as Image data type columns in SQL Server.

. Can someone give me a direction about how to save the uploaded resume and cover letter to table and if it's the easiest way to do it?

. What to deal with different formats of uploaded resumes? I hope to limit to only Word or HTML

. Since I also give user another option - copy/paste the resume and coverletter into a textboxes. Can I simply save the copy/paste resume and cover letter into text field column? Later, say, if any HR recruiter retrieve the text from database, will it concatonates everything together without line break?

Any ideas is appreciated.

You have two options use NChar 4000 or NVarchar 4000 and limit the size of the uploaded file so you can use ANSI SQL %Like% in your search or use NText and use Full Text index which is an add on to SQL Server dependent on Microsoft Search service with the Microsoft Catalog populated all the time to get search results. Full Text index uses Microsoft proprietry key word search as CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE. Run a search for Full Text index implementation in SQL Server BOL(books online) Hope this helps.|||

You can save the resume in the database in an image field

//Create a Buffer Stream to Hold the uploaded file
string FileName = GetFileName(txtFileUpload.PostedFile.FileName.ToString());
string FileType = txtFileUpload.PostedFile.ContentType.ToString();
int FileSize = int.Parse(txtFileUpload.PostedFile.ContentLength.ToString());
byte[] DocBuffer = new byte[FileSize];
Stream objSream;
objSream = txtFileUpload.PostedFile.InputStream;
objSream.Read(DocBuffer,0,FileSize);
//Pass this buffer as a paramter to a Stored procedure which will insert a new record into ur table
SqlParameter pcvContent = new SqlParameter("@.cvContent",SqlDbType.Image);
pcvContent.Direction = ParameterDirection.Input;
pcvContent.Value = cvContent;
dbCommand.Parameters.Add(pcvContent);
//======================================================================
to retrive the saved file from the database you have to do the following
do your select statment
Response.AddHeader("Content-Disposition","attachment; filename='"+ dr["FileName"].ToString() +"'");
Response.ContentType = dr["FileType"].ToString();
Response.BinaryWrite( (byte[]) dr["CvContent"]);
//=========================================================================
this the first solution.
The secound one is to create a resume builder.

|||Personally, I would consider storing BOTH the raw document in an IMAGEcolumn, and ripping the text of the document and storing that in a TEXTcolumn. This will allow you to set up a full-text index forsearching the resumes, while at the same time making it easy for usersto get the resumes back in whatever format they were submitted in.

|||

hi,

I need to do a keyword search in word and pdf documents. I tried using Index Server linked with SQL Server but its giving me many problems. So i'm planning to rip off the text from the word or pdf docs and store the text in a TEXT field as u said in one of the threads so that i can do a search easily.

Is it possible to read from a word or a pdf file and store it in a string. Sorry about contacting u directly

Thanks in Advance