Wednesday, October 31, 2007

Reading XML Document in .Net

There are 3 preferable ways to read XML document.

  • XMLDocument Class - It allows to insert, update, delete or move a node.
  • XMLTextReader Class - It allows read data in fast forward-only manner.
  • XMLValidatingReader Class - It allows to validate XML document against Document Type Definition (DTD), XML Schema definition(XSD) and XML Data Reduced(XDR).

XMLTextReader and XMLValidatingReader Class is derived from XMLReader class which provides
  • fast,
  • non-cacheable,
  • read-only, and
  • forward-only access to XML data.

Choosing between XMLDocument, XMLTextReader and XMLValidatingReader Class.
  • Use XMLDocument when
    • You want to perform operation like Insert, Update and delete.
    • Memory is not a constraint
  • Use XMLTextReader when
    • You want to read data in forward-only manner.
    • When memory is constraint.
  • Use XMLValidatingReader when
    • You want to validate XML document against DTD, XSD and XDR.

What is XML DOM (Document Object Model)

Why is XML DOM?
XML is a standard by which two incompatible system can communicate. XML DOM works with XML data in your application. It is used to read, write and modify XML document programatically.

Understanding Nodes in XML Document

Node type



This node type is the container for all the nodes and is also known as the document root.


This node type represents element nodes.


This node type represents the attributes of an element node.


This node type represents the text that belongs to a particular node or to an attribute.

Example of XML Document, employee.xml
<?xml version="1.0" encoding="utf-8" ?>
<Address>2010 Stanley Dr., Charlotte, NJ 08830</Address>
<Designation>HR Manager</Designation>
<Address>7862 Freepoint Pkwy, Tampa, NJ 08820</Address>
<Designation>Software Professional</Designation>

Understanding XML Document
  • Every XML Document contains a single root element that contains all other nodes of the XML Document. Here <employees> is a root element.
  • XML Element is a Record which contains information. Here <employee>
  • XML Element Attribute further describe the Record details. Here <FirstName> etc are XML Element Attributes.

What is XML Document Object?
XMLDocument provides an in-memory representation of and XML document.

Example of XML Document
Create a Website
Create a XML Document and name it employee.xml

Loading of XML Document
You can load xml document using XMLDocument object's load method

protected void Page_Load(object sender, EventArgs e)
if (!Page.IsPostBack)
XmlDocument EmpXMLDoc = new XmlDocument();
string XMLDocPath = Server.MapPath("Employee.xml");

Saving XML Document
You can save XML Document with XMLDocument.Save method
Example: XMLDocument.Save("Employee.xml");

Read XML Document and display on WebPage
protected void Page_Load(object sender, EventArgs e)
if (!Page.IsPostBack)
XmlDocument EmpXMLDoc = new XmlDocument();
string XMLDocPath = Server.MapPath("Employee.xml");

//Read XML Document //Note: Begin for ChildNode[1]
foreach (XmlNode node1 in EmpXMLDoc.ChildNodes[1])
foreach (XmlNode node2 in node1.ChildNodes)
Response.Write("<b>" + node2.Name + "</b>: " + node2.InnerText + " ");


Tuesday, October 30, 2007

Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System

The Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System Best Practices Analyzer programmatically collects settings and values from data repositories such as MS SQL, registry, metabase and performance monitor. Once collected, a set of comprehensive ‘best practice’ rules are applied to the topology. Administrators running this tool will get a detailed report listing the recommendations that can be made to the environment to achieve greater performance, scalability and uptime.

Download Link

Monday, October 29, 2007

Windows Vista System Icon disappear Fix

Windows vista has problem of Icon disappear from System Tray.

So, to make Important System Icon in sytem tray back in windows vista, you need to delete two entries from registry.

That is

  • Navigate to the key "HKEY_CURRENT_USER\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion \TrayNotify"
  • Delete the values "IconStreams" and "PastIconsStream"
  • To make the change refresh, Open Task Manager (ctrl+shift+esc) and End Task the "explorer.exe" and Add again "explorer.exe" from File menu - New Task.
For understanding each step in depth follow this link.

Adding and viewing Sharepoint User for Sharepoint Home Site

Before Adding user make sure that User is already available in Windows Server 2003 - "Active directory users and computers"

Ok, lets take a quick overview on adding User Group to Active Directory in Windows Server 2003.

Step1: Start Menu - Control Pannel - Administrative Tools and in it Active directory users and computers.

Step2: Drill the Active Directory User and Computers and in it select the server, example on my PC i have named server as "MyServer" so need to drill "MyServer"
- And Expand Users directory

Step3: Right Click user directory and select New from popup menu and click user, to create new user.

And you are done creating user. Note: You have created user in windows server 2003, to make this user access sharepoint site it is necessary to assign proper permission.

So now logged in to sharepoint site as administrator and add user to visitor group as created in previous post.

Advise: Please refer previous post to create user group

Now, lets add user in to "Visitor Group" as created in previous post.

ok, now before adding already available user, lets enter some anonymous user and look what happen.

- As obvious, it will display me error, No exact match was found.

So if you want "sachin tendulkar" user to be added, follow the step as describe above.

Now, lets add user already created. i.e. "Harsh Patel".
- Two option to add, either write his email, name and click on check user button, so if user is available it would be underline, otherwise you can click browse button to search user and then add it.

ok, lets click browse button and find user with name harsh and add it.

After selecting valid user, its now turn to assign permission.
- Choose from Sharepoint User Group of permission already available or
- Give user permission directly.
Now as we want to add him to "Visitor" Group already created we will choose from drop-down of Sharepoint User Group.

Also check "Send Email" Check box if you want to send welcome email to user.

And click ok to create user.

Now, lets check whether user is created by opening Visitor Group.

Thats it, we are done with creating user.

- Created user in Windows 2003 Server.
- Created user in Sharepoint and assign permission and adding it appropriate sharepoint user group.

Runtime Error while opening Sharepoint Site

Before starting discussion, I would like to Thanks Uday Microsoft MVP for Sharepoint for sharing his knowledge in solving the error.

Runtime Error while opening Sharepoint Site

Or do you recieve following error.

Runtime Error

Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed.

Details: To enable the details of this specific error message to be viewable on the local server machine, please create a tag within a "web.config" configuration file located in the root directory of the current web application. This tag should then have its "mode" attribute set to "RemoteOnly". To enable the details to be viewable on remote machines, please set "mode" to "Off".

<!-- Web.Config Configuration File -->

<customErrors mode="RemoteOnly"/>

Cause of Error
There can be more ways this error can trouble, but let me share how this error occur while working with sharepoint.

My PC, is one of the workgroup PC to connect to local network. Now to take advantage of sharepoint and to make it sharepoint server, I have turned my PC to Domain Controller and Installed Active Directory using DCPromo Command on command prompt.

What is Domain Controller?
Domain Controller is first PC in network which controls sub domain.
Example: can be said Domain Controller, while sub domain such as and are domain under the domain controller, for more details and indepth knowledge please refer "Active Directory Concept" and "Domain Creation Concept" in windows 2003 server.

How to turn your PC to Domain Controller?
You can make your PC to Domain Controller on Windows 2003 Server by running command "DCPromo" on command prompt. A wizard will open which guide you step by step in creating your PC to Domain Controller.

Ok, so now back to our problem.

It was obvious that after creating your PC to Domain Controller this error is likely to occur as default setting are changed and that also need to configure.

Solution for Error
  • Note: I haven't considered steps for backup data.
  • Remove SQL Server with UnInstalling all Instances.
  • Remove MOSS.
  • Remove Virtual Directory from InetMgr (IIS) related to Sharepoint.
    • Sharepoint Central Administration
    • Office Server Web Services...
  • Remove directory from c:\inetpub\wwwroot\wss\Virtual Directories and delete all directories under it.
    • Remove directory "80" and so on...
  • Note: I have also deleted directory not deleted while MS SQL Server and also remove it from Registry. i.e. from HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\
  • Restart PC.
  • Install MS SQL Server
  • Restart PC
  • Install MOSS 2007
  • Restart PC
  • Run Configuration Wizard of MOSS
  • And Try opening sharepoint website, now it should work for you.
Please pass your comment and suggestion to improve this steps as each time it not possible to delete all data, and it is also necessary to take backup before applying above step, so share your experience, Thanks.

Manthan Award for Gujarat Government

Microsoft’s Students2Business initiative with Government of Gujarat was awarded the Manthan award ( which is an Indian initiative by Digital Empowerment Foundation, to select and promote the best practices in e-Content and Creativity in India. It involves representatives from each state and union territory of India and visualizes the bridging of the digital divide and narrowing the content gap as its overall goal. The Students2Business program focuses on making the students more employable by providing them training on Microsoft technology by means of existing distance education infrastructure in the state followed by an evaluation, certification and an opportunity to work on projects/internships.

Samvaad - Learn Share and Grow from Microsoft

Microsoft Student Partners connect at regional boot camps
Every year Microsoft recruits Student Partners (MSPs) from colleges across India. These are young technology enthusiasts who work as Microsoft’s representatives on their campuses - conducting trainings, sharing knowledge and motivating fellow students – and in turn they get access to latest technology tools, gain industry exposure and the chance to be interviewed at Microsoft.

MSPs kick off their activities at regional boot camps organized by Microsoft, 3 of which were held in Ahmedabad, Pune and Gurgaon over the past few weeks. Apart from technical training on .NET and sessions on the new technologies, MSPs also got a chance to get to know each other, which will go a long way towards building a strong student-community. If you want to know more about the MSP program, write to or visit

Saturday, October 27, 2007

Getting Started with Sharepoint Services

Lets Gets Started with Sharepoint Service 3.0 and MOSS 2007 with easy step by step lessons.

Understanding Sharepoint - Source of Information

What is Microsoft Windows SharePoint Services? How is it related to Microsoft Office SharePoint Server 2007?
Windows SharePoint Services is the solution that enables you to create Web sites for information sharing and document collaboration. Windows SharePoint Services — a key piece of the information worker infrastructure delivered in Microsoft Windows Server 2003 — provides additional functionality to the Microsoft Office system and other desktop applications, and it serves as a platform for application development.
Office SharePoint Server 2007 builds on top of Windows SharePoint Services 3.0 to provide additional capabilities including collaboration, portal, search, enterprise content management, business process and forms, and business intelligence.

What is Sharepoint Service from User prospective?
From a Users perspective SharePoint is a way of making documents and folders on the Windows platform accessable over the web. The user visits the SharePoint Portal web page, and from there they can add documents, change documents & delete documents. Through this Portal, these documents are now available for discussion, collaboration, versioning and being managed through a workflow. Hence the name "Share-Point". Details about the document can be saved too, such as: who wrote it, when, for whom, its size, version, category or target audience. These can then be used to find the document through SharePoint's Search facility. Even documents not "in" SharePoint can be included in the search engine's index so they become part of the portal. All in all, it's a great way to get stuff up on the web for users with average technical skills, and for administrators to manage the content.

What is Sharepoint Service from Administration prospective?

Administering SharePoint mainly consists of setting it up, which is much easier than you expect, adding the content, which can be just dragging and dropping in whole directory structures and files, and then organinsing the files better by giving them categories or other metadata. This is done either throuhg the Web interface or through the SharePoint Client: a program what means you can access SharePoint as a Web folder and then right-click files to select options like "edit profile". Or add files by dragging them in individually or in bulk.

Setting the security is also important, using NT accounts, either NT4 or Active Directory (or both in mixed mode) you can give users access to files/folders the same way as you do in standard Windows. Users can be grouped and the groups given access priveliges to help manage this better. Also SharePoint has 3 Roles that a User or Group can be given on a perticular item. Readers can see the item (ie document/file or folder) but not change it, Authors can see and edit items and coordinators can set security priveliges for the part of the system they have control over. Thus, you could set 12 different coordinators for 12 different folder trees, and they could manage who can do what within that area only! Good eh?

What is Sharepoint Service from Technical prospective?

Technically SharePoint illustrates neatly what Microsoft's .net strategy is all about: integrating Windows with the Web. Microsoft has previously made accessing stuff on a PC easier, (Windows) then on a network (NT) and now on the web (.NET). SharePoint is an application written to let a user access a web accessable directory tree called the Web Storage System. SharePoint was written with a set of technologies that allow the programmer to pass data, functions, parameters over HTTP, the web's medium. These are XML, XSL and SOAP, to name a few I understand the basics of!

To the user it looks easy, like Hotmail, but everytime they click a button or a link, a lot has to happen behind the scenes to do what they want to do quicky and powerfully. Not as easy as you might think, but SharePoint does it for you. Accessing this Web storage system and the server itself is also done using technologies like ADO, CDO, PKMCDO, LDAP, DDSC, ADSC. More on these later. SharePoint is a great example of how the Internet Platform can be extended and integrated into an existing well adopted technology, Windows.

How Sharepoint is useful?

  • User Authentication
  • Personalization
  • Application Integration and Aggregation
  • Search
  • Collaboration
  • Web Content Management
  • Workflow Management
  • Analytics & Reporting

Advantages of Sharepoint
• A lot of functionality for a low price
• Familiar Microsoft look and feel (Office 2003)
• Little end user training required
• Extensible using .Net technologies
• Simple cross site branding is simple and fast
• Simple document management out of the box
• Fantastic search engine
• Ease of installation and Rapid deployment
• Deploy & manage multiple sites and portals
• Reuse of common functionality
• Support wide range of technologies and systems
• Leverage of existing investments
• Incorporate information from existing ERP applications
• Messaging systems

Disadvantages of Sharepoint
• Previous version Web Parts will not work with the new version
• Set up via a browser interface makes large site production slow
• Navigation paradigm built in so custom navigation is difficult
• Approximately 1000 built in templates means full brand customisation is time consuming and expensive.

How do I install Sharepoint on my computer?
For Installing sharepoint you need to install following software, but before installation check the hardware requirement for sharepoint installation

  • Windows Server 2003 with SP1 Operating System
  • Create "Active Directory" with DcPromo command on Command Prompt.
  • .Net Framework 3.0
  • Windows Server Sharepoint 2003 (WSS 3.0) or
  • Office Sharepoint Server 2007 (MOSS 2007) [Preferred]
Download Links

Download Related Software

Google Page Rank Updated

Google Page Rank Updated.

Google Page Rank of dotnetguts blog is updated from 3 to 4.

Friday, October 26, 2007

What is InfoPath - Microsoft Office Infopath

What is InfoPath - Microsoft Office Infopath 2007 or 2003?
Infopath can use to design a browser-compatible form template and publish it to a server that is running infopath form services. Users can then fill out forms that are based on your form template in a web browser or on a mobile device. In addition, Infopath Forms services provides a central location to store and manage form templates for your organization.

Where can I use Infopath?
What is the use of Infopath? or
Why I use Infopath?

  • With InfoPath, you can simply develop Web Services "hooks" that allow data to be submitted to and retrieved from a variety of applications. Developers can use InfoPath to quickly create new, feature-rich user interfaces to those legacy applications. This process is both much faster and less expensive than the task of re-engineering the legacy application, and can result in a huge cost saving.
  • You can transfer and re-use data with Infopath, as its base is XML and so its now easy to exchange data between two incompatible system.

Example of Infopath usage with application

Core Advantage of using Infopath
  • Ability to communicate between two Incompatible data format
  • Browser-Compatible Form
  • Broader usage scope, it gets easily integrable with other office product. example, you can easily export data to excel worksheet.
  • Offline support, InfoPath forms don't have to be filled out while a user is connected to a network. Users can save forms to their computer, work on them offline, and then submit them to the corporate network when they are reconnected.
  • Low cost and Fast Development
  • For more on Infopath Advantage and How Infopath works with other Microsoft Office Product.

Tutorial and Training on Infopath

Sunday, October 21, 2007

MAIL Server - How to Setup your own MAIL Server

Good Article link for Setting up your own Free MAIL Server

Highlight of Setting up free mail server article

  • Download Free Mail Server (Mercury Mail Server: Freeware)
  • Installation of Free Mail Server
  • Configuration of Free Mail Server

Read Article

For those who are using Free Mail Service provided by can simply configure there outlook email as shown in following article.

Read Article on Configuring Outlook for SecureServer.Net

Friday, October 12, 2007 has launched

A wordwide SQL Server community has been launched. This is a joint effort of Microsoft Employees, few MVPs and SQL Server experts.

Lot of material for SQL Users

Highlights of

  • SQL Server Articles
  • Discussion Topics for Allmost all topic on SQL including all versions of SQL
  • Useful Links for SQL Server
  • Readmade Scripts for SQL Server
  • Useful tools for SQL
  • Tips and Tricks
  • SQL Clinic
  • SQL Community to get instant help on SQL Topics from industry gurus.
Logon to

Wednesday, October 10, 2007

Function Execution in SQL Server 2005

In this article you will learn, everything about using Function Execution in SQL Server 2005

  • String Functions
  • Date and Time Functions
  • Mathematical Functions

String Functions
String Functions are used for manipulating string expression. Note: string expression should be passed within single quote.
  • Len('') - Returns length of string.
    • Example: select Len("Shri Ganesh") will return 11
  • Lower('') - Convert all characters to lowercase characters.
    • Example: select Lower('Shri Ganesh') will return shri ganesh
  • Upper('') - Convert all characters to uppercase characters.
    • Example: select Upper('Shri Ganesh') will return SHRI GANESH
  • LTrim('') - Removes spaces from given character strings on left.
    • Example: select LTrim(' Shri Ganesh') will return Shri Ganesh
    • Note: It doesn't removes tab or line feed character.
  • RTrim('') - Removes space from given character strings on right.
    • Example: select LTrim('Shri Ganesh ') will return Shri Ganesh
    • Note: It doesn't removes tab or line feed character.
  • Trim('') - Removes spaces from given character strings from both left and right.
    • Example: select LTrim(' Shri Ganesh ') will return Shri Ganesh
    • Note: It doesn't removes tab or line feed character.
  • SubString('') - Returns a part of string from original string.
    • SubString(character_expression, position, length)
      • position - specifies where the substring begins.
      • length - specifies the length of the substring as number of characters.
    • Example: select SubString('Shri Ganesh',6,7) where in
    • 6 - Starting position of sub string from given string.
    • 6 - It is no. of characters to be extract from given string, starting from 6.
    • That is it will return "Ganesh" As ganesh start from 6th character upto 6 characters.
  • Replace('') - Replace the desired string within the original string.
    • Replace(character_expression, searchstring, replacementstring)
      • SearchString - string which you want to replace.
      • ReplaceString - new string which you want to replace with
    • Example: select replace('Think High To Achieve High','High','Low')
    • here, function search for every occurrence of High and replace it with Low.
    • Original - Think High To Achieve High
    • Result - Think Low To Achieve Low
  • Right('') - extract particular characters from right part of given string expression.
    • Example: select right('Think High To Achieve High',15) will return "To Achieve High"
    • This function will be helpful when you want particular characters from right part.
    • Example: Let say i have social security nos. and i want to extract last 4 digit of it.
      • select right('111-11-1111',4) will return 1111
        select right('222-22-2222',4) will return 2222
        select right('333-33-3333',4) will return 3333
        select right('444-44-4444',4) will return 4444

Date and Time Functions
Date and Time Functions are used for manipulating Date and Time expression.
  • GetDate() - Returns current date and time of a system.
    • Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
  • GetUTCDate() - Returns current date and time information as per UTC (Universal Time Coordinate or Greenwich Mean Time)
    • Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
DatePart and Abbrevation, which we will be using with DatePart, DateADD, DateDIFF function.

Datepart Abbreviations


yy, yyyy


qq, q


mm, m


dy, y


dd, d


wk, ww


dw, w




mi, n


ss, s



  • DatePart() - Returns an integer representing a datepart of a date.
    • Note: Example are based on considering "2007-10-10 15:34:37.287" as GetDate()
    • Example:
      • select DatePart("day",GetDate()) will return 10.
      • select DatePart("hour",GetDate()) will return 16.
      • select DatePart("dayofyear",GetDate()) will return 283. And so on...
  • DateADD() - Returns adds a date or time interval to a specified date.
    • Syntax: DateADD(Abbrevation, number to be added, date)
    • Example:
      • select DateAdd("day",7,GetDate()) will return 2007-10-17 16:09:18.280
      • select DateAdd("month",20,GetDate()) will return 2009-06-10 16:10:02.643
      • And so on...
  • DateDIFF() - Returns difference between two specified dates.
    • Syntax: DateDIFF(Abbrevation, startdate, enddate)
    • Note: If the end date is earlier than the start date, the function returns a negative number. If the start and end dates are equal or fall within the same interval, the function returns zero.
    • Example:
      • select DATEDIFF("mm", Getdate()-500,GETDATE()) will return 17
      • You must pass valid start and end date otherwise you will receive error.

Mathematical Functions
Mathematical Functions are used for manipulating Mathematical expression.
  • ABS() - Returns positive value of numeric expression.
    • Example: In following example both statement will return 3.14
      • select ABS(3.14)
        select ABS(-3.14)
  • Ceiling() - Returns the smallest integer that is greater than or equal to a numeric expression.
    • Example:
      • select Ceiling(3.14) will return 4
        select Ceiling(-3.14) will return 3.
  • Floor() -Returns the largest integer that is less than or equal to a numeric expression.
    • Example:
      • select Floor(3.14) will return 3
        select Floor(-3.14) will return 4
  • Round() - Returns a numeric expression that is rounded to the specified length or precision.
    • Example:
      • select Round(3.14, 1) will return 3.10
        select Round(-3.17, 1) will return -3.20
      • select Round(3.12345, 4) will return 3.12350
      • select Round(3.12345, 3) will return 3.12300
  • Power() - POWER(numeric_expression, power)
    • Example: select power(2,3) will return 8

How to open .docx file without installing MS Office 2007

An interesting post made by Abhishek Kant, regarding How to open MS Office 2007 file without installing Office 2007 on your computer.

if you don't have Office 2007 and have started to receive docx files from
individuals, you can now read the same using the newly released Word Viewer
2007.. the word viewer replaces all earlier releases... true to its
name, the viewer let's you view the Word documents but don't let you edit
the same... the Word Viewer enables free viewing of .docx and .docm with the
same visual fidelity as the full version of Word 2003.

Get the FREE download here

Tuesday, October 09, 2007

Visual Studio 2008: Information on Microsoft Expression, ASP.NET AJAX

Visual Studio 2008 Beta 2
Additional resources to help you get the most out of your evaluation experience:

This topic in the MSDN Library contains descriptions and quick overviews of some of the new features and enhancements in Visual Studio 2008.

Ready to learn about all the ways that Visual Studio 2008 will make you more productive? Check out the videos on this regularly-updated page. Videos include some quick clips as well as in-depth training videos on the following subjects: client development, Web development, data access, Office development, mobile development, and team development.

Learn about the changes in ASP.NET and Visual Web Developer, including new server controls and types, a new object-oriented client type library, and full IntelliSense support in Visual Studio 2008 and Microsoft Visual Web Developer Express Edition for working with ECMAScript (JavaScript or JScript).
Visual Studio Team System Resources

Develop and Test Web Applications with Visual Studio Team System 2008
Testing Web applications is hard. Visual Studio Team System 2008 provides tools to automate testing, generate testing code, and make it easy to test Web applications and AJAX sites. In this topic, you will learn how to create a simple Web application, record and run a Web test, and bind data sources to your tests.

Integrating your code with the rest of the system can be a painful experience; breaking the sacred nightly build has never been fun. Continuous integration is a response to these problems. Learn how to use Visual Studio Team System build features to integrate your code at each check-in and get feedback on your work as you complete it.

Version control, build, and team workflow are key components of the daily lives of a developer. Understanding these concepts and how to make the best use of your tools is fundamental. Follow these short, step-by-step walkthroughs, and learn how Visual Studio Team System 2008 Team Foundation Server can help you in key scenarios, including version control, build setup, and team workflow features.

Source of Information: MSDN Flash Microsoft Newsletter.

New Features in Visual Studio 2008

Useful links for Visual Studio 2008

More About What's New in Languages and Technologies

.NET Compact Framework Version 3.5.NET Compact Framework
.NET Framework Version 3.5Understanding the .NET Framework
What's New in ADO.NETAccessing Data with ADO.NET
What's New in Architecture EditionVisual Studio Enterprise - User Interface
What's New in DataData Access in Visual Studio
What's New in DeploymentDeployment in Visual Studio
What's New in Smart Device ProjectsSmart Device Projects
What's New in the Visual Basic LanguageVisual Basic Language Reference
What's New in the Visual Studio DebuggerDebugging with Visual Studio
What's New in Visual BasicVisual Basic Concepts
What's New in Visual C#C# Programmer's Reference
What's New in Visual C++ 2008Getting Started with Visual C++
What's New in Visual Studio Team SystemVisual Studio Team System Help
What's New in Visual Studio Tools for OfficeVisual Studio Tools for Office Concepts

Sunday, October 07, 2007

Query execution in SQL Server 2005

SQL Query execution

In this article you will explore

  • How to execute query within sql server 2005 environment
  • How to filter records with where clause
  • How to arrange records with order by clause
  • How to group records by group clause
  • How to filter grouped records with having clause
  • Concept of Joins, explanation and example of
    • Inner Join
    • Outer Join
      • Left Outer Join
      • Right Outer Join
      • Full Join or Cross Join
    • Self Join
  • Concept of sub queries, with explanation and example.
  • Concept of correlated sub queries, with explanation and example.
Ok, so lets begin...

For execution of Query, Open New Query Editor as shown in figure.

Now lets execute different data manipulation statement.

Select Database to perform Database Operation using "use" Statement
--Select Database for Data Manipulation opperation
use MySamplePractise

For mastering, Data Manipulation from beginner to expert, Study following Example with 150+ Queries.

Select Statement Execution in SQL Server 2005

--Display each details of all Customers
select * from customer

--Display FirstName, City and Country Details of Customers
select FirstName, City, Country from Customer

--Using Alias to display details, Display "Customer Name" by concating FirstName and LastName of Customer
select FirstName + ' ' + LastName as 'Customer Name' from Customer

Filtering Records with "Where Clause"

--Display Customers from country USA
select * from Customer
Country = 'USA'

--Display Customers from All countries except USA
select * from Customer
Country <> 'USA'

--Display details of Customers whose CustomerID in a range from 1003 to 1008
select * from Customer
CustomerID >= 1003 and CustomerID <= 1008

--You can replace above statement by using Between statement.
--Between will show customer with customerID 1003 to 1008.
select *
from Customer
where CustomerID between 1003 and 1008

--Display Customers whose LastName starts from P
select * from Customer where LastName like 'P%'

--Display Customers whose country name ends with A
select * from Customer where Country like '%A'

--Display Customers whose FirstName or LastName consist of word khan
select *
from Customer
where FirstName like '%Khan%' or LastName like '%Khan%'

--Display Customers whose FirstName second character is A
select *
from Customer
where FirstName like '_A%'

Ordering Records with "Order by" Caluse
--Note: by default when neither ASC (for Ascending) or DESC (for descending) is specify, it is ordered in Ascending order.

--Display Customers of USA country ordered by state
select * from Customer
where Country='USA'
order by State

--Display Customers of USA country ordered by state in descending order.
select * from Customer
where Country='USA'
order by State DESC

--Order Customer by LastName in descending order and FirstName in Ascending order.
select * from Customer
order by LastName DESC, FirstName ASC

Grouping Records by "Group by" Caluse

--Display Count of Customers in each country
select Country,Count(CustomerID) from Customer
group by country

--Display Count of Customers group the data by Country and State
select Country, state, Count(CustomerID) from Customer
group by country, state

Filtering Grouped data by "Having" Caluse

--Display Count of Customers country wise and display those countries only where more than 2 customers are available.
select Country,Count(CustomerID) from Customer
group by country
having Count(CustomerID) > 2

Using both Where and Having Clause to Filter data.

--Display Count of Customers from country "USA", state wise where in display details of those state only which have atleast 2 Customer
--It will display only Customer Count from "NJ" state of "USA" country.
select State,Count(CustomerID) from Customer
where state='NJ' and country='USA'
group by state
having count(CustomerID) > 2

Using Order by to arrange Grouped data

--Display Count of Customers from country "USA", state wise and it should display details in descending order of state of those state where Customers are atleast 2.
select State,Count(CustomerID) from Customer
where country='USA'
group by state
having count(CustomerID) > 2
Order by state desc

Now, lets display data from multiple table. For that we need to understand Joins so lets begin what is Join and how many different ways to display data.

Joins in SQL Server

Joins in SQL are used to combine the data from two or more tables on a common column between the two tables.

Different types of Joins in SQL Server and its explanation
  • Inner Join
  • Outer Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join or Cross Join
  • Self Join
Lets go through the concept quickly.

Inner Join
Inner Join return those rows which are common in both the tables. In other words, Inner Join displays only the rows that have matching rows in both the tables.
Note: Nulls are excluded. Another thing you may even write just "Join" instead of "Inner Join" as default join is "Inner Join"

Example of Inner Join
We can use two types of syntax to write Join Query, that is
  • Join Query with ANSI Join Syntax
  • Join Query with Microsoft Join Syntax
well i will go through both example, but we will follow ANSI Join Syntax as it is widely used, but according to me Microsoft Join Syntax is easy...

Use Northwind
Method 1: ANSI JOIN Syntax
select * from
orders inner join [order details]
orders.orderid = [order details].orderid

Method 2: Former Microsoft JOIN Syntax
select * from orders, [order details]
where orders.orderid = [order details].orderid

Both query will be generate the same result.

Now, lets turn to example from our SamplePractise Database

--Display Details of Customer who have placed order.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
Customer.CustomerID = Orders.CustomerID

--or-- Note: Inner join is default join and so you can write just join instead of inner join.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer Join Orders
Customer.CustomerID = Orders.CustomerID

--Display Details of Customer living in "USA" who have placed order and arrange data by latest order placed.
select LastName, FirstName, Country, OrderId, OrderDate from
Customer inner Join Orders
Customer.CustomerID = Orders.CustomerID
where Country = 'USA'
order by OrderDate DESC

Outer Join

Outer Join, return all the rows from at least one of the table mentioned in the Join clause. Lets understand 3 types of outer join

Left Outer Join
Left outer join returns all the records from left table and only matching records from right table.

Example of Left Outer Join:
--Display details of all Customers and order details for those customer who have place any order
select LastName, FirstName, Country, OrderID, OrderDate
from Customer Left Outer Join Orders
Customer.CustomerId = Orders.CustomerID

Right Outer Join
Right outer join returns all the records from right table and only matching records from left table.

Example of Right Outer Join:
--Display all product type and product available in it
select producttype, productname
from producttype right outer join product
producttype.producttypeid = product.producttypeid
order by producttype

Full Join or Cross Join
A cross join returns the sets of records from the two joined tables. If A and B are two sets then cross join = A X B.

Example of Full Join or Cross Join
--Display all orders and all its details
select * from orders cross join orderdetails
select * from orders, orderdetails

Self Join
A table is join to itself in a self join.

Example for Self Join:
--Display details of customer living in same state
select distinct a.* from customer a, customer b
where a.customerid <> b.customerid and
a.state = b.state
order by state

--Consider an Example of Employee Table, wherein you want to find details of all the employee drawing same salary
select distinct a.* from employee a, employee b
where a.empid <> b.empid and
a.salary = b.salary

Joining three or more table

In joining three or more tables, join applies to only one relationship, and each relationship represents an association between only two tables.
FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey
The first join applies to table1 and table2, the second join applies to table2 and table3.

Example of Joining three or more tables

--Sample syntax
select * from
table1 left outer join table2 on (table1.field1 = table2.field1),
join table3 on (table2.field2 = table3.field2)

--Display details from customer, order and orderdetails table.
select c.lastname, c.firstname, o.orderid, o.orderdate, od.orderdetailsid, p.productname
customer c Inner Join orders o on (c.customerid = o.customerid)
inner join orderdetails od on (o.orderid = od.orderid)
inner join product p on (od.productid = p.productid)
order by o.orderid, od.orderdetailsid, p.productname

Sub Queries
A query within another query is called sub query.

Example of Sub Query
--Display details of customer who haven't placed any order
select * from customer
where customerid not in (select customerid from orders)

--Display details of customer belonging to "NJ" state of "USA" country has placed order.
select * from customer
where state = 'NJ' and
customerid in (select customerid from orders)

Correlated Sub Queries

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself. Source of Information

Example of Correlated Sub Queries

--Display order information along with amount of order.

select o.orderid, o.orderdate,
(select sum(od.amount) from orderdetails od
where o.orderid = od.orderid) as "Amount"
from orders o

--Display orderdetails along with product details.
select od.orderdetailsid,
(select p.productname from product p
where od.productid = p.productid) as "ProductName",
(select p.productprice from product p
where od.productid = p.productid) as "ProductPrice",
od.quantity as "Quantity",
od.quantity * (select p.productprice from product p
where od.productid = p.productid) as "Amount"
from orderdetails od

Note: you can also use
ANY - To display any matching record within sub query
ALL - To display details of those records who match all the criteria of sub query

Saturday, October 06, 2007

Basics of SQL Server 2005

Lets begin with basics of SQL Server 2005

In this article you will learn

  • How to Create Database in SQL Server 2005
  • How to Create Table in SQL Server 2005
  • How to Create Database Diagram in SQL Server 2005
  • How to Create Relationship among Tables in SQL Server 2005
  • How to Insert Data in SQL Server 2005

First of all logged in to SQL Server 2005 Database with Administrator rights as most of the stuff we gona do require admin rights.

Creating Database in SQL Server 2005

Right click Database Folder and click Database as shown in Figure.

Note: You can also download the script file and run in newly created database

Now type your desired database name and choose appropriate settings for your desired database. I have selected all default settings here and press OK button

So our "MySampleDatabase" Is created.

While working with Windows Vista
Note: You may receive error "An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)" CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

To overcome this error in Windows Vista you need explicitly logon as Administrator as shown in figure

So after creation of Database it consist of various database object as shown in figure.

Creating Tables in SQL Server 2005 database

Now, right click the newly created MySamplePractise Database and add new table

This open the table in design mode, Lets create customer table and choose appropriate datatype, if you are not sure about choosing the correct datatype in sql server 2005 refer this article

Here, CustomerID is Identity field, that is Auto generated by system. It is similar to AutoNumber field in MS Access and Sequence in Oracle.

Understanding Identity Specification Attribute.
  • (Is Identity) - Yes : will generate autonumber
  • Identity Increment - It is number of increment you want from the previously generated ID, here i have taken 1.
  • Identity Seed - It commands SQL Server 2005 from where to start generating autonumber. Here, I have taken value 1000, so it will generate autonumber from 1001
Constraint is a check which is made on table which must be followed by user entering data. It reduce the number of Invalid data feed-in into system by prechecking its validity.

In this table 2 constraint are used that is "Allow Null" and "Primary Key"

  • Allow Null Constraint - If checked than will Allow Null values in field, else it will not allow you to bypass the column without entering data.
  • Primary Key Constraint - It checks that each record in table is unique and not null. To refer more on Keys

Creating Primary Key on Column of SQL Server 2005 Right click the column field and set the field as Primary Key

Now in similar fashion create Order Table, OrderDetails Table, Product Table, ProductType Table.

Order Table

OrderDetails Table

Product Table

ProductType Table

so your object explorer looks as under

Creating Database Diagram in SQL Server 2005

Next step select Table and press Add button

Adding a releationship among tables

Right click the "Many" Table as They don't allow to change foreign key table in Releationship dialog.

Example: Click on Orders Table, as 1 Customer can place Many Orders

Foreign Key Relationship dialog box will appear, press add button and you will find details appear on right pane of dialog box.

Now click the open dialog button on right of "Table And Columns Specification" from right pane of dialog box.

For creating relationship between Orders and Customer, CustomerID is used. Select Table "Customer" for Primary Key Table and table "Orders" for Foreign Key.

And press ok button to create relationship between two table.

Advantage of creating relationship
  • It will not allow to enter orders details without customer information. That is it allow to enter orders details of valid customer.
  • It will not allow to delete customer information, until all details associated with customer is delete.
  • You may change setting as per your need by selecting No from dropdown of "Enforce for Replication" and "Enforce foreign key constraint"
Similarly you need to create relationship between remaining tables.
  • 1 Customer can place Many Orders
  • 1 Order can have Many OrderDetails
  • 1 Product can be in Many OrderDetails
  • 1 ProductType consist of Many Product
At the end Relationship diagram looks as under

When you are dealing with large database with 100's of table, you can change the view of table by selecting all table with "ctrl+A" and right clicking any of selected table and change its views so that it is convinent to view.

A sample diagram will now look as under

So now as Database Designing and Table creation is done, lets enter some sample data

Inserting Record in Table for SQL Server 2005, right click the table and select open table, it gives you MS Access type editor to enter data. Note those who prefer inserting data through SQL Query or Script may open SQL Editor. Similarly making change in table design click modify.

Now key-in the data in customer table, as shown in figure. Note: here CustomerID is generated automatically and its value is starting from 1001 as we have set Increment Seed to 1000.

Insert Record into Customer Table.

Similarly enter data for other tables.

Remember, you don't need to enter data into CustomerID, as it is Identity field wherein data is auto generated by system. As we have enforce Referential Integrity by creating relationship, so you are not allowed to add record in orders table with anonymous customerId.

Most Recent Post

Subscribe Blog via Email

Enter your email address:

Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape