Basic Introduction to LINQ and .NET Windows Forms programming

First, install Visual C# 2008 Express Edition and SQL Server Express 2005 Edition.

For this brief introduction, we will try to create a very simple application that lists out some bank accounts, make deposits/withdrawals and account transfer transactions. It assumes basic knowledge of working with Windows Forms already. We will now create a new blank Windows Forms Application project by clicking on File | New Project. Let’s name it BankTellerApp.

Figure 1

The next step should be associating the application with a database. For this demonstration, we will use User Instance SQL Server Express Data Files. You can find the .MDF and .LOG file here. Download it and save it to a folder of your choice. After that, click on Tools | Connect to Database… to add a new database connection to the Database Explorer in Visual C#.

Figure 2

First, select “Microsoft SQL Server Database File” and press Continue as in Figure 2. Next, browse for the downloaded MDF file. Press “Test Connection” to confirm that SQL Server Express is running and valid. Once it is ok, press “OK” to create the database connection (Figure 3).

Figure 3

You shall see a window similar to the one in Figure 4. This Data Connection is useful for drag-and-drop creation on LINQ classes inside the IDE, which is a very powerful feature of Visual Studio 2008.

Figure 4

Once we have the data connection setup, create a new LINQ to SQL class, by clicking on Project | Add New Item. Select “LINQ to SQL Classes”. Name the file as BankDB.dbml (Figure 5).

Figure 5

You should see a blank design surface for the LINQ Object Relational Editor. After that, expand the “BankAccounts.mdf” node in Database Explorer You can see the tables and other objects of this database. (Figure 6).

Figure 6

Now, drag the tables tblAccountBalance and tblAccounts into the Object Relational Designer surface. The IDE will automatically create classes to read/write to these database tables automatically. The IDE may ask whether you want to copy the MDF to the output directory. Specify yes - this means that the database will be overwritten with a new copy everything the application is run – be sure to understand the implications of this setting so that you do not get confused that data is not properly committed to the database after you exit the application!

A screen similar to Figure 7 should appear.

Figure 7

Since we areusing C#, we would like to have better named classes and so, we rename the classes tblAccountBalance and tblAccount to AccountBalance and Account, respectively. Click on each of the table object and modify the name in the Properties Window, as follows (Figure 8):

Figure 8

The designer surface should reflect the new name change and save the file BankDB.dbml. Congratulations, we have just added two new classes Account and AccountBalance to the project, ready to be used for database retrieval and update.

.

Figure 9

We will now proceed to develop two forms: one that displays a list of existing accounts and another that displays the account details. We will see that Visual Studio has done a lot of “behind-the-scenes” work to make this happen seamlessly without much coding effort. Let’s make a form that displays all the existing accounts Before that, delete the existing Form1.cs in the Solution Explorer. Next, click on Project | Add New Item…, select Windows Form. Name the form as AccountListForm. Do the following steps:

1. Set the form title to “Bank Accounts”.

2. Drop a panel and dock it to the bottom of the form.

3. Add a button to the panel, named btnShow with the text “Show”.

The form should look like Figure 10:

Figure 10

Once we have the form ready, we add a new data source to list the existing accounts. Click on Data | Add New Data Source. Specify Object and click Next. Choose the BankTellerApp.Account as the Data Source(Figure 11) . Click Next and finally click Finish.

Figure 11

Press Shift+Alt+D to show the newly created data source (Figure 12).

Figure 12

We can see that there are controls associated with each object. Drag the Account object (with the datagrid icon) from the Data Sources window to the AccountListForm design surface. Delete the generated navigator control and set the accountDataGridView properties as follows:

l Dock: Fill

l SelectionMode: FullRowSelect

Right-click the accountDataGridView and select “Edit Columns…”. Remove the last three columns and leave only the AccountID and AccountName columns. Set the width of AccountName to 200.

The form should look like Figure 13:

Figure 13

Next, we need to create code to retrieve the existing accounts. Right click on the BankDB.dbml in the Solution Explorer and select “View Code…”.

At the top, add the line “using System.Linq;” and add the following code to BankDB.cs

partial class BankDBDataContext

{

        public Account[] GetAllAccounts()

        {

            return (from acc in Accounts select acc).ToArray();

        }

}

What does this function do? It is a LINQ expression that tries to select all account records from the database and convert it to an array of Account[]. Sounds too simple? Now, we go back to AccountListForm.cs and attach Load Event to AccountListForm. Use the following code:

private void AccountListForm_Load(object sender, EventArgs e)

{

     BankDBDataContext db = new BankDBDataContext();

     accountBindingSource.DataSource = db.GetAllAccounts();

}

Before we execute the application, make sure that Program.cs is updated properly to create AccountListForm instead of Form1, which was deleted. Once this is done, press F5 to launch the application. We will see something like Figure 14:

Figure 14