Sunday, April 18, 2010

Difference between Rollup and Cube

You can use the CUBE and ROLLUP operators to generate summary information in a query. A CUBE operator generates a result set that shows the aggregates for all combinations of values in the selected columns. A ROLLUP operator generates a result set showing the aggregates for a hierarchy of values in the selected columns. Both the CUBE and ROLLUP operators return data in relational form.

The CUBE operator generates a multidimensional cube result set. A multidimensional cube is an expansion of fact data, or the data that records individual events.
This expansion is based on columns that the user wants to analyze. These columns are called dimensions. A cube is a result set that contains a cross tabulation of all the possible combinations of the dimensions.

The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns by using the WITH CUBE keywords.
The result set contains all possible combinations of the values in the dimension columns, together with the aggregate values from the underlying rows that match that combination of dimension values.

The ROLLUP operator is useful in generating reports that contain aggregate values. The ROLLUP operator generates a result set that is similar to the result set generated by the CUBE operator.

However, the difference between the CUBE and ROLLUP operator is that the CUBE generates a result set that shows the aggregates for all combinations of values in the selected columns. By contrast, the ROLLUP operator returns only the specific result set.
The ROLLUP operator generates a result set that shows the aggregates for a hierarchy of values in the selected columns. Also, the ROLLUP operator provides only one level of summarization, for example, the cumulative running sum in a table.

Thursday, March 25, 2010

Difference Between ASP.NET Server Controls,HTML Server Controls and HTML Intrinsic Controls

ASP.NET Server Controls
Advantages:

1. ASP .NET Server Controls can however detect the target browser's capabilities and render themselves accordingly. No issues for compatibility issues of Browsers i.e page that might be used by both HTML 3.2 and HTML 4.0 browsers code to be written by you.
2. Newer set of controls that can be used in the same manner as any HTMl control like Calender controls. (No need of Activex Control for doing this which would then bring up issues of Browser compatibility).
3. Processing would be done at the server side. In built functionality to check for few values(with Validation controls) so no need to choose between scripting language which would be incompatible with few browsers.
4. ASP .NET Server Controls have an object model different from the traditional HTML and even provide a set of properties and methods that can change the outlook and behavior of the controls.
5. ASP .NET Server Controls have higher level of abstraction. An output of an ASP .NET server control can be the result of many HTML tags that combine together to produce that control and its events.


Disadvantages:

1. The control of the code is inbuilt with the web server controls so you have no much of direct control on these controls
2. Migration of ASP to any ASP.NET application is difficult. Its equivalent to rewriting your new application
HTML Server Controls
Advantages:

1. The HTML Server Controls follow the HTML-centric object model. Model similar to HTML
2. Here the controls can be made to interact with Client side scripting. Processing would be done at client as well as server depending on your code.
3. Migration of the ASP project thought not very easy can be done by giving each intrinsic HTML control a runat = server to make it HTML Server side control.
4. The HTML Server Controls have no mechanism of identifying the capabilities of the client browser accessing the current page.
5. A HTML Server Control has similar abstraction with its corresponding HTML tag and offers no abstraction.

Disadvantages:
1. You would need to code for the browser compatibility.
HTML Intrinsic Controls
Advantages:
1. Model similar to HTML
2. Here the controls can be made to interact with Client side scripting

Disadvantages:
1. You would need to code for the browser compatibilit

Tuesday, March 2, 2010

What is Join and its types in SQL Server

Join:

Joins in SQL Server allows the retrieval of data records from one or more tables having some relation between them. Logical operators can also be used to drill down the number of records to get the desired output from sql join queries.

Types:

1. Inner Join
2. Outer Join
o Left Outer Join
o Right Outer Join
o Full Outer Join
3. Cross Join

I) Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.
Natural join query example:
SELECT C.*, P.PRODUCTID, P.PRODUCTNAME FROM CATEGORIES C
INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID
This natural join query will return all the columns of categories table and prodcutId and productName from products table.
Equi Join: Equi Join returns all the columns from both tables and filters the records satisfying the matching condition specified in Join “ON” statement of sql inner join query.

SQL Inner Equi Join Example:

USE NORTHWIND
SELECT * FROM CATEGORIES C INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID


Result will display the following columns:
CategoryID, CategoryName, Description, Picture, ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued

Above equi join sql query will display the categoryId two times in a row because both the tables have categoryId column. You can convert the result into natural join by elimination the identical columns and unnecessary columns.
Diffrence between natural join and equi join
natural join is basically a form of equi join where one of the join fields is projected out. i.e. it avoids repeatition of the join column.

II) Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.
o Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple.


o Right Outer Join: Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table.



o Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.


III) Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table.

Monday, March 1, 2010

what is diffgram in asp.net

DiffGram:

The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. A good use is reading database data to an XML file to be sent to a Web Service.
ADO.NET introduced the DataSet class to support the disconnected, distributed data-access scenarios. With DataSet, the data retrieved from the database is cached in-memory, in addition to the constraints and relationships among the tables. When the ADO.NET DataSet is serialized as XML (for example, when returning a DataSet from an ASP.NET XML Web service method), the XML format used for DataSet serialization is known as DiffGram. Like Updategrams, DiffGrams also contains the tags that specify the original and new state of data. SQLXML and .NET Managed classes can be used to execute DiffGrams to perform the database updates, however there are many things that are supported by Updategrams and not by DiffGrams (ability to pass parameters being one example).


DiffGrams and DataSet

There are occasions when you want to compare the original data with the current data to get the changes made to the original data. One of the common example is saving data on Web Forms applications. When working with Web based data driven applications, you read data using a DataSet, make some changes to the data and sends data back to the database to save final data. Sending entire DataSet may be a costly affair specially when there are thousands of records in a DataSet. In this scenario, the best practice is to find out the updated rows of a DataSet and send only updated rows back to the database instead of the entire DataSet. This is where the DiffGrams are useful.

Note: Do you remember GetChanges method of DataSet? This method returns the rows that have been modified in the current version in a form of DataSet. This is how a DataSet knows the modified rows.

A DiffGram is an XML format that is used to identify current and original versions of data elements. Since the DataSet uses XML format to store and transfer data, it also use DiffGrams to keep track of the original data and the current data. When a DataSet is written as a DiffGram, not only a DiffGram stores original and current data, it also stores row versions, error information, and their orders.

DiffGram XML Format

The XML format for a DiffGram has three parts - data instance, diffgram before and diffgram errors. The tag represents the data instance part of a diffgram, which represents the current data. The diffgram before is represented by the tag, which represents the original version of the data. The tag represents the diffgram errors part, which stores the errors and related information. The diffgram itself is represented by tag . The XML listed in Listing 1 represents the skeleton of a DiffGram.


xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">








Listing 1. A DiffGram format

The sections only store the changed rows and the section only stores the rows that had errors. Each row in a DiffGram is identified with an id and these three sections communicate through this id. For example, if id of a row is "Id1" and it has been modified and had errors,

Besides above discussed three sections, a DiffGram uses other elements. These are described in Table 1.

Table 1 describes the DiffGram elements that are defined in the DiffGram namespace urn:schemas-microsoft-com:xml-diffgram-v1.

Element Description
id
DiffGram id. Normally in the format of [TableName][RowIdentifier]. For example: .
parentId
Parent row of the current row. Normally in the format of [TableName][RowIdentifier]. For example: .
hasChanges Identifies a row in the block as modified. The hasChanges can have one of the three values - inserted, modified, or descent. Value inserted means an Added row, modified means modified row, and descent means children of a parent row have been modified.
hasErrors
Identifies a row in the block with a RowError. The error element is placed in the block.

Error
Contains the text of the RowError for a particular element in the block.


There are two more elements a DataSet generated DiffGrams can have and these elements are RowOrder and Hidden. The RowOrder is the row order of the original data and identifies the index of a row in a particular DataTable. The Hidden identifies a column as having a ColumnMapping property set to MappingType.Hidden.

Now let's see an example of DiffGrams. The code listed in Listing 1 reads data from Employees tables and write in an XML document in DiffGram format.

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb"
Dim sql As String = "SELECT EmployeeID, FirstName, LastName, Title FROM Employees"
Dim conn As OleDbConnection = Nothing
Dim ds As DataSet = Nothing
' Create and open connection
conn = New OleDbConnection(connectionString)
If conn.State <> ConnectionState.Open Then
conn.Open()
End If ' Create a data adapter
Dim adapter As New OleDbDataAdapter(sql, conn)
' Create and fill a DataSet
ds = New DataSet("TempDtSet")
adapter.Fill(ds, "DtSet")
' Write XML in DiffGram format
ds.WriteXml("DiffGramFile.xml", XmlWriteMode.DiffGram)
' Close connection
If conn.State = ConnectionState.Open Then
conn.Close()
End If

Bubble Events in ASP.net

Event Bubbling is nothing but events raised by child controls is handled by the parent control. Example: Suppose consider datagrid as parent control in which there are several child controls.There can be a column of link buttons right.Each link button has click event.Instead of writing event routine for each link button write one routine for parent which will handlde the click events of the child link button events.

protected override bool OnBubbleEvent(object source, EventArgs e) {
if (e is CommandEventArgs) {
// Adds information about an Item to the
// CommandEvent.
TemplatedListCommandEventArgs args =
new TemplatedListCommandEventArgs(this, source, (CommandEventArgs)e);
RaiseBubbleEvent(this, args);
return true;
}
return false;
}

Refer: http://msdn.microsoft.com/en-us/library/aa719644(VS.71).aspx

Thursday, February 25, 2010

Difference between outer join and inner join

Inner join:


We use this when we compare two colums from two different table .Based on equality or non equality, we retrieve the rows matched.
eg.

Select emp.empid , order.orderid
from emp Innerjoin order
on Emp.empid=order.empid

This example gives all the rows from emp,order tables where the empid's in both the tables are same.





Outer Join:

There are three types of outer joins namely:
Left Outer Join---For retreiving all the columns from the first table irrespective of the column match.
Right Outer Join---For retreiving all the columns from the second table irrespective of the column match
Full Outer Join---For retreiving all the columns from both the tables irrespective of column match.

Eg.

If we have two tables named stud1,stud2 with the following data

Stud1: id Name stud2: id Name
1 xxx 1 aaa
2 yyy 2 bbb
3 zzz 4 ccc
4 www 6 ddd
When we use Left Outer Join we get the output as:
1 aaa
2 bbb
3
4 ccc


When we use Right Outer Join we get the output as:
1 aaa
2 bbb
4 ccc
ddd


When we use Full Outer Join we get the output as:
1 aaa
2 bbb
3
4 ccc
ddd

JavaScript Object Notation (JSON)

JavaScript Object Notation (JSON)
To allow for a more efficient transfer of data and classes between web applications and
web services, ASP.NET AJAX supports the JavaScript Object Notation (JSON) format. It is
lighter weight than XML (Extensible Markup Language)/SOAP (Simple Object Access
Protocol), and delivers a more consistent experience because of the implementation
differences of XML/SOAP by the various browsers.
JSON is a text-based data-interchange format that represents data as a set of ordered
name/value pairs. As an example, take a look at the following class definition, which
stores a person’s name and age:

public class MyDetails
{
string FirstName;
string LastName;
int Age;

}

A two-element array of this object is represented as follows:
{ MyDetails : [ { “FirstName” : “Landon”, “LastName” : “Donovan”, “Age” : “22”}
{ “FirstName” : “John”, “LastName” : “Grieb”, “Age” : “46”}
]
}
 
Locations of visitors to this page