SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SCOME - Part 4: Using Visual Studio To Develop The Report

By Drew Salem,

A recap on things so far

Hello again, and welcome to SCOME – Centralizing with ASP.Net, where in part 4 we'll dive head first into the world of Microsoft's Visual Studio. Are the primates still swinging on your SQL Servers and jumping on keyboards where you are? Yeh, they are here too, but our suite of reports will allow us to not only centralize *all* aspects of our monitoring, but also create web applications that will help us manage our day to day DBA duties. In part one we discussed the reasons for Centralizing, and how this can be achieved using the SCOME technique. In part 2 we set up our linked servers and the initial setup tables. In part 3 we started on our Failed Jobs report and discussed how this report can be easily changed to reflect any SQL Server data held in it's system tables and views. Today we finally get to do .Net things. I intended on completing the Failed Jobs report today as it's not quite finished (the final report will allow to extract keyworded entries in the log files), but some people have been very keen to get on, so we’ll extend the Failed Jobs scripts later after we’ve implemented it as an actual report in .Net.

What this article is about

This article is an introduction to Microsoft Visual Studio, the .Net framework, how the web pages fit together and other bits and pieces relevant to writing web applications. It is a straight-forward no-nonsense, sweet and sassy guide where the idea is to get us up and running as quickly as possible. Just a quick reminder that to make this suitable for as many people as possible, I will assume you know nothing, so forgive me if I iterate over something that may seem obvious or something you already know. As there is so much to cover, I'll actually do very little explaining on how some things are the way they are in .Net, or the technology behind them, but feel free to google these as, needless to say, there are very good websites that do this. The aim here however is to get you up and running, so that you can systematically build your own .Net solutions in your own time, that will in-turn aid your day to day DBA duties. You could use Reporting Services to do much of this, but as you get whizzy at developing, you'll find that you may want to use Microsoft .Net components and/or third party components not easily available to the vanilla framework used by Reporting Services, whereas conversely, you can build Reporting Services pages on top of your web app from within Visual Studio. We'll do all this in VB.Net as it's more intuitive than C#.Net, but if you'd rather not, there are lots of free online VB.Net to C# converters. It's worth mentioning that there is nothing that can be done in C#.Net that can't be done in VB.Net. They are both based on the same framework.

The Client Browser, the Web Server and the Database Server

So let's say we use the Payroll web application to look up an employee's details. We punch in the Payroll number and back come the details. What's actually happening is the browser on your PC puts in a request to a web server. The web server holds the code (that we write in the form of web pages) that tells the browser (a) what the page will look like and (b) what the web server needs to do to retrieve the employee's details. Our code on the web server says (to the web server), if the browser sends an employee number, go pass it as a parameter to some stored procedure on this database server. SQL Server does it's thing and passes the results back to the web server, that then passes it back to the client browser. Incidentally, this web server may be somewhere across the world (like the web server that these pages you are reading right now are on), it may be a web server on your internal network, or it could even be the same client PC you are actually browsing from. Likewise, the database server can be remote or local.

Figure 1 - PC Client to Web server to Database Server and back again

Visual Studio allows you to design the look of the web pages (written in HTML and Cascading Style Sheets) as well as write the code that talks to the database (VB.Net or C#.Net). The process may seem a bit much if you've not been exposed to this before, but fortunately we don't have to write much of the interacting components, as the .Net framework was developed for this purpose alone. We just drag the components we want to use within Visual Studio to the page and connect them together with snippets of code.

The .Net Framework

When I first got myself a PC, I didn't know know what the heck the ".Net framework" was. Every time I downloaded a new app to install, the instructions said you must first have the .Net framework installed. Meant nothing to me. There was nothing in my Programs start menu that remotely indicated that I'd have it installed, and when I discovered that it was, I couldn't actually find it. There was no GUI to the framework, what was I supposed to do with it? The reason for this is that you don't get to see it. The framework is just a load of dll files that your application relies on to function. Once downloaded (from Microsoft) and installed, it gets placed into the bowels of your operating system and your application (if a windows form) then uses it. If your application is a web one, such as what we are developing, then the framework must be installed on the web server. In essence, all the .Net framework is, is a lazy mans way of writing less code. You see the dialog box that you use to browse to a file? Chances are that was a .Net component dragged and dropped on to the web page or windows form from a Toolbox in Visual Studio. Just like the Toolbox in the SSMS Maintenance Plan screen. See that drop down list? Also, a .Net component dragged and dropped. See the radio buttons, or the data grid, the context menu (the one that appears when you right click), the login screen, or the calendar control? All .Net components that have been dragged and dropped. All we have to do is tell the web server what we want doing with these by writing code that connects the components together. And we do this using Visual Studio.

Visual Studio

Visual Studio is a an integrated development environment (IDE) from which you can do anything. You can build windows apps, web apps, mobile apps and anything in between using a zillion and one different languages. It's all there in one contained space. It will compile your apps, package them, create dlls, create install wizards. It allows you to use Microsoft components, third party components and open source components. You can design your app via code or the inbuilt graphical designer. It has a full blown debugger that will step through your code one line at a time so you can see where your app goes wrong. Comparatively speaking, it's a very friendly bit of kit. It's just on your side. Heck, I even made a Beef Goulash last week using Visual Studio and that's no lie.

So let's get started. If you don't have a copy of Visual Studio, now is the time to download it. Visual Studio Web Developer 2008 Express Edition is a free cut down version of Visual Studio Pro. I use the Pro edition, but to the best of my knowledge everything we will cover can be done in the freebie one. When you come to install it, make sure you choose Visual Basic as one of the languages you want to use.

Creating a New website

To create a new website, we go to File -> New website (in the future, when you want to work further on your site, go to File -> Open Website, and browse to the folder containing your website). You should be presented with the following screen.

Figure 2 - Creating a new website

1. Select the ASP.Net website icon. This will setup the necessary files to start a blank ASP.Net site.

2. Choose the type of location where you want the files placed. You can choose between a virtual directory, an FTP site or in some folder. We're going for some folder; so choose File System.

3. Microsoft adds new components to the framework every now and again to make life a bit more easier for you and me. Every time they do, they increment the versions. At the time of writing, the latest version was 3.5. I first started my suite of reports when the latest version was 2.0. Every time, I open my web site in Visual Studio, it asks me if I would like to upgrade the website to use .Net 3.5. I always so no, because I've kind of learnt not to bother with things unless I really need to. So here, by all mean's select the latest version. If you want to be really safe though while following along with me, select .Net Framework 2.0. Later, you can upgrade when you know it's all working.

4. Select the path where the website will be kept during development, and hit OK. You should now be presented with the Visual Studio IDE.

Figure 3 - The Visual Studio IDE

The IDE dissected

1. This is the main design area of the screen. This is where your web page or windows form gets designed and the code gets written.

2. This is the Toolbox area, from which you drag and drop components onto your design area. Components such as Dialog Boxes, Drop down lists, Combo Boxes etc. These are grouped into, well groups.

3. The Solution Explorer displays all the files that make up your entire application solution. All your reports will be listed here in the form of web pages.

4. That little Play symbol runs your app when you want to see how it will look like in the real world.

5. The Properties pane. Every element, every page, anything you click has properties and they can be viewed and changed here. Do you want the checkbox to default to checked or unchecked, what colour do you want the page background to be and so on.

6. These tabs at the bottom allow you to switch views from Source View, where you get to see the code behind the design of the page, Design View where you get to see how the page will physically look or Split View where the screen is split and you get to see both (useful when there is lots of code and it gets a bit confusing knowing what does what).

7. Finally, that bottom bit there, is by default the location of various information panes such as what's happening when you are debugging, any errors or warnings regarding your code, build progress etc.

If we take a closer look at the Solution Explorer, you'll find various elements have been placed in the pane.

Figure 4 - Solution Explorer

Ignore the App_Code and App_Data folders, let's look at the others...

1. The web.config file is an xml file and is the most important file you will have in your solution. Without it, your web app won't run. It contains configuration items related to the site as a whole such as connections strings, the security model to be used, the libraries to be used, maximum and minimum limitations you may want to enforce (such as the maximum size a file can be uploaded to your site, or the length of time before a page times out etc). Like everything in Visual Studio, you can edit this file manually, but once again the good people at Microsoft makes life easy for the lazy man by providing all sorts of GUI windows to edit the Web.config file.

2. The Default.aspx and Default.aspx.vb files. When your folder contains lots of web pages (or web forms as they're called in ASP.Net), the web server will look for the Default.aspx and take that has your home page. Unless otherwise specified, it is the first form that it will load when someone visits you site.

The VB.Net code does not have to be in the .aspx.vb page, it can actually go in the .aspx page as long as it is in between <% and %>, but it's good to keep things separate.

For example www.MySQLReports.com/Default.aspx. The aspx extension is typical for a .Net web form. Notice how Default.aspx.vb comes under the Default.aspx? Every time you create a new .aspx web form in Visual Studio, a corresponding .aspx.vb page will be created underneath it. In the .aspx page we write how the page will look by using HTML, Stylesheets, and certain ASP.Net elements (like textboxes, buttons and checkboxes etc). In the .vb page, we write what we want those ASP.Net elements to do, using Visual Basic, such as sending data to and from the database server. Now delete the Default.aspx page, so we can cover how to create a new web form, as this is what we will do every time we want to create a new form (a new report).

Creating a new web form

Go to Website -> Add New Item, you should be presented with the following screen...

Figure 5 - Adding a new web form (page)

Look at all that! We could go to town with this (notice the Reporting Services items in the middle there). Select Web Form, call it FailedJobs. Tick "Place code in separate file", so that the .vb page is also created. We're not actually going to be doing any coding in this particular report (so we won't enter anything into the FailedJobs.aspx.vb file today), as I want to demonstrate how simple it is to display data using the wizard. When we come to extend the FailedJobs report in a future article, we will programme using VB.Net. Now double click on FailedJobs.aspx that has been created in the Solution Explorer. Click on the Source tab in the IDE (number 6 in figure 3 above). At the top of the page, you should see code similar to this:

<%@ Page Language="VB" 
  AutoEventWireup="false" CodeFile="FailedJobs.aspx.vb" 
  Inherits="FailedJobs" %> 

I just want to point out now that the CodeFile="FailedJobs.aspx.vb" part must always be the name of the the corresponding .vb file where your code will be. And the Inherits="FailedJobs" part must always be the same as the name following "Partial Class" situated at the top of the FailedJobs.aspx.vb page. If your pages get muddled up one as you follow these articles and things aren't quite working, check that this is still the case.

Enter SQL Server

Now lets finish off this report. As mentioned above, normally your programming code would go in the aspx.vb file, including any lines that specify where your database is (your Data Source). Lots of ASP.Net controls can have Data Sources associated with them but when we use the Data Source wizard to setup your connection, instead of placing it in the aspx.vb file, it actually places it in the .aspx file. But that's fine. We just want to get a feel for how things work and the options we have available to us. Go to the Toolbox, find the group called Data and select GridView. Drag it to the middle of the window (1 in figure 3). The component should render itself like this (see figure 6).

Figure 6 - The ASP.Net Grid View control

Click on Choose Data Source and create a New Data Source. You should be presented with a "Data Source Configuration Wizard". In the first step, select the database icon and replace "SqlDataSource1", with FailedJobs_DataSource. This is the name we are going to give our connection string. Click OK. The next step is to "Choose Your Data Connection" i.e where is this server we want the string to point to? Click New Connection and you'll be presented with a dialog window that allows you to specify your server and database where the FailedJobs table is. This is your server with all the linked servers set up on it. Once you've done this, click next and the next screen will ask if you want to save this connection so that you can use it in the future without having to specify where the server is again. Save it as DBA_Admin_ConnectionString, click Next and configure the Select statement. We are now telling the wizard what we want displaying in the data grid control. Specify the Failed_Jobs table, select which columns you want returned (figure 7) and click Next. Test your Query and click Finish, the data grid should now look like this (Figure 8).

Figure 7 - Configure what you want the Data Grid to show

Figure 8 - The Data Grid configured (well almost).

Before we continue, I want to show you where that connection string was saved to. It's in the daddy of all files, the Web.config file. Double click on it, and you should be able to see your connection string.

Figure 9 - The connection string in the web.config file

Click on FailedJobs.aspx and click on the GridView so that the whole control is selected. Then click on the Source tab (6 in figure 3). The code representing the GridView should be highlighted. Familairise yourself with the different elements; you could write that without the wizard. Do the same with the FailedJobs_DataSource. Notice how it's calling the DBA_Admin_ConnectionString which is defined in the web.config file.

We're now going to configure the columns on the Data Grid. Go back to Design View and click on the tiny little tab hanging off the right hand side of the Data Grid (see figure 8). You should get figure 10. Enable Paging if you want. This means if you have too many failed jobs, it will display page numbers at the bottom (you got a bigger problem if you got that many failed jobs!). Enable Sorting allows you to sort via column name (such as by Server or Last_Run_Date). Enable Selection allows you to select and copy. Click on Edit Columns to get figure 11. Here you can configure how the columns will look in the Grid View. First of all make sure Auto-generate fields is not ticked, as this will just pull the data from the table without giving us the choice of formatting. On the bottom left (of figure 11), select the field name you want to configure, and on the right, it's properties are displayed. Play will all the available, but the most important two is the HeaderText and the DataField. In the DataField textbox we put the name of the corresponding table column. For example Job_Name. The HeaderText is what the display name will be in GridView. For example "The Job Name Field". If you leave this blank it will just reflect the name of the column in the table (including any alias you may have given the table column). You can completely remove or add fields all together and reorder them by using the two panes on the left of the Fields window (figure 11).

Figure 10- GridView Tasks

Figure 11 - Configuring the GridView fields

Viewing the report in the browser

When you are done configuring the fields of the GridView, hit F5 or click the little play icon (number 4 in figure 3). If you get a message about enabling debugging, click Yes. An internal web sever installed by Visual Studio will chug away and fire up your report in Internet Explorer. Viola!

Figure 12 - Our Failed Jobs report

Looks a bit bland, huh? Every element on your page, including the page itself, has configurable properties. And within those properties you can change the way anything looks i.e colours, borders, padding, margins, alignment etc. All you have to do is click on the item and play with the properties in the Properties pane in Visual Studio. Particularly look for the word "Style", as this will often lead to more styling options.

That's it. You've developed an ASP.Net web page displaying all the failed jobs on all your servers. You can run your FailedJobs retrieval job on your central server as often as you like, and check for failed jobs in your report. You can follow this same procedure to display any data (whether a table, a query or proc) using the same connection wizard. In the next article, (a shorter one hopefully), I'll show how easy it is to change the FailedJobs report into any other type of report that displays info on all your servers, simply by changing what's retrieved from the SQL Server system tables and views in the usp_GetFailedJob proc. We'll demonstrate this by creating an Un-Run jobs report. This will display jobs (such as backup jobs) that haven't been run in the last so many minutes/hours/days. With two reports in our pocket, we'll then setup Internet Information Services (IIS) so that the report pages can be viewed from any PC.

See you then,


Total article views: 7495 | Views in the last 30 days: -1
Related Articles

Printing Report by clicking an ASP.NET button.

Printing Report by clicking an ASP.NET button programatically.


Need One Click Button to Run All Reports

One Click Button to run all reports


Link click is not loading second report

If I click on the link in front end same report is loading instead of second report


Double click on row to navigate to sub-report.

Double click on row to navigate to sub-report.


Navigating to second report on click of header.

Navigating to second report on click of header.

visual studio