|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Storing filepath in database field ??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 Show quote "serge calderara" <sergecalder***@discussions.microsoft.com> wrote in message news:84D8F5A0-3F27-4CC7-8898-28182BCE5866@microsoft.com... > 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 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 Show quote "AMDRIT" wrote: > 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 > > > "serge calderara" <sergecalder***@discussions.microsoft.com> wrote in > message news:84D8F5A0-3F27-4CC7-8898-28182BCE5866@microsoft.com... > > 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 > > > 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 Show quote "AMDRIT" wrote: > 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 > > > "serge calderara" <sergecalder***@discussions.microsoft.com> wrote in > message news:84D8F5A0-3F27-4CC7-8898-28182BCE5866@microsoft.com... > > 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,
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 Show quote "AMDRIT" wrote: > 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 > > '%> > > >  |
|||||||||||||||||||||||
Other interesting topics