Creating the Data-Centric Object

In VS .NET, select the Class1 code module located in the NorthwindDC project. Rename the code module to RegionDC.vb and clear the code module. Add the following four Imports statement:

 Imports NorthwindTraders.NorthwindShared.Interfaces Imports NorthwindTraders.NorthwindShared.Structures Imports System.Configuration Imports System.Data.SqlClient 

In Chapter 2, "Building an N-Tier Application," you created a reference to the NorthwindShared project, which allows you to import NorthwindTraders.NorthwindShared.Interfaces and Structures. The System.Configuration namespace reads the database connection string from the web.config file. The SqlClient namespace contains libraries used to access SQL Server.

Create the class declaration as follows:

 Public Class RegionDC     Inherits MarshalByRefObject     Implements IRegion End Class 

Tip

Microsoft made (besides other changes) one very cool change between release 1.0 and 1.1 of VS .NET. If you are using release 1.1, when you add the Implements IRegion line, all of the interfaces that are contained in IRegion will be automatically generated for you.

The first thing to notice about the RegionDC class is the MarshalByRefObject, which forms the basis for the RegionDC class. This allows the .NET Framework to create a proxy on the remote system. Without this statement, a runtime error would occur when the first remoting call is made. Next, you are implementing the IRegion interface you created earlier. When you first add this line of code, IRegion will be underlined, which denotes an error (this only applies to version 1.0 of the .NET Framework). If you have the Task List visible, you will see something that looks like Figure 3-6.

Figure 3-6: Interface implementation errors

Basically, it says you have not implemented the interface yet, and until you implement all of the methods, you will continue to see these errors.

Note

When you implement an interface, it is a contract that says your object implements all of the methods of the interface. This allows you to make the remote procedure call work.

Understanding Class Attributes

Before you can implement the interface, you need to set up the class attributes. I like to create regions for certain sections of code so that I can collapse them and get them out of the way.

Caution

When I use the term regions, I am talking about regions within the code, not the Region table in the Northwind database.

Note

At this point, it might be a good idea to look at the ClassGenerator application available for download from the Apress Web site (http://www.apress.com). This application makes the process of creating class attributes easier.

Create the following region and add the following attributes to the RegionDC class:

 #Region " Private Attributes"     Private mintRegionID As Integer     Private mstrRegionDescription As String #End Region 

Next, you will create the public attributes of your class. You will create the RegionID as a read-only attribute and the RegionDescription as a read/write attribute. RegionID is set by your stored procedure in the database and should not be settable by outside objects. Create another region and the public properties, as shown in Listing 3-5.

Listing 3-5: The Public Attributes of the RegionDC Class

 #Region " Public Attributes"     Public ReadOnly Property RegionID() As Integer         Get             Return mintRegionID         End Get     End Property     Public Property RegionDescription() As String         Get             Return mstrRegionDescription         End Get         Set(ByVal Value As String)             mstrRegionDescription = Value         End Set     End Property #End Region 

Implementing the Interface

Now it is time to start implementing the IRegion interface. First you will implement the LoadProxy interface. In a class that has so few attributes, the advantage of the LoadProxy method is not apparent, but as you move into building the Employee object, it will become apparent. Enter the following code to declare the LoadProxy method (again, if you are using version 1.1, this will already be declared for you):

 Public Function LoadProxy() As DataSet Implements IRegion.LoadProxy End Function 

The name of the method that implements the LoadProxy interface is not important. It is only important that the method signature is the same as the LoadProxy signature. However, for readability and as a convention, the names should be the same. Also, creating a method signature that matches the interface signature is not enough—you need to mark it as implementing the interface. To do that, you add the Implements IRegion.LoadProxy onto the end of your method signature. When you do that, there will be one less error line in the Task List.

Enter the following method stubs so that you can complete the implementation of the interface:

 Public Function LoadRecord(ByVal intID As Integer) As structRegion Implements _ IRegion.LoadRecord End Function Public Sub Delete(ByVal intID As Integer) Implements IRegion.Delete End Sub Public Sub Save(ByVal sRegion As structRegion, ByRef intID As Integer) _ Implements IRegion.Save End Sub 

The LoadProxy Method

Now that you have implemented your interface methods, you will add some code to them, starting with the LoadProxy method. Add the code in Listing 3-6 to the LoadProxy method.

Listing 3-6: The LoadProxy Method

 Dim strCN As String = ConfigurationSettings.AppSettings("Northwind_DSN") Dim cn As New SqlConnection(strCN) Dim cmd As New SqlCommand() Dim da As New SqlDataAdapter(cmd) Dim ds As New DataSet() cn.Open() With cmd     .Connection = cn     .CommandType = CommandType.StoredProcedure     .CommandText = "usp_region_getall" End With da.Fill(ds) cmd = Nothing cn.Close() Return ds 

The first line of this code reads the web.config file to retrieve the database connection string. Normally, reading XML is a slow process compared to other types of file reads. However, reading the configuration file is fast because that file exists in memory when IIS starts. Everything else is a straightforward call to the database using the Command object and a data adapter to fill the dataset.

Note

For more information on making calls to a database, see Database Programming with Visual Basic .NET, Second Edition by Carsten Thomsen (Apress, 2002). This is a good solid introduction to using ADO.NET.

The LoadRecord Method

Now, you will implement the LoadRecord method. Enter the code in Listing 3-7 to the LoadRecord method of the RegionDC class.

Listing 3-7: The LoadRecord Method

 Dim strCN As String = ConfigurationSettings.AppSettings("Northwind_DSN") Dim cn As New SqlConnection(strCN) Dim cmd As New SqlCommand() Dim da As New SqlDataAdapter(cmd) Dim ds As New DataSet() Dim sRegion As structRegion cn.Open() With cmd     .Connection = cn     .CommandType = CommandType.StoredProcedure     .CommandText = "usp_region_getone"     .Parameters.Add("@id", intID) End With da.Fill(ds) cmd = Nothing cn.Close() With ds.Tables(0).Rows(0)     sRegion.RegionID = Convert.ToInt32(.Item("RegionID"))     sRegion.RegionDescription = _         Convert.ToString(.Item("RegionDescription")) End With ds = Nothing Return sRegion 

For compatibility, you convert the dataset to a structure and return the structure. Java is not able to easily read a dataset. Because you may use this call in an XML Web service later, you want to return a set of data that is easily understood by any application that can read an XML file. Although datasets are easily serialized into XML data, the XML structure can be extremely complicated. Another .NET application could read a serialized dataset easily, but an application written in another language may not be able to read it quite so easily. When you put a Web service interface on your application, you will need to return the information in a format that all languages can parse easily.

Note

It is not that Java cannot understand a dataset, but for Java to use the dataset, it would have to perform extensive parsing of the SOAP envelope. Fortunately, because .NET was built with Web services in mind, .NET handles all of this transparently. Often in the enterprise, however, you need to develop with different platforms in mind.

Most of Listing 3-7 is similar to the code in the previous method except that you are now passing an ID into a stored procedure. There are six overloaded methods on the Command.Parameters.Add method, which you will explore as you create more commands that call stored procedures.

The Delete Method

Enter the code in Listing 3-8 into the Delete method of the RegionDC class.

Listing 3-8: The Delete Method

 Dim strCN As String = ConfigurationSettings.AppSettings("Northwind_DSN") Dim cn As New SqlConnection(strCN) Dim cmd As New SqlCommand() cn.Open() With cmd     .Connection = cn     .CommandType = CommandType.StoredProcedure     .CommandText = "usp_region_delete"     .Parameters.Add("@id", intID)     .ExecuteNonQuery() End With cmd = Nothing cn.Close() 

The only real difference in Listing 3-8 is that you actually execute the command on the Command object explicitly. In the prior blocks of code, the data adapter executed the Command object. You do not need the data adapter here because you are not retrieving data and you are not working with prepared datasets.

The Save Method

Lastly, you come to the Save method. To implement this method, add the code in Listing 3-9 to the Save method.

Listing 3-9: The Save Method

 Dim strCN As String = ConfigurationSettings.AppSettings("Northwind_DSN") Dim cn As New SqlConnection(strCN) Dim cmd As New SqlCommand() With sRegion     Me.mintRegionID = .RegionID     Me.RegionDescription = .RegionDescription End With cn.Open() With cmd     .Connection = cn     .CommandType = CommandType.StoredProcedure     .CommandText = "usp_region_save"     .Parameters.Add("@id", mintRegionID)     .Parameters.Add("@region", mstrRegionDescription)     .Parameters.Add("@new_id", SqlDbType.Int).Direction = _         ParameterDirection.Output     .ExecuteNonQuery()     intID = Convert.ToInt32(.Parameters.Item("@id").Value) End With cmd = Nothing cn.Close() 

This method implements a couple of new things, so let's examine different parts of this routine. First, you are taking the values from the sRegion structure and assigning them to your object's attributes. However, the RegionID value is being assigned to the private member variable, and the RegionDescription value is being assigned to the public member variable. This is because there are no business rules to check for a system-generated number. There are rules that need to be checked for the values entered by the user. You will see how this works in Chapter 5, "Building Business Objects" when you learn about business rules.

There is also a new usage of the Parameter object. The last Parameter Add method looks like the following:

 .Parameters.Add("@new_id", SqlDbType.Int).Direction = _      ParameterDirection.Output 

You need to specify the parameter, but because you are not passing anything in, you do not have to specify a value. However, you do need to provide the data type of the variable you are adding. Finally, you need to set the direction equal to Output. By default, the parameter direction is Input.

Notice also how simple this routine is. The logic to determine how the save is done is in the database, so the code does not need to know how to save the object (using insert or update)—it just knows that it needs to call the Save method.