MS-Access / Getting Started

---

Creating a Registration Web Service

Once you've generated some registration keys, you'll need some mechanism for users to validate the key. In this example, we'll create a Web service that communicates with a SQL Server database on the server that contains registration keys.

Create the Registration Database

The registration database will be stored on SQL Server. For the purposes of the example, we are running SQL Server 2005 Express Edition on Windows 2003 Server with Internet Information Services (IIS). In a production environment, you might run SQL Server and IIS on separate computers.

Begin by creating a new database on SQL Server called AppReg. Create a new table in this database called PID with the following columns:

Field Name	Data Type	Properties
ip	int	Identity (1,1)
pidkey	nvarchar(255)
used	bit

The id column is an IDENTITY column similar to the AutoNumber data type in Access. The pidkey column is used to store the actual registration key. The used column is a bit field that will be set to True when a successful registration occurs. This is also used to prevent existing registration keys from being reused. Add some data to this table. The registration keys that you add will be used later.

The Web service will connect to the database using SQL authentication. Make sure SQL authentication is enabled on the SQL Server. Add a new user to the SQL Server called wsuser with SELECT and UPDATE permissions on the PID table.

Create the Web Service

We will write the Web service using C# in Visual Studio 2005. Follow these steps to set up the Web service and prepare it for code.

1. Launch Visual Studio 2005.
2. Click File, then click New, and choose Web Site.
3. Choose ASP.NET Web Service in the New Web Site dialog box.
4. Change the location to HTTP and enter the address of a Web server to which you have access as follows:
   http://servername/regws
5. Rename the Service.asmx file in the Web site to Registration.asmx.
6. In the Registration.asmx file, change the CodeBehind attribute of the Web service to point to Registration.cs, and the Class attribute from Service to Registration.
7. Rename the Service.cs file in the App_Code folder in the Web site to Registration.cs.
8. In the Registation.cs source file, rename the class from Service to Registration.
9. Remove the HelloWorld method that appears in the Registration.cs source file.
10. Change the Namespace property of the WebService attribute of the class.
11. Select Add New Item from the Website menu in Visual Studio and choose Web Configuration File, and then click OK.
12. Edit the web.config file to add the following code beneath the system.web element in the configuration file.

    <webServices>
     <protocols>
      <add name="HttpGet"/>
     </protocols>
    </webServices>

With the core steps out of the way, we can add some code. Begin by adding the following using statements to the top of the Registration.cs source file:

using System.Data;
using System.Data.SqlClient;

Add the following private data to the class:

SqlConnection m_connection = null;

Add the following WebMethod to the class. This is the actual method that will be executed using the Web service.

    [WebMethod]
    public bool IsValidPIDKey(string pidKey)
    {
	try
	{
	    if (GetPIDCount(pidKey) > 0)
	    {
		UpdatePIDRegistration(pidKey);
		return true;
	    }
	    return false;
	}
	catch (Exception)
	{
	    throw;
	}
	finally
	{
	    m_connection.Close();
	}
    }

This method calls two helper methods. The first method, GetPIDCount, returns the number of unused registration keys that match the specified key in the PID table.

    private int GetPIDCount(string pidKey)
    {
	string connString = "Data Source=<SQLServerName>";
	connString += ";Initial Catalog=AppReg";
	connString += ";User ID=wsuser";
	connString += ";Password=<YourPassword>";
	// this is the actual action we want to take
	m_connection = new SqlConnection(connString);
	m_connection.Open();
	// build the command text
	string commandText = string.Format("SELECT COUNT(*) FROM " +
	    "pid WHERE used = 0 AND pidkey = {0}", pidKey);
	SqlCommand command = new SqlCommand(commandText);
	command.Connection = m_connection;
	command.CommandType = CommandType.Text;
	// return
	return (int)command.ExecuteScalar();
    }

The next method, UpdatePIDRegistration, updates the PID table when a registration key is used:

    private void UpdatePIDRegistration(string pidKey)
    {
	if (m_connection.State == ConnectionState.Closed)
	{
	    m_connection.Open();
	}
	string commandText = string.Format("UPDATE pid SET used = 1 WHERE " +
	    "pidkey = {0}", pidKey);
	SqlCommand command = new SqlCommand(commandText);
	command.Connection = m_connection;
	command.CommandType = CommandType.Text;
	// execute
	command.ExecuteNonQuery();
    }

Save the source file and build the Web site.