Computer Programming: Web Form controls, General introduction in ASP.NET

The Web form controls are closely designed to resemble standard Visual basic Winforms controls. These controls are used for designing the interface for any Web application, for example, when you visit the website of Google, you type your search query in a TextBox, which is a control. ASP.NET provides a standard set of controls that can be used for the development of Web Applications. You can access all these controls from the ToolBox present in the Visual Studio Integrated Development Environment ( IDE ) . These controls can easily be used by just dragging and dropping them at any desired location on the Web form. Based on the tasks performed by them, these controls on the ToolBox are grouped under various categories known as tabs. For example, controls for validating the data are put under the validation tab and controls used for logging on the websites are put under the Login tab. Similarly, controls for common use are put under the standard tab and are known as standard controls . All these controls come under the Control class. All the Standard/ Web server controls are based on the WebControl class, which, in turn, is based on the control class. In other words, the WebControl class has originated from the Control class.

Later session we will discuss about the inheritance hierarchy, pubic properties, public methods, and public events of the Controls and the WebControl classes. We also get to know about the various controls such as Label, Button, TextBox, Literal, Placeholder, Hidden Field, and FileUpload that originate from the WebControl class, along with their implementations.  

How to Perform Grouping of Data Matching a Criteria: SQL Programming

The database users might need to view data in a user-defined format. These reports might involve summarizing data on the basis of various criteria. SQL Server allows you to generate summarized data reports using the PIVOT clause of SELECT statement.

The PIVOT operator is used to transform a set of columns into values. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. In addition, it also performs aggregations on the remaining column values if required in the output. Following is the syntax of PIVOT operator is:

SELECT * from table_name
PIVOT (aggregation_function (value_column)
FOR pivot_column
IN (column_list)
) table_alias

Where,

• Table_name: name of table on which query will execute.
• Pivot_column: the only column on which condition perform.
• Table_alias: alias name of the table used in query only.

Consider an example, you want to display the number of purchase orders placed by certain employees, laid down with the vendors. The following query provides this report:

SELECT VendorID, [164] AS Empl, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198],[223], [231], [233] )
) AS pvt
ORDER BY VendorID

Following output is displayed by the preceding statements.

How to use Compute and ComputeBy Clauses in Query: SQL Programming

The COMPUTE clause, with the SELECT statement, is used to generate summary rows by using aggregate functions in the query results, in sql programming. The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data. The column on which the data is to be grouped is mentioned after the BY keyword.

The GROUP BY clause is used to generate a group summary report and does not produce individual table rows in the result set, whereas the COMPUTE and COMPUTE BY clauses generate the summary report with individual data rows from the table. In other words, the COMPUTE clause is used for control-break summary reporting applications that generate detailed information in the result set.

Syntax:

SELECT column_list
FROM table_name
ORDER BY column_name
COMPUTE aggregate_function (column_name) [, aggregate_function(column_name) …]
[BY column_name [, column_name]…]

Where,

• ORDER BY column_name specifies the name of the column(s) by which data in the result is to be sorted.
• COMPUTE aggregate_function specifies any row aggregate function from the aggregate function list.
• Column_name specifies the name of the column(s) for which the summary report is to be displayed.
• BY column_name specifies the name of the column(s) by which data is to be grouped.

The following SQL query uses the COMPUTE BY clause to calculate the average sickLeaveHours and VacationHours from the Employee table and display them based on Title, VacationHours, and SickLeaveHours:

SELECT Title, 'Average VacationHours' = VacationHours, 'Average SickLeaveHours' = SickLeaveHours FROM HumanResources.Employee
WHERE Title IN ('Recruiter', 'Stocker')
ORDER BY Title, VacationHours, SickLeaveHours
COMPUTE avg(VacationHours), Avg (SickLeaveHours) BY Title

In the preceding query, the data of the VacationHours and SickLeaveHours column is grouped for the recruiter and stocker and the summation of the vacation hours and sick leave hours for both is retrieved, as shown in the following figure.

Consider another example, where you need to use the COMPUTE BY clause to calculate the subtotals of VacationHours and SickLeaveHours for each value in the Designation column. The COMPUTE clause calculates the grand total of VacationHours and SickLeaveHours:

SELECT Title, 'Total VacationHours' = VacationHours, 'Total SickLeaveHours' = SickLeaveHours
FROM HumanResources.Employee
WHERE Title IN ('Recruiter', 'Stocker')
ORDER BY Title, VacationHours, SickLeaveHours
COMPUTE sum (VacationHours), sum(SickLeaveHours) BY Title COMPUTE sum (VacationHours) , sum (SickLeaveHours)

In the preceding example, initially the data of the VacationHours and SickLeaveHours column is grouped for the recruiter and stocker and the summation of the vacation hours and sick leave hours for the recruiter and stocker.

Both these keywords are not supported in the SQL Server management studio 2012, these queries can be executed in 2008 version.

How to Create First MVC Application in Visual Studio: Introduction to MVC

MVC framework, as explained in previous article, separates your logic, views and your database of the web application. Here are simple steps to create an MVC application using Visual Studio 2013, discussed below.

• Click on File-->New--> Project in Visual Studio (I have used VS 2013)
• The New Project window opened with a list of categories in the left pane and some of their installed templates. Select ASP.NET MVC 4 Web Application under the Web categories.

Leave the name as is, you can change it, and click on Ok button.

• Another window will opened which provides the options to choose the framework (by default the application may have) like empty, basic, internet or shown in the list.

• Select Internet application and click on Ok button, it will start creating an MVC application according to your selected options. 
• A dialog box appears to notify you about the process, after some time (depend on your system) your MVC project will create having some folders shown in the solution explorer below.

What are the uses of these folders and how they can be used by the programmer will be discussed later.

Computer Programming: Change Profile Value at runtime in asp.net with example

We already discussed about create profile for authenticated/anonymous user. This article will cover, Disable profile and save it either automatic or manually in asp.net. If you want to save profile in asp.net, follow some steps
Step-1: Add this code in web.config file
<system.web>
      <authentication mode="Windows" />
      <profile automaticSaveEnabled="false" >
        <properties>
          <add name="DOB" />          
        </properties>
      </profile>      
        <compilation debug="false" targetFramework="4.0" />
    </system.web>

Step-2: Add a web form into your project.
Step-3: First manually save some string into Profile property on Page_Load() method. Programmer can also use Profile.save() method.

Profile.DOB = "19/april/1986";
Profile.Save();

Step-4: Add  a TextBox, Button and a label Control to the Design window.
Step-5: Change Profile Property using TextBox at runtime

Default.aspx page 


<%@ Page Language="C#" %>

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

<script runat="server">

    protected void Button1_Click(object sender, EventArgs e)
    {
        Profile.DOB = TextBox1.Text;
        Profile.Save();
        Label1.Text = "Update DOB is :" + Profile.DOB;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page .IsPostBack )
        {
            Profile.DOB = "19/april/1986";
            Profile.Save();
            Label1.Text = "Your DOB is:" + Profile.DOB;
            
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    Profile Data :
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    
        <br />
        Enter DOB :
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
    
    </div>
    </form>
</body>
</html>
Code generate the following output

After change at runtime, Code generate the following output

How to Perform Grouping of Data Matching a Criteria: SQL Programming

At times, you need to view data matching specific criteria to be displayed together in the result set. For example, you want to view a list of all the employees with details of employees of each department displayed together.

Grouping can be performed by following clauses:

• GROUP BY
• COMPUTE and COMPUTED BY
• PIVOT

GROUP BY

The GROUP BY clause summarizes the result set into groups as defined in the query by using aggregate functions. The HAVING clause further restricts the result set to produce the data based on a condition. The syntax of the GROUP BY clause is:

SELECT column_list
FROM table_name
WHERE condition
[GROUP BY [ALL] expression [, expression]
[HAVING search_condition]

Where,

• ALL is a keyword used to include those groups that do not meet the search condition.
• expression specifies the column name(s) or expression(s) on which the result set of the SELECT statement is to be grouped.
• search_condition is the conditional expression on which the result is to be produced.

The following SQL query returns the minimum and maximum values of vacation hours for the different types of titles when the vacation hours are greater than 20:

SELECT JobTitle, Minimum = min (VacationHours), Maximum = max (VacationHours)
FROM HumanResources.Employee
WHERE VacationHours > 20 GROUP BY JobTitle

Outputs:

The GROUP BY ……..HAVING clause is same as the SELECT….WHERE clause. The result set produced with the GROUP BY clause eliminates all the records and values that do not meet the condition specified in the HAVING clause. The GROUP BY clause collects data that matches the condition, and summarizes it into an expression to produce a single value for each group. The HAVING clause eliminates all those groups that do not match the condition.

The following SQL query retrieves all the titles along with their average vacation hours when the vacation hours are more than 30 and the group average value is greater than 55:

SELECT Title, ‘Average Vacation Hours’ = avg (VacationHours) FROM HumanResources.Employee WHERE VacationHours > 30 GROUP BY Title HAVING avg (VacationHours) >55

The ALL keyword of the GROUP BY clause is used to display all groups, including those which are excluded by the WHERE clause. The ALL keyword is meaningful to those queries that contain the WHERE clause. If ALL is not used, the GROUP BY clause does not show the groups for which there are no matching rows. However, the GROUP BY ALL shows all rows, even if the groups have no rows meeting the search conditions.

The following SQL query retrieves the records for the employee titles that are eliminated in the WHERE condition:

SELECT Title, VacationHours = sum (VacationHours) FROM HumanResources.Employee WHERE Title IN (‘Recruiter’, ‘Stocker’,’Design Engineer’) GROUP BY ALL Title

How to Get Summarizing Data from Database: SQL Programming

Summary of the data contains aggregated values that help in data analysis at a broader level. For example, to analyse the sales, the users might want to view the average sales or total sales for a specified time period. The SQL Server provides aggregate functions to generate summarized data.

The users might also want to view the summarized data in different group based on specific criteria. For example, the users want to view the average sales data region-wise or product-wise. In such a case, the sales data of each region will be displayed together. You can group the data by using the GROUP BY clause of the SELECT statement. You can also use aggregate function to summarize data when grouping it.

Summarizing Data by Using Aggregate Functions

At times, you need to calculate the summarized values of a column based on a set of rows. For example, the salary of employees is stored in the Rate column of the EmployeePayHistory table and you need to calculate the average salary earned by the employees.

The aggregate functions, on execution, summarize the values for a column or a group of columns, and produce a single value. The syntax of an aggregated function is:

SELECT aggregate_function ([ALL|DISTINCT] expression) FROM table_name

Where,

• All specifies that the aggregate function is applied to all the values in the specified column.
• DISTINCT specifies that the aggregate function is applied to only unique values in the specified column.
• expression specifies a column or an expression with operators.

Avg Returns the average of values on a numeric expression, either all or distinct.
Count: Returns the number of values in an expression, either all or distinct. The count function also accepts (*) as its parameter, but it counts the number of rows returned by the query.

Min Returns the lowest value in the expression. The following SQL query retrieves the minimum value from the Rate column of the EmployeePayHistory table with a user-defined heading:

SELECT ‘Minimum Rate’ = min (Rate) FROM HumanResources.EmployeePayHistory

Max Return the highest value in the expression.

Sum Returns the sum total of values in a numeric expression, either all or distinct.