payment gateway

Email App

Name:


Email:

Cool Features in SQL Server 2008

Cool Features in SQL Server 2008 The PIVOT and UNPIVOT Operators Let's face it—users usually want to see data in tabular format, which is a bit of a challenge given that data in SQL Server is most often stored in a highly relational form. PIVOT is a T-SQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional crosstab query. Using PIVOT is easy. In your SELECT statement, you specify the values you want to pivot on. The following example in the AdventureWorks2008 database uses the order years (calculated using the DatePart function) as the columns. The FROM clause looks normal except for the PIVOT statement. This statement creates the value you want to show in the rows of the newly created columns. This example uses the aggregate SUM of TotalDue (a calculated field in the FROM clause). Then we use the FOR operator to list the values we want to pivot on in the OrderYear column. The example is shown in Listing 2-11. Listing 2-11. Creating tabular results with the PIVOT operator SELECT CustomerID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004 FROM ( SELECT CustomerID, DATEPART(yyyy, OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader ) AS piv PIVOT ( SUM(TotalDue) FOR OrderYear IN([2001], [2002], [2003], [2004]) ) AS child ORDER BY CustomerID Here are the results: CustomerID Y2001 Y2002 Y2003 Y2004 ----------- ---------- ----------- ------------ ------------ 1 40732.6067 72366.1284 NULL NULL 2 NULL1 5653.6715 12118.0275 4962.2705 3 39752.8421 168393.7021 219434.4265 51925.3549 4 NULL 263025.3113 373484.299 143525.6018 5 NULL 33370.6901 60206.9999 20641.1106 6 NULL NULL 668.4861 2979.3473 7 NULL 6651.036 3718.7804 NULL 8 NULL NULL 19439.2466 10900.0347 9 NULL 320.6283 11401.5975 5282.8652 10 NULL 96701.7401 291472.2172 204525.9634 ... That's all there is to it. Of course, this example is simplified to show you the concept; other, more sophisticated, aggregates are possible, and you can even use CTEs in the FROM clause. In any case, using PIVOT is simple. Using UNPIVOT You can use the UNPIVOT operator to normalize data that is already pivoted. For example, suppose you obtain pivoted data that shows, for each vendor, the number of orders placed by each employee. The code in Listing 2-12 creates such a table. Listing 2-12. Example table containing pivoted data CREATE TABLE VendorEmployee (VendorID int, Emp1Orders int, Emp2Orders int, Emp3Orders int, Emp4Orders int, Emp5Orders int) GO INSERT INTO VendorEmployee VALUES(1, 4, 3, 5, 4, 4) INSERT INTO VendorEmployee VALUES(2, 4, 1, 5, 5, 5) INSERT INTO VendorEmployee VALUES(3, 4, 3, 5, 4, 4) INSERT INTO VendorEmployee VALUES(4, 4, 2, 5, 4, 4) INSERT INTO VendorEmployee VALUES(5, 5, 1, 5, 5, 5) Our table looks like this: VendorID Emp1Orders Emp2Orders Emp3Orders Emp4Orders Emp5Orders ----------- ----------- ----------- ----------- ----------- ----------- 1 4 3 5 4 4 2 4 1 5 5 5 3 4 3 5 4 4 4 4 2 5 4 4 5 5 1 5 5 5 You might want to unpivot the data to display columns for vendor ID, employee, and number of orders. Listing 2-13 shows how to use the UNPIVOT operator to achieve this goal. Listing 2-13. Using the UNPIVOT operator SELECT VendorId, Employee, Orders AS NumberOfOrders FROM (SELECT VendorId, Emp1Orders, Emp2Orders, Emp3Orders, Emp4Orders, Emp5Orders FROM VendorEmployee ) AS p UNPIVOT ( Orders FOR Employee IN (Emp1Orders, Emp2Orders, Emp3Orders, Emp4Orders, Emp5Orders) ) AS unpvt Here are the results: VendorID Employee NumberOfOrders --------- ------------ ---------------------- 1 Emp1Orders 4 1 Emp2Orders 3 1 Emp3Orders 5 1 Emp4Orders 4 1 Emp5Orders 4 2 Emp1Orders 4 ... Dynamically Pivoting Columns The problem with PIVOT is the same problem with CASE and other methods: you have to specify the columns. Consider the code in Listing 2-14. Listing 2-14. Statically driven PIVOT SELECT * FROM (SELECT CustomerID, YEAR(OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader) AS header PIVOT ( SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004]) ) AS piv The results show us a nice crosstab query with the years displayed as columns: CustomerID 2002 2003 2004 ----------- ---------- ---------- ----------- 14324 NULL 2264.2536 3394.9247 22814 NULL 5.514 NULL 11407 NULL 59.659 NULL 28387 NULL NULL 645.2869 19897 NULL NULL 659.6408 15675 2699.9018 2682.9953 2580.1529 24165 NULL 2699.9018 666.8565 ... Because this data goes only up to 2004, what happens when you add 2005 to the data? Do you want to go into all your queries and add the current year to the IN clause? We can accommodate new years in the data by dynamically building the IN clause and then pro-grammatically writing the entire SQL statement. Once you have dynamically written the SQL statement, you can execute it using sp_executesql, as shown in Listing 2-15. Since all we have to do is generate dynamically the IN clause, creating a dynamic PIVOT in SQL Server is much easier than creating a dynamic CASE statement. The results are exactly the same as those shown following Listing 2-14, except that as new yearly data is added to the table, the query dynamically adds the column for it. Remember that your reporting engine will most likely not accommodate the new dynamic columns, but data-bound controls will. Listing 2-15. Dynamically driven PIVOT DECLARE @tblOrderDate AS TABLE(y int NOT NULL PRIMARY KEY) INSERT INTO @tblOrderDate SELECT DISTINCT YEAR(OrderDate) FROM Sales.SalesOrderHeader DECLARE @cols AS nvarchar(max) DECLARE @years AS int SET @years = (SELECT MIN(y) FROM @tblOrderDate) SET @cols = N'' WHILE @years IS NOT NULL BEGIN SET @cols = @cols + N',[' + CAST(@years AS nvarchar(max)) + N']' SET @years = (SELECT MIN(y) FROM @tblOrderDate WHERE y > @years) END SET @cols = SUBSTRING(@cols, 2, LEN(@cols)) DECLARE @sql AS nvarchar(max) SET @sql = ' SELECT * FROM (SELECT CustomerId, YEAR(OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader ) AS a PIVOT ( SUM(TotalDue) FOR OrderYear IN(' + @cols + N') ) AS b ' PRINT @sql -- for debugging EXEC sp_executesql @sql You can accomplish the same results using the newer CTE syntax instead of using the table variable, as shown in Listing 2-16. Listing 2-16. Dynamically driven PIVOT using a CTE DECLARE @cols AS nvarchar(MAX) WITH YearsCTE AS (SELECT DISTINCT YEAR(OrderDate) as [Year] FROM Sales.SalesOrderHeader) SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([YEAR] AS nvarchar(10)) + ']' FROM YearsCTE ORDER BY [YEAR] -- Construct the full T-SQL statement and execute it dynamically. DECLARE @sql AS nvarchar(MAX) SET @sql = ' SELECT * FROM (SELECT CustomerId, YEAR(OrderDate) AS OrderYear, TotalDue FROM Sales.SalesOrderHeader ) AS a PIVOT ( SUM(TotalDue) FOR OrderYear IN(' + @cols + N') ) AS b ' PRINT @sql -- for debugging EXEC sp_executesql @sql The APPLY Operator APPLY is an operator that you specify in the FROM clause of a query. It enables you to invoke a table-valued function (TVF) for each row of an outer table. The flexibility of APPLY is evident when you use the outer table's columns as your function's arguments. The APPLY operator has two forms: CROSS APPLY and OUTER APPLY. CROSS APPLY doesn't return the outer table's row if the TVF returns an empty set for it; the OUTER APPLY returns a row with NULL values instead of the function's columns. To see how APPLY works, we'll first create a TVF that returns a table. Listing 2-17 shows a simple function that returns as a table the top n rows for a customer from the SalesOrderHeader table. Listing 2-17. Returning a table CREATE FUNCTION fnGetCustomerOrders(@CustomerID int, @TopRecords bigint) RETURNS TABLE AS RETURN SELECT TOP (@TopRecords) * FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID ORDER BY OrderDate DESC After creating the fnGetCustomerOrders TVF, we call it from the query, as shown in Listing 2-18. Listing 2-18. Executing a query with APPLY SELECT * FROM Sales.Customer cust CROSS APPLY fnGetCustomerOrders(CustomerID, 100) This query returns all the records from the Customers table and then, as additional fields, the records from the Orders table (by way of the fnGetCustomerOrders function) that match for the customer ID because that's what's being passed in dynamically as the first argument to fnGetCustomerOrders. Because we passed the value 100 for the second parameter, rows for up to the first 100 orders per customer are generated and returned by this query.

Exception Handling in Transactions SQL Server offers major improvements in error handling inside T-SQL transactions. As of SQL Server 2005, you can catch T-SQL and transaction abort errors using the TRY/CATCH model without any loss of the transaction context. The only types of errors that the TRY/CATCH construct can't handle are those that cause the termination of your session (usually errors with severity 21 and above, such as hardware errors). The syntax is shown here: BEGIN TRY --sql statements END TRY BEGIN CATCH --sql statements for catching your errors END CATCH If an error within an explicit transaction occurs inside a TRY block, control is passed to the CATCH block that immediately follows. If no error occurs, the CATCH block is completely skipped. You can investigate the type of error that was raised and react accordingly. To do so, you can use the ERROR_xxx functions to return error information in the CATCH block, as shown in Listing 2-35. Listing 2-35. T-SQL exception handling example BEGIN TRY SELECT 5/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrNumber, ERROR_SEVERITY() AS ErrSeverity, ERROR_STATE() AS ErrState, ERROR_PROCEDURE() AS ErrProc, ERROR_LINE() AS ErrLine, ERROR_MESSAGE() AS ErrMessage END CATCH You can examine the value reported by any of the various ERROR_xxx functions to decide what to do with the control flow of your procedure and whether to abort any transactions. In our example in Listing 2-35, which attempts to divide by zero, here are the values returned by the error functions. (The ERROR_PROCEDURE function returns NULL in this example because the exception did not occur within a stored procedure.) ErrNumber ErrSeverity ErrState ErrProc ErrLine ErrMessage ---------- ------------ ---------- -------- -------- ---------------------------------- 8134 16 1 NULL 2 Divide by zero error encountered. When you experience a transaction abort error inside a transaction located in the TRY block, control is passed to the CATCH block. The transaction then enters a failed state in which locks are not released and persisted work is not reversed until you explicitly issue a ROLLBACK statement. You're not allowed to initiate any activity that requires opening an implicit or explicit transaction until you issue a ROLLBACK. Certain types of errors are not detected by the TRY/CATCH block, and you end up with an unhandled exception even though the error occurred inside your TRY block. If this happens, the CATCH block is not executed. This is because CATCH blocks are invoked by errors that take place in actual executing code, not by compile or syntax errors. Two examples of such errors are syntax errors and statement-level recompile errors (for example, selecting from a nonexistent table). These errors are not caught at the same execution level as the TRY block, but at the lower level of execution—when you execute dynamic SQL or when you call a stored procedure from the TRY block. For example, if you have a syntax error inside a TRY block, you get a compile error and your CATCH block will not run, as shown here: -- Syntax error doesn't get caught BEGIN TRY SELECT * * FROM Customer END TRY BEGIN CATCH PRINT 'Error' END CATCH The result is an error from SQL Server, not from your CATCH block, as follows: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '*'. Statement-level recompilation errors also don't get caught by CATCH blocks. For example, using a nonexistent object in a SELECT statement in the TRY block forces an error from SQL Server, but your CATCH block will not execute, as shown here: -- Statement level recompilation doesn't get caught BEGIN TRY SELECT * FROM NonExistentTable END TRY BEGIN CATCH PRINT 'Error' END CATCH The result is an error from SQL Server, as follows: Msg 208, Level 16, State 1, Line 3 Invalid object name 'NonExistentTable'. When you use dynamic SQL or a stored procedure, these types of compile errors do get caught because they are part of the current level of execution. Each of the SQL blocks shown in Listing 2-36 will execute the CATCH block. Listing 2-36. Catching syntax and recompilation errors in dynamic SQL and stored procedure calls with exception handlers -- Dynamic SQL Example BEGIN TRY EXEC sp_executesql 'SELECT * * FROM Customer' END TRY BEGIN CATCH PRINT 'Error' END CATCH GO -- Stored Procedure Example CREATE PROCEDURE MyErrorProc AS SELECT * FROM NonExistentTable GO BEGIN TRY EXEC MyErrorProc END TRY BEGIN CATCH PRINT 'Error' END CATCH

Facebook sells stake in business

Facebook has sold a 1.96% stake for $200m (£126m) to a Russian internet firm, a move that values the social networking website at $10bn.

http://news.bbc.co.uk/2/hi/business/8069102.stm

Facebook boss Mark Zuckerberg said he had been impressed by Digital Sky Technology's (DST) "impressive growth and financial achievements".

DST has investments in a number of internet firms across Russia and Eastern Europe.

US-based Facebook has more than 200 million global members.

'Ongoing success'

Facebook said DST would not be represented on its board or hold special observer rights.

"This investment demonstrates Facebook's ongoing success at creating a global network for people to share and connect," added Mr Zuckerberg, Facebook's chief executive.

"A number of firms approached us, but DST stood out because of the global perspective they bring."

DST's internet businesses account for more than 70% of all page views on Russian language websites.

It has investments in sites including Mail.ru, Forticom and vKontakte.

The deal comes two years after Facebook sold a 1.6% stake to Microsoft for $240m.

http://news.bbc.co.uk/2/hi/business/8069102.stm

New Search Engine

Guys Go and look for the Demo it's looks like cool search Engine..Maps,Photos,Locations,Atmosphere
it's www.bing.com see the Demo

Design Patterns in C#

There are seven patterns that make up the structural group, each with the role
of building flexibility, longevity, and security into computer software. The names of
the patterns are important, so I’ll introduce them immediately. They are:
• Decorator
• Proxy
• Bridge
• Composite
• Flyweight
• Adapter
• Façade
Structural patterns are concerned with how classes and objects are composed to form
larger structures. Of the many purposes of the seven structural patterns, here are 10:

• Add new functionality dynamically to existing objects, or remove it (Decorator).
• Control access to an object (Proxy).
• Create expensive objects on demand (Proxy).
• Enable development of the interface and implementation of a component to proceed
independently (Bridge).
• Match otherwise incompatible interfaces (Adapter).
• Reduce the cost of working with large numbers of very small objects (Flyweight).
• Reorganize a system with many subsystems into identifiable layers with single
entry points (Façade).
• Select or switch implementations at runtime (Bridge).
• Simplify the interface to a complex subsystem (Façade).
• Treat single objects and composite objects in the same way (Composite).

Creating Control Arrays

http://www.acthompson.net/DotNet/ControlArrays.htm

Learn ASP.net

Refer Following link to learn about ASP.net 

http://www.asp.net/learn/

Shut Down Remote Computer

Click on Start--> select Run -Type

shutdown -i

press ok

select your options press ok

Remote login

Try This for remote login and you can save it as Batch file
mstsc /v:192.168.2.19 /f -console

Linq

Do you want ot know more about Linq Visit
http://msdn2.microsoft.com/en-us/vcsharp/aa336760.aspx#WhereSimple1

open a dial up internet connection from c# dotnet application

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Microsoft.Win32;
using System.Net.Sockets;
using System.Runtime.InteropServices;


//use above as Declarations

class Dial
{
const int INTERNET_CONNECTION_MODEM = 1;
const int INTERNET_CONNECTION_LAN = 2;
const int INTERNET_CONNECTION_PROXY = 4;
const int INTERNET_CONNECTION_MODEM_BUSY = 8;
public const int INTERNET_DIAL_UNATTENDED = 0x8000;
const int INTERNET_AUTODIAL_FORCE_ONLINE = 1;
const int INTERNET_AUTODIAL_FORCE_UNATTENDED = 2;
const int INTERNET_AUTODIAL_FAILIFSECURITYCHECK = 4;
[DllImport("wininet.dll")]
private extern static int InternetDial(IntPtr hwndParent, string lpszConnectoid, int dwFlags, out int lpdwConnection, int dwReserved);
private int m_mlConnection = 0; private bool m_isConnected = false;
public int DialUp()
{ m_mlConnection = 0;
//MessageBox.Show(m_mlConnection.ToString());
int zez = //!!!This is part of code that we are using for call InternetDial
InternetDial(IntPtr.Zero,"DefaultDialUp",INTERNET_DIAL_UNATTENDED,out m_mlConnection,0);
return 0;
}
}



//This is the Part of the Coding For calling the Dial Object


private void button1_Click(object sender, EventArgs e)
{
Dial obj = new Dial();
obj.DialUp();
}

AJAX Videos

Follwing Url Will Take U to the Ajax Control Tool kit Video Demo it's really good for the Beginners
http://www.asp.net/learn/ajax-videos/

How To use a Cookie in c#.Net

The Following Check the Browser Accepts the Cookies and Saves Userpref Cookie