Preparing the Database

In Chapter 7, “Connecting Web Services to Databases,” you learned how to create and manipulate databases by performing ADO.NET operations within a web service. Throughout this chapter, you will create web services that query databases to determine whether or not a program is specifying a valid registration key. Ideally, to perform such operations, you would create a database that contained one or more tables that stored your registration data. For simplicity, this chapter will instead create Dataset objects “on the fly” that contain the registration information. The web services will build the DataSet objects from XML entries that correspond to the data. For example, the XML data in Listing 15.1 represents the fields necessary to track registration data.

Listing 15.1 Registration.xml

<DataSet>  <Table>   <Name>John Doe</Name>   <Email>[e-mail]</Email>   <Key>AAAA0000</Key> </Table>  <Table>   <Name>Jane Doe</Name>   <Email>[e-mail]</Email>   <Key>AAAA0001</Key>  </Table> </DataSet>

As you can see, the XML data consists of three fields: a name, an e-mail address, and a license key. By using the XML-based files, as opposed to databases, we simplify the steps you must perform to “test drive” the concepts that this chapter presents. The XML-based files, however, are not well suited for professional-quality applications because they require that you set permissions that potentially open the door to hackers and they do not have the same performance or flexibility (such as multiple simultaneous access) as would a true database.

The Visual Basic .NET program in Listing 15.2, BuildRegistrationDataSet.vb, illustrates how a web service can build a DataSet object from an XML file. When you run the program, your screen will display a form that contains a button and a text box. When you click the Build DataSet button, the program will use the XML file to create a DataSet object. The program will then loop through each of the elements within the DataSet, displaying the fields in the text box, as shown in Figure 15.1.

Figure 15.1: Building a DataSet object from an XML file

To create the BuildRegistrationDataSet.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name BuildRegistrationDataSet. Select OK. Visual Studio .NET will display a form onto which you can drag and drop the program’s controls.

3. Using the Toolbox, drag and drop the button and text box previously shown in Figure 15.1 onto the form.

4. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 15.2.

Listing 15.2 BuildRegistrationDataSet.vb

Private Sub Button1_Click(ByVal sender As System.Object, _ ÄByVal e As System.EventArgs) Handles Button1.Click      Try      Dim DataSetObj As New DataSet()      DataSetObj.ReadXml("Registration.xml")      Dim I As Integer      TextBox1.Text = ""      For I = 0 To DataSetObj.Tables(0).Rows.Count - 1         TextBox1.Text = TextBox1.Text & _            DataSetObj.Tables(0).Rows(I).Item("Name") & vbCrLf         TextBox1.Text = TextBox1.Text & _            DataSetObj.Tables(0).Rows(I).Item("Email") & vbCrLf         TextBox1.Text = TextBox1.Text & _            DataSetObj.Tables(0).Rows(I).Item("Key") & vbCrLf      Next      Catch Ex As Exception        MessageBox.Show(Ex.Message)      End Try  End Sub

As you can see, the code uses the ReadXML method to read the contents of the Registration.xml file and to build the DataSet object. After that, the code simply loops through the records the object contains. The Registration.xml file must reside within the program’s current directory (the bin folder).

The web services that this chapter presents will call the GetDataSet method that returns a DataSet that contains the registration information. As discussed, to simplify the coding, the method will build the DataSet object from an XML file. Ideally, for your “production-quality” web services, you would use a database to generate the DataSet.

Note 

One problem with building DataSet objects from an XML-based file is that there may be times when one web service must read the file’s contents while a different web service is writing the file’s new contents. A database is much better suited to handle such locking conditions. As the number of programs using the services increases, so too does the likelihood of such conflicts.

The Visual Basic .NET program in Listing 15.3, AddRecord.vb, displays a form similar to that shown in Figure 15.2, which prompts the user to enter the information necessary to register for a license key. After the user enters the data and clicks the Add Record button, the program places the record into the DataSet and then updates the XML file that contains the DataSet entries.

Figure 15.2: Adding a registration record to the XML-based DataSet

To create the AddRecord.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name AddRecord. Select OK. Visual Studio .NET will display a form onto which you can drag and drop the program’s controls.

3. Using the Toolbox, drag and drop the button and text boxes previously shown in Figure 15.2 onto the form.

4. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 15.3.

Listing 15.3 AddRecord.vb

Imports System.IO Public Class Form1     Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code "   'Generated code not shown. #End Region Private Sub Button1_Click(ByVal sender As System.Object, _ ÄByVal e As System.EventArgs) Handles Button1.Click    If TextBox1.Text.Length = 0 Then      MessageBox.Show("You must specify a name")    ElseIf TextBox2.Text.Length = 0 Then      MessageBox.Show("You must specify a e-mail address")    Else      Try        Dim DataSetObj As New DataSet()        DataSetObj.ReadXml("Registration.xml")        Dim LastRecord As Integer        Dim LastKey As String        Dim NumberPortion As String        Dim LetterPortion As String        Dim Number As Integer        LastRecord = DataSetObj.Tables(0).Rows.Count - 1        LastKey = DataSetObj.Tables(0).Rows(LastRecord).Item("Key")        NumberPortion = LastKey.Substring(4, 4)        LetterPortion = LastKey.Substring(0, 4)        If (NumberPortion = "9999") Then          If (LetterPortion = "AAAA") Then            LetterPortion = "BBBB"          ElseIf (LetterPortion = "BBBB") Then            LetterPortion = "CCCC"          End If          NumberPortion = "0000"        Else          Number = CInt(NumberPortion)          Number = Number + 1          If Number < 10 Then            NumberPortion = "000"          ElseIf Number < 100 Then            NumberPortion = "00"          ElseIf Number < 1000 Then            NumberPortion = "0"          End If          NumberPortion = NumberPortion & Number.ToString()        End If        Dim I As Integer        Dim XMLFile As New StreamWriter("Registration.xml")        XMLFile.Write("<DataSet>")        For I = 0 To DataSetObj.Tables(0).Rows.Count - 1           XMLFile.Write("<Table>")           XMLFile.Write("<Name>")           XMLFile.Write(DataSetObj.Tables(0).Rows(I).Item("Name"))           XMLFile.Write("</Name>")           XMLFile.Write("<Email>")           XMLFile.Write(DataSetObj.Tables(0).Rows(I).Item("EMail"))           XMLFile.Write("</Email>")           XMLFile.Write("<Key>")           XMLFile.Write(DataSetObj.Tables(0).Rows(I).Item("Key"))           XMLFile.Write("</Key>")           XMLFile.Write("</Table>")         Next         XMLFile.Write("<Table>")         XMLFile.Write("<Name>")         XMLFile.Write(TextBox1.Text)         XMLFile.Write("</Name>")         XMLFile.Write("<Email>")         XMLFile.Write(TextBox2.Text)         XMLFile.Write("</Email>")         XMLFile.Write("<Key>")         XMLFile.Write(LetterPortion & NumberPortion)         XMLFile.Write("</Key>")         XMLFile.Write("</Table>")         XMLFile.Write("</DataSet>")         XMLFile.Close()         TextBox3.Text = LetterPortion & NumberPortion         TextBox3.ReadOnly = True         Catch Ex As Exception            MessageBox.Show(Ex.Message)         End Try       End If     End Sub End Class

To begin, the program uses the Registration.xml file to create a DataSet object. Then, the code moves to the last record to determine the last key used. The program breaks the key into two parts: a numeric part that ranges from 0000 to 9999 and a letter portion that ranges from AAAA to CCCC (which lets the program support 30,000 users). After the program determines the user’s new key, the program then creates a new Registration.xml file that contains the new entry.

As you will see, most of the web services this chapter presents make use of two databases, the first that contains information for programmers who have paid to use the service and the second that contains information for programmers who have registered to use a trial version of the service. To represent the two databases, place the XML entries in Listing 15.4 within the file PaidUsers.xml.

Listing 15.4 PaidUsers.xml

<DataSet>  <Table>   <Name>John Doe</Name>   <Email>[e-mail]</Email>   <Key>AAAA0000</Key>  </Table> </DataSet>

Then, place the entries in Listing 15.5 within the file TrialUsers.xml.

Listing 15.5 TrialUsers.xml

<DataSet>  <Table>   <Name>Jane Doe</Name>   <Email>[e-mail]</Email>   <Key>AAAA0001</Key>  </Table> </DataSet>

Note 

You must place these XML files within the bin folder of each web service you create.