Basic Syntax

Most of the work you’ll be doing here involves object manipulation, not complex object creation, so you don’t need to know the entire C# language to get started. There are some key differences between VB6 and C# that are helpful to be aware of up front. These will be briefly covered here and also in more detail as they come up in the code examples throughout the book.

C# Operators

These can take some time to get used to. The standard Boolean operations that you may have been used to in Classic VB are sometimes the same and sometimes slightly different in C#. In Table 1-1, you will see the VB6 Operator and the C# Operator. Having compile errors due to using the VB-style operators is easy to fix when you know about it.

Table 1-1. The differences are in the equality and inequality operators. Be careful to use the == when you are testing for equality and = when you are trying to set a value.

Operator Name

VB6 Operator

C# Operator

Equality Operator

=

==

Inequality Operator

<>

!=

Greater Than

>

>

Less Than

<

<

Greater Than or Equal

>=

>=

Less Than or Equal

<=

<=

Outside of the Boolean operators, there are some other slight differences in operators that can save you some time. The first is the increment operator. In VB, you might have done something like:

X = X + 1

Whereas in C#, you can use:

X + = 1;

The addition, subtraction, multiplication, and division increment operators are +=, –=, *=, and /=. So, anywhere that you would use something like X = X (operator) Y, you can use these as shortcuts.

In addition, there are a few other operators that can help you with intense data operations that were not in Classic VB. For example, if you have a situation where you are trying to evaluate an OR expression and each side of the OR expression is data- and processor-intensive, you can use the || operator. Doing this will only evaluate the expressions until it gets a true; once an expression returns true, the statement returns true and the rest of the expressions are not evaluated. In non-processor-intensive operations, you won’t get much time savings from this. But, when you are looking at thousands of rows of data for potentially thousands of customers, you might be able to use this operator to save some time. These aren’t the only operator changes, however, these are the ones that are relevant to the examples in this book.

Selection Statements

The other changes that can take some getting used to are the selection statements. In Classic VB, we had If ... Then ... Else and Select ... Case. In C#, we have if ... else and switch ... case. Let’s assume that we have an integer variable called count that we are trying to evaluate and we have a string variable called reply that we want to populate with a message. See if you can spot the differences compared to VB for both statements:

If (count == 0) {
   reply = "The count is 0";
}
else {
 reply = "The count is not 0";
}
switch (count) {
  case 0:
  reply = "The count is 0";
break;
default:
 reply="The count is not 0";
break;
}

Notice that in VB, we would have had to use the Then keyword, which is not used in C#. Also, where we would use Select ... Case in VB, we have to use switch ... case. In addition, in VB, we have a capital letter at the beginning of the keywords, whereas in C#, they are in all lowercase. Finally, take note of the braces and semicolons that you don’t use in VB. Again, these differences certainly stand out in terms of how they look, but once you write a few statements, you will easily pick up on them.

There are many other differences between the languages—I highlighted these examples because they are often used in data-intensive applications. You can get a full list of operators, keywords, and statements in the help that comes with Visual Studio. Also, the Intellisense in Visual Studio is fantastic and can greatly help you, and the error window also gives surprisingly good help, particularly when you are missing a curly brace, semicolon, or an includes statement.

If you are used to working in Microsoft Access, you can get spoiled by things that are done for you automatically. It is pretty straightforward to make a form in Access that will let you add, update, and delete records. In addition, changing the source data for a grid can really be accomplished with one line of code in VBA. But, building the same functionality from a C# application take some work. Even if you get all of the syntax correct, you have to be careful where you declare objects in C#, where you initialize them, etc. Once you realize where things need to be done, it becomes very easy, and you’ll move quickly up the learning curve.

For this example, we will be showing the screens from Visual Studio 2010 Express, but the code doesn’t change if you use a different version. In addition, we will be using the Northwind Database that comes with Access. Using the Northwind Database poses some challenges that you will run into when using databases where you don’t have control of the schema. These instances will be pointed out and you’ll learn how to handle them.

Note

If you don’t have Access or the Northwind Database, you can download the database from the Microsoft website.

First, open up Visual Studio and go to File→New Project. Pick Visual C# and select Windows Forms Application. On the bottom of that dialog box, type in EditingDatabaseTest and then click OK, as shown in Figure 1-4. Once you do that, you will see the screen shown in Figure 1-5.

The New Project window

Figure 1-4. The New Project window

Editing your project

Figure 1-5. Editing your project

To start with, we’ll recreate more or less what Access does automatically when you build a form. You will fill a grid with data, add buttons to filter the data, and have a second grid that will let you choose different tables with which to populate the first grid. In addition, you will be adding code to allow you to add, update, and delete rows of data. While this seems pretty simple, you’ll see that there is some planning involved to make this work.

Take a look at the toolbox on the left side of your screen. (If the toolbox isn’t there, go to View→Toolbox to show it.) Take notice of the sections—you will be using controls from the Common Controls and Data sections for this sample. On the form, drag on a datagrid from the Data section, a text box from the Common Controls section, a combo box from the Common Controls section, two buttons from the Common Controls section, and a second datagrid from the Data section. When you add the datagrid, you will get the popup dialog shown in Figure 1-6. For the first datagrid, leave the boxes checked to add, update, and delete records. For the second datagrid, uncheck those boxes. On both, leave the datasource as None. You can create a project datasource and use it here, but we are going to start with programming the datasource because it will give you more flexibility. You can lay out these controls however you’d like; you can see how I did it in Figure 1-7. If you pressed F5 to start the project, it would open up and nothing would function yet.

Choosing data sources for the datagrid

Figure 1-6. Choosing data sources for the datagrid

An initial form layout

Figure 1-7. An initial form layout

Next, you will need to put in some code to get the controls functioning. You can get to the code for a Form by pressing F7, or you can right-click on the form’s name in the Solution Explorer on the righthand side of your screen and select View Code from the list. Once there, you will see the lines of code shown in Example 1-1 prefilled for you.

Example 1-1. Basic code to make the controls function

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace EditingDatabaseTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


    }
}

The first thing you’ll notice in the code is the using keyword. These lines of code are very similar to adding a reference in VBA. When you add a using directive, it turns on the Intellisense for the objects, properties, and methods related to that namespace. Please note that you can and often do have to add references to a C# project; I’m only describing it this way to give you a familiar example.

There is an additional using directive that you will need to add for this example to work. Right under using System.Data;, add the following line of code:

using System.Data.OleDb;

This line of code tells C# to use the .NET Framework Provider for OLE DB. You will use objects, properties, and methods in this namespace to connect to the datasource. Also, you need some of the variables and objects that you are using to remain available continuously while the form is open. For this reason, you need to declare those at the class level and not in the individual procedures that you will be writing. Add the necessary lines to have your code read as shown in Example 1-2.

Example 1-2. Connecting to the datasource with OLE DB

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace EditingDatabaseTest
{
    public partial class Form1 : Form
    {
        public string connString;
        public string query;
        public OleDbDataAdapter dAdapter;
        public DataTable dTable;
        public OleDbCommandBuilder cBuilder;
        public DataView myDataView;

        public Form1()
        {
            InitializeComponent();
        }


    }
}

You need the objects and variables that are declared to remain available because these will be necessary for the updating, sorting, filtering, and other operations that you’ll program. If you didn’t declare them at the class level, those objects won’t be available outside of the procedure in which they were declared. After the InitializeComponent(); statement, add the following lines of code:

            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data       
Source=C:\\users\\michael\\documents\\Northwind 2007.accdb";
            query = "SELECT * FROM Customers";
            dAdapter = new OleDbDataAdapter(query, connString);
            dTable = new DataTable();
            cBuilder = new OleDbCommandBuilder(dAdapter);
            cBuilder.QuotePrefix = "[";
            cBuilder.QuoteSuffix = "]";
            myDataView = dTable.DefaultView;

The connection string is very similar to what you would see in VBA. However, you should notice the \\ in the path name. If you use a single \, you will get an unrecognized escape sequence error. The query variable is a string that defines the Select statement that you are using to access the data. The OleDbDataAdapter is the class that holds the data commands and connection that you will use to fill the DataTable. The OleDbCommandBuilder class generates the commands that reconcile changes that happen in a DataTable and the connected database.

Since you are connecting to the Northwind Database, you need the QuotePrefix and QuoteSuffix properties defined with the square brackets. This is because the Northwind Database has spaces in the field names. If you try to update a cell in your datagrid that has spaces in field names without these properties defined, you will get an error. You can always trap that error, but it would make updating impossible in tables with spaces in field names. If you don’t add these properties and your datasource doesn’t have spaces in field names, you will still be able to run error-free. However, I recommend always adding these lines just in case. Next, add the following lines of code to finish up this first procedure:

            dAdapter.Fill(dTable);
            BindingSource bndSource = new BindingSource();
            bndSource.DataSource = dTable;
            this.dataGridView1.DataSource = bndSource;
            for (int q = 0; q <= dataGridView1.ColumnCount - 1; q++)
            {
                     this.comboBox1.Items.Add  (this.dataGridView1.Columns[q].HeaderText.ToString());
            }
            OleDbConnection xyz = new OleDbConnection(connString);
            xyz.Open();
            DataTable tbl = xyz.GetSchema("Tables");

            dataGridView2.DataSource = tbl;
            DataView tbl_dv = tbl.DefaultView;

You are accomplishing several things with this code. First, you are filling the DataTable with the data in the data adapter. Then, you are creating a binding source for the form. (The BindingSource class is part of the System.Windows.Forms namespace.) Then, you are finally ready to set the datasource for the datagrid. Once you do this, the data you selected will populate the grid.

The next part of the code is a for loop, which is being used to populate the combo box with the field names. The code isn’t going to do anything with this data, but you could use that to set the sort field or do any other number of tasks. It is being included here simply to show you an example of how to iterate through the columns of a datagrid.

Finally, the bottom section of that code snippet is being used to populate the second datagrid with the schema of the OleDbConnection.

A populated datagrid

Figure 1-8. A populated datagrid

If you press F5 at this point, the form will open and you will see the screen in Figure 1-8. Because you told the first datagrid that it could add, update, and delete, you will be able to edit those fields. But, you didn’t add code yet to reconcile those changes in the database. So, you can edit the field and everything will show on the screen like it is changed; however, if you close the form and open it again, the changes will not be in the database. Also, you will notice that the bottom datagrid cannot be edited. This is because you unchecked the boxes. The important thing to note here is that those settings only impact the grid; they do not impact the database. If you search for help on datagrids in C# online, you will see many questions from people who made the change in the grid but didn’t add the code to apply the updates—they can’t understand why the data isn’t being changed in the database.

So, let’s add the code for updates. Add this code right below the curly brace ending the Form1() procedure:

private void Cell_Update(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                dAdapter.Update(dTable);
                this.textBox1.Text = "Updated " +  System.DateTime.Now.ToString();
            }
            catch (OleDbException f)
            {
                this.textBox1.Text = "Not Updated " + f.Source.ToString();
            }
        }

Once you do this, you need to set up the grid to call this procedure. Switch to the design view screen (Shift+F7), right-click on the first datagrid, and select Properties. On that box, click on the lightning bolt to get to the events and find the event called RowValidated. In that event, select Cell_Update from the drop-down box. It should be the only item available in the list at this time.

When you are creating an application, the last thing you want to do is have your users get dropped to a debug window or throw an unhandled exception. So, what I’ve done in this section of code is put the code that does the updating in a try ... catch statement. You could accomplish that update in one line of code: dAdapter.Update(dTable);. However, that code can throw an error for any number of reasons. For example, you could be updating a table that doesn’t have a primary key defined (that will always throw an error), or you might have skipped the step where you define the QuotePrefix and QuoteSuffix on the command builder and you have a table with spaces in field names. So, when that happens, you want the code to handle that exception gracefully. In this case, the code will try to execute that line and if it works, it will update the text box telling the user that it updated. If there is an OleDbException, it will update the text box, telling the user that it wasn’t updated. The grid will also show a red X on the left side of the row that didn’t update. Note that you are only trapping an OleDbException. You can trap all exceptions instead of defining one, but it is best to write specific sections of code to handle each type of error you may get.

The other item to note is the dAdapter variable. If you declare that variable in the Form1() procedure, it will run fine when the application first starts running, but it will give you an error when writing the update section of code because the dAdapter variable will be out of context.

Adding Filtering

The next thing you are going to program here is the filtering functionality. Go back to the design view on the form and change the button text for the buttons to be Set Filter and Clear Filter. Then come back to the code window and we’ll add the procedures for this functionality.

There are a number of ways that you can add filtering functionality. What you’ll do here is essentially the filter by selection functionality from Access, but we will default to using the entire field. You can do wildcards and such, but for now, we will focus on the basics. Enter the following code below the update procedure:

private void filter_click(object sender, EventArgs e)
        {
            string mystr;
            if (myDataView.RowFilter == "")
            {
                mystr = "[" + dataGridView1.CurrentCell.OwningColumn.HeaderText.ToString() + "]";
                mystr += " = '" + dataGridView1.CurrentCell.Value.ToString() + "'";
                myDataView.RowFilter = mystr;
            }
            else
            {
                mystr = myDataView.RowFilter + " and ";
                mystr += "[" + dataGridView1.CurrentCell.OwningColumn.HeaderText.ToString() + "]";
mystr += " = '" + dataGridView1.CurrentCell.Value.ToString() + "'";
                myDataView.RowFilter = mystr;
            }
        }

A couple of things are important here. First, there is a line of code checking to see if the grid is already filtered. If the grid is filtered, clicking the filter button again adds to the filter. If the filter is empty, the code just sets the filter. Second, since we are not using the OleDbCommandBuilder class here, the brackets will not be added to our column names automatically. So, you just need to add the square bracket to the front and back of the column name. Finally, you should take a look at all of the properties and methods that are available on the CurrentCell. In this case, you are referencing the OwningColumn of the cell and the HeaderText of that column; the HeaderText is the same as the field name in the table. Also, as in the other procedure, the object that we are referring to (myDataView in this case) is declared at the class level, so it is available to all procedures in the form.

Next, you will want to set this code to run when the Set Filter button is clicked. So, go back to the design view and right-click on the first button (you should have already set the Text property to Set Filter), then click on the lightning bolt to show the events. Find the click event and in the drop-down box, select filter_click.

Go back to the code view and add the following lines of code underneath the filter_click procedure:

private void clear_filter(object sender, EventArgs e)
        {
            myDataView.RowFilter = "";
        }

Switch back to the design view and set the click event for the second button to clear_filter, just like you did for the first button. You may notice that only clear_filter and filter_click are available when you have another event already programmed for the row updating. This is because the Cell_Update procedure is specific to DataGridViewCellEventArgs, so it will only show up for datagrid events.

Once you have done this, press F5, and when the form opens, click in the first cell under Job Title, which should say owner. Then click on the button to Set Filter. You will see a form like the one shown in Figure 1-9.

Setting a filter

Figure 1-9. Setting a filter

If you click Clear Filter, it will remove the Filter. This functionality is fairly simple, but you can see how actually programming it is a bit complex. It wouldn’t make sense to go through all of this if all we wanted to do was edit a static table. If you wanted to do that, you could create a project datasource, which would set the code to allow updates, adds, deletes, etc. So, what I’m trying to show here is how you can select a different table and populate the first datagrid.

Your next task is to add another button to the form and call it Change Source. Add the following code below the last procedure you wrote:

private void change_data_source(object sender, EventArgs e)
        {
            string tbl_str = dataGridView2.CurrentRow.Cells[2].Value.ToString();
            query = "SELECT * FROM [" + tbl_str + "]";
            dAdapter = new OleDbDataAdapter(query, connString);
            dTable = new DataTable();
            cBuilder = new OleDbCommandBuilder(dAdapter);
            cBuilder.QuotePrefix = "[";
            cBuilder.QuoteSuffix = "]";
            myDataView = dTable.DefaultView;
            dAdapter.Fill(dTable);
            BindingSource bSource = new BindingSource();
            bSource.DataSource = dTable;
            this.dataGridView1.DataSource = bSource;

            for (int q = 0; q <= dataGridView1.ColumnCount - 1; q++)
            {
                this.comboBox1.Items.Add(this.dataGridView1.Columns[q].HeaderText.ToString());
            }

        }

This is essentially the same code as our opening code except that we are setting the table name equal to the third column of the schema grid. Please note that the columns of the grid are 0-based, so the third column has an int index of 2. Once you’ve done this, go back to the design view and set the click event to change_data_source. Your final form should look like the one shown in Figure 1-10.

Form with an added Change Source button

Figure 1-10. Form with an added Change Source button

Some Other Considerations

You should be aware of some errors that you will see with the datagrid, particularly with the Northwind Database. If you try to add records to some tables, you will see a red exclamation point to the left of the row, and if you hover over it, you will see “An INSERT INTO query cannot contain a multi-valued field.” This is because some of the tables in the Northwind Database take advantage of an Access-only feature of storing more than one value in a field (for example, multiple examples from a list). Since you won’t be able to insert records into the database if your table has a field like that, I would avoid it if you are planning on updating outside of Access.

Let’s take a look at a situation where you can update. In the second datagrid, click on the cell that says Invoices (you will need to scroll down), and then click the Change Source button. The data in the first datagrid will change to show the Invoices table. Then scroll down to the bottom and try to add a new row. Use 125 as the Order ID and use 7/1/2011 as the Invoice Date, then put zeroes in the columns with numbers. Then tab down to the next row or click off the row that you are trying to add.

When you do this, you will get a red exclamation point. When you hover over it, it will tell you that you need a related record in the table Orders. So, change the Order ID to 58 (which exists in the Orders table), and then click off the row. You will see that the update works. Then, click on Inventory Transactions (right above Invoices in the bottom grid) and click on Change Source. Then, go right back to Invoices and hit the Change Source button again. If you scroll to the bottom, you will see the row of data that you added and you’ll see that the database added the primary key automatically.

Now, you can try to delete that row. Click on the space right to the left of the first column on that row that you added. This will highlight the row. Now press the Delete key. This deletes the row from the database.

You can see from this example that while this is slightly more complicated than doing the same thing in Access, once you have the pattern down, it is relatively straightforward to add a datagrid and change the datasource, filter, etc.

Example 1-3 provides the full code listing for the example in this chapter.

Example 1-3. Putting all of the code together

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace EditingDatabaseTest
{
    public partial class Form1 : Form
    {
        public string connString;
        public string query;
        public OleDbDataAdapter dAdapter;
        public DataTable dTable;
        public OleDbCommandBuilder cBuilder;
        public DataView myDataView;

        public Form1()
        {
            InitializeComponent();
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\\users\\michael\\documents\\Northwind 2007.accdb";
            query = "SELECT * FROM Customers";
            dAdapter = new OleDbDataAdapter(query, connString);
            dTable = new DataTable();
            cBuilder = new OleDbCommandBuilder(dAdapter);
            cBuilder.QuotePrefix = "[";
            cBuilder.QuoteSuffix = "]";
            myDataView = dTable.DefaultView;
            dAdapter.Fill(dTable);
            BindingSource bndSource = new BindingSource();
            bndSource.DataSource = dTable;
            this.dataGridView1.DataSource = bndSource;
            for (int q = 0; q <= dataGridView1.ColumnCount - 1; q++)
            {
                this.comboBox1.Items.Add(this.dataGridView1.Columns[q].HeaderText.ToString());
            }
            OleDbConnection xyz = new OleDbConnection(connString);
            xyz.Open();
            DataTable tbl = xyz.GetSchema("Tables");

            dataGridView2.DataSource = tbl;
            DataView tbl_dv = tbl.DefaultView;
        }

        private void Cell_Update(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                dAdapter.Update(dTable);
                this.textBox1.Text = "Updated " + System.DateTime.Now.ToString();
            }
            catch (OleDbException f)
            {
                this.textBox1.Text = "Not Updated " + f.Source.ToString();
            }
        }

        private void filter_click(object sender, EventArgs e)
        {
            string mystr;
            if (myDataView.RowFilter == "")
            {
                mystr = "[" + dataGridView1.CurrentCell.OwningColumn.HeaderText.ToString() + "]";
                mystr += " = '" + dataGridView1.CurrentCell.Value.ToString() + "'";
                myDataView.RowFilter = mystr;
            }
            else
            {
                mystr = myDataView.RowFilter + " and ";
                mystr += "[" + dataGridView1.CurrentCell.OwningColumn.HeaderText.ToString() + "]";
                mystr += " = '" + dataGridView1.CurrentCell.Value.ToString() + "'";
                myDataView.RowFilter = mystr;
            }
        }

        private void clear_filter(object sender, EventArgs e)
        {
            myDataView.RowFilter = "";
        }

        private void change_data_source(object sender, EventArgs e)
        {
            string tbl_str = dataGridView2.CurrentRow.Cells[2].Value.ToString();
            query = "SELECT * FROM [" + tbl_str + "]";
            dAdapter = new OleDbDataAdapter(query, connString);
            dTable = new DataTable();
            cBuilder = new OleDbCommandBuilder(dAdapter);
            cBuilder.QuotePrefix = "[";
            cBuilder.QuoteSuffix = "]";
            myDataView = dTable.DefaultView;
            dAdapter.Fill(dTable);
            BindingSource bSource = new BindingSource();
            bSource.DataSource = dTable;
            this.dataGridView1.DataSource = bSource;

            for (int q = 0; q <= dataGridView1.ColumnCount - 1; q++)
            {
                this.comboBox1.Items.Add(this.dataGridView1.Columns[q].HeaderText.ToString());
            }

        }


    }
}

Before we head to the next chapter and connect to SQL Server, let’s review some of the differences between data access inside of Microsoft Access and from C#. One of the biggest challenges is setting the events to fire at the right time and declaring the variables in the right place. In this example, it was done for you. But when you are writing from scratch, it is easy to get this part wrong. You’ll know when it happens when you try to access a variable that Visual Studio says is out of context. So, when that happens, you’ll know exactly where to look.

The events are a little trickier. As an example, some people will go through the events that are available in the datagrid, and they might choose an event like CellEndEdit to put the update code in. However, you will end up with errors when you try to add new rows because you will be missing required fields when the update fires right after the first column is updated. If you look around some of the technology forums, you’ll see some debate about where to fire the update event. My personal opinion is that doing it after the row validates is best, as it will only fire when you leave a row. You also have the option of having a Save button and only firing the updates when that button is pressed. The point is that you have options for when you call events and you can test them to see where it works the best in your particular application.

The final item of importance is to understand when you are dealing with an object or control that is in the Windows Forms namespace or the System.Data namespace, and when you are in the System.Data.OleDb namespace. There are times when you may want to try something, but you can’t find the object or method that you want. When you run into situations like that, all you need to do is hover over the class name where you declare the variable and it will tell you what namespace that class is in.

Get C# Database Basics now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.