ASP.NET

Mutually exclusive checkboxes with jQuery

2011-12-21T02:49:00.000-08:00

Red:
Blue:
Green:

MS CRM QUestions

2010-08-31T23:48:00.000-07:00

What is the difference between Role Based and Object Based Security Model in MSCRM?
Code based security is entirely coded in the application itself. Role based security uses one of the authentication/authorization mechanisms of the application server.
The fundamental concept in role-based security is that of privilege, defined at design time, on a system-wide basis. Each user has a set of privileges (there are well over a hundred privileges) that are enabled for that user. However, Policies and Roles grant privileges and simplify the process.
The other form of security applies to individual instances of objects. There is a fundamental difference between an access right and a privilege: an access right is a right granted to a user on an object), but a privilege is a right granted to a user on a class of objects. Access rights apply only after privileges have taken effect. In other words, if a user does not have the privilege to read accounts, the user will not be able to read any account, whether or not it has been shared.

Difference between Find and Advanced Find? Which one is faster and Why?
Find perform a search on an attribute for which it is defined. Advanced Find perform search on the conditions and the attributes for which user customizes or runs it. Normal Find in faster as it looks for one attributes and matches with that and returns the result whereas Advanced Find searches for all the attributes and conditions while parsing through the records.
Find is applicable on only active records and it finds only on 2 or 3 column which we have defined in the find view and even it returns any those column which is there in the view but advanced find is applicable to all the records and it finds all the columns and even it returns all the column and filter criteria can be on any column and most important - find filters on just one condition but adv find filters on multiple condition at one time.. Find is faster than Advanced Find.

Difference between CRM Discovery Service and CRM Metadataservice?
CRM service - when we need ORG related information like ORG name, Properties, CRM service path for this ORG -- then we use CRM discovery service..this is only to get the ORG related information
Meta data service - we use this when we need to interact with the CRM records – create, update or delete anything-- it is very specific to the CRM records. When we need to interact with the CRM entity and attribute -- like create an entity (not record) or attribute -- add a value to pick list-- retrieve the pick list value retrieve the entity properties-- attribute property and all we need Meta data service.

Difference between Plug-in and Workflows?
RequirementPlug-inWorkflowNeeds a synchronous action to happen before or after an event occursXThe same piece of logic will be executed for different events and possibly on different entities XxThe logic needs to be executed while offlineXNeeds elevation of privileges (impersonation)XNeeds to execute on events other than assign, create, update, set stateXThe process/logic may take a long time to complete or will be a persistent process (multiple long running steps)xNeeds an asynchronous actionXxEnd users will need to modify the process logicxChild sub processes will be triggered x

Whenever you install MSCRM what all databases get created?
MSCRM_Config and MSCRM_orgname

Whenever you install MSCRM what all user groups get created in Active Directory?
UserGroupAll Microsoft CRM users . This group is updated automatically as users are added and removed from Microsoft CRM.
ReportingGroupA group that contains all users within Microsoft CRM. This group is updated as users are added to Microsoft CRM. Users in this group have read-only access to the filtered views in the Microsoft CRM database.
PrivUserGroupPrivileged Microsoft CRM user group for special administrative functions.
SQLAccessGroupA group that contains Microsoft CRM ASP.NET account and other service accounts. Members in this group have full access to the Microsoft CRM database and this group is used by the Microsoft CRM platform layer. End users should never be added to this group

Difference between MSCRM3.0 and MSCRM4.0?
Multiple organizations can now be hosted and WSDL APIs are now unique per organization in MSCRM4.0.
The metadata API service has been extended to retrieve language information in MSCRM4.0.
Plug-ins (callouts) and workflow now use the same event framework, allowing for even more extensibility.
The SDK has been expanded to include offline access.
Now we can programmatically create, read, update and delete the metadata such as entities, attributes and relationship.
There are three services instead of two which we used to have in previous version
Many to Many Relationship was not available in MSCRM3.0
Multicurrency feature was not available in MSCRM3.0
What is 'Append' and 'Append To' privilege in MSCRM? Give one example of it?Ans: 'Append' and 'Append To' priviledges works together. 'Append To' priviledge will allow other entities to get attached with the entity. 'Append' priviledge will allow the entity to attach the records to the entity with 'Append To' privildege.Let us understand this with simple example:Let us say that you want to attach a note to a case then note entity should have 'Append' access right and case entity should have 'Append To' access right.Let us take one more example to understand this. Suppose you have two custom entities called 'TestCustomEntity1' and 'TestCustomEntity2'. You want to attach the 'TestCustomeEntity2' records to 'TestCustomEntity1'records. For this you need to have 'Append' access right on 'TestCustomEntity1' entity and 'Append To' access right on 'TestCustomEntity2'.Now guess will I be able to attach the records? Answer is " NO" because we need to create a 1:N relationship between 'TestCustomEntity1' and 'TestCustomEntity2'. Now the user who has above mentioned access right in his security role will only be able to add 'TestCustomEntity2' records to 'TestCustomEntity1'.
How to create a Custom Entity record using SDK?Using Dynamic Entity.
How to join two table using Query Expression?Using Linked entity. You should always try to minimize the number of SWS calls that we make in the database. Often during code review it is explored that the number of Microsoft CRM web-service could have been reduced by making use of the Linked-entity concept. So we should always look for the opportunity to minimize the effort.
Can we modify the name of Root Business Unit?No; we will have to re-install MSCRM.
Suppose if I have 20 user license and I have created 20users. What will happen if I create 21st User?The 21st User will get created in MSCRM but that user will be in disabled state.
What is the maximum number of tabs allowed on a Microsoft Dynamics CRM 4.0 forms? 8
How to enable/disable the form assistant? How to make sure the form assistant is expanded/collapsed on a form?Navigate to Customization >> Open the Entity >> Open Forms and Views >> Open Form >> Select Form Properties >> Open Display Tab >> Check/Uncheck the " Enable the Form Assistant" and " Expanded by Default" .The interviewer will always try to figure-out whether one is comfortable with the basic concepts of Microsoft CRM (MS CRM) or not and after that; questions will be asked from your previous experience (if you have any experience in CRM). Those questions will be something like this:
What was your role in the MSCRM implementation project that you have worked on?You should be honest while giving answer to this question and should give a brief overview of the project and your role. This is very important question because the answers of this question will trigger so many questions. You should highlight the key skills you have; this way you will divert the attention of the interviewer to your key skills and try not to expose the area in which you are less confident.
What was the most challenging task you have faced till now?Here you should give answer that exhibits your positive attitude. E.g. for a technical consultant it may be something like ... " I was new to the support and during this experience i faced challenging issue related to plug-in that improved my debugging skills. Email-to-case plug-in was really difficult as we had to take care of so many conditions. I have learnt one thing during my previous assignment and that is 'Never give-up'" .
How is CRM changing and what does the business owner need to be aware of?
At the heart of CRM is the benefit of having the customer record at the center of the data universe rather than multiple galaxies of transactions held in separate, transaction-specific apps. So the innovation lies in new and improved visibility for putting information to use in intelligent decision making. Companies that used to serve 100 are serving 10,000, and with this kind of scaling, better top-level tools and custom dashboards are where I see CRM continuing to morph and advance.
19. Who are the newcomers to the CRM landscape?
It is such a hot area right now, there are literally hundreds. But two I have been following are Zoho and HighRise. Both are niche vendors that have garnered great contact lists with their other products and created relatively simple implementations for their clients and others. They both illustrate the fact that CRM is no longer esoteric — it is going mainstream, which is a great thing for business, particularly customer service and data security.
20. How much should I plan to spend on a good solution?
That’s always a tough question, given that applications vary widely in price based on whether it’s a hosted or installed delivery model, user-based or organization-wide subscription model, or a per-gigabyte or other data model. I would use the cost-per-sale and cost-per-lead values to help determine what a system is worth to a business. For most customers, services are going to be anywhere from $20 to $350 per month per user.
What exactly should I be expecting CRM to do for me?
This is an important question, as there are many misconceptions about software and CRM in particular. Besides some fundamentals, like data security and access and ease of use, CRM will primarily help you do what you do anyway, but move it to the next level. If your main focus is customer service, CRM will help you monitor, deliver, and measure your effectiveness. If your goal is a flat organization where the right hand knows immediately what the left hand is doing, CRM will help you be informed about the customer’s world and not just what relates to your department or team.
What are your expectations for CRM in the next five years?
First, I expect CRM to become much more commonplace. Players like Zoho and 37signals (Highrise) are knocking down barriers to entry. I also expect to see some consolidation. I think the bigger players, like Salesforce, Microsoft, and SAP, will buy up some of their smaller rivals to build into their suites and migrate their user bases. As long as the acquiring provider keeps the connections intact during the migration and meets a similar price point, it will be a win-win. I also think we’ll be seeing more mobile-friendly applications, like Salesforce’s Visualforce and NetSuite’s iPhone, to maximize data access and timeliness.
What are the most common mistakes you see companies make with CRM?
Many organizations use Outlook BCM or Excel for managing their contacts, which offer no planning or setup process — just create a column or type in a field and get started. This causes problems when information is related and the flexible aspects of the previous solution are overlooked. There are real benefits that won’t happen without understanding the new vernacular; the specific way the new solution describes the data. For example, an “account” in Salesforce may not be the same as an “account” in Highrise. In fact, it might have another name altogether, such as “company.” Understanding how the particular vendor uses “leads” or “opportunities” will help to avoid a great deal of frustration.
Do you have a few key best practices someone considering CRM can use?
Yes, I have three that anyone can use. First, consider your future needs. Look down the road and ask “How many contacts will I have in five years?” “How many salespeople will I have?” “How many of my people will need real-time access to this information at home or on their phones and PDAs?” “How much would it cost me to replace these contacts?”
Second, take the opportunity to clean up your data now. Moving to a CRM solution is an opportunity to start with a clean version of accurate data. De-duplicate and otherwise scrub the data to minimize the possibility of needing to import twice. For example, the flexibility of Excel and Outlook BCM allow placing incorrectly formatted information in their fields. This data will not import well without some good planning.
Third, be sure to communicate throughout the process and get early buy-in. The biggest focus of Saleforce.com with its customers is adoption. Members of your team are influencers in their departments. Leverage their expertise and influence by building a team to help you make decisions about the solution. Even if you disagree, listening, acknowledging, and respecting will build loyalty and acceptance within the process.
What advantages might CRM have for specific verticals?
The answer to this question is not if but how much. Since CRM helps you do what you do better, if you are in a professional services company with long sales cycles, project terms, and frequent interactions and touch points, CRM will be exponentially more valuable to you. So service businesses, like lawyers, consultants, and accountants, are ripe for CRM but often have a technological aversion and a strong status quo to maintain.
Does CRM fall more to sales or marketing in most organizations?
In my experience, marketing is somewhat of a new concept in CRM. Sales is definitely involved, but most often it is operations leading the charge.
What are my best resources for finding out more about CRM?
One resource I would recommend any company creates for it is a one- to two-page document that answers the best practice questions above and includes input from the team. Send it to five vendors your team has selected and go over the proposals to see which companies address you as a unique business — not just with a customizable offering but as a discrete business. Here are a few good sites I would recommend to anyone considering a solution

additonal email data

2010-04-25T10:12:00.001-07:00

additional info xslt

2010-04-25T10:10:00.001-07:00

<?xml version="1.0" encoding="UTF-8"? >
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:asp="remove" >
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:param name="requestoremail"/>
<xsl:param name="requestorlocation"/>
<xsl:param name="requestorpassword"/>
<xsl:param name="array"/>
<xsl:param name="strBreak"/>

<xsl:param name="strCurrentDate"/>

<xsl:template match="ITRAC/ServiceName">
<table border="0" cellpadding="0" cellspacing="0" width="710px" >
<tr>
<td>
<div id="content_area_Popup">
<div class="ControlHeading">
<table border="0" cellpadding="0" cellspacing="0" width="710px">
<tr>
<td>
<div id="ModalPopuprequest_heading">
<xsl:value-of select="@name"/>
</div>
</td>
</tr>
<tr>
<td>
<div id="ModalPopuprequest_Directive">
Please provide the detail request information below:
</div>
</td>
</tr>
<tr>
<td>
<div id="ServicePopupInner" >
<table border="0" cellpadding="0" cellspacing="0" class="service_contentTable" >
<tr>
<td>
<table border="0" cellpadding="2" cellspacing="0">
<tr>
<td height="5px">
</td>
</tr>
<xsl:for-each select="//Field">
<tr>
<xsl:if test="@id!='footername'">
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<asp:Label runat="server" text="{@headtext1}" CssClass="labelHeading" />
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<asp:Label runat="server" text="{@headtext2}" CssClass="labelHeading" />
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol2'">
<td style="width:10px"></td>
<td colspan="3">
<asp:Label runat="server" text="{@headtext1}" CssClass="labelHeading" />
</td>
</xsl:if>
</xsl:if>
</tr>
<tr>
<xsl:if test="@id!='footername'">
<td style ="width:10px; padding:3px 3px 3px 3px;" >

<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkbox'">
<asp:CheckBox id="{@checkboxid}" runat="server" />
</xsl:if>
</td>
<td>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkboxindent1'">
<asp:CheckBox id="{@checkboxid}" runat="server" />
</xsl:if>
</td>
<td class="labelText" valign="middle" style ="padding-left:5px">
<xsl:variable name="strNew"></xsl:variable>
<xsl:choose>
<xsl:when test="contains(@label, '\n')">
<xsl:value-of select="substring-before(@label, '\n')"/>
<br/>
<p class="SubTextInPopUp"><xsl:value-of select="substring-after(@label, '\n')"/></p>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@label"/>
</xsl:otherwise>
</xsl:choose>
<asp:Label runat="server" text="{$strNew}"/>
</td>
<td style="width:5px">
</td>
<td valign="top" style ="padding:3px 3px 3px 3px;" >
<xsl:if test="@type = 'Label' and @SecondColumn='true'">
<asp:Label id ="{@id}" runat="server" text="{@label}" Font-Bold="true" />
</xsl:if>
<xsl:if test="@type = 'TextArea'">
<asp:TextBox id="{@id}" runat="server" text="" CssClass="inputboxpopup" height="100px" TextMode="MultiLine" />
</xsl:if>
<xsl:if test="@type = 'Date'">
<asp:TextBox id="{@id}" runat="server" CssClass="date-pick" />
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false'">
<asp:TextBox id="{@id}" runat="server" text="" CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoremail'">
<asp:TextBox id="{@id}" runat="server" text="{$requestoremail}" CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorlocation'">
<asp:TextBox id="{@id}" runat="server" text="{$requestorlocation}" CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorregion'">
<asp:TextBox id="{@id}" runat="server" text=" " CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoroffice'">
<asp:TextBox id="{@id}" runat="server" text=" " CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestordivision'">
<asp:TextBox id="{@id}" runat="server" text=" " CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorpassword'">
<asp:TextBox id="{@id}" runat="server" text="{$requestorpassword}" CssClass="inputboxpopup" readonly="false" />
</xsl:if>
<xsl:if test="@type = 'TextBoxText' and @IsActiveDirectory='true'">
<asp:TextBox id="{@id}" runat="server" Text="{@text}" CssClass="inputboxpopup" />
</xsl:if>
<xsl:if test="@type = 'CheckBoxList'">
<asp:CheckBoxList id="{@id}" runat="server" RepeatDirection="Vertical" RepeatLayout="Table" RepeatColumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" CellSpacing="10px" style="padding:0 35px 8px 1px; vertical-align:top; margin: 0; float:left;"></asp:ListItem>
</xsl:for-each>
</asp:CheckBoxList>
</xsl:if>
<xsl:if test="@type = 'DropDownList' and @IsActiveDirectory='false'">
<asp:DropDownList id="{@id}" runat="server" style="width:209px;">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" ></asp:ListItem>
</xsl:for-each>
</asp:DropDownList>
</xsl:if>
<xsl:if test="@type = 'ListBox'">
<asp:ListBox id="{@id}" runat="server" SelectionMode="Multiple" style="width:209px;">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" ></asp:ListItem>
</xsl:for-each>
</asp:ListBox>
</xsl:if>

<xsl:if test="@type = 'RadioButtonList'">
<asp:RadioButtonList id="{@id}" runat="server" RepeatDirection="Vertical" RepeatLayout="Table" RepeatColumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" style="padding:0 35px 8px 1px; vertical-align:top; margin: 0; float:left;" ></asp:ListItem>
</xsl:for-each>
</asp:RadioButtonList>
</xsl:if>

<div style="padding-left:20px;">
<xsl:if test="@type = 'RadioButtonListIndent2'">
<asp:RadioButtonList id="{@id}" runat="server" RepeatDirection="Vertical" RepeatLayout="Table" RepeatColumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" style="padding:0 35px 8px 1px; vertical-align:top; margin: 0; float:left;" ></asp:ListItem>
</xsl:for-each>
</asp:RadioButtonList>
</xsl:if>
</div>
</td>
</xsl:if>
<xsl:if test="@type = 'label' and @id='footername'">
<td class="labelHeading" colspan="6" align="left" style="padding-left:60px;">
<asp:Label runat="server" text="{@label}"/>
</td>
</xsl:if>
</tr>
</xsl:for-each>
</table>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>
</div>
</td>
</tr>
</table>
</xsl:template>
<xsl:template name="break">
<xsl:param name="text" select="."/>
<xsl:choose>
<xsl:when test="contains($text, ' ')">
<xsl:value-of select="substring-before($text, ' ')"/>
<br/>
<xsl:call-template name="break">
<xsl:with-param name="label" select="substring-after($text, ' ')"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>

additional info plain data

2010-04-25T10:09:00.000-07:00

audit report.xslt

2010-04-25T10:08:00.002-07:00

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/">
<xsl:apply-templates select="Data"/>

</xsl:template>

<xsl:template match="Data">

<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="s24">
<Interior ss:Color="#083eb8" ss:Pattern="Solid"/>
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:Bold="1" ss:Color="#ffffff"/>

</Style>
</Styles>

<Worksheet ss:Name="Audit Report Data" >
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="250.25"/>
<Column ss:Width="100"/>
<Column ss:Width="150.5"/>
<Column ss:Width="113.25"/>
<Column ss:Width="110.25"/>

<xsl:for-each select="Fiscal">
<Row>
<xsl:for-each select="FiscalData">

<Cell >
<Data ss:Type="String">
<xsl:value-of select="text()" />
</Data>
</Cell>
</xsl:for-each>
</Row>
</xsl:for-each>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

<Print>

<ValidPrinterInfo/>

<HorizontalResolution>600</HorizontalResolution>

<VerticalResolution>600</VerticalResolution>

</Print>

<Selected/>

<Panes>

<Pane>

<Number>3</Number>

<ActiveRow>12</ActiveRow>

<ActiveCol>1</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>

update additional info

2010-04-25T10:05:00.001-07:00

XSL

2010-04-21T00:29:00.000-07:00

<?xml:namespace prefix = xsl /><xsl:stylesheet version="1.0" xsl="http://www.w3.org/1999/XSL/Transform" asp="remove">
<xsl:output version="1.0" indent="yes" encoding="UTF-8" method="xml"></xsl:output>
<xsl:param name="session_name"></xsl:param>
<xsl:template match="ServiceName">
<table cellspacing="0" cellpadding="0" width="700" border="0">
<tbody><tr>
<td>
<div id="content_area">
<div class="ControlHeading">
<table cellspacing="0" cellpadding="0" width="700" border="0">
<tbody><tr>
<td>
<div id="request_heading" style="TEXT-ALIGN: left">

</div>
</td>
</tr>
<tr>
<td>
<table class="service_contentTable" style="TEXT-ALIGN: left" cellspacing="0" cellpadding="0" border="0">
<tbody><tr>
<td>
<table cellspacing="0" cellpadding="0" border="0">
<tbody><tr>
<td height="5">
</td>
</tr>
<xsl:for-each select="//Field">
<tr>
<td class="labelHeading">
<xsl:choose>
<xsl:when test="contains(@label, '\n')">
<xsl:value-of select="substring-before(@label, '\n')"></xsl:value-of>
<xsl:value-of select="substring-after(@label, '\n')"></xsl:value-of>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@label"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
</td>
<td style="WIDTH: 5px">
</td>
<td>
<xsl:if test="@type = 'TextBox'">

<xsl:value-of select="@value"></xsl:value-of>
</xsl:if>
<xsl:if test="@type = 'CheckBoxList'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

<xsl:if test="@type = 'DropDownList'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

<xsl:if test="@type = 'ListBox'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

<xsl:if test="@type = 'RadioButtonList'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

</td>
</tr>
</xsl:for-each>
</tbody></table>
</td>
</tr>
</tbody></table>
</td>
</tr>
</tbody></table>
</div>
</div>
</td>
</tr>
</tbody></table>
</xsl:template>
</xsl:stylesheet>

-----------------------

<xsl:stylesheet version="1.0" xsl="http://www.w3.org/1999/XSL/Transform" asp="remove">
<xsl:output version="1.0" indent="yes" encoding="UTF-8" method="xml"></xsl:output>
<xsl:param name="requestoremail"></xsl:param>
<xsl:param name="requestorlocation"></xsl:param>
<xsl:param name="requestorpassword"></xsl:param>
<xsl:param name="array"></xsl:param>
<xsl:param name="strBreak"></xsl:param>

<xsl:param name="strCurrentDate"></xsl:param>

<xsl:template match="ITRAC/ServiceName">
<table cellspacing="0" cellpadding="0" width="710" border="0">
<tbody><tr>
<td>
<div id="content_area_Popup">
<div class="ControlHeading">
<table cellspacing="0" cellpadding="0" width="710" border="0">
<tbody><tr>
<td>
<div id="ModalPopuprequest_heading">
<xsl:value-of select="@name"></xsl:value-of>
</div>
</td>
</tr>
<tr>
<td>
<div id="ModalPopuprequest_Directive">
Please provide the detail request information below:
</div>
</td>
</tr>
<tr>
<td>
<div id="ServicePopupInner">
<table class="service_contentTable" cellspacing="0" cellpadding="0" border="0">
<tbody><tr>
<td>
<table cellspacing="0" cellpadding="2" border="0">
<tbody><tr>
<td height="5">
</td>
</tr>
<xsl:for-each select="//Field">
<tr>
<xsl:if test="@id!='footername'">
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<?xml:namespace prefix = asp /><asp:label text="{@headtext1}" runat="server" cssclass="labelHeading"></asp:label>
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<asp:label text="{@headtext2}" runat="server" cssclass="labelHeading"></asp:label>
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol2'">
<td style="WIDTH: 10px"></td>
<td colspan="3">
<asp:label text="{@headtext1}" runat="server" cssclass="labelHeading"></asp:label>
</td>
</xsl:if>
</xsl:if>
</tr>
<tr>
<xsl:if test="@id!='footername'">
<td style="PADDING-RIGHT: 3px; PADDING-LEFT: 3px; PADDING-BOTTOM: 3px; WIDTH: 10px; PADDING-TOP: 3px">

<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkbox'">
<asp:checkbox id="{@checkboxid}" runat="server"></asp:checkbox>
</xsl:if>
</td>
<td>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkboxindent1'">
<asp:checkbox id="{@checkboxid}" runat="server"></asp:checkbox>
</xsl:if>
</td>
<td class="labelText" style="PADDING-LEFT: 5px" valign="center">
<xsl:variable name="strNew"></xsl:variable>
<xsl:choose>
<xsl:when test="contains(@label, '\n')">
<xsl:value-of select="substring-before(@label, '\n')"></xsl:value-of>

<p class="SubTextInPopUp"><xsl:value-of select="substring-after(@label, '\n')"></xsl:value-of></p>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@label"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
<asp:label text="{$strNew}" runat="server"></asp:label>
</td>
<td style="WIDTH: 5px">
</td>
<td style="PADDING-RIGHT: 3px; PADDING-LEFT: 3px; PADDING-BOTTOM: 3px; PADDING-TOP: 3px" valign="top">
<xsl:if test="@type = 'Label' and @SecondColumn='true'">
<asp:label id="{@id}" text="{@label}" runat="server" bold="true"></asp:label>
</xsl:if>
<xsl:if test="@type = 'TextArea'">
<asp:textbox id="{@id}" text="" runat="server" height="100px" cssclass="inputboxpopup" textmode="MultiLine"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'Date'">
<asp:textbox id="{@id}" text="{$strCurrentDate}" runat="server" cssclass="date-pick"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false'">
<asp:textbox id="{@id}" text="" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoremail'">
<asp:textbox id="{@id}" text="{$requestoremail}" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorlocation'">
<asp:textbox id="{@id}" text="{$requestorlocation}" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorregion'">
<asp:textbox id="{@id}" text=" " runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoroffice'">
<asp:textbox id="{@id}" text=" " runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestordivision'">
<asp:textbox id="{@id}" text=" " runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorpassword'">
<asp:textbox id="{@id}" text="{$requestorpassword}" runat="server" readonly="false" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBoxText' and @IsActiveDirectory='true'">
<asp:textbox id="{@id}" text="{@text}" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'CheckBoxList'">
<asp:checkboxlist id="{@id}" runat="server" repeatdirection="Vertical" repeatlayout="Table" repeatcolumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem style="PADDING-RIGHT: 35px; PADDING-LEFT: 1px; FLOAT: left; PADDING-BOTTOM: 8px; MARGIN: 0px; VERTICAL-ALIGN: top; PADDING-TOP: 0px" text="{@text}" value="{@value}" selected="{@selected}" cellspacing="10px"></asp:listitem>
</xsl:for-each>
</asp:checkboxlist>
</xsl:if>
<xsl:if test="@type = 'DropDownList' and @IsActiveDirectory='false'">
<asp:dropdownlist id="{@id}" style="WIDTH: 209px" runat="server">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:dropdownlist>
</xsl:if>
<xsl:if test="@type = 'ListBox'">
<asp:listbox id="{@id}" style="WIDTH: 209px" runat="server" selectionmode="Multiple">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:listbox>
</xsl:if>

<xsl:if test="@type = 'RadioButtonList'">
<asp:radiobuttonlist id="{@id}" runat="server" repeatdirection="Vertical" repeatlayout="Table" repeatcolumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem style="PADDING-RIGHT: 35px; PADDING-LEFT: 1px; FLOAT: left; PADDING-BOTTOM: 8px; MARGIN: 0px; VERTICAL-ALIGN: top; PADDING-TOP: 0px" text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:radiobuttonlist>
</xsl:if>

<div style="PADDING-LEFT: 20px">
<xsl:if test="@type = 'RadioButtonListIndent2'">
<asp:radiobuttonlist id="{@id}" runat="server" repeatdirection="Vertical" repeatlayout="Table" repeatcolumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem style="PADDING-RIGHT: 35px; PADDING-LEFT: 1px; FLOAT: left; PADDING-BOTTOM: 8px; MARGIN: 0px; VERTICAL-ALIGN: top; PADDING-TOP: 0px" text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:radiobuttonlist>
</xsl:if>
</div>
</td>
</xsl:if>
<xsl:if test="@type = 'label' and @id='footername'">
<td class="labelHeading" style="PADDING-LEFT: 60px" align="left" colspan="6">
<asp:label text="{@label}" runat="server"></asp:label>
</td>
</xsl:if>
</tr>
</xsl:for-each>
</tbody></table>
</td>
</tr>
</tbody></table>
</div>
</td>
</tr>
</tbody></table>
</div>
</div>
</td>
</tr>
</tbody></table>
</xsl:template>
<xsl:template name="break">
<xsl:param name="text" select="."></xsl:param>
<xsl:choose>
<xsl:when test="contains($text, ' ')">
<xsl:value-of select="substring-before($text, ' ')"></xsl:value-of>

<xsl:call-template name="break">
<xsl:with-param name="label" select="substring-after($text, ' ')"></xsl:with-param>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>

----------------

xsl v

2010-04-21T00:21:00.000-07:00

A potentially dangerous Request.Form value was detected from the client (TextBoxN="...")

2010-02-22T23:23:00.000-08:00

Set ValidateRequest="false"

Unless you actually need users to be able to enter HTML you must convert the string to its html encoding equivalent - basically this means that certain characters (like "<") are converted to codes (so "<" is converted to "<"). To perform this conversion use HttpUtility.HtmlEncode, for example:
MyLabel.Text := HttpUtility.HtmlEncode(MyTextBox.Text);

public static string ConvertStringToHTML(string OldString)
{
string NewString="";
NewString=OldString.Replace("&","&");//this should be first, next & will come for < and >.
NewString=NewString.Replace("<","<").Replace(">",">");
return NewString;
}
public static string ConvertHTMLToString(string OldString)
{
string NewString="";
NewString=OldString.Replace("&","&");
NewString=NewString.Replace("<","<").Replace(">",">");
return NewString;
}

How to format datetime & date in Sql Server 2005

2010-02-16T08:52:00.000-08:00

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

– Oct 2 2008 11:01AM

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

– Oct 2 2008 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

– 02 Oct 2008 11:02:07:577

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

– 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy

————

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

DECLARE @StringDate VARCHAR(32)

SET @StringDate = @FormatMask

IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

DATENAME(YY, @Datetime))

IF (CHARINDEX (‘YY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YY’,

RIGHT(DATENAME(YY, @Datetime),2))

IF (CHARINDEX (‘Month’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Month’,

DATENAME(MM, @Datetime))

IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

SET @StringDate = REPLACE(@StringDate, ‘MON’,

LEFT(UPPER(DATENAME(MM, @Datetime)),3))

IF (CHARINDEX (‘Mon’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Mon’,

LEFT(DATENAME(MM, @Datetime),3))

IF (CHARINDEX (‘MM’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘MM’,

RIGHT(‘0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

IF (CHARINDEX (‘M’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘M’,

CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

IF (CHARINDEX (‘DD’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘DD’,

RIGHT(‘0′+DATENAME(DD, @Datetime),2))

IF (CHARINDEX (‘D’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘D’,

DATENAME(DD, @Datetime))

RETURN @StringDate

END

GO

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’) – 12.01.03

GO

————

/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

– SQL format datetime

– Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

– US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

– ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

– UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

– German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

– ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

– ISO8601 format: 2008-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

————

– SQL Server datetime formats

– Century date format MM/DD/YYYY usage in a query

– Format dates SQL Server 2005

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(char(10), OrderDate, 101),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

/* Result

SalesOrderID OrderDate OrderDateTime

43697 07/01/2001 2001-07-01 00:00:00.000

*/

– SQL update datetime column

– SQL datetime DATEADD

UPDATE Production.Product

SET ModifiedDate=DATEADD(dd,1, ModifiedDate)

WHERE ProductID = 1001

– MM/DD/YY date format

– Datetime format sql

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(varchar(8), OrderDate, 1),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY SalesOrderID desc

/* Result

SalesOrderID OrderDate OrderDateTime

75123 07/31/04 2004-07-31 00:00:00.000

*/

– Combining different style formats for date & time

– Datetime formats

– Datetime formats sql

DECLARE @Date DATETIME

SET @Date = ‘2015-12-22 03:51 PM’

SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)

– Result: 12-22-2015 3:51PM

– Microsoft SQL Server cast datetime to string

SELECT stringDateTime=CAST (getdate() as varchar)

– Result: Dec 29 2012 3:47AM

————

– SQL Server date and time functions overview

————

– SQL Server CURRENT_TIMESTAMP function

– SQL Server datetime functions

– local NYC – EST – Eastern Standard Time zone

– SQL DATEADD function – SQL DATEDIFF function

SELECT CURRENT_TIMESTAMP – 2012-01-05 07:02:10.577

– SQL Server DATEADD function

SELECT DATEADD(month,2,‘2012-12-09′) – 2013-02-09 00:00:00.000

– SQL Server DATEDIFF function

SELECT DATEDIFF(day,‘2012-12-09′,‘2013-02-09′) – 62

– SQL Server DATENAME function

SELECT DATENAME(month, ‘2012-12-09′) – December

SELECT DATENAME(weekday, ‘2012-12-09′) – Sunday

– SQL Server DATEPART function

SELECT DATEPART(month, ‘2012-12-09′) – 12

– SQL Server DAY function

SELECT DAY(‘2012-12-09′) – 9

– SQL Server GETDATE function

– local NYC – EST – Eastern Standard Time zone

SELECT GETDATE() – 2012-01-05 07:02:10.577

– SQL Server GETUTCDATE function

– London – Greenwich Mean Time

SELECT GETUTCDATE() – 2012-01-05 12:02:10.577

– SQL Server MONTH function

SELECT MONTH(‘2012-12-09′) – 12

– SQL Server YEAR function

SELECT YEAR(‘2012-12-09′) – 2012

————

– T-SQL Date and time function application

– CURRENT_TIMESTAMP and getdate() are the same in T-SQL

————

– SQL first day of the month

– SQL first date of the month

– SQL first day of current month – 2012-01-01 00:00:00.000

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of the month

– SQL last date of the month

– SQL last day of current month – 2012-01-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))

– SQL first day of last month

– SQL first day of previous month – 2011-12-01 00:00:00.000

SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of last month

– SQL last day of previous month – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))

– SQL first day of next month – 2012-02-01 00:00:00.000

SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of next month – 2012-02-28 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))

GO

– SQL first day of a month – 2012-10-01 00:00:00.000

DECLARE @Date datetime; SET @Date = ‘2012-10-23′

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))

GO

– SQL last day of a month – 2012-03-31 00:00:00.000

DECLARE @Date datetime; SET @Date = ‘2012-03-15′

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))

GO

– SQL first day of year

– SQL first day of the year - 2012-01-01 00:00:00.000

SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)

– SQL last day of year

– SQL last day of the year – 2012-12-31 00:00:00.000

SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,

DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))

– SQL last day of last year

– SQL last day of previous year – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))

GO

– SQL calculate age in years, months, days

– SQL table-valued function

– SQL user-defined function – UDF

– SQL Server age calculation – date difference

– Format dates SQL Server 2008

USE AdventureWorks2008;

GO

CREATE FUNCTION fnAge (@BirthDate DATETIME)

RETURNS @Age TABLE(Years INT,

Months INT,

Days INT)

AS

BEGIN

DECLARE @EndDate DATETIME, @Anniversary DATETIME

SET @EndDate = Getdate()

SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)

INSERT @Age

SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE

WHEN @Anniversary > @EndDate THEN 1

ELSE 0

END), 0, 0

UPDATE @Age SET Months = Month(@EndDate - @Anniversary) - 1

UPDATE @Age SET Days = Day(@EndDate - @Anniversary) - 1

RETURN

END

GO

– Test table-valued UDF

SELECT * FROM fnAge(‘1956-10-23′)

SELECT * FROM dbo.fnAge(‘1956-10-23′)

/* Results

Years Months Days

52 4 1

*/

———-

– SQL date range between

———-

– SQL between dates

USE AdventureWorks;

– SQL between

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN ‘20040301′ AND ‘20040315′

– Result: 108

– BETWEEN operator is equivalent to >=…AND….<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN ‘2004-03-01 00:00:00.000′ AND ‘2004-03-15 00:00:00.000′

/*

Orders with OrderDates

‘2004-03-15 00:00:01.000′ – 1 second after midnight (12:00AM)

‘2004-03-15 00:01:00.000′ – 1 minute after midnight

‘2004-03-15 01:00:00.000′ – 1 hour after midnight

are not included in the two queries above.

*/

– To include the entire day of 2004-03-15 use the following two solutions

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= ‘20040301′ AND OrderDate < ‘20040316′

– SQL between with DATE type (SQL Server 2008)

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE CONVERT(DATE, OrderDate) BETWEEN ‘20040301′ AND ‘20040315′

———-

– Non-standard format conversion: 2011 December 14

– SQL datetime to string

SELECT [YYYY Month DD] =

CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+

DATENAME(MM, GETDATE()) + ‘ ‘ +

CAST(DAY(GETDATE()) AS VARCHAR(2))

– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638

SELECT replace(convert(varchar, getdate(),111),‘/’,”) +

replace(convert(varchar, getdate(),108),‘:’,”)

– Datetime custom format conversion to YYYY_MM_DD

select CurrentDate=rtrim(year(getdate())) + ‘_’ +

right(‘0′ + rtrim(month(getdate())),2) + ‘_’ +

right(‘0′ + rtrim(day(getdate())),2)

– Converting seconds to HH:MM:SS format

declare @Seconds int

set @Seconds = 10000

select TimeSpan=right(‘0′ +rtrim(@Seconds / 3600),2) + ‘:’ +

right(‘0′ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +

right(‘0′ + rtrim(@Seconds % 60),2)

– Result: 02:46:40

– Test result

select 2*3600 + 46*60 + 40

– Result: 10000

– Set the time portion of a datetime value to 00:00:00.000

– SQL strip time from date

– SQL strip time from datetime

SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000

/*******

VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

SMALLDATETIME date range:

January 1, 1900 through June 6, 2079

DATETIME date range:

January 1, 1753 through December 31, 9999

DATETIME2 date range (SQL Server 2008):

January 1,1 AD through December 31, 9999 AD

DATE date range (SQL Server 2008):

January 1, 1 AD through December 31, 9999 AD

*******/

– Selecting with CONVERT into different styles

– Note: Only Japan & ISO styles can be used in ORDER BY

SELECT TOP(1)

Italy = CONVERT(varchar, OrderDate, 105)

, USA = CONVERT(varchar, OrderDate, 110)

, Japan = CONVERT(varchar, OrderDate, 111)

, ISO = CONVERT(varchar, OrderDate, 112)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY PurchaseOrderID DESC

/* Results

Italy USA Japan ISO

25-07-2004 07-25-2004 2004/07/25 20040725

*/

– SQL Server convert date to integer

DECLARE @Datetime datetime

SET @Datetime = ‘2012-10-23 10:21:05.345′

SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)

– Result: 20121023

– SQL Server convert integer to datetime

DECLARE @intDate int

SET @intDate = 20120315

SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)

– Result: 2012-03-15 00:00:00.000

————

– SQL Server CONVERT script applying table INSERT/UPDATE

————

– SQL Server convert date

– Datetime column is converted into date only string column

USE tempdb;

GO

CREATE TABLE sqlConvertDateTime (

DatetimeCol datetime,

DateCol char(8));

INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

UPDATE sqlConvertDateTime

SET DateCol = CONVERT(char(10), DatetimeCol, 112)

SELECT * FROM sqlConvertDateTime

– SQL Server convert datetime

– The string date column is converted into datetime column

UPDATE sqlConvertDateTime

SET DatetimeCol = CONVERT(Datetime, DateCol, 112)

SELECT * FROM sqlConvertDateTime

– Adding a day to the converted datetime column with DATEADD

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))

SELECT * FROM sqlConvertDateTime

– Equivalent formulation

– SQL Server cast datetime

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))

SELECT * FROM sqlConvertDateTime

GO

DROP TABLE sqlConvertDateTime

GO

/* First results

DatetimeCol DateCol

2014-12-25 16:04:15.373 20141225 */

/* Second results:

DatetimeCol DateCol

2014-12-25 00:00:00.000 20141225 */

/* Third results:

DatetimeCol DateCol

2014-12-26 00:00:00.000 20141225 */

————

– SQL month sequence – SQL date sequence generation with table variable

– SQL Server cast string to datetime – SQL Server cast datetime to string

– SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

DECLARE @StartDate datetime;

SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+

RIGHT(‘0′+convert(varchar,month(getdate())),2) + ‘01′ AS DATETIME)

WHILE ( @i < 120)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)

FROM @Sequence

GO

/* Partial results:

MonthSequence

Jan 1 2012 12:00AM

Feb 1 2012 12:00AM

Mar 1 2012 12:00AM

Apr 1 2012 12:00AM

*/

————

————

– SQL Server Server datetime internal storage

– SQL Server datetime formats

————

– SQL Server datetime to hex

SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))

/* Results

Now HexNow

2009-01-02 17:35:59.297 0×00009B850122092D

*/

– SQL Server date part – left 4 bytes – Days since 1900-01-01

SELECT Now=DATEADD(DAY, CONVERT(INT, 0×00009B85), ‘19000101′)

GO

– Result: 2009-01-02 00:00:00.000

– SQL time part – right 4 bytes – milliseconds since midnight

– 1000/300 is an adjustment factor

– SQL dateadd to Midnight

SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0×0122092D), ‘2009-01-02′)

GO

– Result: 2009-01-02 17:35:59.290

————

————

– String date and datetime date&time columns usage

– SQL Server datetime formats in tables

————

USE tempdb;

SET NOCOUNT ON;

– SQL Server select into table create

SELECT TOP (5)

FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),

BirthDate = CONVERT(char(8), BirthDate,112),

ModifiedDate = getdate()

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

ON c.ContactID = e.ContactID

ORDER BY EmployeeID

GO

– SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

GO

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

GO

/* Results

Table definition for the Employee table

Note: BirthDate is string date (only)

CREATE TABLE dbo.Employee(

FullName nvarchar(50) NOT NULL PRIMARY KEY,

BirthDate char(8) NULL,

ModifiedDate datetime NOT NULL

)

*/

SELECT * FROM Employee ORDER BY FullName

GO

/* Results

FullName BirthDate ModifiedDate

Guy Gilbert 19720515 2009-01-03 10:10:19.217

Kevin Brown 19770603 2009-01-03 10:10:19.217

Rob Walters 19650123 2009-01-03 10:10:19.217

Roberto Tamburello 19641213 2009-01-03 10:10:19.217

Thierry D’Hers 19490829 2009-01-03 10:10:19.217

*/

– SQL Server age

SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),

RowMaintenanceDate = CAST (ModifiedDate AS varchar)

FROM Employee ORDER BY FullName

GO

/* Results

FullName Age RowMaintenanceDate

Guy Gilbert 37 Jan 3 2009 10:10AM

Kevin Brown 32 Jan 3 2009 10:10AM

Rob Walters 44 Jan 3 2009 10:10AM

Roberto Tamburello 45 Jan 3 2009 10:10AM

Thierry D’Hers 60 Jan 3 2009 10:10AM

*/

– SQL Server age of Rob Walters on specific dates

– SQL Server string to datetime implicit conversion with DATEADD

SELECT AGE50DATE = DATEADD(YY, 50, ‘19650123′)

GO

– Result: 2015-01-23 00:00:00.000

– SQL Server datetime to string, Italian format for ModifiedDate

– SQL Server string to datetime implicit conversion with DATEDIFF

SELECT FullName,

AgeDEC31 = DATEDIFF(YEAR, BirthDate, ‘20141231′),

AgeJAN01 = DATEDIFF(YEAR, BirthDate, ‘20150101′),

AgeJAN23 = DATEDIFF(YEAR, BirthDate, ‘20150123′),

AgeJAN24 = DATEDIFF(YEAR, BirthDate, ‘20150124′),

ModDate = CONVERT(varchar, ModifiedDate, 105)

FROM Employee

WHERE FullName = ‘Rob Walters’

ORDER BY FullName

GO

/* Results

Important Note: age increments on Jan 1 (not as commonly calculated)

FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDate

Rob Walters 49 50 50 50 03-01-2009

*/

————

– SQL combine integer date & time into datetime

————

– Datetime format sql

– SQL stuff

DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,

DateAsINT int,

TimeAsINT int

)

– NOTE: leading zeroes in time is for readability only!

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)

SELECT DateAsINT, TimeAsINT,

CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+

STUFF(STUFF ( RIGHT(REPLICATE(‘0′, 6) + CONVERT(varchar(6), TimeAsINT), 6),

3, 0, ‘:’), 6, 0, ‘:’)) AS DateTimeValue

FROM @DateTimeAsINT

ORDER BY ID

GO

/* Results

DateAsINT TimeAsINT DateTimeValue

20121023 235959 2012-10-23 23:59:59.000

20121023 10204 2012-10-23 01:02:04.000

20121023 2350 2012-10-23 00:23:50.000

20121023 244 2012-10-23 00:02:44.000

20121023 50 2012-10-23 00:00:50.000

20121023 6 2012-10-23 00:00:06.000

*/

————

– SQL Server string to datetime, implicit conversion with assignment

UPDATE Employee SET ModifiedDate = ‘20150123′

WHERE FullName = ‘Rob Walters’

GO

SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 2015-01-23 00:00:00.000

/* SQL string date, assemble string date from datetime parts */

– SQL Server cast string to datetime – sql convert string date

– SQL Server number to varchar conversion

– SQL Server leading zeroes for month and day

– SQL Server right string function

UPDATE Employee SET BirthDate =

CONVERT(char(4),YEAR(CAST(‘1965-01-23′ as DATETIME)))+

RIGHT(‘0′+CONVERT(varchar,MONTH(CAST(‘1965-01-23′ as DATETIME))),2)+

RIGHT(‘0′+CONVERT(varchar,DAY(CAST(‘1965-01-23′ as DATETIME))),2)

WHERE FullName = ‘Rob Walters’

GO

SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 19650123

– Perform cleanup action

DROP TABLE Employee

– SQL nocount

SET NOCOUNT OFF;

GO

————

————

– sql isdate function

————

USE tempdb;

– sql newid – random sort

SELECT top(3) SalesOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM DateValidation

/* Results

SalesOrderID stringOrderDate

56720 Oct 26 2003 12:00AM

73737 Jun 25 2004 12:00AM

70573 May 14 2004 12:00AM

*/

– SQL update with top

UPDATE TOP(1) DateValidation

SET stringOrderDate = ‘Apb 29 2004 12:00AM’

GO

– SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

GO

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an

out-of-range value.

*/

– sql isdate – filter for valid dates

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

GO

/* Results

SalesOrderID OrderDate

73737 2004-06-25 00:00:00.000

70573 2004-05-14 00:00:00.000

*/

– SQL drop table

DROP TABLE DateValidation

Go

————

– SELECT between two specified dates – assumption TIME part is 00:00:00.000

————

– SQL datetime between

– SQL select between two dates

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ‘1997-11-01′ AND

RateChangeDate < DATEADD(dd,1,‘1998-01-05′)

GO

/* Results

EmployeeID RateChangeDate

3 1997-12-12 00:00:00.000

4 1998-01-05 00:00:00.000

*/

/* Equivalent to

– SQL datetime range

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ‘1997-11-01 00:00:00′ AND

RateChangeDate < ‘1998-01-06 00:00:00′

GO

*/

————

– SQL datetime language setting

– SQL Nondeterministic function usage – result varies with language settings

SET LANGUAGE ‘us_english’; –– Jan 12 2015 12:00AM

SELECT US = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘British’; –– Dec 1 2015 12:00AM

SELECT UK = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘German’; –– Dez 1 2015 12:00AM

SET LANGUAGE ‘Deutsch’; –– Dez 1 2015 12:00AM

SELECT Germany = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘French’; –– déc 1 2015 12:00AM

SELECT France = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘Spanish’; –– Dic 1 2015 12:00AM

SELECT Spain = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘Hungarian’; –– jan 12 2015 12:00AM

SELECT Hungary = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘us_english’;

GO

————

————

– Function for Monday dates calculation

————

USE AdventureWorks2008;

GO

– SQL user-defined function

– SQL scalar function – UDF

CREATE FUNCTION fnMondayDate

(@Year INT,

@Month INT,

@MondayOrdinal INT)

RETURNS DATETIME

AS

BEGIN

DECLARE @FirstDayOfMonth CHAR(10),

@SeedDate CHAR(10)

SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-’ + convert(VARCHAR,@Month) + ‘-01′

SET @SeedDate = ‘1900-01-01′

RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

@FirstDayOfMonth)) / 7 * 7, @SeedDate)

END

GO

– Test Datetime UDF

– Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2016,2,3)

– 2015-02-16 00:00:00.000

– First Monday of current month

SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)

– 2009-02-02 00:00:00.000

————

Gridview Footer Page

2009-11-08T23:33:00.000-08:00

#region Namespaces
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Text;
#endregion

/// <summary&rt;
/// Summary description for GridViewPageFooter
/// </summary&rt;
public class GridViewPageFooter
{
public GridViewPageFooter()
{
//
// TODO: Add constructor logic here
//
}
public static void GetGridViewPager(GridView gv, GridViewRow gvr)
{
if ((PlaceHolder)gvr.FindControl("phPager") == null)
{
throw new ApplicationException("A placeholder with an ID=\"phPager\" is required for this to work");
}

PlaceHolder ph = (PlaceHolder)gvr.FindControl("phPager");

// html markup and style
StringBuilder sb = new StringBuilder();
sb.Append("<div style=\"float:left;\"&rt;");
sb.Append(string.Format("<span class=\"pagerCurrentPage\"&rt;Page: {0} of {1}</span&rt;", (gv.PageIndex + 1).ToString(), gv.PageCount));

Literal lit = new Literal();
lit.Text = sb.ToString();
ph.Controls.Add(lit);

//adding the table
//StringBuilder sbTable = new StringBuilder();
//sbTable.Append("width=\"100\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"left\"&rt;");
//sbTable.Append("

// Previous page button
ImageButton btnImage = new ImageButton();
btnImage.ID = "btnPrevPage";
btnImage.CommandName = "Page";
btnImage.CommandArgument = "Prev";
btnImage.ToolTip = "Previous Page";
//btnImage.ImageAlign = ImageAlign.Middle;

if (gv.PageIndex == 0)
{
btnImage.Enabled = false;
btnImage.ImageUrl = "~/Images/prev_icon.jpg";
}
else
{
btnImage.Enabled = true;
btnImage.ImageUrl = "~/Images/prev_icon.jpg";
//btnImage.OnClientClick = "GeneaSys.Common.addProgressText('Previous grid page...');";
btnImage.Click += new ImageClickEventHandler(btn_Click2);
}

ph.Controls.Add(btnImage);

// Pager page links
LinkButton lnb;
int numberOfPageLinks = gv.PagerSettings.PageButtonCount;
int startPage = (((gv.PageIndex) / numberOfPageLinks) * numberOfPageLinks) + 1;
if (startPage &rt; gv.PageCount - numberOfPageLinks && startPage &rt; numberOfPageLinks) startPage = (gv.PageCount - numberOfPageLinks) + 1;
for (int i = startPage; i < numberOfPageLinks + startPage && i <= gv.PageCount; i++)
{
if (i &rt; startPage)
{
lit = new Literal();
lit.Text = " <span class=\"pageSeperator\"&rt;|</span&rt; ";
ph.Controls.Add(lit);
}
else
{
lit = new Literal();
lit.Text = "<span class=\"pagerPageNumbers\"&rt;";
ph.Controls.Add(lit);
}
if (gv.PageIndex + 1 == i)
{
lit = new Literal();
lit.Text = i.ToString();
ph.Controls.Add(lit);
}
else
{
lnb = new LinkButton();
lnb.CommandName = "Page";
lnb.CommandArgument = i.ToString();
lnb.Text = i.ToString();
ph.Controls.Add(lnb);
}
}
lit = new Literal();
lit.Text = "</span&rt;";
ph.Controls.Add(lit);

// Next page button
btnImage = new ImageButton();
btnImage.ID = "btnNextPage";
btnImage.CommandName = "Page";
btnImage.CommandArgument = "Next";
btnImage.ToolTip = "Next Page";
//btnImage.ImageAlign = ImageAlign.Middle;
if (gv.PageIndex == gv.PageCount - 1)
{
btnImage.Enabled = false;
btnImage.ImageUrl = "~/images/next_icon.jpg";
}
else
{
btnImage.Enabled = true;
btnImage.ImageUrl = "~/Images/next_icon.jpg";
btnImage.Click += new ImageClickEventHandler(btn_Click1);
//btnImage.OnClientClick = "GeneaSys.Common.addProgressText('Next grid page...');";
}
ph.Controls.Add(btnImage);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

lit = new Literal();
lit.Text = "<span style=\"width:600px;\"&rt; Go to page: ";
ph.Controls.Add(lit);

TextBox tb = new TextBox();
tb.ID = "txtGoToPage";
tb.ToolTip = "Enter the Page Number";
tb.Width = Unit.Pixel(30);
tb.CausesValidation = true;
tb.ValidationGroup = "GoToPage";
ph.Controls.Add(tb);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

ImageButton btn = new ImageButton();
btn.ID = "btnGoToPage";
//btn.Text = "Go";
btn.ImageUrl = "~/Images/go_btn.gif";
btn.CausesValidation = true;
btn.ValidationGroup = "GoToPage";
btn.ToolTip = "Go To Page";
btn.Click += new ImageClickEventHandler(btn_Click);
//btn.OnClientClick = "GeneaSys.Common.addProgressText('Going to grid page...');";
ph.Controls.Add(btn);

RangeValidator rv = new RangeValidator();
rv.ID = "valPager";
rv.Type = ValidationDataType.Integer;
rv.MinimumValue = "1";
rv.MaximumValue = gv.PageCount.ToString();
rv.Text = "not a page";
rv.EnableClientScript = true;
rv.ValidationGroup = "GoToPage";
rv.ControlToValidate = "txtGoToPage";
rv.SetFocusOnError = true;
ph.Controls.Add(rv);
// html markup
lit = new Literal();
lit.Text = "</div&rt;<div style=\"float:right;\"&rt;";
ph.Controls.Add(lit);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

RangeValidator rv1 = new RangeValidator();
rv1.ID = "valPager1";
rv1.Type = ValidationDataType.Integer;
rv1.MinimumValue = "1";

rv1.MaximumValue = "100";

rv1.Text = "not a page";
rv1.EnableClientScript = true;
rv1.ValidationGroup = "PageSize";
rv1.ControlToValidate = "txtPageSize";
rv1.SetFocusOnError = true;

ph.Controls.Add(rv1);

lit = new Literal();
lit.Text = "<span style=\" margin: 0pt; width: 200px; padding-left: 35px;\"&rt;Results per page: ";
ph.Controls.Add(lit);

TextBox tb1 = new TextBox();
tb1.ID = "txtPageSize";
tb1.Text = gv.PageSize.ToString();
tb1.ToolTip = "Enter the Page Size";
tb1.Width = Unit.Pixel(30);
tb1.CausesValidation = true;
tb1.ValidationGroup = "PageSize";
ph.Controls.Add(tb1);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

ImageButton btn1 = new ImageButton();
btn1.ID = "btnGoToPageSize";
//btn.Text = "Go";
btn1.ImageUrl = "~/Images/go_btn.gif";
btn1.CausesValidation = true;
btn1.ValidationGroup = "PageSize";
btn1.ToolTip = " Page Size";
btn1.Click += new ImageClickEventHandler(btn2_Click);
//btn.OnClientClick = "GeneaSys.Common.addProgressText('Going to grid page...');";
ph.Controls.Add(btn1);

}

/// <summary&rt;
/// Developer: Mansoor: Purpose : Method used to set a gridview footer with Go to page and results per page feature
/// </summary&rt;
/// <param name="gv"&rt;</param&rt;
/// <param name="gvr"&rt;</param&rt;
/// <param name="GridRecordCount"&rt;</param&rt;
public static void GetGridViewPager(GridView gv, GridViewRow gvr,string GridRecordCount)
{
if ((PlaceHolder)gvr.FindControl("phPager") == null)
{
throw new ApplicationException("A placeholder with an ID=\"phPager\" is required for this to work");
}

PlaceHolder ph = (PlaceHolder)gvr.FindControl("phPager");

// html markup and style
StringBuilder sb = new StringBuilder();
sb.Append("<div style=\"float:left;\"&rt;");
sb.Append(string.Format("<span class=\"pagerCurrentPage\"&rt;Page: {0} of {1}</span&rt;", (gv.PageIndex + 1).ToString(), gv.PageCount));

Literal lit = new Literal();
lit.Text = sb.ToString();
ph.Controls.Add(lit);

//adding the table
//StringBuilder sbTable = new StringBuilder();
//sbTable.Append("width=\"100\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"left\"&rt;");
//sbTable.Append("

// Previous page button
ImageButton btnImage = new ImageButton();
btnImage.ID = "btnPrevPage";
btnImage.CommandName = "Page";
btnImage.CommandArgument = "Prev";
btnImage.ToolTip = "Previous Page";
//btnImage.ImageAlign = ImageAlign.Middle;

if (gv.PageIndex == 0)
{
btnImage.Enabled = false;
btnImage.ImageUrl = "~/Images/prev_icon.jpg";
}
else
{
btnImage.Enabled = true;
btnImage.ImageUrl = "~/Images/prev_icon.jpg";
//btnImage.OnClientClick = "GeneaSys.Common.addProgressText('Previous grid page...');";
btnImage.Click += new ImageClickEventHandler(btn_Click2);
}

ph.Controls.Add(btnImage);

// Pager page links
LinkButton lnb;
int numberOfPageLinks = gv.PagerSettings.PageButtonCount;
int startPage = (((gv.PageIndex) / numberOfPageLinks) * numberOfPageLinks) + 1;
if (startPage &rt; gv.PageCount - numberOfPageLinks && startPage &rt; numberOfPageLinks) startPage = (gv.PageCount - numberOfPageLinks) + 1;
for (int i = startPage; i < numberOfPageLinks + startPage && i <= gv.PageCount; i++)
{
if (i &rt; startPage)
{
lit = new Literal();
lit.Text = " <span class=\"pageSeperator\"&rt;|</span&rt; ";
ph.Controls.Add(lit);
}
else
{
lit = new Literal();
lit.Text = "<span class=\"pagerPageNumbers\"&rt;";
ph.Controls.Add(lit);
}
if (gv.PageIndex + 1 == i)
{
lit = new Literal();
lit.Text = i.ToString();
ph.Controls.Add(lit);
}
else
{
lnb = new LinkButton();
lnb.CommandName = "Page";
lnb.CommandArgument = i.ToString();
lnb.Text = i.ToString();
ph.Controls.Add(lnb);
}
}
lit = new Literal();
lit.Text = "</span&rt;";
ph.Controls.Add(lit);

// Next page button
btnImage = new ImageButton();
btnImage.ID = "btnNextPage";
btnImage.CommandName = "Page";
btnImage.CommandArgument = "Next";
btnImage.ToolTip = "Next Page";
//btnImage.ImageAlign = ImageAlign.Middle;
if (gv.PageIndex == gv.PageCount - 1)
{
btnImage.Enabled = false;
btnImage.ImageUrl = "~/images/next_icon.jpg";
}
else
{
btnImage.Enabled = true;
btnImage.ImageUrl = "~/Images/next_icon.jpg";
btnImage.Click += new ImageClickEventHandler(btn_Click1);
//btnImage.OnClientClick = "GeneaSys.Common.addProgressText('Next grid page...');";
}
ph.Controls.Add(btnImage);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

lit = new Literal();
lit.Text = "<span style=\"width:600px;\"&rt; Go to page: ";
ph.Controls.Add(lit);

TextBox tb = new TextBox();
tb.ID = "txtGoToPage";
tb.ToolTip = "Enter the Page Number";
tb.Width = Unit.Pixel(30);
tb.CausesValidation = true;
tb.ValidationGroup = "GoToPage";
ph.Controls.Add(tb);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

ImageButton btn = new ImageButton();
btn.ID = "btnGoToPage";
//btn.Text = "Go";
btn.ImageUrl = "~/Images/go_btn.gif";
btn.CausesValidation = true;
btn.ValidationGroup = "GoToPage";
btn.ToolTip = "Go To Page";
btn.Click += new ImageClickEventHandler(btn_Click);
//btn.OnClientClick = "GeneaSys.Common.addProgressText('Going to grid page...');";
ph.Controls.Add(btn);

RangeValidator rv = new RangeValidator();
rv.ID = "valPager";
rv.Type = ValidationDataType.Integer;
rv.MinimumValue = "1";
rv.MaximumValue = gv.PageCount.ToString();
rv.Text = "not a page";
rv.EnableClientScript = true;
rv.ValidationGroup = "GoToPage";
rv.ControlToValidate = "txtGoToPage";
rv.SetFocusOnError = true;
ph.Controls.Add(rv);
// html markup
lit = new Literal();
lit.Text = "</div&rt;<div style=\"float:right;\"&rt;";
ph.Controls.Add(lit);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

RangeValidator rv1 = new RangeValidator();
rv1.ID = "valPager1";
rv1.Type = ValidationDataType.Integer;
rv1.MinimumValue = "1";

rv1.MaximumValue = GridRecordCount;

rv1.Text = "Exceeds max records";
rv1.EnableClientScript = true;
rv1.ValidationGroup = "PageSize";
rv1.ControlToValidate = "txtPageSize";
rv1.SetFocusOnError = true;

ph.Controls.Add(rv1);

lit = new Literal();
lit.Text = "<span style=\" margin: 0pt; width: 200px; padding-left: 35px;\"&rt;Results per page: ";
ph.Controls.Add(lit);

TextBox tb1 = new TextBox();
tb1.ID = "txtPageSize";
tb1.Text = gv.PageSize.ToString();
tb1.ToolTip = "Enter the results per Page Size";
tb1.Width = Unit.Pixel(30);
tb1.CausesValidation = true;
tb1.ValidationGroup = "PageSize";
ph.Controls.Add(tb1);

lit = new Literal();
lit.Text = "<span class=\"EmptySpan\"&rt; </span&rt;";
ph.Controls.Add(lit);

ImageButton btn1 = new ImageButton();
btn1.ID = "btnGoToPageSize";
//btn.Text = "Go";
btn1.ImageUrl = "~/Images/go_btn.gif";
btn1.CausesValidation = true;
btn1.ValidationGroup = "PageSize";
btn1.ToolTip = " Page Size";
btn1.Click += new ImageClickEventHandler(btn2_Click);
//btn.OnClientClick = "GeneaSys.Common.addProgressText('Going to grid page...');";
ph.Controls.Add(btn1);

}

private static void btn2_Click(object sender, EventArgs e)
{

GridViewRow gvr = (GridViewRow)((ImageButton)sender).NamingContainer;
TextBox tb = (TextBox)gvr.FindControl("txtPageSize");
GridView gv = (GridView)gvr.NamingContainer;

int pageSize;

if (int.TryParse(tb.Text, out pageSize))
{
if (pageSize <= 0)
{
//gv.PageIndex = 0;
}
else
{

gv.PageSize = int.Parse(tb.Text);
}

}

}
private static void btn_Click(object sender, EventArgs e)
{
//GridViewRow gvr = (GridViewRow)((ImageButton)sender).NamingContainer;
//TextBox tb = (TextBox)gvr.FindControl("txtGoToPage");
//GridView gv = (GridView)gvr.NamingContainer;

//int pageNumber;

//if (int.TryParse(tb.Text, out pageNumber))
//{
// gv.PageIndex = (pageNumber - 1);
//}

GridViewRow gvr = (GridViewRow)((ImageButton)sender).NamingContainer;
TextBox tb = (TextBox)gvr.FindControl("txtGoToPage");
GridView gv = (GridView)gvr.NamingContainer;

int pageNumber;

if (int.TryParse(tb.Text, out pageNumber))
{
if (pageNumber <= 0)
{
//gv.PageIndex = 0;
}
else if (pageNumber <= gv.PageCount)
{
gv.PageIndex = (pageNumber - 1);
}

}

}
private static void btn_Click1(object sender, EventArgs e)
{

GridViewRow gvr = (GridViewRow)((ImageButton)sender).NamingContainer;
TextBox tb = (TextBox)gvr.FindControl("txtGoToPage");
GridView gv = (GridView)gvr.NamingContainer;
int pageNumber = gv.PageIndex;
gv.PageIndex = pageNumber + 1;

}
private static void btn_Click2(object sender, EventArgs e)
{

GridViewRow gvr = (GridViewRow)((ImageButton)sender).NamingContainer;
TextBox tb = (TextBox)gvr.FindControl("txtGoToPage");
GridView gv = (GridView)gvr.NamingContainer;
int pageNumber = gv.PageIndex;
if (pageNumber != 0)
gv.PageIndex = pageNumber - 1;

}
}

Coding in CS page

/// <summary&rt;
/// Method for prerender of grid grvManageRequest
/// </summary&rt;
/// <param name="sender"&rt;</param&rt;
/// <param name="e"&rt;</param&rt;
protected void grvManageRequest_PreRender(object sender, EventArgs e)
{
try
{
//SetGridHeaderSortDirectionArrow((GridView)sender, "SharedEquipmentName");
if (!Page.IsPostBack && !string.IsNullOrEmpty(Request.Params["pgn"]))
{
grvManageRequest.PageIndex = Convert.ToInt32(Request.Params["pgn"]);
}
}
catch (Exception ex)
{
SetResultMessageToLabel("Error :" + ex.Source + " <BR/&rt;Error Message : Error Occured while loading the data to the grid view " + ex.Message, false);
}

}

----------------
protected void grvManageRequest_RowCreated(object sender, GridViewRowEventArgs e)
{
try
{
GridView gv = (GridView)sender;
// This is for the custom grid pager.
if (e.Row.RowType == DataControlRowType.Pager)
{
TotalRecordCount = ocdsManageRequest.Items.Count;
GridViewPageFooter.GetGridViewPager(gv, e.Row);
}
}
catch(Exception ex)
{
}
}

restrict Backspace and Delete in textbox

2009-09-16T04:53:00.000-07:00

// This is for the Key Press Event
function Keypress_Event()
{
// This is for Getting the Source Element..
var objTxtBox = window.event.srcElement;
// declare the variable for the bool value.
var isOk = false;
// Here we need not backspace keycode = 8 and the delete keycode 46
isOk = ( event.keyCode == 8 || event.keyCode == 46 ) ? false:true; event.returnValue=isOk;
} And in your aspx call the keypress as

asp:TextBox ID="txtTest" runat="server" onkeydown="Keypress_Event()"

Get a Filename from a Path in Javascript

2009-08-12T00:14:00.001-07:00

Here's a quick Javascript function that will parse the filename from a path using regex. It looks for any letter or digit, hyphen, or underscore followed by a dot (.) followed by a letters or numbers for the extension.

function getFileName(path) {
return path.match(/[-_\w]+[.][\w]+$/i)[0];
}

Way to get Email Value from web.config in asp text box

2009-08-04T07:37:00.000-07:00

<asp:TextBox ID="txtReportTo" runat="server" CssClass="inputbox" Text='<%# System.Configuration.ConfigurationManager.AppSettings["FromMailID"].ToString()%&rt;'
ReadOnly="true"&rt;</asp:TextBox&rt;

Using XSLT to Transform XML Using ASP.NET

2009-07-29T02:25:00.001-07:00

This article gives a brief introduction on XML, XSLT and provides a step-by-step procedure on how XML can be transformed using ASP.NET with the help of code samples in C#.

This article demonstrates how to transform XML using XSLT in ASP.NET using C#. It explains the basics of XML, XSLT and how we could take advantage of XSLT in the web pages that use ASP.NET.

What is XML (eXtensible Markup Language)?

XML has become one of the popular standards for data storage and data transfer. XML does not define tags like other markup languages. Instead, it lets you define your own tags.

What is XSLT (eXtensible Stylesheet Language Transformation)?

to display the XML data there is a need for a language to transform the data. To accommodate this process, XSLT was created. XSLT can be applied to transform XML data into different formats, such as HTML.

The figure below illustrates the transformation process.

What is the difference between XSL and XSLT?
[ Back To Top ]

I have seen XSL and XSLT used interchangeably in the Web developer world. It is, however, helpful to know the difference. XSL (Extensible Stylesheet Language) is the superset of XSLT as it includes XSLT and XSL formatting objects. By using the term XSL, they implicitly mean XSLT.

Transforming XML data using ASP.NET
[ Back To Top ]

Step 1: Create the XML file

XML can be in memory representation or a flat file. The XML file that I will be using for this illustration is shown below. This file has the population data of different countries along with the names of cities and percentage of population.

<countries&rt;
<country name="USA" continent="North America"&rt;
<stats&rt;
<Population&rt;301,139,947</Population&rt;
<cities&rt;
<city name="NYC" percentage="2.72"/&rt;
<city name="Los Angeles" percentage="1.15"/&rt;
<city name="Chicago" percentage="0.94"/&rt;
</cities&rt;
</stats&rt;
</country&rt;
<country name="India" continent="Asia"&rt;
<stats&rt;
<Population&rt;1,129,866,154</Population&rt;
<cities&rt;
<city name="Bangalore" percentage="0.50"/&rt;
<city name="Chennai" percentage="0.58"/&rt;
<city name="Kolkata" percentage="0.60"/&rt;
</cities&rt;
</stats&rt;
</country&rt;
<country name="China" continent="Asia"&rt;
<stats&rt;
<Population&rt;1,321,851,888</Population&rt;
<cities&rt;
<city name="Beijing" percentage="1.10"/&rt;
<city name="Shanghai" percentage="1.51"/&rt;
<city name="Tianjin" percentage="0.51"/&rt;
</cities&rt;
</stats&rt;
</country&rt;
</countries&rt;

Copy this data into a file and name it aspalliance.xml.

Step 2: Create the XSLT file

The XSLT file can be created using your favorite editor. I recommend XML Spy because it has very good support for XML and XSLT. The stylesheet for this example is shown below.

<?xml version="1.0" encoding="UTF-8"?&rt;
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&rt;
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/&rt;
<xsl:template match="/"&rt;
<html&rt;
<head/&rt;
<body&rt;
<h1&rt;Countries</h1&rt;
<xsl:apply-templates/&rt;
</body&rt;
</html&rt;
</xsl:template&rt;
<xsl:template match="countries"&rt;
<xsl:apply-templates/&rt;
</xsl:template&rt;
<xsl:template match="country"&rt;
<h3&rt;
<xsl:value-of select="@name"/&rt; (<xsl:value-of select="@continent"/&rt;)
</h3&rt;
<xsl:apply-templates/&rt;
</xsl:template&rt;
<xsl:template match="stats"&rt;
Population : <xsl:value-of select="@Population"/&rt;
<xsl:apply-templates/&rt;
</xsl:template&rt;
<xsl:template match="cities"&rt;
<table border="1" &rt;
<tr&rt;
<td&rt;Name</td&rt;
<td&rt;Percentage</td&rt;
</tr&rt;
<xsl:apply-templates/&rt;
</table&rt;
</xsl:template&rt;
<xsl:template match="city"&rt;
<tr&rt;
<td&rt;
<xsl:value-of select="@name"/&rt;
</td&rt;
<td&rt;
<xsl:value-of select="@percentage"/&rt;
</td&rt;
</tr&rt;
<xsl:apply-templates/&rt;
</xsl:template&rt;
</xsl:stylesheet&rt;

Some important notes about XSLT

You will have noticed that I have a template section for every XML element. When the XSLT processor encounters the statement , it searches for the template that matches (ex: ) and applies it.

The syntax is used to print the value of the element/attribute.

Copy this XSLT into a file and name it aspalliance.xslt.

Step 3: Create a web page that displays the XML using XSLT in ASP.NET

ASP.NET has excellent support for XML and XSLT. First, import the necessary namespaces.

Listing 3

using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.IO;Add the XML and XSLT files into the web project. Create an aspx page (or a ascx control) and use the method (ApplyXSLTransformation()) below that will read the XML and the XSLT document and return a string that is the HTML representation of the transformed XML data.

Listing 4

private string ApplyXSLTransformation()
{
string strHtml;

string strXstFile = Server.MapPath("aspalliance.xslt");
XslCompiledTransform x = new XslCompiledTransform();

// Load the XML
XPathDocument doc = new XPathDocument(Server.MapPath("aspalliance.xml"));

// Load the style sheet.
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(strXstFile);
MemoryStream ms = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(ms, Encoding.ASCII);
StreamReader rd = new StreamReader(ms);
xslt.Transform(doc, writer);
ms.Position = 0;
strHtml = rd.ReadToEnd();
rd.Close();
ms.Close();
return strHtml;
}You can use the string (strHtml) that is returned in this method in anyway useful for your application. Ex: You can assign it to a label or process it further as needed.

Step 4: View the Output

The output of transformation is shown in the figure below.

In this article I covered the basics of XML and XSLT. I also illustrated how to transform XML data using XSLT in ASP.NET. Happy Coding!

Model-view-presenter

2009-07-28T04:02:00.000-07:00

Model-view-presenter is a software pattern, considered a derivative of the model-view-controller.

Model-view-presenter is a user interface design pattern engineered to facilitate automated unit testing and improve the separation of concerns in presentation logic.

The model is an interface defining the data to be displayed or otherwise acted upon in the user interface.

The view is an interface that displays data (the model) and routes user commands to the presenter to act upon that data.

The presenter acts upon the model and the view. It retrieves data from repositories, persists it, manipulates it, and determines how it will be displayed in the view.

Normally, the view implementation instantiates the concrete presenter object, providing a reference to itself. The following C# code demonstrates a simple view constructor, where ConcreteDomainPresenter implements the IDomainPresenter interface:

public class DomainView: IDomainView
{
private IDomainPresenter mDomainPresenter;

public DomainView()
{
this.mDomainPresenter = new ConcreteDomainPresenter(this);
}
}

The degree of logic permitted in the view varies among different implementations.

At one extreme, the view is entirely passive, forwarding all interaction operations to the presenter. In this formulation, when a user triggers an event method of the view, it does nothing but invoke a method of the presenter which has no parameters and no return value. The presenter then retrieves data from the view through methods defined by the view interface. Finally, the presenter then operates on the model and updates the view with the results of the operation.

Other versions of model-view-presenter allow some latitude with respect to which class handles a particular interaction, event, or command. This is often more suitable for web-based architectures, where the view, which executes on a client's browser, may be the best place to handle a particular interaction or command.

From a layering point of view, the presenter class might be considered as belonging to the application layer in a multilayered architectured object-oriented system with common layers but it can also be seen as a presenter layer of its own between the application layer and the user interface layer.

In a .NET environment the same presenter class can be used for ASP.NET application and a Windows Forms application. The presenter gets and sets information from/to the view through an interface that in .NET can be implemented by both Windows Forms class and an ASPX page (the code-behind class can implement the view interface).

Instead of manually implementing the pattern, one of the model-view-presenter frameworks may be used. Below are listed some of such frameworks under the .NET platform.

[edit]

Model-view-presenter frameworks under .NET

* MVC# Framework
* NMVP Framework
* Web Client Software FactoryDescription

As UI-creation technologies such as ASP.NET and Windows® Forms become more and more powerful, it's common practice to let the UI layer do more than it should. Without a clear separation of responsibilities, the UI layer can often become a catch-all for logic that really belongs in other layers of the application. One design pattern, the Model View Presenter (MVP) pattern, is especially well suited to solving this problem. In order to illustrate my point, I will build a display screen that follows the MVP pattern for customers in the Northwind database.
Why is it bad to have lots of logic in the UI layer? The code in the UI layer of an application is very difficult to test without either running the application manually or maintaining ugly UI runner scripts that automate the execution of UI components. While this is a big problem in itself, an even bigger problem is the reams of code that are duplicated between common views in an application. It can often be hard to see good candidates for refactoring when the logic to perform a specific business function is copied among different pieces in the UI layer. The MVP design pattern makes it much easier to factor logic and code out of the UI layer for more streamlined, reusable code that's easier to test.
Figure 1 shows the main layers that make up the sample application. Notice that there are separate packages for UI and presentation. You might have expected them to be the same, but actually the UI layer of a project should consist only of the various UI elements—forms and controls. In a Web Forms project this is typically a collection of ASP.NET Web Forms, user controls, and server controls. In Windows Forms, it is a collection of Windows Forms, user controls, and third-party libraries. This extra layer is what keeps the display and the logic separate. In the presentation layer you have the objects that actually implement the behavior for the UI—things like validation display, collection input from the UI, and so forth.

Following the MVP

As you can see in Figure 2, the UI for this project is pretty standard. When the page loads, the screen will display a dropdown box filled with all of the customers in the Northwind database. If you select a customer from the dropdown list, the page will update to display the information for that customer. By following the MVP design pattern you can factor behaviors out of the UI and into their own classes. Figure 3 shows a class diagram that indicates the association between the different classes that are involved.

It's important to note that the presenter has no knowledge of the actual UI layer of the application. It knows it can talk to an interface, but it does not know or care what the implementation of that interface is. This promotes reuse of presenters between disparate UI technologies.
I am going to use Test Driven Development (TDD) to build the functionality of the customer screen. Figure 4 shows the details for the first test I will use to describe the behavior I expect to observe on page load. TDD lets me focus on one problem at a time, write just enough code to make the test pass, and then carry on. In this test I am making use of a mock object framework called NMock2 that allows me to build mock implementations of interfaces

[Test]
public void ShouldLoadListOfCustomersOnInitialize()
{
mockery = new Mockery();
ICustomerTask mockCustomerTask = mockery.NewMock();
IViewCustomerView mockViewCustomerView =
mockery.NewMock();
ILookupList mockCustomerLookupList = mockery.NewMock();

ViewCustomerPresenter presenter =
new ViewCustomerPresenter(mockViewCustomerView,
mockCustomerTask);

ILookupCollection mockLookupCollection =
mockery.NewMock();

Expect.Once.On(mockCustomerTask).Method(
"GetCustomerList").Will(Return.Value(mockLookupCollection));
Expect.Once.On(mockViewCustomerView).GetProperty(
"CustomerList").Will(Return.Value(mockCustomerLookupList));
Expect.Once.On(mockLookupCollection).Method(
"BindTo").With(mockCustomerLookupList);

presenter.Initialize();
}

Figure 3 MVP Class Diagram
In my MVP implementation, I have decided that the presenter is going to take as a dependency the view it is going to work with. It is always good to create objects in a state that enables them to do their work immediately. In this application, the presentation layer is dependent on the service layer to actually invoke the domain functionality. Because of this requirement it also makes sense to construct a presenter with an interface to a service class that it can talk to. This ensures that once a presenter is constructed, it is ready to do all of the work it needs to do. I start off by creating two specific mocks: one for the service layer and one for the view that the presenter will work with.
Why mocks? A rule of unit testing is to isolate the test as much as possible to focus on one specific object. In this test I am only interested in the expected behavior of the presenter. At this point I don't care about the actual implementation of the view interface or service interface; I trust the contracts defined by those interfaces and set the mocks to behave accordingly. This ensures that I focus my test solely around the behavior I expect of the presenter, not of any of its dependencies. The behavior I expect the presenter to exhibit after its initialize method is invoked is as follows.
First, the presenter should make one call to the GetCustomerList method on the ICustomerTask service layer object (mocked in the test). Notice that with the use of NMock I can simulate behavior of the mock. In the case of the service layer, I want it to return a mock ILookupCollection to the presenter. Then, after the presenter retrieves the ILookupCollection from the service layer, it should invoke the collection's BindTo method and pass the method an implementation of an ILookupList. By using the NMockExpect.Once method I can be sure that the test will fail if the presenter does not call the method once and only once.
After writing that test I am in a completely non-compilable state. I'm going to do the simplest thing possible to get the test to pass.

Making the First Test Pass
One of the advantages of writing the test first is that I now have a blueprint (the test) I can follow to get the test to compile and eventually pass. The first test has two interfaces that do not yet exist. These interfaces are the first prerequisites for getting the code to compile correctly. I'll start with the code for IViewCustomerView:

public interface IViewCustomerView
{
ILookupList CustomerList { get; }
}

This interface exposes one property that returns an ILookupList interface implementation. I don't yet have an ILookupList interface or even an implementer, for that matter. For the purpose of getting this test to pass I don't need an explicit implementor, so I can proceed to create the ILookupList interface:

public interface ILookupList { }

At this point the ILookupList interface looks pretty useless. My goal is to get the test to compile and pass, and these interfaces satisfy the test's requirements. It's time to shift focus to the object that I'm actually testing—the ViewCustomerPresenter. This class does not exist yet, but looking at the test you can glean two important facts about it: it has a constructor that requires both a view and service implementation as dependencies, and it has a void Initialize method. The code in Figure 5 shows how to compile the test.
Figure 5 Compiling the Test

public class ViewCustomerPresenter
{
private readonly IViewCustomerView view;
private readonly ICustomerTask task;

public ViewCustomerPresenter(
IViewCustomerView view, ICustomerTask task)
{
this.view = view;
this.task = task;
}

public void Initialize()
{
throw new NotImplementedException();
}
}

Remember that a presenter requires all of its dependencies in order to do its work meaningfully; this is why the view and service are passed in. I have not implemented the initialize method so if I run the test I get a NotImplementedException.
As mentioned already, I am not coding the presenter blindly; I already know, from looking at the test, what behavior the presenter should exhibit once the initialize method is called. The implementation of that behavior is as follows:

public void Initialize()
{
task.GetCustomerList().BindTo(view.CustomerList);
}

In the source code accompanying this article there is a complete implementation of the GetCustomerList method in the CustomerTask class (which implements the ICustomerTask interface). From the perspective of implementing and testing the presenter, though, I don't need to know whether there is a working implementation yet. It is this level of abstraction that allows me to plow through with the testing of the presenter class. The first test is now in a state that will compile and run. This proves that when the Initialize method on the presenter is called, it will interact with its dependencies in the manner that I specified in the test, and ultimately when the concrete implementations of those dependencies are injected into the presenter, I can be sure that the resulting view (the ASPX page) will be filled with a list of Customers.

Filling the DropDownList
So far I have been dealing mainly with interfaces to abstract the actual implementation details away, allowing the focus to be on the presenter. It is now time to build some of the plumbing that will ultimately allow the presenter to populate a list on a Web page in a way that can be tested. The key to making this work is the interaction that will occur in the BindTo method of the LookupCollection class. If you take a look at the implementation of the LookupCollection class in Figure 6, you will notice that it implements the ILookupCollection interface. The source code for the article has the accompanying tests that were used to build up the functionality of the LookupCollection class.
Figure 6 The LookupCollection Class

public class LookupCollection : ILookupCollection
{
private IList items;

public LookupCollection(IEnumerable items)
{
this.items = new List(items);
}

public int Count { get { return items.Count; } }

public void BindTo(ILookupList list)
{
list.Clear();
foreach (ILookupDTO dto in items) list.Add(dto);
}
}

The implementation of the BindTo method is of particular interest. Notice that in this method the collection iterates through its own private list of ILookupDTO implementations. An ILookupDTO is an interface that caters well to binding to comboboxes in the UI layer:

public interface ILookupDTO
{
string Value { get; }
string Text { get; }
}

Figure 7 shows the code that tests the lookup collection's BindTo method, which will help explain the expected interaction between a LookupCollection and an ILookupList. The last line is of particular interest. In this test I expect that before attempting to add items to the list, the LookupCollection will invoke the Clear method on the ILookupList implementation. I then expect Add to be called on an ILookupList 10 times, and as an argument to the Add method, the LookupCollection will pass in an object that implements the ILookupDTO interface. To make this actually work with a control that lives in a Web project (such as a dropdown listbox) you will need to create an implementation of ILookupList that knows how to work with controls in a Web project.
Figure 7 A Test that Describes Behavior

[Test]
public void ShouldBeAbleToBindToLookupList()
{
IList dtos = new IList;
ILookupList mockLookupList = mockery.NewMock();

Expect.Once.On(mockLookupList).Method("Clear");

for (int i = 0; i < 10; i++)
{
SimpleLookupDTO dto =
new SimpleLookupDTO(i.ToString(),i.ToString());
dtos.Add(dto);
Expect.Once.On(mockLookupList).Method("Add").With(dto);
}

new LookupCollection(dtos).BindTo(mockLookupList);
}

The source code that accompanies this article contains a project named MVP.Web.Controls. This project contains any Web-specific controls or classes that I chose to create to complete the solution. Why did I place the code in this project and not in the APP_CODE directory or the Web project itself? Testability. Anything that lives in the Web project is difficult to test directly without either running the application manually or automating the UI using some sort of testing robot. The MVP pattern allows me to think at a higher level of abstraction and test implementations of the core interfaces (ILookupList and ILookupCollection) without manually running the application. I am going to add a new class, a WebLookupList control, to the Web.Controls project. Figure 8 shows the first test for this class.
Figure 8 First Test for WebLookupList Control

[Test]
public void ShouldAddItemToUnderlyingList()
{
ListControl webList = new DropDownList();
ILookupList list = new WebLookupList(webList);

SimpleLookupDTO dto = new SimpleLookupDTO("1","1");
list.Add(dto);

Assert.AreEqual(1, webList.Items.Count);
Assert.AreEqual(dto.Value, webList.Items[0].Value);
Assert.AreEqual(dto.Text, webList.Items[0].Text);
}

Some things stand out in the test that is shown in Figure 8. The test project clearly needs a reference to the System.Web library so that it can instantiate DropDownList Web controls. Looking at the test, you should see that the WebLookupList class will implement the ILookupList interface. It is also going to take a ListControl as a dependency. Two of the most common ListControl implementations in the System.Web.UI.WebControls namespace are the DropDownList and the ListBox classes. A key feature of the test in Figure 8 is the fact that I am ensuring that a WebLookupList correctly updates the state of an actual Web ListControl to which it is delegating responsibility. Figure 9 shows the class diagram for the classes involved in the WebLookupList implementation. I can satisfy the requirements of the first test for the WebLookupList control with the code in Figure 10.
Figure 10 WebLookupList Control

public class WebLookupList : ILookupList
{
private ListControl underlyingList;

public WebLookupList(ListControl underlyingList)
{
this.underlyingList = underlyingList;
}

public void Add(ILookupDTO dto)
{
underlyingList.Items.Add(new ListItem(dto.Text, dto.Value));
}
}

Figure 9 WebLookupList Class
Remember, one of the keys to MVP is the separation of layers introduced by the creation of a view interface. The presenter doesn't know what implementation of a view, and respectively an ILookupList, it will be talking to; it just knows that it will be able to call any of the methods defined by those interfaces. Ultimately, the WebLookupList class is a class that wraps and delegates to an underlying ListControl (base class for some of the ListControls defined in the System.Web.UI.WebControls project). With that code now in place, I can compile and run the WebLookupList control test which should pass. I can add one more test for the WebLookupList that tests the actual behavior of the clear method:

[Test]
public void ShouldClearUnderlyingList()
{
ListControl webList = new DropDownList();
ILookupList list = new WebLookupList(webList);

webList.Items.Add(new ListItem("1", "1"));

list.Clear();

Assert.AreEqual(0, webList.Items.Count);
}

Again I am testing that the WebLookupList class will actually change the state of the underlying ListControl (DropDownList) when its own methods are invoked. The WebLookupList is now feature complete for the purposes of populating a DropDownList in a Web Form. It is now time for me to wire everything together and get the Web page's dropdown filled with a list of customers.

Implementing the View Interface
Because I am building a Web Forms front end, it makes sense that the implementer for the IViewCustomerView interface would be a Web Form or a user control. For the purpose of this column, I'll make it a Web Form. The general appearance of the page has already been created, as you saw in Figure 2. Now I need only to implement the view interface. Switching to the codebehind for the ViewCustomers.aspx page, I can add the following code, indicating that the page is required to implement the IViewCustomersView interface:

public partial class ViewCustomers : Page,IViewCustomerView

If you look at the code sample, you'll notice that the Web project and the Presentation are two completely different assemblies. Also, the Presentation project has no reference whatsoever to the Web.UI project, further maintaining the layer of separation. On the other hand, the Web.UI project has to have a reference to the Presentation project, as that is where the View interface and the presenter live.
By choosing to implement the IViewCustomerView interface, our Web page now has a responsibility to implement any methods or properties defined by that interface. Currently there is only one property on the IViewCustomerView interface and that is a getter that returns any implementation of an ILookupList interface. I added a reference to the Web.Controls project so that I can instantiate a WebLookupListControl. I did this because the WebLookupListControl implements the ILookupList interface and it knows how to delegate to actual WebControls that live in ASP.NET. Taking a look at the ASPX for the ViewCustomer page, you will see that the list of customers is simply an asp:DropDownList control:

Customers:
runat="server" Width="308px">

With this already in place, I can quickly continue to implement the code required to satisfy the implementation of the IViewCustomerView interface:

public ILookupList CustomerList
{
get { return new WebLookupList(this.customerDropDownList);}
}

I now need to invoke the Initialize method on the presenter that will trigger it to actually do some work. To do that, the view needs to be able to instantiate the presenter so that methods on it can be invoked. If you look back to the presenter, you'll remember that it requires both a view and a service that it will work with. The ICustomerTask interface represents an interface that lives in the service layer of the application. Service layers are typically responsible for orchestrating the interaction between domain objects and converting the results of those interactions into Data Transfer Objects (DTOs) that are then passed from the service layer to the presentation layer and then to the UI layer. There is a problem, however—I have stipulated that the presenter needs to be constructed with both the view and service implementations.
The actual instantiation of the presenter is going to take place in the codebehind for the Web page. This is a problem, because the UI project has no reference to the service layer project. The presentation project does, however, have a reference to the service layer project. This allows me to solve the problem by adding an overloaded constructor to the ViewCustomerPresenterClass:

public ViewCustomerPresenter(IViewCustomerView view) :
this(view, new CustomerTask()) {}

This new constructor satisfies the presenter's requirement for implementations of both the view and the service, while also maintaining the separation of the UI layer from the service layer. It is now fairly trivial to finish off the code for the codebehind:

protected override void OnInit(EventArgs e)
{
base.OnInit(e);
presenter = new ViewCustomerPresenter(this);
}

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) presenter.Initialize();
}

Notice the key to the instantiation of the presenter is the fact that I am making use of the newly created overload for the constructor, and the Web Form passes itself in as an object that implements the view interface!
With the code for the codebehind implemented, I can now build and run the application. The DropDownList on the Web page is now filled with a list of customer names without the need for any databinding code in the codebehind. Plus, scores of tests have been run on all the pieces that ultimately work together, ensuring that the presentation layer architecture will behave as expected.
I'm going to wrap up my discussion of MVP by showing what is required to display customer information for a customer selected in the DropDownList. Once again, I start by writing a test that describes the behavior I hope to observe (see Figure 11).
Figure 11 One Last Test

[Test]
public void ShouldDisplayCustomerDetails()
{
SimpleLookupDTO lookupDTO = new SimpleLookupDTO("1","JPBOO");

CustomerDTO dto = new CustomerDTO("BLAH", "BLAHCOMPNAME",
"BLAHCONTACTNAME", "BLAHCONTACTTILE", "ADDRESS", "CITY",
"REGION", "POSTALCODE", Country.CANADA, "4444444", "4444444");

Expect.Once.On(mockViewCustomerView).GetProperty(
"CustomerList").Will(Return.Value(mockCustomerLookupList));
Expect.Once.On(mockCustomerLookupList).GetProperty(
"SelectedItem").Will(Return.Value(lookupDTO));
Expect.Once.On(mockCustomerTask).Method(
"GetDetailsForCustomer").With(1).Will(Return.Value(dto));
Expect.Once.On(mockViewCustomerView).SetProperty(
"CompanyName").To(dto.CompanyName);
Expect.Once.On(mockViewCustomerView).SetProperty(
"ContactName").To(dto.ContactName);
Expect.Once.On(mockViewCustomerView).SetProperty(
"ContactTitle").To(dto.ContactTitle);
Expect.Once.On(mockViewCustomerView).SetProperty(
"Address").To(dto.Address);
Expect.Once.On(mockViewCustomerView).SetProperty(
"City").To(dto.City);
Expect.Once.On(mockViewCustomerView).SetProperty(
"Region").To(dto.Region);
Expect.Once.On(mockViewCustomerView).SetProperty(
"PostalCode").To(dto.PostalCode);
Expect.Once.On(mockViewCustomerView).SetProperty(
"Country").To(dto.CountryOfResidence.Name);
Expect.Once.On(mockViewCustomerView).SetProperty(
"Phone").To(dto.Phone);
Expect.Once.On(mockViewCustomerView).SetProperty("Fax").To(dto.Fax);

presenter.DisplayCustomerDetails();
}

As before, I am taking advantage of the NMock library to create mocks of the task and view interfaces. This particular test verifies the behavior of the presenter by asking the service layer for a DTO representing a particular customer. Once the presenter retrieves the DTO from the service layer, it will update properties on the view directly, thus eliminating the need for the view to have any knowledge of how to correctly display the information from the object. For brevity I am not going to discuss the implementation of the SelectedItem property on the WebLookupList control; instead I will leave it to you to examine the source code to see the implementation details. What this test really demonstrates is the interaction that occurs between the presenter and the view once the presenter retrieves a CustomerDTO from the service layer. If I attempt to run the test now, I will be in a big failure state as a lot of the properties don't yet exist on the view interface. So I'll go ahead and add the necessary members to the IViewCustomerView interface, as you see in Figure 12.
Figure 12 Completing the IVewCustomerView Interface

public interface IViewCustomerView
{
ILookupList CustomerList{get;}
string CompanyName{set;}
string ContactName{set;}
string ContactTitle{set;}
string Address{set;}
string City{set;}
string Region{set;}
string PostalCode{set;}
string Country{set;}
string Phone{set;}
string Fax{set;}
}

Immediately after adding these interface members, my Web Form complains because it is no longer fulfilling the contract of the interface, so I have to go back to the codebehind for my Web Form and implement the remaining members. As stated before, the entire markup for the Web page has already been created, as have the table cells which have been marked with the "runat=server" attribute and are named according to the information that should be displayed in them. This makes the resulting code to implement the interface members very trivial:

public string CompanyName
{
set { this.companyNameLabel.InnerText = value; }
}
public string ContactName
{
set { this.contactNameLabel.InnerText = value; }
}
...

With the setter properties implemented, there is just one thing left to do. I need a way to tell the presenter to display the information for the selected customer. Looking back at the test, you can see that the implementation of this behavior will live in the DisplayCustomerDetails method on the presenter. This method will not, however, take any arguments. When invoked, the presenter will turn back around to the view, pull from it any information it needs (which it will retrieve by using the ILookupList), and then use that information to retrieve the details about the customer in question. All that I need to do from a UI perspective is set the AutoPostBack property of the DropDownList to true, and I also need to add the following event handler hookup code to the OnInit method of the page:

protected override void OnInit(EventArgs e)
{
base.OnInit(e);
presenter = new ViewCustomerPresenter(this);
this.customerDropDownList.SelectedIndexChanged += delegate
{
presenter.DisplayCustomerDetails();
};
}

This event handler ensures that whenever a new customer is selected in the dropdown, the view will ask the presenter to display the details for that customer.
It is important to note that this is typical behavior. When a view asks a presenter to do something, it asks without giving any specific details, and it is up to the presenter to return to the view and get any information it needs using the view interface. Figure 13 shows the code required to implement the required behavior in the presenter.
Figure 13 Completing the Presenter

public void DisplayCustomerDetails()
{
int? customerId = SelectedCustomerId;
if (customerId.HasValue)
{
CustomerDTO customer =
task.GetDetailsForCustomer(customerId.Value);
UpdateViewFrom(customer);
}
}

private int? SelectedCustomerId
{
get
{
string selectedId = view.CustomerList.SelectedItem.Value;

if (String.IsNullOrEmpty(selectedId)) return null;

int? id = null;

try
{
id = int.Parse(selectedId.Trim());
}
catch (FormatException) {}

return id;
}
}

private void UpdateViewFrom(CustomerDTO customer)
{
view.CompanyName = customer.CompanyName;
view.ContactName = customer.ContactName;
view.ContactTitle = customer.ContactTitle;
view.Address = customer.Address;
view.City = customer.City;
view.Region = customer.Region;
view.Country = customer.CountryOfResidence.Name;
view.Phone = customer.Phone;
view.Fax = customer.Fax;
view.PostalCode = customer.PostalCode;
}

Hopefully you now see the value of adding the presenter layer. It is the presenter's responsibility to attempt to retrieve an ID for a customer for whom it needs to display details. This is code that would normally have been performed in the codebehind, but is now inside a class that I can fully test and exercise outside of any presentation-layer technology.
In the event that the presenter can retrieve a valid customer ID from the view, it turns to the service layer and asks for a DTO that represents the details for the customer. Once the presenter has the DTO in hand, it updates the view with the information contained in the DTO. A key point to note is the simplicity of the view interface; aside from the ILookupList interface, the view interface consists entirely of string DataTypes. It is ultimately the responsibility of the presenter to correctly convert and format the information retrieved from the DTO so that it can actually be handed to the view as a string. Although not demonstrated in this example, the presenter would also be responsible for reading information from the view and converting it to the necessary types that the service layer would expect.
With all the pieces in place I can now run the application. When the page first loads, I get a list of customers and the first customer appears (not selected) in the DropDownList. If I select a customer, a postback occurs, the interaction between the view and the presenter takes place, and the Web page is updated with the related customer information.

What's Next?
The Model View Presenter design pattern is really just a fresh take on the Model View Controller pattern that many developers are already familiar with; the key distinction is that MVP truly separates the UI from the domain/service layer of the application. Although this example was fairly simple from a requirements perspective, it should help you abstract the interaction between a UI and the other layers of your applications. Also, you should now see ways that you can use these layers of indirection to make your application more testable with automation. As you delve deeper into the MVP pattern, I hope you'll find other ways to pull as much formatting and conditional logic out of your codebehinds and place them into testable view/presenter interaction models.

Title Time Script

2009-07-20T01:41:00.000-07:00

This script displays the live time in the title of your surfer's browser! It does not erase the default title of the document, but rather, simply appends the time to it. Like the Title date script, the script manipulates the IE 4+ and NS6+ exclusive document.title property, so it's only visible to IE 4+/NS6+ browsers.

<script language="JavaScript" type="text/javascript">

var scroller;
var ampm;
var actualtitle=document.title+" "
function antiMilitaryTime()
{
if (hr == "12"){
ampm="P.M."
}
else if (hr == "13"){
hr="1"
ampm="P.M."
}
else if (hr == "14"){
hr="2"
ampm="P.M."
}
else if (hr == "15"){
hr ="3"
ampm="P.M."
}
else if (hr == "16"){
hr = "4"
ampm="P.M."
}
else if (hr == "17"){
hr = "5"
ampm="P.M."
}
else if (hr == "18"){
hr = "6"
ampm="P.M."
}
else if (hr == "19"){
hr = "7"
ampm="P.M."
}
else if (hr == "20"){
hr = "8"
ampm="P.M."
}
else if (hr == "21"){
hr = "9"
ampm="P.M."
}
else if (hr == "22"){
hr = "10"
ampm="P.M."
}
else if (hr == "23"){
hr = "11"
ampm="P.M."
}
else if (hr == "24"){
hr = "12"
}
}
function addZero(){
if (min <= "9"){
min = "0"+min
}
if (sec<= "9"){
sec = "0"+sec
}
if (hr <=9){
hr = "0"+hr
}
}
function time(){
dt=new Date()
sec=dt.getSeconds()
hr=dt.getHours()
ampm="A.M."
min=dt.getMinutes()
}
function scroll() {
time()
antiMilitaryTime()
addZero()
var scroller="TIME: "+hr+":"+min+":"+sec+" "+ampm
var timeout=setTimeout("scroll()", 1000)
document.title=actualtitle+scroller
}
if (document.all||document.getElementById)
scroll()

</script&rt;

How to find the current IIS version installed in your system

2009-07-16T21:29:00.000-07:00

Hi All

I was wondering the other day where i could find my IIS Version number and once when i was looking at the documentation provided then i noticed that the simplest way to find out the version number is by simply typing the URL

http://localhost/iisHelp/iis/misc/default.asp

Thats it...

Regards
Mansoor Ali

Building an N-Tier Application in .NET

2009-07-07T03:50:00.000-07:00

Building an N-Tier Application in .NET
Upgrading to Microsoft .NET
Paul D. Sheriff
PDSA, Inc.
February 2002
Summary: After reviewing the types of n-tier applications, you'll learn how to create a typed dataset that can return data from a Web service and consume a Web service from a Windows application. (17 printed pages)
Objectives
· Review the types of n-tier applications.
· Review the goals of a good n-tier application.
· Learn how to create a typed dataset that can return data from a Web service.
· Consume a Web service from a Windows application.
Assumptions
· You have developed an n-tier application.
· You are very familiar with classes.
· You have used the OleDbDataAdapter and DataSets.
· You know how to create a Typed DataSet.
· You can create a form in Microsoft® Visual Studio® .NET.
· You are familiar with relational databases and Microsoft ADO.NET.
· You have created a Web service in Microsoft® .NET.
Contents
Types of N-Tier Applications
Creating an N-Tier Application
What's Different from Visual Basic 6.0?
Summary
Types of N-Tier Applications
There are many types of n-tier applications that programmers have developed over the years. Since classes were first introduced in Microsoft Visual Basic® 4.0, many programmers have attempted to come up with the definitive method of creating n-tier applications. Although all of them are clever, there has never been a consensus on how every n-tier application should be constructed. As many programmers as there are in the world, there seem to be that many methods of developing n-tier applications. Below is a list of some of the possible ways.
· Create a component with one class that returns a disconnected ADO recordset for any SQL statement sent to it. All updates are performed in the ADO recordset, then passed back to the component for batch updating.
· Create a component that has many classes based around business processes. All data for the business process is passed to this component. The component will update the appropriate tables from the data supplied. Another component would be used to return views of data needed to support the user interface for a business process.
· Create one class per table using ADO embedded into the class. This is a logical n-tier model where both the EXE and the classes (in a DLL) are installed on the client machine.
· Create one class per table using ADO on the client side. This client-side class passes SQL through DCOM to a server-side Data Class running under MTS that returns a disconnected ADO recordset to the client side.
· Create two classes per table, one with properties that can be set from the client side EXE. These properties are then bundled into an XML string and passed across DCOM to a server-side class running under MTS. The XML contains instructions on how to gather or modify the data, and a disconnected ADO recordset is returned back to the client-side class.
· Create one class per table using a DOMDocument object on the client side to process all the data. The client-side class bundles up properties into an XML string that is then sent to a server-side data class. The server side extracts the instructions (the SQL) from the XML and performs the appropriate action on the database server. The server-side class then returns XML to the client-side class to inform the client what happened on the database. In this scenario, there is only one data class for all tables.
· Create one server-side class for all tables. The client side passes SQL to the server-side class and it returns a disconnected ADO recordset. All forms then use these ADO recordsets for processing data.
· Create a client-side EXE that sends a SOAP request to a Web server for XML data. XML is processed on the client side using the DOMDocument object.
As you can see, there are many different methods for creating an n-tier application. They all work, and they each have advantages and disadvantages. The goal of this document is not to dispute any of these, nor to look at the advantages and disadvantages, but to simply present a way to create n-tier data classes within Visual Basic .NET.
In this document, you will learn to create a Typed DataSet using built-in tools in Visual Studio .NET. A Typed DataSet is inherited from the DataSet class in .NET. It also provides you with properties that match up to each column in the base table. You use a standard data adapter object to fill up the DataSet from the data source. The wizard that generates this Typed DataSet reads the schema information from the data source and maps these data types to each of the columns. This is why this is called a Typed DataSet.
A Typed DataSet will help you speed up your development process in a few ways. First, you no longer have to remember column names; you will have a Microsoft® InteliSense® list after creating an object from this DataSet class. This avoids run-time errors as column names can be checked at compile time. Second, you no longer have to see SQL in your front-end client application. All of the SQL is buried in the data adapter. By putting these Typed DataSets into a separate component, you are able to reuse these classes from multiple projects.
Goals of a Good N-Tier Application
N-tier design came about as a result of the failings of the client/server model. There are many goals that an n-tier application design should achieve. Here are some of them.
· If you change the underlying data access methods, the client-side code should not have to change.
· All data access routines should be exposed as objects instead of function calls. As an example, it is much easier to use ADO than the ODBC API calls.
· SQL should be eliminated from the client-side code. The client code should just be concerned with methods and properties.
· Table and column names should be eliminated from the client-side code. Typed datasets can present table and column names as properties, providing an IntelliSense list, as opposed to having to type in a string name. This means at compile time, checks can be made for data types and names of columns.
· The client code should not care where the data comes from. It should just care that it can retrieve and modify the data in some object and the object will take care of the details.
· The coding you need to do on the client side should be simplified. Instead of using many functions, your application should be able to use objects with properties and methods.
· It becomes easier to create and use the classes than the function calls.
· It becomes easier to add functionality to your applications, and change the functionality, without breaking the client-side code.
Disadvantages to N-Tier
Although there are many advantages to a good n-tier application, there are some disadvantages as well.
· You end up creating a lot of classes. This can lead to maintenance issues and could even be a performance issue as it does take time to create a new class at run time.
· N-tier does not work well when you do not know the structure of the tables from which you will be retrieving data. For example, in a Query By Example (QBE) application where the user may put together several columns from several tables, there is no way to generate classes on the fly to accomplish this.
· Creating reports is not something that lends itself to a good n-tier design, as report writers do not use classes to get at data.
In the end, the advantages of a good n-tier design will far outweigh the disadvantages. In the cases where you simply cannot use n-tier, go ahead and use the typical client/server method of development. There is certainly nothing wrong with mixing both of these paradigms in the same application if appropriate.
Creating an N-Tier Application
When you talk about a true distributed n-tier type of application, you are talking about separating the components of the different tiers on different machines as well as in separate components. Figure 1 shows a typical example of an n-tier application with multiple components on each machine.

Figure 1. A distributed n-tier application has three physical tiers with one or more logical tiers on each machine
There are many different ways you could configure an n-tier application. For example, the business rules may go on a separate machine and you might use .NET Remoting to talk from the client application to the business rule tier as shown in Figure 2.

Figure 2. Business rules can be placed on a separate machine to facilitate ease of maintenance
You may also have a data input validation rule component on the client to check simple rules such as required fields and formatting. These are rules that you do not want to make a trip across the network just to check. You may then also add a business rule layer on the same tier as the data layer component to check complicated business rules that compare the data from one table to another.
These are just a few different configurations that you may utilize. Of course, you could come up with something unique that fits your specific situation. Regardless of how you structure the physical implementation of the components, make sure that the logical structure of the program is broken up into components as shown in the above figures.
Creating the User Interface
In the example you see in Figure 1, the client tier consists of a Windows application and a business rule component. The Windows application makes all requests for data, and all updates through the business rule component. This isolates the location of the data from the Windows application. The advantage of doing this is if you change where the data comes from, you do not need to make any changes to the client application, only to the business rule component.

Figure 3. This is a DataGrid that has been bound to the return result from the business rules component
Perform the following steps to build a simple Windows client application that will display employee information in a DataGrid control on a Windows Form.
1. Create a new Windows Application project named EmpClient.
2. Rename the default form (Form1.vb) file name to frmEmpInfo.vb.
3. Set the form's Name property to EmpInfo.
4. Set the form's Text property to Employee Information.
5. Set the Startup Object in the Project Properties to EmpInfo.
6. Drag a DataGrid onto this form. Set the Name property to grdEmps.
7. Add a Button control to this form. Set the Name property to btnUpdate. Set the Text property to Update.
At this point, the user interface for your employee form is complete. Now it is time to start building the components so you can retrieve the data to populate this DataGrid.
Creating the Data Tier
The data tier is responsible for connecting to your data source, building a typed data set, and returning that data set from a method within this component.
Follow these steps to build a data tier component.
1. In the Solution Explorer window, right-click the solution named EmpClient.
2. On the shortcut menu, click Add, and then click New Project.
3. Choose the Class Library template. Set the name of this class library to EmpData.
4. Delete the class file named Class1.vb from the project.
5. To add a new component to the project, on the Project menu, click Add Component. Set the name of the component to clsEmp.vb.
6. View the code for this component and change the name of the class from clsEmp to Employees.
7. In the design view, click and drag a SqlDataAdapter control from the Data tab of the toolbox onto the design surface of this component.
8. Go through the steps of this wizard to connect to your SQL Server, pointing to the Northwind database on that server. Select all rows and columns from the Employee table within this database.
9. Rename the SqlConnection object from SqlConnection1 to cnNorthwind. Rename the SqlDataAdapter object from SqlDataAdapter1 to daEmps.
10. Click the daEmps object and, on the Data menu, click Generate DataSet. Set the name of the New DataSet to dsEmps.
At this point, you have a component with some data access objects on it. The reason to use a component instead of a regular class is that you need the ability to drag a Connection and DataAdapter object onto a design surface. A component will let you do this; a regular class will not. Of course, you could always just create your own data adapter and connection objects in code, but this way is much easier.
All that is left to do after adding this component is to add a couple of methods to the component. The first method, named GetData, returns a reference to the typed dataset filled with employee data. The second method, named Update, accepts a typed dataset as a parameter and submits the changes in this dataset to the backend data source.
The GetData Method in the Data Component
This method is responsible for declaring an object of the type dsEmps. Remember that this is the name of the typed dataset that you generated. This file is represented in the Solution Explorer window as dsEmps.xsd. This is the schema definition file for the Employees table from the Northwind database. The code behind this xsd file is the generated typed dataset named dsEmps.
Public Function GetData() As dsEmps
Dim dsData As dsEmps

Try
dsData = New dsEmps()

daEmps.Fill(dsData)

Return dsData
Catch
Throw
End Try
End Function
After creating the instance of the dsEmps object, you will use the SqlDataAdapter object to fill the dsEmps object with data from the Employees table. This typed dataset is then returned from the method to be consumed by some other component. You will learn how to consume this dataset in the next section.
The Update Method in the Data Component
The Update method accepts a typed dataset from a calling program, and performs the Update method on the SqlDataAdapter object to send any changes in the dataset to the Employees table. It also returns this same dataset back to the calling program so any updated fields, like TimeStamps or Identity fields, can be merged back into the dataset in the calling program.
Public Function Update(ByVal dsData As dsEmps) As dsEmps
Try
' Update Data In Table
daEmps.Update(dsData)

Catch
' Throw any exceptions back to client
Throw

End Try

Return dsData
End Function
Notice the use of the structured exception handling in both of these methods. If any errors are encountered, the exceptions are simply thrown back to the calling component for handling. No errors will be handled in these components.
Creating the Web Service
In Figure 1, you can see that the Data Tier is called from the Web service. You will now create the Web service project that calls the EmpData component that you just created.
1. In the Solution Explorer window, click the solution named EmpClient.
2. Right-click and on the shortcut menu, click Add, and then click New Project.
3. Select the ASP.NET Web service template and set the Name of this project to EmpWS.
4. Delete the Service1.asmx file from the project.
5. To add a new Web service file, on the Project menu, click Add Web service. Set the name of this new Web service to Employees.asmx.
6. Click the EmpWS project and add a reference to the EmpData project.
Now that you have created the Web service named Employees, you can create the two methods that will consume the dataset from the data component. You name these two methods the same name as the methods in the data component for consistency.
The GetData Method in the Web Service
The GetData method in the Web service creates a reference to the EmpData.Employees class. Once you have created this new object, invoke the GetData method on this object and return the dataset from this Web method.
Public Function GetData() _
As EmpData.dsEmps
Dim dc As EmpData.Employees

Try
dc = New EmpData.Employees()

Return dc.GetData

Catch
' Throw any exceptions back to client
Throw

End Try
End Function
Remember from the diagram in Figure 1 that the business rule component retrieves the data from this Web service. The reason you don't just create the typed data set in the Web service project is that you want the flexibility to use the data component directly from a Windows application if you are not going to be doing a true distributed application. So just by eliminating the Web service project, you can change the code in the business rule layer to talk directly to the data component instead of the Web service, and you do not have to change any other code.
The Update Method
The Update method in this Web service project simply passes the dataset that was passed in as an argument directly to the data component's Update method.
Public Function Update( _
ByVal dsData As EmpData.dsEmps) As EmpData.dsEmps
Dim dc As EmpData.Employees

Try
dc = New EmpData.Employees()

dc.Update(dsData)

Catch
' Throw any exceptions back to client
Throw

End Try

Return dsData
End Function
Creating the Business Rules Component
Now it is time to bring all of the projects together by hooking up the business rule component to the Windows application, and having the business rule component make the call to the Web service to retrieve the data.
Follow the steps below to create the component that acts as the interface between the Windows application and the data tier.
1. In the Solution Explorer window, right-click the EmpClient solution, and on the shortcut menu, click Add, and then click New Project.
2. Select the Class Library template and set the Name of this new project to EmpBusRule.
3. Rename the Class1.vb file to clsEmployee.vb.
4. Change the name of the Public Class from Class1 to Employees.
5. To ensure that the solution compiles and to also build the Web service files needed for referencing, on the Build menu, click Build Solution.
6. Click the EmpBusRule project and add a Web Reference to the EmpWS.vsdisco file.
Note If you cannot set a reference to the vsdisco file, set a reference to the Employees.asmx file instead.
7. Expand the Web References folder in this project and rename the LocalHost item (or whatever the name of your Web Server is) to EmpService.
The GetData Method in the Business Rules Component
The GetData method in the business rule component simply accesses the Web service component to request the data from the data tier. Although this seems to be an indirect approach to getting the data, it allows us to create a truly distributed approach to our n-tier application.
Public Function GetData() As EmpService.dsEmps
Dim ws As EmpService.Employees

Try
ws = New EmpService.Employees()

Return ws.GetData

Catch
Throw

End Try
End Function
The Update Method in the Business Rules Component
The Update method is responsible for taking the dataset of changes, checking to make sure that the business rules are not violated, and then pushing the data back across the HTTP interface to the Web service component.
Public Function Update( _
ByVal dsData As EmpService.dsEmps) As EmpService.dsEmps
Dim ws As EmpService.Employees

Try
ws = New EmpService.Employees()

' Check business Rules
Me.Check(dsData)

' Update Data In Table
ws.Update(dsData)

Catch
' Throw any exceptions back to client
Throw

End Try

Return dsData
End Function
The Check Method in the Business Rules Component
The Check method is where you put in any appropriate business rules that you need to check prior to inserting or updating any rows in the dataset.
Public Sub Check(ByVal dsData As EmpService.dsEmps)
Dim strMsg As String
Dim row As EmpService.dsEmps.EmployeesRow

' Check business rules
For Each row In dsData.Employees.Rows
If row.RowState = DataRowState.Added Or _
row.RowState = DataRowState.Modified Then
If row.FirstName.Trim() = "" Then
strMsg &= "First Name must be filled in" & _
ControlChars.CrLf
End If
If row.LastName.Trim() = "" Then
strMsg &= "Last Name must be filled in" & _
ControlChars.CrLf
End If
If row.HireDate < row.BirthDate Then
strMsg &= "Hire Date must be greater
than Birth Date" & _
ControlChars.CrLf
End If
End If
Next

If strMsg <> "" Then
' Throw a new ApplicationException
' with our custom error message in it
Throw New ApplicationException(strMsg)
End If
End Sub
Notice that you should check the RowState property to see whether the row has been added or updated. You do not need to check the row if it has been deleted. During the Check method you can see the benefit of using Typed Datasets. Instead of referencing a column name through an index in a Dataset you have an actual property name. This enforces type safety and gives you an IntelliSense list of column names so you do not have to look them up in your database.
Consuming the Data from the Windows Application
Now that you have all of the components hooked up together, it is time to make the Windows application consume data from the business rules component. Follow the steps below to make this work.
1. In the Solution Explorer window, click the EmpClient project.
2. Add a reference to the EmpBusRule project.
3. Bring up the code for the form and add the Private mdsEmps variable, as shown in the code below.
4. Public Class EmpInfo
5. Inherits System.Windows.Forms.Form
6.
Private mdsEmps As EmpBusRule.EmpService.dsEmps
This member variable on this form is a typed dataset that represents the Employees table in the Northwind database. You use this variable to fill the DataGrid control on the employee form.
Loading Data into the DataGrid
Next, you need to create the routine to load the DataGrid. First, you add code to the form's Load event procedure.
1. Double-click the form to display the Load event procedure.
2. Add the following code:
3. Private Sub EmpInfo_Load( _
4. ByVal sender As System.Object, _
5. ByVal e As System.EventArgs) Handles MyBase.Load
6. GridLoad()
End Sub
7. Create the GridLoad procedure immediately below the Load event procedure.
8. Public Sub GridLoad()
9. Dim br As EmpBusRule.Employees
10.
11. Try
12. br = New EmpBusRule.Employees()
13.
14. ' mdsEmps is a typed dataset on this form
15. mdsEmps = br.GetData
16.
17. grdEmps.DataMember = "Employees"
18. grdEmps.DataSource = mdsEmps
19.
20. Catch exp As Exception
21. MsgBox(exp.Message)
22.
23. End Try
End Sub
The GridLoad procedure declares a new business rule object. It then uses the GetData method in the business rule object to assign the new typed dataset mdsEmps. You assign the table name to the DataMember and the dataset object to the DataSource property of the DataGrid control.
You should now be able to run this application, and if you have done everything correctly, the DataGrid should be loaded with employee data.
1. Set the EmpClient project as the Startup Project if it is not already.
2. Press F5 to run the application.
You should now see employee data in the list box that looks like Figure 2.
Updating Data from the DataGrid
You will now create the procedure under the Update button on the form to take any changes you make to the data in the grid and push them back to the data tier. Of course, you will first push the changes to the DataSet through the business rules component, which then sends the data through the Web service component, and then finally to the data tier component. The data tier component then connects up to the SQL Server and pushes the changes to the server.
Private Sub btnUpdate_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim br As New EmpBusRule.Employees()
Dim dsChanges As New EmpBusRule.EmpService.dsEmps()
Dim strMsg As String

Try
If mdsEmps.HasChanges Then
dsChanges.Merge(mdsEmps.GetChanges())

MsgBox("Count = " & _
CStr(dsChanges.Employees.Rows.Count))

' Update the Data on the Server
dsChanges = br.Update(dsChanges)

MsgBox("Data Has Been Updated Successfully")

' You can either merge the changes back
' in and accept the changes, or you can
' reload the data from the table. I prefer
' reloading as it gets all the other
' changes by all other users.
'mdsEmps.Merge(dsChanges)
'mdsEmps.AcceptChanges()

' If everything is OK, refresh all data
' from the web service.
GridLoad()
Else
MsgBox("No changes have been made
to the data")
End If

Catch exp As ApplicationException
MsgBox(exp.Message)

Catch exp As Exception
MsgBox(exp.Message)

End Try
End Sub
The first part of this event procedure checks to see whether any changes have even been made to the DataSet. If they have, you get the changed data rows by using the GetChanges method of the DataSet class. This will return just those rows that were changed in the DataGrid.
Once you have this short list of rows from the dataset you can submit that to the Update method of the business rule component. The business rule component then sends it to the Web service component, which sends it on to the data tier component, and finally updates SQL Server. If any changes are made to the rows in the dataset (such as a TimeStamp field updating, or an identity field updating), these rows are passed back to this procedure. You can then either merge the data back into the main dataset, or you could simply reload the entire dataset directly from the database (going through the components of course).
When attempting to submit the changes to the back end, there could be a business rule that is violated. For example, if someone deletes the first name of an employee, the business rule that checks for the existence of the first name in a row of data will fail. This will be thrown as an ApplicationException object. You should check for this type of exception first, then display the error message returned, as that will be the description of the business rule or rules that are in violation.
The last thing you have to check for is any generic exceptions. For example, the database server might be down, and thus a regular exception might be thrown. Or maybe a concurrency exception is thrown because you updated a row and another user updated a row just before you did.
What's Different from Visual Basic 6.0?
Although the concepts for designing an n-tier application are pretty much the same as they have always been, the implementation is quite different. You should find that the amount of code you have to write is significantly reduced due to the advancements in Web services and ADO.NET.
Summary
In this document, you learned how to put together an n-tier application. Although you are shown just one of the many ways you could accomplish this, the format used here is very simple and easy to create. While the component you build here may be created on one machine, you can easily move the components from one machine to another and scale this solution using .NET Remoting and Web services. Whether you choose a logical or a physical implementation for your applications, you should always strive to develop your application using separate components for each process.

Working with MDI Applications and Creating Menus

2009-07-07T03:46:00.000-07:00

Working with MDI Applications and Creating Menus

Upgrading to Microsoft .NET
Paul D. Sheriff, PDSA, Inc.
Ken Getz, MCW Technologies
January 2002

Summary: Introduces you to the concept of Multiple Document Interface (MDI) and how to create menus within an MDI application. (20 printed pages)

Objectives
· Learn to create MDI parent forms
· Learn to create MDI child forms
· Learn to create and use menus

Assumptions
The following should be true for you to get the most out of this document:
· You are familiar with Microsoft® Visual Studio® .NET
· You are familiar with designing forms and other user interfaces

Contents
MDI Overview
Create an MDI Project
Creating Menus
Working with MDI Child Forms
Creating Shortcut Menus
Manipulating Menus at Run Time
How Did the Code Work?
Summary

MDI Overview

This document introduces you to the concept of Multiple Document Interface (MDI) and how to create menus within an MDI application. You will learn to create an MDI application in Microsoft Visual Studio .NET and learn why you might want to use this type of interface. You will learn about child forms that are contained within the MDI application, and learn to create shortcut, or context-sensitive, menus.
MDI is a popular interface because it allows you to have multiple documents (or forms) open in one application. Examples of MDI applications include Microsoft Word, Microsoft Excel, Microsoft PowerPoint®, and even the Visual Studio integrated development environment itself. Each application consists of one (or more) parent windows, each containing an MDI client area—the area where the child forms (or documents) will be displayed. Code you write displays as many instances of each of the child forms that you want displayed, and each child form can only be displayed within the confines of the parent window—this means you can't drag the child forms outside the MDI container. Figure 1 shows a basic MDI application in use.

Figure 1. Use MDI to open multiple windows and have them all contained within the parent area
Single Document Interface
Don't be misled: MDI is only one of several possible paradigms for creating a user interface. You can also create applications that display just a single form. They're easier to create, in fact. Those applications are called Single Document Interface (SDI) applications. Microsoft Windows® Notepad is an SDI application, and you can only open a single document at a time. (If you want multiple documents open, you simply run Notepad multiple times.) You are under no obligation to create your applications using the MDI paradigm. Even if you have multiple forms in your project, you can simply have each one as a stand-alone form, not contained by any parent form.

Uses of MDI

You'll use MDI most often in applications where the user might like to have multiple forms or documents open concurrently. Word processing applications (like Microsoft Word), spreadsheet applications (like Microsoft Excel), and project manager applications (like Microsoft Project) are all good candidates for MDI applications. MDI is also handy when you have a large application, and you want to provide a simple mechanism for closing all the child forms when the user exits the application.

Creating an MDI Parent Form

To create an MDI parent form, you can simply take one of your existing forms and set its IsMDIContainer property to True. This form will now be able to contain other forms as child forms. You may have one or many container forms within your application.
Tip Note the difference here between Visual Studio .NET and Microsoft Visual Basic® 6.0 behavior. In Visual Basic 6.0, you could only have a single MDI parent form per application, and you had to use the Project menu to add that one special form. In Visual Studio .NET, you can turn any form into an MDI parent form by simply modifying a property, and you can have as many MDI parent forms as you require within the same project.
You may have as many different child forms (the forms that remain contained within the parent form) as you want in your project. A child form is nothing more than a regular form for which you dynamically set the MdiParent property to refer to the MDI container form.
Note The user interface objects you've designed within the Visual Studio environment are really templates for forms. That is, they don't actually become real Form objects until you instantiate them at run time. Therefore, your project can contain as many different templates for MDI child forms as you like. You can instantiate and then show as many instances of as many different templates as you need, while your applications are running.
Run-time Features of MDI Child Forms
At run time, the MDI parent form and the MDI child forms take on special features:
· All child forms are displayed within the MDI parent's client area. The client area is the area below the MDI parent's title bar, any menus, and any tool bars.
· Child forms can be moved and sized only within the MDI parent's client area.
· Child forms can be minimized and their icon will be displayed within the parent's client area.
· Child forms can be maximized within the parent's client area and the caption of the child form is appended to the caption of the MDI form.
· Windows automatically gives child forms that have their FormBorderStyle property set to a sizable border a default size. This size is based on the size of the MDI parent's client area. You can override this by setting the FormBorderStyle property of the child form to any of the fixed type of borders.
· Child forms cannot be displayed modally.
· The MDI form can be minimized and only one icon will be displayed on the desktop representing the MDI form and all of its children.
· If the MDI form is unloaded, all of the loaded children will also be unloaded.
Note The client area includes any usable area on the MDI form minus any toolbars or status bars that you may have added to the MDI form.
Create an MDI Project
In this section, you will walk through the steps of creating a simple MDI application using Visual Studio .NET. To do this, you will create a new form that will be the MDI parent form. You will add some menus to this new form, and then you will load the product form from a menu as a child form.
Create the MDI Parent Form
To create the MDI parent form
1. Open Visual Studio .NET
2. Create a new Windows application project.
3. Set the name of the project to MDI.sln.
4. Rename the form that is created automatically to frmMain.vb.
5. With the frmMain selected, set the form's IsMdiContainer property to True.
6. Set the WindowState property to Maximized.
That's all there is to it: you've created an MDI parent form.

Creating Menus
Your main form will require menus so that you can perform actions such as opening child forms, copying and pasting data, and arranging windows. Visual Studio .NET includes a new menu designer that makes creating and modifying menus a snap.
To add menus to your MDI parent form
1. Double-click the MainMenu tool in the Toolbox window to add a new object named MainMenu1 to the form tray.
Note Unlike the form designer in Visual Basic 6.0, the Visual Studio .NET form designer places controls that don't have a user interface at run time into a special area on the form designer: the form "tray". They're out of the way, and don't get buried underneath other controls. This is a real improvement!
2. At the top of the MDI parent form, click the box with Type Here in it and type &File.
Tip Just as in Visual Basic 6.0, inserting an ampersand (&) into a menu caption displays the caption with an underscore under the following letter. Pressing Alt+ acts as a hotkey, activating the menu item. One thing to note: if you're using Windows 2000 or later, it's possible that the hotkeys won't show up underlined until you press the Alt key. This setting is buried in the Display applet within Control Panel. In the Display Properties dialog box, check the Effects page: the Hide keyboard navigation indicators until I use the Alt key option controls this behavior.
3. Press Enter to move to the next menu item and type &Products.
4. Press Enter to move to the next menu item and type a hyphen (-).
Tip Rather than using the "-" to indicate a divider in the menu, you can insert the next menu item (Exit, in this case), and then right-click the new item. Select "Insert Separator" from the context menu, and Visual Studio .NET will insert a separator above the current item for you.
5. Press Enter and type E&xit.
You have now created the first drop-down menu on your main form. You should have something that looks like Figure 2.

Figure 2. The menu designer allows you to type your menu structure in a WYSIWYG fashion
To the right of the File menu and at the same level, you'll see another small box with the text, Type Here. Click it and type the following menu items by pressing Enter after each one.
· &Edit
· Cu&t
· &Copy
· &Paste
Once more to the right of the Edit menu and at the same level, add the following menu items in the same manner.
· &Window
· &Cascade
· Tile &Horizontal
· Tile &Vertical
· &Arrange Icons

Creating Names for Each Menu
After creating all the menu items, you'll need to set the Name property for each. (Because you'll refer to the name of each menu item from any code you write concerning that menu item, it's important to choose a name you can understand from within your code.) Instead of clicking each menu item one at a time and then moving over to the Properties window to set the Name property, Visual Studio provides a shortcut: Right-click an item in the menu, then select Edit Names from the context menu. Now you can simply click each menu item and set the name property directly on each menu. This is certainly quicker than using the Properties window to accomplish the same task.

Use the following names for your menu items:
· mnuFile
· mnuFProducts
· mnuFExit
· mnuEdit
· mnuECut
· mnuECopy
· mnuEPaste
· mnuWindow
· mnuWCasade
· mnuWHorizontal
· mnuWVertical
· mnuWArrange

Test out your application: Press F5 and you should see your main MDI window appear with your menu system in place.
Display a Child Form
To add the code that displays the child form, frmProducts, make sure the main form is open in Design view, and on the File menu, double-click Products. Visual Studio .NET will create the stub of the menu item's Click event handler for you. Modify the procedure so that it looks like the following:
Private Sub mnuFProducts_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles mnuFProducts.Click
Dim frm As New frmProducts()

frm.MdiParent = Me

frm.Show()
End Sub
This code declares a variable, frm, which refers to a new instance of the frmProducts form in the sample project. Then, you set the MdiParent property of the new form, indicating that its parent should be the current form (using the Me keyword). Finally, the code calls the Show method of the child form, making it appear on the screen.

Some interesting things to note:
· Me is a built-in keyword in Visual Basic .NET—just as in Visual Basic 6.0, this keyword refers to the class whose code is currently running. In this case, that's the MDI parent form, whose menu item you just clicked.
· You don't have to set the MdiParent property of the new child form. If you don't, the form will simply load as a new normal form, outside the MDI parent. As a matter of fact, you can set the MDI parent to be a different MDI container if you like.
· If you don't call the Show method, the child form won't ever display.
Differentiating Between Child Windows
You'll note that each instance of the Products form looks identical. You'll most likely need some way to differentiate the windows. One alternative is to modify the caption of the window as you load each instance. In the sample, you can create a static variable to contain a counter, increment that variable each time you open a form, and then assign that value into the Text property of the form.
To uniquely identify each child form
1. Modify the mnuFProducts_Click procedure so that it looks like this:
2. Private Sub mnuFProducts_Click( _
3. ByVal sender As System.Object, _
4. ByVal e As System.EventArgs) _
5. Handles mnuFProducts.Click
6. Dim frm As frmProducts
7. Static intCount As Integer
8.
9. frm = New frmProducts()
10.
11. ' Increment the caption counter.
12. intCount += 1
13.
14. ' Set the caption to be unique.
15. frm.Text = frm.Text & " " & intCount.ToString()
16.
17. frm.MdiParent = Me
18. frm.Show()
End Sub
19. Run the project, create a few Products forms, and note that the caption of each form includes a different number.
Note What's that Static keyword? Using Static, rather than Dim, to declare a variable inside a procedure creates a variable that maintains its value from one invocation of the procedure to the next. When you declare a variable using Dim, that variable gets reinitialized each time the procedure is called. When you use Static, the variable maintains its value. That's what you want, in this case—you want intCount to maintain its value, so that it continues to increment each time you create a new instance of frmProducts.

Child Menus in MDI Applications

What if a child form has its own set of menus? How do those menus interact with the menus of the parent form? In previous versions of Visual Basic you really didn't have much control over the behavior—the menus of the currently active child simply replaced the menus of its parent. In Visual Studio .NET, however, you can control how the menus interact, using the MergeOrder and MergeType properties of the individual menu items.

The MergeOrder property controls the relative position of the menu item when its menu structure gets merged with the parent form's menus. The default value for this property is 0, indicating that this menu item will be added at the end of the existing menu items. The MergeType property controls how the menu item behaves when it has the same merge order as another menu item being merged. Table 1 shows a list of the possible values you can assign to the MergeType property.
Table 1. The MergeType property allows you to specify what happens when menu items merge
Value Description
Add The MenuItem is added to the collection of existing MenuItem objects in a merged menu. (Default)
MergeItems All submenu items of this MenuItem are merged with those of existing MenuItem objects at the same position in a merged menu.
Remove The MenuItem is not included in a merged menu.
Replace The MenuItem replaces an existing MenuItem at the same position in a merged menu.

By default, a menu item's MergeOrder property is set to 0. The MergeType property is set to Add by default. This means that if you create a child form with a menu on it, the menu will be added at the end of the main menu. Consider Figure 3, which shows a child form called from the parent form's main menu. This form has a Maintenance menu on it (and the parent form does not). All of the items on the parent's main menu have their MergeOrder properties set to 0 and this menu's MergeOrder property is set to 0, so this menu will be added at the end of the main menu on the MDI parent form.

Figure 3. A child form that has menus will by default be added to the end of the main menu
To create the form in Figure 3
1. On the Project menu, click Add Windows Form.
2. Set the new form's name to frmChildWithMenus.vb.
3. Add a MainMenu control to this form.
4. Set the Name property for the MainMenu control to mnuMainMaint.
5. Add the following menus as shown in Table 2.

Table 2. Windows Form menus
Menu Name
&Maintenance mnuMaint
&Suppliers mnuMSuppliers
&Categories mnuMCategories
If you were to call this form exactly like you did the Products form in the previous section you will see that your main form looks like Figure 4. You can see that by default, the menu is added to the end of this form.

Figure 4. Menus are added to the end of the main menu by default
Call this form by adding a new menu item under the File menu:
1. Open frmMain.vb in Design view.
2. Click on the separator after the Products menu item and press the Insert key to add a new menu item.
3. Type Child form with Menus as the text of this new menu item.
4. Set the Name property of this new menu item to mnuFChild.
5. Double click this new menu item and modify its Click event handler so that it looks like this:
6. Private Sub mnuFChildMenus_Click( _
7. ByVal sender As System.Object, _
8. ByVal e As System.EventArgs) _
9. Handles mnuFChildMenus.Click
10. Dim frm As New frmChildWithMenus()
11. frm.MdiParent = Me
12. frm.Show()
End Sub
Note If you wish to merge the Maintenance menu in between the Edit and Window menus, you could set the MergeOrder property on the Edit menu item to 1, and the MergeOrder property on the Window menu to a 2. Then on the Maintenance menu item on frmChildWithMenus, set the MergeOrder property to 1 and leave the MergeType with its default value, Add. Taking these steps will add the Maintenance menu after the menu on the main form with the same MergeOrder number as it has (that is, after the Edit menu, but before the Window menu).
Working with MDI Child Forms
If you have multiple child forms open, you may want to have them arrange themselves, much as you can do in Word or Excel, choosing options under the Window menu. Table 3 lists the available options when arranging child windows.
Table 3. Choose one of these values when arranging child windows
Menu Item Enumerated Value
Tile Horizontal MdlLayout.TileHorizontal
Tile Vertical MdiLayout.TileVertical
Cascade MdiLayout.Cascade
Arrange Icons MdiLayout.ArrangeIcons
Add some menus to your main form for each of these options:
1. Open frmMain.vb in Design view.
2. On the Window menu, double-click Cascade.
3. For the Cascade menu item, modify the Click event handler so that it looks like the following:
4. Private Sub mnuWCascade_Click( _
5. ByVal sender As System.Object, _
6. ByVal e As System.EventArgs) _
7. Handles mnuWCascade.Click
8. Me.LayoutMdi(MdiLayout.Cascade)
End Sub

On the Window menu, double-click each menu item and add the appropriate code.
Tip The LayoutMDI method replaces the Arrange method you may have used in Visual Basic 6.0.

What If There's No Active Child Form?
If there's no active child form, attempting to work with the ActiveMdiChild property of the parent form will trigger a run-time error. To avoid this situation, you can check the value of the property in the Click event handler for the Window menu item, and enable or disable the Center Child Form menu item accordingly. To add this feature, follow these steps:
1. With frmMain open in Design view, press F7 to edit the form's code module.
2. Select mnuWindow from the Class Name combo box (the list on the top left of the editor window), and then select Select from the Method Name combo box (the list on the right).
3. Modify the Select event handler, so that it looks like this:
4. Private Sub mnuWindow_Popup( _
5. ByVal sender As Object, _
6. ByVal e As System.EventArgs) _
7. Handles mnuWindow.Popup
8. mnuWCenterChild.Enabled = _
9. Not (Me.ActiveMdiChild Is Nothing)
End Sub
10. 4.Run the project, and verify that if you have child windows displayed, the Center Child Form menu item is enabled. If there isn't a child form open, verify that the menu item is disabled.
How does this work? Here are some things to consider:
· The Popup event occurs when you select a menu item that includes sub-items. (You can't use the Click event, since that event doesn't occur for menu items that contain sub-items.)
· The Is operator allows you to compare values to the built-in value Nothing. In this case, the ActiveMdiChild property returns this special value if it doesn't refer to a form, and you must use the Is operator to check for this. (The = operator checks values for equality: the Is operator checks references to objects for equality.)
· The syntax of the procedure may be confusing. The value in parentheses (Me, ActiveChild, Is, Nothing) returns a Boolean value: it's either True or False. The Not operator toggles the returned value to be the opposite Boolean value. The whole line of code assigns the return value from the expression in parentheses to the Enabled property of the menu item. In this case, if it's not true that the ActiveChild is Nothing, you'll enable the menu item. If it's True, you'll disable the menu item.

Tracking Child Windows

Visual Basic .NET will keep track of all child forms that you create, and it's easy to create a window list menu to manage the child windows. If you wish to see a list of all of the child forms and be able to give a specific child form focus, follow these steps:
1. Load frmMain in Design view.
2. Select frmMain's Window menu.
3. In the Properties window, set the MdiList to True.
4. Run the project, open a couple of Products forms, and then click the Window drop-down menu. You should see each instance of the Product form that you opened displayed in the window list.

Creating Shortcut Menus

In most modern Windows applications, you can click the right mouse button and see a context-sensitive, or shortcut (pop-up), menu. These menus give you the ability to perform actions based on the current context—that is, changing depending on the current situation.
Visual Studio provides the ContextMenu control, making it just as easy to create context menus as it was to create main menus. Once you've dropped one of these controls on your form (it will appear in the tray area, just like the MainMenu control did), you can edit the menu items to be displayed by this context menu.
Note The design of your context menu won't display on the form until you've clicked the ContextMenu control. This makes it possible for one form to contain both MainMenu and ContextMenu controls.

Tip You can place as many ContextMenu controls as you need on your forms.
To add a context menu to the Products form:
1. Open frmProducts in Design view.
2. In the Toolbox, find the ContextMenu control.
3. Double-click this control to add it to the tray area of the form.
4. Click the ContextMenu control to give it focus.
5. Change the Name property to cmnuProdID.
6. Add the menu items (shown in Table 4) to this control, and set the Name properties as shown. When you add to a context menu, you start with the first menu item under the top-level menu. Figure 5 shows the finished context menu.
Table 4. Context menu items
Menu Name
&Lookup mnuPLookUp
&Copy mnuPCopy
&Paste mnuPPaste

Figure 5. When adding items to the ContextMenu control, create a top-level item that won't ever be displayed as the parent for your items
7. Click the Product ID text box.
8. In the Properties window, set the ContextMenu property to the ContextMenu control you just created, cmnuProdID.
9. Run the project. On the File menu, click Products to create a child form, and righ-click on the Product ID text box to see the context menu appear.
If you were completing this form, you could now add code to respond to the Click events on each of these menus.
Manipulating Menus at Run Time
You may need to modify the behavior of menu items while your application is running. In this section, you'll see how to check and uncheck, and add and remove menu items programmatically.
To set up for the following sections, you will need to add three new menu items under the File menu on your MDI form.
1. Open frmMain.vb in Design view.
2. Click the File menu to display its sub-items.
3. Add three menu items, as described in Table 5.

Table 5. File menu items
Menu Name
&Add Menus mnuFAddMenus
&Remove Menus mnuFRemoveMenus
&Add &New Menu mnuFAddNew
Checking and Unchecking Menu Items
Visually selecting a menu item programmatically is easy: simply set the menu item's Checked property to True or False, as necessary. For example, you might want to indicate that you've added new menu items by adding a check to the Add Menus item, and remove it once you've removed the items. You'll add and remove the menu items in the next section, but for now, add and remove the check by following these steps:
1. On the File menu, double-click Add Menus to view the Click event procedure.
2. Modify the event procedure, so that it looks like this:
3. Private Sub mnuFAddMenus_Click( _
4. ByVal sender As System.Object, _
5. ByVal e As System.EventArgs) _
6. Handles mnuFAddMenus.Click
7. If Not mnuFAddMenus.Checked Then
8. mnuFAddMenus.Checked = True
9. End If
End Sub
10. To repeat the previous two steps, on the File menu, double-click Remove Menus and modify its Click event procedure so that it looks like this:
11. Private Sub mnuFRemoveMenus_Click( _
12. ByVal sender As System.Object, _
13. ByVal e As System.EventArgs) _
14. Handles mnuFRemoveMenus.Click
15. If mnuFAddMenus.Checked Then
16. mnuFAddMenus.Checked = False
17. End If
End Sub
18. Run the project, and on the File menu, double-click Add Menus. Verify that you see a check next to the item. To repeat, on the File menu, click Remove Menus to verify that the check has been removed.
Adding and Removing Menus
Windows Forms allows you to add and remove menus programmatically at run time. Using the Add and Remove (or RemoveAt) methods of the MenuItems collection, you can create new menus at run time, and delete any menu items.
Note In Visual Basic 6.0, you could only delete menu items that you created dynamically, at run time. That is, you couldn't delete static menu items. In Visual Studio .NET, you can delete any menu item.

Adding Menus Programmatically

To add a menu item, call the Add method of a particular menu item. For example, you might want to add two new menu items on the File menu. To do this, follow these steps:
1. On the on the File menu, double-click Add Menus to view its Click event handler.
2. Modify the event procedure so that it looks like this:
3. Private Sub mnuFAddMenus_Click( _
4. ByVal sender As System.Object, _
5. ByVal e As System.EventArgs) _
6. Handles mnuFAddMenus.Click
7. If Not mnuFAddMenus.Checked Then
8. mnuFAddMenus.Checked = True
9. ' Adds these menus to the end of the File menu
10. With mnuFile.MenuItems
11. .Add("New Menu 1")
12. .Add("New Menu 2")
13. End With
14. End If
End Sub
Each menu item contained within the MainMenu (or ContextMenu) control is itself a MenuItem object, and just as with any other object, you refer to the menu items using the Name property you assigned to each. If a menu item contains other menu items (as each top-level menu item does), you can use its MenuItems property to refer to the collection of menu items it contains. In this case, you called the Add method of a MenuItem object (mnuFile) to add menu items to the collection of items. (In this example, mnuFile was the name you assigned to the File menu item.)
Removing Menus Programmatically
To remove menu items programmatically, you can either call the MenuItem collection's Remove or RemoveAt method. Remove requires you to provide a MenuItem object; if you instead want to remove items by their position within the menu, call the RemoveAt method.

In this example, the simplest way to remove the menu items you created in the previous steps is to specify their position within the menu. You'll call the RemoveAt method to do the work. Because menu items are numbered starting at 0 (as are all collections and arrays in Visual Basic .NET), you need to take that into account when removing menu items.
To handle removing the two menu items you've just added, modify the Click event procedure for the Remove Menus items on the File menu, so that the procedure looks like this:
Private Sub mnuFRemoveMenus_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles mnuFRemoveMenus.Click
If mnuFAddMenus.Checked Then
With mnuFile.MenuItems
' Remove the last two items
.RemoveAt(.Count - 1)
.RemoveAt(.Count - 1)
End With
mnuFAddMenus.Checked = False
End If
End Sub
Run the project to verify that choosing the Add and Remove Menus menu items correctly adds and removes the two extra items.
Using Menu Groups
When you add a check to a menu item, you're indicating the state of that menu item—it's either selected, or it's not. Normal checked menus work individually, and are independent of other menu items.
You may have a need to treat a group of menu items as a dependent set. In this case, selecting one item from the group forces all the other items in the group to be deselected. Although Visual Studio .NET doesn't provide a way to make this happen for you, it does supply the RadioCheck property of menu items that at least provides a visual indication. Rather than seeing a normal check, menu items with their RadioCheck property set to True display a dot when they're selected. It's still up to your code to deselect all the other items in your menu group, once the user selects a menu item.

To demonstrate this behavior, modify properties and add code so that the four window management menu items (Cascade, Tile Horizontal, Tile Vertical, Arrange Icons) work as a group. To do that, follow these steps:
1. With frmMain open in Design view, select the Window>Center Child Form menu item.
2. Right-click, and on the shortcut menu, click Insert Separator, which inserts a separator item above the selected item.
3. Click on the Window>Cascade menu item, then at the same time, press Ctrl and click the other four window management items on the menu, selecting all four.
4. In the Properties window, set the RadioCheck property for the four selected menu items to True.
5. Add the following procedure to the frmMain class:
6. Private Sub RadioCheck_Click( _
7. ByVal sender As System.Object, _
8. ByVal e As System.EventArgs) _
9. Handles mnuWArrange.Click, mnuWCascade.Click, _
10. mnuWHorizontal.Click, mnuWVertical.Click
11. mnuWArrange.Checked = False
12. mnuWCascade.Checked = False
13. mnuWHorizontal.Checked = False
14. mnuWVertical.Checked = False
15. CType(sender, MenuItem).Checked = True
End Sub
16. Run the project, create some Product forms, and use the Window menu items to arrange the children. As you use the Cascade, Tile Horizontal, and other menu items, you should see a circle next to the most recently selected item. Figure 6 shows the results of adding this new feature.

Figure 6. Setting a menu item's RadioCheck property to True shows a dot, rather than a check, next to selected items
How Did the Code Work?
It may seem odd that you managed to add new functionality to four menu items without modifying their event procedures at all. This example took advantage of a new feature in Visual Basic .NET—you can hook up as many event handlers to a specific event as you need, using the Handles clause on a procedure. Here, you provided a new procedure (RadioCheck_Click) that handles Click events for each of the four menu items you want to have work together.
Here are the important issues:
· The procedure you want to call must match the procedure signature of the standard Click event for menu items. That is, it must receive two parameters (one as System.Object, the other as System.EventArgs) and return nothing at all. (It must be a Sub, in other words.)
· You must add a Handles clause for each event you want to handle. In this case, you're handling the Click event of four different menu items.
· Within the procedure, you can use the first parameter (named sender, in this example) to figure out which object triggered the event. This example first sets each of the four items to be unchecked, and then checks the item that triggered the event.
It's also interesting to note how the RadioCheck_Click procedure set the RadioCheck property of the object it received as its first parameter. To convert the Object variable into a MenuItem type, the code calls the CType function, indicating the variable to convert, and the result type (MenuItem):
CType(sender, MenuItem).Checked = True
You'll use CType a lot in Visual Basic .NET. This important function allows you to convert variables from one type to another. Because Visual Basic .NET is so strictly typed (it's always careful about the specific data types you're working with, unlike Visual Basic 6.0), you'll often need to use this function to convert variables into a specific data type.
Note It's important to note that when you use the Handles clause as you did here to add event handlers, you cannot control the order in which those events get handled. Visual Basic .NET does supply a different mechanism, the AddHandler statement, which gives you more control. Using the Handles clause is simpler, however, and if you don't care about the order in which the procedures handle the event (in this case, you don't), it's an easier way to add extra functionality.

Summary

In this document you have learned to build an MDI application using several techniques that you find in professional Windows applications. You also learned to create and manipulate menus. Whether or not you choose to use the MDI paradigm will depend on the complexity of your application and how many forms will need to be displayed at one time.

Why use Stored Procedures?

2009-07-07T03:45:00.000-07:00

Introduction

A lot of people are now using stored procedures for their database operations, but what is so good about them? This article accompanies 'Developing Stored Procedures' and looks at the advantages and disadvantages of stored procedures. Note that most of the features listed here depend on the database server you are using.

Performance

If you have a really complex query then stored procedures will speed up the query after the first run (much like ASP.NET pages). This is because, much like ASP.NET, it generates an execution plan for the procedure. This is like taking your VB.NET code and moving it to MSIL code, it is faster and quicker to run. The DB Server will automatically update this plan when changes to the procedure are made.

Encapsulation

This ties in with Performance because you can take multiple statements (eg. an INPUT, UPDATE and SELECT) into one procedure, this significantly reduces the code needed to type in when using those statements (eg. on an ASP.NET page) and the 'compiling' done to the procedure make it faster to execute.
Parameterized Queries
Using input/output queries, you can customize the way that your queries operate, like functions and methods in regular programming languages that allow you to input parameters to change the output, stored procedures allow you to do the same.

Maintainability

When you write a stored procedure, applications can call it whenever they want and you can update the procedure whenever you see fit and the new version will be used by everyone who used it before and they don't even have to know it. This is especially good if a new requirement comes up and you already have 5000 people using it, you can just update the SP and everyone seamlessly uses the new one (unless you screw it up and it causes them all to fail).

Security

With a regular SQL statement, security would have to be set by what a user can and cannot do to the database or table. This can be very restricting and tedious if you want to update the security for a user or add a new user to the list. Depending on your database server (I know that Oracle and SQL Server can do this) you can set up permissions for individual stored procedures. You could lock down the database and have stored procedures the only way to access the database if you wanted to be than conscious.

Easy to learn and copy

Stored procedures are just SQL and are very easy to learn and to copy. You can find resources on creating them nearly anywhere on the web and tutorials on SQL as well. You can also copy them easily from database to database (or database server to database server). If you have the MSDN Library CD's then you will find everything you need to know about programming the stored procedures using T-SQL.

Easy Debugging

This really only applies to users of VS.NET, but you can interpret error messages if you want.
VS.NET uses it's debugging tools to debug T-SQL stored procedures so that you will always get it right. There may be some other debugging tools out there (there must be) and those should also provide the features to debug your stored procedures.

Disadvantages

As you can see, there are so many advantages from using stored procedures that you're probably wondering how terrible the bad stuff it. The truth is - it isn't. As far as I can see, they are nearly perfect for your database, but sometimes they require some work to create and debug them (especially if you are new to SQL programming), the only other thing that I can see if that there is not universal language for stored procedures. i.e. There is T-SQL for SQL Server and PL/SQL for Oracle, these two languages are quite different.

Summary
You can see that the advantages far outweigh the disadvantages (if you really call those disadvantages). So, why not use them? Check out the RELATED ARTICLES section for more information on creating and using stored procedures.

USING JAVASCRIPT FOR DISABLING BUTTON

2009-07-07T03:38:00.000-07:00

JAVASCRIPTS FOR DISABLE BUTTON

Button.JS CODE
---------------

function addLoadEvent(func) {
if(typeof window.onload != 'function')
window.onload = func;
else {
var oldLoad = window.onload;

window.onload = function() {
if(oldLoad) oldLoad();
func();
}
}
}

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Button Disabler *
* Version 1.0 *
* Written by Josh Stodola *
* January 29, 2008 *
* *
* * * * * * * * * *CONFIGURATION OPTIONS* * * * * * * * * * * *
* *
* IsTesting (Boolean, defaults to false) *
* When this is set to true, the form will never submit. *
* Use this to confirm that the script is working. *
* *
* DisabledButtonValue (String, defaults to 'Please Wait') *
* This is the value to show on the button once disabled. *
* *
* HideNonSubmitButtons (Boolean, defaults to true) *
* When true, the script will also hide any reset buttons *
* or Javascript buttons it encounters. *
* *
* ShowHourglassCursor (Boolean, defaults to true) *
* When true, the script will change the cursor to the *
* OS-defined "waiting" symbol to indicate loading. *
* *
* StopLoopAtFirstTextbox (Boolean, defaults to false) *
* When true, the script will stop looping through input *
* elements once it encounters the first textbox. The loop *
* begins at the bottom of the form. This can be set to *
* true to make the script more efficient. *
* *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

var ButtonDisabler = {
IsTesting: false,
DisabledButtonValue: 'Please Wait',
HideNonSubmitButtons: true,
ShowHourglassCursor: true,
StopLoopAtFirstTextbox: true,
IsCapable: (document.getElementById && document.createElement),
AddSubmitEvent: function(frm, func) {
if(typeof frm.onsubmit != 'function')
frm.onsubmit = func;
else {
var oldSub = frm.onsubmit;

frm.onsubmit = function() {
if(oldSub) {
if(oldSub())
return func();
else
return false;
}
else
return func();
}
}
},
LoadEventHandlers: function() {
if(ButtonDisabler.IsCapable) {
for(var i = 0; i < document.forms.length; i++) {
var frm = document.forms[i];

ButtonDisabler.AddSubmitEvent(frm, function() {
ButtonDisabler.DisableForm(frm);
return !ButtonDisabler.IsTesting;
});
}
}
},
DisableForm: function(frm) {
if(!frm) return;
var inputs = frm.getElementsByTagName('INPUT');

for(var j = inputs.length - 1; j >= 0; j--) {
var elem = inputs[j];

if(elem.type == 'submit' || elem.type == 'image') {
var btn = document.createElement('button');

btn.disabled = 'disabled';
btn.innerHTML = ButtonDisabler.DisabledButtonValue;

elem.parentNode.insertBefore(btn, elem);
elem.style.display = 'none';
}

if(elem.type == 'reset' || elem.type == 'button') {
if(ButtonDisabler.HideNonSubmitButtons)
elem.style.display = 'none';
}

if(elem.type == 'text' && ButtonDisabler.StopLoopAtFirstTextbox)
break;
}

if(ButtonDisabler.ShowHourglassCursor)
document.body.style.cursor = 'wait';

frm.onsubmit = function() {
return false;
}
}
};

addLoadEvent(ButtonDisabler.LoadEventHandlers);

SCRIPT.js
-------------

function validateForm(frm) {
if(!document.getElementById('radGender_0').checked
&& !document.getElementById('radGender_1').checked) {
alert('I know it\'s rude, but I must know your gender');
return false;
}

if(!document.getElementById('chkFood_0').checked
&& !document.getElementById('chkFood_1').checked
&& !document.getElementById('chkFood_2').checked
&& !document.getElementById('chkFood_3').checked) {
alert('What is your favorite food?');
return false;
}

return true;
}

ASPX PAGE
---------

<%@ Page Language="VB" %>
<script runat="server">
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Threading.Thread.Sleep(5000)
End Sub
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Disable Web Form Submit Button - ASP.NET Example</title>
<script type="text/javascript" src="script/button.js"></script>
<script type="text/javascript" src="script/script.js"></script>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<h1>Disable Web Form Submit Button</h1>
<h2>Solution by Josh Stodola</h2>
<h3>ASP.NET Example</h3>
<form id="form1" runat="server" onsubmit="return validateForm(this);">
<div>
<label for="txtFirst">First Name</label>
</div>
<div>
<asp:TextBox ID="txtFirst" runat="server" MaxLength="50"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvFirst" runat="server" ErrorMessage="Enter your name" ControlToValidate="txtFirst" SetFocusOnError="true" EnableClientScript="true"></asp:RequiredFieldValidator>
</div>
<div>
<label for="txtLast">Last Name</label>
</div>
<div>
<asp:TextBox ID="txtLast" runat="server" MaxLength="50"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvLast" runat="server" ErrorMessage="Enter your name" ControlToValidate="txtLast" SetFocusOnError="true" EnableClientScript="true"></asp:RequiredFieldValidator>
</div>
<div>
<label for="txtAge">Age</label>
</div>
<div>
<asp:TextBox ID="txtAge" runat="server" MaxLength="2" Width="40"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvAge" runat="server" ErrorMessage="Enter your age" ControlToValidate="txtAge" SetFocusOnError="true" EnableClientScript="true"></asp:RequiredFieldValidator>
</div>
<div>
<label for="radGenter">Gender</label>
</div>
<div>
<asp:RadioButtonList ID="radGender" runat="server">
<asp:ListItem Text="Male" Value="0" />
<asp:ListItem Text="Female" Value="1" />
</asp:RadioButtonList>
</div>
<div>
<label for="chkFood">Favorite Foods</label>
</div>
<div>
<asp:CheckBoxList ID="chkFood" runat="server">
<asp:ListItem Text="American" Value="A" />
<asp:ListItem Text="Chinese" Value="C" />
<asp:ListItem Text="Italian" Value="I" />
<asp:ListItem Text="Mexican" Value="M" />
</asp:CheckBoxList>
</div>
<div>
<label for="txtComments">Comments</label>
</div>
<div>
<asp:TextBox ID="txtComments" TextMode="multiLine" Rows="7" Columns="30" runat="server"></asp:TextBox>
</div>
<div>
<asp:Button ID="btnSubmit" runat="server" Text="Submit Form" />
<input id="btnReset" type="reset" value="Clear Form" />
</div>
</form>
</body>
</html>

ASP.NET DropDownList OptionGroup support

2009-07-07T03:31:00.000-07:00

In APP_Browser FOlder
---------------------

<browsers>
<browser refID="IE6to9">
<controlAdapters>
<adapter controlType="System.Web.UI.WebControls.DropDownList"
adapterType="CraigBlog.Net.Code.Adapters.DropDownListAdapter" />
</controlAdapters>
</browser>



</browsers>

IN APP_CODE Folder add a folder named Adapters and add this code
----------------------------------------------------------------

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Collections.Generic;

namespace CraigBlog.Net.Code.Adapters {
public class DropDownListAdapter : System.Web.UI.WebControls.Adapters.WebControlAdapter {
protected override void RenderContents(HtmlTextWriter writer) {
DropDownList list = this.Control as DropDownList;

string currentOptionGroup;
List renderedOptionGroups = new List();

foreach(ListItem item in list.Items) {
if(item.Attributes["OptionGroup"] == null) {
RenderListItem(item, writer);
} else {
currentOptionGroup = item.Attributes["OptionGroup"];

if(renderedOptionGroups.Contains(currentOptionGroup)) {
RenderListItem(item, writer);
} else {
if(renderedOptionGroups.Count > 0) {
RenderOptionGroupEndTag(writer);
}

RenderOptionGroupBeginTag(currentOptionGroup, writer);
renderedOptionGroups.Add(currentOptionGroup);

RenderListItem(item, writer);
}
}
}

if(renderedOptionGroups.Count > 0) {
RenderOptionGroupEndTag(writer);
}
}

private void RenderOptionGroupBeginTag(string name, HtmlTextWriter writer) {
writer.WriteBeginTag("optgroup");
writer.WriteAttribute("label", name);
writer.Write(HtmlTextWriter.TagRightChar);
writer.WriteLine();
}

private void RenderOptionGroupEndTag(HtmlTextWriter writer) {
writer.WriteEndTag("optgroup");
writer.WriteLine();
}

private void RenderListItem(ListItem item, HtmlTextWriter writer) {
writer.WriteBeginTag("option");
writer.WriteAttribute("value", item.Value, true);

if(item.Selected) {
writer.WriteAttribute("selected", "selected", false);
}

foreach(string key in item.Attributes.Keys) {
writer.WriteAttribute(key, item.Attributes[key]);
}

writer.Write(HtmlTextWriter.TagRightChar);
HttpUtility.HtmlEncode(item.Text, writer);
writer.WriteEndTag("option");
writer.WriteLine();
}
}
}

ASPX PAGE
---------

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DropDownListOptionGroup.aspx.cs" Inherits="CraigBlog.Net.DropDownListOptionGroup" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>DropDownList with OptionGroup support</title>
</head>
<body>
<form id="form1" runat="server">
<div style="padding: 50px 0px 0px 50px">
<asp:DropDownList ID="ddlItems" runat="server"></asp:DropDownList>
</div>
</form>
</body>
</html>

CODE BEHIND PAGE
-----------------
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace CraigBlog.Net {
public partial class TestDropDownList : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e)
{
ListItem item1 = new ListItem("Camel", "1");
item1.Attributes["OptionGroup"] = "Mammals";

ListItem item2 = new ListItem("Lion", "2");
item2.Attributes["OptionGroup"] = "Mammals";

ListItem item3 = new ListItem("Whale", "3");
item3.Attributes["OptionGroup"] = "Mammals";

ListItem item4 = new ListItem("Walrus", "4");
item4.Attributes["OptionGroup"] = "Mammals";

ListItem item5 = new ListItem("Velociraptor", "5");
item5.Attributes["OptionGroup"] = "Dinosaurs";

ListItem item6 = new ListItem("Allosaurus", "6");
item6.Attributes["OptionGroup"] = "Dinosaurs";

ListItem item7 = new ListItem("Triceratops", "7");
item7.Attributes["OptionGroup"] = "Dinosaurs";

ListItem item8 = new ListItem("Stegosaurus", "8");
item8.Attributes["OptionGroup"] = "Dinosaurs";

ListItem item9 = new ListItem("Tyrannosaurus", "9");
item9.Attributes["OptionGroup"] = "Dinosaurs";

ddlItems.Items.Add(item1);
ddlItems.Items.Add(item2);
ddlItems.Items.Add(item3);
ddlItems.Items.Add(item4);
ddlItems.Items.Add(item5);
ddlItems.Items.Add(item6);
ddlItems.Items.Add(item7);
ddlItems.Items.Add(item8);
ddlItems.Items.Add(item9);
}
}
}

HGRIDVIEW

2009-07-07T03:28:00.001-07:00

#region Copyright
// Please do not remove this section. If you can improve it further, you may send a copy to the
// author at azharkhan@hotmail.com
//
// Author : Azhar A. Khan
// Date : 08/27/2008
// Filename : HWGridView.cs
// History :
// 08/27/2008 Initial Version
// 08/28/2008 Romoved ApplyFilterCommand call from OnFilterCommand, since it will be called in OnPreRender
#endregion

using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace HWControls
{
public delegate void FilterCommandEventHandler(object sender, FilterCommandEventArgs e);

public class FilterCommandEventArgs : EventArgs
{
private string filterExpression;

public FilterCommandEventArgs()
{
}

public FilterCommandEventArgs(string filterExpression)
{
this.filterExpression = filterExpression;
}

public string FilterExpression
{
get
{
return this.filterExpression;
}
set
{
this.filterExpression = value;
}
}
}

[ToolboxData("<{0}:HWGridView runat=server>")]
public class HWGridView : GridView
{

TextBox m_txtPageNo = null;
Hashtable m_txtFilter = new Hashtable();
Hashtable m_ddlFilter = new Hashtable();

private static readonly object EventFilterCommand = new object();
// Events
[Category("Extend GridView Events"), Description("ExtededGridView_OnFilterCommand")]
public event EventHandler FilterCommand
{
add
{
base.Events.AddHandler(EventFilterCommand, value);
}
remove
{
base.Events.RemoveHandler(EventFilterCommand, value);
}
}

public HWGridView()
{
PagerSettings.Position = PagerPosition.TopAndBottom;
}

#region Pager
protected virtual void InitializeTopPager(GridViewRow row,
int columnSpan,
PagedDataSource pagedDataSource)
{
TableCell cell = new TableCell();
if (columnSpan > 1)
{
cell.ColumnSpan = columnSpan;
}
Literal ltrlSpan = new Literal();
ltrlSpan.Text = " " +
pagedDataSource.DataSourceCount.ToString() +
" record(s) found.";
cell.Controls.Add(ltrlSpan);
row.Cells.Add(cell);
}

protected virtual void InitializeBottomPager(GridViewRow row,
int columnSpan,
PagedDataSource pagedDataSource)
{
TableCell goToCell = new TableCell();
goToCell.Style.Add(HtmlTextWriterStyle.Width, "100%");

Table pagerTable = (Table)row.Cells[0].Controls[0];
pagerTable.Rows[0].Cells.Add(goToCell);

Literal ltrlSpanBegin = new Literal();
ltrlSpanBegin.Text = "Page ";

if (m_txtPageNo == null)
{
m_txtPageNo = new TextBox();
m_txtPageNo.Width = new Unit(20);
m_txtPageNo.Style.Add("height", "10px");
m_txtPageNo.Font.Size = new FontUnit("10px");
m_txtPageNo.CssClass = this.PagerStyle.CssClass;
}

Literal ltrlText = new Literal();
ltrlText.Text = " of " + PageCount.ToString();

Button btnGo = new Button();
btnGo.Text = "Go";
btnGo.CommandName = "Page1";
btnGo.CommandArgument = "2";
btnGo.ID = "ctl_PageIndex";
btnGo.Height = new Unit("16px");
btnGo.Font.Size = new FontUnit("10px");
btnGo.CssClass = this.PagerStyle.CssClass;
if (this.PagerStyle.ForeColor!=null) {
btnGo.Style.Add(HtmlTextWriterStyle.Color,
this.PagerStyle.ForeColor.ToString());
}

Literal ltrlSpanEnd = new Literal();
ltrlSpanEnd.Text = "";

goToCell.Controls.Add(ltrlSpanBegin);
goToCell.Controls.Add(m_txtPageNo);
goToCell.Controls.Add(ltrlText);
goToCell.Controls.Add(btnGo);
goToCell.Controls.Add(ltrlSpanEnd);
}

protected override void InitializePager(GridViewRow row,
int columnSpan,
PagedDataSource pagedDataSource)
{
//if (this.TopPagerRow == null &&
if (this.Controls[0].Controls.Count == 0 &&
(this.PagerSettings.Position == PagerPosition.Top ||
this.PagerSettings.Position == PagerPosition.TopAndBottom))
{
InitializeTopPager(row, columnSpan, pagedDataSource);
}
else
{
base.InitializePager(row, columnSpan, pagedDataSource);
InitializeBottomPager(row, columnSpan, pagedDataSource);
}
}

protected virtual void HandlePageCommand(GridViewCommandEventArgs e)
{
TextBox txtPageIndex;
txtPageIndex = (TextBox)((System.Web.UI.Control)e.CommandSource).Parent.Controls[1];
Button btnPageIndex = (Button)((System.Web.UI.Control)e.CommandSource).Parent.Controls[3];
if (txtPageIndex.Text.Length > 0)
{
try
{
int ndx = int.Parse(txtPageIndex.Text);
ndx = ndx - 1;
if (ndx >= PageCount)
ndx = PageCount - 1;
if (ndx < 0)
ndx = 0;
this.PageIndex = ndx;
btnPageIndex.CommandArgument = txtPageIndex.Text;
}
catch (Exception e1)
{
if (e1.Message.Length == 0)
return;
}
}
}

protected override void OnRowCommand(GridViewCommandEventArgs e)
{
switch (e.CommandName)
{
case "Page1":
HandlePageCommand(e);
break;
default:
base.OnRowCommand(e);
break;
}
}
#endregion

#region Filter
protected virtual void AddFilter(int columnIndex,
TableCell headerCell,
DataControlField field)
{
if (headerCell.Controls.Count == 0)
{
LiteralControl ltlHeaderText = new LiteralControl(headerCell.Text);
headerCell.Controls.Add(ltlHeaderText);
}

LiteralControl ltlBreak = new LiteralControl("
");
headerCell.Controls.Add(ltlBreak);
TextBox txtFilter = null;
if (m_txtFilter.Contains(columnIndex))
{
txtFilter = (TextBox)m_txtFilter[columnIndex];
}
else
{
txtFilter = new TextBox();
txtFilter.ID = ID + "_txtFilter" + columnIndex.ToString();// +headerCell.ClientID;
if (field.ItemStyle.Width.Value != 0.0)
{
txtFilter.Style.Add(HtmlTextWriterStyle.Width, Convert.ToString(field.ItemStyle.Width.Value - 40) + "px");
}
else if (field.HeaderStyle.Width.Value != 0.0)
{
txtFilter.Style.Add(HtmlTextWriterStyle.Width, Convert.ToString(field.HeaderStyle.Width.Value - 40) + "px");
}
txtFilter.Style.Add(HtmlTextWriterStyle.Height, "10px");
txtFilter.Style.Add(HtmlTextWriterStyle.FontSize, "9px");
m_txtFilter[columnIndex] = txtFilter;
}

DropDownList ddlFilter;
if (m_ddlFilter.Contains(columnIndex))
{
ddlFilter = (DropDownList)m_ddlFilter[columnIndex];
}
else
{
ddlFilter = new DropDownList();
ddlFilter.ID = ID + "_ddlFilter" + columnIndex.ToString();
ddlFilter.Items.Add(" ");
ddlFilter.Items.Add("=");
ddlFilter.Items.Add("<");
ddlFilter.Items.Add(">");
ddlFilter.Items.Add("<=");
ddlFilter.Items.Add(">=");
ddlFilter.AutoPostBack = true;
ddlFilter.SelectedIndexChanged += new EventHandler(this.HandleFilterCommand);
//ddlFilter.SelectedIndex = 0;
ddlFilter.Style.Add(HtmlTextWriterStyle.Width, "30px");
ddlFilter.Height = new Unit("7px");
ddlFilter.Style.Add(HtmlTextWriterStyle.Height, "8px");
ddlFilter.Style.Add(HtmlTextWriterStyle.FontSize, "8px");
m_ddlFilter[columnIndex] = ddlFilter;
}

headerCell.Controls.Add(txtFilter);
headerCell.Controls.Add(ddlFilter);
}

protected virtual void AddFilters(GridViewRow headerRow,
DataControlField[] fields)
{
for (int i = 0; i < Columns.Count; i++)
{
if (Columns[i].HeaderText.EndsWith(" "))
{
AddFilter(i, headerRow.Cells[i], fields[i]);
}
}
}

protected virtual void AddGlyph(GridView grid, GridViewRow item)
{
Label glyph = new Label();
glyph.EnableTheming = false;
glyph.Font.Name = "webdings";
glyph.Text = (grid.SortDirection == SortDirection.Ascending ? " 5" : " 6");
//glyph.Font.Size = FontUnit.Medium;
//glyph.Text = " " + (grid.SortDirection == SortDirection.Ascending ? "▲" : "▼");
// Find the column you sorted by
for (int i = 0; i < grid.Columns.Count; i++)
{
string colExpr = grid.Columns[i].SortExpression;
if (colExpr != "" && colExpr == grid.SortExpression)
{
item.Cells[i].Controls.Add(glyph);
}
}
}

protected virtual void InitializeHeaderRow(GridViewRow row, DataControlField[] fields)
{
AddGlyph(this, row);
AddFilters(row, fields);
}

protected override void InitializeRow(GridViewRow row, DataControlField[] fields)
{
base.InitializeRow(row, fields);
if (row.RowType == DataControlRowType.Header)
{
InitializeHeaderRow(row, fields);
}
}

protected virtual String GetFilterCommand()
{
String filterCommand = "";
for (int i = 0; i < this.Columns.Count; i++)
{
if (this.Columns[i].HeaderText.EndsWith(" "))
{
DataControlFieldHeaderCell headerCell = (DataControlFieldHeaderCell)this.HeaderRow.Cells[i];
TextBox txtFilter = (TextBox)m_txtFilter[i];
DropDownList ddlFilter = (DropDownList)m_ddlFilter[i];
BoundField aColumn;
if (!(this.Columns[i] is BoundField) || String.IsNullOrEmpty(ddlFilter.SelectedValue.Trim()))
{
continue;
}
aColumn = (BoundField)this.Columns[i];
if (String.IsNullOrEmpty(txtFilter.Text))
{
continue;
}
if (String.IsNullOrEmpty(filterCommand))
{
filterCommand = aColumn.DataField + " " + ddlFilter.SelectedValue + " " + txtFilter.Text;
}
else
{
filterCommand += " AND " + aColumn.DataField + " " + ddlFilter.SelectedValue + " " + txtFilter.Text;
}
}
}

return filterCommand;
}

protected virtual void ApplyFilterCommand(String filterCommand)
{
DataSourceView dsv = this.GetData();
if (dsv is SqlDataSourceView)
{
String selectCommand = ((SqlDataSourceView)dsv).SelectCommand;
if (selectCommand.Contains(filterCommand)) {
return;
}
if (selectCommand.Contains("WHERE"))
{
selectCommand += " AND " + filterCommand;
}
else
{
selectCommand += " WHERE " + filterCommand;
}
((SqlDataSourceView)dsv).SelectCommand = selectCommand;
}
}

protected virtual void OnFilterCommand(FilterCommandEventArgs e)
{
EventHandler handler = (EventHandler)base.Events[EventFilterCommand];
if (handler != null)
{
handler(this, e);
}
}

private void HandleFilterCommand(object sender, EventArgs e)
{
this.RequiresDataBinding = true; //this is required to make sure that unsetting of filter is also handled
FilterCommandEventArgs filterArgs = new FilterCommandEventArgs();
filterArgs.FilterExpression = GetFilterCommand();
this.OnFilterCommand(filterArgs);
}

protected override void OnPreRender(EventArgs e)
{
String filterCommand = GetFilterCommand();

if (String.IsNullOrEmpty(filterCommand) == false)
{
ApplyFilterCommand(filterCommand);
}
base.OnPreRender(e);
}
#endregion
}
}

RANDOM PASSWORD GENERATION

2009-07-07T03:15:00.000-07:00

RANDOM PASSWORD GENERATION ASPX PAGE

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RandomPassword.aspx.cs" Inherits="RandomPassword" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>Enter Password length: <asp:TextBox ID="txtPassLength" runat="server"></asp:TextBox><asp:Button ID="btnGenerate" runat="server" Text="Generate" OnClick="btnGenerate_Click" /></div>
<div>Random Password: <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox></div>
</div>
<br /><br />
<div>
<b>Coding Support Provided By <a href="http://www.top54u.com">www.top54u.com</a><br />
<a href="http://programming.top54u.com">Programming Ezine</a></b><br />
<br />
</div>
</form>
</body>
</ht

CODE BEHIND PAGE
-------------------

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class RandomPassword : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnGenerate_Click(object sender, EventArgs e)
{
string allowedChars = "";
allowedChars = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,";
allowedChars += "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,";
allowedChars += "1,2,3,4,5,6,7,8,9,0,!,@,#,$,%,&,?";

char[] sep ={ ',' };
string[] arr = allowedChars.Split(sep);

string passwordString = "";

string temp = "";

Random rand = new Random();
for (int i = 0; i < Convert.ToInt32(txtPassLength.Text); i++)
{
temp = arr[rand.Next(0, arr.Length)];
passwordString += temp;
}

txtPassword.Text = passwordString;
}
}

Fixed Records SP

2009-07-07T03:12:00.000-07:00

CREATE PROCEDURE [dbo].[Test_Customers_Paginacion]
@PageSize int = 10,
@PageIndex int = 1

AS

DECLARE @RowNumBegin int
DECLARE @RowNumEnd int

-- Index based on "1", so to get the first page
-- we set the param to @PageIndex = 1
SET @RowNumBegin = (@PageIndex -1) * @PageSize
SET @RowNumEnd = @RowNumBegin + @PageSize

-- Make the Quero and add a referente ID to each row by using and auto incremental field
SELECT ContactName, ContactTitle, Address, City, RowNum = Identity (int, 1, 1)
INTO #t
FROM Customers

-- Select the subset of rows that we want to retrieve
SELECT *
FROM #t
WHERE RowNum BETWEEN @RowNumBegin AND @RowNumEnd

-- Just to be sure...
DROP TABLE #t

GO

GridView Header CheckBox Check

2009-07-07T03:10:00.000-07:00

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Demo</title>
</head>
<body onload="AttachListener()">
<form id="form1" runat="server">
<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" Font-Names="Verdana" ForeColor="#333333" GridLines="None">

<Columns>

<asp:TemplateField>

<HeaderTemplate>
<input type="checkbox" id="chkAll" name="chkAll" onclick="Check(this)" />
</HeaderTemplate>

<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Category Name">
<ItemTemplate>
<asp:Label ID="lblCategoryName" runat="server" Text = '<%# Eval("CategoryName") %>' />
</ItemTemplate>
</asp:TemplateField>

</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />

</asp:GridView>

</div>
</form>
</body>
</html>

<script language="javascript" type="text/javascript">

var counter = 0;

var pattern = '^GridView1';

// Get the checkboxes inside the Gridview which is part of the template column
function GetChildCheckBoxCount()
{
var checkBoxCount = 0;

var elements = document.getElementsByTagName("INPUT");

for(i=0; i<elements.length;i++)
{
if(IsCheckBox(elements[i]) && IsMatch(elements[i].id)) checkBoxCount++;
}

return parseInt(checkBoxCount);
}

// A function that checks if the checkboxes are the one inside the GridView
function IsMatch(id)
{
var regularExpresssion = new RegExp(pattern);

if(id.match(regularExpresssion)) return true;
else return false;
}

function IsCheckBox(chk)
{
if(chk.type == 'checkbox') return true;
else return false;
}

function AttachListener()
{
var elements = document.getElementsByTagName("INPUT");

for(i=0; i< elements.length; i++)
{
if( IsCheckBox(elements[i]) && IsMatch(elements[i].id))
{
AddEvent(elements[i],'click',CheckChild);
}
}
}

function CheckChild(e)
{
var evt = e || window.event;

var obj = evt.target || evt.srcElement

if(obj.checked)
{
if(counter < GetChildCheckBoxCount())
{ counter++; }
}

else
{
if(counter > 0) { counter--; }
}

if(counter == GetChildCheckBoxCount())
{ document.getElementById("chkAll").checked = true; }
else if(counter < GetChildCheckBoxCount()) { document.getElementById("chkAll").checked = false; }

}

function AddEvent(obj, evType, fn)
{
if (obj.addEventListener)
{
obj.addEventListener(evType, fn, true);
return true;
}

else if (obj.attachEvent)
{
var r = obj.attachEvent("on"+evType, fn);
return r;
}
else
{
return false;
}
}

function Check(parentChk)
{
var elements = document.getElementsByTagName("INPUT");

for(i=0; i<elements.length;i++)
{
if(parentChk.checked == true)
{
if( IsCheckBox(elements[i]) && IsMatch(elements[i].id))
{
elements[i].checked = true;
}
}
else
{
elements[i].checked = false;
// reset the counter
counter = 0;
}
}

if(parentChk.checked == true)
{
counter = GetChildCheckBoxCount();
}

}

</script>

if (!Page.IsPostBack)
{
BindData();
}

private void BindData()
{
string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", myConnection);

DataSet ds = new DataSet();
ad.Fill(ds);

GridView1.DataSource = ds;
GridView1.DataBind();
}

Custom Pager

2009-07-07T03:08:00.000-07:00

Custom Pager ASPX PAGE
----------------------

<html>
<body>
<form id="Form1" runat="server">
<p>
<asp:DataGrid id="dgSearchResults" runat="server" Width="462px" Height="143px" OnPreRender="DataGrid1_PreRender" OnItemCreated="DataGrid1_ItemCreated" AllowPaging="True" BorderStyle="None" GridLines="Vertical" BorderWidth="1px" BorderColor="#999999" BackColor="White" CellPadding="3" OnPageIndexChanged="dgSearchResults_PageIndexChanged">
<FooterStyle forecolor="Black" backcolor="#CCCCCC"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#000084"></HeaderStyle>
<PagerStyle horizontalalign="Right" forecolor="Black" position="TopAndBottom" backcolor="#999999" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#008A8C"></SelectedItemStyle>
<AlternatingItemStyle backcolor="Gainsboro"></AlternatingItemStyle>
<ItemStyle forecolor="Black" backcolor="#EEEEEE"></ItemStyle>
</asp:DataGrid>
</p>
</form>
</body>
</html>

<%@ Page Language="C#" Debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

Gridview Highlight Search

2009-07-07T03:02:00.000-07:00

ASPX Page
---------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">

.highlight
{
background:Yellow;
}

</style>
</head>
<body>
<form id="form1" runat="server">
<div>
Enter Search Keyword:
<asp:TextBox ID="txtSearchBox" runat="server" Text="Ca" />
<asp:Button ID="Btn_Search" OnClick="Search" runat="server" Text="Search" />
<br />
<br />
<br />
<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="false" AllowPaging="True"
Font-Size="Large" OnPageIndexChanging="gvProducts_PageIndexChanging" PageSize="10">
<Columns>
<asp:TemplateField HeaderText="Product Name">
<ItemTemplate>
<asp:Label ID="lblProductName" runat="server" Text='<%# SearchKeyWord( searchWord,(string) Eval("emp_name") ) %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />

<asp:Panel ID="Panel1" runat="server" Height="50px" Width="263px">
</asp:Panel>
<br />
<br />
<br />
</div>
</form>
</body>
</html>

Code Behind Page
----------------

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

public partial class _Default : System.Web.UI.Page
{
protected string searchWord = String.Empty;
private const int PAGE_SIZE = 10;

private Hashtable hMatches = new Hashtable();

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}

CreateLinks();

}

private void CreateLinks()
{
Hashtable myHashTable = ViewState["MyHashTable"] as Hashtable;

if(myHashTable != null && myHashTable.Count > 0)
{

foreach (object key in new ArrayList(myHashTable.Keys))
{
LinkButton link = new LinkButton();
link.Command += new CommandEventHandler(link_Command);
link.CommandArgument = key.ToString();
link.Text = String.Format("There are {0} results on page {1}",myHashTable[key].ToString() , (Convert.ToInt32(key) + 1).ToString()) + "
";

Panel1.Controls.Add(link);
}

Panel1.DataBind();
}
}

private void BindData()
{
string connectionString = "Server=SERVERNAME;Database=TestDB;user=rsmsuser;pwd=rsmsuser";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT emp_id, emp_name FROM ali_employee", myConnection);

DataSet ds = new DataSet();
ad.Fill(ds);

gvProducts.DataSource = ds;
gvProducts.DataBind();
}

protected void Search(object sender, EventArgs e)
{
// clear the old results
Panel1.Controls.Clear();
Panel1.DataBind();

searchWord = txtSearchBox.Text;
SearchContainer(searchWord);

// Hmmm
BindData();

}

protected string SearchKeyWord(string searchString, string text)
{
Regex reg = new Regex(searchString.Replace(" ", "|"), RegexOptions.IgnoreCase);
return reg.Replace(text,new MatchEvaluator(ReplaceKeyWords));

}

private void SearchContainer(string searchString)
{
int index = 0;

// get the container
DataSet ds = GetDataSet();

double pageIndex = 0;

Regex reg = new Regex(searchString.Replace(" ","|"),RegexOptions.IgnoreCase);

foreach(DataRow row in ds.Tables[0].Rows)
{
if (reg.IsMatch(row["emp_name"] as String))
{
pageIndex = Math.Ceiling( Convert.ToDouble( index / PAGE_SIZE ));

if (hMatches.ContainsKey(pageIndex))
{
hMatches[pageIndex] = ((int)hMatches[pageIndex]) + 1;
}
else
{
hMatches.Add(pageIndex, 1);
}
}

index++;
}

ViewState["MyHashTable"] = hMatches;

CreateLinks();

}

void link_Command(object sender, CommandEventArgs e)
{
gvProducts.PageIndex = Convert.ToInt32( e.CommandArgument);
searchWord = txtSearchBox.Text;
BindData();
}

private DataSet GetDataSet()
{
string connectionString = "Server=SERVERNAME;Database=TestDB;user=rsmsuser;pwd=rsmsuser";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT emp_id, emp_name FROM ali_employee", myConnection);

DataSet ds = new DataSet();

ad.Fill(ds);

return ds;
}

private string ReplaceKeyWords(Match m)
{
return "" + m.Value + "";
}

protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvProducts.PageIndex = e.NewPageIndex;
searchWord = txtSearchBox.Text;
BindData();
}
}

Document Saving and Retrieving fom Database

2009-07-06T23:47:00.000-07:00

Document Saving in Database
----------------------------

Table Structure
---------------

CREATE TABLE [dbo].[eDocuments](
[DownloadID] [int] IDENTITY(1,1) NOT NULL,
[DocTitle] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocStream] [image] NULL,
[DocExt] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DocSize] [float] NULL,
[DocType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Decription] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReviewDate] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Procedure for Saving Document Data into Database
-------------------------------------------------

CREATE PROC [dbo].[eDocumentsUPD]
(
@DownloadID INT=0,
@DocTitle VARCHAR(200),
@DocStream IMAGE,
@DocExt VARCHAR(5),
@DocSize FLOAT,
@DocType VARCHAR(25),
@Decription VARCHAR(500)=NULL
)
AS
DECLARE @RECORDCOUNT INT
SET @RECORDCOUNT=0
SELECT @RECORDCOUNT=COUNT(*)
FROM eDocuments
WHERE DownloadID=@DownloadID

IF @RECORDCOUNT=0
BEGIN
INSERT INTO eDocuments
(
DocTitle,DocStream,DocExt,DocSize,DocType,Decription ,ReviewDate
)
SELECT @DocTitle,@DocStream,@DocExt,@DocSize,@DocType,@Decription ,GETDATE()
Select @DownloadID=IDENT_CURRENT('eCtblDownloadableDocuments')
END
ELSE
BEGIN
UPDATE eDocuments
SET DocTitle=DocTitle,
DocStream=DocStream,
DocExt=DocExt,
DocSize=DocSize,
DocType=DocType,
Decription= Decription ,
ReviewDate=GETDATE()
WHERE DownloadID=DownloadID

END

select @DownloadID as DownloadID

IF @@error!=0
BEGIN

RAISERROR 20001 'eDocumentsUPD: Failed Insert/Updae Records Into eDocuments'

RETURN(1)

END

RETURN(0)

Stored Procedure for Getting Documents Info Based on DocumentID
---------------------------------------------------------------

CREATE PROC [dbo].[eDocumentsGETByID]
@DownloadID int
AS

SELECT
ISNULL(DownloadID,0) AS DownloadID ,
ISNULL(DocTitle,'') AS DocTitle,
DocStream,
ISNULL(DocExt,'') AS DocExt,
ISNULL(DocSize,0) AS DocSize,
ISNULL(DocType,'') AS DocType,
ISNULL(Decription,'') AS Decription ,
ReviewDate

FROM eDocuments
where DownloadID=@DownloadID

Error Saving SP and Table Structure in Database

2009-07-06T23:25:00.000-07:00

Error Table Structure inorder to maintain Error data in database

CREATE TABLE [dbo].[PRJtblErrors](
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorDate] [datetime] NOT NULL DEFAULT (getdate()),
[UserName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ('DBAdmin'),
[ModuleName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (db_name()),
[ScreenName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FunctionNameOrMethodName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

------------------------------------------
Call this procedure in the catch block
------------------------------------------

Sample
------
BEGIN
TRY

End Try
Begin Catch
Execute PRJspErrorInfo
End Catch

/**
-----------------------------------------------------------------------------------------
-- Stored Proc Name:
-- Author :
-- Date Created :
-- Version :
-- Last Revision :
-- Function :
-- :
-------------------------------------------------------------------------------------------
**/

CREATE PROCEDURE [dbo].[PRJspErrorInfoSave]
AS
BEGIN
INSERT INTO PRJtblErrors
(
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage
)
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END

Stored Procedure for Saving Address Information

2009-07-06T23:22:00.000-07:00

Procedure for Saving Address Information

/**
-----------------------------------------------------------------------------------------
-- Stored Proc Name:
-- Author :
-- Date Created :
-- Version : 1.0
-- Last Revision :
-- Function :
-- :
-------------------------------------------------------------------------------------------
**/
CREATE PROCEDURE [dbo].[eCspAddressInfoSave]
(
@AddressID INT,
@RefID INT,
@RefType VARCHAR(50),
@Street VARCHAR(50)=Null,
@Location VARCHAR(50)=Null,
@City VARCHAR(50)=Null,
@State INT=0,
@Country INT=0,
@Zip VARCHAR(50)=Null,
@Phone1 VARCHAR(50)=Null,
@Phone2 VARCHAR(50)=Null,
@Fax VARCHAR(50)=Null,
@Email VARCHAR(200)=Null,
@CreatedBy VARCHAR(50)=Null,
@ModifiedBy VARCHAR(50)=Null
)
AS
DECLARE @RECORDCOUNT INT
SET @RECORDCOUNT=0
SELECT @RECORDCOUNT=COUNT(*)
FROM AddressInfo
WHERE AddressID=@AddressID

IF @RECORDCOUNT=0
BEGIN

INSERT INTO AddressInfo (
RefID,
RefType,
Street,
Location,
City,
State,
Country,
Zip,
Phone1,
Phone2,
Fax,
Email,
CreatedBy,
CreatedDate,
ModifiedBy,
ModifiedDate
)
VALUES (
@RefID,
@RefType,
@Street,
@Location,
@City,
@State,
@Country,
@Zip,
@Phone1,
@Phone2,
@Fax,
@Email,
@CreatedBy,
GETDATE() ,
@ModifiedBy,
GETDATE()
)
Select @AddressID=@@Identity
END
ELSE
BEGIN
UPDATE AddressInfo
SET
RefID=@RefID,
RefType=@RefType,
Street=@Street,
Location=@Location,
City=@City,
State=@State,
Country=@Country,
Zip=@Zip,
Phone1=@Phone1,
Phone2=@Phone2,
Fax=@Fax,
Email=@Email,
ModifiedBy=@ModifiedBy,
ModifiedDate=GETDATE()
WHERE AddressID=@AddressID

END

select @AddressID as AddressID

IF @@error!=0
BEGIN

RAISERROR 20001 'eCspAddressInfoSave: Failed Insert/Updae Records Into eCtblAddressInfo'

RETURN(1)

END

RETURN(0)

Scalar Valued Functions in SQL Server - Sample To Retrieve Name

2009-07-06T23:17:00.000-07:00

This Function takes ID as the input Parameter and fetches down the name and returns it.

CREATE function [dbo].[fnName](@ID int)
returns varchar(200)
as
begin
declare @Name varchar(200)
set @Name=''

SELECT @Name= LTRIM(RTRIM(REPLACE(ISNULL(E.FirstName,'')+' '+ISNULL(E.MiddleName,'')+' '+ISNULL(E.LastName,''),' ',' ')))
FROM dbo.USERInfo E
WHERE E.USERID=@ID

set @Name=ISNULL(@Name,'')

return @Name
end

Split Function in SqlServer

2009-07-06T23:13:00.000-07:00

/**
-----------------------------------------------------------------------------------------
-- Function Name: Split
-- Function : Split String and Convert into Table
Return Type: Table
-- :
-------------------------------------------------------------------------------------------
**/

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (RowNo INT ,Items varchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @RowNo INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
SELECT @RowNo=0
-- following line added 02/12/04 as null
-- values cause issues
IF @String IS NULL RETURN
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING

SELECT @RowNo=@ROWNO+1
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(RowNo,Items) VALUES(@RowNo,@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Gridiview Child Controls Event Firing Sample

2009-06-29T23:19:00.000-07:00

<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3"
DataKeyNames="CompanyID" DataSourceID="SqlDataSource1" onrowdatabound="GridView1_RowDataBound1"
>
<RowStyle ForeColor="#000066" />
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:CheckBox ID="chkchoose" runat="server" AutoPostBack="true" OnCheckedChanged="chkchoose_CheckedChanged" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Select" Text="Select"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CompanyID" InsertVisible="False"
SortExpression="CompanyID">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("CompanyID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("CompanyID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TargetID" SortExpression="TargetID">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("TargetID") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("TargetID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CompanyName" SortExpression="CompanyName">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("CompanyName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("CompanyName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email" SortExpression="Email">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="URL" SortExpression="URL">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("URL") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("URL") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" SortExpression="Country">
<EditItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("Country") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged" ></asp:DropDownList>

</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MCCMSConnectionString4 %>"
SelectCommand="SELECT [CompanyID], [TargetID], [CompanyName], [Email], [URL], [Country] FROM [CMStbCompanies]"></asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:MCCMSConnectionString %>"
SelectCommand="SELECT [Acronym], [AcronymID] FROM [CMStbAcronyms] WHERE ([AcrCat] = @AcrCat)">
<SelectParameters>
<asp:Parameter DefaultValue="Countries" Name="AcrCat" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

In Code Behind Page inorder to fire the events

protected void chkchoose_CheckedChanged(object sender, EventArgs e)
{
CheckBox chk = (CheckBox)sender;
TableCell cell = chk.Parent as TableCell;
GridViewRow item = cell.Parent as GridViewRow;

int index = item.RowIndex;
string content = item.Cells[0].Text;

if (chk.Checked)
{
Response.Write(String.Format("Checked Row {0} contains {1}", index, content));
}

}

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList list = (DropDownList)sender;

TableCell cell = list.Parent as TableCell;
GridViewRow item = cell.Parent as GridViewRow;

int index = item.RowIndex;
string content = item.Cells[0].Text;

Response.Write(String.Format("Row {0} contains {1}", index, content));

}

Also You bind the DropDown in Code Behind instead at the Item Template Level

protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddlCountry");

if (ddl != null)
{
ddl.DataTextField = "Acronym";
ddl.DataValueField = "AcronymID";
ddl.DataSource = SqlDataSource2;
ddl.DataBind();
}
}
}

DropDownList Controls Events In an ASP.Net DataGrid

2009-06-29T23:12:00.000-07:00

In this article we will demonstrate how to add a data bound, event raising control into a column of an ASP.Net DataGrid. We will also see how to place a control into the header row of a DataGrid. We will demonstrate this using the DropDownList control.

The following screen shot shows a web form in design mode. We are going to query the authors table in SQL Server’s pubs table. You can see we have one column (au_fname) to display the au_fname column from the table. In the second column we have added DropDownList controls to both the header line, and in each row with a data record.

Let’s first take a look at the ASPX markup to create the grid.

<asp:DataGrid
id="DataGrid1" runat="server"
AutoGenerateColumns="False"
OnItemDataBound="DataGrid1_ItemDataBound">
<Columns>
<asp:BoundColumn DataField="au_fname" HeaderText="au_fname" />
<asp:TemplateColumn>
<HeaderTemplate>
<asp:DropDownList
ID="HeaderDropDown" Runat="server"
AutoPostBack="True"
OnSelectedIndexChanged="DropDown_SelectedIndexChanged" />
</HeaderTemplate>
<ItemTemplate>
<asp:DropDownList
ID="ItemDropDown" Runat="server"
AutoPostBack="True"
OnSelectedIndexChanged="DropDown_SelectedIndexChanged" />
</ItemTemplate>
</asp:TemplateColumn>
</asp:DataGrid>

We have a DropDownList in the header declared as HeaderDropDown, and a DropDownList in the Item template declared as ItemDropDown. Notice we set the AutoPostBack property to true. Setting AutoPostBack to true allows the form to post back to the server and raise an event each time the user changes a selection in the DropDownList control. We also assign an event handler for the SelectedIndexChanged event. Notice we are sharing the same event handler (DropDown_SelectedIndexChanged) for all DropDownList controls. We will see later in the code how we can still identity the exact control firing the event.

Before we look at the SelectedIndexChanged event handlers, let’s think about populating the drop down controls with data for the user to select. When the DataGrid binds to a data source it will create a DropDownList control to place in the header row, and also a DropDownList for each row of data rendered. We need to catch when ASP.NET creates these controls so we can populate them with data for the user to select from. The best place to do this is during the ItemDataBound event. You can see in the ASPX above we are handling this event using the DataGrid1_ItemDataBound method, shown below.

protected void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.AlternatingItem ||
e.Item.ItemType == ListItemType.Item)
{
string[] options = { "Option1", "Option2", "Option3" };

DropDownList list = (DropDownList)e.Item.FindControl("ItemDropDown");
list.DataSource = options;
list.DataBind();
}
else if(e.Item.ItemType == ListItemType.Header)
{
string[] options = { "OptionA", "OptionB", "OptionC" };

DropDownList list = (DropDownList)e.Item.FindControl("HeaderDropDown");
list.DataSource = options;
list.DataBind();
}
}

ItemDataBound occurs after a DataGridItem is bound to the grid. The event fires for each row, including the header and the footer rows. We know if the item belongs to the header, the footer, or one of the items by checking the Item.ItemType property of the event. Rows of data will always have a ListItemType of Item or AlternatingItem.

We use the FindControl method to obtain a reference to the DropDownList control for each row. To learn more about using FindControl in these scenarios, see ‘In Search Of ASP.NET Controls’. We specify the control to find by name. For the header remember we specified a name of “HeaderDropDown”, while rows with data will have a name of “ItemDropDown”.

We have an array of strings to represent data for the DropDownList control to bind against. Note that this event will fire for each row of the grid, so you’ll want to make sure this method performs well. You would not want to perform a database query each time the event fires. In this example, the DropDownList control in the header binds against a different set of strings than the DropDownList controls in the item rows.

Whenever the user modifies any of the DropDownList selections in our grid, the DropDown_SelectedIndexChanged event will fire. Remember, we assigned the same event handler for all of our lists, and we set the AutoPostBack property to true so a new selection should post back to the server and raise the event immediately. The event handler is shown below.

protected void DropDown_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList list = (DropDownList)sender;

TableCell cell = list.Parent as TableCell;
DataGridItem item = cell.Parent as DataGridItem;

int index = item.ItemIndex;
string content = item.Cells[0].Text;

Response.Write(
String.Format("Row {0} contains {1}", index, content)
);

}

First, notice the sender parameter will be the DropDownList control modified by the user. By casting the object reference to a DropDownList we get a reference to the modified control and can see what the user has selected.

Secondly, notice the DropDownList control’s parent will be a TableCell of the grid. The parent of the TableCell will be a DataGridItem. Once we have a reference to the DataGridItem, we can see which row was selected by using the ItemIndex property. (Note: ItemIndex will be -1 for the header row). With the DataGridItem we can also inspect the values of other cells in the row.

The following image shows the form in action just after we made a new selection to the list in Cheryl's row.

Once you know the control invoking the event you can find almost any other piece of information you need about the grid or the underlying data source by getting to the DataGridItem object and inspecting the ItemIndex property or the collection of TableCells. With this information in hand, adding controls to the rows of your DataGrid should become a straightforward process. You can download the code for this article. Add this code to an empty web project as an existing item.

Original POST at

How to Maintain Checkbox state in a Gridview for an Header

2009-06-29T02:30:00.000-07:00

How to find Nth Highest Salary of Employee

2009-06-22T23:34:00.001-07:00

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER – Query to Retrieve the Nth Maximum value

I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database Table

Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.

For particular example of employee :

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks;
GO
SELECT TOP 1 Rate
FROM (
SELECT DISTINCT TOP 4 Rate
FROM HumanResources.EmployeePayHistory
ORDER BY Rate DESC) A
ORDER BY Rate
GO

How do you find the Second highest Salary?

2009-06-22T22:08:00.000-07:00

This is the most common question asked in Interviews.

EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?

Answer:

We can write a sub-query to achieve the result

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.

--------------------------------------------------------------------------------

SQL SYNTAX

2009-06-22T03:53:00.000-07:00

SQL Syntax Introduction

CREATE TABLE syntax

ALTER TABLE syntax

DROP TABLE syntax

CREATE VIEW syntax

DROP VIEW syntax

CREATE SEQUENCE syntax

DROP SEQUENCE syntax

COMPACT TABLE syntax

CREATE SCHEMA syntax

DROP SCHEMA syntax

INSERT syntax

DELETE syntax

UPDATE syntax

SELECT syntax

COMMIT and ROLLBACK syntax

CREATE USER, ALTER USER and DROP USER syntax

GRANT/REVOKE syntax

SET syntax

DESCRIBE syntax

SHOW syntax

SHUTDOWN syntax

--------------------------------------------------------------------------------

1. SQL Syntax Introduction
This section is a reference for the SQL grammar that Mckoi SQL Database supports. Mckoi SQL Database supports a subset of entry level ANSI SQL-92. This section is not intended to be a tutorial for learning SQL. For SQL books and online tutorials see the links section on the home page.

2. CREATE TABLE syntax

CREATE TABLE [ IF NOT EXISTS ] table_name
( column_declare1, column_declare2, constraint_declare1, ... )

column_declare ::= column_name type [ DEFAULT expression ]
[ NULL | NOT NULL ] [ INDEX_BLIST | INDEX_NONE ]

type ::= BIT | REAL | CHAR | TEXT | DATE | TIME |
FLOAT | BIGINT | DOUBLE | STRING | BINARY | NUMERIC |
DECIMAL | BOOLEAN | TINYINT | INTEGER | VARCHAR |
SMALLINT | VARBINARY | TIMESTAMP | LONGVARCHAR |
JAVA_OBJECT | LONGVARBINARY

constraint_declare :: = [ CONSTRAINT constraint_name ]
PRIMARY KEY ( col1, col2, ... ) |
FOREIGN KEY ( col1, col2, ... ) REFERENCES f_table [ ( col1, col2, ... ) ]
[ ON UPDATE triggered_action ] [ ON DELETE triggered_action ] |
UNIQUE ( col1, col2, ... ) |
CHECK ( expression )
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ NOT DEFERRABLE | DEFERRABLE ]

triggered_action :: =
NO ACTION | SET NULL | SET DEFAULT | CASCADE

When declaring string or binary column types the maximum size must be specified. The following example declares a string column that can grow to a maximum of 100 characters,

CREATE TABLE Table ( str_col VARCHAR(100) )
When handling strings the database will only allocate as much storage space as the string uses up. If a 10 character string is stored in str_col then only space for 10 characters will be allocated in the database. So if you need a column that can store a string of any size, use an arbitrarily large number when declaring the column. Mckoi SQL Database does not use a fixed size storage mechanism when storing variable length column data.

JAVA_OBJECT is a column type that can contain serializable Java objects. The JAVA_OBJECT type has an optional Java class definition that is used for runtime class constraint checking. The following example demonstrates creating a JAVA_OBJECT column.

CREATE TABLE ObjectTable (
obj_id NUMERIC, obj JAVA_OBJECT(java.awt.Point))
If the Java class is not specified the column defaults to java.lang.Object which effectively means any type of serializable Java object can be kept in the column.

String types may have a COLLATE clause that changes the collation ordering of the string based on a language. For example, the folling statement creates a string that can store and order Japanese text;

CREATE TABLE InternationalTable (
japanese_text VARCHAR(4000) COLLATE 'jaJP')
The 'jaJP' is an ISO localization code for the Japanese language in Japan. Other locale codes can be found in the documentation to java.text.Collate.

Unique, primary/foreign key and check integrity constraints can be defined in the CREATE TABLE statement. The following is an example of defining a table with integrity constraints.

CREATE TABLE Customer (
number VARCHAR(40) NOT NULL,
name VARCHAR(100) NOT NULL,
ssn VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,

CONSTRAINT cust_pk PRIMARY KEY (number),
UNIQUE ( ssn ), // (An anonymous constraint)
CONSTRAINT age_check CHECK (age >= 0 AND age < 200)
)

3. ALTER TABLE syntax

ALTER TABLE table_name ADD [COLUMN] column_declare
ALTER TABLE table_name ADD constraint_declare
ALTER TABLE table_name DROP [COLUMN] column_name
ALTER TABLE table_name DROP CONSTRAINT constraint_name
ALTER TABLE table_name DROP PRIMARY KEY
ALTER TABLE table_name ALTER [COLUMN] column_name SET default_expr
ALTER TABLE table_name ALTER [COLUMN] column_name DROP DEFAULT

ALTER CREATE TABLE ....
ALTER is used to add / remove / modify the columns and integrity constraints of a table. The ADD [COLUMN] form adds a new column definition to the table (using the same column declaration syntax in the CREATE command). The DROP [COLUMN] form drops the column with the name from the table. ALTER [COLUMN] column_name SET default_expr alters the default value for the column. ALTER [COLUMN] column_name DROP DEFAULT removes the default value set for the column.

The following example adds a new column to a table;

ALTER TABLE Order ADD notes VARCHAR(60000) DEFAULT 'n/a'

ADD constraint_declare is used to define a new integrity constraint on a table (using the same constraint declaration syntax in the CREATE command). DROP CONSTRAINT is used to drop a named constraint from a table.

The other form of this statement is ALTER CREATE TABLE .... This alters the table to the specification of the given CREATE statement. Any columns that are in the original table are not lost provided the column name is in the new table specification. Any columns that were not in the original table are set to the default value.

The following example demonstrates this form of ALTER statement;

ALTER CREATE TABLE table
( col1 INTEGER NOT NULL UNIQUE,
col2 NUMERIC,
col3 VARCHAR(90000) )
The ALTER CREATE TABLE ... syntax is an extension to the SQL-92 standard.

4. DROP TABLE syntax

DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....
Removes the table(s) from the database. The IF EXISTS clause will drop the table only if it exists. If this clause is not present an error is generated if the table does not exist. Any data that was in a dropped table is lost so use with care.

5. CREATE VIEW syntax

CREATE VIEW table_name [ ( column_name1, column_name2, ... ) ]
AS SELECT ...
Creates a new view. A view is a virtual table based on the result of a SELECT query. The content of a view may reference any number of other tables and views.

A simple example of a view follows;

CREATE VIEW ViewOfTableA AS SELECT col1 FROM TableA
A view acts like a regular table and can be queried as you would a table made with the CREATE TABLE statement. Views are read-only.

6. DROP VIEW syntax

DROP VIEW table_name
Removes a view from the database. A view can be changed by dropping and recreating it.

7. CREATE SEQUENCE syntax

CREATE SEQUENCE name
[ INCREMENT increment_value ]
[ MINVALUE minimum_value ]
[ MAXVALUE maximum_value ]
[ START start_value ]
[ CACHE cache_value ]
[ CYCLE ]
Creates a new sequence generator that can be used to generate an iterative sequence of values. Sequence generators have a number of uses including the creation of primary keys for a table. The INCREMENT, MINVALUE, MAXVALUE, START, and CACHE values are all optional.

The INCREMENT value specifies how the sequence increments each iteration. By default a sequence generator increments by 1. The MINVALUE and MAXVALUE values specify the bounds of the sequence generator. By default MINVALUE and MAXVALUE are 0 and Long.MAX_VALUE respectively. The START value specifies the first key (exclusive) of the generator. The CACHE value specifies how many keys should be cached ahead of time.

Below is an example that creates a new sequence generator called 'seq_key_1' that starts at 10 and increments by 2 each iteration;

CREATE SEQUENCE seq_key_1 INCREMENT 2 START 10
A sequence generator is accessed by a call to the NEXTVAL function. The NEXTVAL function iterates the generator and returns the next value from the sequence. The NEXTVAL function is an atomic operation and guarantees that no two identical values will be returned regardless of the frequency or concurrency of calls to the function. Below is a simple example;

SELECT NEXTVAL('seq_key_1')

8. DROP SEQUENCE syntax

DROP SEQUENCE name
Drops a sequence generator previously created with the CREATE SEQUENCE statement. A sequence generator may be changed by dropping the sequence and then recreating it.

9. COMPACT TABLE syntax

COMPACT TABLE table_name
Compacts the table data file in the file system. This removes all unused space from the table file and may rearrange the structure of the table to a form that better fits the characteristics of the data being stored.

10. CREATE SCHEMA syntax

CREATE SCHEMA schema_name
Creates a schema with the given name. By default a database has three schema initially defined, SYS_INFO, SYS_JDBC and APP. The SYS_INFO and SYS_JDBC schema contain a number of important system tables and the APP schema is the default user schema.

The following is an example of creating a new schema and changing to it;

CREATE SCHEMA my_schema;
SET SCHEMA my_schema;

11. DROP SCHEMA syntax

DROP SCHEMA schema_name
Drops the schema with the given name. A schema may only be dropped if it contains no tables. The SYS_INFO and APP schema may not be dropped.

12. INSERT syntax

INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
VALUES ( expression1_1, expression1_2, .... ),
( expression2_1, expression2_2, .... ), ....

INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
SELECT ...

INSERT INTO table_name
SET col_name1 = expression1, col_name2 = expression2, ....
This is the SQL command to insert records into a table in the database. This statement comes in three forms. The first inserts data from a VALUES clause;

INSERT INTO table ( col1, col2, col3 )
VALUES ( 10, 4 + 3, CONCAT('1', '1', 'c') ),
( 11, (28 / 2) - 7, CONCAT(col1, 'c') )
The second form is used to copy information from a SELECT query into the table specified in the INSERT statement. For example;

INSERT INTO table ( col1, col2, col3 )
SELECT id, num, description
FROM table2
WHERE description LIKE '11%'
The third form uses a list of column SET assignments. For example;

INSERT INTO table
SET col1 = 10, col2 = 4 + 3, col3 = CONCAT(col1, 'c')
If a column of the table is not specified in an INSERT the default value declared for the column is used. If no default value was declared a NULL value is inserted in the column. If the column is declared as NOT NULL the insert operation fails.

13. DELETE syntax

DELETE FROM table_name
[ WHERE expression ]
[ LIMIT limit_amount ]
Deletes all the rows from the table that match the WHERE clause. An optional LIMIT clause specifies the maximum number of matched rows to be removed. An example of using the DELETE statement;

DELETE FROM table
WHERE col3 LIKE '11%' AND col1 < 1000 LIMIT 200

14. UPDATE syntax

UPDATE table_name
SET col_name1 = expression1, col_name2 = expression2, ....
[ WHERE expression ]
[ LIMIT limit_amount ]
Updates information in a table. The SET clause is a list of assignments that describe how the columns of the data matched by the WHERE clause are to be updated. Any columns not assigned in the SET clause are left unchanged. Examples of using UPDATE;

UPDATE Employee
SET salary = salary * 1.25
WHERE name = 'Bob'

UPDATE Order
SET id = id + 3, part = CONCAT(part, '-00')
WHERE part LIKE 'PO-%'
LIMIT 10

15. SELECT syntax

SELECT [ DISTINCT | ALL ]
column_expression1, column_expression2, ....
[ FROM from_clause ]
[ WHERE where_expression ]
[ GROUP BY expression1, expression2, .... ]
[ HAVING having_expression ]
[ ORDER BY order_column_expr1, order_column_expr2, .... ]

column_expression ::= expression [ AS ] [ column_alias ]

from_clause ::= select_table1, select_table2, ...
from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr ...
from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr ...
from_clause ::= select_table1 [INNER] JOIN select_table2 ...

select_table ::= table_name [ AS ] [ table_alias ]
select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]

order_column_expr ::= expression [ ASC | DESC ]
The SELECT statement is used to form queries for extracting information out of the database. The following example query will return the number, quantity and price of all orders for more than 5 items sorted in descending order by order number. In addition it rounds the order price to two decimal places and applies a dollar ($) sign to the output.

SELECT number, quantity, CONCAT('$', ROUND(price, 2))
FROM Order
WHERE quantity > 5
ORDER BY number DESC
The ORDER BY and GROUP BY clause may refer to a column, a column alias, or an expression. The HAVING clause is evaluated after the grouping and aggregate columns have been resolved.

For examples of using SELECT with aggregate functions see the 'Internal SQL Functions' section.

16. COMMIT and ROLLBACK syntax

COMMIT
ROLLBACK
Transactional operations for closing a transaction and either committing all the changes made or rolling back and disposing all changes. COMMIT may cause a concurrent transaction conflict exception to be thrown. If a conflict is detected the transaction is automatically rolled back. See the 'Transactions' section of the documentation for further details of how Mckoi handles transactions.

17. CREATE USER, ALTER USER and DROP USER syntax

CREATE USER username SET PASSWORD 'password'
[ SET GROUPS groups_list ]
[ SET ACCOUNT ( LOCK | UNLOCK ) ]

ALTER USER username SET PASSWORD 'password'
[ SET GROUPS groups_list ]
[ SET ACCOUNT ( LOCK | UNLOCK ) ]

DROP USER username
These are user management commands for creating/altering and dropping users in the system. Only members of the 'secure access' group are permitted to perform these operations, which includes the administrator user that is setup when the Mckoi database is created.

The following example creates a user called 'harry' with the password 'cat';

CREATE USER harry SET PASSWORD 'cat'
See the 'JDBC Driver' section for documentation on connecting to a database using a username and password to connect to a database.

18. GRANT/REVOKE syntax

GRANT privileges ON database_object TO ( PUBLIC | user_list )
[ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ] privileges ON database_object
FROM ( PUBLIC | user_list )

privileges ::= priv_item1, priv_item2, ...

priv_item ::= ALL [ PRIVILEGES ] | SELECT | INSERT | UPDATE |
DELETE | REFERENCES | USAGE

database_object ::= [ TABLE ] table_name | SCHEMA schema_name

user_list ::= PUBLIC | username1, username2, ...
Grants or revokes types of access on a table or view to a user. When a table or view is created the system gives full grant options to the user that created the object. The user is given the option to grant other users selective access to the object through the GRANT and REVOKE syntax. For example, the follow statement shows how a user would grant user 'toby' permission to SELECT from a table called MyTable;

GRANT SELECT ON TABLE MyTable TO toby
The GRANT command allows granting all users access to an object. The following statement makes MyTable globally readable;

GRANT SELECT ON TABLE MyTable TO PUBLIC
If you wish to give a user the option of granting a privilege to another user, add WITH GRANT OPTION to the GRANT statement.

19. SET syntax

SET variable = expression
SET AUTO COMMIT ( ON | OFF )
SET TRANSACTION ISOLATION LEVEL ( SERIALIZABLE )
SET SCHEMA schema_name

Makes a change to the state of the connection. SET AUTO COMMIT is used to switch transaction 'auto commit mode' on or off. When auto commit mode is on the engine commits after every statement. By default, a connection starts with auto commit mode switched on. SET TRANSACTION ISOLATION LEVEL currently only supports the SERIALIZABLE isolation level. See the 'Transactions' section of the documentation for details of how Mckoi handles transactions.

SET SCHEMA is used to change the default schema of a connection.

20. DESCRIBE syntax

DESCRIBE table_name
This command provides information about the columns of the table. It shows the column names, the type / size and scale (if applicable) and other useful information.

21. SHOW syntax

SHOW engine_variable

engine_variable ::= TABLES | SCHEMA | STATUS | CONNECTIONS
Shows internal information about the database system. SHOW TABLES returns a list of tables in the database. SHOW STATUS returns debugging and statistical information about the internal state of the database engine. SHOW CONNECTIONS returns a snapshot of the current connections on the database. SHOW SCHEMA lists all the schema defined.

22. SHUTDOWN syntax

SHUTDOWN
Shuts down the database. If the database is running as a server the database shuts down cleanly and the process is stopped. If the database is embedded in a Java application it is cleanly put into a shut down state.

Only a user with the correct grants may successfully execute this command.

Navigate to another URL by pressing enter key

2009-06-04T02:46:00.000-07:00

Problem : When I press enter , I want to navigate to another web page. I do not want to use form onsubmit event, as I am having the form tag on the master page.

Solution:
I have a form which has a search text box. The user enters the search keywords and presses enter.
Below is the code (HTML rendering in this blog is awful, sorry about the illegible code) which does that

<script language="javascript" type="text/javascript">
function submitOnEnter(event)
{
var keycode;
if (window.event)
keycode = window.event.keyCode;
else if (e) keycode = e.which;
else return true;
if (keycode ==13)
{
location.href ="default.aspx";
location.reload;
// this always get me!! I forget to call the reload}

}
</script>

Search
onclick="default.aspx">

A much more simpler implementation can be done using JQuery.

Thanks to jackson pushpanathan for this piece of work.You can find the original code here

http://technosia.blogspot.com/2008/10/navigate-to-another-url-by-pressing.html

c # Question and Answers

2009-06-01T04:33:00.002-07:00

1) Explain about C#?
C # is also known as c sharp. It is a programming language introduced by Microsoft.
C# contains features similar to Java and C++. It is specially designed to work with Microsoft .NET platform.

2) Explain about the rules for naming classes in C#?
These are the rules for naming classes in c sharp.
• Must begin with a letter. This letter may be followed by a sequence of letters, digits (0-9), or ‘_’. The first character in a class name cannot be a digit.
• Must not contain any embedded space or symbol like ? - + ! @ # % & * ( ) { } [ ] , : ; ‘ “ \ and/. However an underscore _ can be used wherever a space is required.
• Must not use a keyword for a class name.

3) What are the rules to be followed while naming variables in C#.
The following rules are used for naming variables in C#.
* Must begin with a letter or an underscore _ which may be followed by a sequence of letters, digits (0-9), or ‘_’. The first character in a variable name cannot be a digit.
* Must not contain any embedded space or symbol like ? - + ! @ # % & * ( ) { } [ ] , : ; ‘ “ \ and/. However an underscore _ can be used wherever a space is required.
• Must be unique
• Can have any number of characters
• Keywords cannot be used as variable names.

4) What are the different types of Data?
There are two different types of data supported by C#. They are
1) Value types: -They directly contain data. When you declare an int variable, the system allocates memory to store the value.
2) Reference type: -The reference types do not maintain data but they contain a reference to the variables, which are stored in memory. This means that if the value in the memory location is modified by one of the variables, the other variables automatically reflect the changes value

5) Explain about member functions?
A function is a set of statements that perform a specific task in response to a message. The functions of a class are called member functions in Csharp. Member functions are declared inside the class. The function declaration introduces the function in the class and the function definition contains the function code.

6) Explain about comment entry?
Comments are a part of the program and are used to explain the code. Compilers ignore comment entries. If a comment entry spans more than one line, it has to be enclosed within ‘/*’ and ‘*/’. The symbol ‘//’ treats the rest of code within the same line as a comment.

7) What are operators?
Applications use operators to process the data entered by a user. Operators like + and – are used to process variables and return a value. An operator is a set of one or more characters that is used for computations or comparisons. Operators can transform one or more data values, called operands into a new data value.

8) Explain about the break statement?
A break statement is used to exit the switch statement. This prevents the execution of the remaining case structures by ending the execution of the switch case construct. Each break statement terminates the enclosing switch statement and the flow of control. If none of the cases match the default case is invoked.

9) Define encapsulation?
Encapsulation literally means to enclose in or as if in a capsule. Encapsulation is defined as the process of enclosing one or more items within a physical or logical package. It involves preventing access to nonessential details.

10) Define access specifier with reference to class?
An access specifier defines the scope of a class member. A class member refers to the variables and functions in a class. A program can have one or more classes. You may want some members of a class to be accessible to other classes. But, you may not want some other members of the class to be accessible outside the class.

11) Describe about private access specifier?

The private access specifier allows a class to hide its member variables and member functions from other class objects and functions. Therefore, the private member of a class is not visible outside a class. If a member is declared private, only the functions of that class can access the member. Even the instance of the class cannot access its members

12) Explain about protected internal access specifier?
This specifier allows a class to hide its member variables and member functions to be accessed from other class objects and functions, except the child class, within the application. The protected internal access specifier becomes important while implementing inheritance.

13) Define parameter by value?
Pass by value is the default mechanism for passing parameters to a method. The simplest definition of a value parameter is a data type name followed by a variable name. When a method is called, a new storage location is created for each value parameter. The values of the corresponding expressions are copied into them. The expression supplied for each value parameter must be similar to the declaration of the value parameter.

14) State the methods through which parameters can be passed?
Parameters can be passed by using any one of the following mechanism.
Value: -They are sometimes called in or out parameters; therefore, the data can be transferred into the method but cannot be transferred out.
Reference: -Are sometimes called in or out parameters, therefore, the data can be transferred into the method and out again.
Output: -Are sometimes called out parameters, data can be transferred out of the method.

15) Explain about reference parameter?
A reference parameter is a reference to a memory location of a data member. Unlike a value parameter, a reference parameter does not create a new storage location. Instead a reference parameter represents the same location in memory as the variable that is supplied in the method call.

16) How do you use a structure?
A structure is a value type data type. When you want a single variable to hold related data of various data types, you can create a structure. To create a structure you use the struct keyword.

17) What is an enumerator?
Enumeration is a value data type, which means that enumeration contains its own values and cannot inherit or pass inheritance. Enumerator allows you to assign symbolic names or integral constants.

.NET Assembly

2009-06-01T04:33:00.001-07:00

What is a .NET assembly?
An assembly is the primary building block of a .NET application and can take the form of a dynamic link library (DLL) or executable file (EXE). An assembly is a collection of functionality that is built, versioned, and deployed as a single implementation unit.

What does an assembly contain?
A .NET assembly may contain the following elements:

Assembly Manifest - Metadata that describes the assembly and its contents (see below)
Source Code - Compiled into Microsoft intermediate language (MSIL)
Type Metadata - Defines all types, their properties and methods, and most importantly, public types exported from this assembly
Resources - Icons, images, text strings and other resources
The assembly manifest is required; the other elements are optional.

What is an assembly manifest?
An assembly manifest is metadata inside an assembly that describes everything there is to know about the assembly and its contents. The manifest contains:

Strong Name - The assembly's name, version, culture, optional processor architecture, and public key (for shared assemblies)
File Contents - Name and hash of all files in the assembly
Type List - Types defined in the assembly, including public types that are exported from the assembly
Resource List - Icons, images, text strings and other resources contained in the assembly
Dependencies - Compile-time dependencies on other assemblies
Security - Permissions required for the assembly to run properly

What is a multi-file assembly?
An assembly can consist of one or more files called modules. Exactly one of these modules contains the assembly manifest. Note that the files in a multi-file assembly can reside in separate locations and are linked together with the assembly manifest.

Multi-file assemblies are rare, and Visual Studio doesn't directly support their creation. The most common reason for multi-file assemblies is when a single assembly combines code from multiple programming languages. (more)

What is the difference between a private and shared assembly?
A private assembly is used only by a single application and is stored in that application's installation folder (or subfolder therein). The name of a private assembly name must be unique within the application that uses it.

A shared assembly is used by multiple applications and is typically stored in a global folder known as the Global Assembly Cache (GAC). When building an assembly, a developer must specifically choose to build it as a shared assembly by giving it a cryptographically strong name. For example, the .NET Framework is a collection of shared assemblies.

What is the difference between an assembly and a namespace?
Namespaces are logical, whereas assemblies are physical.

A namespace is a logical naming scheme to group related types. Namespaces can contain other namespaces to form a hierarchy. The "fully qualified name" of a type is its namespace followed by its type name, separated by a period (for example, System.Windows.Forms.Button). Type names must be unique within a namespace, but the same type name can be used in different namespaces.

An assembly is a physical deployment scheme to group related types. An assembly can contain one or many namespaces. A namespace can exist in one or many assemblies.

What are assembly attributes?
Assembly attributes are values (typically set by the developer) that provide additional information about a .NET assembly. Assembly attributes are grouped as follows:

Identity Attributes - Determine the identity of an assembly: name, version, culture and flags.
Informational Attributes - Provide additional information about an assembly: company name, product name, copyright, trademark, and file version.
Manifest Attributes - Provide information in the assembly manifest: assembly title, description, alias, and configuration (such as debug or release).
Strong Name Attributes - Used to help set an assembly's strong name, including key file, key name, and whether delay signing is used.
How do I set assembly attributes?
There are two ways to set the attributes for an assembly in your development project. Using Visual Studio 2005:

Option 1: AssemblyInfo File
In the Visual Studio Solution Explorer, navigate to the Properties folder, then open the AssemblyInfo.cs file. You can directly edit the attributes in the AssemblyInfo file:

// General information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle( "MyAssembly" )]
[assembly: AssemblyDescription( "Description of MyAssembly" )]
[assembly: AssemblyConfiguration( "" )]
[assembly: AssemblyCompany( "MyCompany" )]
[assembly: AssemblyProduct( "MyProduct" )]
[assembly: AssemblyCopyright( "Copyright (c) 2007 MyCompany Inc" )]
[assembly: AssemblyTrademark( "MyProduct is a trademark of MyCompany Inc." )]
[assembly: AssemblyCulture( "" )]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible( false )]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid( "b7bfd909-f048-4d5b-9f33-1d0642398e4f" )]

// Version information for an assembly consists of the following four values:
// Major Version
// Minor Version
// Build Number
// Revision
[assembly: AssemblyVersion( "1.0.0.*" )]
[assembly: AssemblyFileVersion( "1.0.0.0" )]

Option 2: Project Properties
Select your assembly project in the Visual Studio Solution Explorer. Click the Properties button. Select the Application tab. Then click the Assembly Information button. The following dialog will appear, allowing you to edit the assembly attributes:

How do I access a loaded assembly?
When working with assemblies, be sure to include the Reflection namespace:

using System.Reflection;
To access your application's main assembly (i.e., the executable file):

Assembly asm = Assembly.GetExecutingAssembly();
To access an external assembly loaded by your application, call GetAssembly with a type defined in the external assembly:

Assembly asm = Assembly.GetAssembly( typeof( MyType ) );
How do I retrieve assembly attributes programmatically?
Once you have the Assembly object as shown above, you can obtain its identity attributes from its associated AssemblyName object:

AssemblyName asmName = asm.GetName();
Console.WriteLine( "Name={0}, Version={1}, Culture={2}, ProcessorArchitecture={3}",
asmName.Name, asmName.Version, asmName.CultureInfo, asmName.ProcessorArchitecture );For all other assembly attributes, you can load the attribute directly with the GetCustomAttributes method:

// assembly description attribute
string asmDesc = ((AssemblyDescriptionAttribute)asm.GetCustomAttributes(
typeof( AssemblyDescriptionAttribute ), false )[0]).Description;
// assembly title attribute
string asmTitle = ((AssemblyTitleAttribute)asm.GetCustomAttributes(
typeof( AssemblyTitleAttribute ), false )[0]).Title;
// etc.

To view all attributes for an assembly:

object[] attributes = asm.GetCustomAttributes( true );
foreach (object obj in attributes)
{
Console.WriteLine( obj.ToString() );
}

What is a strong name?
A strong name is a .NET assembly name combined with its version number and other information to uniquely identify the assembly. This allows multiple versions of the same assembly to peacefully co-exist in the global assembly cache, where shared assemblies are typically stored.

A strong name consists of five parts:

Simple Name - Usually the name of the file (without the extension) that contains the assembly
Public Key - RSA cryptographic public key that helps verify the assembly's authenticity
Version - Four-part version number, in the form of Major.Minor.Build.Revision
Culture - Target audience for the assembly, such as "neutral" (default audience), "en-us" (English - United States) or "fr" (France) etc.
Processor Architecture - Defines the assembly's format, such as MSIL (intermediate language) or x86 (binary for Intel x86 processors)
An example strong name is "Mini-Launcher, Version=0.3.612.24542, Culture=neutral, PublicKeyToken=ffa52ed9739048b4, ProcessorArchitecture=MSIL".

Why use strong names?
Strong names are required to store shared assemblies in the global assembly cache (GAC). This is because the GAC allows multiple versions of the same assembly to reside on your system simultaneously, so that each application can find and use its own version of your assembly. This helps avoid DLL Hell, where applications that may be compiled to different versions of your assembly could potentially break because they are all forced to use the same version of your assembly.

Another reason to use strong names is to make it difficult for hackers to spoof your assembly, in other words, replace or inject your assembly with a virus or malicious code.

What is a strong name key file?
A strong name key file has a .snk extension and contains a unique public-private key pair. You use the strong name key file to digitally sign your assembly (see below). Note that this type of file is not secure, as the private key in a .snk file can be easily compromised.

For added protection, Visual Studio can encrypt a strong name key file, which produces a file with the .pfx (Personal Information eXchange) extension. The .pfx file is more secure because whenever someone attempts to use the encrypted key, she will be prompted for the password.

How do I create a strong name key file for a .NET assembly?
Visual Studio 2005 makes it easy to create a strong name key file:

Select your assembly project in the Visual Studio Solution Explorer.
Click the Properties button. The project properties will appear in the main window.
Select the Signing tab:
Check the Sign the assembly checkbox.
In the Choose a strong name key file drop-down, select New. The "Create Strong Name Key" dialog appears:
In the Key file name text box, type the desired key name. Typically this is the name of your assembly but can be anything. Visual Studio will automatically append the proper file extension.
If desired, you can protect the strong name key file with a password. To do so, check the Protect my key file with a password checkbox, then enter and confirm the password.
Click the OK button.
Now when you compile your project, Visual Studio will automatically sign your assembly with the new strong name key you have just created.

Or if you prefer to use the command-line, you can create a key pair file with the strong name utility sn.exe in the .NET SDK, for example:

sn -k MyKey.snk

Then you reference that key file to when compiling your code with the C# compiler csc.exe:

csc /keyfile:MyKey.snk MyCodeFile.cs

What does it mean to sign an assembly?
.NET uses digital signatures to verify the integrity of an assembly. The signatures are generated and verified using public key cryptography, specifically the RSA public key algorithm and SHA-1 hash algorithm. The developer uses a pair of cryptographic keys: a public key, which everyone can see, and a private key, which the developer must keep secret.

To create a strong-named assembly, the developer signs the assembly with his private key when building the assembly. When the system later loads the assembly, it verifies the assembly with the corresponding public key.

How do I sign an assembly?
When you compile your assembly with a strong name key file, the compiler digitally signs the assembly:

The compiler calculates the cryptographic digest (a hash) of your assembly contents. This is known as the compile-time digest. Modifying just a single byte of your assembly will change this hash value.
The compiler encrypts the digest using the 1024-bit private key from your public-private key pair file.
The compiler then stores the encrypted digest and public key into the assembly.
How does the system verify a signed assembly?
Sometime later, when an application attempts to load your signed assembly:

The .NET assembly loader calculates the cryptographic digest of the current assembly contents. This is known as the run-time digest.
The loader extracts the stored compile-time digest and public key from the assembly.
The loader uses the public key to decrypt the compile-time digest.
The loader then compares the run-time digest with the decrypted compile-time digest to ensure they match. If not, then the assembly has been modified since you compiled it, and the assembly load fails.
This process is different when loading shared assemblies from the GAC. Because assemblies are verified when they are first installed into the GAC–and they cannot be modified while in the GAC–the .NET assembly loader does not verify an assembly when loading it from the GAC. This can improve the startup speed of your application if you load many shared assemblies.

What is delay signing?
Delay signing is signing an assembly with its strong name public key, which is freely distributable, instead of using the private key as usual. This allows developers to use and test a strong-named assembly without access to the private key. Then at a later stage (typically just before shipping the assembly), a manager or trusted keyholder must sign the assembly with the corresponding private key. (more)

How do I protect my private keys?
Private keys must remain secret. A hacker with your private key could spoof your signed assemblies by replacing or injecting them with a virus or other malicious code. There are a few strategies you can use to protect your private keys:

Password Protection. As shown above, Visual Studio will allow you to protect your strong name key file with a password.
Delay Signing. As mentioned above, delay signing enables your development team to build and test your assembly without access to the private key.
Cryptographic Container. One of the most secure ways to protect your strong name key is to store it in a secure cryptographic container (see sidebar "Protecting Your Keys" in this article).
How many private keys should I have?
There are three main strategies for how many private keys a developer should use:

One private key for all your applications and assemblies
One private key for each application (an application may have multiple assemblies)
One private key for each assembly
Which option to use depends on your security situation and risk tolerance. With option 1, it's easier to keep a single key secure, but if your one private key is compromised, then all of your assemblies are compromised. With option 3, there are more keys to manage and hence lose, but if one key is compromised, then only one of your many assemblies is compromised. I recommend option 2 or 3 to reduce your overall exposure.

Are there problems with using strong names?
Strong names are not perfect. There are some issues to consider when using strong names:

Requires Exact Match. If you use strong names, your application or library must load the assembly with the exact strong name that you specify, including version and culture. Note that you can bypass this requirement with a publisher policy (to be discussed in a future article).
Cannot Lose Private Key. If your private key is lost or stolen, the security of your assembly is compromised. You will be forced to re-issue a new assembly signed with a new public-private key pair.
Cannot Stop Full Replacement. Strong names cannot prevent a hacker from removing the strong name signature, maliciously modifying your assembly, re-signing it with his own key, and then passing off his assembly as yours. The user must have some way to ensure the public key they have from your assembly is valid and truly came from you. Note that you can use more sophisticated signing schemes (such as Authenticode) to help with this issue.

Where are shared assemblies stored?
A shared assembly is used by multiple applications. You can store shared assemblies pretty much anywhere. However, the challenge is to ensure that all dependent applications can find the shared assembly. The recommended way to ensure this is to store shared assemblies in the Global Assembly Cache.

What is the Global Assembly Cache (GAC)?
The Global Assembly Cache is a system folder (typically C:\Windows\assembly) that contains .NET shared assemblies. Companies that wish to share assemblies with others or even just among their own applications typically store these shared assemblies in the GAC. All of the .NET framework libraries are stored in the GAC.

Why should I store my shared assemblies in the GAC?
You should install assemblies in the GAC only when necessary. As a general guideline, assemblies should be kept private and stored in the application's folder unless you explicitly need to share them. There are some benefits to storing shared assemblies in the GAC:

Global Location

The GAC is the known standard location for .NET shared assemblies. When an application attempts to load an assembly, the GAC is one of the first places it looks. If there's any chance that an application outside your control may someday require access to your shared assembly, you should install your assembly in the GAC so the application is sure to find it.

Security
The GAC is a system folder typically protected by administrator rights. Once an assembly is installed in the GAC, it cannot be easily modified. Also, assemblies stored in the GAC must be signed with a cryptographic key. These protections make it difficult to spoof your assembly, in other words, replace or inject your assembly with a virus or malicious code.

Version Management
.NET allows multiple versions of the same assembly to reside in the GAC so that each application can find and use the version of your assembly to which it was compiled. This helps avoid DLL Hell, where applications that may be compiled to different versions of your assembly could potentially break because they are all forced to use a single version of your assembly.

Faster Loading
The system verifies assemblies when they are first installed in the GAC, eliminating the need to verify an assembly each time it is loaded from the GAC. This can improve the startup speed of your application if you load many shared assemblies.

Why would I avoid the GAC?
The GAC should contain "global" shared assemblies only, so there are many instances when you would NOT install an assembly in the GAC:

The assembly is private to your application and not to be shared with other applications.
You want to use XCOPY or FTP copy to install a .NET application to a single folder. This eliminates the need to access the Registry and GAC and does not require administrator rights.
The assembly is not strong-named or you do not want tight version control.
COM interop and unmanaged code do not require the GAC.
How do I see assemblies installed in the GAC?
The .NET Framework includes an Assembly Cache Viewer. Open Windows Explorer, enter %windir%\assembly in the address bar, and all global assemblies will appear in a special view that shows the assembly name, version, culture, public key token, and processor architecture.

Can I install multiple versions of the same assembly in the GAC?
Yes. Normally you would not be able to have two files with the same name in a Windows folder, but the GAC is a special folder that stores its contents by strong name. Hence, two assemblies with the same name but different versions or cultures may coexist in the GAC.

How do I add/remove assemblies from the GAC?
Assemblies added to the GAC must be signed with a strong name. There are multiple ways to add/remove assemblies from the GAC:

Windows Installer
The preferred way to add/remove assemblies from the GAC is with Microsoft Windows Installer 2.0. Visual Studio includes a limited version of Windows Installer, and most major setup programs such as InstallShield also use Windows Installer. There are benefits to using Windows Installer:

Windows Installer provides a simple interface for developers to add/remove shared assemblies in the GAC and can handle private assemblies as well.
Installer provides a familiar interface and setup experience for the user.
Installer can also install application shortcuts and supporting files such as ReadMe and license agreements and can run other installation programs and scripts.
Installer registers and tracks references to assemblies installed in the GAC to determine which assemblies are still required.
Installer can repair and patch assemblies and rollback unsuccessful installations.
Installer can install assemblies on-demand as they are needed by applications.
GAC Utility
The .NET developer's kit includes a command line utility GACutil.exe to interact with the GAC. This utility is intended for use in a development environment only and should not be used to install assemblies on a client PC because:

The GACutil license agreement states that it is not freely distributable.
GACutil is part of the .NET SDK, which may not be installed on many target PCs.
GACutil lacks many important features found in Windows Installer such as assembly repair and rollback.
Assembly Cache Viewer
Using the Assembly Cache Viewer shown above, you can drag & drop assemblies from any folder into the GAC and also delete assemblies installed in the GAC.

.NET Framework Configuration Administrative Tool
To access the .NET Framework Configuration Tool:

Click Start > Control Panel.
Double-click on Administrative Tools.
Double-click on Microsoft .NET Framework 2.0 Configuration.
Ensure My Computer is selected in the tree.
In the Tasks group, click the Manage the Assembly Cache link.
Two links appear, enabling you to view and add assemblies in the GAC.
How do I access the GAC programmatically?
You can access the GAC from code with the fusion.dll library. Here is an excellent C# wrapper for the GAC.

You are strongly advised NOT to access the GAC from code unless you are creating an administrative or setup tool. The Fusion APIs expose your application to the inner workings of assembly binding and may cause your application to fail on future .NET versions.

How do I add my shared assembly to the Visual Studio "Add Reference" dialog?
If you add an assembly to the GAC, it will NOT automatically appear in the Visual Studio "Add Reference" dialog; instead you must add your assembly manually.

How do I move the GAC?
When installing .NET, you cannot configure the GAC location, however you can move the GAC after it is installed. See this article and scroll down to "Relocating the GAC."