Database Best-Practices for Beginners

Very good question indeed and you are certainly on the right track!

Being a computer engineer myself, databases and how to write code to interact with databases was also never a big part of my university degree and sure enough I'm responsible for all the database code at work.

Here's my experience, using legacy technology from the the early 90s on one project and modern technology with C# and WPF on another.

I'll do my best to explain terminology as I go but I'm certainly not an expert myself yet.

Tables, Objects, and Mappings Oh My!

A database contains tables but what really is that? It's just flat data related to other flat data and if you dive in and start grabbing things its going to get messy quickly! Strings will be all over the place, SQL statements repeated, records loaded twice, etc... It's therefore generally a good practice to represent each table record ( or collection of tables records depending on their relationships ) as an single object, generally referred to as a Model. This helps to encapsulate the data and provide functionality for maintaining and updating its state.

In your posting your Customer class would act as the Model! So you've already realized that benefit.

Now there are a variety of tools/frameworks (LINQ2SQL, dotConnect, Mindscape LightSpeed) that will write all your Model code for you. In the end they are mapping objects to relational tables or O/R mapping as they refer to it.

As expected when your database changes so do your O/R mappings. Like you touched on, if your Customer changes, you have to fix it in one place, again why we put things in classes. In the case of my legacy project, updating models consumed a lot of time because their were so many, while in my newer project it's a few clicks BUT ultimately the result is the same.

Who should know what?

In my two projects there has been two different ways of how objects interact with their tables.

In some camps, Models should know everything about their tables, how to save themselves, have direct shared access to the connection/session and can perform actions like Customer.Delete() and Customer.Save() all by themselves.

Other camps, put reading, writing, deleting, logic in a managing class. For example, MySessionManager.Save( myCustomer ). This methodology has the advantage of being able to easily implement change tracking on objects and ensuring all objects reference the same underlying table record. Implementing it however is more complex than the previously mention method of localized class/table logic.

Conclusion

You're on the right track and in my opinion interacting with databases is extremely rewarding. I can remember my head spinning when I first started doing research myself.

I would recommend experimenting a bit, start a small project maybe a simple invoicing system, and try writing the models yourself. After that try another small project and try leveraging a database O/R mapping tool and see the difference.

Your evolution is definitely in the right direction. A few more things to consider:

• Use prepared statements versus String.Format to bind your parameters. This will protect you from SQL injection attacks.
• Use the DBProviderFactory and System.Data.Common inferfaces to further disconnect your implementation from a specific database.
• After that, look at methods to generate your SQL commands and map data into objects automatically. If you don't want to jump into a big complex ORM, look for simple examples: ADO.NET ORM in 10 minutes, Light ORM library, or Creating an ORM in .NET. If you decide to go this route, you'll ultimately be better served by a mature library like the Entity Framework, Hibernate, or SubSonic.

My advice if you want to learn about databases, the first step is forget about the programming language, next, forget about which database you are using and learn SQL. Sure there are many differences between mySQL, MS SQLserver and Oracle but there is so much that is the same.

Learn about joins, select as, date formats, normalization. Learn what happens when you have millions and millions of records and things start to slow down, then learn to fix it.

Create a test project related to something that interests you, for example a bike store. See what happens when you add a few million products, and a few million customers and think of all the ways the data needs to relate.

Use a desktop app for running queries on a local database (sequel pro, mysql workbench etc) as it's much quicker than uploading source code to a server. And have fun with it!