Some Really Really Basic Questions

[funnyusername]

Hi folks,

I won't pretend to know anything about databases because I haven't started learning about them yet. But I have a few questions in case I shouldn't be wasting time learning about them!

I'm moving my program over to C# from VBA. It involves employee's, supplier's, consumable's, vehicle's, and a large number of various details for each item, and category, which interact together in different ways. For example one employees wages might need to be altered if he gets a raise, or used at various times by the program to determine charges.

Anyway, I was told to look at databases, so I've been taking a cursory look at SQL and XML. So far I understand that SQL is a language not an application, and it will allow my program to interact with databases.

However, can I create (Or delete) a new database file in C# dynamically if for instance a new category of consumables is added ?

Do I have to interact with a seperate program such as MS Access (For example), and if so, what if my programs user doesn't own MS Access ?

None of the books I've looked at about C# databases or SQL or XML begin from a basic enough starting point. How should I actually create, from nothing, a 2 dimensional database file which my program can then refer to with SQL ? I'm very proficient with MS Excel and have looked at Access a long time ago.

If you think databases are for me, can you recommend a good beginner book, because I've looked at some 'Beginning C# databases' etc on Amazon and they haven't answered the above questions?

Thankyou for any replies.

[Alsvha]

When talking about databases it is usually meant as an application which can store your data.
Access is a database application for example.
SQL Server, Oracle etc are database servers.

Some databases allow you to just use the database file without having the application itself, which for example Access could just to mention one (there are many others). I believe that SQL Server Express allows for the same, and is free as well on top of it.
So you do not create a database file dynamically from C#, but have a database file you access and use from C#.

SQL is the language you use to manipulate the databases and the information stored therein, and thus not a database in it self. Different databases normally use different variants of SQL all based on the same "foundation" but with some alterations. So some inbuilt function in Oracle or MySQL for example, doesn't work in Access of SQL Server and so on.

XML however is a structured file, and as such could be used for data storage, and it has benefits and disadvantages compared to a database.
One advantage is of course it is database independent, it can be easier to just read and alter manually then having to look up values via the database application and so on. So for small single/few user projects (applications or web applications) XML as data storage could indeed work fine.

However if the application needs multiple concurrent users, it would be worth considering databases. And depending on the number of users and amount of data, it would be worth it to start considering a real database server such as the SQL Server, MySQL etc.
They simply offer many benefits with datastorage and manipulation and concurrent user control (meaning data integrity).


But without knowing much of the scope (size, data amount, school/work etc) of your application, it is difficult to advice you entirely.

Hope this helps a little.

[funnyusername]

Thankyou Alsvha,

You mentioned more information..... :)

What we're talking about here is a single user program on a standalone PC. Therefore the database files would have to be distributed with the program, or if created dynamically would be specific for that user. There would be no online or network data storage, only local files on the installed PC.

In the program there are about 50 generic categories of consumable for example, each of which should be capable of storing data for 10,000 items (Date for each item = Item Name, Price, Stock Level, Auto-Stock Re-Order level, Item Supplier etc), but these individual items are user defined during use of the program, and not specified before distribution. Some categories may have no items stored, some may be nearly full, depending upon the individual user. I imagine that there will need to be a single 2 dimensional database file for each category.

There would be a file for storage of maximum 20,000 suppliers, and their details.

There also would be a file for storage of a vehicle fleet and its individual vehicle details.

There would be a file to store past sale details.

A file for customer details.

etc etc

Does this help you to help me a bit more? Thankyou, I appreciate the advice.

[dglienna]

Look on MSDN for C# and SQL. There are 101 Sample Apps that include how to access both Access, and SQL Server. If the users have Access, they won't have to buy it if you want to use it.

I suggest that you use SQL Express (which is free) that you include with the install program) along with the database structure (or temp tables)

While you can create data dynamically, and alter tables dynamically from your program (as well as do temp tables) you probably wouldn't want to have users creating a whole 'Initial Catalog' of info. You'd want to supply it, along with owner info, and security access info.

[davide++]

Hi all.

In addition to what was said (all right), I dislike using database dinamically created. If the user can create or alter tables, you will lose the control on your data model, and usually the performance will slow down. Instead it's quite easy to design generic tables which store user data; they are structured as "Field" plus "Values"; of course, they have some limitations.

[funnyusername]

Thanks folks. That helps a lot. I have VS Standard Edition now, and MS SQL came with it. Can I use this or will I still need to distribute something like SQL Express with my program.

I'm working on a new books also which covers some basic database interaction in Chapter twenty something. I'm on Chapter 6 and don't want to skip ahead! :D So it might help in a few days.

Thanks.

[dglienna]

I'm using SQL Express to develop an app for a client that has SBS2003 and SQL Server 2005. I just have to change the connection string in the .config file the first time I run it. (can't seem to figure out how to avoid that step)

Just means changing the server name.

[funnyusername]

Well, after looking into it more I think I'm going to try and work my program using a few hundred text files instead!

All I wanted was a way to save a file with columns and rows of data which could be recalled and edited in my C# code. I've realised that it isn't that simple if even possible, and I think I can't distribute a second program like SQL Express with my program.

So I'm investigating the possibility of having a single text file for every column of data, and indexing every line in every file with a number, which will correspond to the line numbers in every other text file. Not sure how its going to work yet, probably lots of loops to locate the equivalent data from each file as required, but I can't seem to get a better idea.

I reached the chapter in my book on databases, and it started like every other database book. It told me to download the sample database and proceeded to give instructions on working with it which is no use to me. We're talking the basics of the basics here, but I've looked on amazon and can't find anything suitable.

Basically I want a two dimensional array, but instead of the data being held in memory, I want it held in a file so its not lost upon program exit. Any ideas?

[davide++]

Well

Basically your requirement is to manage a certain amount of data; you need to perform some simple operation (insert, delete, change and search), so you think you can develope all this using flat file to store data instead of a database. Now you're thinking about advantages you can get using this way, for example you don't need to learn SQL, don't need to distribuite a third part application and so on...
OK, if the operations you're going to manage are simple it is no problem to write a program that read an write files, but you're talking about the possibility to add indexing, and pheraps later you will want to use joins between data (that means join between files).
The risk is that storing data, which probably is a few important part of your application, will be the harder part... and you will end up to rewrite a little DBMS, when there are many of them available.
I suggest to think back about the possibility to use a simple database, for example Access.