Dates and Times in C# .NET

At some stage of your programming career, you'll need the ability to manipulate dates and time. A typical example would be a database programme where you want to record when an entry was made, especially if it's an order for a product. Or if you want to, say, calculate how many days it's been since an order was placed.
Start a new project for this. Add a button to your new form, and double click it to get at the code.
An inbuilt structure you can use to manipulate dates is called DateTime. Add this to you button code:

DateTime theDate;

theDate = DateTime.Now;

MessageBox.Show( theDate.ToString() );

After setting up a DateTime variable called theDate, we have this line:

theDate = DateTime.Now;

The Now property returns the current system date and time of your computer, wherever you are in the world.
The third line in our code converts the DateTime into a string and then displays it in a message box. When the code is run, and the button clicked, the message box will be something like this:

This is the date in UK format (day first, then month), followed by the current time (hours, minutes and seconds).
You can also have this, instead of Now:

theDate = DateTime.Today;

And even this

theDate = DateTime.UtcNow;

Utc is something called Coordinated Universal Time, or International Atomic Time.
Try all three in your code and see how they differ.
To get at just the year, or the month, or the day, take a look at the IntelliSense list when you type the dot after DateTime:

If you click on Day to highlight it, you'll see that it is an Integer:
To use this, then, you can set up a new integer variable and hand it the day:

theDate = DateTime.UtcNow;

int theDay = theDate.Day;

The Month and Year are also Integers, so the code is similar:

int theMonth = theDate.Month;

Or

int theYear = theDate.Year;

You can also convert your date to a string, and use something called a Format Provider. Try this code:

DateTime theDate = DateTime.UtcNow;

string custom = theDate.ToString("d");

MessageBox.Show(custom);

When you run your programme and click the button, the date displays in this format:

12/11/13

Change the "d" in the code above to "D" (capital D instead of lowercase). When you run the code, the date is displayed like this:

12 November 2013

Here's a list of letters you can use, and what they will display. Try a few and see for yourself:

Another thing you can do with DateTime is to specify a format. Type the following:

DateTime firstDate = new DateTime

After the final "e", type a round bracket. You should see this:

There are 12 different ways to use the DateTime structure. Each one is giving you the option of a date/time format. Examine option 4 of 12:

You can just type some numbers here:

DateTime firstDate = new DateTime(2013, 01, 14);

What you'll get back is the Date you specified. But you'll also get the time as a series of zeros:

If you want to calculate the difference between one date and another, then a more precise structure is used with DateTime - TimeSpan. Here's how to use it:

DateTime firstDate = new DateTime(2013, 01, 14);
DateTime secondDate = DateTime.Parse("1 Feb 2013");

TimeSpan dateDiff;
dateDiff = secondDate.Subtract(firstDate);
MessageBox.Show("Date diff:" + dateDiff.ToString() );

The first line sets up a date: the 14th of January 2013. The second line sets up a date in a different way:

DateTime secondDate = DateTime.Parse("1 Feb 2013");

After the dot of DateTime, we're using Parse. In between the round brackets of Parse, simply type your date between double quotes.
When you subtract one date from another, the answer is returned as a TimeSpan structure. This uses similar methods and properties as DateTime. One of the options is Subtract:

TimeSpan dateDiff;
dateDiff = secondDate.Subtract( firstDate );

After typing a date then a dot, use the Subtract method. In between the round brackets of Subtract, you need another date - the one you're trying to subtract. When the code is run, it will calculate the difference between the two dates.
You can also add date and time values to ones you already have. Examine this code:

firstDate = firstDate.AddDays(30);
MessageBox.Show( firstDate.ToString("D") );

Now we're using AddDays after our firstDate variable. In between the round brackets of AddDays, type how many days you want to add.
When the IntelliSense list appears, have a look at other date and time values you can add: AddMonths, AddYears, etc.

Getting at the values on other Forms

Turn your Form2 into a Change Case dialogue box, just like ours below:

When the OK button is clicked, we want the text in the text box on Form1 to change case, depending on which of the three options was chosen.
The problem we face is that the text box is private to Form1, and can't be seen from outside it. If you tried to refer to the text box from Form2, you'd just get errors.
One solution is to set up a public static variable, of type TextBox. You then assign textBox1 to this new variable.
So add the following to Form1:

public static TextBox tb = new TextBox();

This creates a new TextBox object called tb. Add the line just under your Form variable, and your coding window will look like this:

Notice that we've deleted the message box code, and went back to the original. That's because we don't need the message box anymore. Delete yours as well.
Now that we have a TextBox object, we can assign our text box on form one to it. In the Form Load event of Form1, add the following line:

tb = txtChangeCase;

(The easiest way to bring up the code stub for the Form Load event is to double click a blank area of the form in design view.)
Here's what all the Form1 code looks like now:

When the main form (Form1) loads, the text box will now be available to Form2.
So double click your OK button on Form2 to bring up its code stub. Enter the following:

string changeCase = Form1.tb.Text;

We're setting up a string variable called changeCase. The contents of this new string variable will be the Text from the text box called tb on Form1.
To change the case of the text, we can use string methods for two of them: Uppercase and Lowercase. Like this:

changeCase = changeCase.ToUpper();

changeCase = changeCase.ToLower();

Unfortunately, C# .NET does not have a direct string method to change text to Proper Case (or Title case as it's also know). Proper Case is capitalising the first letter of each word. For example, "This Is Proper Case".
In order to get Proper Case, you have to reference two System namespaces. One called Globalization and one called Threading. Add the following to the very top of the coding window:

using System.Globalization;
using System.Threading;

The code window will then look something like this:

Now that we have these two references, the next thing to do is to set up something called a CultureInfo object:

CultureInfo properCase = Thread.CurrentThread.CurrentCulture;

The CurrentCulture tells you information about the various language options of your particular country. Our CultureInfo object is called properCase.
That's not the end of it, though! You also need a TextInfo object:

TextInfo textInfoObject = properCase.TextInfo;

It's this TextInfo object that has the methods we need. We're setting up a TextInfo object called textInfoObject. We're handing it the TextInfo property of our properCase CultureInfo object.
Finally, we can change the case:

changeCase = textInfoObject.ToTitleCase( changeCase );

The TextInfo object has a method called ToTitleCase. In between the round brackets of the method, you type what it is you want to convert.
Hopefully, in future versions of C#, they'll add an easier way to convert to Proper Case!
To get which of the options on our Form2 was chosen, we can add a series of if … else statements:

So we're just checking to see which radio button was selected. We're then doing the case conversion.
To put the changed text into the text box on Form1, add the following line:

Form1.tb.Text = changeCase;

Add the line just before your DialogResult line. The full code for the button should be this

Run your programme and test it out. Click your button to bring up Form2. Select the Upper Case option and then click your OK button. You should find that the text in txtChangeCase on your main form will now be in uppercase.

The next section of the C# course deals with Dates and Times.

Modal Forms in C# .NET

Return to the code for your button. Instead of using the Show method, change it to this:

secondForm.ShowDialog();

The method we're now using is ShowDialog. This creates what's known as a Modal form. A Modal form is one where you have to deal with it before you can continue. Run your programme to test it out. Click the button and a new form appears. Move it out of the way and try to click the button again. You won't be able to.
Modal forms have a neat trick up their sleeves. Add two buttons to your blank second form. Set the following properties for them:

Name: btnOK
Text: OK

Name: btnCancel
Text: Cancel

Your second form will then look like this:

Double click the OK button and add the following:

this.DialogResult = DialogResult.OK;

After you type the equals sign, the IntelliSense list will appear. Select DialogResult again, then a dot. The IntelliSense list will then show you this:

Select OK. What this does is to record the result of the button click, and set it to OK.
Double click your Cancel button and add the following code:

this.DialogResult = DialogResult.Cancel;

It's the same code, except we've chosen Cancel as the Result. Your coding window for form 2 should look like this:

You can use Form1 to get which of the buttons was clicked on Form2. Was it OK or was it Cancel?
Change the button code on Form1 to this:

The code checks to see if the OK button was clicked. If so, it displays a message. We'll get it to do something else in a moment. But you don't have to do anything with the Cancel button: C# will just unload the form for you.
Try it out. Click your Change Case button on Form1. When your new form appears, click the OK button. You should see the message. Try it again, and click the Cancel button. The form just unloads.

Creating Multiple Forms in C# .NET

There aren't many programmes that have only one form. Most programmes have other forms that are accessible from the main one that loads at start up. In this section, you'll learn how to create programmes with more than form.
The programme we'll create is very simple one. It will have a main form with a text box and a button. When the button is clicked, it will launch a second form. On the second form, we'll allow a user to change the case of the text in the text box on form one.
Here's what form one looks like:

Design the above form. Set the Name property of the text box to txtChangeCase. For the Text property, add some default text, but all in lowercase letters. Set the Name property of the button to btnFormTwo.
Adding a new form to the project is easy. Click Project from the menu bar at the top of the Visual C# software. From the Project menu, select Add New Windows Form. You'll see the Add New Item dialogue box appear. Make sure Windows Form is selected. For the Name, leave it on the default of Form2.cs. When you click OK, you should see a new blank form appear:

It will also be in the Solution Explorer on the right:

Adding the form to the project is the easy part - getting it to display is an entirely different matter!
To display the second form, you have to bear in mind that forms are classes. When the programme first runs, C# will create an object from your Form1 class. But it won't do anything with your Form2 class. You have to create the object yourself.
So double click the button on your Form1 to get at the coding window.
To create a Form2 object, declare a variable of Type Form2:

Form2 secondForm;

Now create a new object:

secondForm = new Form2();

Or if you prefer, put it all on one line:

Form2 secondForm = new Form2();

What we've done here is to create a new object from the Class called Form2. The name of our variable is secondForm.
To get this new form to appear, you use the Show( ) method of the object:

secondForm.Show();

Your code should now look like this:

Run your programme and test it out. Click your button and a new form should appear - the blank second form.
However, there's a slight problem. Click the button again and a new form will appear. Keep clicking the button and your screen will be filled with blank forms!
To stop this from happening, move the code that creates the form outside of the button. Like this:

Try your programme again. Click the button and you won't get lots of forms filling the screen.
In the next lesson, you'll learn what a Modal form is.

Update a Record, Delete a record

Update a Record

Sometimes, all you want to do is to update a record in the database, change a name, for example, or amend an address.
Add a new button to your form. Call it btnUdate. Double click the button to get at the code. Add the following lines:

DataRow row = ds.Tables[0].Rows[inc];

row[1] = txtFirstName.Text;
row[2] = txtSurname.Text;
row[3] = txtJobTitle.Text;
row[4] = txtDepartment.Text;

This is very similar to the code for adding a new record. Again we start by creating a DataRow object. This time, we place a specific row in the new object. That row is one from the DataSet:

ds.Tables[0].Rows[ inc ];

However, this is the old row. The new details are in the textboxes, the ones you may have amended. To place the new details into the new row object, we simply transfer the textbox date to a position in the row object:

row[1] = txtFirstName.Text;
row[2] = txtSurname.Text;
row[3] = txtJobTitle.Text;
row[4] = txtDepartment.Text;

All we need to do now is to make a call to the UpdateDatabase method from our class. Again, we can do this in a try … catch block:

try
{

objConnect.UpdateDatabase(ds);

MessageBox.Show("Record Updated");

}
catch (Exception err)
{

MessageBox.Show(err.Message);

}

The only line we really need is this one:

objConnect.UpdateDatabase(ds);

This is the same as before: we're just passing our UpdateDatabase method the name of the DataSet, which is called ds.
The whole of the btnUpdate code is this:

Try it out. Run your form and amend one of the records. Click your Update button. When you close the form down and open it back up again, the amended record will display.

Delete a Record

To delete a record from the Dataset, you use the Delete method on the DataSet:

ds.Tables[0].Rows[inc].Delete( );

This is enough to Delete the entire Row ( Rows[inc] ). But it is only deleted from the Dataset. To delete if from the underlying database as well, we can call our UpdateDatabase method again.

objConnect.UpdateDatabase(ds);

Because a row has been deleted, we need to change the value of our MaxRows variable. We also need to deduct 1 from the inc variable, and make a call NavigateRecords:

MaxRows = ds.Tables[0].Rows.Count;
inc--;

NavigateRecords();

Add another button to your form. Call it btnDelete. Here's the whole of the code to add to your new button:

Try it out. Run your form. Navigate to a record you want to delete, then press your Delete button. Close your form and reopen it. The record you delete should be gone.

Exercise P
Examine this version of our form:

If you look at the bottom, you'll see a label that says Record 1 of 10. Implement this in your own programme. If you set up a method, you can just call it from NavigateRecords.

OK, that's enough of databases! It's a huge subject, obviously, and many books have been written on the subject. We've only touched the surface in these lessons, and encourage you to delve deeper. Especially if you want a job as a programmer!

In the next section, we'll take a look at multiple forms.

Add a Record to the Dataset and Update the Database

When you add a new record, you'll want to add it to the Dataset and the underlying database. Let's see how.
Add three new buttons to the form. Set the following properties for your buttons:

Name: btnAddNew
Text: Add New

Name: btnSave
Text: Save
Enabled: False

Name: btnCancel
Text: Cancel
Enabled: False

The Add New button won't actually add a new record. We'll use this button to clear the textboxes, ready for a new record to be added. We can also disable this button after it has been clicked, and enable the Save and Cancel buttons. The Save button is where we'll add the record to the Dataset and to the Database.
Double click your Add New button, and add code to clear the text boxes:

textBox1.Clear();
textBox2.Clear();
textBox3.Clear();
textBox4.Clear();

If the user clicks the Add New button then we want to disable it, and then switch on the Save and Cancel button. To do that, here's the code:

btnAddNew.Enabled = false;
btnSave.Enabled = true;
btnCancel.Enabled = true;

That's all we need to do here. You can test it out, if you want. When the form loads it will look like this (we've put our buttons on a GroupBox):

When you click the Add New button, the form will look like this:

But all the code does is to clear the four textboxes of text. The user can then enter a new record.
Return to Design Time and double click your Cancel button. What we need to do here is to call NavigateRecords. We need to do this because the textboxes are all blank. Calling NavigateRecords will put the record that was erased back into the textboxes.
The Cancel button also needs to reset all three buttons. Here's the code to add, then:

NavigateRecords( );

btnCancel.Enabled = false;
btnSave.Enabled = false;
btnAddNew.Enabled = true;

You can test it out. Click your Add New button when the form loads. The textboxes will go blank, and the first record will disappear. Now click Cancel. You should find that the record reappears.

Saving a New Record

After a new record has been entered into the text boxes, we can save it. Double click your Save button to get at the code.
To save a record, you need to do two things: save it to the Dataset, and save it to the underlying database. You need to do it this way because the Dataset with its copy of the records is disconnected from the database. Saving to the Dataset is NOT the same as saving to the database.
To add a record to the Dataset, you need to create a new Row:

DataRow row = ds.Tables[0].NewRow( );

This creates a new DataRow object that we've called row. The DataRow object is used to add new rows to a Dataset. But the new row will not have any data. To add data to the row, the format is this:

row[1] = textFirstName.Text;

So after your DataRow variable (row) you need a pair of square brackets. In between the square brackets type its position in the Row. This is the Column number. So row[1] refers to the first_name column, for us. After an equals sign, you type whatever it is you want to add to that Column - the text from textFirstName, in our case.
Finally, you issue the Add command:

ds.Tables[0] Rows.Add( row );

After Add, and in between a pair of round brackets, you type the name of the row you want to add, which was row in our example. The new row will then get added to the end of the Dataset.
So add this code to your Save button:

DataRow row = ds.Tables[0].NewRow( );
row[1] = txtFirstName.Text;
row[2] = txtSurname.Text;
row[3] = txtJobTitle.Text;
row[4] = txtDepartment.Text;

ds.Tables[0].Rows.Add(row);.Rows.Add(row);

To actually update the database, we need to add a new method to our class. This method will use the DataAdapter we set up. The DataAdapter is linked to the database itself, and can issue Update, Insert and Delete commands. It's quite easy to do and only takes a couple of lines.
If your DatabaseConnections class is not open, double click it in the Solution Explorer to get at the code. Now add the following method:

public void UpdateDatabase( System.Data.DataSet ds )
{

}

So this is a public method that doesn't return a value (void). We called it UpdateDatabase. In between the round brackets of UpdateDatabase have a look at what the code is:

System.Data.DataSet ds

This is a DataSet being passed to the method. The name of the DataSet is ds. Our DataSet, remember, is where all the records are kept, even the ones that have changed. We'll be updating the database with these new changes.
In order to do an automatic update, you need something called a CommandBuilder. This is initialised with the name of a DataAdapter, the DataAdapter we set up earlier in the Class.
Add this rather long line to your method (it should be on one line in your code):

System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder( da_1 );

We've called our CommandBuilder cb. At the end, in between round brackets, we have the name of our DataAdapter, which was da_1.
Now that we have a CommandBuilder, we can do something with it. Add this line to your method:

cb.DataAdapter.Update( ds.Tables[0] );

The CommandBuilder has a property called DataAdapter. This property has a method of its own called Update. The Update method takes the name of a DataSet and a table. The DataSet for us is ds, which is the one we passed in to our UpdateDatabase method when we set it up. After a dot, you type the name of a table in your dataset.
Here's what your method should look like:

And that's it. That's enough to update the underlying database with your new record. Of course, we have yet to call our new method into action.
Go back to the code for your Save button. Add a try … catch statement:

try
{

}
catch (Exception err)
{

MessageBox.Show(err.Message);

}

For the try part, add the following:

try
{

objConnect.UpdateDatabase(ds);

MaxRows + 1;
inc = MaxRows - 1;

MessageBox.Show("Database updated");

}

As the first line of the try part, we have this:

objConnect.UpdateDatabase(ds);

Here, we access our DatabaseConnections object (objConnect), and call our new method UpdateDatabase. In between the round brackets of our UpdateDatabase method we have the name of our DataSet, which is ds. This contains all the records we pulled from the database, and any amendments we've made. We're handing the whole thing over to the new method, where the CommandBuilder and DataAdapter will take care of the updates for us.
Notice the next two lines:

MaxRows = MaxRows + 1;
inc = MaxRows - 1;

Because we have added a new Row to the Dataset, we also need to add 1 to the MaxRows variable. The inc variable can be set to the last record in the Dataset, which is MaxRows - 1.
The final three lines of our Save code are these:

btnCancel.Enabled = false;
btnSave.Enabled = false;
btnAddNew.Enabled = true;

These three lines just reset the buttons to their original state.
The whole of the code for your Save button should look like this:

Try it out. When you start your programme, click the Add New button to clear the textboxes. Enter a new record in the blank textboxes and then click your Save button. Click your Previous and Next buttons. You'll see that the new record appears. When you close down your project and open it up again, the new record should still be there.
(If the new record doesn't appear when you restart, go back to Design Time. In the Solution Explorer, click on your Database under Resources to select it. Now have a look at the Properties window below the Solution Explorer. Locate a property called Copy to Output Directory. It has three settings: Do not copy, Copy always, and Copy if newer. If your database is not updating, try either Copy if newer or Copy always.)
In the next lesson, you'll learn how to update a record, and how to delete a record.

Moving Forward and Backward through the Database

To move forward through the database table, add a button to your form. Give it the Name btnNext. Add some text to the button.
Double click your button to get at the coding window. For the code, we need to check what is inside of the MaxRows variable and make sure we don't go past it. MaxRows, remember, is holding how many records are in the DataSet. We also need to increment the inc variable. It is this variable that will move us on to the next record.
Add the following if statement to your button:

The first line of the If Statement says "If inc does not equal MaxRows minus 1". If it doesn't then we increment the inc variable and call NavigateRecords. But can you see why we need to say MaxRows - 1? It's because of the Rows[inc] line in our NavigateRecords method. The count for Rows starts at zero. So if we only have 4 records in the database, the count will be for 0 to 3. MaxRows, however, will be 4. If we don't deduct 1, the programme will crash with an error: IndexOutOfRange.
If MaxRows is reached, then we can display a message for the user: "No more rows".
Run your programme and test it out. You should be able to move forward through your database. Here's what your form should look like when the last record is reached:

Move Backwards through the Database

We can use similar code to move backwards through the records in the database.
Add another button to your form. Change the Text property to Previous Record. Change the Name property to btnPrevious.
Double click your new button to get at the coding window. Now add the following:

if (inc > 0)
{

inc--;
NavigateRecords( );

}
else
{

MessageBox.Show("First Record");

}

The if statement is now only checking the inc variable. We need to check if it's greater than zero. If it is, we can deduct 1 from inc, and then call our NavigateRecords methods. When the form loads, remember, inc will be 0. So if we tried to move back one record after the form first loads the programme would crash. It would crash because we'd be trying to access Rows[-1].
Run your programme and test it out. Click you Previous button and you should see this:

Jump to the Last Record in your Database

To move to the last record of your database, you only need to make sure that the inc variable and MaxRows have the same value.
Add a new button to your form. Set the Text property as Last Record, and the Name property as btnLast. Double click the button, and add the following code:

if (inc != MaxRows - 1)
{

inc = MaxRows - 1;
NavigateRecords( );

}

The If Statement again checks that inc is not equal to MaxRows minus 1. If it isn't, we have this:

inc = MaxRows - 1;

MaxRows minus 1 would equal 3 in a four record database. Because Rows[inc] goes from 0 to 3, this is enough to move to the last record after the call to NavigateRecords.

Jump to the First Record in your Database

To move to the first record in the database, we only need to set inc to zero.
Add another button to your form. Change the Text property to First Record. Change the Name property to btnFirst. Double click your new button and add the following code:

if (inc != 0)
{

inc = 0;
NavigateRecords( );

}

This just checks to see if inc isn't already zero. If it isn't, we set the inc variable to 0. Then we call the NavigateRecords method.
Run your programme and test it out. You should now be able to move through the records in your database without the programme crashing. What we'll do now is to allow the user to add a new record to the database. This is more complex than the navigation, so you may need to pay close attention!

Using the Database Connection Class

You now have a Windows form and a Database class that you created yourself. Go back to the form and add four textboxes. Change the Name property of each textbox to the following:

txtFirstName
txtSurname
txtJobTitle
txtDepartment

Add four labels to your form and place them next to the textboxes. Add the following text to the labels:

First Name
Surname
Job Title
Department

Your form will then look something like this:

What we're going to do now is to connect to the database using our class. We'll do this from the Form Load event. We'll place the first record from the table into the textboxes. Once this is done, we can then add buttons that will allow is to scroll forwards and backwards through all the records in the table.
To add the Form Load code stub, simply double click anywhere on your form that is not a textbox or a label. The code stub that opens up will look like this:

Because we're going to be adding buttons later, we need to set up variables where all the buttons can see them. The first one we need is a variable to store our connection object. Add the following line just outside of the Form Load event:

DatabaseConnection objConnect;

The variable is called objConnect. It is of type DatabaseConnection, which is the name of our class.
We also need a string to hold our connection string from the Setting page we set up earlier. So add this, as well:

string conString;

This is just a normal string variable that we've called conString.
Your coding window should now look like this:

We also need a DataSet object. This is because the GetConnection method in our class is set up to load all the database data into a DataSet. When we call our GetConnection method we'll need somewhere to put the DataSet, which will be another DataSet. So add this line to your code, just below the other two:

DataSet ds;

A DataSet contains rows, which correspond to a row in the database table. To manipulate each row, you work with a DataRow object. You'll see how this works shortly. But add this line to your code, as well:

DataRow dRow;

The final two variables we need to add outside of the form load event are these two:

int MaxRows;
int inc = 0;

The two variables are both integers. The first one, MaxRows, will tell us how many rows there are in the DataSet, which is how many rows were pulled from the database table. The other integer variable, inc, will be used to move from one record to another, and back again. We'll need this for the buttons we'll add later.
But your coding window should now look like this:

We can now turn our attention to the Form Load event. We'll place the code for this event in a try … catch statement. So add the following to your form load event:

try
{

}
catch (Exception err)
{

MessageBox.Show(err.Message);

}

Your code window should look like this:

The first thing to do in the try part is to set up an object from our class. Here's the line that does that:

objConnect = new DatabaseConnection( );

This creates a new object for us, of type DatabaseConnection. (Don't forget the round brackets on the end.)
We can now grab that connection we set up in the Settings page earlier. To do that, start with your conString variable then an equal sign:

conString =

After the equal sign, type the word Properties, then a dot. As soon as you type the dot, you'll see the IntelliSense list appear:

Select Settings (which refers to the Settings page), then type another dot. You'll see the IntelliSense list again:

Select Default, and type another dot:

You should see your connection string on the list, which was EmployeesConnectionString for us. Select this and then end the line with a semicolon.
Now that we've placed the connection string into our conString variable, we can hand it over to our class. Type objConnect (the name of our class object in the Form), then a dot. You should see this:

Our connection_string property is showing up on the IntelliSense list. This allows us to pass over the connection string to our DatabaseConnection class.
Finish the line by adding the conString variable:

objConnect.connection_string = conString;

The next thing we need to do is to pass over some SQL to our DatabaseConnection class. We set this up on the Settings page. The SQL was this:

SELECT * FROM tbl_employees

This reads "Select all the records from the table called tbl_employees".
Add the following line to your code:

objConnect.Sql = Properties.Settings.Default.SQL;

You should see the IntelliSense list appear as you're typing the above line. At the end, is the name of the setting we added - SQL. This all gets passed to our DatabaseConnection class via the Sql property before the equal sign.
What we've done so far is to hand our DatabaseConnection class a connection string, which contains the name and location of the database; and we've also handed it some SQL, so that we can pull records from a table in the database. We set up a method in our DatabaseConnection class that takes these two values (connection string and SQL), and uses them to place data from the database into a DataSet. Let's now add code to return that DataSet.
The name of our DataSet in the Form code is ds. This is the variable we set up at the top of the Form Load event. We can call our GetConnection method from the DatabaseConnection class and hand its DataSet over to the variable ds. Add this code:

ds = objConnect.GetConnection;

So objConnect is our object created from the DatabaseConnection class. Type a dot and you'll see the IntelliSense list appear:

The GetConnection method is on the list. Double click to add it to your code, and then type a semicolon to end the line.
We can count how many records are in the DataSet. (These are the same records that are in the database table, remember.) You do it like this:

MaxRows = ds.Tables[0].Rows.Count;

MaxRows is the integer variable we set up at the top of the Form Load event. After an equal sign, we have this:

ds.Tables[0].Rows.Count;

The name of our DataSet is ds. The DataSet has a property called Tables. This is a list of all the tables in your DataSet. (We only have one table.) The first table is at position 0. The 0 goes between square brackets. After a dot, you type Rows.Count. This counts how many rows are in the DataSet.
To fill the textboxes, we can add a method. Let's call it NavigateRecords:

private void NavigateRecords()
{

}

We don't need to return a value so the method is set up as void.
Because we want to access one row at a time, we can use the DataRow object we set up earlier. If you wanted to access row 1 from the DataSet, the code would be this:

dRow = ds.Tables[0].Rows[1];

Here, the entire first row will be placed in the dRow variable. To place this first row into the textboxes we can use the ItemArray property of DataRow objects. The ItemArray property has a method called GetValue. In between the round brackets of GetValue you type the column you want to access. For example, take this code:

txtFirstName.Text = dRow.ItemArray.GetValue(1).ToString();

The value in between the round brackets of GetValue is 1. This will place row 1, column 1 into the textbox called txtFirstName. If we then advanced the row counter to 2, row 2 column 1 will be placed into the textbox. Advanced the row counter again and row 3 column 1 will end up in the textbox. (The ToString at the end just ensures that any non-text values get converted to strings.)
To advance the row counter, we can use our inc variable:

dRow = ds.Tables[0].Rows[inc];

So instead of hard-coding a value of 1 between Rows, we're using whatever value is inside of the inc variable. We can then increment the inc variable later from buttons on the form.
So add the following code, just below the Form Load event:

private void NavigateRecords()
{

dRow = ds.Tables[0].Rows[inc];

txtFirstName.Text = dRow.ItemArray.GetValue(1).ToString();
txtSurname.Text = dRow.ItemArray.GetValue(2).ToString();
txtJobTitle.Text = dRow.ItemArray.GetValue(3).ToString();
txtDepartment.Text = dRow.ItemArray.GetValue(4).ToString();

}

Now add a call to NavigateRecords from your Form Load event:

MaxRows = ds.Tables[0].Rows.Count;

NavigateRecords( );

The whole of your code should now look like this:

You can try out your code, now. Run our programme and you should find that the first record from the database table appears in your textboxes:

In the next lesson, you'll add some buttons to the form so that you can scroll forwards and backwards through all the records in the databas table.

Creating a Database Connection Class

 In order to connect to our database, we can create a class to handle all the connection issues.
From the Project menu at the top of Visual Studio, select Add Class:

You'll see the following dialogue box appear:

Make sure the Class item is selected. In the Name box at the bottom, type DatabaseConnection.cs. Then click the Add button at the bottom.
When you click Add, your new class will appear in the Solution Explorer on the right:

The code stub for your class will be open in the main window (if it's not, simply double click DatabaseConnection in the Solution Explorer):

(2010 users won't have the Threading.Tasks using statement.)
The class we're going to create, as its name suggests, will help us to connect to a database. We'll add fields, properties and methods to the class.
The first thing to do is to set up two field variables:

private string sql_string;
private string strCon;

Add the two lines above to your class code and it will look like this

The sql_string field variable will hold a SQL string like "SELECT * FROM table". We'll get this from the Settings we set up earlier. The strCon field variable will hold a location of the database. Again, we'll get this from the Settings we added.
The next code to add is a write-only property (meaning it has no get part):

public string Sql
{

set { sql_string = value;

}

We've called this property Sql. It's going to be a string. It's public because we want to access this property from outside of this class. The value (the SQL itself) will be assigned to the sql_string field variable. We're saying, set the variable called sql_string to whatever is held in the Sql variable on line one.
The next bit of code sets something into the strCon field variable. This something is our connection string, the one we set up on the Settings page. Again, this is a write-only property.

public string connection_string
{

set { strCon = value; }

}

When you've added the two properties above, your code should look like this:

The next thing we need is something called a DataSet. Think of a DataSet as a grid that holds the data from our table. Each row in the DataSet grid will hold a row from our database table. The table is then closed, and the rows and columns in the DataSet is manipulated with code. In other words, you load table data into a DataSet, rather than manipulate the table in the database.
Add this code to you class:

public System.Data.DataSet GetConnection
{

get { return MyDataSet( ); }

}

We've called this property GetConnection. This is a read-only property. For the get part we're calling a method with the name MyDataSet. The method connects to the database and fills a Dataset. It's this method that does all the work. Here's the code stub to add:

private System.Data.DataSet MyDataSet( )
{

}

The method is called MyDataSet. Instead of it being a string or an integer it's of this type:

System.Data.DataSet

So the type is DataSet. The inbuilt DataSet code lives in System.Data.
The first line to add to the new method is this rather long line:

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection( strCon );

The variable we've created is called con. This variable is of type SqlClient.SqlConnection. Again, the SqlClient.SqlConnection code lives in System.Data. We're creating a new object of this type. In between the round brackets of SqlConnection we have our strCon variable. So SqlConnection will use our connection string in strCon to connect to the database.
The next line to add is this:

con.Open( );

Here, we open a connection to the database.
However, although we have opened a connection to the database, we haven't yet opened the table in the database. To do that, we need something called a DataAdapter. A DataAdapter is used to open up a table in a database. We'll need to reference this DataAdapter later in our project, so we'll need to add a new line to the top of the Class. Add the following line just below the sql_string and strCon variables:

private string sql_string;
private string strCon;
System.Data.SqlClient.SqlDataAdapter da_1;

The variable we've set up is called da_1. It's of type SqlClient.SqlDataAdapter.
Go back to your MyDataSet method and add this line:

da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);

In between round brackets we have our sql_string variable. This is used to tell C# which records we want from the table. (We're going to be passing it that Settings string we added earlier.) After a comma, you need a connection object. This tells C# which database the table is in.
The next line is this:

System.Data.DataSet dat_set = new System.Data.DataSet( );

This sets up a DataSet object. The variable we've created is called dat_set. The whole method is going to be returning this DataSet, which will hold all the records from the table.
The final three lines are these:

da_1.Fill(dat_set, "Table_Data_1");

con.Close( );

return dat_set;

The first of those lines uses the Fill method of our DataAdapter object. This is used to fill a DataSet. In between the round brackets of Fill you need the name of a DataSet to fill. After a comma, you can add a name for the Fill, if you want. We've added one called Table_Data_1.
The final two lines close the connection object, and return the DataSet.
The whole of the code for your class should look like this:

Save your work and we'll put this new class to use.