Almost on a daily basis we run across posts that ask about using a .NET language to read and update a database. Oh it might have a slight variation like “How do I load a value from a database into a textbox?” or “How do I read a database into _______ control and then update the database?” So I figured I would put a few little snippets here to show you some ways it can be done. We will work on reading a simple column (name) from an Access Database table (Employees) and stick those names into a listbox for selection. While there are many ways to do these things, I simply create 3 different ways that you can play with below. At the end we will cover a quick way to make a nice update to the database. So grab a drink, kick off your shoes and lets play with some snippets here on the Programming Underground!
So how do we get this stupid name column out of our table “Employees” of the access database and into a control? Well, we have to do a few simple things. One is to connect to the database, another thing is to query the database for the info and lastly we put that info into our source control… this case is our listbox. Connecting to the database is pretty straight forward and really starts out with picking the right connection object from the System.Data namespace to target out database. Since Access can be “accessed” through the OLEDB setup we can use the OleDbConnection class for that. All we need now is to give it a connection string. This is nothing but a string that tells the class where to find the database, what driver (or DSN) to use, any username and passwords to login to the database, and any security features we will need to implement for our access. One great place to find connection string data for tons of different databases is at connectionstrings.com.
I am using a local instance of an access database that doesn’t require any real credentials to access. All we need is to tell it which driver to use and where to find the database file. If this had been a database like MySQL located on another server, we would have to specify IP address info as well as port. But lets keep the example small people!
So first we bring in the System.Data.OleDb namespace so we get to use OleDbConnection as well as other classes you will see later. Then we give it the connection string and open the connection…
// Form level variable for our connection
OleDbConnection connection;
// Constructor
public Form1()
{
connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\example.mdb;Persist Security Info=False");
InitializeComponent();
}
Notice in our code above we create a new OleDbConnection and give it a connection string to tells it where to find example.mdb and to use our OLEDB 4.0 driver. Nothing much to see here other than how to create the class and use a connection string to tell the connection where to find the database.
Since we now have a database connection made, we have a few choices on how to query and load our data. I will show each method here. Some have their advantages and disadvantages, so be sure to research which one might fit your needs the most. The first example will be to use a DataAdapter class and its fill method to load it into a DataSet. Think of this method like using a hose to fill up a bucket. The DataAdapter will use our connection (a well???) and pump info into our DataSet which will be our bucket. Once we have it in the bucket, we can use the data independent of the connection or adapter. This example will use the DataSet to “bind” to the listbox and empty its contents into the listbox control.
private void button1_Click(object sender, EventArgs e)
{
// Create our adapter and give it a query for the database using the connection object.
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select name from employees", connection);
// Here is our "bucket"
DataSet ds = new DataSet();
// Turn on the hose and fill the bucket with the results of our query. We will then label this particular set of results as "employees"
dataAdapter.Fill(ds, "employees");
// Tell our listbox to use this table of results from the dataset.
// Both the info it shows and the value of the items will be the employee's name column.
listBox1.DataSource = ds;
listBox1.DisplayMember = "employees.name";
listBox1.ValueMember = "employees.name";
}
Not too bad right? The only trick here is that we bind the database to the listbox and tell the listbox to use the name column as both the names we display to the user as well as the value each item has. So if we see “John” in the listbox, the value of that item will also be “John”. We could have given it a different value than what it shows. Just like you can give each option item in HTML select boxes a different value than its actual list item name. The advantage of this method is that it is a quick “dump all the info here” method. It is also good in that it is directly tied to the dataset itself. So if we modify the dataset, the listbox automatically changes as well. The disadvantage is that it can get a bit tricky to update the dataset itself unless you have a good picture of how that dataset is setup. Keep in mind that this would be altering the dataset data, not the database.
The second method I will show you is again using a dataset but this time we will loop through it to load the listbox. This method is good for times where you want to modify each value before adding it to the listbox.
private void button1_Click(object sender, EventArgs e)
{
// Again our data adapter is going to be our hose, same query
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select name from employees", connection);
// Our "bucket"
DataSet ds = new DataSet();
// Fill the bucket with the results of the query and give it the name "employees"
dataAdapter.Fill(ds, "employees");
// Loop through the rows of the only table in the DataSet
// Now keep in mind that the info in a DataSet can contain multiple tables of data and each table has columns and rows like a spreadsheet.
// So here we ask it to get the first table (aka Employees) and loop through each DataRow. We use the row to access the column "name" and add that value to the listbox.
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
listBox1.Items.Add(dataRow["name"].ToString());
}
}
Slightly different than the first method, differing only by how we load the data. We could alter the tables in the DataSet directly before looping through it, but sometimes beginners find it easier to just loop through it like this and before displaying. Like if we wanted to make sure that each name is properly capitalized we could do that before adding it to the listBox1.Items.
The last method here for selecting data is using a command object and fetching what is called a “Reader”. This method is especially common because the command object is very flexible and good not only for select queries, but for updates, inserts and delete queries as well. Very versatile! We will use it again to show you a simple update in a second. Another beautiful thing about using a Reader to move through data is because they are “forward only”. Since you can only move to the next record at a time and can’t go back, .NET has to dedicate less resources to managing the internal record pointer and can speed up access. This means it is great for any situation where you simply want to loop through the values quickly and easily without needing to go back. Nice for report displays etc. Of course this is also the disadvantage, we can’t simply go to the previous record if we wanted to. We could reset the pointer, but then that would kill the speed here.
// Our command object which again uses the connection string and a connection object
OleDbCommand cmd = new OleDbCommand("select name from employees", connection);
// Our reader is going to fetch our results
OleDbDataReader reader = cmd.ExecuteReader();
// Determine if the reader actually has rows
if (reader.HasRows)
{
// Loop through the reader real fast and add the first string column it has (our name column)
while (reader.Read())
{
listBox1.Items.Add(reader.GetString(0));
}
}
Again this is faster than the other methods and you will certainly see a difference when it comes to loading larger lists of data. Since we don’t really have to go back and forth through the data, we can use the reader here to quickly speed through the list of names and spit them into the listbox. There are other Execute statements that you can use with the command object including ExecuteScalar for single values and ExecuteNonQuery which we will now use to execute any statement which is not a “query” statement. That is, any SQL that is not a select (aka insert, update and delete).
private void updateDatabase()
{
// Create a command object, give it a connection object, give it a query to update our database where name equals 'John'
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = connection;
cmd.CommandText = "update employees set name = 'Jonathan' where name = 'John'";
// Now simply execute it!
cmd.ExecuteNonQuery();
}
The function above is very simple and straight forward. We simply make a new command object, give it the connection (make sure it is open first) and an update query for the commandtext. We then call the ExecuteNonQuery() method to make the update happen. The database will then be updated anywhere the name equals “John”…. it will become “Jonathan”.
If we were to use one of the other methods again to read this database table back to us, we would see that the Access database Employees table would be modified. Again, very simple and straight forward. This isn’t the only way to do these queries and you may see other examples of doing it. However, if you are working with Access and only need to do something real quick, this example should serve you well.
NOTE: A word on security here, make sure you always validate the information coming in from the user before using them in any kind of database query. For more information on doing this securely, please look up “Parameters” collection which is part of the command object. This, along with a nicely setup query string, can help you avoid nasty people injecting bad code into your database. As always, treat all user input as dirty input!
Use the examples above to experiment with reading and updating/inserting/deleting records from Access databases. You will quickly be able to build some great applications which utilize databases for your own small projects. For more advanced features, I suggest you take an extra hard look at the command, connection and dataset classes. These are the building blocks for using databases in .NET.
Enjoy and thanks for reading! 🙂
