Thursday, December 29, 2011

Deprecated HTML elements /Bad Tag

There are several HTML elements and attributes that have now been declared deprecated by the World Wide Web Consortium (the organization that sets HTML standards).

'Deprecated' means that the elements no longer serve a purpose and have been replaced by other methods, mostly involving cascading stylesheets (CSS). Although it is recommended that web browsers continue to support them, eventually they will become obsolete.

Here lists all the deprecated elements and attributes of HTML 4..

  1.  The FONT and BASEFONT tags
  2.  The CENTER tag and ALIGN attribute
  3. The U, S and STRIKE elements
  4. The BACKGROUND and BGCOLOR attribute
  5. The BORDER attribute
  6. The TEXT, LINK, ALINK and VLINK attributes
  7. The HPSACE and VSPACE attributes
  8. The LANGUAGE attribute
  9. The CLEAR attribute
  10. The WIDTH and HEIGHT attributes
  11. The TYPE attribute for lists
  12. The START and VALUE attributes for lists
  13. The COMPACT attribute for lists
  14. The APPLET element
  15. The DIR and MENU elements
  16. The ISINDEX element
  17. The NOSHADE and SIZE attributes for HR
  18. The NOWRAP attribute
  19. The VERSION attribute
For more details....Deprecated HTML element/ Bad Tag

String Formatting in C#


Basic number formatting specifiers:
Specifier Type Format Output (Passed Double 1.42) Output (Passed Int -12400)
c Currency {0:c} $1.42 -$12,400
d Decimal (Whole number) {0:d} System.FormatException -12400
e Scientific {0:e} 1.420000e+000 -1.240000e+004
f Fixed point {0:f} 1.42 -12400.00
g General {0:g} 1.42 -12400
n Number with commas for thousands {0:n} 1.42 -12,400
r Round trippable {0:r} 1.42 System.FormatException
x Hexadecimal {0:x4} System.FormatException cf90

Custom number formatting:
Specifier Type Example Output (Passed Double 1500.42) Note
0 Zero placeholder {0:00.0000} 1500.4200 Pads with zeroes.
# Digit placeholder {0:(#).##} (1500).42
. Decimal point {0:0.0} 1500.4
, Thousand separator {0:0,0} 1,500 Must be between two zeroes.
,. Number scaling {0:0,.} 2 Comma adjacent to Period scales by 1000.
% Percent {0:0%} 150042% Multiplies by 100, adds % sign.
e Exponent placeholder {0:00e+0} 15e+2 Many exponent formats available.
; Group separator see below

The group separator is especially useful for formatting currency values which require that negative values be enclosed in parentheses.

This table will help you to format the string value to double value:

Value           Format String              Result
1234.567     {0:0.00}                      1234.57
1234.567     {0:00000.0000}          01234.5670
1234.567     {0:#####.##}             1234.57
1234.567     {0:#.###}                   1234.567
1234.567     {0:#.#}                       1234.6
1234.567     {0:#,#.##}                  1,234.57
1234.567     {0:$#,#.##}                $1,234.57
1234.567     {0:$ #,#.##}               $ 1,234.57
1234.567     {0:($ #,#.##)}             ($ 1,234.57)
-1234.567    {0:#,#.##}                 -1,234.57
.1234           {0:#%}                       12%
.1234           {0:Percent = #.0%}     Percent = 12.3%

double dValue=1234.567;
string sCurrency =  dValue.ToString(" {0:0.00}",dValue);
Output: 1234.57

double dValue=23.00;
String.Format("{0:$#,##0.00;($#,##0.00);Zero}",dValue );
This will output “$1,240.00″ if passed 1243.50. It will output the same format but in parentheses if the number is negative, and will output the string “Zero” if the number is zero.

Disable Browser Back Button

It is a common question for developer how to to prevent user’s from going to previous page using Browser Back button.
You can do it in various way:

Javascript Codesnippet1:

function disableBackButton()
setTimeout("disableBackButton()", 0);

For internet explorer: onload="disableBackButton()">
If you are using firefox then use onunload="disableBackButton()"> instead of onload.

For Server side:
C#-> 2.0

ASP.NET 2.0 (there is a new HttpCachePolicy class) :

    * Code disables caching by browser. Hence hitting the back browser button
    * causes the Page_Load event to fire again.
Response.Cache.SetExpires(DateTime.Now); //or a date much earlier than current time

“Razor” – a new view engine for ASP.NET

Razor” – a new view engine for ASP.NET developed by Microsoft.

The new view-engine option have been working on is optimized around HTML generation using a code-focused templating approach. The codename for this new view engine is “Razor”,

Microsoft had a number of design goals when building and testing Razor, attempting to create a compact, expressive, and fluid ASP.NET view engine.

In addition, the Redmond company worked to ensure that customers would be able to leverage Razor immediately, by using their existing language and HTML skills.

Design Goals for "Razor"

1.Compact, Expressive, and Fluid: Razor minimizes the number of characters and keystrokes required in a file, and enables a fast, fluid coding workflow. Unlike most template syntaxes, you do not need to interrupt your coding to explicitly denote server blocks within your HTML. The parser is smart enough to infer this from your code. This enables a really compact and expressive syntax which is clean, fast and fun to type.

2.Easy to Learn: Razor is easy to learn and enables you to quickly be productive with a minimum of concepts. You use all your existing language and HTML skills.

3.Is not a new language: We consciously chose not to create a new imperative language with Razor. Instead we wanted to enable developers to use their existing C#/VB (or other) language skills with Razor, and deliver a template markup syntax that enables an awesome HTML construction workflow with your language of choice.

4.Works with any Text Editor: Razor doesn’t require a specific tool and enables you to be productive in any plain old text editor (notepad works great).

5.Has great Intellisense: While Razor has been designed to not require a specific tool or code editor, it will have awesome statement completion support within Visual Studio. We’ll be updating Visual Studio 2010 and Visual Web Developer 2010 to have full editor intellisense for it.

6. Unit Testable: The new view engine implementation will support the ability to unit test views (without requiring a controller or web-server, and can be hosted in any unit test project – no special app-domain required).

For more details please visits

How to get visitor's Country,longitude,latitude from IP address is

It is very common questions for new web developer how to get the client's country location to track the user.In various forum I

have faced this questions. So that today I want to share how to get client's country location,longitude,latitude,country code etc.
To get this you need to use a

free database which has IP to Location mapping
call a web service that does the same for you.

Using free web services is the easy way to get the location of the user based on its IP address. After goggling I have found the

following web service which provide this service absolutely free and that too without any complex interface to do the same.

The above website provides free IP Geolocation Web Service that returns data in three formats .

1. XML [Extended Markup Language]
2. CSV [Comma Separated Values]
3. JSON [JavaScript Object Notation]

Here I am explaining how to get the data in XML format.
It is very easy to use this web service,just send your ip address through the URL

The returned XML with result are as below:


How to consume this web service and get the result:
Here WebRequest and WebProxy is responsible for make call this url and xml response is received by WebResponse and store it to dataset using XMLTextReader.

private DataTable GetGeoLocation(string ipaddress)
//Create a WebRequest
WebRequest rssReq = WebRequest.Create(""
+ ipaddress);
//Create a Proxy
WebProxy px =new WebProxy("" + ipaddress, true);

//Assign the proxy to the WebRequest
rssReq.Proxy = px;

//Set the timeout in Seconds for the WebRequest
rssReq.Timeout = 2000;
{ //Get the WebResponse
WebResponse rep = rssReq.GetResponse();

//Read the Response in a XMLTextReader
XmlTextReader xtr = new XmlTextReader(rep.GetResponseStream());

//Create a new DataSet
DataSet ds = new DataSet();

//Read the Response into the DataSet
return ds.Tables[0];
return null;

How to show result in aspx page:
You can place this code on Page_Load event or under any button event whenever you like:
Protected void ShowGeoLocatio()
string sIpaddress;
sIpaddress= Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if(sIpaddress== "" || sIpaddress== null)
sIpaddress= Request.ServerVariables["REMOTE_ADDR"];

//call the function to consume web service and store result into datatable
DataTable odtGeoLocation = GetGeoLocation(sIpaddress);
if (odtGeoLocation != null)
if (odtGeoLocation .Rows.Count > 0)
lblCity.Text = odtGeoLocation .Rows[0]["City"].ToString();
lblRegion.Text = odtGeoLocation .Rows[0]["RegionName"].ToString();
lblCountry.Text = odtGeoLocation .Rows[0]["CountryName"].ToString();
lblCountryCode.Text = odtGeoLocation .Rows[0]["CountryCode"].ToString();
lblError="Sorry,no data found!!";

What is HTTP Handler and HTTP Module in Co-Relation and Differences

I have little bit confusion about HTTP Handler and HTTP Module. Then I move to community and get a huge response. After studying for a while now I get a clear idea. Now sharing with you hope that it will also help other guys.

What is HTTP handler?

An ASP.NET HTTP handler is the process (frequently referred to as the "endpoint") that runs in response to a request made to an ASP.NET Web application. The most common handler is an ASP.NET page handler that processes .aspx files. When users request an .aspx file, the request is processed by the page through the page handler. You can create your own HTTP handlers that render custom output to the browser.(msdn)

HTTP handlers process the request and are generally responsible for initiating necessary business logic tied to the request. Custom handlers must implement the System.Web.IHttpHandler interface. Additionally, a handler factory can be created which will analyze a request to determine what HTTP handler is appropriate. Custom handler factories implement the System.Web.IHttpHandlerFactory interface. execution process(Image source:MSDN)

What is HTTP Module?

An HTTP module is an assembly that is called on every request that is made to your application. HTTP modules are called as part of the ASP.NET request pipeline and have access to life-cycle events throughout the request. HTTP modules let you examine incoming and outgoing requests and take action based on the request.(msdn)

HTTP modules are executed before and after the handler and provide a method for interacting with the request.
Custom modules must implement the System.Web.IHttpModule interface. Modules are typically synchronized with events of the System.Web.IHttpModule class (implemented within the Global.asax.cs or .vb file).

The following consists of a list of events that should be considered when implementing your module:


The co-relation between HTTP handler and HTTP module is both are an integral part of the ASP.NET application.
Every request flows through a number of HTTP modules, which cover various areas of the application (i.e. authentication and session information). After passing through each module, the request is assigned to a single HTTP handler, which determines how the system will respond to the request. Upon completion of the request handler, the response flows back through the HTTP modules to the user.


HTTP Module
1.It represents more something like an modulare peace of code that's similar to another Global.asax.
2.It represent code that is in play for all page requests.
3.These are objects which also participate the pipeline.

HTTP Handler
1.Its a handler for one request.
2.It is more like a single page.
3.These are the end point objects in ASP.NET pipeline.
4.These are essentially processes the request and produces the response

SQL SERVER – Simple Example of Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at .
Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.
USE AdventureWorks
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.
Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result).  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.
This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.

Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0

Tutorial 1: Creating a Web Project and Connecting To the Database

To begin with, create an empty new ASP.NET Web Site Project (File->New Web Site) within Visual Web Developer.  We can then connect and load our Northwinds database in the IDE.  To-do this you can follow one of two approaches:

Option 1: Connect to a local or remote SQL 2000 or SQL 2005 database that has the Northwinds sample installed.  To-do this, go to the “Server Explorer” property window in Visual Web Developer (choose View->Server Explorer if it isn’t currently visible), right-click on the “Data Connections” node and choose to add a new database connection.  You can then walkthrough a wizard to pick your SQL database and load the Northwinds database in the IDE:

Option 2: If you don’t have a SQL 2000 or SQL 2005, you can alternatively use the free SQL 2005 Express Edition.  Download this .zip file containing the Northwinds.mdf database file, and copy it to the “App_Data” directory underneath your web site’s root directory.  Click the “Refresh” button in the solution explorer, and it will appear in the Solution Explorer:

SQL Express databases within the App_Data directory will automatically show-up as a listed item in the Server Explorer property window (note: I sometimes get a timeout error the very first time I expand a newly copied sql express database here – I think because it is generating the log file the first time it is accessed.  If this happens just click it again and it seems to immediately expands):

After the Northwinds database has been loaded using either Option #1 or Option #2 above, you can expand, view, edit, and add to any of the tables, stored procedures, views, triggers, etc with the database.  You can also use the query builder to test out and run queries against the database (or right-click on a table and choose “Show Table Data” to see all of it).

Tutorial 2: Launching the DataSet Designer

To create our Northwind DAL, right-click on the project node in the solution explorer, and select “Add New Item”.  Then select the “DataSet” option and name it “Northwind.xsd”:

This will prompt me as to whether I want to add it under the “App_Code” directory.  When I click “yes” it will bring up a data-design surface, and (if in a web project) automatically launch the “Create TableAdapter” wizard (if you are in a class library project you need to right click and choose “Add->Table Adapter to launch this wizard):

Tutorial 3: Creating our First Table Adapter

The “Create TableAdapter” wizard will first prompt me for the database to use, and provide a list of all database connections currently registered in Visual Web Developer’s server-explorer window (note: you can also create new connections from this wizard if you want). 

After I choose the Northwind database I want to use, it will prompt me for where to store the database connection-string.  By default it will avoid hard-coding it within your code, and will instead save it within the new section of your web.config file (or app.config file if you are in a Class Library Project).  Note that with .NET 2.0 you can now optionally encrypt configuration file values to keep them secure, as well as use the new ASP.NET 2.0 Property Page within the IIS GUI Admin Tool to change it later (ideal for administrators).  Pick a name for what you want to call it:

You can then choose how you want to configure the TableAdapter – you can use either SQL Statements that you embed within your DAL layer, or stored procedures (SPROCs) that you call from it:

For our first table adapter we’ll use a SQL Statement.  You can either type this in directly within the next window:

Or alternatively launch the query-builder from that wizard step to graphically construct it (one handy feature with it is the ability to execute test queries to validate results):

Once we’ve finished building and testing our query, we will return back to the “Enter a SQL Statement” page.  Before moving to the next page, we’ll want to click the “Advanced Options” button to verify what type of operations we want the designer to generate:

The above dialog shows the default settings that are set when you run the “Create TableAdapter” wizard from within a VS 2005 Web Site Project (these are the most common data patterns we typically see developer’s use with stateless web applications).  When you run the wizard from within a Class Library Project or from a Windows Client Project, the “Use Optimistic Concurrency” checkbox will be selected by default as well. 

For the purposes of this DAL walkthrough, we want to turn off optimistic concurrency (note: one of my later blog postings on optimistic concurrency will change that setting – but I will be showing straight updates first).

When we click next on the wizard again, we’ll be prompted for the method names we want to create in our DAL to run our Category SQL query.  There are two patterns of data usage that this data wizard can generate – one is the “Fill” pattern used with DataSets, that will generate a method that accepts a DataSet or DataTable parameter that we want to add category information to.  The second pattern of data usage is one where we will generate a method that will simply return a new DataTable containing our category information.  This later approach is the one we’ll be primarily using in all of our later tutorials, and is more typical with stateless web-applications.

In the wizard I’m going to name this DataTable method “GetAllSuppliers”.  I will also keep the “GenerateDBDirectMethods” checkbox to automatically create default Insert, Update and Delete methods based off of my Select query on the component:

The wizard will then list all of the DAL methods it will create as part of this wizard process for, and when I hit finish add them to our data designer:

What I now have is a strongly-typed DataAdapter class (by default named “NorthwindTableAdapters.SuppliersTableAdapter”) with a “GetAllSuppliers” method that I can use to execute a SQL query and obtain back a strongly-typed DataTable results class (by default named “Northwind.SuppliersDataTable”). 

I could use these objects to easily obtain all of the Suppliers information from the Northwind database and output it within a page like so:
Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter
Dim suppliers As Northwind.SuppliersDataTable
Dim supplier As Northwind.SuppliersRow

suppliers = suppliersAdapter.GetAllSuppliers()

For Each supplier In suppliers
Response.Write("Supplier: " & supplier.CompanyName & "
Notice that I don’t need to write any manual ADO.NET code, construct a SQL query, or manage connections at all in the above code – all of that is encapsulated by the SuppliersDataTable and SuppliersTableAdapter that we now have in our project.  Notice also how the SuppliersTableAdapter class is strongly typed – meaning I get intellisense and compilation checking on the “suppliersAdapter.GetSuppliers()” method.  I can also access each property returned in the Suppliers result in a strongly typed fashion (for example: supplier.CompanyName or supplier.SupplierID).

I could alternatively write the below .aspx page and associated .aspx.vb code-behind file to easily databind and output the results in a grid:


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

    <title>Simple Category Listingtitle>
    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
    <form id="form1" runat="server">
        <div class="datatable">
            <h1>Simple Category Listingh1>
            <asp:GridView ID="GridView1" runat="server">

Imports NorthwindTableAdapters

Partial Class SuppliersTest2
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim suppliersAdapter As New SuppliersTableAdapter

        GridView1.DataSource = suppliersAdapter.GetAllSuppliers()

    End Sub

End Class
Which then generates the below output at runtime:

Note that when using the new ASP.NET ObjectDataSource control we will be able to eliminate the need to write any code for the scenario above (which even now only has three lines), as well as to automatically enable paging, sorting and editing on the GridView without any page code needed.  I’ll cover how to-do this in a later blog posting on using this DAL.  My goal with the sample above was just to show how you could procedurally databind the GridView using techniques you might already be familiar with in ASP.NET 1.1.

Tutorial 4: Adding Parameterized Methods within Table Adapters

It is usually pretty rare in web applications to want to request all of the data within a database table.  More typically you’ll want to use SQL queries to retrieve only the subset of data that you need.  The data designer makes it super easy to create multiple strongly-typed data-methods within a DAL to help with the parameterized SQL operations.

To add one to our Suppliers object, right-click on it and choose “Add Query”:

Once again we can choose either a stored procedure, or a SQL statement.  For the SQL statement you can choose to return multiple rows, or just a single value (useful for things like SELECT Count(*) commands, or to retrieve a single value):

For this parameterized query I’m going to return multiple rows, and will be filtering the data by country value.  I can express a parameter value using a “@parametername” syntax within my SQL statement like so (note: there can be any number of parameters in the SQL statement):

I can then name this parameterized data method “GetSuppliersByCountry”:

And now I have two methods I can use within my SuppliersTableAdapter to get Supplier data (GetAllSuppliers and GetSuppliersByCountry).  Note that the designer is fully re-entrant, meaning you can at any point re-configure a data method (for example: to change the SQL statement, add/remove parameters, etc).  Just right click and select the “configure” method to-do so:

You can also use the designer to run any of the data methods.  Just right-click and select “Preview Data” to execute them and see the results (note how any parameters can be specified as part of this operation):

And in code I can now easily build a page that uses this new method like so:


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

<head runat="server">
    <title>Suppliers By Country Sampletitle>
    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
    <form id="form1" runat="server">
        <h1>Suppliers By Country:h1>

            Select a country: <asp:TextBox ID="CountryTxt" runat="server" />
                              <asp:Button ID="SearchBtn" Text="Search" runat="Server" />           
        <div class="datatable">
            <asp:GridView ID="GridView1" runat="server">

Imports NorthwindTableAdapters

Partial Class SuppliersByCountry
    Inherits System.Web.UI.Page

    Protected Sub SearchBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchBtn.Click

        Dim suppliersAdapter As New SuppliersTableAdapter

        GridView1.DataSource = suppliersAdapter.GetSuppliersByCountry(CountryTxt.Text)

    End Sub

End Class
Which will generate a Suppliers search page:

Note that I did not have to write any ADO.NET code, manually create parameter collections, manage connection objects etc.  I only had to write 3 lines of code total to build the above sample from scratch using the Visual Web Developer data and page designers.

Tutorial 5: Using Insert, Update, and Delete DBDirect Commands on TableAdapters

Because we kept the default “GeneratedDBDirect Methods” checkbox selected, the Create Table Adapter wizard automatically added default Insert, Update and Delete methods to the SuppliersTableAdapter.  You can see these, as well as edit/customize them further, by selecting the SuppliersTableAdapter object within the DataSet Designer and then looking at the property-grid (note: you must select the SuppliersTableAdapter heading to have these methods show up in the property grid – they won’t show up if you just select the Suppliers heading):

Using the “CommandText” property in the property-grid, you can pull up a query designer for each of the default statements and customize them:

You can also optionally add your own custom Insert/Update/Delete methods to each TableAdapter.  For example, if I wanted to add a custom Insert method that had the additional behavior of returning the new identity column value of a newly created Supplier row (which has an auto-increment property set for the primary key), I could do so by right-clicking on the SuppliersTableAdapter and choosing “New Query”:

I’ll then pick doing the INSERT command with a SQL statement, and choose to create an Insert:

The DataSet designer will then automatically suggest the below SQL statement for me (it looks at the SELECT statement you entered earlier to suggest a default INSERT statement):

Note that the “SELECT @@Identity” statement at the end will return the newly created primary key from the insert operation.  I could further customize the INSERT operation however I want.

I can then use the wizard to name the method “InsertSupplier” (or any other name I want).  The wizard will then add this method to the SuppliersAdapter.  The last step I’ll do (since I want to return the @@Identity value from the insert), is to change the type of the InsertSupplier method from “NonQuery” to “Scalar”:

You could then write the code below to add a new supplier, and then update the supplier values, and then delete the supplier within the Suppliers table:
Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim supplierId As Integer

supplierId = supplierAdapter.InsertSupplier("Microsoft" _
                                            , "ScottGu" _
                                            , "General Manager" _
                                            , "One Microsoft Way" _
                                            , "Redmond" _
                                            , "USA" _
                                            , "98004" _
                                            , "425-555-1212")

supplierAdapter.Update("Microsoft" _
                       , "Someone Else" _
                       , "New title" _
                       , "New Address" _
                       , "New City" _
                       , "UK" _
                       , "New Zip" _
                       , "New Number" _
                       , supplierId)

Note that I did not have to write any ADO.NET code, manually create any parameter collections, or manage connection objects etc.  Because the data designer generates typed methods and DataTables, I’ll get both intellisense/compilation-checking as well as type validation within my DAL (so for example: if I try to pass an integer instead of a DateTime it would give me a compile error).

Tutorial 6: Using DataTables to Insert/Update/Delete Suppliers

Tutorial 5 above showed how to use data methods directly on the SuppliersTableAdapter to manipulate individual rows within our database.  Alternatively, developers can also work to perform these operations using the SuppliersDataTable and SuppliersDataRow objects.  These are particularly useful when adding/updating/deleting multiple rows at a time (these updates can optionally be batched to the database in one database call using this approach). 

The below example demonstrates how to retrieve all of the suppliers in the US with a single database call, then conditionally update some of the supplier’s zip-codes, and then add a new supplier.  We’ll then update the database with all of the additions/changes:
Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter
Dim suppliers As Northwind.SuppliersDataTable
Dim supplier As Northwind.SuppliersRow

' Obtain all Suppliers in the US
suppliers = supplierAdapter.GetSuppliersByCountry("USA")

' Loop through all suppliers and update any 98042 postcodes to 98004
For Each supplier In suppliers

If supplier.PostalCode = "98052" Then
            supplier.PostalCode = "98004"
End If


' Create a New Supplier Just for Fun
supplier = suppliers.NewSuppliersRow()

' Set new data properties on supplier2 row
With supplier

.CompanyName = "Microsoft"
      .ContactName = "ScottGu"
.Address = "One Microsoft Way"
      .ContactTitle = "General Manager"
      .City = "Redmond"
      .PostalCode = "98052"
      .Country = "USA"
      .Phone = "425-555-1212"

End With

' Add New Supplier to SuppliersDataTable

' Update Database with all changes (updates + additions)
Note that there are fancier ways to-do filtering (and sorting) operations that I’m not using above, but I wanted to keep this tutorial simple as opposed to overload it with too many concepts.

Tutorial 7: Putting the Northwinds DAL Together

The above tutorials hopefully provide the basic background needed to create DAL’s using the DataSet designer and then use them from code.

Using the above knowledge, you should be able to go off and quickly create the following strongly-typed TableAdapters with associated data methods pretty easily (note that the designer and query builder can be used to avoid having to manually write any of the SQL queries below – and it will auto-suggest subsequent queries for each adapter after you add the first one).



SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone
FROM           Suppliers


SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone
FROM           Suppliers
WHERE        Country=@Country


SELECT        SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone
FROM           Suppliers
WHERE        SupplierId=@SupplierID


SELECT        DISTINCT Country
FROM           Suppliers



SELECT        CategoryID, CategoryName, Description
FROM           Categories


SELECT        CategoryID, CategoryName, Description
FROM           Categories
WHERE        CategoryId=@CategoryId 



SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo
FROM           Employees


SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo
FROM           Employees
WHERE        EmployeeID=@EmployeeID


SELECT        EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo
FROM           Employees
WHERE        ReportsTo = @ReportsTo



SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,
                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName
FROM           Products


SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,
                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName
FROM           Products
WHERE        SupplierID=@SupplierID


SELECT        ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,
                    (SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName
FROM           Products
WHERE        CategoryID=@ CategoryID

Most of the above TableAdapters are pretty straight-forward and simple.  The one that is a little more advanced is the ProductsTableAdapter.  Specifically, there in addition to retrieving the Products table columns I’m also retrieving the CompanyName from the Suppliers table that maps to the Product’s SupplierId column (it has a foreign key relationship to the Suppliers table).  The above SQL statement will add this as a read-only “SupplierName” column on our ProductsDataTable.

This will allow me to be more efficient from a database perspective when I want to build a list UI on top of my Products table, and save me from having to hit the database an extra time per-row to retrieve this value when displaying a product list on the site.  

The good news is that the data designer can still infer enough from the product adapter’s SQL statements to still automatically generate the correct INSERT, UPDATE and DELETE commands for the ProductsTableAdapter.  In cases where you are doing more advanced JOINS across multiple tables and merging results from multiple places, the data designer might not be able to generate these automatically.  The good news is that you can still define these manually within the designer (just click on the table-adapter, and then within its property grid choose to create a new “Insert” (or update or delete) command and define your own logic).

When finished, my Northwinds data design-surface looks like this (note how the designer will automatically map and detail foreign-key relationships):

When I hit save on the Northwinds.xsd file (where all of the adapters, definitions and relations are declaratively stored in an XML file), I’ll be able to program and data-bind against any of the objects defined within it.

Tutorial 8: Adding Custom Code to the DAL

One of the nice things about the data designer is that the .xsd file that declaratively stores all of the relevant definitions gets translated into either VB or C# code at compile and runtime.  This means that you can easily step through it within the debugger, and always understand “what is it doing for me under the covers”. 

To easily see what code has been generated by the data designer, just open the “Class View” property window in Visual Web Developer or VS 2005 (if it isn’t visible select the View->Class View menu item), and navigate to the respective namespaces and types to see the object model of them.  You can then right-click and choose “Browse Definition” to jump to the generated code for any method:

In addition to viewing the generated code, you can also add to it and extend it in many ways.  You can do this either by sub-classing the generated objects, or alternatively by using the new “Partial” keyword feature now supported by the .NET 2.0 language compilers.  The partial keyword enables you to add any method, property or events you want to the classes generated within the DAL, and they will be compiled together into a single generated type.  Because the partial class code you write lives in a separate file from the files generated by the designer, you do not have to worry about the designer ever clobbering or overwriting your code.

To see a simple example of how you could use this feature, I could easily add an additional “GetProducts()” method to each row of Suppliers within my Northwind DAL (allowing me to easily obtain the ProductDataTable for the specific Supplier I have a reference to).  Enabling this is as easy as adding a “SuppliersRow.vb” file in my app_code directory and adding this code to it:
Partial Public Class NorthWind

    Partial Public Class SuppliersRow

        Public Function GetProducts() As ProductsDataTable

            Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter
            Return productsAdapter.GetProductsBySupplier(Me.SupplierID)

        End Function

    End Class

End Class
This tells the VB compiler that I want to add a “GetProducts()” method to the SuppliersRow class (I’m using the “SupplierID” property on the supplier’s row to automatically figure out the subset of products by Supplier – note that I can write “Me.SupplierId” – since this method is part of the SuppliersRow class).

Once I add this I can then write this code to easily hierarchically list each product produced by each Supplier:
Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter
Dim suppliers As Northwind.SuppliersDataTable
Dim supplier As Northwind.SuppliersRow

suppliers = suppliersAdapter.GetAllSuppliers()

For Each supplier In suppliers

Response.Write("Supplier: " & supplier.CompanyName & "

      Dim products As Northwind.ProductsDataTable
      Dim product As Northwind.ProductsRow

      products = supplier.GetProducts()

      For Each product In products
            Response.Write("------- Product: " & product.ProductName & "

And I could easily build this page using a and control to databind a list of suppliers, and then allow a user to click any of the suppliers to list a hierarchical drill-down of their products:

I’d do this by databinding the suppliers against the DataList:

Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender

Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

      DataList1.DataSource = suppliersAdapter.GetAllSuppliers()

End Sub
And then using the “Select” feature of DataList to have the selected item template hierarchically retrieve and display the products (this way only the selected supplier’s products are retrieved):


<h1>Supplier/Product Drilldown:h1>
<p>Click a Supplier to List Its Productsp>
<asp:DataList ID="DataList1" runat="server">
            <asp:linkbutton ID="Details" CommandName="Select" runat="server"><%#Eval("CompanyName") %>asp:linkbutton>
            <div class="selectedsupplier">
                <strong><%#Eval("CompanyName") %>strong>
                <asp:Repeater ID="ProductsList" DataSource='<%# Container.DataItem.Row.GetProducts() %>' runat="server">


Hopefully the above set of tutorials provides a useful (and somewhat exhaustive -- sorry for the length) walkthrough of the core features and capabilities you can easily take advantage of with the DataSet designer in Visual Web Developer and VS 2005 to build easy data access layers. 

Over the next few weeks I’m going to be posting many (short) blog posting that walkthrough using the DAL I built above to implement common web data patterns using ASP.NET 2.0.

Hope this helps,