Connecting to a Database Through Code
The first example we encountered for connecting to databases involves Visual Studio tools that don’t require you to type any code at all. We will now begin a series of lessons which concentrates on using the ADO.NET classes to type codes that connect to different data sources. We will use the Windows Forms Application project type to present the different examples of connecting to a data source. You should have also followed the lesson on creating a database and table where we created the database and table that we will need in the following lesson.
What we will be covering in the next lessons is how to query (retrieve records), insert or add records, delete records, and update existing records. I will present two ways of doing these things, the connected way, and the disconnected way. Although both do the same thing, they have their specific purposes and scenarios where you can use them.
The connected approach is good if you want to execute commands quickly and you are working with a big set of data. The disadvantage of it is that you need to open the connection and no other connection can use it until you close it again. The disconnected approach is recommended if you are working with small chunks of data from the database. One big advantage of using it is that you don’t need an open connection as the results are filled inside a DataSet. You can then do the modifications in the DataSet and submit the changes to the database later.
If you have already tried out connecting to the database using the tools offered by Visual Studio, this time, you have to type every single code used to connect to the data source. Why do this if such tools already exist? This is because those tools generate hundreds of lines of code which is optimized and full of advanced stuff you won’t be needing for a simple data-driven application. The codes we will write will only contain several lines of code. As we progress we will modify the code we will learn and apply them to more advanced applications and techniques.
Note again that we will be using the Microsoft SQL Server 2008 Express as our main data source and I am assuming that you have installed it correctly including the sample database and tables we have created.