.NET Basics for the Database Developer: Take the Plunge

.NET Basics for the Database Developer: Take the Plunge

any MS Access and SQL Server developers are moving to .NET. It’s a natural migration because these developers can use .NET to create the same types of solutions they would in either database, but there’s much more to .NET than your ordinary database. After all, .NET is not a database?it doesn’t store data?but a programming framework. This short introduction to the mysterious world of .NET shows Access and SQL Server developers how to use a development tool, the freely available Visual Basic Express (VB Express), to create connections to data and a user interface to manipulate that data.

Learning .NET is a bit like being caught up in a tornado and thrown to parts unknown. Don’t worry if you feel a bit lost at first, because .NET is an entirely different reality for most beginners who are coming from the database development realm. Like Dorothy’s Oz, you might find the journey a bit intimidating at first. However, before long you’ll see .NET for the beautiful set of tools it really is. Once you’re acclimated, you won’t be in such a hurry to click your heels and transport back home. Most likely, you’ll want to stick around to learn more.

Microsoft .NET is here to stay and there’s no time like the present to take the plunge.

What Is .NET?

Before jumping right in to .NET, you should know a few terms, beginning with a general definition of .NET. The .NET Framework is a class library (Framework Class Library, or FCL), a runtime host, and a collection of utilities. The FCL contains all the classes that .NET makes available to your application. You don’t have to write them yourself. The runtime host protects everything by managing permissions, digital certificates, and so on. The runtime host is also the component that lets you write a VB.NET application that references code written in other languages, such as C#. In addition, the runtime host cleans up after you by reclaiming memory from unreferenced objects. Several utilities come with the framework, including compilers, debugging tools, and so on.

Some other new terms you’ll encounter include:

  • Framework: An environment that provides an interface. In this case, the .NET Framework is the environment that provides a new interface to Windows services and the Windows API.
  • Project: An assembly in a .NET solution. A solution can contain multiple projects, each of which can be a Windows Forms application, a class library, or a console application.
  • Solution: The application container, which has a startup project that the developer sets. Think of the multiple projects in a solution as roughly equivalent to the groups in the MS Access Visual Basic Editor, where you see report modules, form modules, class modules, and so on. The startup project in a larger solution usually isn’t the one that contains the table adapters or the user interface or the reports or a lot of other things. The startup project is the entry point to the application. The startup project discussed this article is a Windows Form application.

These definitions will become more relevant as the article progresses.

Connecting To and Retrieving Data

If you’re coming from Access or SQL Server, you’ll appreciate VB Express’ connection wizard because it performs all the grunt work for you. The following quick example walks through connecting to and retrieving data from Northwind (a sample database that comes with Access):

    Creating a New Project

  1. Launch VB Express and click New Project on the Standard toolbar to start a new project. Click Windows Form Application in the resulting dialog box.
  2. Enter a meaningful name in the Name control, as shown in Figure 1. Using this method, VB Express creates a new directory off the default directory using the project’s name. If you want to save the project in a directory other than the default directory, repeat Step 1 but click Save All instead of New Project.
    Figure 1. Give Your Project a Meaningful Name: Enter a meaningful name in the Name control.
  3. Click OK. The Solution Explorer to the right contains files. By default, each new project contains a “My Project” item and a form, named Form1. MS Access developers might be tempted to relate VB Express components in the IDE to Access .mdb files, but that really isn’t an accurate comparison. You can think of .NET as an .mdb file without walls or boundaries, but you can reuse a project in another solution by just adding it to the new project. You can’t do that with .mdb files.

    Connecting to the Database

  4. To make a connection to the MS Access Northwind database, click the Data Sources tab (bottom right) in Solution Explorer. If the tab isn’t available, choose Show Data Source from the Data menu.
  5. Click Add New Data Source to launch the Data Source Configuration Wizard.
  6. Click Next as the Database option is the default.
  7. Click New Connection in the next pane.
  8. In the resulting Add Connection dialog box, click Change and choose Microsoft Access Database File from the Change Data Source dialog box (see Figure 2). Then click OK.
  9. Click Browse in the Add Connection dialog box and locate Northwind.mdb (in the Samples folder of your Office directory) as shown in Figure 3. If the database is password protected, enter the password. Northwind.mdb isn’t password protected, so you don’t need to enter anything this time. Click OK.

    Figure 2. Specify the Database Type: Click Change, and choose Microsoft Access Database File from the Change Data Source dialog box.
    Figure 3. Identify the Database: Click Browse in the Add Connection dialog box and locate Northwind.mdb.

  10. Click Test Connection and then click OK to clear the confirmation message.
  11. Figure 4. VB Express Asks You If You Want to Copy the Database: Decide whether to copy the entire database or just the data you need.
  12. With the connection working, click OK to return to the wizard, and click Next to continue.

    Retrieving the Data

  13. At this point, the wizard needs you to identify the data. VB Express asks you if you want to copy the database (see Figure 4). If you click Yes, it will copy the entire database. Only click Yes if you truly mean to copy the entire database into your project. This time, click No.
  14. The next pane lets you save the connection string in the configuration file. The default is Yes, and most of the time, this is the best choice. Click Next.
  15. The wizard will retrieve the data objects in the connected database and display them in the source file. This is where you identify the data you want to retrieve and manipulate in your .NET project. Click the plus sign (+) to expand the Tables node. Next, check the Products table, as shown in Figure 5. Notice that VB Express supplies a default name for the dataset. Most of the time, this name is adequate, but you can change it.
  16. Click Finish and VB Express will retrieve the specified data. Figure 6 shows the NorthwindDataSet in the example project.

    Figure 5. The Products Table: Identify the tables and views you want to include in your .NET project.
    Figure 6. The Wizard Connected to Northwind’s Products Table: Here is the NorthwindDataSet in the example project.

  17. Choose Save All from the File menu and then click Save. If you didn’t specify a specific directory earlier, you can do so now. Most of the time, the default directory is adequate during development.

Click the Solutions Explorer tab to see the changes (see Figure 7). The Solutions Explorer contains two new files: app.config and NorthwindDataSet.xsd. Simply double-click them to see the actual files. Figure 8 shows app.config with the connection string highlighted. This file contains the code the wizard created. Figure 9 shows the graphic representation of the data source.

Figure 7. The Solutions Explorer Tab: After connecting to Northwind.mdb, the Solutions Explorer contains two new files.
Figure 8. App.config with the Connection String Highlighted: This code connects to the data source, Northwind.mdb.
Figure 9. A Familiar Representation of the Data Source: VB Express shows a graphic representation of the data source.

Designing a Form

After you’ve established a connection and retrieved data, you’ll want to display that data. VB Express offers form objects similar to MS Access. To display data from Northwind’s Products table, do the following:

  1. Click the Data Sources tab.
  2. Click the Form1 tab (if necessary) and drag the Products table from the Data Sources tab to Form1 in Form Designer. VB Express will generate a DataGridView control and name it accordingly, using the selected item’s name. Figure 10 shows the resulting form and grid after being resized to show most of the fields. In addition, VB Express adds several components to the components tray (below the form).
  3. Press F5 to run the application and display data (see Figure 11).

    Figure 10. A Control to Display the Data: Here is the resulting form and grid resized to show most of the fields.
    Figure 11. Pressing F5 Runs the Application: Press F5 to run the application and display data.

With just a few clicks, you’ve displayed data from a foreign data source! And it gets better, because the form behaves like any other Windows form.

Use the navigational tools at the top to perform basic tasks such as inserting, deleting, and modifying records. However, if you modify data, you must also click Save Data to update the actual data source via the connection. Without writing a single line of code yourself, you connected to a data source and displayed it, thanks to .NET.

If you’re familiar with MS Access, a lot of what you just did might seem familiar. MS Access provides wizards that do a lot of work for you. However, like MS Access, VB Express wizards aren’t always the best route to creating the most efficient .NET application. They are used here just to get you started.

Deploying the Project

During the development process, .NET created an .exe file, which you can distribute to other users. By default, .NET stores a project’s .exe file in that project file’s folder as follows:

C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2008Projects

Be sure to make adjustments for your system. Distribute the project’s .exe file and instruct the recipient to double-click it to get started. As long as the recipient has .NET and access to the data source using the same path, the .exe file will work.

Wizards Aren’t Always the Way

Now that you’ve started your journey through .NET, it’s time to start thinking like a .NET developer and less like an Access or even a SQL Server developer. The example in this article allowed wizards to connect a simple project to a data source and quickly display that data. Wizards, though helpful, aren’t always the best option however. In .NET, you can create a custom version of the control with all the desired presets (such as row height, colors, borders, and so on) and then use your custom control to provide a consistent user interface.

Alternatively, you could build a typed dataset that doesn’t rely on a direct database connection. Remember how the example .exe file is limited to a specific data source? Using a typed dataset, the user can switch the connection via the user interface. The good news for .NET beginners is that providing all that extra functionality is easy.


Share the Post: