Getting Set Up to Use the API | Microsoft SQL Server 2005 Notification Services

The SQL-NS Subscription Management API is a managed code API. In other words, it is written using the .NET Framework and requires the Common Language Runtime (CLR). It can be used from any of the .NET languages (such as C#, VB .NET, or Managed C++). In this chapter, the code examples are written in C#.

Note

Although the API is written in managed code, you can still use it in native code applications through COM interop. However, this is not covered in this chapter. For more information on how to use the API through COM interop, see the SQL-NS Books Online.

This section describes the steps you need to take to get your system set up to work with the code in this chapter. The instructions here assume that you have set up your development system as described in Chapter 2, "Getting Set Up," and that you've been able to successfully build the code in previous chapters. It is also assumed that you're going to use Microsoft Visual Studio .NET to do the development work in this chapter. If you are using another development tool, you will have to adapt the Visual Studio instructions given in this chapter to your tool's environment.

The SQL-NS Assembly

The SQL-NS API is implemented in an assembly called Microsoft.SqlServer.NotificationServices.dll. In this and future chapters, I'll refer to this as the "SQL-NS assembly." When you install SQL-NS, this assembly is installed in the bin directory of your SQL-NS installation. On my system, the full path to this directory is

 C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\bin

On your system, the location might vary, depending on where you installed SQL-NS and the version that you installed. (At the time of this writing, version 9.0.242 is the latest version, and this is reflected in the preceding path.)

The SQL-NS assembly installed in this location is intended to be referenced at development time, to obtain the signatures of the public classes, methods, and properties in the SQL-NS API. When you create a Visual Studio project that uses the SQL-NS API, you will need to add a reference to this SQL-NS assembly. Visual Studio should be configured to locate the assembly from the appropriate path on your system automatically and show it to you in the list of available assemblies, as explained in the section that follows. If you're not using Visual Studio, you'll need to locate the assembly on your system yourself and refer to it by full path when building your code.

Referencing the SQL-NS Assembly

Any code you write that uses the SQL-NS API must reference the SQL-NS assembly. The code samples in this chapter already have the reference set up, but if you create a new Visual Studio project for any code you write on your own, you can add a reference to the assembly by performing the following steps:

1.	In Visual Studio's Solution Explorer, expand the node for your project.
2.	Right-click the References node and choose Add Reference from the context menu.
3.	In the Add Reference dialog box, select the .NET tab and scroll through the list of available assemblies to find `Microsoft.SqlServer.NotificationServices`.
4.	Select `Microsoft.SqlServer.NotificationServices` and click OK.

After doing this, you see Microsoft.SqlServer.NotificationServices listed under the References node for your project in Solution Explorer.

Tip

If the SQL-NS assembly does not appear in the list in the .NET tab of the Add Reference dialog box, you can browse for it manually by selecting the Browse tab and then navigating to the bin directory of your SQL-NS installation (described in the previous section).

Preparing the SQL-NS Instance

In this chapter, we build an SMI for the music store application that we prototyped in Chapters 5 and 6. To ensure that you're starting from the right place after the testing done in those chapters, re-create the instance from scratch.

To do this, go back to Chapter 6 and perform the instructions listed in the "Final Testing of the Prototype" section (p. 179). Pay close attention to step 5 (ensuring that your ADF contains the complete, correct prototype code). If necessary, you can always refer to C:\SQL-NS\Chapters\06\SupplementaryFiles\ApplicationDefinition-6.xml (copy this file from the CD-ROM if you changed it on your development machine), which contains the code in its correct final form. Do complete all the steps (including the testing steps, 10 through 17) just to verify that the instance is still working as expected.

The SMI Visual Studio Solution

The SMI we build in this chapter is a web front end for the music store application. Its user interface consists of a website that users can log in to and manipulate their subscriptions in the music store application. Its logic layer implements the required operations using the SQL-NS API.

The SMI is implemented as an ASP.NET 2.0 website in Visual Studio. You'll find all the source files for the SMI in the C:\SQL-NS\Samples\MusicStore\MusicStoreWeb directory. Use the following instructions to open the SMI solution (which contains the code for both the user interface and the logic layer) in Visual Studio:

1.	Start Visual Studio .NET 2005.
2.	From the File menu, choose Open, Web Site.
3.	In the panel on the left, select the File System icon and then browse the folder tree to find the `C:\SQL-NS\Samples\MusicStore\MusicStoreWeb` folder.
4.	Select the `C:\SQL-NS\Samples\MusicStore\MusicStoreWeb` folder and click Open.
5.	If the Solution Explorer is not visible, go to the View menu and select Solution Explorer (or press Ctrl+Alt+L) to open it.

Do not run the code at this time. It will not work correctly because we have not yet created some of the supporting objects required for its operation. For now you should open the solution so that you can browse through the code as you read this chapter.

Creating the Database Objects for the ASP.NET Membership Provider

As you might expect, our SMI will need to create and manage accounts for its users. ASP.NET 2.0 (on which our SMI is built), provides facilities for managing user accounts in web applications via membership providers. A membership provider is a component that stores and retrieves user account information in a particular way. In our application, we use ASP.NET's built-in SqlMembershipProvider to store user account information in the music store's SQL database. The SqlMembershipProvider implements the operations required to create, delete, and modify user accounts. Without this provider, we'd have to implement all these operations ourselves.

The SqlMembershipProvider relies on a set of database objects to store and manipulate user account information. We need to create these objects in our MusicStore database to enable it for use with the SqlMembershipProvider. Use the following steps to create the required database objects:

Make sure that you've created the MusicStore instance as described in the "Preparing the SQL-NS Instance" section (p. 192).

Open the script, C:\SQL-NS\Samples\MusicStore\CreateASPNETObjects.sql in Management Studio, and execute it.

The script will display several status messages as it executes. You may also see error messages with the following text:

 Cannot grant, deny, or revoke permissions to sa, dbo,  information_schema, sys, or yourself.

You can safely ignore these messages.

Examine the MusicStore database in Object Explorer to see the objects created (you may need to refresh the Object Explorer tree). All the objects created by the script you just ran are in the dbo schema and have names that begin with aspnet_. For example, you'll find a table called dbo.aspnet_Users.

The objects created in this section fall into the category of non-SQL-NS objects, as depicted in the data layer in Figure 7.2. They store supporting information about subscribers that is outside the scope of the tables created by the SQL-NS compiler.

Database Permissions for Subscription Management

To perform subscription management operations, the account under which the SMI runs must have access to the database containing the instance and application database objects and be made a member of the NSSubscriberAdmin role in that database.

In the development phase, you run the SMI code under your development account (if you're using Windows Authentication, this is the Windows account under which you're logged in to your development machine; if you're using SQL Server Authentication, you'll use the SQL username and password of the development account you created in Chapter 2). Because this is also the account you used to create the MusicStore database, it is the database owner and therefore has the required permissions to connect and perform subscription management operations. It isn't necessary to explicitly grant the development account membership in the NSSubscriberAdmin role.

Note

As in previous chapters, the account and security recommendations given here apply to your development environment only. When deploying your SQL-NS application in production, you will most likely use different accounts and configure the security settings differently. Chapter 13, "Deploying a SQL-NS Instance," provides detailed information on security considerations for deployment.