Storing filepath in database field ??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear all,

I am buidling a ASP 1.1 appliciation.

what is the best way to store file path in SQL server database field
Do I have to store the Physicalpath or only the file and then retreive the
full path name when needed ?

thnaks for all
regards
serge
 
Serge,

Think about your question and what you know about the files you would like
to refer to.

Not to belittle you, but a full path can become pretty long, especially if
it is a UNC. Web Query strings have a buffer size limit and you could risk
exceeding that with a full path. If all your files are stored in a common
folder, you could recieve a perfomance hit indexing and fetching files from
that source.

Each file path part has a known length limitation and would be easily
reconstructed given that each part was stored independantly.

Only you can answer the risk/reward tradeoff questions. I would recommend
that if there are to be a lot of files involved and/or multiple file
locations that you break up the file path and store each in a database or
file see example.

example:
File_Table
PathID int IDENTITY (1,1),
FileName Varchar(254) not null, --max file part len is 254 (NT Servers
can only have 16 char names so \\uncpath will always work)
ParentPathID int,
PropertyFlag int,
Size float(9),
LastAccessed datetime,
LastModified datetime,
CheckedOut datetime,
CheckedOutBy int,

File_Flags
Directory=2
File =4
Readonly =8

the path \\myfileserver\myfileshare\some directory\some nested
directory\some file.ext

file size is 150KB

becomes

1, \\myfileserver, Null, 6, 0, getdate(), getdate(), null, null
2, myfileshare, 1, 6, 0, getdate(), getdate(), null, null
3, some directory, 2, 6, 0, getdate(), getdate(), null, null
4, some nested directory, 3, 6, 0, getdate(), getdate(), null, null
5, some file, 4, 10, 150000, getdate(), getdate(), null, null


client request would be
user's userid is 15
http://myserver/myapp/getfile.aspx?myfile=5&UID=15

the data row would look like
5, some file, 4, 10, 150000, getdate(), 'Unchanged Date', getdate(), 15
 
hi,

Sorry I did not reaaly cacth what you means on the wa storing files. I
understand that path can be long and the limitation you mention.

The idea is that I have a web page where my user introduce some laboratory
measurement. To a particular mesurement they are able to attached a file.
When a particular measurement data is retrive I display those information on
a datagrid. Then I got it my to create dynamically a hyperlink to the content
of the file Field.

Ex:
All files will be uploaded in the virtual path .\SampleFiles folder
In a way or an other in order to properly open that selected file, I should
get the full path ??

thnaks for comments
regards
serge
 
One more precision.
The main idea is to provide from that databased field a direct link to the
file which wil be open in a new page. I do not redirect to an other aspx page

The way you store it sounds complicated for my simple application.
The only thing I woul like to perfom is hve a direct Href link to the
content of the database field.

An other possibility would be that I am storing in the database field only
the FileName. But then how to rebuild the full path of that file and attache
it to my Hyperlink column defined as follow in a datagrid server control:

<asp:HyperLinkColumn
Text="<img border=0 src=file.gif>"
HeaderText="File"
DataNavigateUrlField="DOC_LINK"
DataNavigateUrlFormatString="{0}" Target="_blank">
</asp:HyperLinkColumn>

Any ideas ?

thnaks for your help
serge
 
Serge,

You say the application is simple, however; if you go in with that approach
it will always just be simple. Changing out hardward, workflow, and growing
will be more difficult.

Weather you store the whole path, partial path, or just the file name is up
to you. I caution you to think about what could change in the future, will
the files always be stored in .\samplefiles, or could you eventually move
them to another file server or NAS device?

To answer your question "Do I have to store the Physicalpath or only the
file", the answer is you only need the file name if you will always know the
file path. "<a href=""./samplefiles/" & filename & """
target=""_blank"">Document</a>" where filename is the only item retrieved
from the SQL database.

The for that matter, you could just store the file itself in the SQL
database as a binary blob. Your link would look like "<a
href=""showdoc.aspx?DocID=" & DocID & """ target=""_blank"">Document</a>".

Sample showdoc.aspx

<%

dim b() as byte 'byte array of the file

....
'Logic to get the blob
'Open Connection
'Fill table where DocID = request("DocID")
table.rows(0)("DocID").Copyto(b,0,table.rows(0)("DocID").Length)
....

Response.Contenttype = "text/plain"

Response.BinaryWrite b
Response.Flush
Response.End

'%>
 
Hi thnaks for you reply I will think about both solution.
BUt based on that starting from the first solution you provide

as follow :the path \\myfileserver\myfileshare\some directory\some nested
directory\some file.ext

file size is 150KB

becomes

1, \\myfileserver, Null, 6, 0, getdate(), getdate(), null, null
2, myfileshare, 1, 6, 0, getdate(), getdate(), null, null
3, some directory, 2, 6, 0, getdate(), getdate(), null, null
4, some nested directory, 3, 6, 0, getdate(), getdate(), null, null
5, some file, 4, 10, 150000, getdate(), getdate(), null, null


client request would be
user's userid is 15
http://myserver/myapp/getfile.aspx?myfile=5&UID=15

the data row would look like
5, some file, 4, 10, 150000, getdate(), 'Unchanged Date', getdate(), 15
<<<<<

I did not catch the way you can rebuild and find the proper file to open ?
What is myFile= 5 and UID=15

how do you know those id's???

Sory for maybe dummies questions but I do not have experience on the best wa
to store sql data but now it start to learn

thnaks for help
serge
 
Back
Top