Retrieving Data Using NET

In this example, we'll reach out to the Northwind 2007 database and populate a worksheet with data from the Employees table. The end result will look a lot like one of our early data access samples in Chapter 2, where we imported data from the Northwind database and then resized the columns using the AutoFit command. We will create a data access component and then use it from within our add-in.

1. Open Visual Studio 2005, and the start page will appear.

2. In the Recent Projects area (shown in Figure 9-9) in the Create section, click the Project link to open the New Project dialog box (shown previously in Figure 9-8).

Figure 9-9. Recent Projects section of Visual Studio 2005 start page

3. In the Name text box, name the project NWindDataAddln.

4. Leave the "Create directory for solution" check box checked.

The new project is created, as shown in Figure 9-10.

Figure 9-10. New Excel 2007 add-in project

We'll look at the default contents of the add-in project in a moment, but first let's create our data access component. We will add another project to our add-in project. Once the coding is finished, we'll have to add a reference to our data access project before we can use it.

The Data Access Component

We could add a new class directly in our add-in project, but it makes sense to put that in its own project where it will become a separate component. This gives you the option to easily use your data access layer in other projects.

1. Select the NWindDataAddIn project in the Solution Explorer.

2. Select File > Add > New Project (Figure 9-11).

Figure 9-11. Adding a new project to the solution

3. In the Add New Project dialog box, select Windows from the "Project types" list.

4. Select Class Library from the Templates section.

5. Name the new project NWindDataAccess, as shown in Figure 9-12.

5. Name the new project NWindDataAccess, as shown in Figure 9-12.

Figure 9-12. Creating a new class library project 6. Click OK.

A new project is added to the Solution Explorer and an empty class module is created, as shown in Figure 9-13.

Figure 9-13. New class library project added

The default name for the new class is Class1. Let's change that.

1. Select the Class1.vb file from the NWindDataAccess project in the Solution Explorer.

2. In the Properties pane (below the Solution Explorer, as shown in Figure 9-14), change the File Name property to NWindData.vb.

Figure 9-14. Changing the class name

Once that's done, all references to Class1 will be changed to reflect the new class, as shown in Figure 9-15.

Figure 9-15. Renamed class and file name

Now we'll add some code to our class. This will be a very simple class that does nothing more than query the Northwind 2007 database for a list of employees.

1. On your local PC, create a folder called C:\ExampleDBs (if you did not already do so for the ActiveX example).

2. Copy the Northwind 2007.accdb file into the new folder.

Note These two steps are more of a necessity for those who do not have Visual Studio 2005 (so the code will run from the sample files you downloaded from www.apress.com). If you are using Visual Studio 2005, you can modify the code to refer to the Northwind 2007 database from any location on your PC.

3. Put your cursor in the class module code window and press Ctrl+Home to position the insertion point at the very beginning of the code.

4. Press Enter.

5. Add the following statement to reference the OLEDB library: Imports System.Data.OleDb

6. Move the insertion point to the blank line inside the class code.

7. Add the following module-level declarations:

Const TABLE_NAME As String = "Table1" Private m_sNwindName As String

The constant TABLE_NAME will hold the table name we'll use when we fill a DataSet from the Northwind Employees table. The sNwindName variable will hold the path to the database. Next, let's add a public property to the data access class to store and retrieve the location of the database.

8. Add the following property to the NWindData class module:

Public Property NwindPathFileName() As String Get

Return m_sNwindName End Get

Set(ByVal value As String)

If System.IO.File.Exists(value) Then m_sNwindName = value

Else

Throw New System.IO.FileNotFoundException End If End Set End Property

The Property Get is very straightforward in that it's just returning the value from the private variable. The Property Set has a bit of validation code. We're checking to see if the file exists before we assign the new value to the private variable. If it does not exist, the class will throw a System.IO.FileNotFoundException error to the client code.

Note Managed code does not raise errors as classic VBA and VB did. In the .NET world, exceptions are thrown by our code and caught in exception handling blocks.

Now we'll add a method that accepts a SQL statement to get the data and returns the data in the form of a .NET DataSet object.

9. Add a new function named GetData to the NWindData class, as follows:

Public Function GetData(ByVal Which As String) As DataSet

10. Add the following variable declarations:

Dim dsReturn As New DataSet() Dim cnn As OleDbConnection Dim sConnString As String

11. Create the connection to the data by adding the following code:

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" w

& "Data Source=" & m_sNwindName & ";" cnn = New OleDb.OleDbConnection(sConnString)

12. Create a DataAdapter to hold the data and fill the DataSet by adding the following code:

Dim da As New OleDbDataAdapter(Which, cnn)

Next, we are going to fill the DataSet from the DataAdapter. We will add exception handling to this process. As VBA programmers, we're used to using the age-old On Error Go To syntax in our error handlers. In our managed code, we use Try...Catch blocks to catch any exceptions our code throws.

13. Add the following code to fill the DataSet and watch for and handle exceptions: Try da.Fill(dsReturn, TABLE_NAME) Catch ex As Exception MsgBox(ex.Message) End Try

This Try...Catch block includes the optional Finally clause. Any code inserted here will always run regardless of errors. It's a good place for cleanup code. The last thing to do is return our filled DataSet.

14. Add the following code after the Try...Catch block: Return dsReturn

The complete GetData function looks like Listing 9-5.

Listing 9-5. Complete GetData Function

Public Function GetData(ByVal Which As String) As DataSet Dim dsReturn As New DataSet() Dim cnn As OleDbConnection Dim sConnString As String sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" w

& "Data Source=" & m_sNwindName & ";" cnn = New OleDb.OleDbConnection(sConnString)

Dim da As New OleDbDataAdapter(Which, cnn)

Try da.Fill(dsReturn, TABLE_NAME) Catch ex As Exception MsgBox(ex.Message) Finally

MsgBox(dsReturn.Tables("Table1").Rows.Count & " Records") End Try

Return dsReturn End Function

That completes our work on the data access component. Let's bring our attention back to the add-in project that Visual Studio 2005 created for us.

The Add-In Project

When we created our add-in project, Visual Studio 2005 created the NWindDataAddIn project, and it created a deployment project named NWindDataAddlnSetup. Within the NWindDataAddIn project, we have one file, named ThisAddIn.vb. This is where we'll put the code that will run on our client Excel applications.

Before we begin, we must add a reference to our data component.

1. In the Solution Explorer, select the NWindDataAddIn project.

2. Right-click the project and choose Add Reference to display the Add Reference dialog box.

3. On the Projects tab, select NWindDataAccess, as shown in Figure 9-16.

Figure 9-16. The Add Reference dialog box Click OK.

Figure 9-16. The Add Reference dialog box Click OK.

In the Solution Explorer, double-click the ThisAddIn.vb file to open the code window.

Add-in projects include one line of code in the startup method referencing the Excel application, as shown in Figure 9-17.

Figure 9-17. The predefined add-in project code

6. Place the insertion point in the blank line above the ThisAddIn_Startup method.

7. Add the following module-level variable declarations:

Private m_oNWind As NWindDataAccess.NWindData Private m_oSheet As Excel.Worksheet Private m_oDS As DataSet

Here, we create an object to hold a reference to our data component and create variables to hold references to an Excel worksheet and our DataSet.

8. In the ThisAddIn_Startup method, put the insertion point in the blank line under the last comment.

9. Add the following code:

m_oNWind = New NWindDataAccess.NWindData m_oSheet = Me.Application.Worksheets("Sheet1") GetData()

This code instantiates an instance of the data component, fills m_oSheet with a reference to Sheet1 in our Excel workbook, and calls a function named GetData. Visual Studio 2005 may bark at you because this function does not exist, yet but that's only temporary.

10. Put the insertion point inside the ThisAddIn_Shutdown method.

11. Add the following line of cleanup code: m_oNWind = Nothing

Now let's create the GetData method.

Getting the Data Since we added a reference to our data access project, we can refer to its properties and methods from our add-in. The GetData method will call out to our data access layer and fill our module-level DataSet variable. Then it will push the data out to the Excel sheet.

1. In the ThisAddIn class module, add a new subroutine named GetData.

2. In the GetData procedure, add the following variable declarations:

Dim sDB As String = "C:\ExampleDBs\Northwind 2007.accdb" Dim iCols As Integer Dim i As Integer Dim row As Integer

We're passing in the location of the database to the sDB String variable, and then we have the remaining Integer variables to hold our place as we walk through the DataSet and display our data.

3. On the first blank line below the variable declarations, type the following line of code: Try

4. Press Enter, and Visual Studio 2005 will add a complete Try...Catch block for you.

5. Place the insertion point in the first blank line below the Try line of code.

6. Add the following code to set the file name in the data access component and call its GetData method:

With m_oNWind

.NwindPathFileName = sDB m_oDS = .GetData("select * from employees") End With

7. Add the following code to walk through the DataSet and insert the column headings in the worksheet:

m_oDS.Tables("Table1").Columns(i).Caption

Next

8. Place the insertion point in the blank line following the previous code, and press Enter.

9. Add the following code to walk through the DataSet and insert the employee data on the worksheet:

For Each RowIterator As DataRow In m_oDS.Tables("Table1").Rows For i = 0 To iCols - 1

RowIterator(m_oDS.Tables("Table1").Columns(i).Caption)

Next

Next, we'll add the code to format the Excel worksheet by applying the AutoFit command to size each column to show its longest data entry.

10. Place the insertion point in the blank line following the previous code, and press Enter.

11. Add the following code:

Dim r As Excel.Range m_oSheet.Select() r = m_oSheet.Range("A1") r.Select()

Application.Selection.CurrentRegion.Select()

Application.Selection.Columns.AutoFit()

r.Select()

The last thing for us to do is a bit of exception handling.

12. Place the insertion point at the beginning of the line containing the Catch statement, and press Enter.

13. Move the insertion point up into the blank line you just inserted.

14. Add the following code to trap for the FileNotFoundException:

Catch ex As System.IO.FileNotFoundException MsgBox("File: " & sDB & " not found")

That's all the code for the GetData method. The completed subroutine looks like Listing 9-6.

Listing 9-6. Complete GetData Subroutine

Private Sub GetData()

Dim sDB As String = "C:\ExampleDBs\Northwind 2007.accdb" Dim iCols As Integer Dim i As Integer Dim row As Integer

With m_oNWind

.NwindPathFileName = sDB m_oDS = .GetData("select * from employees") End With iCols = m_oDS.Tables("Table1").Columns.Count For i = 0 To iCols - 1

m_oDS.Tables("Table1").Columns(i).Caption

For Each RowIterator As DataRow In m_oDS.Tables("Table1").Rows For i = 0 To iCols - 1

RowIterator(m_oDS.Tables("Table1").Columns(i).Caption)

Next

Dim r As Excel.Range m_oSheet.Select() r = m_oSheet.Range("A1") r.Select()

Application.Selection.CurrentRegion.Select()

Application.Selection.Columns.AutoFit()

r.Select()

Catch ex As System.IO.FileNotFoundException

MsgBox("File: " & sDB & " not found") Catch ex As Exception

End Try End Sub

15. Save the project, and then run it by selecting Debug > Start Debugging, or by pressing the F5 key.

Excel 2007 will appear with the Northwind Employees table loaded into Sheet1, as shown in Figure 9-18.

H *ï * c * ) '

Bookl - Microsoft Excel

_ H X

Home Insert

Page Layout Formulas

Data Review View Developer — X

Paste

a y

Callbri

11 -

I" "

m\9

General T

Styles

□^Insert -if* Delete -gl Format'

Sort & Find & <2t FilterT SelectT

B I IT

PC k

$ - % »

fflHi^a

-*-.G .00 .00 ->.0

Clipboard

Fant

Alignment ^

Number ^ J

Cells

Editing

fx ID

*

A

B

c

O

E

F

— -

1

ID

Company

Last Name

First Name

E-mail Address

Job Title

2

1

Northwind Traders

Freehafer

Nancy

[email protected]

Sales Representative

3

2

Northwind Traders

Cencini

Andrew

[email protected]

Vice President, Sales

4

3

Northwind Traders

Kotas

Jan

[email protected]

Sales Representative

5

4

Northwind Traders

Sergienko

Mariya

[email protected]

Sales Representative

6

5

Northwind Traders

Thorpe

Steven

[email protected]

Sales Manager

7

6

Northwind Traders

Neipper

Michael

[email protected]

Sales Representative

8

7

Northwind Traders

Zare

Robert

[email protected]

Sales Representative

9

8

Northwind Traders

Giussani

Laura

[email protected]

Sales Coordinator

10

9

Northwind Traders

Heilung-La rsen

Anne

[email protected]

Sales Representative

11

12

13

14

15

M ^ ^

m Sheetl Sheet2 Sheet3 fj

Ready

m

IdSifl] £]| 100%

y a ■■+■'

Figure 9-18. Northwind data added to the worksheet from the add-in project

I mentioned earlier that this is accomplished with no code at all in the workbook. Let's take a look at the VBE in the Excel workbook we just opened through our code.

Open the Excel VBE by selecting the Developer ribbon > Code tab > Visual Basic command, or by pressing Alt+F11. Look through all of the built-in code modules and you will not find one bit of code.

Note All Excel workbooks ship with built-in code modules representing the code behind the workbook (ThisWorkbook) and its worksheets (Sheet1, Sheet2, etc.). You can find this code in the VBA IDE Project Explorer by double-clicking the item in the Project Explorer's Microsoft Excel Objects folder.

How does the workbook know where to find the data, then?

1. Return to Excel and click the Office button, and then select Excel Options.

2. Select Add-Ins from the left-hand navigation to display the Add-ins list, as shown in Figure 9-19.

Figure 9-19. The Add-ins list in the Excel Options dialog box

This section gives you a snapshot of available and active add-in applications. Excel will leave these add-ins loaded even after you close Visual Studio. Before you close the workbook, and while the Excel Options dialog is still open, unload the add-in as follows.

3. From the Manage drop-down list, choose COM Add-Ins, and then click the Go button.

4. Deselect the NWindDataAddIn project, as shown in Figure 9-20.

Figure 9-20. Unloading an add-in

5. Click OK to unload the add-in.

6. Close the workbook without saving.

7. Close Visual Studio 2005.

0 0

Post a comment