Filesystem > SQL-E
-
This is my first post here, and my first try to ask this question ANYwhere...geez I hope you guys can help me with this...here goes:
On my PC, I collect a lot of text files. i copy and paste articles from the net, and then serve them up to my friends ASP.NET style (ie. storing links to the files in my SQL Express DB, then using ASP.NET Gridview to display the DB). They don't have copy permissions, just Read Only. After 120 days, I delete them to make room for the new ones...rotating system almost like a news reader.
Since this data changes every few days, here's what I do. I make a file, then make an entry in my SQL DB. Then if I move it to the "Old" Folder, I edit that entry in my DB. Then when I delete it, I delete it from the DB. Multiply that times 300-350. That's ridiculous, but I really like doing this for them. It just is VERY burdensome under this (above) system.
What I am looking for is, and thence my question: How can I enumerate the contents of a drive (or folder(s)) and then import that into SQL. That would make life so easy as a two-step process...edit everything on my file system that I want changed, then enumerate my drive/folder(s), then import it into SQL! Wow...that looks nice in print.
Can I cut the middle man out? (for example, is there any script (like a batch file I could write) that would just do both of these things at once in command-prompt? Wow, click one file and get an sql database of your drive...I know that can't be possible, it sounds like a dream. But I know SQL has a command prompt interface, so impossible might not be the word.
Is <whisper>xml</whisper> the answer? Directory Listing to XML, and then import the XML into SQL Express? wow, that's two languages not only to learn, but to learn how they work together...i can muddle through them individually, but ...wow...together...wow.
I ask all this with open ears and will patiently try ANY recommendations (that have to do with my question...lol), and I'm sorry for using up so much real estate with this post.
I know this sounds like i'm a total newbie, but i'm no so much...i'm just trying to question my design techniques and see if there is a different, easy, more simple and elegant way to do this....and well, sometimes the best questions are those asked out loud.
Thanks, cory
-
Hi Cory,
Everything is possible - nothing is impossible
.To enumerate the contents of a drive (or folder) from SQL Server you can use the following extended procedure:
exec master.sys.xp_dirtree 'c:\',1,1
The first parameter accepts a path to a folder. The second parameter provides the depth you want to go to and indicates that you only want to enumerate files and folders in the given directory. If you'd used a value of 2 you would enumerate files in the given folder and all of its folders. The third parameter, indicates that you want to pull out file names as well as folders. It also provides a column indicating whether the object is a folder or a file.
importing this into SQL is pretty easy too.
- create table #imported_files
- (
- subdirectory varchar(256),
- depth smallint,
- file bit
- )
insert into #imported_files exec master.sys.xp_dirtree 'c:\',1,1
Once you've got the relevant data in the temporary table you can then do whatever you want with it and drop the temporary table once you're done.
Hope that helps,
-
i'm going to try this today. you wouldn't believe it if i said i did the happy dance when i saw there was a reply here...
if i can get this to work, then it's worth it's weight in gold to me. thank you SO MUCH for taking the time to post a reply to me.
as far as "after temporary tables you can do what you want with it", i don't know the difference between temp tables or otherwise, but i'm going to learn. IF i can't figure something out, THEN i'll come back and ask another question, but i really want to understand this, not just copy and paste your (graciously provided) code, so i'm going to study for a while.
thanks again.
i'll let you know how it worked out for me...
cory
well...i'm back. step one for my adventure IS to copy and paste your code and then begin learning how it does what it does, but i get the message:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'file'.
I know that it's my fault, but I didn't really know WHERE to run this info, so i ran it as a New Query... and got that error. I guess I need help again.
please and thanks. -
Pablo,
Sorry, that's my fault.
"file" is a reserved keyword, hence the syntax error. Try this instead.
create table #imported_files
(
subdirectory varchar(256),
depth smallint,
isfile bit
)
insert into #imported_files exec master.dbo.xp_dirtree 'c:\',1,1
Once you're done processing the contents of the table you should drop the temporary table.
drop table #imported_files
To find out more about temporary table has a look for the following chapter in Books Online, "Creating and Modifying a Table". It explains all about temporary tables in there.
-
So, I've been experimenting with this for over a month now, because it seems so tantalizingly close to my needs. It does everything you said it would, which is incidentally everything I asked for! But at the time, I didn't know to ask the follow-up question: Is is possible to read this in to the database in a relational model?
I've explored everywhere for this solution! I think it's a matter , again, of not knowing exactly which keywords I'm looking for: for example, it took me forever to find out about "shredding" xml files. So I wondered if this was the answer...import the filesystem listing into a temp table, and then generate xml from that, and then shred the xml back into a relational model?
Or just import the filesystem into a NON-temp table, and use that to generate a dataset in my asp.net application and manipulate it in code? What I'm wanting is a drill-down style app where a user clicks on a date, gets the articles listed for that date, and clicks on an article to read it.
That's what I have right now, but it's just too cumbersome to manually edit the database every time. Right now, I'm storing dates in one table, tying them to a foreign key in a Folders table, and then tying THOSE to foreign keys in an Articles table.
Your above option is wonderful for getting my filesystem into a db table in one step, but there is no relational model to this (that I can discern) to allow the drill down technique...
An example of what I get from the extended procedure is:
'the actual main level date directory
'the subfolder of articles
'the actual files
etc.
But there are many Folders of depth=2 isfile=0 named Political Articles, and many articles of depth=3 isfile=1 names article 1.txt, so how can I manipulate this hierarchical model into a relational model, so a user can click on 09/13/2006 and get all the Folders actually attached to that date? Remember that I'm wanting to reduce the hand-editing in the database here, or eliminate it entirely...I want to be able to list my filesystem into a database (relational hopefully) to realize the drill-down technique...
BTW, I'm completely open to ANY suggestions. If the problem here is that I need to restructure my filesystem storage techniques so that the above extended procedure imports into the database in a better structure, so be it. If I need to use this only to be able to generate xml from it, and then manipulate that into my asp.net project, then so be it. I just have been researching in circles and getting no closer to my goal: Worry about Content More, Editing Less.
Please help again! And thank you...
-
It looks like I just might bypass SQL Server completely and use the ASP.NET Filesystem Objects....I'll let you know how my adventures go...Still looking for answers to the above post just in case my new adventure goes sour!
Thanks
-
Hi,
I see what you're trying to do. You basically want one extra column that tells you which is the parent directory to any given file/folder, if I'm not mistaken.
There is a structure to the output of xp_dirtree in that the parent of a given file/folder always lies above that file or folder. So if you added an identity column to your table and you had the following file in your output:
Id Subdirectory Depth IsFile
--------------------------------------
10 SomeFile.txt 2 0
You could get that file's parent directoy with the following query:
select subdirectory
from #temptable
where id in (select max(id) from subdirectory where id < 10 and depth = 1)
In fact, you could go one further and do this on the entire table as follows:
create table #temptable
(
id int identity,
subdirectory varchar(1000),
depth int,
isfile bit,
parent int
)
insert into #temptable (subdirectory, depth, isfile)
exec master.sys.xp_dirtree 'c:\',2,1
update #temptable
set parent = (select max(id) from #temptable where id < t.id and depth = (t.depth - 1))
from #temptable t
I'm not sure whether this works out better than using the ASP.NET filesystem object but it possibly gives you another option to experiment with.
Hope that helps,
-
Well, just for an update, the ASP.NET FileSystemInfos did the trick for me completely. In this case, I mean, in which all I wanted was a drill-down hierarchy of my File System that was web-based and "business logic"-ally manipulable.
However, in launching a new app, I really DO need all the data-manipulation abilities provided by databases...and well, I love databases, just not the really large ones!
so thank you for the above reply, and I will try it out!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply