How to Retrieve XML Data Using XQuery

In addition to FOR XML, SQL Server allows programmer to extract data stored in variables or columns with the XML data type by using XQuery. XQuery is a language that uses a set of statements and functions provided by the XML data type to extract data. As compared to the FOR XML clause of the SELECT statement, the XQuery statements allow you to extract specific parts of the XML data.

Each XQuery statement consists of two parts, prolog and body. In the prolog section, you declare the namespaces. In addition, schemas can be imported in the prolog. The body parts specifies the XML nodes to be retrieved. The XQuery language includes the following statements:

• For: Used to iterate through a set of nodes at the same level as in an XML document.
• Let: Used to declare variables and assign values.
• Order by: Used to specify a sequence.
• Where: Used to specify criteria for the data to be extracted.
• Return: Used to specify the XML returned from a statement.

The XQuery statements also use the following functions provided by the XML data type:

Query: Used to extract XML from an XML data type. The XML to be retrieved is bicycle is manufactured at AdventureWorks, it passes through a series of work centre locations. Each work centre location produces a different cycle component. Therefore, the number of production steps varies between different work centres.

To analyse the production process, the management of AdventureWorks needs to retrieve a list of the location IDs of all the work centers, which have more than four steps. You need to generate the list displaying the location ids in the ascending order of the steps included in the work centres.

To perform this task, the database developer can create the following query:

SELECT Instructons.query
(‘ declare namespace
ns=http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions:
for $work in /ns:root/ns:Location
where count(#work/ns:step) > 4
order by count ($work/ns:step)
return
count($work/ns:step)’) AS Result
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Value: Used to return a single value from an XML document. To extract a single value, you need to specify an XQuery expression that identifies a single node and a data type of the value to be retrieved.

For example, the management of AventureWorks, Ins. Wants a list containing the product model id, product name, machine hours, and labour hours. However, not all product have production instructions. As a database developer, you have stored this data in the XML format in the ProductModel table. You can create the following query to display the results:

SELECT ProductModelID, Name, Instructions.value (‘declare namespace ns=”http//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductManuInstructions”;
(/ns:root/ns:Location/@LaborHours) [1]’, ‘float’)AS
LaborHours,
Instructions.value(declare namespace
Ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions”;
(/ns:root/ns:Location/@MachineHours) [1]’, ‘float’) AS MachineHours
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Exist: Used to check the existence of a node in an XML data. The function returns I if the specified node exists else it returns 0. For example, the management of AdventureWorks, wants the details of all the customers in the city ‘NJ’. The details of all the customers are stored in an XML format in the CustDetails table. You can use the following query to display the results:

SELECT Cust_ID, Cust_Details.exist
(‘Customer[@City=’NJ”]’) AS ‘True’ FROM CustDetails

Retrieve XML data from DataSet

How to Define Methods with Behavior: Java

Objects have behavior that is implemented by its methods. Other objects can ask an object to do something by invoking its methods. This section tells you everything you need to know about writing methods for your Java classes.

In Java, you define a class’s methods in the body of the class for which the method implements some behavior. Typically, you declare a class’s methods after its variables in the class body although this is not required.

Implementing Methods

Similar to a class implementation, a method implementation consists of two parts: the method declaration and the method body

methodDeclaration {
        methodBody
}

The Method Declaration

At minimum, a method declaration has a name and a return type indicating the data type of the value returned by the method:

returnType methodName( ) {
. . .
}

This method declaration is very basic. Methods have many other attributes such as arguments, access control, and so on.

Objects as Instances of Class

A class defines only a blueprint and its concrete version comes into effect only through objects that implement the functionality as defined by class. Recall our class example of City class. The objects created from this class will have two variables: name and population; and they will be able to represent cities. The object of City class will also have a method namely display ( ). An object of a class is typically named by a variable of the class type. For example, the program CityTrial in Example 4.7 declares the two variables metl1 and metro2 to be of type City, as follows;

City metro1, metro2;

This gives us variables of the class City, but so far there are no objects of the class. Objects are class value that are named by the variables. To obtain an object you must use the new operator to create a “new” object. For example, the following creates an object of the class City and names it with the variable metro1:

Metro = new city ( );

For now you need not go into details, simply note that the statement like:

Class-variable = new class-Name ( );

Creates a new object of the specified class and associates it with the class type variables. Since the class variable now names an object of the class, we will often refer to the class variable as an object of the class. (This is really the same usage as when we refer to an int variable n as “the integer n”, even though the integer is strictly speaking not n but the value of n.)

Unlike what we did in previous lines, the declaration of a class type variable and the creation of the object are more typically combined into one statement as follows:
City metro1 = new City( );

To instantiate an object, Java uses the keyword new.

Declare member variables in JAVA

How to Declare Member Variables in Classes: JAVA

A class’s state is represented by its member variables. You declare a class’s member variables in the body of the class. Typically, programmer declare a class’s variables before you declare its methods, although this is not required.

classDeclaration {
        member variable declarations
        method declarations
}

To declare variables that are members of a class, the declarations must be within the class body, but not within the body of a method. Variables declared within the body of a method are local to that method i.e., available and accessible only inside the method.

Types

• Class variable (static variable): A data member that is declared once for a class. All objects of the class type, share these data members, as there is single copy of them available in memory. The class variables are declared by adding keyword static in front of a variable declaration.
• Instance variable: A data member that is created for every object of the class. For example, if there are 10 objects of a class type, there would be 10 copies of instance variables, one each for an object.

Consider the following code.

Public class sample {
        int anInt; // instance variable
        float aFloat; // instance variable
        static float anotherFloat; // class variable
};

Suppose there are five objects created for class type Sample. Then there would be five copies of variables anInt and aFloat but there would be one copy of variable anotherFloat which all five objects can share.

Notice that class variables are declared by adding a keyword static before the variable declaration. Keyword static in the variable declaration makes it class variable.

Method are similar: your classes can have instance methods and class methods. Instance methods operate on the current object’s instance variables but also have access to the class variables. Class methods (also called static methods), on the other hand, cannot access the instance variables declared within the class (unless they create a new object and access them through the object). Also, class methods can be invoked on the class, you don’t need an instance to call a class method.

A class method can be invoked by:

• Just its name within its own class e.g., check( ).
• Class-name.method-name outside its class e.g., Sample.check ( )

In above two lines, we assume that method check( ) is a static method in class Sample.

Implement Object-Oriented in JAVA

Fix A table control was not found in the template for data list

<!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></title>

    <style type="text/css">

        #form1

        {

            width: 162px;

            height: 384px;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

   <asp:DataList ID="DataList1" runat="server" DataKeyField="Sno" 

        DataSourceID="SqlDataSource1" CellPadding="0" ExtractTemplateRows="True">

        <ItemTemplate>

            Sno:

            <asp:Label ID="SnoLabel" runat="server" Text='<%# Eval("Sno") %>' />

            <br />

            name:

            <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' />

            <br />

            address:

            <asp:Label ID="addressLabel" runat="server" Text='<%# Eval("address") %>' />

            <br />

<br />

        </ItemTemplate>

    </asp:DataList>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

        DeleteCommand="DELETE FROM [User] WHERE [Sno] = @Sno" 

        InsertCommand="INSERT INTO [User] ([name], [address]) VALUES (@name, @address)" 

         SelectCommand="SELECT * FROM [User]" 

        UpdateCommand="UPDATE [User] SET [name] = @name, [address] = @address WHERE [Sno] = @Sno">

        <DeleteParameters>

            <asp:Parameter Name="Sno" Type="Int32" />

        </DeleteParameters>

        <InsertParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

        </InsertParameters>

        <UpdateParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

            <asp:Parameter Name="Sno" Type="Int32" />

        </UpdateParameters>

    </asp:SqlDataSource>

    <div>

    

    </div>

    </form>

</body>

</html>

solved code

<!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></title>

    <style type="text/css">

        #form1

        {

            width: 162px;

            height: 384px;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

  <asp:DataList ID="DataList1" runat="server" DataKeyField="Sno" 

        DataSourceID="SqlDataSource1" CellPadding="0" ExtractTemplateRows="False">

        <ItemTemplate>

            Sno:

            <asp:Label ID="SnoLabel" runat="server" Text='<%# Eval("Sno") %>' />

            <br />

            name:

            <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' />

            <br />

            address:

            <asp:Label ID="addressLabel" runat="server" Text='<%# Eval("address") %>' />

            <br />

<br />

        </ItemTemplate>

    </asp:DataList>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

        DeleteCommand="DELETE FROM [User] WHERE [Sno] = @Sno" 

        InsertCommand="INSERT INTO [User] ([name], [address]) VALUES (@name, @address)" 

         SelectCommand="SELECT * FROM [User]" 

        UpdateCommand="UPDATE [User] SET [name] = @name, [address] = @address WHERE [Sno] = @Sno">

        <DeleteParameters>

            <asp:Parameter Name="Sno" Type="Int32" />

        </DeleteParameters>

        <InsertParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

        </InsertParameters>

        <UpdateParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

            <asp:Parameter Name="Sno" Type="Int32" />

        </UpdateParameters>

    </asp:SqlDataSource>

    <div>

    

    </div>

    </form>

</body>

</html>

Solution of the problem

set ExtractTemplateRows="False" in data list, like

<asp:DataList ID="DataList1" runat="server" DataKeyField="Sno" 

        DataSourceID="SqlDataSource1" CellPadding="0" ExtractTemplateRows="False">

How to change Gridview header's style like border style and color

Initial Steps for viewers
Step-1 : Open Visual Studio IDE
Step-2 : Add New Webform into your project
Step-3 : Open Design page for adding control onit.
Step-4 : You can change  Header's BorderStyle and BorderColor of it .

<%@ 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 runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 

        AutoGenerateColumns="False" DataKeyNames="Sno" DataSourceID="SqlDataSource1">

        <Columns>

            <asp:BoundField DataField="Sno" HeaderText="Sno" InsertVisible="False" 

                ReadOnly="True" SortExpression="Sno" />

            <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />

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

                SortExpression="address" />

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

                SortExpression="contactno" />

        </Columns>

 <HeaderStyle BorderColor="Yellow" BorderStyle="Outset" />

    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

        DeleteCommand="DELETE FROM [userdataTable] WHERE [Sno] = @Sno" 

        InsertCommand="INSERT INTO [userdataTable] ([name], [address], [contactno]) VALUES (@name, @address, @contactno)" 

        SelectCommand="SELECT * FROM [userdataTable]" 

        UpdateCommand="UPDATE [userdataTable] SET [name] = @name, [address] = @address, [contactno] = @contactno WHERE [Sno] = @Sno">

        <DeleteParameters>

            <asp:Parameter Name="Sno" Type="Int32" />

        </DeleteParameters>

        <InsertParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

            <asp:Parameter Name="contactno" Type="Int32" />

        </InsertParameters>

        <UpdateParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

            <asp:Parameter Name="contactno" Type="Int32" />

            <asp:Parameter Name="Sno" Type="Int32" />

        </UpdateParameters>

    </asp:SqlDataSource>

    <div>

    

    </div>

    </form>

</body>

</html>

code output:

How to change back color of data list

<!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></title>

    <style type="text/css">

        #form1

        {

            width: 162px;

            height: 384px;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

<asp:DataList ID="DataList1" runat="server" DataKeyField="Sno" 

        DataSourceID="SqlDataSource1" BackColor="Red">

        <ItemTemplate>

            Sno:

            <asp:Label ID="SnoLabel" runat="server" Text='<%# Eval("Sno") %>' />

            <br />

            name:

            <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' />

            <br />

            address:

            <asp:Label ID="addressLabel" runat="server" Text='<%# Eval("address") %>' />

            <br />

<br />

        </ItemTemplate>

    </asp:DataList>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 

        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 

        DeleteCommand="DELETE FROM [User] WHERE [Sno] = @Sno" 

        InsertCommand="INSERT INTO [User] ([name], [address]) VALUES (@name, @address)" 

         SelectCommand="SELECT * FROM [User]" 

        UpdateCommand="UPDATE [User] SET [name] = @name, [address] = @address WHERE [Sno] = @Sno">

        <DeleteParameters>

            <asp:Parameter Name="Sno" Type="Int32" />

        </DeleteParameters>

        <InsertParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

        </InsertParameters>

        <UpdateParameters>

            <asp:Parameter Name="name" Type="String" />

            <asp:Parameter Name="address" Type="String" />

            <asp:Parameter Name="Sno" Type="Int32" />

        </UpdateParameters>

    </asp:SqlDataSource>

    <div>

    

    </div>

    </form>

</body>

</html>

How to find minute difference between two times

If you want to get minute difference between two times then you should use TimeSpan Structure for that. First to create two DateTime object. Now, create difference between two dates using TimeSpan structure also get minutes using TotalMinutes property of TimeSpan structure.

Source Code

<form id="form1"
     runat="server">
    <asp:Button ID="Button1"
    runat="server"
    BackColor="#FF33CC"
    onclick="Button1_Click"
    Text="Click"
     Width="73px" />
    <div>  
    <asp:Label ID="Label1"
     runat="server"
     BackColor="Yellow"
     Text="Label"></asp:Label>  
    </div>
    </form>

Code Behind

protected void Button1_Click(object sender, EventArgs e)
    {
        {            
            DateTime now = DateTime.Now;
            Label1.Text = "now : " + now.ToString();
           DateTime dateAfter4Hours = now.AddHours(4);
            TimeSpan TS = dateAfter4Hours - now;            
            int minutes = (int)TS.TotalMinutes;
            Label1.Text += "<br ><br />after four hours: ";
            Label1.Text += dateAfter4Hours.ToString();
            Label1.Text += "<br ><br />This is difference between to datetime object in minutes  : ";
            Label1.Text += minutes;
        }

    }
Code Generate the following output