[AVReidy]

So a while back I created an application that allows the user to create different kinds of quizzes to test themselves. It works great, and unfortunately it has been an excuse for me not to code regularly anymore. Since school has started, I've had the chance to actually use it in a real application (which was a cool feeling - using a program I made to help me study). Using it to study made gave me some ideas on how to improve it, and it became obvious that I should allow the user to save their entire quiz in a database.

I don't know very much about databases, so I'm wondering how one actually goes about making a SQL database. The book I have teaches LINQ, but it doesn't even mention SQL. All of the examples show how arrays can be accessed like databases through LINQ. So, is a SQL database generated by writing some code and saving it as a certain file? I don't expect anyone to tell me the exact code or anything, but a broad overview of how creating and saving databases works would be great. What programs will I need?

[Martyr2]

Databases are software you have to install (except for MS Access which is file based but we will ignore that for the time being). Various databases like Microsoft's SQL Server, MySQL, Oracle etc are all pieces of software that you download/purchase, install and it runs as a service on your machine. Much like a web server runs on a computer to handle HTTP requests.

To get you started you can download (free) Microsoft SQL Server Express. Once you install this and it runs in the background, you can then access it with tools like SQL Server Management Tools. These tools will provide you the means of creating databases, setting up tables, linking them together, setting up users and their permissions on the database etc.

Once you setup your database, then you can open up visual studio and program applications that will interact with the database. These are done through objects like SqlConnection, SqlCommand, SqlDataAdapters, Datasets, DataTables etc. These are known as ADO objects (more info here). You can find them in visual studio by right clicking the toolbox, choosing items and from the checkbox list select them.

Once you add them, try dragging a SqlDataAdapter control onto your form and you will see the wizard to link your application to your database you created on your new SQL Server installation.

This is how it is done for other databases as well.

[AVReidy]

I don't understand why it's called a server. Just because it serves data to your application?

Also, when you download a computer game like Minecraft that saves your worlds, for example, are you downloading pre-made database files with it?

[Martyr2]

That is exactly why it is called Server. It is serving data records. It also does a lot more. SQL Server is pretty sophisticated and has things like backup utilities, user management, scheduling services etc. Express version is a bit of a cut down version of the professional version, but it will be perfect for you to design with. That is why they created it and made it free.

I am not familiar with Minecraft's saving abilities, but it looks like the save everything to files and don't use a database in the traditional sense. They put all the data in a file and read the file when they need info.



P.S. A video pointing you to the save files can be found here and how to load worlds just by loading the files.

This post has been edited by Martyr2: 18 September 2011 - 04:10 PM

[Curtis Rutland]

AVReidy, on 18 September 2011 - 05:55 PM, said:

I don't understand why it's called a server. Just because it serves data to your application?

Also, when you download a computer game like Minecraft that saves your worlds, for example, are you downloading pre-made database files with it?

It's called a server because instead of connecting directly to the file, you're connecting to the server and it's serving the data back to you.

There is such a thing as an "embedded" database, which doesn't require a server, or even an installation. Two examples: SQLite, and Sql Server Compact edition (still called server, but not really a server). These are portable databases that don't rely on servers, they just need a small DLL included in your project. Many applications intended to be distributed rely on something like this. Also, some mobile applications. Android uses SQLite, for instance.

[AVReidy]

So the SQL server is like a half way point that serves information from a file to an application?

I'm interested in using an embedded database because I really don't see the practicality of using a server unless it's for personal use on a single computer. What would be the reason to use a database server over an embedded database? I'm not clear on that.

[Curtis Rutland]

The database server is useful in enterprise applications. When you deploy a corporate application that accesses data, it's not going to be accessing a file on a share somewhere. It's going to connect to the "server", and the server is going to manage all the connections and serve all the data.

Also, the server lets you do things such Analytics, Reporting, and Integration services.

If you're distributing this app over the internet, and it's data is for it and only it, it's best to use an embedded DB, or some other mechanism for a save file.

If you're distributing this over a corporate intranet, it's probably better to use a single DB server.

[eclipsed4utoo]

You would use a database server if you had multiple applications connecting to the same database and you wanted to share data between them.

You would also use a database server if you had multiple PCs running 1 or more applications that you wanted to share data between.

You would use an embedded database if the information will be local to the current application and is not being shared to other PCs/applications.