Best Database Practices for Beginners

Question

So, I'm a fairly new programmer working towards a bachelor's degree in Comp Sci with very little experience. When searching for internship assignments for my program, I noticed that what I heard from several professors - "working with databases is 90% of all modern assignments in the field of computer science" - it seems that this is true. However, my program does not actually have courses with databases until the 3rd year, so I try to at least learn something about myself on average.

I have seen very little on SO and on the Internet in general for someone like me. There seem to be tons of textbooks on mechanics on how to read and write data in a database, but little about the relevant best practices. To demonstrate what I'm talking about, and to help solve my actual question, here's what you can easily find on the Internet :

public static void Main () { using (var conn = new OdbcConnection()) { var command = new OdbcCommand(); command.Connection = conn; command.CommandText = "SELECT * FROM Customer WHERE id = 1"; var dbAdapter = new OdbcDataAdapter(); dbAdapter.SelectCommand = command; var results = new DataTable(); dbAdapter.Fill(results); } // then you would do something like string customerName = (string) results.Rows[0]["name"]; } 

And so on. This is pretty easy to understand, but obviously full of problems. I started with code like this and quickly started saying things like "Well, it seems silly to have SQL everywhere, I have to put all this in a constant file." And then I realized that it’s stupid to have the same lines of code everywhere and just put all this with connection objects, etc. Inside the method:

 public DataTable GetTableFromDB (string sql) { // code similar to first sample } string getCustomerSql = String.Format(Constants.SelectAllFromCustomer, customerId); DataTable customer = GetTableFromDB(getCustomerSql); string customerName = (string) customer.Rows[0]["name"]; 

It seemed like a big improvement. Now it's super-easy, say, to switch from OdbcConnection to SQLiteConnection. But this last line, data access, still seemed awkward; and it’s still painful to change the name of the field (for example, moving from "name" to "CustName" or something else). I started reading about using typed datasets or custom business objects . I am still confused by all the terminology, but decided to look into it anyway. I believe it is foolish to rely on the brilliant Database Wizard to do all this for me (as in related articles) before I really find out what is happening and why. Therefore, I myself took a hit at him and began to receive such things as:

 public class Customer { public string Name {get; set;} public int Id {get; set;} public void Populate () { string getCustomerSql = String.Format(Constants.SelectAllFromCustomer, this.Id); DataTable customer = GetTableFromDB(getCustomerSql); this.Name = (string) customer.Rows[0]["name"]; } public static IEnumerable<Customer> GetAll() { foreach ( ... ) { // blah blah yield return customer; } } } 

to hide the ugly contents of the table and provide strong typing, allowing the external code to just do something like

 var customer = new Customer(custId); customer.Populate(); string customerName = customer.Name; 

which is really nice. And if the Customer table changes, changes in the code should happen only in one place: inside the Customer class.

So, at the end of all this incoherent, my question is this. Has my slow evolution in database code occurred in the right direction? And where am I going next? This style is well suited for small databases, but when there are many different tables, writing all these classes for each of them will be painful. I have heard about software that can generate this type of code for you, but I'm still confused by the DAR / ORM / LINQ2SQL / etc jargon, and these huge software products are overwhelming. I'm looking for some good, not overwhelmingly complex resources that can point me in the right direction. All I can find on this subject is complex articles that run through my head, or articles that just show you how to use point-and-click wizards in Visual Studio, etc. Also note that I'm looking for information about working with databases in code, and not information about design / normalization of the database ... there is a lot of good material.

Thanks for reading this gigantic wall of text.

Answer 1

A very good question, and you are definitely on the right track!

Being a software engineer himself, databases and code writing methods for interacting with databases were also not a big part of my university degree, and I am sure that I am responsible for all the database code at work.

Here is my experience using outdated technologies from the beginning of the 90s on one project and modern technologies with C # and WPF on another.

I will do my best to explain the terminology while I go, but of course I am not an expert yet.

Tables, Objects, and Mappings Oh My!

The database contains tables, but what really? This is just flat data associated with other flat data, and if you dive in and start grabbing things, they will soon become messy! Strings will be everywhere, SQL statements are repeated, records are loaded twice, etc. Therefore, it is usually recommended to represent each table record (or a collection of table records depending on their relationship) as a single entity, usually referred to as a model. This helps to encapsulate data and provide functions for maintaining and updating state.

In your publication, your Customer class will act as a Model! So, you already understood this benefit.

Now there are many tools / frameworks (LINQ2SQL, dotConnect, Mindscape LightSpeed) that will write all your model code for you. They ultimately map objects to relational tables or O / R collation, as they reference it.

As expected, when you change your database, your O / R mappings. Like you, affected, if your Client changes, you have to fix it in one place, again, why do we put things in classes. In the case of my old project, updating the models took a lot of time because there were so many, while in my new project it was a few clicks, but in the end the result is the same.

Who should know what?

In my two projects, there were two different ways of interacting objects with their tables.

In some camps, models should know everything about their tables, about how to save themselves, have direct access to the connection / session, and perform actions such as Customer.Delete() and Customer.Save() themselves.

Other camps set reading, writing, deleting, logic in the control class. For example, MySessionManager.Save( myCustomer ) . The advantage of this methodology is that it makes it easy to track changes in objects and ensure that all objects refer to the same base table record. However, its implementation is more complicated than the method of mentioning previously localized class / table logic.

Conclusion

You are on the right track, and in my opinion interacting with databases is extremely useful. I remember how my head was spinning when I first started doing research.

I would recommend experimenting a bit, launching a small project, perhaps a simple billing system, and try writing models yourself. After that, try another small project and try to use the O / R database mapping tool and you will see the difference.

Answer 2

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

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

Answer 3

My advice is if you want to learn about databases, the first step is to forget about the programming language, then forget about which database you use and learn SQL. Of course, there are many differences between mySQL, MS SQLserver and Oracle, but there are so many that one and the same.

Learn about associations, select dates, normalization as formats. Find out what happens when you have millions and millions of records, and everything starts to slow down, and then learn how to fix it.

Create a test project related to something that interests you, for example, a bicycle store. See what happens when you add a few million products and several million customers and think about how this data should be related.

Use a desktop application to run queries in the local database (continued pro, mysql workbench, etc.), since this is much faster than downloading the source code to the server. And enjoy it!

Answer 4

IMHO, you are definitely going in the right direction to really enjoy working with supported code! However, I'm not sure that the approach will scale to a real application. Some considerations that may be helpful.

• While the code you write will be really enjoyable to work and truly supported, it includes a lot of work up, this is part of the reason why wizards are so popular. They are not the most pleasant things to work, but save a lot of time.
• A query from a database is just the beginning; Another reason for using typed datasets and wizards in general is that in most applications, users at some stage are going to edit your information and send it for updating. Single records are great, but what if your data is best represented in a standardized way with a hierarchy of tables 4 in depth? Writing code to automatically create manual update / insert / delete instructions for all calls that are infernal, so tools are the only way forward. printed DataSets will generate all the code to perform these updates for you and have very powerful features to handle disabled (for example, client-side) updates / rollbacks of recent changes.
• What the last guys said about SQL injection (which is a major event in the industry) and protecting themselves using the DBCommand object and adding DbParameters.

In general, there is a really big problem when switching from code to databases, called impedance mismatch . Bridging the gap is very difficult, and so most industries rely on heavy lifting tools. My advice would be to try to master the wizards - because while going through the wizard is not a skill test, learning all of their flaws / mistakes and their various workarounds is a really useful skill in the industry and will allow you to move on to more advanced management scenarios faster data (for example, the disabled update of the table hierarchy with 4 depths, which I mentioned).

Answer 5

If you are a little afraid of things like Linq to SQL and Entity Framework, you can step halfway between them and explore something like iBATIS.NET. It is simply a data conversion tool that takes some of the disadvantages of managing database connectivity and maps your result sets to custom domain objects.

You still have to write all the classes of objects and SQL, but it maps all your data into classes for you using reflection, and you don’t need to worry about all the basic relationships (you can easily write a tool to create your classes). When you work with iBATIS (suppose you might be interested), your code will begin to look like this:

 var customer = Helpers.Customers.SelectByCustomerID(1); 

This SelectByCustomerID function exists inside the Customers transformer, the definition of which might look like this:

 public Customer SelectByCustomerID(int id) { Return Mapper.QueryForObject<Customer>("Customers.SelectByID", id); } 

Customers.SelectByID maps to the definition of an XML statement, where Clients is the namespace and SelectByID is the identifier of the map containing your SQL:

 <statements> <select id="SelectByID" parameterClass="int" resultClass="Customer"> SELECT * FROM Customers WHERE ID = #value# </select> </statements> 

Or, when you want to change a client, you can do things like:

 customer.FirstName = "George" customer.LastName = "Costanza" Helpers.Customers.Update(customer); 

LINQ to SQL and Entity Framework are becoming more attractive, creating SQL for you automatically. I like iBATIS because I still have full control over SQL and my domain objects.

Check out iBATIS (now ported to Google as MyBatis.NET). Another great package is NHibernate , which is several steps ahead of iBATIS and closer to full ORM.

Answer 6

Visual database page using combobox and datagrid

namespace

TestDatabase.Model

{Class Database

 { private MySqlConnection connecting; private MySqlDataAdapter adapter; public Database() { connecting = new MySqlConnection("server=;uid=;pwd=;database=;"); connecting.Open(); } public DataTable GetTable(string tableName) { adapter = new MySqlDataAdapter("SELECT * FROM "+ tableName, connecting); DataSet ds = new DataSet(); adapter.Fill(ds); adapter.UpdateCommand = new MySqlCommandBuilder(adapter).GetUpdateCommand(); adapter.DeleteCommand = new MySqlCommandBuilder(adapter).GetDeleteCommand(); ds.Tables[0].RowChanged += new DataRowChangeEventHandler(Rowchanged); ds.Tables[0].RowDeleted += new DataRowChangeEventHandler(Rowchanged); return ds.Tables[0]; } public void Rowchanged(object sender, DataRowChangeEventArgs args) { adapter.Update(sender as DataTable); } } 

}

Answer 7

VMV DATABSE

 namespace TestDatabase.ViewModel { class MainViewModel : INotifyPropertyChanged { private Model.Database database; private DataTable table; public string[] options; public string selected; public DataTable Table { get { return table; } set { table = value; ChangeProperty("Table"); } } public string[] Options { get { return options; } } public string Selected { get { return selected; } set { selected = value; Table = database.GetTable(value); } } public MainViewModel() { database = new Model.Database(); options = new string[] { "" }; // names of tables } public event PropertyChangedEventHandler PropertyChanged; private void ChangeProperty(string name) { if (this.PropertyChanged != null) { this.PropertyChanged(this, new PropertyChangedEventArgs(name)); } } } }