Dot Net Guide

Beginning SQL Server 2005 Reporting Services Part 3: The Chart Control

2011-01-15T09:20:00.000-08:00

This is Part 3 of a four-part article series on SQL Server Reporting Services 2005 (SSRS). Part 1 provided a step-by-step guide to basic report creation and Part 2 took a tour of some of the core SSRS features and functions that you'll need to develop dynamic reports. Here, we turn our attention to the chart control that ships with SSRS.

Microsoft's chart control is, in fact, a scaled-down version of the Dundas Chart Control. Scaled down or not, as far as built-in controls go, I have to say that this one is pretty impressive and feature-rich. For many people, this feature alone constitutes a pretty good reason to adopt SSRS.

In this article we will set the chart control on a report and populate it. We will also cover the different charting display options and I'll demonstrate some rather cool formatting techniques.

Getting Started

Now that we have reached Part 3, I'm going to assume that you've installed all the software you need to follow through the examples, that you have created the ReportingDemo database in SQL Server and have also mastered the basics of creating basic reports, data sources and datasets. If any of these assumptions make you feel nervous, please work your way through Part 1 (at least) and then come back.

NOTE:

You can download the code file for this article from the Code Download link in the speech bubble to the right of the article title. The code file contains a sample Visual Studio project with all the reports from this article, along with a .bak file (or, alternatively, a SQL Script) for creating the ReportingDemo database.

Ed.Note:

The BAK file provided with part 1 of this series accidentally omitted the Purchase table. If you created theReportingDemo database from that BAK file (as I did) then you will need to run the provided ReportingDemo_Update script to create and populate the Purchase table.

Creating a basic chart

Start by creating a new report server project (called ChartProject, or similar) and add a shared data source that points to your ReportingDemo database. Add a new report called MyChart.rdl to the project. Set up a data set for the report using the following query, which fetches various bits of information pertaining to customer purchases:

SELECT
Purchase.PurchaseID,
Purchase.CustomerID,
Purchase.PurchaseDate,
Purchase.PurchaseType,
Purchase.PurchaseAmount, 
Customer.FirstName + ' ' + Customer.LastName AS CustomerName

FROM Purchase INNER JOIN
Customer
ON Purchase.CustomerID = Customer.CustomerID

On your new report, drag the open content area out so that we have room to work and then drag a chart control onto the content area. It should look like this:

From the Dataset fly-out window expand the dataset you created. Drag the PurchaseAmount field into the area above the chart labeled Drop data fields here. Drag the PurchaseDate field into the area below the chart labeled Drop category fields here.

We have now designed a report that will display the total value of customer purchases on a given date. Save the report and navigate to the Preview tab to run the report. It should look like this:

If your requirements are fairly basic, then charting can be this easy.

Chart types

Before we delve deeper into the chart control, it's worth noting that this control can produce a variety of the different chart types. Simply right-click on the chart (in the Layout tab) and use the Chart Type option to select a new chart type. For the time being, switch to a Smooth Line chart.

Add a series

The Data fields and the Category fields make up the horizontal and vertical settings on the chart. The chart control can also layer multiple sets of data onto the same chart. Each layer is called a series. In this case the purchase data we are looking at pertains to two different customers. Drag the CustomerName field into the area to the right of the grid called Drop series fields here. Switch to the Preview tab and view the report, which displays each customer's spending patterns over the various dates:

Setting report properties

Before we get started, change the chart type back to Simple Bar. Then, right-click on the chart again and select Properties. Here you can set all of the properties of the grid and you can also control nearly every aspect of how your data is displayed. Note that you can also manually set up Data, Categories and Seriesdata, which we did via drag and drop previously. We are going to look at each tab of the Properties dialog.

Please notice that as we look through the various options, you will see the 'fx' button nearly everywhere. This is used to set a property value with an expression rather than with a hard-coded value, as described in Part 2.

General tab

On this tab you can give your chart a Name. You can also set the chart type. When you click on Chart Area Style you can set the properties for the background of the chart. The Plot Area Style button allows you to modify basic properties for the chart itself. You can also add a Title to your chart. You can set the title properties such as Font and Color, using the button just to the right of the Title field. Another neat option is that you can apply a palette to the chart. Let's set the palette to Pastel. Click on Chart Area Style and select the Fill tab. Set the color to Thistle and the Gradient to TopBottom. The chart is easier on the eye already!

Data tab

The Data tab allows you to manually set up which data is displayed in the grid and how it is displayed. Just like on the chart, there are three areas for data: Values (called Data Fields in the drag and drop), Categories andSeries. You can add and remove items in the different areas. Click on the Edit buttons to set the specific property for each group you created.

Now we are going to take a closer look at the Values properties in the Data tab. Click the Edit button next to the Values field. The dialog will open to the Edit Chart Value tab. On this tab you can set the field from the dataset the values of which will be displayed on the chart, and you can also give the series a name. TheAppearance tab allows you to set point markers on your chart and control the style for the series. The Point Labels give you the ability to set a data field or other expression to label the points on your grid. The Action tab turns values into a hyperlink. You can jump to different places in the report or indeed to any valid URL. The URL can be hard-coded or set with an expression.

Let's open up the Point Labels tab so that we can display the values of the purchases being charted right on the bars in the chart. Set the Data Label to the expression =Fields!PurchaseAmount.Value and set the Format Code to C for currency. This will display the purchase amount directly on the grid bars in the $X.00 format. You can use the Label Style to manipulate the font and the color of the label.

X and Y axis tabs

These tabs allow you to set the values for the X and Y display. You can add a title which appears outside the chart area. You can also control the gridlines and the label formatting. Our X Axis is the purchase date. Let's get rid of the time in the purchase date by placing a 'd' into the Format code textbox. This tells the chart to format the date time value as mm/dd/yyyy. Let's check the Side Margins and Reversed checkboxes. This will add some margin space to the chart and place the values on the top. On the Y Axis tab check the Interlaced strips option to break up the chart's background.

Legend tab

This tab allows you to control the legend on the chart. The first option you have is whether or not to show the legend at all. The layout option allows you to display the legend in either a single column, single row, or in a table of columns and rows. The position you use for your legend will likely determine which layout is best. For example, if you move the legend to the bottom position, you might want to use the Row layout. Give it a go. The Legend Style button opens a dialog that allows you to control the style of the legend itself.

3D effect tab

This tab allows you to turn your flat chart into an exciting 3D experience.

I love this tab! I could play around with these settings for hours. For the most part the default settings for each chart type look great. The four values that control the 3-D aspects of the chart are Horizontal Rotation, Perspective, Wall Thickness and Vertical Rotation. While it is fun to work with these to see what you can do, I suggest that you start with the defaults and use the slider for each value to see if you make the values on the chart readable. They work particularly well when you are working with a multi-layered 3-D chart like the one in this example.

For this example set the following values:

Horizontal Rotation	65º
Perspective	0%
Wall Thickness	5%
Vertical Rotation	-5º

Set the Shading to Realistic. Check the Clustered option to display each series on a different plane. Check the Cylinder option to change the square bar on the chart into cylinders.

After you've had a chance to preview these settings, come back and play with the various settings. Believe me, you can make some incredible looking charts with the 3D settings.

Filter tab

This screen allows you to use the columns in your datasets, or expressions, to filter the data that is displayed on the grid. This could be applicable if you were displaying multiple charts on a single report. The important thing about the filter is that it allows you to show a subset of data from an existing dataset. For example, you could have multiple charts report off the same dataset, but each shows the purchases of a different purchase type.

Let’s display two charts in our report. One will report on Internet Sales and the other on In Store Sales. First copy the chart and paste the copy below the existing chart.

For the top chart, open the properties tab and on the General tab set the Title to 'Internet Sales'. Now go to theFilters tab and set the filter:

Expression	Operator	Value
=Fields!PurchaseType.Value	=	Internet

Click Ok and close the properties window.

For the bottom chart, open the properties tab and on the General tab set the Title to 'In Store Sales'. Now go to the Filters tab and set the filter:

Expression	Operator	Value
=Fields!PurchaseType.Value	=	In Store

The Report

Navigate to the Preview tab and let's take a look at the final reports. As you can see, we create a report with two 3-D Charts displaying subsets of data from a single dataset. While the data for this article was kept necessarily simple to allow us to focus on the chart control itself, imagine what you can accomplish in your own reporting system.

Wrap up

I hope this article has given you some insight into the chart control. Between the control's rich features and SSRS's ability to set nearly every property with an expression, you can now see that Microsoft has delivered an extremely powerful and user-friendly charting component in their reporting tool.

Beginning SQL Server 2005 Reporting Services Part 2

2011-01-15T09:18:00.000-08:00

We will investigate:

Use of expressions that allow you to dynamically control nearly every aspect of reporting from control properties to data binding
Common functions that allow run calculations and manipulate data within the report itself
Custom code, used to build your own library of functions within the report.

We'll also set up a Matrix control and create a sub-report. Lastly, we will add some flair to the report by adding dynamic drills and sorting.

Getting started

In order to follow my examples, you will need to have SQL Server 2005, SQL Server 2005 Reporting Services, and Visual Studio 2005 correctly installed and configured. If you need some help with this, please refer back to the links in Part 1 of this series. Next, download the code file for this article (see the Code Download link in the speech bubble to the right of the article title). The code file contains a sample Visual Studio project and a SQL Script for creating the ReportDemo database. If you've not done so already, go ahead and create the database using theReportingDemoDatabaseScript.sql script or, alternatively, by restoring the provided ReportingDemo.bak file.

Now start a new Business Intelligence project in Visual Studio 2005 (or using BIDS, which installs with SQL Server 2005). Select Project | Add Existing Item function to add to the project the shared datasource (ReportDb.rds) and the sample report, FirstReportMan.rdl. Next, open up the shared datasource and set the server, database, login and password to point to your copy of the database. We will start with the FirstReportMan.rdl report, so double-click on it in the solution explorer to load it into the designer.

Expressions

An expression is a short statement, written in code, which sets the value of a property. An expression can set the value of a property based on just about any data available in the report: parameters, report global values, fields in a dataset, and built-in functions.

SSRS 2005 has made some drastic improvements in how expressions are created. It now supplies an intuitive expression editor. If you scroll through the Properties window, you will notice that most of the properties of controls on the report can be hard-coded or can be set through expressions. (That's the option you see everywhere.) You will also see buttons labeled fx in the pop-up Properties dialog. These buttons will access the expression editor.

The expression feature makes SSRS 2005 into an incredibly versatile tool, because it allows most aspects of the report, from the data it shows to how it shows it, to behave dynamically. Rather than using a hard-coded value for a particular property, the SSRS resolves the expression to set the value for the property.

Looking at our report, let's say we want it to highlight all the customers from a particular State. We could use the expression:

=iif(Fields!State.Value = "CA", "Bold", "Normal")

If we apply this expression to the detail row in the table and run the report it will bold the text for each row where the value of the State field is California. That's great if the data from California is all that you are interested in. However, it would be ridiculous to have one report for each state, so let's make this expression even more dynamic by adding a report parameter that will determine which State is highlighted when the report is run.

First, let's add the new parameter to the report. Open the FirstReportMan.rdl report and navigate to the Layout tab. From the Report menu select Report Parameters. When the dialog window opens, click the Add button at the bottom of the screen. Call the new parameter 'HighlightState' and set the available values as follows:

California	CA
Florida	FL
Louisiana	LA
Illinois	IL
Texas	TX

Now click on OK.

Note:
You'll see that when setting up a list of pre-defined values for a parameter that there is a 'From Query' option. You can use this option by defining a dataset on the data tab of the report designer. For example, if we had a State table in the database we could define a dataset to retrieve the data for the parameter dropdown from that table. Once you choose the 'From Query' option, you select the dataset, the value field (this is the field from the dataset that contains the value passed to the report) and the label field (the field from the dataset that contains the value displayed to the user when they are prompted for parameters).

The next step is to set up the expression. Click on the table control on the report. When the table control is selected, an extra control tab appears at the top and left of the table. Click on the little 'row maker' icon, just to the left of the detail row. This allows you to set properties for the entire row. In the properties window select FontWeight and choose from the drop-down list to open the expression editor. Typing =iif( should give you Intellisense. You can use the expression builder to create the following line of code:

=iif(Fields!State.Value = Parameters!HighlightState.Value, "Bold", "Normal")

The iif is a flow control statement commonly used in SSRS expressions. Its format is:

iif (boolean statement, true result, false result)

If the boolean statement is true, then the true result is returned, and if not, the false result is returned. In this case, the boolean statement is asking: 'Does the state code equal the state code that was passed to the report? If it does, return the word 'bold'. If it does not, return the word 'normal''. The value returned is then used to set the property for that instance of the row.

Now switch to the Preview tab and select a State to highlight. When the report runs, all the rows with customers from the selected State should be displayed in bold.

Common functions

Let's navigate back to the expression editor. In the bottom half of the screen there are three columns. The first column lists different categories for building expressions. The last option is Common Functions. Expand theCommon Functions node. Click on the Date & Time category. In the next column, double-click on one of the functions. You will see that it is added to the expression box at the top of the dialog box. Next, simply put the cursor in the expression box after the function. If you then type in the opening left bracket you should instantly see Intellisense for the function you selected.

Let's use the Year function in the report to display only the year in which someone was born, instead of their full birth date. Right-click on the field that displays the date of birth and select Properties. On the General tab, find theValue field and click the fx button to bring up the expression editor. Change expression to read.

=Year(Fields!DateOfBirth.Value)

There we have it. It's a good idea to preview the report now.

Also of note here are the aggregate functions. These functions assist you in working with all of the data in a dataset. For example, let's say your report is an order form with a list of items and their prices. You can use theSum function on the Price field to create a total price on the report. In a different example, if you had a report that listed all the purchase dates and the total amount of each purchase, you could use the AVG function to get the average purchase price for that customer.

Global variables

Let's go back again to the expression editor. This time, click on Globals in the first column of the expression editor. In the second column, you will see the list of the global variables that are provided to the report.

In this example, we are going to use the global variables to create a page-numbering scheme for our report. First, add a footer to the FirstReportMan.rdl report. You enable this by going to the Layout tab and selecting the Report Footer option from the Report main menu. Now that you have a footer, drag four text boxes into the footer section. Set their font properties to 8 pt, bold. Set the text for each text box as follows:

Page

=Globals!PageNumber

=Globals!TotalPages

Switch to the Preview tab and run the report. You will now have a footer on each page that looks something like 'Page 1 of 5'.

Some other globals worth noting are:

ExecutionTime – this tells you how long it took to run the report
ReportServerUrl – this allows you to verify that you are getting your report from the correct source
UserID - this shows under which User ID the report was run

You can use these global variables to build some basic diagnostics into your reporting. You could also add these variables to the report footer and use a parameter to hide or show them. That way, even in production, your users can see a tidy, clean report, but you can see the same report with all the extra information on it.

Custom functions

There are about 100 common functions in SSRS 2005, and they can handle most of what you will need to do. Occasionally, however, you will still need to set an expression with more control than the common functions can manage. So, to make SSRS even more flexible, you can write custom VB.NET or C# functions and use them in expressions.

Now we are going to add a custom function to the FirstReportMan.rdl report.

Open the report and navigate to the Layout tab. From the Report menu select Report Properties and then jump to the Code tab. We are going to write a custom function that returns a different color, depending on the value that is passed to it. We will then use that function to set the background color for the status field in the report detail table.

Copy the following code into the code window:

Public Function GetColor(ByVal status as String) as String
IF status = "100" Then
Return "White"
End IF
IF status = "101" Then
Return "Yellow"
End IF
IF status = "102" Then
Return "Tomato"
End IF
End Function

Click OK and close the window.

Note:The full list of available colors is found in the Color dropdown list in the properties window.

Now that we have a function that returns color names we need to wire up that function to an expression. Click on the Customer Status cell and open the Properties window. Find the Background Color property and chooseExpression from the dropdown. Add the following line of code to create an expression:

=code.GetColor(Fields!CustomerStatus.Value)

When the report runs and this expression is resolved, SSRS will call your GetColor function and pass the value of the customer status for that instance of the row to the function. The function takes over and decides which color should be returned. SSRS then uses that value as the value for the background property for that cell.

Please note that custom functions must be called using =code..

Now navigate to the Preview tab and run the report..

Subreports

A subreport is a report that is embedded into another report. Subreports can take parameters and execute their own datasets. A key aspect to note is that a subreport in SSRS is actually just another report (unlike some reporting tools, where a subreport is a special construct). In fact, in SSRS you can execute a subreport on its own.

To add a subreport to a report, you simply drag a subreport control onto the report and tell it which report to display. If the subreport requires parameters, you have to tell the main report which value to pass to the subreport. It's actually very simple.

Let's add a new report to the project and call it MainReport.rdl. Create a new dataset using the shared datasource and the query:

SELECT CustomerID, FirstName, LastName FROM Customer

Switch to the Layout tab. Drag a table on the report detail area. Set up the first column to display the customer's first name (by dragging that column from the Datasets tab into the Detail row) and set up the second column to display the customer's last name. Label the third column 'Address'. Preview the report, just to be sure it works.

Create another report, and call this one MySubReport.rdl. This time, create a dataset that uses the shared data source, and use the following query text:

SELECT Address, City, State, ZipCode
FROM Customer
WHERE (CustomerID = @CustomerID)

In the Layout tab, use text boxes to create the address layout, as shown in figure 5. You can simply drag the text boxes onto the screen by clicking on the field in datasets tab and dragging it onto design surface. You will also note that when you do this, the expression used to set the value property for the textbox uses the First() function. This function will use the value from the first row returned by the dataset. All other rows returned are ignored.

Now preview the report and use '100' for the CustomerID parameter.

Let's jump back to the MainReport.rdl. To embed the subreport, drag a SubReport control into the detail cell for the column you labeled 'Address'. Right-click on the SubReport control and select Properties. In the Properties dialog choose MySubReport from the subreport dropdown.

Next, switch to the Parameters tab. This is where you connect your subreport to the main report. You do this by indicating which value from the main report is to be passed to the subreport to fulfill its parameter requirements.

In the Parameter Name column choose CustomerID and in the Parameter Value column choose=Fields!CustomerID.Value. This will wire up the subreport to whichever customer is being displayed in the row of the table.

Click OK to close the dialog, and then preview the main report.

The Matrix

It's this question that drives us, Neo. What is the Matrix? OK, I'll stop right here. You can insert your own slam to the second two movies. However, there is a really cool control in SSRS called the Matrix control. The Matrix is used to create pivot table style reports. We are going to set up a simple Matrix control to show us which customers, in which State, have which status.

First, start a new report called MatrixReport.rdl. Add our standard dataset where you use the shared data source, and then add the Select * from Customer query.

Navigate to the Layout tab and drag a new Matrix control onto the page. Where it says Rows, drag the State field from the dataset. Where it says Columns, drag the CustomerStatus field from the dataset. Drag first name and last name into the data area on the Matrix control. After a little formatting, it should resemble this:

When the report runs, it looks like this:

Adding drill downs

The drill down functionality in SSRS allows you to have areas of your report that can expand and collapse, much like a tree view.

First, start a new report called AdvancedTable.rdl. Add our standard dataset, where you use the shared data source and the Select * from Customer query.

Navigate to the Layout tab and drag a new table control onto the page. The first thing we need to do is to add a group to the table. Click the table to highlight it, then right-click on the little box with three horizontal lines in it that appear at the left side of the table at the beginning of detail row. Select Insert Group. When the Group Dialog appears use =Fields!State.Value as the expression, so that the report now groups on State. Now drag the State field into the first column on the Group row. Next, drag the First Name, Last Name and City fields into the detail row. Add some formatting, and preview the report.

We've now performed a grouping, but we still have to enable the drilldown. Click on the table to highlight it. Right-click on the nub that appears, just to the left of the detail row. Select Properties. When the Properties window opens, expand the visibility section. Set the Hidden property to True and set the ToggleItem property to State. TheHidden property determines the state of the row when the report is first run. If we set it to True then the data is collapsed and hidden. By setting the ToggleItem property to Statee, when the report is run a little + sign appears next to it when the report is run and it works like a tree view.

Switch to the Preview tab and run the report. Now only the States appear, but you can see the customer in each State by using the tree-style +/- controls.

Adding dynamic sorting

It is incredibly easy to add sorting to your table. Let's re-open the FirstReportMan.rdl report. Navigate to the Layouttab of the report designer. Click in the cell labeled First Name in the header of the table. Right-click and selectProperties. Switch to the Interactive Sort tab. Check the checkbox for 'Add an interactive sort action to this textbox'. Next, set the Sort expression to the value that is displayed in the column – in this case=Fields!FirstName.Value. Now repeat this process for the rest of the report.

Preview the report, and you should have interactive sorting on each column.

Wrap up

I hope this look into some of the more advanced features of SQL Server Reporting Services 2005 will help you make your reports more interactive and better adapted for your users. In Part 3, we will take an in-depth look at the chart control and its diverse set-up functionality.

Beginning SQL Server 2005 Reporting Services Part 1

2011-01-15T09:16:00.000-08:00

Building and deploying basic reports

If ever there was job for "real" developers to shuffle to their juniors, it is building reports. It's not real development anyway; it's more like glorified formatting. Besides, the esoteric tools that you use to build reports try to provide an abstraction layer, which often prevents you from having any real control over the report. I've heard all these arguments before. If this sounds like a conversation you've had by the water cooler, then I invite you to take a closer look at SQL Server 2005 Reporting Services (SSRS).

SSRS 2005 is the latest version of SQL Reporting Services, and ships with all versions of SQL Server 2005. SSRS allows you to quickly and easily create reports from multiple database sources. The finished reports can be presented directly from the reporting services website, or they can be displayed in your web- or Windows-based applications. Reports can be exported to multiple formats, including comma delimited text, XML, portable document format (pdf) and Microsoft Excel.

This article is the first of four parts, which will provide in-depth coverage of SQL Server 2005 Reporting Services:

Part 1 covers the basics of the SSRS. It demonstrates how to build quick, simple reports and will familiarize you with the basic Report Designer environment
Part 2 will cover use of custom functions, aggregate functions, sub-reporting, the matrix control, drill-downs, and sorting.
Part 3 will take an in-depth look at the charting control
Part 4 digs into the actual Report Definition Language (RDL) and the new Report Builder tool.

Installing and configuring Reporting Services

After much debate, we decided that full installation and configuration details were outside the scope of an article focused on how to build basic reports using SSRS, especially as there are already several online resources that treat this area in detail. See, for example:

http://msdn2.microsoft.com/en-us/library/ms143736.aspx

and

http://www.awprofessional.com/articles/article.asp?p=357694&seqNum=1&rl=1

Well worth reviewing are the details in the latter on the need to install SSL on any machine hosting SSRS.

---Editor's installation notes---In order to test the reports in this article, I went through a single-machine installation on Windows XP (meaning that SQL Server 2005, Reporting Services, and much more, are all installed on my poor laptop). Since I didn't install SSRS when I installed SQL Server 2005 (I didn't have IIS installed at the time), I had to first install IIS 5.1, and then Reporting Services (from the SS2005 disks). I then jumped immediately into Reporting Services Configuration Manager tool (All Programs ¬ SQL Server 2005 ¬ Configuration tools). The left panel acts as a sort of checklist of items to configure:

All went well until the Database Setup. Because I had not installed SSRS at the same time that I installed SQL Server, the ReportServer and ReportServerTempDB were not created by default. These databases hold all the report definitions and reporting server configuration information. Connecting to SQL Server using Service Credentials, the databases appeared to be created, but I received an error suggesting that I didn't have permissions to perform the requested task, and a red cross remained next to the Database Setup icon. This had me stumped for a while – eventually I appeared to solve it by hopping to the Server Status section, stopping the Report Server, opening SSMS and deleting both the ReportServer and ReportServerTempDB databases, and then restarting the Report Server and trying again! Even though I swear this didn't work the first time I tried it, it seemed to do the trick the second time (while I was on the phone moaning to Steve that I couldn't get it to work!). However, I'm still not clear why I had the problem in the first place – or why it suddenly went away.

This wasn't quite the end of my problems. Installing SSRS creates two new virtual directories under the default website on IIS. By default, they are named Reports and ReportServer. The ReportServer site hosts a web service for running and managing reports. The Reports site allows you upload reports from a browser and run reports over the web. When I tried to navigate to http://localhost/ReportServer I received the following error: "Failed to access IIS metabase". Fortunately, the information on the error page is quite useful (for a change) and this, coupled with a quick Google search, led me to grant access to the metabase to the ASPNET user account by executing the following from the directory housing the aspnet_regiis executable (WINDOWS\Microsoft.NET\Framework\v2.0.50727, in my case):

spnet_regiis -ga ASPNET

This did the trick, and I was up and running. I received some raised eyebrows here when I proposed to install SSRS. It made my long-suffering sys admin team particularly nervous. The 2000 version of SSRS seems to have a very bad reputation among the Red Gate team, both as a performance nightmare, and in one case for "completely annihilating" a developer's machine. However, the above slight quirks aside, the SSRS 2005 experience seems pretty smooth and I've had no real performance, or other, issues. So far.
-----End of Editor's note----

Once you've completed the installation and configuration process, the first thing to do is download the code file for this article (see the Code Download link in the speech bubble to the right of the article title). The code file contains a sample Visual Studio project and a SQL Script for creating the ReportDemo database. You will need this database if you want to follow the examples in this series of articles so go ahead and create the database using the ReportingDemoDatabaseScript.sql script or, alternatively, by restoring the provided ReportingDemo.bak file.

If you are using SQL Server Authentication on your SQL Server you will also need to set up a login with permissions to the ReportDemo database (for the examples, I set up a login called DemoUser as a DBO on my ReportDemo database).

Using the Report Wizard

There are two ways to create SSRS Reports. You can build the report manually, or you can use the Report Wizard to give yourself a head start. For this first report, we are going to take advantage of the wizard. Start a new Business Intelligence project in Visual Studio 2005. Then, from the menu, select Project > Add New Item. Select the Report Wizard option and call the file "FirstReportWZ.rdl". Click Add. Click Next on the Welcome screen and this will bring you to the Select the Data Source screen:

Select the New data source radio button and give the data source a name. Select Microsoft SQL Server as the type.

Note:Use of the Shared data source option is actually a better option in most cases but, for the sake of this example, we will just create a new data source. We're going to set up a shared data source later in this article.

Click the Edit button to bring up the Connection Properties dialog:

Enter or select a server name. The default is to log on using Windows Authentication. If you are using SQL Server Authentication, choose that setting, and enter the username and password. Finally, select a database, in this caseReportingDemo. Make sure you test the connection before you click OK.

Next up is the Design the Query screen. You can use the Query Builder by clicking the button at the top left, but in this case simply enter "Select * From Customer" into the Query string text box and click Next. On the Select the Report Type simply select the type you prefer (tabular or matrix) and hit Next.

This brings up the Design the Table screen:

Select State in the Available fields box and click the Group button. This will group the data in the result set by State. Select Next. On the Choose the Table Layout page, select the Stepped option and check the Enable Drilldowncheckbox. On the next screen, choose a style for your report (I chose Ocean) and click Next.

This brings up the final screen, Completing the Wizard:

It shows a summary of your report options, and gives you an opportunity to rename the report and to preview it. Click Finish to end the wizard.

You should be shown the report in the standard Report Designer:

There are three tabs: Data, Layout and Preview. You should be on the Layout tab. Click on the Preview tab to view the report. Since this report doesn't take any arguments, it will run as soon as you click on the tab.

Use the +/- symbols next to the State abbreviations to expand and collapse the grouping of the report.

Manual report creation

The previous section, while perhaps overly simplistic, does show you how to get a report up and running with zero code and zero property setting. Obviously, such reports will not meet most development requirements, but do not discount this option as a starting point for more complicated reports. Once you've created a report with the Report Wizard, you are free to make any modifications to it.

In this section, we are going to create a more realistic report from scratch, including how to create and use a shared data source object, stored procedures, and how to format your report, set report properties and use report parameters.

Creating a shared data source

Before we start the report, we are going to build a shared data source i.e. a data source that is common to, and can be used by, all of the reports on a reporting server. While each report can contain its own connection information, it is good practice to use shared data sources, as it will save you a lot time and headaches. This way, you only have to set the connection information once. For example, let's say your company has Development, Test and Production environments. If you put the connection information into each report, you will have to change it each time when you publish to Development, Test and Production. If you use a shared data source, you still have to set up a data source object for each of three environments, but you can simply publish the report to each environment, and they will automatically use the connection information associated with that environment.

From the menu select Project > Add New Item > Select Data Source. On the General tab, call the data source "ReportsDB". Leave the Type as Microsoft SQL Server. Click the Edit button on the right, and enter the connection information for Reporting Demo, as before. Click OK on the Shared Data Source screen and the data source is done.

Add a new report

From the menu select Project > Add New Item. Select Report and name it "FirstReportMan.rdl". A new report will be added to the project, and the Report Designer will open at the Data tab. At this point, let's take a closer look at the Report Designer tool. At the top of the Report Designer window are three tabs: Data, Layout and Preview. TheData tab is used to build data sources for your report. The Layout tab is the physical report designer where you set up the header, the footer and the data presentation of the report. The Preview tab allows you to actually run the report from Visual Studio 2005, without having to publish it to a report server first. If your report takes parameters, the Preview tab will ask you to fill them out before it runs the report.

The first thing we need to do is get data into our report. At the top of the Data tab choose from the dropdown list:

The Dataset dialog opens:

Name the dataset "ReportData", and select the ReportsDB data source from the dropdown. SelectStoredProcedure as the command type, and enter "spr_CustomerSelectAll" in the query string box. Click OK to close the dialog.

Note:

spr_CustomerSelectAll is a very simple, parameter-less stored procedure that selects all of the columns from the Customer table. The source code is included in the ReportingDemoDatabaseScript.sql script with the code download.

Test the dataset by clicking on the big red exclamation point at the top of the report designer. This executes the query and displays the results. You can repeat these steps if you wish to add multiple datasets to your report.

Setting up the report display

Next, switch to the Layout tab. This is where you actually build the report display. Start by dragging a table from the Toolbox, onto the Body section of the report. By default, the table shows a header row at the top, a detail row in the middle and a footer row at the bottom. You can add additional columns to the table by right-clicking on one of the columns and selecting one of the two Insert Column options.

Click on the Datasets tab under Toolbox on the left-hand side of the Visual Studio environment. You should see theReportData dataset. Expand it, select FirstName and drag it to the first cell of the body row of the table. This will display the First Name field in the first column. Now put the LastName in the second column, and theCustomerStatus in the third:

Note that, when you drag the column FirstName into the body row of the table, SSRS make a guess as to what to call the row. It calls it "First Name". You can click on the Preview tab to view the report.

Adding formatting

OK, so we have a basic, very plain, report. Let's add some formatting to make it look good. Let's add theDateOfBirth column to the table. You can expand and shrink the size of the columns by highlighting and dragging:

If you wish to get rid of the footer row of the table and then click on any field in the table. You will see a grey box surround the table. Right-click on the icon at the beginning of the body row and then click the Table Footer option to deselect it.

The table header is next. To format all the cells in the header in the same way, click and drag over all the cells to select them. Open the Properties window and set background color, font, and so on.

Note, however, that if you wish to change the justification of the text or the size, then you do so using the main menu at the top, not via the properties window. Again, you can click on the Preview tab to view the report.

Add a report header

The table already has a header, but the report can also have a header and footer. Report headers and footers appear on every page of the report. Put the Report Designer in Layout mode and select Report > Page Headerfrom the menu. A new band labeled Page Header appears on the report above the body. Drag a text box onto the header and click directly on it. Type "My First Report", not in the Properties window, but right on the control.

To adjust the text style and size of your heading, highlight the text box and use the option from the top menu.

Formatting

Each field can have formatting. Let's deal with that ugly Date of Birth field. The time information is superfluous, so let's format the date properly. Right-click on the cell that contains the date of birth, and choose Properties. Next, select the Format tab.

Click on the second button (labeled "…") to the right of the Format code field. When the dialog opens, use the standard options. Select Date in the list on the left, then choose the sample date format you want on the right. ClickOK to close the dialog.

Click OK to close the Properties window and then click on the Preview tab to view the report.

Setting report properties

To access the report properties, you need to be on the Layout tab of the Report Designer. When you click on this tab, a new menu item, Report, activates. This menu gives you options for turning on and off the report header and footer sections (as we have seen), adding embedded images, setting report properties, and setting report parameters.

Setting up the report print settings is not intuitive, compared to other products. In the report Layout tab you see your report with a ruler across the top. That ruler is set up in inches. A standard sheet of paper is 8.5 x 11 inches. The default setting in SSRS is to have a 1-inch margin all the way around the content area. So, at 8.5 inches wide with a 1-inch margin on the left and another on the right, you have 6.5 inches for content. Any more, and the content beyond the 6.5 inches will spill over onto a second sheet of paper when printed. It will not automatically shift to Landscape.

To get Landscape reports, you have to make the proper report settings. Navigate to the Layout tab of the Report Designer. From the Report menu select Report Properties. Switch to the Layout tab of the dialog box. To switch to Landscape printing, you need to change Page width to 11in and the Page height to 8.5in.

On the same Layout tab, you can also get up your report to display the data in multiple columns, and you can control the margins. By default, all the margins are set at 1 inch. Remember, if your report width, plus the right and left margins, is greater than the width of the printer, you will get spill-over when you print out your report.

Using parameters

There are two ways to add parameters to your reports. The first is to use parameters in the queries in your data sources. The second is to set up parameters through the Report Parameters dialog box. We will do both.

Query parameters

First, we will set up parameters using a data source query. Navigate to the Data tab, and click the "…" button right next the Dataset dropdown box. The Dataset dialog box opens and allows you to edit the query for the dataset. Change the Query string from "spr_CustomerSelectAll" to "spr_CustomerSelectByState".

Note:spr_CustomerSelectByState accepts a parameter called StateCD that allows us to filter the data from the Customer table by State. Again, the source code is included in the ReportingDemoDatabaseScript.sql script with the code download.

Click OK and then run the query by clicking the red exclamation point (!) on the Data tab toolbar. You will be prompted to enter a parameter value for StateCD (the State code). Enter "CA" and click OK. The query should return all the customers that live in California.

Switch to the Preview tab. Instead of the report just running, there should be a place for you to enter a value for the "StateCD" and run the report. Enter "CA" and click the View Report button. You should see your report filtered by state.

To edit the parameters, or to add new ones, switch to the Layout tab of the Report Designer and open the Report Parameters dialog from the Report menu. First, we are going to make the "State code" prompt a little more friendly. Select StateCD in the Parameters list box then, in the Parameters Properties box, change the prompt to read "State". In the Available values section enter "California" and "CA" on the first line. Enter "Louisiana" and "LA" on the second line.

This will make the prompt for "State value" into a dropdown. You can also build additional datasets into your report, and use the values in those datasets as criteria for dropdowns.

Report parameters

Now let's add a parameter that is not used in a query. Click the Add button in the Report Parameters dialog from the Report menu. Give the new parameter the name "ReportTitle". Its Data type should be String. Set the Prompt to "Report Title". Click OK and close the dialog box.

Drag a new text box onto the report header area. Right-click on it and choose the Expression option. In the Edit Expression dialog select Parameters. Double-click on the ReportTitle parameter. This text box will now display the value you pass to the Report Title parameter.

Switch to the Preview tab and try it out.

Publishing your reports

Up to now, you have been running your reports in the Visual Studio 2005 environment but, to make them useful, you must publish them to a report server. The easiest way to do this is to have Visual Studio publish your reports.

Start by right-clicking on Project in the Project Explorer and choose Properties.

Set TargetServerURL to the URL of your report server. Use the TargetReportFolder property to set up a folder for your reports. If the folder does not exist, the Publisher will create it for you. The other key thing to pay attention to here is the OverwriteDataSources property. When this property is set to "True", it will automatically copy over all your data source objects when you publish. When set to "False", it will copy any new data source objects, but it will not overwrite existing ones. This is important when dealing with development, test and production servers. You can publish a set of data source objects to each server, pointed at the correct database, and never have to worry about what database your reports are hitting in each environment.

From the Build menu select Deploy Solution. This will publish the project to the selected folder on your report server. You can also deploy individual reports by right-clicking on the file in the Solution explorer and selectingDeploy.

Once your report has been published, you can access and run it on your server through the browser athttp:///. From the Home page, you should be able to find the folder you published to, with the reports in it. Select a report to run. At the top of the page you can enter any values for report parameters, and then run the report. From here, you can also print or export the report.

Using jQuery UI Datepicker in a Web Page

2010-12-05T06:27:00.000-08:00

The jQuery UI library includes a rich set of widgets and effects that you can use in your HTML applications. One such widget is the jQuery Datepicker widget. The jQueryUI Datepicker is a highly configurable widget to quickly add datepickers to your web pages.

Let us see how to use the jQueryUI Datepicker widget in your web pages using Expression Web. This article assumes you are familiar with jQuery. If not, take a quick jQuery course here.

Launch Expression Web and create a new HTML Page (File > New or click on the New Document icon on Standard toolbar and choose HTML). Add the following links in the element of the page.

We have added the following three files above:

jQuery UI theme (jquery-ui.css) - A link to a CSS file to import the Smoothness theme. The jQuery UI supports different themes and the smoothness theme is just one amongst them.
The jQuery library (jquery.min.js) — The jQuery library which handles DOM manipulations, CSS, AJAX, Events and Animations on your page. The jQuery UI library depends on the jQuery library.
The jQuery UI library (jquery-ui.min.js) — The jQuery UI library which contains all the jQuery UI effects and widgets such as the Datepicker widget.

Note: The CSS and jQuery script links shown above are taken from the Microsoft Ajax Content Delivery Network (CDN). These links from CDN enables you to easily add popular JavaScript libraries such as jQuery to your Web applications, without having to download the jQuery scripts and manually referencing them in your pages.

Now add a textbox control (call it txtDate) to the page and write the following code in the element

Do you know that if you view the webpage in a browser and focus the cursor (click or tab) inside the textbox, you will see a fully functional Datepicker control? See below;

But there’s a lot more to the Datepicker control. The Datepicker control is highly configurable. So let us say if we want to display multiple months at the same time, use the numberOfMonths option as shown below, which displays two months instead of one:

Similarly, to show the year as a dropdown and limit the years from 2008 to 2012 only, use the changeYear and yearRange option as shown below:

I hope you now see how highly configurable the Datepicker widget it. You can see a bunch of other Datepicker options over here. So play around with them and let me know your experience!

You can download the HTML source code over here.

LINQ To XML Tutorials with Examples

2010-08-29T08:11:00.000-07:00

LINQ To XML Tutorials with Examples

For this article, we will be using a sample file called ‘Employees.xml’ for all our samples which is available with the source code. So make sure you keep it handy with you while are practicing these examples. The mark up for Employees.xml is as follows:

xml version="1.0" encoding="utf-8" ?>

<EmpId>1EmpId>

<Name>SamName>

<Sex>MaleSex>

<Phone Type="Home">423-555-0124Phone>

<Phone Type="Work">424-555-0545Phone>

<Street>7A Cox StreetStreet>

<City>AcampoCity>

<State>CAState>

<Country>USACountry>

Address>

Employee>

<EmpId>2EmpId>

<Name>LucyName>

<Sex>FemaleSex>

<Phone Type="Home">143-555-0763Phone>

<Phone Type="Work">434-555-0567Phone>

<Street>Jess BayStreet>

<City>AltaCity>

<State>CAState>

<Country>USACountry>

Address>

Employee>

<EmpId>3EmpId>

<Name>KateName>

<Sex>FemaleSex>

<Phone Type="Home">166-555-0231Phone>

<Phone Type="Work">233-555-0442Phone>

<Street>23 Boxen StreetStreet>

<City>MilfordCity>

<State>CAState>

<Country>USACountry>

Address>

Employee>

<EmpId>4EmpId>

<Name>ChrisName>

<Sex>MaleSex>

<Phone Type="Home">564-555-0122Phone>

<Phone Type="Work">442-555-0154Phone>

<Street>124 KutbayStreet>

<City>MontaraCity>

<State>CAState>

<Country>USACountry>

Address>

Employee>

Employees>

The application is a console application targeting .NET 3.5 framework, although you can use the latest .NET 4.0 framework too. I have also used ‘query expressions’, instead of Lambda expression in these samples. It is just a matter of preference and you are free to use any of these.

This tutorial has been divided into 2 sections:

Section 1: Read XML and Traverse the Document using LINQ To XML

Section 2: Manipulate XML content and Persist the changes using LINQ To XML

The following namespaces are needed while testing the samples: System; System.Collections.Generic; System.Linq; System.Text; System.Xml; System.Xml.Linq;

Go grab a hot cup of coffee, put on your developer cap and let us get started:

Section 1: Read XML and Traverse the XML Document using LINQ To XML

1. How Do I Read XML using LINQ to XML

There are two ways to do so: Using the XElement class or the XDocument class. Both the classes contain the ‘Load()’ method which accepts a file, a URL or XMLReader and allows XML to be loaded. The primary difference between both the classes is that an XDocument can contain XML declaration, XML Document Type (DTD) and processing instructions. Moreover an XDocument contains one root XElement.

Using XElement

XElement xelement = XElement.Load("..\\..\\Employees.xml");

IEnumerable<XElement> employees = xelement.Elements();

// Read the entire XML

foreach (var employee in employees)

{

Console.WriteLine(employee);

}

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim employees As IEnumerable(Of XElement) = xelement.Elements()

' Read the entire XML

For Each employee In employees

Console.WriteLine(employee)

Next employee

Output:

Using XDocument

XDocument xdocument = XDocument.Load("..\\..\\Employees.xml");

IEnumerable<XElement> employees = xdocument.Elements();

foreach (var employee in employees)

{

Console.WriteLine(employee);

}

VB.NET (Converted Code)

Dim xdocument As XDocument = XDocument.Load("..\..\Employees.xml")

Dim employees As IEnumerable(Of XElement) = xdocument.Elements()

For Each employee In employees

Console.WriteLine(employee)

Next employee

Output:

Note 1: As you can observe, XDocument contains a single root element (Employees).

Note 2: In order to generate an output similar to the one using XElement, use “xdocument.Root.Elements()” instead of “xdocument.Elements()”

Note 3: VB.NET users can use a new feature called XML Literal.

2. How Do I Access a Single Element using LINQ to XML

Let us see how to access the name of all the Employees and list them over here

XElement xelement = XElement.Load("..\\..\\Employees.xml");

IEnumerable<XElement> employees = xelement.Elements();

Console.WriteLine("List of all Employee Names :");

foreach (var employee in employees)

{

Console.WriteLine(employee.Element("Name").Value);

}

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim employees As IEnumerable(Of XElement) = xelement.Elements()

Console.WriteLine("List of all Employee Names :")

For Each employee In employees

Console.WriteLine(employee.Element("Name").Value)

Next employee

Output:

3. How Do I Access Multiple Elements using LINQ to XML

Let us see how to access the name of all Employees and also list the ID along with it

XElement xelement = XElement.Load("..\\..\\Employees.xml");

IEnumerable<XElement> employees = xelement.Elements();

Console.WriteLine("List of all Employee Names along with their ID:");

foreach (var employee in employees)

{

Console.WriteLine("{0} has Employee ID {1}",

employee.Element("Name").Value,

employee.Element("EmpId").Value);

}

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim employees As IEnumerable(Of XElement) = xelement.Elements()

Console.WriteLine("List of all Employee Names along with their ID:")

For Each employee In employees

Console.WriteLine("{0} has Employee ID {1}", employee.Element("Name").Value, employee.Element("EmpId").Value)

Next employee

Output:

4. How Do I Access all Elements having a Specific Attribute using LINQ to XML

Let us see how to access details of all Female Employees

XElement xelement = XElement.Load("..\\..\\Employees.xml");

var name = from nm in xelement.Elements("Employee")

where (string)nm.Element("Sex") == "Female"

select nm;

Console.WriteLine("Details of Female Employees:");

foreach (XElement xEle in name)

Console.WriteLine(xEle);

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim name = _

From nm In xelement.Elements("Employee") _

Where CStr(nm.Element("Sex")) = "Female" _

Select nm

Console.WriteLine("Details of Female Employees:")

For Each xEle As XElement In name

Console.WriteLine(xEle)

Next xEle

Output:

5. How Do I access Specific Element having a Specific Attribute using LINQ to XML

Let us see how to list all the Home Phone Nos.

XElement xelement = XElement.Load("..\\..\\Employees.xml");

var homePhone = from phoneno in xelement.Elements("Employee")

where (string)phoneno.Element("Phone").Attribute("Type") == "Home"

select phoneno;

Console.WriteLine("List HomePhone Nos.");

foreach (XElement xEle in homePhone)

{

Console.WriteLine(xEle.Element("Phone").Value);

}

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim homePhone = _

From phoneno In xelement.Elements("Employee") _

Where CStr(phoneno.Element("Phone").Attribute("Type")) = "Home" _

Select phoneno

Console.WriteLine("List HomePhone Nos.")

For Each xEle As XElement In homePhone

Console.WriteLine(xEle.Element("Phone").Value)

Next xEle

Output:

6. How Do I Find an Element within another Element using LINQ to XML

Let us see how to find the details of Employees living in 'Alta' City

XElement xelement = XElement.Load("..\\..\\Employees.xml");

var addresses = from address in xelement.Elements("Employee")

where (string)address.Element("Address").Element("City") == "Alta"

select address;

Console.WriteLine("Details of Employees living in Alta City");

foreach (XElement xEle in addresses)

Console.WriteLine(xEle);

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim addresses = _

From address In xelement.Elements("Employee") _

Where CStr(address.Element("Address").Element("City")) = "Alta" _

Select address

Console.WriteLine("Details of Employees living in Alta City")

For Each xEle As XElement In addresses

Console.WriteLine(xEle)

Next xEle

Output:

7. How Do I Find Nested Elements (using Descendants Axis) using LINQ to XML

Let us see how to list all the zip codes in the XML file

XElement xelement = XElement.Load("..\\..\\Employees.xml");

Console.WriteLine("List of all Zip Codes");

foreach (XElement xEle in xelement.Descendants("Zip"))

{

Console.WriteLine((string)xEle);

}

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Console.WriteLine("List of all Zip Codes")

For Each xEle As XElement In xelement.Descendants("Zip")

Console.WriteLine(CStr(xEle))

Next xEle

Output:

8. How do I apply Sorting on Elements using LINQ to XML

Let us see how to List and Sort all Zip Codes in ascending order

XElement xelement = XElement.Load("..\\..\\Employees.xml");

IEnumerable<string> codes = from code in xelement.Elements("Employee")

let zip = (string)code.Element("Address").Element("Zip")

orderby zip

select zip;

Console.WriteLine("List and Sort all Zip Codes");

foreach (string zp in codes)

Console.WriteLine(zp);

VB.NET (Converted Code)

Dim xelement As XElement = XElement.Load("..\..\Employees.xml")

Dim codes As IEnumerable(Of String) = _

From code In xelement.Elements("Employee") _

Let zip = CStr(code.Element("Address").Element("Zip")) _

Order By zip _

Select zip

Console.WriteLine("List and Sort all Zip Codes")

For Each zp As String In codes

Console.WriteLine(zp)

Next zp

Output:

Section 2: Manipulate XML content and Persist the changes using LINQ To XML

9. Create an XML Document with Xml Declaration/Namespace/Comments using LINQ to XML

When you need to create an XML document containing XML declaration, XML Document Type (DTD) and processing instructions, Comments, Namespaces, you should go in for the XDocument class.

XNamespace empNM = "urn:lst-emp:emp";

XDocument xDoc = new XDocument(

new XDeclaration("1.0", "UTF-16", null),

new XElement(empNM + "Employees",

new XElement("Employee",

new XComment("Only 3 elements for demo purposes"),

new XElement("EmpId", "5"),

new XElement("Name", "Kimmy"),

new XElement("Sex", "Female")

)));

StringWriter sw = new StringWriter();

xDoc.Save(sw);

Console.WriteLine(sw);

VB.NET (Converted Code)

Dim empNM As XNamespace = "urn:lst-emp:emp"

Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-16", Nothing), _

New XElement(empNM + "Employees", _

New XElement("Employee", _

New XComment("Only 3 elements for demo purposes"), _

New XElement("EmpId", "5"), _

New XElement("Name", "Kimmy"), _

New XElement("Sex", "Female"))))

Dim sw As New StringWriter()

xDoc.Save(sw)

Console.WriteLine(sw)

10. Save the XML Document to a XMLWriter or to the disk using LINQ to XML

Use the following code to save the XML to a XMLWriter or to your physical disk

XNamespace empNM = "urn:lst-emp:emp";

XDocument xDoc = new XDocument(

new XDeclaration("1.0", "UTF-16", null),

new XElement(empNM + "Employees",

new XElement("Employee",

new XComment("Only 3 elements for demo purposes"),

new XElement("EmpId", "5"),

new XElement("Name", "Kimmy"),

new XElement("Sex", "Female")

)));

StringWriter sw = new StringWriter();

XmlWriter xWrite = XmlWriter.Create(sw);

xDoc.Save(xWrite);

xWrite.Close();

// Save to Disk

xDoc.Save("C:\\Something.xml");

Console.WriteLine("Saved");

VB.NET (Converted Code)

Dim empNM As XNamespace = "urn:lst-emp:emp"

Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-16", Nothing),_

New XElement(empNM + "Employees", _

New XElement("Employee", _

New XComment("Only 3 elements for demo purposes"), _

New XElement("EmpId", "5"), _

New XElement("Name", "Kimmy"), _

New XElement("Sex", "Female"))))

Dim sw As New StringWriter()

Dim xWrite As XmlWriter = XmlWriter.Create(sw)

xDoc.Save(xWrite)

xWrite.Close()

' Save to Disk

xDoc.Save("C:\Something.xml")

Console.WriteLine("Saved")

11. Load an XML Document using XML Reader using LINQ to XML

Use the following code to load the XML Document into an XML Reader

XmlReader xRead = XmlReader.Create(@"..\\..\\Employees.xml");

XElement xEle = XElement.Load(xRead);

Console.WriteLine(xEle);

xRead.Close();

VB.NET (Converted Code)

Dim xRead As XmlReader = XmlReader.Create("..\\..\\Employees.xml")

Dim xEle As XElement = XElement.Load(xRead)

Console.WriteLine(xEle)

xRead.Close()

12. Find Element at a Specific Position using LINQ to XML

Find the 2^nd Employee Element

// Using XElement

Console.WriteLine("Using XElement");

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emp1 = xEle.Descendants("Employee").ElementAt(1);

Console.WriteLine(emp);

Console.WriteLine("------------");

//// Using XDocument

Console.WriteLine("Using XDocument");

XDocument xDoc = XDocument.Load("..\\..\\Employees.xml");

var emp1 = xDoc.Descendants("Employee").ElementAt(1);

Console.WriteLine(emp);

VB.NET (Converted Code)

' Using XElement

Console.WriteLine("Using XElement")

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emp1 = xEle.Descendants("Employee").ElementAt(1)

Console.WriteLine(emp)

Console.WriteLine("------------")

'// Using XDocument

Console.WriteLine("Using XDocument")

Dim xDoc As XDocument = XDocument.Load("..\..\Employees.xml")

Dim emp1 = xDoc.Descendants("Employee").ElementAt(1)

Console.WriteLine(emp)

13. List the First 2 Elements using LINQ to XML

List the details of the first 2 Employees

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emps = xEle.Descendants("Employee").Take(2);

foreach (var emp in emps)

Console.WriteLine(emp);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emps = xEle.Descendants("Employee").Take(2)

For Each emp In emps

Console.WriteLine(emp)

Next emp

14. List the 2nd and 3rd Element using LINQ to XML

List the 2nd and 3rd Employees

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emps = xEle.Descendants("Employee").Skip(1).Take(2);

foreach (var emp in emps)

Console.WriteLine(emp);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emps = xEle.Descendants("Employee").Skip(1).Take(2)

For Each emp In emps

Console.WriteLine(emp)

Next emp

15. List the Last 2 Elements using LINQ To XML

We have been posting the entire elements as output in our previous examples. Let us say that you want to display only the Employee Name, use this query:

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emps = xEle.Descendants("Employee").Reverse().Take(2);

foreach (var emp in emps)

Console.WriteLine(emp.Element("EmpId") + "" + emp.Element("Name"));

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emps = xEle.Descendants("Employee").Reverse().Take(2)

For Each emp In emps

Console.WriteLine(emp.Element("EmpId") + emp.Element("Name"))

Next emp

To display only the values without the XML tags, use the ‘Value’ property

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emps = xEle.Descendants("Employee").Reverse().Take(2);

foreach (var emp in emps)

Console.WriteLine(emp.Element("EmpId").Value + ". " + emp.Element("Name").Value);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emps = xEle.Descendants("Employee").Reverse().Take(2)

For Each emp In emps

Console.WriteLine(emp.Element("EmpId").Value & ". " & emp.Element("Name").Value)

Next emp

If you notice, the results are not ordered i.e. the Employee 4 is printed before 3. To order the results, just add call Reverse() again while filtering as shown below:

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emps = xEle.Descendants("Employee").Reverse().Take(2).Reverse();

foreach (var emp in emps)

Console.WriteLine(emp.Element("EmpId").Value + ". " + emp.Element("Name").Value);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emps = xEle.Descendants("Employee").Reverse().Take(2).Reverse()

For Each emp In emps

Console.WriteLine(emp.Element("EmpId").Value & ". " & emp.Element("Name").Value)

Next emp

16. Find the Element Count based on a condition using LINQ to XML

Count the number of Employees living in the state CA

XElement xelement = XElement.Load("..\\..\\Employees.xml");

var stCnt = from address in xelement.Elements("Employee")

where (string)address.Element("Address").Element("State") == "CA"

select address;

Console.WriteLine("No of Employees living in CA State are {0}", stCnt.Count());

VB.NET (Converted Code)

XElement xelement = XElement.Load("..\\..\\Employees.xml");

var stCnt = from address in xelement.Elements("Employee")

where (string)address.Element("Address").Element("State") == "CA"

select address;

Console.WriteLine("No of Employees living in CA State are {0}", stCnt.Count());

17. Add a new Element at runtime using LINQ to XML

You can add a new Element to an XML document at runtime by using the Add() method of XElement. The new Element gets added as the last element of the XML document.

XElement xEle = XElement.Load("..\\..\\Employees.xml");

xEle.Add(new XElement("Employee",

new XElement("EmpId", 5),

new XElement("Name", "George")));

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

xEle.Add(New XElement("Employee", _

New XElement("EmpId", 5), _

New XElement("Name", "George")))

Console.Write(xEle)

18. Add a new Element as the First Child using LINQ to XML

In the previous example, by default the new Element gets added to the end of the XML document. If you want to add the Element as the First Child, use the ‘AddFirst()’ method

XElement xEle = XElement.Load("..\\..\\Employees.xml");

xEle.AddFirst(new XElement("Employee",

new XElement("EmpId", 5),

new XElement("Name", "George")));

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

xEle.AddFirst(New XElement("Employee", _

New XElement("EmpId", 5), _

New XElement("Name", "George")))

Console.Write(xEle)

19. Add an attribute to an Element using LINQ to XML

To add an attribute to an Element, use the following code:

XElement xEle = XElement.Load("..\\..\\Employees.xml");

xEle.Add(new XElement("Employee",

new XElement("EmpId", 5),

new XElement("Phone", "423-555-4224", new XAttribute("Type", "Home"))));

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

xEle.Add(New XElement("Employee", _

New XElement("EmpId", 5), _

New XElement("Phone", "423-555-4224", _

New XAttribute("Type", "Home"))))

Console.Write(xEle)

20. Replace Contents of an Element/Elements using LINQ to XML

Let us say that in the XML file, you want to change the Country from “USA” to “United States of America” for all the Elements. Here’s how to do so:

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var countries = xEle.Elements("Employee").Elements("Address").Elements("Country").ToList();

foreach (XElement cEle in countries)

cEle.ReplaceNodes("United States Of America");

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim countries = xEle.Elements("Employee").Elements("Address").Elements("Country").ToList()

For Each cEle As XElement In countries

cEle.ReplaceNodes("United States Of America")

Next cEle

Console.Write(xEle)

21. Remove an attribute from all the Elements using LINQ to XML

Let us say if you want to remove the Type attribute ( ) attribute for all the elements, then here’s how to do it.

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var phone = xEle.Elements("Employee").Elements("Phone").ToList();

foreach (XElement pEle in phone)

pEle.RemoveAttributes();

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim phone = xEle.Elements("Employee").Elements("Phone").ToList()

For Each pEle As XElement In phone

pEle.RemoveAttributes()

Next pEle

Console.Write(xEle)

To remove attribute of one Element based on a condition, traverse to that Element and SetAttributeValue("Type", null); You can also use SetAttributeValue(XName,object) to update an attribute value.

22. Delete an Element based on a condition using LINQ to XML

If you want to delete an entire element based on a condition, here’s how to do it. We are deleting the entire Address Element

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var addr = xEle.Elements("Employee").ToList();

foreach (XElement addEle in addr)

addEle.SetElementValue("Address", null);

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim addr = xEle.Elements("Employee").ToList()

For Each addEle As XElement In addr

addEle.SetElementValue("Address", Nothing)

Next addEle

Console.Write(xEle)

SetElementValue() can also be used to Update the content of an Element.

23. Remove ‘n’ number of Elements using LINQ to XML

If you have a requirement where you have to remove ‘n’ number of Elements; For E.g. To remove the last 2 Elements, then here’s how to do it

XElement xEle = XElement.Load("..\\..\\Employees.xml");

var emps = xEle.Descendants("Employee");

emps.Reverse().Take(2).Remove();

Console.Write(xEle);

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

Dim emps = xEle.Descendants("Employee")

emps.Reverse().Take(2).Remove()

Console.Write(xEle)

24. Save/Persists Changes to the XML using LINQ to XML

All the manipulations we have done so far were in the memory and were not persisted in the XML file. If you have been wondering how to persist changes to the XML, once it is modified, then here’s how to do so. It’s quite simple. You just need to call the Save() method. It’s also worth observing that the structure of the code shown below is similar to the structure of the end result (XML document). That’s one of the benefits of LINQ to XML, that it makes life easier for developers by making it so easy to create and structure XML documents.

XElement xEle = XElement.Load("..\\..\\Employees.xml");

xEle.Add(new XElement("Employee",

new XElement("EmpId", 5),

new XElement("Name", "George"),

new XElement("Sex", "Male"),

new XElement("Phone", "423-555-4224", new XAttribute("Type", "Home")),

new XElement("Phone", "424-555-0545", new XAttribute("Type", "Work")),

new XElement("Address",

new XElement("Street", "Fred Park, East Bay"),

new XElement("City", "Acampo"),

new XElement("State", "CA"),

new XElement("Zip", "95220"),

new XElement("Country", "USA"))));

xEle.Save("..\\..\\Employees.xml");

Console.WriteLine(xEle);

Console.ReadLine();

VB.NET (Converted Code)

Dim xEle As XElement = XElement.Load("..\..\Employees.xml")

xEle.Add(New XElement("Employee", _

New XElement("EmpId", 5), _

New XElement("Name", "George"), _

New XElement("Sex", "Male"), _

New XElement("Phone", "423-555-4224", _

New XAttribute("Type", "Home")), _

New XElement("Phone", "424-555-0545", _

New XAttribute("Type", "Work")), _

New XElement("Address", _

New XElement("Street", "Fred Park, East Bay"), _

New XElement("City", "Acampo"), _

New XElement("State", "CA"), _

New XElement("Zip", "95220"), _

New XElement("Country", "USA"))))

xEle.Save("..\..\Employees.xml")

Console.WriteLine(xEle)

Console.ReadLine()

Well with that, we conclude this long article of some 'How Do I' operations while using LINQ to XML. Through this article, we have only attempted to scratch the surface of what can be done using LINQ to XML. LINQ to XML is an amazing API and I hope this set of examples has demonstrated that. The entire source of the article in C# and VB.NET can be downloaded over here. The VB.NET code has been translated using a C# to VB.NET Converting tool.

Ref: .Net Curry

How to call other external applications from your .Net application?

2010-07-13T19:42:00.000-07:00

Calling an external application from your .Net application is frequently done if you are developing a product. During the installation of a product you might require to redirect the users who are installing your product to some web pages of your web site where they may get some information regarding your product. This web page might be a user manual in html format. You may sometimes be required to open a readme file in text format. This file is usually opened using a Notepad application. So there are many such requirements which require you to call an external application from your .Net application.

We will see some simple examples on how to call and execute an external application. For this we will create a console application with the following code:
Module Module1
Sub Main()
System.Diagnostics.Process.Start(“winword.exe”)
End Sub
End Module
The above code is written in VB.Net to call the Microsoft Word application from your .Net console application. Once your application is run, it opens the Word external application and your application terminates. Suppose you want to open an existing word document using your .Net console application you can pass the filename of the work document as an argument to the Start method of the Process class. This can be achieved as given below:
Module Module1
Sub Main()
System.Diagnostics.Process.Start(“winword.exe”, _ “first.doc”)
End Sub
End Module
For the above document to be opened properly you should have the document “first.doc” in the folder which contains the exe file of your application. Or else you have to specify the path of the document properly. You can also specify the path to avoid such problems in locating the file as,
System.Diagnostics.Process.Start(“winword.exe”, “c:\first.doc”)
Your application terminates after opening the document. Suppose you want to make your application wait for sometime while you work in the document, you can make it wait by using the WaitForExit() method of the process. A small change in your code would make your application wait indefinitely.
Dim p As New System.Diagnostics.Process()
p.Start("winword.exe", "c:\first.doc")
p.WaitForExit(10000)
The above code should make your application wait for 10 secs before it terminates. If you don’t pass any arguments to the WaitForExit() method, it will make your application wait indefinitely.
It is possible to set many other process information for the process that opens the external application. For example for the above process which opens the MS Word application you can also set other process information as given in the code below:
Module Module1
Sub Main()
Dim pi As New System.Diagnostics.ProcessStartInfo()
pi.FileName = "winword.exe"
pi.Arguments = "c:\first.doc"
pi.WorkingDirectory = "c:\"
pi.WindowStyle = ProcessWindowStyle.Maximized
Dim p As New System.Diagnostics.Process()
p.Start(pi)

End Sub
End Module
You can set the process start information properties such as Arguments, WorkingDirectory, and WindowStyle using the above code. There are other properties that might be useful to you. The Arguments property takes the filename that is to be opened, and the FileName properties takes the application name that is to be opened. You can set the working directory using the WorkingDirectory property. The window style can also be set using the WindowStyle property.
In some case you might be required to redirect the users to some web page in the internet. To achieve this you can just pass the URL of the web page to the Start method of the process as given below:
System.Diagnostics.Process.Start(“www.yoursite.com”)
The above code opens the default browser in your system to open the website given in the URL that is passed to the Start method. Thus you find it is easy to call an external application from your .Net application.

Web Service - Understanding WSDL and UDDI

2010-03-22T23:28:00.001-07:00

Web Services Description Language (WSDL) is one of the prime specifications in web services, the other two being SOAP and UDDI. WSDL is the description language for web services that describes a set of SOAP messages and how these messages are exchanged across network. WSDL will be in XML format; therefore it can be easily understood and edited by humans and machines.

Another advantage of WSDL being in XML format is that it is programming language independent and also platform independent. In addition, WSDL defines where the web service is available from and what communications protocol has been used to talk to the web service. As a result the WSDL file describes everything that is required to write a program for an XML Web service. There are tools available in Microsoft Visual Studio .NET to read a WSDL file and generate the code required to communicate with an XML Web service.
Universal Discovery Description Language (UDDI) is a directory where you can expose your web services for other users to easily access it. You can also consume the web service that is already posted in UDDI. However, you can also post a web service without registering it in UDDI. DISCO is another directory where you can post your web service. But if you want to reach to maximum of customers, you can place it in UDDI. The UDDI directory offers three parts for you to register:
• White Pages
• Yellow Pages
• Green Pages
The white pages consist of the description such as name and address of the company offering the service. The yellow pages consist of industrial categories based on standard taxonomies such as North American Industry Classification System and Standard Industrial Classification. The green pages describe the interface to the web service in detail so that anyone can write an application after using the web service. Web services are described in UDDI directory through a document called Type Model or tmodel. Normally, this tModel contains a WSDL file that describes a SOAP interface to an XML Web service, but the tModel is flexible enough to describe almost any kind of web service.
Apart from using the web services from UDDI, you can also search a particular web service in UDDI. In addition, you can search for companies’ information that posted web services. In certain times, you might know the names of the companies that offer web services but you may not be aware of the web services that they offer. The WS Inspection is a specification in UDDI that allows you to search for a collection of web services that are located in a particular company name. You can evaluate these web services according to your requirements.

What is Global.asax ? How to use it in .Net web projects ?

2010-03-20T08:37:00.000-07:00

The Global.asx file is an optional file that contains code for responding to application level events raised by ASP.NET. This file is also called as ASP.NET application file. This file resides in the root directory of an application. If we are not defining this file in application, the ASP.NET page framework assumes that you have not defined any applicationa/Session events in the application.

Followign are the list of events handled in the global.asax file.

Event: Application_Start

When an application starts. This event occurs only one time in an application’s life cycle. It occurs again when you restart the application. It’s the best place to count number of visitors.

Event: Application_End

This event occurs when application ends.

Event: Session_Start

This event occurs whenever a new session starts. This is the best place to count user sessions for an application

Practical Usage: Setting user specific values, caching items based upon user context.

Event: Session_End

This event Opposite of the previous event.

Event: Application_Error

This event occurs when an unhandled error or exception occurs in an application.

Event: Application_BeginRequest

This event occurs every time when server makes a request. This event may occur multiple times in an applications life cycle.

Event: Application_End

This event occurs when an application closes

Tracking Emails - Know Whether the Recipient Opens it

2010-03-01T22:54:00.000-08:00

It is very easy to track whether the user has read the email. The concept is very simple. You insert a link to a small transparent image in the mail sent, the mail being sent in HTML format, and when the user opens the email the image is loaded sending you a querystring to the web page that tracks the email. The web page requests the querystring or the parameter that is passed. This ensures that the email is read by the user.

When the page that is tracking is requested by the email sent you can get the value of the date and time when the request comes and you can store them in a database or send you an alert indicating the time and date at which the email was read by the user. This is the simple way that is used to track whether the emails sent are read or not.
To send emails you may be importing the namespace System.Web.Mail. You would be creating a MailMessage object as given below.
Imports System.Web.Mail
string sBody;
MailMessage mailMsg = new MailMessage();
mailMsg.BodyFormat = MailFormat.Html;
SmtpMail.SmtpServer = “your Server name”;
mailMsg.From = "your email address";
mailMsg.To = “to email address”;
mailMsg.Subject = “subject line of the mesg”;
mailMsg.Body = sBody;
SmtpMail.Send(mailMsg);
The above code sends an email to an email address as given in the mailMsg.To property. For tracking an email, all you have to do is to add an image tag to the body of the message i.e. sBody. This can be done by
sBody = sBody + "";
In the above line sParam is the parameter that is passed to the url and sYourDomain is your domain name where you create this page to track the email. If you send an email with the body of the message as given above, when the user reads the email, the image tag will try to load the url given in the tag. This will send the parameter “sentto” that is given by you to the page requested. In the trackemail.aspx page you can write code to retrieve the value of the parameter “sentto”. Once you receive the parameter you can do anything on that page to indicate that the user has read the email.
The code given below can be used in the trackemail.aspx page to retrieve the parameter.
private void Page_Load(object sender, System.EventArgs e)
{
if ( Page.IsPostBack == false )
{
if ( Request.Params["sentto"] != null )

//here you can send a mail to yourself
...
mailMsg.Body = Request.Params["sentto"].ToString()
+ “ has read your email”;
...
}
Response.Redirect("transparent.gif");
}
Once you have retrieved the parameter that is passed to the page you can redirect the request to a transparent image so that this transparent image, “transparent.gif” loads in the email message of the user who is reading the mail. Make this “transparent.gif” as small as possible, usually of 1 pixel by 1 pixel height and width, so that it does not take time to load. Since it is transparent the user who reads your mail may not know that an image has been loaded in the email message and the email that he reads is being tracked.
This way of tracking the email is so easy. Once you get the parameter in your trackemail.aspx page you can add a time stamp to the body of the email message that is sent to you in the Page_Load event of that page. This enables you to know the time and date at which the email was read by the user.

Software Development Life cycle(SDLC)

2010-02-12T08:18:00.000-08:00

Using Data Transformation Services (DTS)

2010-02-06T00:33:00.001-08:00

This procedure details the recommended method for transferring a database from a development server to one of our shared servers. The first half of the article covers how to generate an SQL script for all database objects. The second half of the article covers how to transfer the database to the destination server using the DTS Import/Export Wizard.
Unfortunately, the transfer functionality is limited (due to the non-DBO privileges of the user). Using the DTS Import/Export Wizard, it is not possible to transfer user-defined data types. Also, indexes, keys, and constraints may not transfer correctly. It might be possible to use a custom DTS script to accomplish a full transfer of all objects, however, that would be beyond the scope of our support.
This procedure is applicable to DTS transfers using the following SQL Server combinations:

SQL 7 to SQL 7
SQL 7 to SQL 2000
SQL 2000 to SQL 2000

In this procedure, SQL 2000 is used as an example. If you are using SQL 7, the images might appear slightly different, but the steps will remain the same.

Generating SQL Scripts Using Enterprise Manager:
With the Enterprise Manager open, right-click the source database, select All Tasks, and click Generate SQL Script.

Within the Generate SQL Scripts dialog box, from the General tab, first click the Show All button and then click to select Script all objects.

From the Formatting tab, click to select Generate the CREATE command for each object. From the Options tab, click to clear all Security Scripting Options. Click OK. Once complete, open the script using a text editor, and replace all instances of dbo (or whichever userID owned the objects on the source server) with your userID. Also, remove any calls to the setuser function, as these will fail. If you chose the option Generate the DROP command for each object in Step 3, you will need to leave the reference to dbo in the parts of the script where system tables such as sysobjects, systypes, and so on. are being qualified. Enterprise Manager does not always generate the statements in the correct order, so it may be necessary to move things around in the script. For example, it might generate a statement for a stored-procedure or a user-defined function that references a table that was created further down in the script. Execute the script on the destination server.

Transferring Databases Using the DTS Import/Export Wizard:

If your tables have foreign key constraints and relationships defined, you might have to transfer your data in separate batches so that you dont violate referential integrity.

With the Enterprise Manager open, right-click the source database, select All Tasks, and click Export Data. From the Data Transformation Services Import/Export Wizard dialog box, click Next. In the Choose a Data Source dialog box, from the Data Source drop-down menu, click to select your data source type. From the Server drop-down menu, click to select the source server. Click to choose the preferred method of authentication. If you choose Use SQL Server Authentication, you will need to provide a Username and Password. From the Database drop-down menu, click to select the database you want to copy, and click Next. In the Choose a destination dialog box, from the Destination drop-down menu, click to select your destination data source type. From the Server drop-down menu, click to select the destination server. Click to choose the preferred method of authentication. If you choose Use SQL Server Authentication, you will need to provide a Username and Password. From the Database drop-down menu, click to select the database you will be copying to, and click Next. From the Specify Table Copy or Query dialog box, click to choose whether you want to copy one or more tables or the results of a query from the source database. Click Next. From the Select Source Tables and Views dialog box, click to select all available tables, and then click to clear all views. Click Next. From the Save, schedule, and replicate package dialog box, click to select Run Immediately. Click Next. From the Completing the DTS Import/Export Wizard dialog box, review the Summary and click Finish. From the DTS Import/Export Wizard confirmation dialog box, click OK. From the Executing Package dialog box, wait for the process to complete and click Done. All data and tables should be copied. Now it is time to copy over the other SQL Server objects. Repeat Step 1 through 10 of the DTS Import\Export Wizard section. From the Specify Table Copy or Query dialog box, click to select Copy objects and data between SQL Server databases. Click Next. From the Select Objects to Copy dialog box, click to select Create destination objects (tables, views, stored procedures, constraints, etc.) and Drop destination objects first. Click Next. From the Select Objects dialog box, click to clear Show all tables and Show user-defined data types." Click OK. In the Advanced Copy Options dialog box, from the Security options section, click to clear all three security options. Click OK. From the Save, schedule, and replicate package dialog box, click to select Run immediately. Click Next. From the DTS Import/Export Wizard confirmation dialog box, click OK. From the Executing Package dialog box, wait for the process to complete and click Done.

Click and Retrieve the Value of a GridView Cell using jQuery

2010-01-21T20:32:00.000-08:00

This article demonstrates how to click and retrieve data from a GridView cell.

<title>Click and retrieve Cell Valuetitle>

.highlite

{

background-color:Gray;

}

style>

src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js">

script>

$(function() {

$(".gv > tbody > tr:not(:has(table, th))")

.css("cursor", "pointer")

.click(function(e) {

$(".gv td").removeClass("highlite");

var $cell = $(e.target).closest("td");

$cell.addClass('highlite');

var $currentCellText = $cell.text();

var $leftCellText = $cell.prev().text();

var $rightCellText = $cell.next().text();

var $colIndex = $cell.parent().children().index($cell);

var $colName = $cell.closest("table")

.find('th:eq(' + $colIndex + ')').text();

$("#para").empty()

.append("Current Cell Text: "

+ $currentCellText + "
")

.append("Text to Left of Clicked Cell: "

+ $leftCellText + "
")

.append("Text to Right of Clicked Cell: "

+ $rightCellText + "
")

.append("Column Name of Clicked Cell: "

+ $colName)

});

script>

head>

<body>

<h2>Click on any of the table cells to retrieve information

about the cell and its surrounding elementsh2><br />

<asp:GridView ID="grdEmployees" runat="server"

AllowPaging="True" PageSize="5" AutoGenerateColumns="False"

DataSourceID="ObjectDataSource1" class="gv">

<asp:BoundField DataField="ID" HeaderText="ID" />

<asp:BoundField DataField="FName" HeaderText="FName" />

<asp:BoundField DataField="MName" HeaderText="MName" />

<asp:BoundField DataField="LName" HeaderText="LName" />

<asp:BoundField DataField="DOB" HeaderText="DOB"

DataFormatString="{0:MM/dd/yyyy}"/>

<asp:BoundField DataField="Sex" HeaderText="Sex" />

Columns>

asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"

SelectMethod="GetEmployeeList" TypeName="Employee">

asp:ObjectDataSource>

<br />

div>

form>

body>

html>

The ObjectDataSource uses the Employee.cs or Employee.vb class whose definition is as follows:

using System;

using System.Collections.Generic;

public class Employee

{

public int ID { get; set; }

public string FName { get; set; }

public string MName { get; set; }

public string LName { get; set; }

public DateTime DOB { get; set; }

public char Sex { get; set; }

public List<Employee> GetEmployeeList()

{

List<Employee> empList = new List<Employee>();

empList.Add(new Employee() { ID = 1, FName = "John", MName = "", LName = "Shields", DOB = DateTime.Parse("12/11/1971"), Sex = 'M' });

empList.Add(new Employee() { ID = 2, FName = "Mary", MName = "Matthew", LName = "Jacobs", DOB = DateTime.Parse("01/17/1961"), Sex = 'F' });

empList.Add(new Employee() { ID = 3, FName = "Amber", MName = "Carl", LName = "Agar", DOB = DateTime.Parse("12/23/1971"), Sex = 'M' });

empList.Add(new Employee() { ID = 4, FName = "Kathy", MName = "", LName = "Berry", DOB = DateTime.Parse("11/15/1976"), Sex = 'F' });

empList.Add(new Employee() { ID = 5, FName = "Lena", MName = "Ashco", LName = "Bilton", DOB = DateTime.Parse("05/11/1978"), Sex = 'F' });

empList.Add(new Employee() { ID = 6, FName = "Susanne", MName = "", LName = "Buck", DOB = DateTime.Parse("03/7/1965"), Sex = 'F' });

empList.Add(new Employee() { ID = 7, FName = "Jim", MName = "", LName = "Brown", DOB = DateTime.Parse("09/11/1972"), Sex = 'M' });

empList.Add(new Employee() { ID = 8, FName = "Jane", MName = "G", LName = "Hooks", DOB = DateTime.Parse("12/11/1972"), Sex = 'F' });

empList.Add(new Employee() { ID = 9, FName = "Robert", MName = "", LName = "", DOB = DateTime.Parse("06/28/1964"), Sex = 'M' });

empList.Add(new Employee() { ID = 10, FName = "Krishna", MName = "Murali", LName = "Sunkam", DOB = DateTime.Parse("04/18/1969"), Sex = 'M' });

empList.Add(new Employee() { ID = 11, FName = "Cindy", MName = "Preston", LName = "Fox", DOB = DateTime.Parse("06/15/1978"), Sex = 'M' });

empList.Add(new Employee() { ID = 12, FName = "Nicole", MName = "G", LName = "Holiday", DOB = DateTime.Parse("08/21/1974"), Sex = 'F' });

empList.Add(new Employee() { ID = 13, FName = "Sandra", MName = "T", LName = "Feng", DOB = DateTime.Parse("04/15/1976"), Sex = 'F' });

empList.Add(new Employee() { ID = 14, FName = "Roberto", MName = "", LName = "Tamburello", DOB = DateTime.Parse("01/06/1982"), Sex = 'M' });

empList.Add(new Employee() { ID = 15, FName = "Cynthia", MName = "O", LName = "Lugo", DOB = DateTime.Parse("01/21/1968"), Sex = 'M' });

empList.Add(new Employee() { ID = 16, FName = "Yuhong", MName = "R", LName = "Li", DOB = DateTime.Parse("08/22/1979"), Sex = 'M' });

empList.Add(new Employee() { ID = 17, FName = "Alex", MName = "", LName = "Shoop", DOB = DateTime.Parse("03/01/1972"), Sex = 'M' });

empList.Add(new Employee() { ID = 18, FName = "Jack", MName = "K", LName = "Brown", DOB = DateTime.Parse("04/11/1978"), Sex = 'M' });

empList.Add(new Employee() { ID = 19, FName = "Andrew", MName = "U", LName = "Gibson", DOB = DateTime.Parse("08/21/1977"), Sex = 'M' });

empList.Add(new Employee() { ID = 20, FName = "George", MName = "K", LName = "Wood", DOB = DateTime.Parse("07/15/1972"), Sex = 'M' });

empList.Add(new Employee() { ID = 21, FName = "Eugene", MName = "", LName = "Miller", DOB = DateTime.Parse("09/13/1974"), Sex = 'M' });

empList.Add(new Employee() { ID = 22, FName = "Russell", MName = "", LName = "Gorgi", DOB = DateTime.Parse("08/19/1978"), Sex = 'M' });

empList.Add(new Employee() { ID = 23, FName = "Katie", MName = "", LName = "Sylar", DOB = DateTime.Parse("08/21/1978"), Sex = 'M' });

empList.Add(new Employee() { ID = 24, FName = "Michael", MName = "M", LName = "Bentler", DOB = DateTime.Parse("11/26/1977"), Sex = 'M' });

empList.Add(new Employee() { ID = 25, FName = "Barbara", MName = "L", LName = "Duffy", DOB = DateTime.Parse("05/29/1972"), Sex = 'M' });

empList.Add(new Employee() { ID = 26, FName = "Stefen", MName = "J", LName = "Northup", DOB = DateTime.Parse("01/26/1972"), Sex = 'M' });

empList.Add(new Employee() { ID = 27, FName = "Shane", MName = "", LName = "Nay", DOB = DateTime.Parse("02/21/1978"), Sex = 'M' });

empList.Add(new Employee() { ID = 28, FName = "Brenda", MName = "", LName = "Lugo", DOB = DateTime.Parse("08/18/1981"), Sex = 'F' });

empList.Add(new Employee() { ID = 29, FName = "Shammi", MName = "I", LName = "Rai", DOB = DateTime.Parse("03/13/1968"), Sex = 'M' });

empList.Add(new Employee() { ID = 30, FName = "Rajesh", MName = "H", LName = "Vyas", DOB = DateTime.Parse("04/19/1969"), Sex = 'M' });

empList.Add(new Employee() { ID = 31, FName = "Gabe", MName = "P", LName = "Lloyd", DOB = DateTime.Parse("08/21/1971"), Sex = 'M' });

return empList;

}

VB.NET

Imports System

Imports System.Collections.Generic

Public Class Employee

Private privateID As Integer

Public Property ID() As Integer

Get

Return privateID

End Get

Set(ByVal value As Integer)

privateID = value

End Set

End Property

Private privateFName As String

Public Property FName() As String

Get

Return privateFName

End Get

Set(ByVal value As String)

privateFName = value

End Set

End Property

Private privateMName As String

Public Property MName() As String

Get

Return privateMName

End Get

Set(ByVal value As String)

privateMName = value

End Set

End Property

Private privateLName As String

Public Property LName() As String

Get

Return privateLName

End Get

Set(ByVal value As String)

privateLName = value

End Set

End Property

Private privateDOB As DateTime

Public Property DOB() As DateTime

Get

Return privateDOB

End Get

Set(ByVal value As DateTime)

privateDOB = value

End Set

End Property

Private privateSex As Char

Public Property Sex() As Char

Get

Return privateSex

End Get

Set(ByVal value As Char)

privateSex = value

End Set

End Property

Public Function GetEmployeeList() As List(Of Employee)

Dim empList As New List(Of Employee)()

empList.Add(New Employee() With {.ID = 1, .FName = "John", .MName = "", .LName = "Shields", .DOB = DateTime.Parse("12/11/1971"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 2, .FName = "Mary", .MName = "Matthew", .LName = "Jacobs", .DOB = DateTime.Parse("01/17/1961"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 3, .FName = "Amber", .MName = "Carl", .LName = "Agar", .DOB = DateTime.Parse("12/23/1971"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 4, .FName = "Kathy", .MName = "", .LName = "Berry", .DOB = DateTime.Parse("11/15/1976"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 5, .FName = "Lena", .MName = "Ashco", .LName = "Bilton", .DOB = DateTime.Parse("05/11/1978"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 6, .FName = "Susanne", .MName = "", .LName = "Buck", .DOB = DateTime.Parse("03/7/1965"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 7, .FName = "Jim", .MName = "", .LName = "Brown", .DOB = DateTime.Parse("09/11/1972"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 8, .FName = "Jane", .MName = "G", .LName = "Hooks", .DOB = DateTime.Parse("12/11/1972"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 9, .FName = "Robert", .MName = "", .LName = "", .DOB = DateTime.Parse("06/28/1964"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 10, .FName = "Krishna", .MName = "Murali", .LName = "Sunkam", .DOB = DateTime.Parse("04/18/1969"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 11, .FName = "Cindy", .MName = "Preston", .LName = "Fox", .DOB = DateTime.Parse("06/15/1978"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 12, .FName = "Nicole", .MName = "G", .LName = "Holiday", .DOB = DateTime.Parse("08/21/1974"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 13, .FName = "Sandra", .MName = "T", .LName = "Feng", .DOB = DateTime.Parse("04/15/1976"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 14, .FName = "Roberto", .MName = "", .LName = "Tamburello", .DOB = DateTime.Parse("01/06/1982"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 15, .FName = "Cynthia", .MName = "O", .LName = "Lugo", .DOB = DateTime.Parse("01/21/1968"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 16, .FName = "Yuhong", .MName = "R", .LName = "Li", .DOB = DateTime.Parse("08/22/1979"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 17, .FName = "Alex", .MName = "", .LName = "Shoop", .DOB = DateTime.Parse("03/01/1972"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 18, .FName = "Jack", .MName = "K", .LName = "Brown", .DOB = DateTime.Parse("04/11/1978"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 19, .FName = "Andrew", .MName = "U", .LName = "Gibson", .DOB = DateTime.Parse("08/21/1977"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 20, .FName = "George", .MName = "K", .LName = "Wood", .DOB = DateTime.Parse("07/15/1972"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 21, .FName = "Eugene", .MName = "", .LName = "Miller", .DOB = DateTime.Parse("09/13/1974"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 22, .FName = "Russell", .MName = "", .LName = "Gorgi", .DOB = DateTime.Parse("08/19/1978"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 23, .FName = "Katie", .MName = "", .LName = "Sylar", .DOB = DateTime.Parse("08/21/1978"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 24, .FName = "Michael", .MName = "M", .LName = "Bentler", .DOB = DateTime.Parse("11/26/1977"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 25, .FName = "Barbara", .MName = "L", .LName = "Duffy", .DOB = DateTime.Parse("05/29/1972"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 26, .FName = "Stefen", .MName = "J", .LName = "Northup", .DOB = DateTime.Parse("01/26/1972"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 27, .FName = "Shane", .MName = "", .LName = "Nay", .DOB = DateTime.Parse("02/21/1978"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 28, .FName = "Brenda", .MName = "", .LName = "Lugo", .DOB = DateTime.Parse("08/18/1981"), .Sex = "F"c})

empList.Add(New Employee() With {.ID = 29, .FName = "Shammi", .MName = "I", .LName = "Rai", .DOB = DateTime.Parse("03/13/1968"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 30, .FName = "Rajesh", .MName = "H", .LName = "Vyas", .DOB = DateTime.Parse("04/19/1969"), .Sex = "M"c})

empList.Add(New Employee() With {.ID = 31, .FName = "Gabe", .MName = "P", .LName = "Lloyd", .DOB = DateTime.Parse("08/21/1971"), .Sex = "M"c})

Return empList

End Function

End Class

The example starts with a filter applied on the GridView rows

$(".gv > tbody > tr:not(:has(table, th))")

This filter is required since a GridView does not render a THEAD and a TFOOT (accessibility tags) by default. For the header, the GridView generates TH’s inside a TR. Similarly for the footer, the GridView generates a TABLE inside a TR and so on. Hence it is required to use additional filters to exclude header and footer rows while performing operations on the GridView rows. The filter shown above selects only those rows which are inside the TBODY.

This recipe uses event delegation. Observe how we have used ‘e.target’ to find out the element that was clicked. This object is cached in the ‘cell’ variable.

var $cell = $(e.target).closest("td");

The closest() as given in the jQuery documentation, “works by first looking at the current element to see if it matches the specified expression, if so it just returns the element itself. If it doesn't match then it will continue to traverse up the document, parent by parent, until an element is found that matches the specified expression.”

With the help of the ‘cell’ variable, we can use the DOM tree traversal methods like prev() and next(), to retrieve the value of the immediate ‘preceding’ and ‘following’ elements, respectively.

var $leftCellText = $cell.prev().text();

var $rightCellText = $cell.next().text();

Similarly the column header text is retrieved using the code shown below.

var $colIndex = $cell.parent().children().index($cell);

var $colName = $cell.closest("table")

.find('th:eq(' + $colIndex + ')').text();

As shown above, after retrieving the column index, we use the closest() method to traverse up the DOM, parent by parent until we find the table element. The Header text is then selected using

('th:eq(' + $colIndex + ')').text()

When a Cell of the GridView is clicked (for example 2^nd Row and 3^rd Column), the result is as shown below:

See a Live Demo.

The entire source code of this article can be downloaded over here

Saving an Image in a SQL Server Database

2009-12-24T21:35:00.000-08:00

Most of the web applications have a lot of images used in it. These images are usually stored in a web server folder and they are accessed by giving the relative path to the file with respect to the root folder of the website.

.Net being the platform for distributed application now, ASP.Net can be used to store images that are small to be stored in a database like SQL Server 2000 and later versions. For this purpose the SQL Server database provides a data type called “image” which is used to store images in the database.
To access these images stored in the database we will be using the ADO.Net classes. To find out how to insert and retrieve an image in to the SQL Server database, you can create a .aspx page which can have a HTMLInputFile control which is used to select the image file that is to be saved in the database.
You can also create a textbox control in which you can add the image name or some comment or an image id for the image saved. Use a button control to upload the image to the database. Namespaces like System.Data.SqlClient, System.Drawing, System.Data, System.IO, and System.Drawing.Imaging are used in this task.
In the OnClick property of the button you can write the following code to upload an image to the database.

// create a byte[] for the image file that is uploaded
int imagelen = Upload.PostedFile.ContentLength;
byte[] picbyte = new byte[imagelen];
Upload.PostedFile.InputStream.Read (picbyte, 0, imagelen);
// Insert the image and image id into the database
SqlConnection conn = new SqlConnection (@"give the connection string here...");
try
{
conn.Open ();
SqlCommand cmd = new SqlCommand ("insert into ImageTable "
+ "(ImageField, ImageID) values (@pic, @imageid)", conn);
cmd.Parameters.Add ("@pic", picbyte);
cmd.Parameters.Add ("@imageid", lblImageID.Text);
cmd.ExecuteNonQuery ();
}
finally
{
conn.Close ();
}
You can also write the above code in a function and call that function in the OnClick event of the upload button. The code given above performs the following steps in the process of inserting an image into the database.
1. Get the content length of the image that is to be uploaded
2. Create a byte[] to store the image
3. Read the input stream of the posted file
4. Create a connection object
5. Open the connection object
6. Create a command object
7. Add parameters to the command object
8. Execute the sql command using the ExecuteNonQuery method of the command object
9. Close the connection object
To retrieve the image from the SQL Database you can perform the following steps.
1. Create a MemoryStream object. The code can be something like,
MemoryStream mstream = new MemoryStream ();
2. Create a Connection object
3. Open the connection to the database
4. Create a command object to execute the command to retrieve the image
5. Use the command object’s ExecuteScalar method to retrieve the image
6. Cast the output of the ExecuteScalar method to that of byte[]
byte[] image = (byte[]) command.ExecuteScalar ();
7. Write the stream
mstream.Write (image, 0, image.Length);
8. Create a bitmap object to hold the stream
Bitmap bitmap = new Bitmap (stream);
9. Set the content type to “image/gif”
Response.ContentType = "image/gif";
10. Use the Save method of the bitmap object to output the image to the OutputStream.
bitmap.Save (Response.OutputStream, ImageFormat.Gif);
11. Close the connection
12. Close the stream
mstream.Close();
Using the above steps you can retrieve and display the image from the database to the web page.
You can use these algorithms and take advantage of the “image” data type available in the SQLServer 2000 database to store small images that correspond to a particular record in the table of the database. This method of storing avoids the tedious task of tracking the path of the web folder if the images are stored in a web folder.

Creating Email Triggers in SQL Server 2005

2009-12-09T01:06:00.000-08:00

Creating Email Triggers in SQL Server 2005

What exactly is a trigger?

It is a piece of SQL that is activated when a certain event happens or occurs. A common usage is when a new record is added to a database, this triggers an action - for example, a new member triggers an email to the admin for the registration. Or you have certain records in a database table that are assigned to a particular customer and, upon that record being updated, that customer/admin should be notified of the change.

Triggers are useful to fire events and perform some action when data is inserted, updated, or deleted from a table. Microsoft SQL Server's xp_sendmail stored procedure can be invoked to send an email to one or more recipients.

Creating the Table

We will start by creating a table which will hold Customer Information that need to be updated. The name of the table is CustomerInfo

or you can use the following Sql Script

CREATE TABLE [CustomerInfo]

(

          [CUSTOMER_ID] [int] IDENTITY(1,1) NOT NULL,

[FIRST_NAME] [varchar](50) NULL,

[MIDDLE_NAME] [varchar](50) NULL,

[LAST_NAME] [nvarchar](50) NULL,

)

Inserting the Data

Now, we will insert some dummy data into the table. You can directly insert the data into the table by using the Grid Format or you can just write the script to do that.

or you can use the following Sql Script

INSERT INTO [CustomerInfo]([CUSTOMER_ID],[FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES (1,'Amjad','Ali','Leghari')

INSERT INTO [CustomerInfo]([CUSTOMER_ID],[FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES (2,'Ali','Raza','Shaikh')

Creating the Trigger

Now coming down to the business, we will create the trigger for the CustomerInfo table, but there are few steps that need to be checked before creating the trigger.

Check that the trigger already exists
If the trigger already exists, then delete it

If the trigger doesn’t exists, now you can create the specified trigger. Right click on the Trigger section and click on “New Trigger”.

This will generate the following template.

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>

ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>

AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

END

So, now we end up writing the following trigger

-- Create Trigger with name 'CustomerUpdateMail'

CREATE TRIGGER CustomerUpdateMail

-- and the table is CustomerInfo

ON CustomerInfo

-- trigger is fired when an update is made for the table

FOR UPDATE

-- holds the CustomerID so we know which Customer was updated

declare @CustomerID varchar(10)

-- holds the body of the email

declare @body varchar(2000)

-- holds the old customer name which has been changed

declare @CustomerName varchar(10)

-- holds the new customer name

declare @CustomerNewName varchar(10)

-- gets the previous customer first name that was deleted

SELECT @CustomerID = Customer_id,

@CustomerName = d.First_Name

FROM deleted d

-- gets the new customer first name

SELECT @CustomerNewName = First_Name

FROM inserted

SET @body = 'Customer with ID=' @CustomerID ' has been updated

with previous First Name is ' @CustomerName '

and the new First Name is ' @CustomerNewName

--xp_sendmail is the extended sproc used to send the mail

EXEC master..xp_sendmail

@recipients = 'ali_raza_shaikh@datasprings.com',

@subject = 'Customer Information Updated',

@message = @body

So, after writing the trigger, execute it to create the trigger and you can see the newly created trigger under the trigger section.

xp_sendmail extended stored procedure

Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message. In order to use this extended stored procedure you must have SQL Mail Setup. You must grant EXECUTE permissions for the xp_sendmail procedure in the master database. After completing all these steps, the following lines will send the mail to the target recipient.

--xp_sendmail is the extended sproc used to send the mail

EXEC master..xp_sendmail

@recipients = 'deepakkarma@gmail.com',

@subject = 'Customer Information Updated',

@message = @body

Testing the Trigger

After the table has been created, we inserted the dummy data into the tables and in the end created a trigger to mail the user about the customer update, now its time to execute the trigger. You can execute the trigger by using the following lines.

update customerinfo

set First_Name='Deepak Update'

where customer_id=1

Create an ASP.NET TextBox Watermark Effect using jQuery

2009-12-06T06:24:00.001-08:00

Create an ASP.NET TextBox Watermark Effect using jQuery

This short article demonstrates how to create a watermark effect on your TextBox and display instructions to users, without taking up screen space. This article is a sample chapter from my EBook called 51 Tips, Tricks and Recipes with jQuery and ASP.NET Controls. The chapter has been modified a little to publish it as an article.

Note that for demonstration purposes, I have included jQuery code in the same page. Ideally, these resources should be created in separate folders for maintainability.

Let us quickly jump to the solution and see how we can create a watermark effect on your TextBox using client-side code.

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>TextBox WaterMarktitle>

src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js">

script>

.water

{

font-family: Tahoma, Arial, sans-serif;

color:gray;

}

style>

$(function() {

$(".water").each(function() {

$tb = $(this);

if ($tb.val() != this.title) {

$tb.removeClass("water");

}

});

$(".water").focus(function() {

$tb = $(this);

if ($tb.val() == this.title) {

$tb.val("");

$tb.removeClass("water");

}

});

$(".water").blur(function() {

$tb = $(this);

if ($.trim($tb.val()) == "") {

$tb.val(this.title);

$tb.addClass("water");

}

});

script>

head>

<body>

<h2>TextBox Watermark Demonstrationh2> <br />

<asp:TextBox ID="txtFNm" class="water" Text="Type your First Name"

Tooltip="Type your First Name" runat="server">asp:TextBox><br />

<asp:TextBox ID="txtLNm" class="water" Text="Type your Last Name"

Tooltip="Type your Last Name" runat="server">asp:TextBox>

<asp:Button ID="btnSubmit" runat="server" Text="Submit" />

Tip: Click on the TextBox to start typing. The watermark

text disappears.

div>

form>

body>

html>

The code shown above adds the “watermark” behavior to controls marked with the ‘class=water’ attribute. When the user loads the page, a watermarked textbox displays a message to the user. As soon as the watermarked textbox receives focus and the user types some text, the watermark goes away. This technique is a great space saver as you can use it to provide instructions to the user, without using extra controls that take up valuable space on your form.

The ‘Tooltip’ attribute applied to the textbox is crucial to this example. The ‘Tooltip’ gets rendered as ‘title’. Observe the code, as we use this ‘title’ property to compare it to the textbox value and remove the watermark css, when the textbox control gains focus

$(".water").focus(function() {

$tb = $(this);

if ($tb.val() == this.title) {

$tb.val("");

$tb.removeClass("water");

}

});

Similarly when the user moves focus to a different control without entering a value in the textbox, we add the watermark css again.

$(".water").blur(function() {

$tb = $(this);

if ($.trim($tb.val()) == "") {

$tb.val(this.title);

$tb.addClass("water");

}

});

The water class declared in Demos.css looks like this:

.water

{

font-family: Tahoma, Arial, sans-serif;

font-size:75%;

color:gray;

}

When the page loads for the first time, the watermark is visible as shown here:

When the user enters the First/Last Name and submits the form, the watermark behavior is no more needed to be displayed. This is achieved by comparing the ‘title’ with the ‘value’ of the textbox. If the ‘value’ does not match the ‘title’, this indicates that the user has entered some value in the textboxes and submitted the form. So in this case we remove the watermark appearance.

$(".water").each(function() {

$tb = $(this);

if ($tb.val() != this.title) {

$tb.removeClass("water");

}

});

After the user enters the details and submits the form, the result is similar to the one shown here, without the watermark:

Thanks to Arnold Matusz for sharing the tip about the tooltip property. The code has been tested on IE 7, IE 8, Firefox 3, Chrome 2, Safari 4 browsers

You can see a Live Demo over here

HOW TO: Pass Current Credentials to an ASP.NET Web Service

2009-11-26T22:23:00.000-08:00

This step-by-step article describes how to pass the user's current credentials to an XML Web service that was created by using ASP.NET.

The DefaultCredentials property of the CredentialCache class contains the system credentials of the current security context. For client applications, these credentials represent the user name, the password, and the domain of the user who is currently logged on. Client credentials are not passed automatically. To pass the client's Windows security context to a Web service, you must set the Credentials property of the Web service proxy to CredentialCache.DefaultCredentials.

Create the Web Service

Start Microsoft Visual Studio .NET. Create a new ASP.NET Web Service project by using Visual C# .NET or Visual Basic .NET. By default, Service1.asmx is created.
Name the project MyWebService.
In Solution Explorer, right-click Service1.asmx, and then click View Code.
In the Service1.asmx.cs file (or the Service1.asmx.vb file if you used Visual Basic .NET), remove the comment on the default WebMethod HelloWorld().
On the Build menu, click Build Solution.
Type the following URL in your browser to view the Service1 Web service description:
http://localhost/MyWebService/Service1.asmx
To test the HelloWorld WebMethod, click the HelloWorld link. Notice that the WebMethod works as expected.

Set Integrated Windows Authentication for the Web Service

Click Start, point to Settings, and then click Control Panel.
In Control Panel, double-click Administrative Tools.
Double-click Internet Information Services.
Expand Internet Information Services, and then locate the MyWebService virtual directory.
Right-click MyWebService, and then click Properties.
Click the Directory Security tab. Under Anonymous access and authentication control, click Edit.
In the Authentication Methods dialog box, click to select the check box for Integrated Windows authentication.

Use the Web Service

Create a new ASP.NET Web Application by using Visual C# .NET or Visual Basic .NET. Name the project WebServiceTest.
In Solution Explorer, right-click References, and then click Add Web Reference.
In the Address text box, type the following URL for WebServiceTest:
http://localhost/MyWebService/Service1.asmx
Click Go, and then click Add Reference.
In Solution Explorer, right-click WebForm1.aspx, and then click View Code.

In the Design View of WebForm1, double-click WebForm1 to open the Page_Load event code. Change the Page_Load event code as follows:

Visual C# .NET Sample Code

private void Page_Load(object sender, System.EventArgs e)
{
// Start an instance of the Web Service client-side proxy.
localhost.Service1 myProxy = new localhost.Service1();
Response.Write( myProxy.HelloWorld()); 
}

Visual Basic .NET Sample Code

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Start an instance of the Web Service client-side proxy.
Dim myProxy As localhost.Service1 = New localhost.Service1()
Response.Write(myProxy.HelloWorld())
End Sub

On the Build menu, click Build Solution.
Type the following URL in the browser to view the Service1 Web service description:
http://localhost/WebServiceTest/WebForm1.aspx
You may receive an Access Denied error message. This occurs because your credentials are not delivered with the Web service request for authentication.

Pass Current Credentials to the Web Service

The CredentialCache class belongs to the System.Net namespace.

Add the following namespace declaration to the top of the file:

Visual C# .NET Sample Code
```
using System.Net;
```
Visual Basic .NET Sample Code
```
Imports System.Net
```

Assign DefaultCredentials to the Credentials property of the Web service client-side proxy. To do this, change the code of the Page_Load event as follows:

Visual C# .NET Sample:

private void Page_Load(object sender, System.EventArgs e)
{
// Start an instance of the Web service client-side proxy.
localhost.Service1 myProxy = new localhost.Service1();
myProxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
Response.Write( myProxy.HelloWorld()); 
}

Visual Basic .NET Sample Code

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Start an instance of the Web service client-side proxy.
Dim myProxy As localhost.Service1 = New localhost.Service1()
myProxy.Credentials = System.Net.CredentialCache.DefaultCredentials
Response.Write(myProxy.HelloWorld())
End Sub

On the Debug menu, click Start. Hello World appears in the browser.

ASP.NET MVC 2 Beta Default Parameter Values

2009-11-26T06:53:00.001-08:00

ASP.NET MVC 2 Beta Default Parameter Values

If you’ve been living under a rock in the last few days you may have missed one of the big announcements at PDC 09. Microsoft has released ASP.NET MVC 2 Beta! This is very exciting! To get your hands on this beta release you can go here to download it.

Some of the cooler features in this release are the following:

- Strongly typed UI helpers
- Areas support
- DataAnnotation validation support
- UI helper templating support

ASP.NET MVC 2 Beta works side by side with ASP.NET MVC 1. This article was creating using Visual Studio 2008 Service Pack 1. Once you’ve installed the beta you’ll have the option for both projects.

I’ll focus more on the different areas in upcoming articles. This article will look at the new DefaultValue attribute. This is a way for specifying default values for parameters. In ASP.NET MVC 1 you either had to write a custom route to pass in default values, or create nullable types as parameters and check for the null values in your code. ASP.NET MVC 2 Beta now supports decorating action method parameters with the DefaultValueAttribute from the System.ComponentModel namespace. This allows you to specify parameters values if none are present.

The above scenario is much simpler with an example. Suppose you build a website which displays a list of cars. You’ll need to create a cars controller. Each car has a make and model, but the user might not know the make and model, so this is when you would create a default route in the global.asax file to handle this:

routes.MapRoute(

"CarsDefault",

"Cars/{action}/{make}/{model}",

new { controller = "Cars", action = "Model", make = "Holden", model = "Camaro" }

);

routes.MapRoute(

"Default",

"{controller}/{action}/{id}",

new { controller = "Home", action = "Index", id = "" }

);

The first MapRoute will default the make value to Holden and the model value to Camaro if no parameters are passed to the action method. There’s nothing wrong with doing it this way. The downside of this is your global.asax file can fill up quickly if you have numerous routes. In ASP.NET MVC 2 Beta this is taken care of for you thanks to DefaultValue attribute. Now you can leave the global.asax file and move the logic into your action method:

using System.ComponentModel;

public ActionResult Make([DefaultValue("Holden")] string make,

[DefaultValue("Camaro")] string model)

{

return View();

}

The DefaultValue attribute is prefixed to the parameter, so in the example above, if I don’t specify a make, the default value will be Holden. This frees up the global.asax file and makes your code more discoverable in my opinion. Default values can be positioned anywhere in your arguments, so your action could look like this:

using System.ComponentModel;

public ActionResult Make([DefaultValue("Holden")] string make,

string model)

{

return View();

}

This is one of the new features in ASP.NET MVC 2 Beta. In the next few weeks I’ll be posting more articles on the other new features. Stay tuned! The entire source code of this article can be downloaded over here

Partitioning Data Using LINQ

2009-11-16T20:24:00.003-08:00

We often come across requirements where data needs to be partitioned into two parts and then return one of the parts. The Partition Operators in LINQ helps you partition data. These operators are Skip, SkipWhile, Take and TakeWhile. In this article, we will see how to use these operators to partition collections. Malcolm Sheridan had posted a cool article demonstrating a practical usage of the Skip and Take operators. You can read it over here Efficient Server Side Paging with the ASP.NET GridView Control. You can also read more about Partition Operators over here

For demonstration purposes, we will take the Person class as a sample whose definition is as shown below:

class Program

{

static void Main(string[] args)

{

List<Person> collectionOne = new List<Person>();

collectionOne.Add(new Person() { ID = 1, Name = "Jack" });

collectionOne.Add(new Person() { ID = 2, Name = "Julian" });

collectionOne.Add(new Person() { ID = 3, Name = "David" });

collectionOne.Add(new Person() { ID = 4, Name = "Kathy" });

collectionOne.Add(new Person() { ID = 5, Name = "Jennifer" });

collectionOne.Add(new Person() { ID = 6, Name = "Billy" });

}

static void PrintConsole(List<Person> per)

{

foreach (Person p in per)

{

Console.WriteLine("{0} {1}", p.ID, p.Name);

}

Console.ReadLine();

}

class Person

{

public int ID { get; set; }

public string Name { get; set; }

}

VB.NET

Module Module1

Sub Main()

Dim collectionOne As New List(Of Person)()

collectionOne.Add(New Person() With {.ID = 1, .Name = "Jack"})

collectionOne.Add(New Person() With {.ID = 2, .Name = "Julian"})

collectionOne.Add(New Person() With {.ID = 3, .Name = "David"})

collectionOne.Add(New Person() With {.ID = 4, .Name = "Kathy"})

collectionOne.Add(New Person() With {.ID = 5, .Name = "Jennifer"})

collectionOne.Add(New Person() With {.ID = 6, .Name = "Billy"})

End Sub

Sub PrintConsole(ByVal per As List(Of Person))

For Each p As Person In per

Console.WriteLine("{0} {1}", p.ID, p.Name)

Next p

Console.ReadLine()

End Sub

End Module

Class Person

Private privateID As Integer

Public Property ID() As Integer

Get

Return privateID

End Get

Set(ByVal value As Integer)

privateID = value

End Set

End Property

Private privateName As String

Public Property Name() As String

Get

Return privateName

End Get

Set(ByVal value As String)

privateName = value

End Set

End Property

End Class

The PrintConsole() method accepts a List<> and prints it on the console.

Let us now explore the Partition Operators.

Skip – The ‘Skip’ Operator skips elements to a specified position in a sequence and returns the remaining elements. Let us see how to use the Skip operator

In this example, we will be skipping the first two elements in the List and return the remaining elements

var p1 = collectionOne.Skip(2).ToList();

PrintConsole(p1);

VB.NET

Dim p1 = collectionOne.Skip(2).ToList()

PrintConsole(p1)

Output

SkipWhile – The ‘SkipsWhile’ operator skips elements in a sequence as long as the specified condition remains true and then returns the remaining elements.

In this example, we will be skipping the elements in the List till the name “Kathy” and return the remaining elements along with “Kathy”

var p2 = collectionOne.SkipWhile(x => x.Name != "Kathy").ToList();

PrintConsole(p2);

VB.NET

Dim p2 = collectionOne.SkipWhile(Function(x) x.Name <> "Kathy").ToList()

PrintConsole(p2)

Output

Take – The ‘Take’ operator grabs elements to a specified position in the sequence and returns the grabbed elements

In this example, we will grab the first two elements in the List

var p3 = collectionOne.Take(2).ToList();

PrintConsole(p3);

VB.NET

Dim p3 = collectionOne.Take(2).ToList()

PrintConsole(p3)

Output

TakeWhile - The ‘TakeWhile’ operator grabs elements to a specified position in the sequence till the specified condition is true and returns the grabbed elements

In this example, we will grab the elements till ID<=4 in the List

var p4 = collectionOne.TakeWhile(x => x.ID <= 4).ToList();

PrintConsole(p4);

VB.NET

Dim p4 = collectionOne.TakeWhile(Function(x) x.ID <= 4).ToList()

PrintConsole(p4)

Output

In this article, we saw how to use LINQ operators to partition data.

Use Keyboard Shortcuts to Create Tooltips for Forms using jQuery

2009-11-04T20:16:00.001-08:00

I’ve said this once and I’ll say it again, jQuery is cool. Something on the web that is very helpful to users when filling out HTML forms is for you, the developer, to give them help along the way. I thought it would be cool to use jQuery to show help tooltips when the user clicks on a combination of keys using their keyboard. This article will demonstrate how to capture keyboard events in JavaScript and display help tooltips.

Before we get started, this example uses the latest version of jQuery which is 1.3.2. That can be downloaded from here. The jQuery Tools and can be downloaded from here.

Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application. Add the following HTML to the Default.aspx page:

<table>

<tr>

<td>

Given Name

td>

<td>

td>

<td>

Please supply your given namediv>

td>

tr>

<tr>

<td>

Surname

td>

<td>

td>

<td>

Please supply your surname. This can only be <b>20b> characters longdiv>

td>

tr>

table>

The HTML above is quite simple. There’s a table which contains two text boxes. In the cell to the right of the text boxes there’s a

element which contains an image and some useful text if the user gets lost or in unsure of what value to put in the text box. To make a generic piece of JavaScript I’m going to use the title attribute in the

element. I’ll use that as the key to combine with the shift key. The next step is to add the following jQuery code to the section of the page. Ideally JavaScript should go to a separate .js file, however for this example; I’ll keep it in the same page:

$(document).ready(function() {

$(this).keydown(function(e) {

var evt = e || window.event;

if (evt.shiftKey) {

findHelp(evt).fadeIn("slow");

}

});

$(this).keyup(function(e) {

var evt = e || window.event;

if (evt.shiftKey) {

findHelp(evt).hide("fast");

}

});

function findHelp(e) {

var key = (e.keyCode ? e.keyCode : e.charCode);

return $("div[title=" + String.fromCharCode(key) + "]");

}

script>

In the above code, I’m binding to the document’s keydown and keyup events. On the keydown event, I want to show the tooltip, and on keyup, hide it.

$(this).keydown(function(e)

$(this).keyup(function(e)

Both pieces of code get information about the event through the argument e. Because I only want this to work when the user holds down the shift key, I’m checking for that through the evt.shiftKey variable. This indicates whether the shift key was pressed when the event fired

var evt = e || window.event;

if (evt.shiftKey)

From there I have created a function called findHelp. The sender of the event is passed to this function. I grab either the keyCode or charCode value, and then use that in the jQuery Selector to find the related

element by its title attribute:

function findHelp(e) {

var key = (e.keyCode ? e.keyCode : e.charCode);

return $("div[title=" + String.fromCharCode(key) + "]");

}

Running this code now will display the simple form. If you hold down Shift + S or Shift + G it will show and hide the tooltip for each text box.

Before the keydown event and on the keyup event

During the keydown event

This is a nice addition for any website that wants to help their users fill out HTML forms. For more information on keyboard events you can go here for more reading. The entire source code of this article can be downloaded over here

Implementing KeyBoard Shortcuts on an ASP.NET Hyperlink Control using jQuery

2009-11-02T21:07:00.000-08:00

Implementing KeyBoard Shortcuts on an ASP.NET Hyperlink Control using jQuery

Popular Web apps like Gmail and Windows Live Mail feature Keyboard shortcuts, which help you save time by executing common operations using the Keyboard, without having to switch between the keyboard and mouse. In this short article, I will demonstrate how to use jQuery to implement keyboard shortcuts on an ASP.NET Hyperlink control. This article is a sample chapter from my EBook called 51 Tips, Tricks and Recipes with jQuery and ASP.NET Controls.

Note that for demonstration purposes, I have included jQuery code in the same page. Ideally, these resources should be created in separate folders for maintainability.

Let us quickly jump to the solution and see how we can implement KeyBoard shortcut on the Hyperlink control

<title>Implement KeyBoard Shortcuts on Hyperlinkstitle>

src='../Scripts/jquery-1.3.2.min.js'>

script>

$(function() {

$(document).keyup(function(e) {

var key = (e.keyCode ? e.keyCode : e.charCode);

switch (key) {

case 49:

navigateUrl($('a[id$=linkA]'));

break;

case 50:

navigateUrl($('a[id$=linkB]'));

break;

case 51:

navigateUrl($('a[id$=linkC]'));

break;

default: ;

}

});

function navigateUrl(jObj) {

window.location.href = $(jObj).attr("href");

alert("Navigating to " + $(jObj).attr("href"));

}

});

script>

head>

<body>

<h2>Use Keyboard Keys 1, 2 or 3 to invoke respective

websitesh2><br />

<asp:HyperLink ID="linkA" runat="server"

NavigateUrl="http://www.dotnetcurry.com">

DotNetCurryasp:HyperLink><br /><br />

<asp:HyperLink ID="linkB" runat="server"

NavigateUrl="http://www.sqlservercurry.com">

SqlServerCurryasp:HyperLink><br /><br />

<asp:HyperLink ID="linkC" runat="server"

NavigateUrl="http://www.devcurry.com">

DevCurryasp:HyperLink><br /><br />

div>

form>

body>

html>

Implementing a Keyboard shortcut in jQuery is relatively simple as shown here. The code first captures the keyup event and the key is detected using the keyCode or charCode.

$(document).keyup(function(e) {

var key = (e.keyCode ? e.keyCode : e.charCode);

In the code shown below, if the key = 49, digit 1 is pressed by the user and the first Hyperlink is auto-clicked. Similarly if the key = 50 or 51, then digit 2 or 3 are pressed respectively and the 2^nd or 3^rd hyperlink is autoclicked. Once the key matches any of the switch case statement, the function navigateUrl() is called passing in the respective hyperlink control object to the function. So if the user pressed 1, the first hyperlink control object is passed to the function as shown below:

switch (key) {

case 49:

navigateUrl($('a[id$=linkA]'));

break;

The navigateUrl function looks like this:

function navigateUrl(jObj) {

window.location.href = $(jObj).attr("href");

}

The function accepts the hyperlink object and sets the ‘window.location.href’ to the href attribute of the Hyperlink passed in. This is how we invoke actions with shortcut keys.

Tip: keyCode represents the actual key pressed as a numerical value, whereas charCode gives the ASCII/Unicode value of the key press result (for eg: Shift + A). Firefox and other browsers also support ‘e.which’. IE and Opera does not support charCode.

Keyboard shortcuts improve productivity by accomplishing tasks more quickly and without much effort. In applications, where the user has to select from a variety of actions to perform, keyboard shortcuts can save on time and effort.

You can see a Live Demo over here. The entire source code of this article can be downloaded over here

Reference : http://www.dotnetcurry.com/

Prevent Cut, Copy and Paste Operations in an ASP.NET TextBox using jQuery

2009-10-13T23:32:00.000-07:00

Prevent Cut, Copy and Paste Operations in an ASP.NET TextBox using jQuery

In this short article, I will demonstrate how to prevent users from doing Cut, Copy and Paste operations in an ASP.NET TextBox using jQuery. This article is a sample chapter from my upcoming EBook called 51 Tips, Tricks and Recipes with jQuery and ASP.NET Controls. The chapter content has been modified to publish it as an article. Also please note that for demonstration purposes, I have included JavaScript into the same file. Ideally, these resources should be created in separate folders for maintainability.

The event handling approach in jQuery begins by attaching a handler to an event. The jQuery bind() event method does exactly the same and binds one or more events to a handler. The signature of the bind() method is as follows:

bind(eventType,data,handler)

The parameters of the bind() method are as follows:

eventType is a string holding a JavaScript event type such as click, focus, keyup etc.

data is some optional data you want to pass to the handler

handler is the event handler function which will execute when the event is triggered

Let us quickly jump to the solution and see how we can use the bind() event method to write minimal code to prevent cut, copy and paste operations on the textbox.

<title>Prevent Cut, Copy and Paste Operations in a TextBoxtitle>

src='http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js'>

script>

$(function() {

$('input[id$=tb1]').bind('cut copy paste', function(e) {

e.preventDefault();

alert('You cannot ' + e.type + ' text!');

});

script>

head>

<body>

<h2>Prevent Cut, Copy and Paste Operations in a TextBoxh2>

<asp:TextBox ID="tb1" runat="server"

Text="Text which cannot be copied/cut/pasted"

ToolTip="Try Copy/Cut/Paste in textbox"/>

div>

form>

body>

html>

Observe how convenient it is to use jQuery to list multiple events (cut, copy, paste) together and bind it to a handler as shown below.

$('input[id$=tb1]').bind('cut copy paste', function(e) {});

If the user performs any of these events on the textbox (tb1), the default behavior is prevented using e.preventDefault() and the user is alerted with a message. The e.type describes the type of event performed.

$('input[id$=tb1]').bind('cut copy paste', function(e) {

e.preventDefault();

alert('You cannot ' + e.type + ' text!');

});

This piece of ‘minimal’ code handles a typical requirement where you are asked to ‘confirm’ an email address and want the user to type it manually, instead of copying and pasting it.

Note: Text can be edited in this textbox, but cut/copy/paste actions are prevented

This demo has been tested on IE7, IE8, Firefox 3, Chrome 2 and Safari 4.

Limit Number Of Characters In an ASP.NET Multiline TextBox using jQuery

2009-10-08T22:12:00.001-07:00

Limit Number Of Characters In an ASP.NET Multiline TextBox using jQuery

This article shows you how to limit characters entered in an ASP.NET Multiline TextBox. The ASP.NET Multiline TextBox ignores the MaxLength property. So one of the ways of solving this requirement is to use Client Script and detect if the maximum character limit of the Multiline TextBox has been reached. I will be showing a common technique adopted by developers to solve this requirement. I will then explain why this technique is not user friendly and how to improve on it.

This article is a sample chapter from my upcoming EBook called 51 Tips, Tricks and Recipes with jQuery and ASP.NET Controls. The chapter content has been modified to publish it as an article. Also please note that for demonstration purposes, I have included both the JavaScript and CSS into the same file. Ideally, these resources should be created in seperate folders for maintainability.

A common technique adopted by developers is to capture the textbox keyup event and calculate the number of characters in the textbox, as the user types in it. If the character exceeds the limit of the textbox (in our case 50 characters), the additional characters entered by the user is disallowed. The code is as shown below:

<title>Limit Characters in a Multiline TextBoxtitle>

src='http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js'>

script>

$(function() {

var limit = 50;

$('textarea[id$=tb1]').keyup(function() {

var len = $(this).val().length;

if (len > limit) {

this.value = this.value.substring(0, limit);

}

$('#spn).text(limit - len + " characters left");

});

script>

head>

<body>

<div>

<asp:TextBox ID="tb1" runat="server" TextMode="MultiLine"/><br />

div>

form>

body>

html>

Observe how we are selecting the control using textarea in the code shown above $('textarea[id$=tb1]'). This is as the ASP.NET Multiline TextBox renders as a textarea.

Although the solution given above restricts the user from entering more than 50 characters, this behavior can be confusing for users who may not realize that they have reached the TextBox limit. Instead of disallowing extra characters, a slick way of handling this requirement would be to visually indicate to the user when the textbox limit has been exceeded. Then before submitting the form, give the user a chance to remove the extra text. The code shown below changes the background color of the textbox to red when the textbox limit is exceeded. The user is also prevented from submitting the form.

<title>Limit Characters in a Multiline TextBoxtitle>

src='http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js'>

script>

.exceeded{

background-color:red;

}

style>

$(function() {

var limit = 50;

var tb = $('textarea[id$=tb1]');

$(tb).keyup(function() {

var len = $(this).val().length;

if (len > limit) {

//this.value = this.value.substring(0, 50);

$(this).addClass('exceeded');

$('#spn').text(len - limit + " characters exceeded");

}

else {

$(this).removeClass('exceeded');

$('#spn').text(limit - len + " characters left");

}

});

$('input[id$=btnSubmit]').click(function(e) {

var len = $(tb).val().length;

if (len > limit) {

e.preventDefault();

}

});

script>

head>

<body>

<h2>Type into this textbox which accepts 50 characters overallh2>

<asp:TextBox ID="tb1" runat="server" TextMode="MultiLine"/><br />

(This textbox accepts only 50 characters) <br />

<asp:Button ID="btnSubmit" runat="server" Text="Submit"/>

Tip: Clicking on the Submit button when number of characters are

less than 50, results in a postback to same page. If you exceed 50

characters, the exceeded characters are printed below the textbox

and a postback is prevented.

div>

form>

body>

html>

When the number of characters exceeds the textbox limit, we add the exceeded class to the textbox, which turns the textbox background to red, indicating the user that the limit has been exceeded. The result is shown here:

When the user tries and submits the form now, the length of the TextBox is calculated and the user is prevented from submitting the form, since the textbox exceeds the permissible length.

When the user removes the extra text, the exceeded class is removed. The result is shown here:

The form can now be submitted. You can see a Live Demo here . This demo has been tested on IE7, IE8, Firefox 3, Chrome 2 and Safari 4.

Using jQuery To Create Stunning Tooltips in your ASP.NET Applications

2009-10-06T22:09:00.000-07:00

Using jQuery To Create Stunning Tooltips in your ASP.NET Applications

“jQuery Tools is a collection of the most important user interface components for the web. These are tabs and accordions, tooltips, overlays, exposing effects and scrollables. They can dramatically improve the usability and responsiveness of your site. They mainly focus on presenting information and visual appeal. After all, this is exactly what most websites desperately want: to present their content to the reader in an easy and visually pleasing manner.”

I’ll be focusing some future articles on these tools, but this article will concentrate on how to create stunning tooltips using this plugin. The end result in this article will show you how to create these tooltips using dynamic data by using jQuery’s Ajax functionality. The end result will look something like this:

Before we get started, this example uses the latest version of jQuery which is 1.3.2. That can be downloaded from here. The jQuery Tools and can be downloaded from here.

The jQuery Tools plugin has quite a few options when it comes time to configuration, but the one you should not forget is the tip attribute. This jQuery selector selects the tooltip element being used. The attribute that will allow you to position the tooltip is funnily enough called position. The position property specifies the position in relation to the trigger element. For example, a value of 'bottom center' will place the tooltip on the bottom edge of the trigger, centred horizontally. Other values are:

top center, top right, center right, bottom right, bottom left, center left and top left

To make this real, instead of showing this tooltip against static data such as images, I’m going to use jQuery’s Ajax functionality to query the server for a list of running processes. Okay let’s gets started. Open Visual Studio 2008 and create a new Web Application. First off add a new class to the project and call it ProcessInfo. This class will hold information about the process. Add the following code to the class:

public class ProcessInfo

{

public string ProcessName { get; set; }

public string MainWindowTitle { get; set; }

public long PagedMemorySize64 { get; set; }

}

VB.NET

Public Class ProcessInfo

Private privateProcessName As String

Public Property ProcessName() As String

Get

Return privateProcessName

End Get

Set(ByVal value As String)

privateProcessName = value

End Set

End Property

Private privateMainWindowTitle As String

Public Property MainWindowTitle() As String

Get

Return privateMainWindowTitle

End Get

Set(ByVal value As String)

privateMainWindowTitle = value

End Set

End Property

Private privatePagedMemorySize64 As Long

Public Property PagedMemorySize64() As Long

Get

Return privatePagedMemorySize64

End Get

Set(ByVal value As Long)

privatePagedMemorySize64 = value

End Set

End Property

End Class

The next step is to open the Default.aspx.cs file and add the following code:

[WebMethod]

public static List<ProcessInfo> GetRunningProcesses()

{

var query = (from p in System.Diagnostics.Process.GetProcesses()

select new ProcessInfo

{

ProcessName = p.ProcessName,

MainWindowTitle = p.MainWindowTitle,

PagedMemorySize64 = p.PagedMemorySize64

}).ToList();

return query;

}

VB.NET

Public Shared Function GetRunningProcesses() As List(Of ProcessInfo)

Dim query = ( _

From p In System.Diagnostics.Process.GetProcesses() _

Select New ProcessInfo With {.ProcessName = p.ProcessName, .MainWindowTitle = p.MainWindowTitle, .PagedMemorySize64 = p.PagedMemorySize64}).ToList()

Return query

End Function

The code above will be called by jQuery’s Ajax function. It returns a list of running process on the machine thanks to the Process object. The Process class provides access to local and remote processes. The LINQ query will return a generic list of ProcessInfo objects that will be used to create the tooltips. The next step is to add the JavaScript. Open the Default.aspx page and add the following jQuery code to the section of the page. Ideally JavaScript should go to a separate .js file, however for this example; I’ll keep it in the same page:

function showToolTip() {

$("#results span[title]").tooltip({

position: "center right",

opacity: 0.7,

tip: "#demotip",

effect: "fade"

});

}

$(document).ready(function() {

$("#btnSearch").click(function() {

$.ajax({

type: "POST",

url: "Default.aspx/GetRunningProcesses",

data: "{}",

contentType: "application/json; charset=utf-8",

dataType: "json",

async: true,

success: function(msg) {

var processInfo = msg.d;

$("#results").text("");

for (var i = 0; i < processInfo.length; i++) {

// create the title for each item

var title = "Process Name: " +

processInfo[i].ProcessName +

"<BR>" +

"Paged Memory Size64: " +

processInfo[i].PagedMemorySize64;

$("#results").append("">" +

processInfo[i].ProcessName +

");

}

});

script>

In the code above I am binding to the input button’s click event. When the user clicks the button, it will execute the WebMethod GetRunningProcesses. A generic list containing the ProcessInfo data will be returned in the msg.d argument. For each process returned, a span tag will be appended in the div element. When the user hover’s their mouse over the tag, the JavaScript function showToolTip will run. This function is responsible for displaying the tooltip:

function showToolTip() {

$("#results span[title]").tooltip({

position: "center right",

opacity: 0.7,

tip: "#demotip",

effect: "fade"

});

}

Using jQuery’s selectors the tooltip will find the related span tag to trigger the tooltip. The tool looks for the element that is placed next to the tooltip to be the tooltip. The return value is a jQuery object associated with the selection. The result is a stunning tooltip as the screen shot below shows:

The information in the tooltip can be configured as it’s just HTML. So if you want you can display a nice image to the user so they’ll be able to associate the process by the icon. The background image of the tooltip is configurable too. The styling for the tooltip is below:

#demotip {

display:none;

background:transparent url(http://flowplayer.org/tools/img/tooltip/black_arrow.png);

font-size:12px;

height:70px;

width:160px;

padding:25px;

color:#fff;

}

This is a nice addition to your jQuery ensemble. The entire source code of this article can be downloaded over here

Error Logging using ASP.NET 2.0

2009-10-02T22:13:00.000-07:00

Error Logging using ASP.NET 2.0

This article has been republished with a few minor changes.

Errors and failures may occur during development and operation of a website. ASP.NET 2.0 provides tracing, instrumentation and error handling mechanisms to detect and fix issues in an application.

In this article, we will adopt a simple mechanism to log errors and exceptions in our website. We will be using a mechanism where the user will be redirected to a separate page whenever an error is encountered in the application. Simultaneously, the error will get logged in a text file on the server. The error file will be created on a daily basis, whenever the error is encountered. Having said that, let us now see some code.

Step 1: Start by creating an Error folder where all errors will be logged. Right click the website > New Folder. Rename the folder to “Error”. Also add a web.config file, if one does not already exist in your site. Right click the website > Add New Item > Web.config.

Step 2: Now we will create the error handler code. To do so, right click your website > Add New Item > select Class. Rename the class to ‘ErrHandler.cs’ and click on ‘Add’. When you do so, you will be prompted with a message to place the class in ‘App_Code’ folder. Accept the message to place the class in the 'App_Code' folder.

Step 3: Now let us add functionality to the ErrHandler class. This class will accept the error message and write the message in a text file. One text file will be created for each day. If the text file already exists, the message will be appended to the text file. If not, a new text file will be created based on today’s date and error message will be written in it.

The code will look similar to the following:

/// Handles error by accepting the error message

/// Displays the page on which the error occured

public static void WriteError(string errorMessage)

{

try

{

string path = "~/Error/" + DateTime.Today.ToString("dd-mm-yy") + ".txt";

if (!File.Exists(System.Web.HttpContext.Current.Server.MapPath(path)))

{

File.Create(System.Web.HttpContext.Current.Server.MapPath(path)).Close();

}

using (StreamWriter w = File.AppendText(System.Web.HttpContext.Current.Server.MapPath(path)))

{

w.WriteLine("\r\nLog Entry : ");

w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture));

string err = "Error in: " + System.Web.HttpContext.Current.Request.Url.ToString() +

". Error Message:" + errorMessage;

w.WriteLine(err);

w.WriteLine("__________________________");

w.Flush();

w.Close();

}

catch (Exception ex)

{

WriteError(ex.Message);

}

VB.NET

''' Handles error by accepting the error message

''' Displays the page on which the error occured

Public Shared Sub WriteError(ByVal errorMessage As String)

Try

Dim path As String = "~/Error/" & DateTime.Today.ToString("dd-mm-yy") & ".txt"

If (Not File.Exists(System.Web.HttpContext.Current.Server.MapPath(path))) Then

File.Create(System.Web.HttpContext.Current.Server.MapPath(path)).Close()

End If

Using w As StreamWriter = File.AppendText(System.Web.HttpContext.Current.Server.MapPath(path))

w.WriteLine(Constants.vbCrLf & "Log Entry : ")

w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture))

Dim err As String = "Error in: " & System.Web.HttpContext.Current.Request.Url.ToString() & ". Error Message:" & errorMessage

w.WriteLine(err)

w.WriteLine("__________________________")

w.Flush()

w.Close()

End Using

Catch ex As Exception

WriteError(ex.Message)

End Try

End Sub

That was our ErrHandler class. We will now see how to use this Error Handler class and handle errors at the page level as well as at the application level.

Handling errors at Page Level

In the Default.aspx, drag and drop a button from the toolbox. Rename this button to btnError and set the Text as ‘Throw Handled Exception’. Here we will throw an exception. Since we have a catch block defined, the exception will be caught and the error will be logged in the Error folder. Since a text file with today’s date, does not exists, a new text file will be created by the code.

The button click handler will look similar to the following:

protected void btnHandled_Click(object sender, EventArgs e)

{

try

{

throw new Exception("Sample Exception");

}

catch (Exception ex)

{

// Log the error to a text file in the Error folder

ErrHandler.WriteError(ex.Message);

}

VB.NET

Protected Sub btnHandled_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnHandled.Click

Try

Throw New Exception()

Catch ex As Exception

' Log the error to a text file in the Error folder

ErrHandler.WriteError(ex.Message)

End Try

End Sub

Now with the code in place, run the application and click on the button. Since we have handled the error and logged the exception in our code, you will not notice anything when the button is clicked. However, close the application and refresh the Error folder. You will see a new text file created with today’s date. The exception has been logged successfully as shown below. The date and time will differ on your machine.

Log Entry :

01/11/2008 23:33:46

Error in: http://localhost:51087/ErrorHandling/Default.aspx. Error Message:Sample Exception

__________________________

Redirecting users on unhandled errors

Let us see how to catch unhandled errors and redirect the user to a different page, whenever such an unhandled error occurs at the application level.

To catch unhandled errors, do the following. Add a Global.asax file (Right click project > Add New Item > Global.asax). In the Application_Error() method, add the following code:

void Application_Error(object sender, EventArgs e)

{

// Code that runs when an unhandled error occurs

Exception objErr = Server.GetLastError().GetBaseException();

string err = "Error in: " + Request.Url.ToString() +

". Error Message:" + objErr.Message.ToString();

// Log the error

ErrHandler.WriteError(err);

}

VB.NET

Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)

' Code that runs when an unhandled error occurs

Dim objErr As Exception = Server.GetLastError().GetBaseException()

Dim err As String = "Error in: " & Request.Url.ToString() & ". Error Message:" & objErr.Message.ToString()

' Log the error

ErrHandler.WriteError(err)

End Sub

We capture the error using the Server.GetLastError(). Now to redirect users to a different page whenever an unhandled error occurs, open your web.config file and locate the tag and uncomment it. After removing the comment, the tag will look similar to the following code:

<customErrorsmode="RemoteOnly"defaultRedirect="GenericErrorPage.htm">

<errorstatusCode="403"redirect="NoAccess.htm" />

<errorstatusCode="404"redirect="FileNotFound.htm" />

customErrors>

Now change:

mode="RemoteOnly"tomode="On"

defaultRedirect="GenericErrorPage.htm" to defaultRedirect="ErrorPage.aspx"

The modified code will now look like this:

<customErrorsmode="On"defaultRedirect="ErrorPage.aspx">

<errorstatusCode="403"redirect="NoAccess.htm" />

<errorstatusCode="404"redirect="FileNotFound.htm" />

customErrors>

This configuration will now redirect the user to an Error page when an error occurs. Let us create this error page and display some message to the user.

Right Click Project > Add New Item> Create a new ErrorPage.aspx page in the application and display a sample message on the page informing the user that an error has occurred.

To test our functionality, go back to Default.aspx, add another button and rename it to btnUnhandled and set its Text property to ‘Throw Unhandled Exception’. Here instead of throwing the exception as we did for ‘btn_Error’, we will introduce a ‘Divide By Zero’ exception and not handle it. Observe that there is no try catch block as shown below. So when the error occurs, the user will be redirected to the ‘ErrorPage.aspx’ as a result of the changes made in our web.config file.

protected void btnHandled_Click(object sender, EventArgs e)

{

try

{

throw new Exception("Sample Exception");

}

catch (Exception ex)

{

// Log the error to a text file in the Error folder

ErrHandler.WriteError(ex.Message);

}

VB.NET

Protected Sub btnUnhandled_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUnhandled.Click

Dim i As Integer = 1

Dim j As Integer = 0

Response.Write(i \ j)

End Sub

Run the application and click on the ‘Throw Unhandled Exception’ button. You will observe that the user will be automatically redirected to the Error Page and the error will be logged in the Error folder. Well that’s it.

In this article, we saw how to implement a simple error logging system in our application. Logging errors can be very useful and helps us detect errors during development and operation of a website. ASP.NET also provides some advanced options titled under ‘Health Monitoring’ where the errors can be stored in Sql Server or even emailed to the administrator based on the criticality of it.

I hope this article was useful and I thank you for viewing it. Download the source code of the application over here.

Dynamically Generate META tags in ASP.NET pages with LINQ to SQL

2009-10-02T21:58:00.001-07:00

Dynamically Generate META tags in ASP.NET pages with LINQ to SQL

Earlier this year I wrote an article on how to Dynamically Generate Meta Tags for ASP.NET Pages using LINQ To XML. I received allot of feedback from readers on that article, and some people pointed out this information should be coming from a database. Well I thought it was time to address this by writing an article on how to generate this information from a database.

The first step is creating the database. I have created a database called MetaTags and it contains two tables. One called Pages and the other called MetaData. The script to create the database (MetaTags.sql) can be downloaded along with the source code (link given at the end) of this article, but the tables will end up looking like this:

The Page table will store the name of the page, and the MetaData will store Meta information on the page. This is a one to many relationship as one page can have multiple Meta tags.

The next step in to create a new web application project. Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application. I’m going to use LINQ to SQL in this example as I think it’s a great piece of technology. Right click on the project and choose Add > New Item > LINQ to SQL Classes. Name the new file MetaTags. Once the file is created, connect to your SQL Server and drag and drop the two tables created in the previous step. Your file should look like this:

Save and build your project. Now that’s done the last thing to do is add a master page to the project. Right click the project and choose Add > New Item > Master Page. When the page is created, open up the code behind file and add the following code:

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

FetchMetaData();

}

private void FetchMetaData()

{

using (var dc = new MetaDataDataContext())

{

var query = from page in dc.Pages

join meta in dc.MetaDatas on page.PageId equals meta.PageId

where page.PageName.ToLower() == Request.Url.AbsolutePath.ToLower()

select new

{

Key = meta.KeyName,

meta.Value

};

foreach (var item in query)

{

HtmlMeta meta = new HtmlMeta();

meta.Name = item.Key;

meta.Content = item.Value;

Page.Header.Controls.Add(meta);

}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

If (Not IsPostBack) Then

FetchMetaData()

End If

End Sub

Private Sub FetchMetaData()

Using dc = New MetaDataDataContext()

Dim query = From page In dc.Pages _

Join meta In dc.MetaDatas On page.PageId Equals meta.PageId _

Where page.PageName.ToLower() = Request.Url.AbsolutePath.ToLower() _

Select New

Key = meta.KeyName, meta.Value

For Each item In query

Dim meta As New HtmlMeta()

meta.Name = item.Key

meta.Content = item.Value

Page.Header.Controls.Add(meta)

Next item

End Using

End Sub

In the code above, the code will run on each page request. It executes the LINQ query which returns Meta information for the requesting page:

var query = from page in dc.Pages

join meta in dc.MetaDatas on page.PageId equals meta.PageId

where page.PageName.ToLower() == Request.Url.AbsolutePath.ToLower()

select new

{

Key = meta.KeyName,

meta.Value

};

VB.NET

Dim query = From page In dc.Pages _

Join meta In dc.MetaDatas On page.PageId Equals meta.PageId _

Where page.PageName.ToLower() = Request.Url.AbsolutePath.ToLower() _

Select New

Key = meta.KeyName, meta.Value

If records are found, the foreach loop will enumerate through each record and create a new HtmlMeta object. This object allows programmatic access to the tag on the server:

foreach (var item in query)

{

HtmlMeta meta = new HtmlMeta();

meta.Name = item.Key;

meta.Content = item.Value;

Page.Header.Controls.Add(meta);

}

VB.NET

For Each item In query

Dim meta As New HtmlMeta()

meta.Name = item.Key

meta.Content = item.Value

Page.Header.Controls.Add(meta)

Next item

That’s it. When you run the application you’ll see the HTML Meta tags have been added to the page:

One thing I will point out is that I have used Request.Url.AbsolutePath in my LINQ query. This property will return the absolute path of the requesting page, so for example if the requesting page is coming from the Default.aspx page in the root directory it will appear as this:

/Default.aspx

I chose to leave this instead of querying for the exact page name because for the simple fact that if you have a sub folder, there’s a chance you could have a page that is the same name as another page in a different folder. So if you have a folder called Admin and you want to add Meta tags to it, you’ll need to add the absolute path into the database:

/Admin/Default.aspx

This way you’ll always fetch the correct Meta tags for the requested page. I hope you have found this article of interest. Meta tags are a great way to describe your website to the outside world, and this way you can dynamically add this data with LINQ. Yet another good use for LINQ! The entire source code of this article can be downloaded from here