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
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 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
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
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
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
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,