Hi everyone,
I have a really (long) general question here that i hope some of you can help me out with. Its a pretty important question because it will determine how i will structure my project. Its regarding the use of files and databases in my project, and which is better to use and why. First a little background info on what ive done so far in my project so you know my situation.
My application is going to draw on a lot of tabulated data and it will also be saving, editing and loading data regularly. I want to save calculations i make in the program, save the currently current state of a number of textboxes/comboboxes and save user settings as well as other things. So basically imagine a form with a number of comboboxes annd textboxes, and also a listbox. I want the user to able to press 'Save' and all the currently entered items in my textboxes as well as the selected items in my comboboxes will be saved. The user can then select an item from the listbox that will load these values back into the respective controls when clicked.
Currently im using SQL databases in my project and thats it. The problem is, SQL files are big and i will have a lot of them so it will significantly increase the size of my project. Im also having a slight problem with SQL thats rather annoying. When you run my application for the first time after you start your computer.. it has trouble peforming an operation that involves SQL for the first time. Its like it takes too long for SQL to load that first time and throws a SQL exception. After you restart the application though it has no problems. Another reason why SQL doesnt appeal to me is that the end user of my application will need SQL in order to use my program... SQL isnt free (at least for longer than a year). Anywayz im getting sidetracked. I think its pretty easy to use SQL databases to do what i was mentioning above and they would work well but perhaps files are better to use
When you look in the directories of most programs on your computer, you can see that there are many files and folders with different extensions (such as *.dat and *.txt etc). and whatnot. When you read the VB help on files and data storage, they only really reference 2 types: databases (SQL and Access) and text files. So whats the story then with the *.dat files and all those you see in other applications Whats the difference between a *.txt file and a *.dat file Is there a difference Are they only given different extensions for organizational purposes or is it nessacary to give these files the proper extension
The reason the use of files appeals to me, is that you have more control of your project. VB 2005 does a lot of stuff automatically for you and if your using SQL databases (like i am) you cant really choose how you program files are going to be setup when you publish. In fact you dont really have much control of the publish project procedure at all (that i can see at least.. im sure theres a way to do it but i dont know how). Theres just a lot of ugly files with annoying names that dont really give your project a professional look and makes everything look messy. I want to be able to designate folders that i want my files stored in (such as a system folder for system files, a save folder for saved files, a data file for my data files etc etc).
Ok so in summary what im asking is this:
If i use SQL for my project... how do i get around that exception that gets thrown when SQL loads up for the first time What are my options in terms of publishing my program to multiple users who dont have SQL
If i use data files for my project... whats the difference with the files extensions and which one do i use and where. Can i use files for the applications i mentioned above... surely it would be easier to bind those controls to a database and use tableadapters etc to do what im asking
Obviously i will use both types of data storage in my project but i would rather use the most efficient form of data storage for the applications i need them for.
Sorry for the novel but i had a lot ask! ;)
Cheers
Aaron
Databases vs Files... which ones best?
MMansour
Some comments:
1. I've never had an issue with SQLServer causing issues - what version are you running
2. a file is a file is a file - extension is irelevant: not strictly true - it essentially tells the operating system what to do with the file.
3. Multiple SQL Databases It sounds like you don't understand the purpose of SQL Server: sure, there are good reasons for multiple databases, but it doesn't sound like you're using them appropriately. SQL Data Storage is useful for huge clumps of data which are related to each other in some way. Additionally, you may need to access that data randomly (SQL Server does more than that, of course, but that's the basics).
4. If your SQL Databases are big then any other file you use will be big.
5. What is 'big' 100Kb 1Mb 10Mb 1GB
6. You can save your data in a file in any format you like, with any extension you like.
7. If you just 'load' and 'save' data, then a regular file may be most appropriate.
8. Why do you say "SQL isn't free (At least for longer than a year)": what version of SQLServer are you using SQLServer 2005 Express is free - with no time restrictions - and is essentially the replacement for MSDE. I use it extensively.
10. Suck it and see - try using some kind of file system and see what happens. You won't know what's appropriate unil you try it.
Jumpsteady
Hey thanx for the reply guys.
First off i was originally using VB2005 Express and SQLServer 2005 Express. I bought Visual Studio 2005 SE the other day so now im using that i.e. im using SQL Server 2005. I still have that same problem though. I dont really have that problem on this computer (my home PC) much but i still get it occasionally. I have a pretty good PC at home (3.0gHz P4, 1g RAM) but the computer i was having the most problems with only runs a 1.5gHz Celeron, 256mB RAM. So that might be the problem.. ill have to read the SQL hardware requirement specs.
Yeah i was wondering whether it was better to create multiple databases or just one big one with lots of datatables in them, so it sounds like the latter is the go from your response above. OK by big i mean roughly 2Mb.. i know thats not really a big file but in comparsion to a text file that has the same amount of data in it (which would probably be about 10kB in size) thats a very big difference. I assume the difference in size is because there is a lot of code that goes into creating a SQL database and its connections settings etc so thats where the larger size is coming from.. i dont know.
I can save and load data easily enough with files but its the editing that i think would be difficult. Like what i was saying in my previuos post... i really need to save the state of the controls on my form so say if you load a saved set of 'control states', the controls update accordingly. But what if when i load this data and then change some controls and try to save and update this new configuration Is that hard to code I really dont have much experience with using files but i would prefer to use them.
I think i might try what Jensen was suggesting with the XML. That could be a better option.
Thanx again guys for the suggestions
KrankyKat
I'm not really sure this directly relates to what you asked, but I share the experience here in case it helps anyone reading.
I used to think that a filesystem was just as good or better for storing simple data than a database. After all, by using files in a filesystem you can indicate information hierachy, relations (in a limited way) and hold metadata in filenames or attributes and actual data inside the files themsleves. Right
Then came Freedb.
If you have a copy of Nero (that well known CD/DVD burning and ripping package) one of the features that it offers is to auto-recognise CDs. This is based around a database called FreeDB. This uses a filesystem hierachy to segregate the CDs into genres and then into artist, then albums, then a file containing the track list. Now, I remember using FreeDb many years ago when it first started and saw their approach as a confirmation of my own belief in this method. In those days it worked well - though it knew about relatively few CDs.
Last year I found that Nero wanted to download the latest version of Freedb (now containing - I think - around 1.5 million CD details). This came as a ZIP file of about 200Mbytes or so. On my 2.8Ghz processor (1Gb memory) SATA disc system it took WINZIP about 70 minutes to unpack the Zip file into the filesystem. The unpacked filesystem was a huge maze-like set of files many levels deep, with many folders containing huge numbers of entries.
Then, in order for Nero's Rip-to-Wave facility to work properly, Nero wanted to walk through the fileset and compile its contents into a database. This little task took a solid 25 hours, with disk thrashing all the way through - no dark time on the disc activity light at all. This was not just on my system, the Nero website warns that this build would take a LONG time! In addition to this, until I deleted the file hierachy representing the raw Freedb database, my system took about 8-9 minutes to reboot (I think system reboot time on XP is always related to complexity of the filesystem contents - so this was scary but not surprising
)
So, having allowed Nero to build a proper database to work with (that auto-recognise bit now works fine by the way) I removed the raw "filesystem database" Freedb (that took about 15 minutes too) and stopped believing that large data sets could be usefully accomodated in a filesystem.
All the best.
Alan T
H.w.Hendrick
It sounds to me like you don't really need a SQL database to store your data. It sounds like you are really just storing configuration settings.
If I were you, I would try storing your data in an XML file.
The DataSet has ReadXml and WriteXml methods that will make reading and writing to the XML file a breeze for you. You will still be able to use the DataSet like you would if you were getting your data from a SQL database.