Extending IronPython Using Visual Basic.NET

Considering C# and VISUAL BASIC.NET Extension Similarities

Visual Basic.NET does have some distinct advantages over C# when building an extension. The most important of these distinctions is that Visual Basic.NET does more for you in the background. For example, Visual Basic.NET automatically creates a namespace for you — it isn’t something you have to think about. Visual Basic.NET also performs some type conversions automatically, so you don’t have to think about type conversions as much either. When you do need to perform a type conversion, you use the CType() function, which makes the kind of conversion a little more apparent.

You can easily use either C# or Visual Basic.NET to perform simple tasks. For example, either language works fine for creating a math library or for working with files. C# probably has an advantage in working with low-level extensions, especially those that interact with the Win32 API. On the other hand, the tendency of Visual Basic.NET to hide some of the gory details of programming works to your advantage when working with higher-level programming requirements, such as database access. Consequently, this chapter describes the requirements for creating a database extension.

Creating the Simple Visual Basic.NET Extension

The best place to begin learning how to create extensions is to create a very simple one. The sections that follow explore a simple Visual Basic.NET extension. This project creates a simple math library. In the process, it demonstrates some unique principles of creating extensions using Visual Basic.NET.

Creating the Project

A Visual Basic.NET extension project is nothing more than the typical class library. The following steps help you create the project for this example. You can use the same steps when working with the other examples — simply change the project name.

1. Choose File ➪ New ➪ Project. You’ll see the New Project dialog box shown in Figure 17-1.

   

2. Choose the Visual Basic folder in the Installed Templates list.
3. Select .NET Framework 3.5 or an earlier version of the .NET Framework if you’re using Visual Studio 2010. Don’t select the .NET Framework 4.0 entry because IronPython won’t load extensions based on the .NET Framework 4.0. The list of templates changes when you change the .NET Framework version.
4. Select the Class Library template.
5. Check Create Directory for Solution if it isn’t already checked. When working with extensions, creating a solution directory provides a place for putting solution-level objects.
6. Type Calcs in the Name field and click OK. Visual Studio creates a class library project for you.
7. Right-click Class1.vb in Solution Explorer and choose Rename from the context menu. Visual Studio makes the filename editable.
8. Type Calcs.VB for the new filename and press Enter. Visual Studio displays a dialog box that asks whether you’d like to rename all of the Class1.vb references to match the new filename.
9. Click Yes. The project is ready for use.

Developing the Visual Basic.NET Extension

The Visual Basic.NET extension code for this example is relatively simple. Listing 17-1 shows the constructor, operator overrides, and methods used for this example.

Listin g 17-1: A simple calculations extension

[code]
Public Class Calcs
Private Data As Int32
Public Sub New(ByVal Value As Int32)
Me.Data = Value
End Sub
Public Overrides Function ToString() As String
Return Data.ToString()
End Function
Public Shared Operator +(ByVal Value1 As Calcs, _
ByVal Value2 As Calcs) As Calcs
Return New Calcs(Value1.Data + Value2.Data)
End Operator
Public Shared Operator -(ByVal Value1 As Calcs, _
ByVal Value2 As Calcs) As Calcs
Return New Calcs(Value1.Data – Value2.Data)
End Operator
Public Shared Operator *(ByVal Value1 As Calcs, _
ByVal Value2 As Calcs) As Calcs
Return New Calcs(Value1.Data * Value2.Data)
End Operator
Public Shared Operator /(ByVal Value1 As Calcs, _
ByVal Value2 As Calcs) As Calcs
Return New Calcs(Value1.Data / Value2.Data)
End Operator
Public Function Inc() As Calcs
Return New Calcs(Me.Data + 1)
End Function
Public Function Dec() As Calcs
Return New Calcs(Me.Data – 1)
End Function
End Class
[/code]

The code begins with a constructor that accepts an Int32 value as input. The example doesn’t include a default constructor because IronPython needs to assign a value to the object during the instantiation process. A default constructor would still need to assign a value to the private Data member, so it’s just better to assign a valid value to Data at the outset.

The ToString() override comes next. The default behavior for ToString() is to display the name of the class. You must override this behavior to display the value of Data. Notice that you must access Data as Me.Data — the copy of Data associated with this particular instance of the Calcs class.

The four Operator methods are defined as Shared, rather than Overrides. The Operator methods act as static class members so that you can use them naturally in IronPython. The input arguments for each method are the objects you create within IronPython. Consequently, there isn’t any concept of numeric type for Value1 or Value2 (you could theoretically use the same methods for any numeric value). The actual math operation occurs on the Data member of each object.

IronPython doesn’t support the ++ or — operators that are supported by Visual Basic for increment and decrement. Consequently, the class provides an Inc() and Dec() method. Notice that these methods aren’t defined as Shared because they work with a single object. You need to consider the differences between binary (those that work with two objects) and unary (those that work with a single object) operators when creating your extension. Binary operators are always declared as Shared, while unary operators appear as a standard method.

At this point, you can compile the class if desired. Start a copy of the IronPython console and type the following commands to load the extension.

[code]
import clr
clr.AddReferenceToFile(‘Calcs.DLL’)
import Calcs
dir(Calcs.Calcs)
[/code]

The dir() function shows the content of the Calcs extension as shown in Figure 17-2. Notice that Inc() and Dec() appear as you expect. However, there aren’t any entries for +, -, *, and / methods. These operators still work as you expect, but IronPython shows a Python equivalent for the operators in the form of __add__(), __radd__(), __sub__(), __rsub__(), __mul__(), __rmul__(), __div__ (), and __rdiv__(). These methods don’t appear unless you define the operators in your class.

If you’re looking at the class in the IronPython console, you might want to give it a quick try before you close up the console and move on to the next part of the example. Try this code and you’ll see an output of 15 from the __add__() method. Figure 17-2 shows the results of the calculation.

[code]
Value1 = Calcs.Calcs(10)
Value2 = Calcs.Calcs(5)
print Value1.__add__(Value2)
[/code]

Adding the IronPython Project

At this point, you have a Visual Basic.NET extension (or module) to use with IronPython. Of course, you’ll want to test it. The easiest way to do this is to add the IronPython project directly to the current solution. The following steps describe how to perform this task.

1. Right-click the solution entry in Solution Explorer and choose Add ➪ Existing Project from the context menu. You’ll see the Add Existing Project dialog box shown in Figure 17-3.

   

2. Locate IPY.EXE on your hard drive and highlight it. Click Open. You’ll see a new project entry added to the solution.
3. Right-click the ipy entry in Solution Explorer and choose Set as Startup Project from the context menu. This step ensures that choosing one of the startup options from the Debug menu starts the IronPython application.
4. Right-click the ipy entry in Solution Explorer and choose Properties from the context menu. You’ll see the General tab of the ipy Properties window shown in Figure 17-4.

   

5. Type -D TestCalcs.py in the Arguments field.
6. Click the ellipses in the Working Directory field to display the Browse for Folder dialog box. Locate the output folder of the Calcs.DLL (or other extension) file. Click OK. The IDE adds the correct directory information to the Working Directory field.
7. Open Windows Explorer. Locate the CalcsCalcsbinDebug folder. Right-click in the right pane and choose New ➪ Text Document from the context menu. Name the file TestCalcs.py and press Enter. Click Yes if asked if you want to rename the file extension.
8. Right-click the solution item in Solution Explorer and choose Add ➪ Existing Item from the context menu to display the Add Existing Item dialog box shown in Figure 17-5.
9. Locate the TestCalcs.py file in the solution and click Add. Visual Studio adds TestCalcs.py to the Solution Items folder in Solution Explorer and automatically opens the file for you. You’re ready to add test code for the application.

Creating the IronPython Application

It’s time to write code to test Calcs.DLL. Listing 17-2 shows the code you’ll use for testing purposes.

Listin g 17-2: Testing the extension using IronPython

[code]
# Add a reference to the CLR
import clr
# Obtain access to the extension.
clr.AddReferenceToFile(‘Calcs.DLL’)
import Calcs
# Create an instance of the class and fill it with data.
Value1 = Calcs.Calcs(10)
# Print the original value, then decrement and increment it.
print ‘Original Value1 Content: ‘, Value1
print ‘Value1 + 1: ‘, Value1.Inc()
print ‘Value1 – 1: ‘, Value1.Dec()
# Create a second value and display it.
Value2 = Calcs.Calcs(5)
print ‘nOriginal Value2 Content: ‘, Value2
# Use the two values together in different ways.
print ‘nValue1 + Value2 = ‘, Value1 + Value2
print ‘Value1 – Value2 = ‘, Value1 – Value2
print ‘Value1 * Value2 = ‘, Value1 * Value2
print ‘Value1 / Value2 = ‘, Value1 / Value2
# Pause after the debug session.
raw_input(‘nPress any key to continue…’)
[/code]

The code begins by importing the Common Language Runtime (CLR). It then uses the AddReferenceToFile() method to create a reference to Calcs.DLL. The final step is to import the Calcs code.

Before the code can use the Calcs code, it must create an instance of it, Value1. Notice that the code calls the Calcs.Calcs() constructor with an initial value. Any time you want to assign a value to Value1, you must use the Calcs.Calcs() constructor. If you were to assign a value using Value1 = 15, it would change the type of Value1. A consequent addition, such as Value1 + Value2, would produce the following error:

[code]
Traceback (most recent call last):
File “<stdin>”, line 1, in <module>
TypeError: unsupported operand type(s) for +: ‘int’ and ‘Calcs’
[/code]

One way to overcome this problem would be to override the = operator.

After the code creates Value1, it demonstrates the use of the Inc() and Dec() methods. These two methods simply add or remove 1 from the value of Value1. If you want to change the actual value of Value1, you need to make Value1 equal to the output of the method like this:

[code]
Value1 = Value1.Inc()
[/code]

The next step is to create Value2, a second Calcs object you can use for binary operations. The code outputs the initial value of Value2. The remainder of the example demonstrates the use of the various operators. As you can see, they work precisely as you would expect. You could even use them to create a third value like this:

[code]
Value3 = Value1 + Value2
[/code]

Figure 17-6 shows the output from this example. Except for the absence of the ++ and — operators, everything works much as you would expect.

Using Visual Basic.NET for User Interface Support

It’s certainly possible to create message boxes and even Windows Forms applications using IronPython. The biggest issue is that IronPython lacks support for the designers that make the task of writing Windows Forms code so easy. You have to be able to picture the form you want in your mind and then use trial and error to get it to appear in the application. Consequently, most developers will probably want to use a language such as Visual Basic.NET to create their Windows Forms applications and then make those forms accessible from IronPython as part of an extension.

The examples in the sections that follow aren’t all that complicated, but they do demonstrate the principles required to build your own library of message boxes and Windows Forms classes. By the time you finish these examples, you’ll have everything needed to create your own user interface library for use in IronPython.

Creating the User Interface Library Module

From an IronPython perspective, user interface elements come in two forms: messages boxes and Windows Forms. Obviously, Visual Basic.NET can create a host of user interface presentations, but if you start at this basic level, you’ll find the task of creating a user interface library module easier. The following sections describe how to create both a message box class and a Windows Forms class that you place in a single DLL for use with your IronPython application. Of course, a production DLL could have hundreds of different forms, depending on the user interface requirements for the application.

Defining Simple Message Boxes

Message boxes (created using the MessageBox class) are extremely useful for displaying short messages and getting canned responses. Depending on the buttons you provide, a user could tell you that the application should retry an operation or answer yes to simple questions. If you need a little more input, you can always rely on an input box (created with the InputBox() method of the Interaction class). Of course, an input box is still limited to a single field, but even so, it does extend the kinds of input you can receive from the user.

Listing 17-3 demonstrates both the MessageBox.Show() and InputBox() methods. In addition, you’ll see how to implement the __doc__() method that most IronPython developers rely upon to obtain information about your extension.

Listin g 17-3: Working with simple message boxes

[code]
Imports System.Windows.Forms
Public Class Dialogs
Public Function ShowMessage(ByVal Msg As String) As String
Return MessageBox.Show(Msg).ToString()
End Function
Public Function ShowMessage(ByVal Msg As String, _
ByVal Title As String) As String
Return MessageBox.Show(Msg, Title).ToString()
End Function
Public Function ShowMessage(ByVal Msg As String, ByVal Title As String, _
ByVal Buttons As Int16) As String
Return MessageBox.Show(Msg, Title, CType(Buttons, MessageBoxButtons) _
).ToString()
End Function
Public Function ShowMessage(ByVal Msg As String, ByVal Title As String, _
ByVal Buttons As Int16, ByVal Icon As Int16 _
) As String
Return MessageBox.Show(Msg, Title, CType(Buttons, MessageBoxButtons), _
CType(Icon, MessageBoxIcon)).ToString()
End Function
Public Function ShowMessage(ByVal Msg As String, ByVal Title As String, _
ByVal Buttons As Int16, ByVal Icon As Int16, _
ByVal DefaultButton As Int16) As String
Return MessageBox.Show(Msg, Title, CType(Buttons, MessageBoxButtons), _
CType(Icon, MessageBoxIcon), _
CType(DefaultButton, MessageBoxDefaultButton) _
).ToString()
End Function
Public Function GetInput(ByVal Msg As String, ByVal Title As String)
Return InputBox(Msg, Title, “Type a value”)
End Function
Public Function __doc__() As String
Return “This is a help string”
End Function
End Class
[/code]

Before you can compile this code, you need to add a reference to System.Windows .Forms.DLL. Right-click Dialogs in Solution Explorer and choose Add Reference from the context menu. You’ll see the Add Reference dialog box shown in Figure 17-7. Highlight the System.Windows.Forms entry and click OK. At this point, you also need to add an Imports System.Windows.Forms entry to your project and you’re ready to work with message boxes.

The code begins by creating a series of ShowMessage() methods. The first is relatively simple and the complexity increases with each ShowMessage() method entry. Notice that the ShowMessage() method uses Int16 input values to select the buttons, icon, and default button. You could also use enumerations to provide input values. The one thing you don’t want to do is ask the IronPython developer to provide a MessageBoxButtons, MessageBoxIcon, or MessageBoxDefaultButton value, because then the IronPython developer would need to import all the required .NET Framework functionality, reducing the usefulness of your extension. The CType() function helps you convert the Int16 values into the appropriate enumeration value. Interestingly enough, there are 21 forms of the MessageBox .Show() method, even though the example shows only five of them.

The GetInput() method shows just one of several InputBox() method variations you can use. In this case, the IronPython developer supplies the prompt (or message) and title to display onscreen. The GetInput() method supplies a default InputBox() value. Normally, you want to supply a value so that the user knows to type something and what you want the user to type. Even if the required input seems obvious to you, many users won’t know what to provide.

The __doc__() provides a help string for the IronPython developer. The example shows something quick, but in reality, you’d provide complete documentation for your class. The output string can use all the standard formatting characters. You could even read the content in from an external source, such as a file, to make it easy to provide updates without having to recompile the extension. Using an external file would also allow the IronPython developer to personalize the content.

Defining Complex Forms

A Windows Forms class can contain anything you want. It can even call other forms as needed. In fact, anything you can do with a Visual Basic.NET Windows Forms application is doable with IronPython. Of course, you do need to maintain interaction with the IronPython application. The following steps describe how to create a simple Windows Forms class for your extension.

1. Right-click Dialogs in Solution Explorer and choose Add ➪ New Item. Select the Windows Forms entry in the Installed Templates list. You see the Add New Item dialog box shown in Figure 17-8.

   

2. Highlight the Windows Form entry. Type TestForm.VB in the Name field and click Add. Visual Studio adds the new form to your project and automatically opens it for editing.
3. Create the form just as you normally would for any static application. Figure 17-9 shows the form used for this example. It’s simple, but it contains multiple data entry fields and multiple exit options.

The form shown in Figure 17-9 is a little deceptive. Before you assume anything about this form, it does have a few differences from the forms you’ve created for your static applications.

• Buttons that close the form, rather than do something within the form, must have the DialogResult property set to a unique value or you won’t be able to tell which button the user clicked. For this example, the DialogResult for btnOK is OK, while the DialogResult for btnCancel is Cancel.
• Getting information from the form you create to the IronPython application can prove problematic. You could contrive all sorts of odd methods for accomplishing the task, but the simplest method is to set the Modifiers property for the individual controls (txtName and txtColor) to Public. In this case, using Public doesn’t create a problem because IronPython sets everything to public. In all other respects, there’s no difference between this form and any other form you’ve created in the past.

To make things simple, this example doesn’t use any code-behind for the form itself. Any codebehind works as you’d expect. There isn’t any difference between calling the form from IronPython than calling it from within your Visual Basic.NET application.

Accessing the User Interface Library Module from IronPython

It’s time to use the extension you’ve created with an IronPython application. The following sections describe an alternative way to set up your project so that you don’t have to create the IronPython file using Windows Explorer and show how to use the extension.

An Alternative Method for Adding the IronPython Project

There are a number of ways to configure a test setup for your extensions. The “Adding the IronPython Project” section shows one technique. The technique shown in that section works well when you want to maintain separate builds of your extension. For example, you might want to maintain separate debug and release builds.

Unfortunately, that earlier method is a bit clumsy — you have to create the IronPython file using Windows Explorer. The technique in this section avoids that problem. In addition, this technique shows how to maintain just one build — the build you’re currently using for debugging, testing, or experimentation. Use the following steps to create a centralized test configuration:

1. Right-click Dialogs in Solution Explorer and choose Properties from the context menu. Select the Compile tab. You’ll see the Properties window shown in Figure 17-10.

   

2. Click Browse next to the Build Output Path field to display the Select Output Path dialog box shown in Figure 17-11. Because you’ll add the IronPython test file at the solution level, you need to send the output to the solution level as well.
3. Select the first Dialogs entry in the list and click OK. Visual Studio adds an absolute path to the Output Path field that you must change for every machine that uses the application. As an alternative, you could type .. (two periods and a backslash) in the field to place the output in the solution folder.
4. Select the next configuration in the Configuration field.
5. Perform Steps 2 through 4 for each configuration. Make sure each configuration uses the same output directory. Normally, your project will contain only Debug and Release configurations.
6. Right-click the solution entry in Solution Explorer and choose Add ➪ Existing Project from the context menu. You’ll see the Add Existing Project dialog box shown in Figure 17-3.
7. Locate IPY.EXE on your hard drive and highlight it. Click Open. You’ll see a new project entry added to the solution.

   

8. Right-click the ipy entry in Solution Explorer and choose Set as Startup Project from the context menu.
9. Right-click the ipy entry in Solution Explorer and choose Properties from the context menu. You’ll see the General tab of the ipy Properties window shown in Figure 17-4.
10. Type -D DialogTest.py in the Arguments field.
11. Click the ellipses in the Working Directory field to display the Browse for Folder dialog box. Locate the solution folder for the project (the first Dialogs folder). Click OK. The IDE adds the correct directory information to the Working Directory field.
12. Right-click the solution entry in Solution Explorer and choose Add ➪ New Item from the context menu. You see the Add New Item dialog box shown in Figure 17-12.

    

13. Type DialogTest.py in the Name field and click Add. Visual Studio adds the new file to the Solution Items folder in Solution Explorer and opens the file automatically for editing.

Performing the Message Box and Form Tests

The example is ready except for the test code. Listing 17-4 shows the IronPython code you need for this example.

Listin g 17-4: Testing the message boxes and forms

[code]
# Define the message box tests.
def TestMessages():
# Create a message box object.
MyDialog = Dialogs.Dialogs()
# Show the help information.
print ‘Dialogs Class Help Information.’
print MyDialog.__doc__()
# Test a simple message box.
print ‘nTesting a simple message box.’
print ‘Simple message box output: ‘,
print MyDialog.ShowMessage(‘Hello’)
# Perform a more complex test.
print ‘nA more complex message box.’
print ‘Complex message box output: ‘,
print MyDialog.ShowMessage(‘Hello Again’, ‘Title 2’, 3, 64, 256)
# Get some user input.
print ‘nUsing an InputBox.’
print ‘InputBox Output: ‘,
print MyDialog.GetInput(‘Type Your Name:’, ‘User Name Entry’)
# Define the form test.
def TestForm():
# Create the form instance.
MyForm = Dialogs.TestForm()
# Display the form and test the dialog result.
print ‘nThe form example.’
if MyForm.ShowDialog().ToString() == ‘OK’:
# Display the results.
print ‘The user clicked OK.’
print ‘User Name: ‘, MyForm.txtName.Text
print ‘Favorite Color: ‘, MyForm.txtColor.Text
# Display an alternate result.
else:
print ‘The user clicked cancel.’
# Import the Common Language Runtime.
import clr
# Access the extension.
clr.AddReferenceToFile(‘Dialogs.DLL’)
import Dialogs
# Test the message box code.
TestMessages()
# Test the form code.
TestForm()
# Pause after the debug session.
raw_input(‘nPress any key to continue…’)
[/code]

The code begins by importing CLR support and then uses the AddReferenceToFile() to add a reference to the Dialogs.DLL. The next step is to import the Dialogs namespace for use. The __main__() function calls two functions, TestMessages() and TestForm(), to test the content of the Dialogs namespace. It then pauses so you can see the results.

The TestMessages() function begins by creating an instance of Dialogs.Dialogs, MyDialog. It then calls the MyDialog.__doc__() method to output the help information provided by the Dialogs class. Normally you’d use this method at the interactive console, but it’s good to see how the method works.

The next step is to test the MyDialog.ShowMessage() method. To keep you from clicking all afternoon, the test code uses just two forms of the method. The first form shows the simplest dialog box, while the second shows the most complex. The most complex dialog box (shown in Figure 17-13) contains a message, title, icon, and three buttons. Notice that the second button, rather than the first button, is selected by default. Normally, a message box selects the first button by default.

The next step is to display an input box. In this case, the MyDialog.GetInput() method displays an input box that contains a simple prompt and a title, as shown in Figure 17-14. Notice the default message in the input box. The input box automatically highlights this default entry so that the first thing the user types will erase the default content. The output from the MyDialog .GetInput() method is the text that the user types in the input box.

The TestForm() function begins by creating an instance of the Dialogs.TestForm class, MyForm. The code then displays the dialog box shown in Figure 17-9 using the MyForm.ShowDialog() method. Notice that the example code adds a call to ToString(), so that the entire method call is MyForm.ShowDialog().ToString(). This is a technique for converting the System.Windows .Forms.DialogResult to a simple string that you can compare with the desired output, which is ‘OK‘ in this case.

When the call succeeds (the user clicks OK), the code prints the user’s name and favorite color. Notice that the code directly accesses both txtName.Text and txtColor.Text to obtain the required information. When the call fails (the user clicks Cancel), the code outputs a simple failure message. Figure 17-15 shows typical output from this example.

Using Visual Basic.NET for Database Support

Visual Basic.NET makes database management easy. Of course, there are all the handy designers that Visual Basic.NET makes available. The features of Server Explorer help as well. However, the fact that Visual Basic.NET tends to hide some of the details is what helps the most. The following sections provide a simple database management example that you could easily expand to help IronPython work with all sorts of data.

Obtaining and Configuring the Database

This example relies on an old standby, the Northwind database. Microsoft has passed this database by for significantly more complex examples, but Northwind remains unsurpassed in its ability to create useful examples with very little code, so it’s the database of choice for this chapter. You can download the Northwind database from http://www.microsoft.com/downloads/details .aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034.

Make sure you have a database manager installed on your system. The Northwind database works just fine with versions of SQL Server as old as SQL Server 2000, but you should at least try a newer version, even if it’s SQL Server 2008 Express. The following steps tell you how to install the Northwind database.

1. Double-click the SQL2000SampleDb.msi. You’ll see the normal Welcome dialog box for installing Microsoft products. Click Next. You’ll see the licensing agreement.
2. Click I Agree after reading the license agreement, and then click Next. You’ll see an Installation Options dialog box. There aren’t any actual installation options.
3. Click Next. You’ll see a Confirm Installation dialog box.
4. Click Next. The installer installs the files into the C:SQL Server 2000 Sample Databases folder on your machine (you aren’t given a choice about the installation folder). After the installation is complete, you’ll see an Installation Complete dialog box.
5. Click Close. The Northwind database and its associated script are now loaded on your machine.
6. Open a command prompt in the C:SQL Server 2000 Sample Databases folder.
7. Type OSQL -E -i InstNwnd.SQL and press Enter (the command line switches are case sensitive — make sure you type the command correctly). The OSQL utility will start building and installing the Northwind database. This process can take a while to complete — get a cup of coffee and enjoy. When the process is complete, you see a command prompt with a bunch of numbers on it and no error message, as shown in Figure 17-16.

Creating the Database Support Module

Creating a database support module is a multi-step process. At a minimum, you must first create a connection to the database and then work with that connection using code. The example that follows isn’t very complex. All that this example will do is retrieve some information from the database in the interest of keeping things simple. Even so, the basics shown in the example provide enough information for you to start creating database extensions of your own.

Creating a Connection to the Database

The first step in working with the Northwind database is to create a connection to it. The following steps describe how to perform this task.

1. Right-click on the Data Connections entry in Server Explorer and choose Add Connection from the context menu. You may see the Choose Data Source dialog box shown in Figure 17-17. If not, you’ll see the Add Connection dialog box shown in Figure 17-18 and will need to proceed to Step 3.
2. Highlight the Microsoft SQL Server entry. Select the .NET Framework Data Provider for SQL Server entry in the Data Provider field. Click Continue. You’ll see the Add Connection dialog box shown in Figure 17-18.

   

3. Select or type the server name in the Server Name field. You can type a period (.) for the default server. The Add Connection dialog box automatically enables the Select or Enter a Database Name field.
4. Select the Northwind database in the Select or Enter a Database Name field.
5. Click Test Connection. You see a success message box (click OK to dismiss it).
6. Click OK. Visual Studio displays the new connection in Server Explorer, as shown in Figure 17-19.

   

   

7. Choose Data ➪ Add New Data Source. You’ll see the Data Source Configuration Wizard dialog box shown in Figure 17-20.

   

8. Highlight Database and click Next. You’ll see the Choose Database Model page.
9. Highlight the Dataset option and click Next. You’ll see the Choose Your Data Connection page. Notice that the Northwind database connection already appears in the connection field. The connection name will have your machine name, followed by the database name, followed by .dbo, such as main.Northwind.dbo. If it doesn’t, make sure you select it from the list. If the connection doesn’t appear in the list, click Cancel and start over with Step 1 because your connection wasn’t successful.
10. Select the Northwind connection and click Next. The wizard will ask how you want to save the connection. There isn’t a good reason to change the default name provided.
11. Click Next. You see the Choose Your Database Objects page shown in Figure 17-21.
12. Check the Customers table entry, as shown in Figure 17-21. The example relies on the Customers table and none of the other database content. Click Finish. The new data source appears in the Data Sources window, as shown in Figure 17-22. If you can’t see this window, choose Data ➪ Show Data Sources.

Adding Database Manipulation Code

After all the work you performed to obtain access to the data, the actual database manipulation code is relatively easy. Listing 17-5 shows the small amount of code used to actually retrieve a particular record from the database based on the CustomerID field. Of course, you can add any level of complexity required.

Listin g 17-5: Retrieving data from the database

[code]
Public Function GetData(ByVal Customer As String) As _
NorthwindDataSet.CustomersRow
‘ Obtain access to the table.
Dim MyData As NorthwindDataSetTableAdapters.CustomersTableAdapter = _
New NorthwindDataSetTableAdapters.CustomersTableAdapter()
‘ Create a DataSet.
Dim DS As NorthwindDataSet.CustomersDataTable = _
New NorthwindDataSet.CustomersDataTable()
‘ Fill the DataSet with data.
MyData.Fill(DS)
‘ Find a particular record using the Customer ID.
Return DS.FindByCustomerID(Customer)
End Function
[/code]

The code begins by creating a TableAdapter object. Because the example relies on the Data Source Configuration Wizard, it has a specific TableAdapter to use in the form of the NorthwindDataSetTableAdapters.CustomersTableAdapter, MyData object. MyData provides the means to select information from the table. In addition, it can update, delete, and insert records. Essentially, MyData is the database connection.

The next step is to create a DataTable object. Again, the example has a specific version, NorthwindDataSet.CustomersDataTable class, DS object. DS contains all the data selected from the database through the TableAdapter object.

In order to get data from the database into the DataTable object, the code calls the MyData.Fill() method. Until the code calls this method, DS contains all of the information about the Customers table, but none of the records.

Finally, the code calls the DS.FindByCustomerID() method to find the record requested by the caller. The input argument to this method, Customer, is a string that contains the CustomerID field value. The output from the call is a NorthwindDataSet.CustomersRow object, which is a specialized form of the DataRow. Interestingly enough, IronPython can use the DataRow directly without having to translate it in any way.

Accessing the Database Module through IronPython

The example extension has a method, GetData(), that accepts a CustomerID as input and provides a NorthwindDataSet.CustomersRow as output. All you need now is some IronPython code to make the request and display the result. Listing 17-6 shows a typical example.

Listin g 17-6: Displaying a record onscreen

[code]
# Import the Common Language Runtime.
import clr
# Access the extension.
clr.AddReferenceToFile(‘Northwind.DLL’)
import Northwind
# Create an instance of the Northwind access object.
MyData = Northwind.DBAccess()
# Fill a row with data.
Row = MyData.GetData(‘ALFKI’)
# Display the data on screen.
print ‘All the data for Customer ID ALFKI’
print ‘nCustomer ID: ‘, Row.CustomerID
print ‘Company Name: ‘, Row.CompanyName
print ‘Contact Name: ‘,
if Row.IsContactNameNull():
print ‘Nothing’
else:
print Row.ContactName
print ‘Contact Title: ‘,
if Row.IsContactTitleNull():
print ‘Nothing’
else:
print Row.ContactTitle
print ‘Address: ‘,
if Row.IsAddressNull():
print ‘Nothing’
else:
print Row.Address
print ‘City: ‘,
if Row.IsCityNull():
print ‘Nothing’
else:
print Row.City
print ‘Region: ‘,
if Row.Is_RegionNull():
print ‘Nothing’
else:
print Row._Region
print ‘Postal Code: ‘,
if Row.IsPostalCodeNull():
print ‘Nothing’
else:
print Row.PostalCode
print ‘Country: ‘,
if Row.IsCountryNull():
print ‘Nothing’
else:
print Row.Country
print ‘Phone: ‘,
if Row.IsPhoneNull():
print ‘Nothing’
else:
print Row.Phone
print ‘Fax: ‘,
if Row.IsFaxNull():
print ‘Nothing’
else:
print Row.Fax
# Pause after the debug session.
raw_input(‘nPress any key to continue…’)
[/code]

This listing looks like a lot of code, but the process is relatively simple. The example begins as usual by gaining access to CLR, using the AddReferenceToFile() method to create a reference to the extension, and creating an instance of the extension class.

At this point, the code calls MyData.GetData() with a CustomerID of ‘ALFKI‘. The output is placed in Row. If you use the dir() function on Row, you see it provides a lot more than a listing of fields that appear as part of the output. Figure 17-23 shows the attributes Row provides.

The output fields come in two types. The first are fields that the row must contain. These fields always contain data. The second are optional fields that might not contain data. If you try to print these fields, you’ll get an error. Consequently, the next section of code displays the mandatory fields first.

Notice the if…else structures that appear next. Every optional field includes an IsFieldNameNull() method. Before you print these optional fields, use the null check, such as Row.IsContactNameNull(), to verify that the field contains data. In this case, the code simply prints ‘Nothing‘ when the field is null.

You need to consider one other issue when working through your database access methods. Notice that the _Region field has an underscore in front of it. This underscore doesn’t appear in the database or in the Visual Basic.NET code — IronPython adds it for some reason. If you suddenly find that some fields aren’t accessible, even though you’re using the right name, check for an underscore. Figure 17-24 shows the output from this example.