Before we actually build the application, we will design the report first. The report will be designed to work off a single database (because we will need to create a connection to a specific database). One we find the report working fine, we can then build the application and manipulate the data source to make the report work off other databases. I'm assuming that you can design a report using SQL Reporting Services. If you are new to SQL Reporting Services, I recommend that you read the following articles in the sequence below:

• Reporting Services - A First Look
• Adding Parameters to a Report
• Accessing Reports via URLs and Embedding Reports
• Understanding Reporting Services Web Service Access

Let us look at the steps for designing the report first.

• Create a new SQL Reporting Services project called DBPropViewer and add a report called DBProperty. Feel free to choose your own names for the project and report.
• Create a shared data source (I've called it DBConnector) and point it to your SQL Server and any database on that server.
• Add a dataset called ServerProperties and use the query shown below. This query is used to fetch some server level properties for the server to which the connection is made.

SELECT
	[ServerName] = SERVERPROPERTY ('ServerName'),
	[Version] = 'SQL Server ' + CONVERT (NVARCHAR(50), SERVERPROPERTY ('ProductVersion')),
	[Edition] = SERVERPROPERTY ('Edition'),
	[FullText] = 
		CASE SERVERPROPERTY ('IsFullTextInstalled')
			WHEN 1 THEN 'Yes' ELSE 'No'
		END

• Add another dataset called ObjectCountand use the query shown below. This query uses the information present in the two information schema views information_schema.tables and information_schema.routines. The nett result of this query is the count of all procedures, functions etc and also the count of the tables present in the database.

SELECT
	[Type] = table_type,
	[Count] = COUNT(*)
FROM
	information_schema.tables
GROUP BY
	table_type
UNION ALL
SELECT
	[Type] = routine_type,
	[Count] = COUNT(*)
FROM
	information_schema.routines
GROUP BY
	routine_type

• Add another dataset called DatabaseProperties and use the query shown below. This query fetches some pieces of information from the sysdatabases table like name, creation date etc. It also queries the backupset table from the msdb database to get information about the backup dates. Note that we use a parameter called @DatabaseName to which we will pass the database for which the information is required.

SELECT
	dtb.name AS [DatabaseName],
	dtb.dbid AS [DatabaseID],
	dtb.crdate AS [CreationDate],
	(SELECT COUNT(*) FROM master.dbo.sysprocesses p WHERE p.dbid = dtb.dbid) AS [ActiveConnections],
	(SELECT MAX(backup_finish_date) FROM msdb..backupset WHERE type = 'D' AND database_name = dtb.name) AS [LastBackupDate],
	(SELECT MAX(backup_finish_date) FROM msdb..backupset WHERE type = 'L' AND database_name = dtb.name) AS [LastLogBackupDate]
FROM
	master..sysdatabases AS dtb
WHERE
	(dtb.name = @DatabaseName)

• Using the above datasets, we will now design the report. The following figure shows the report layout that I've designed. Feel free to design your own layout if you do not like mine...

• Let us now test this report. For this, we can use the Preview option. I've provided pubs as the input for the database name parameter. The following figure shows the output of the report on my machine

OK, we have now designed the report. Let us now design an application that can consume this report. We will design a Windows Forms application that will allow a user to select different databases on a server and then show information about that database by embedding the above report into the application. The following are the steps we will use to build the application.

• Create a Windows application project (I've called ReportWS)
• Add a reference to SQLDMO. We will use the routines provided in this library to query for the list of databases. To know more about SQLDMO, refer to the following article
• Add a web reference to the report server web-service. The following figure shows the web-reference dialog box. For more information refer to the following article

• Design the following interface for the application (I've called the form testForm.vb). The interface has two main elements to it. The first is a combo box which will show the list of databases present in a given server. For this, we use the functions provided in SQLDMO. The next is a browser control that we will use to refer to the above designed report. Remember we added a parameter for the database name?? That parameter will be supplied from the combo-box.

• Let us now see the code behind these controls. Before we do this, make sure that you add an imports statement for the SQLDMO library referenced earlier. The first code snippet we will see is present in the class initialize of the form. The code to be added is shown below in bold. Note that the code initializes the report server web-service and sets it up to use the default login credentials.

Private rs As ReportWS.ReportingService
Public Sub New()
    MyBase.New()

    'This call is required by the Windows Form Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call
    rs = New ReportWS.ReportingService
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials
End Sub

• The next code snippet we will add is in the form's Load event. The code to be added is shown below. This code basically establishes a connection to a SQL Server and then fetches all the databases and adds it to the combo-box. Finally, the SQLDMO object is destroyed.

Dim oServer As SQLServer
Dim oDatabase As Database

' Create a new SQL Server object
oServer = New SQLServer
Try
    ' Connect to the required SQL Server
    oServer.Connect("lp-srinivas", "sa", "password")

    ' Get a list of all the available databases and add it
    ' to the combo box
    For Each oDatabase In oServer.Databases
        cboDatabases.Items.Add(oDatabase.Name)
    Next
Catch ex As Exception
    MessageBox.Show(ex.Message, "SQLDMO Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
    oServer.Application.Quit()
End Try

• The next code snippet we will add is in the comb-boxes SelectedIndexChanged event. The code to be added is shown below. The code declares a DataSourceDefinition object and fills it with the contents of the shared data source that we created earlier (DBConnector). We then change the connection string of the definition via the ConnectString property and then update the data source back via the SetDataSourceContents method. We then form the URL required for accessing our report and then set the browser to navigate to that URL.

Dim dsDefinition As ReportWS.DataSourceDefinition
Dim strURL As String

Try
    ' Get a handle to the data source definition for our report
    dsDefinition = rs.GetDataSourceContents("/DBPropViewer/DBConnector")
    If Not (dsDefinition Is Nothing) Then
        dsDefinition.ConnectString = "data source=.;initial catalog=" & cboDatabases.SelectedItem

        ' Update the data source for our report
        rs.SetDataSourceContents("/DBPropViewer/DBConnector", dsDefinition)

        ' Point the browser back to the report. The updated data source
        ' will be used
        strURL = "http://localhost/reportserver?/DBPropViewer/DBProperty&rc:Command=Render&rc:Toolbar=False&DatabaseName=" & cboDatabases.SelectedItem
        webBrowser.Navigate(strURL)
    End If
Catch ex As Exception
    MessageBox.Show(ex.Message, "RS Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try

• That's it! Our application is ready. Let us now test it. The following figure shows the output that I see on my computer. You can select different databases and see the report change accordingly. Behind the scenes, remember that we are changing the database connection string silently!! That's a very useful capability, right??

There are many other useful applications that we can write using this capability of report server web-services and we will see some other applications in future articles. For now, have fun with this application :-) You can build other capabilities into this application. Here are some ideas:

• Displaying the size of the database as a pie-chart
• Displaying information about other objects in the database