Using SQL Server 2012 T-SQL New Features

Introduction :
SQL Server 2012 “Denali” is the next major release of Microsoft database server. There are some new features that are added to T-SQL to make common tasks much easier. I will show how to use some of the new features in this article.

Sequence :
Generating a sequence number, a.k.a. auto number, is a common task in an enterprise application. For a single table, you can specify identity field. But, if you want to have database wide sequential number, then you must devise something by yourself before SQL Server 2012. One solution to this problem is to create a table that has a numeric field can be used to store sequential number, then use SQL to increase it every time used one. In SQL Server 2012, we have a new solution - use Sequence.

Create Sequence

To create a Sequence in SQL Server 2012 is very simple. You can create it with SQL Server Management Studio or T-SQL.

1. Create Sequence with SQL Server Management Studio
In Object Explorer window of SQL Server Management Studio, there is a Sequences node under Database -> [Database Name] -> Programmability. You can right click on it to bring up context menu, and then choose New Sequence… to open the New Sequence window. In New Sequence window, you can define the new Sequence, like Sequence Name, Sequence schema, Data type, Precision, Start value, Increment by, etc. After entering all the required information, click OK to save it. The new Sequence will show up in
Sequences node.

2. Create Sequence with T-SQL

The following T-SQL script is used to create a new Sequence:

CREATE SEQUENCE DemoSequence
START WITH 1
INCREMENT BY 1;

Use Sequence

The new NEXT VALUE FOR T-SQL keyword is used to get the next sequential number from a Sequence.
SELECT VALUE FOR DemoSequence

One thing I want to mention in here is Sequence doesn’t support transaction, if you run this script:

BEGIN TRAN
SELECT NEXT VALUE FOR dbo.DemoSequence
ROLLBACK TRAN

You can see even the transaction is rolled back at the end. The NEXT VALUE FOR will still return the next sequential number. This behavior is consistent with identity field.

Page Data

A common situation for displaying page is how to display large amount of data in DataGrid. Earlier, the programmer usually used the paging feature of DataGrid to handle this situation. Therefore, by choosing a different page number, different set of data are displayed on the screen. However, how to retrieve data from database is multiplicity. A developer could:

1. Retrieve all data from database, and then let DataGrid to only display the current page data.
2. Retrieve the current page data from database by using temp table.
3. Retrieve the current page data from database by using ROW_NUMBER() function.

The SQL Server 2012 provided a new way to retrieve current page data from database.

SELECT *

FROM Customers
ORDER BY CustomerID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

The OFFSET keyword and FETCH NEXT keyword allow the developer to only retrieve certain range data from database. If you compare this script with ROW_NUMBER() function introduced in SQL Server 2008, you can see this script is shorter and more intuitive.

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS sequencenumber, *
FROM Customers) AS TempTable
WHERE sequencenumber > 10 and sequencenumber <= 20

Exception Handling :

SQL Server 2005 introduced TRY CATCH block to handle exception in T-SQL. The TRY CATCH block is similar to whatever in C# language except you need always raise a new exception after catching it. There is no way to simply re-throw it.

A sample of T-SQL script with exception handling in SQL Server 2005:

BEGIN TRY
BEGIN TRANSACTION – Start the transaction

-- Delete the Customer
DELETE FROM Customers
WHERE EmployeeID = ‘CACTU’

-- Commit the change
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- There is an error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
In SQL Server 2012, by using Throw keyword, the above script will be changed to this:
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

-- Delete the Customer
DELETE FROM Customers
WHERE EmployeeID = ‘CACTU’

-- Commit the change
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- There is an error
ROLLBACK TRANSACTION

-- Re throw the exception
THROW
END CATCH

Also, you can use Throw to replace RAISERROR function:

THROW 51000, ‘The record does not exist.’, 1;

Enhanced EXECUTE keyword

The EXECUTE keyword is used to execute a command string. The previous version SQL Server only has WITH RECOMPILE option to force new plan to be re-compiled. The SQL Server 2012 dramatically improved this part. The option part is like this right now.

[ WITH <execute_option> [ ,…n ] ]

<execute_option>::=
{
RECOMPILE
| { RESULT SETS UNDEFINED }
| { RESULT SETS NONE }
| { RESULT SETS ( <result_sets_definition> [,…n] ) }
}

<result_sets_definition> ::=
{
(
{ column_name
data_type
[ COLLATE collation_name ]
[ NULL | NOT NULL ] }
[,…n ]
)
| AS OBJECT
[ db_name . [ schema_name ] . | schema_name . ]
{table_name | view_name | table_valued_function_name }
| AS TYPE [ schema_name.]table_type_name
| AS FOR XML
}
The way to use the new added options is like this:
EXEC CustOrderDetail ‘2’
WITH RESULT SETS
(
(
ProductName1 varchar(100),
Unitprice1 varchar(100),
Quantity1 varchar(100),
Discount1 varchar(100),
ExtendedPrice1 varchar(100)
)
);

Get Metadata :

Application sometimes needs more insight of the SQL script result set. In the past, you needed to write a complicated script to query system tables or views, e.g. sys.objects, to get all the information. In SQL Server 2012, the new system stored procedure sp_describe_first_set makes the work trivial.
sp_describ_first_result_set @tsql = N’SELECT * FROM customers’

Summary

There are more T-SQL new features in the upcoming SQL Server 2012. Majority of them are designed to improve development efficiency and reduce development effort.

Cheers,
Original Post by Henry He,

Ajax Control Toolkit Seadragon in Asp.net 4.0

The Seadragon control can be used for interactively viewing images. Use your mouse to pan and zoom around the image


Seadragon Server PropertiesThe properties in italics are optional.
<ajaxToolkit:Seadragon ID="Seadragon2"
runat="server"
CssClass="seadragon"
SourceUrl="dzc_output.xml">
<ControlsCollection>
<ajaxToolkit:SeadragonControl runat="server" Anchor="TOP_RIGHT">
<asp:Menu runat="server" >
set menu style
<Items>
<asp:MenuItem Text="Menu" Value="Menu" />
<asp:MenuItem Text="Control" Value="Control" />
<asp:MenuItem Text="Over" Value="Over" />
<asp:MenuItem Text="Seadragon" Value="Seadragon" />
</Items>
</asp:Menu>
</ajaxToolkit:SeadragonControl>
</ControlsCollection>
<OverlaysCollection>
<ajaxToolkit:SeadragonScalableOverlay
runat="server"
Rect-Height="0.24"
Rect-Width="0.26"
CssClass="overlay"
Rect-Point-X="0.14"
Rect-Point-Y="0.06"
</ajaxToolkit:SeadragonScalableOverlay>
</OverlaysCollection>
</ajaxToolkit:Seadragon>

•animationTime - The amount of time in seconds that animations should last. Default is 1.5.


•blendTime - The amount of time in seconds that new tiles take to blend from transparent to opaque. Default is 0.5.

•alwaysBlend - Whether tiles should always blend in and out, not just when they're first loaded. Default is false.

•autoHideControls - Whether controls should get automatically hidden when the user's mouse is off the viewer and the image has stopped animating. Default is true.

•immediateRender - Whether the most appropriate tiles should always be rendered first, before any lower-res tiles are rendered. This loses the "sharpening" effect and instead creates a very visible "tiling" effect. Default is false.

•minZoomDimension - The minimum size (in screen pixels) of either dimension that can result from zooming out. Default is 16.

•maxZoomPixelRatio - The maximum pixel ratio (screen pixel to content pixel) that can result from zooming in. Default is 4.

•visibilityRatio - The minimum portion of the viewport that must show visible content in both dimensions. Default is 0.1.

•springStiffness - Determines how sharply the springs used for animations move. Default is 5.0.

•imageLoaderLimit - The maximum number of concurrent image downloads that can be performed by each viewer. Default is 2.

•clickTimeThreshold - The maximum number of milliseconds that can pass between a mousedown and a mouseup for the action to still be considered a "quick" click. Default is 200.

•clickDistThreshold - The maximum number of pixels the mouse can move between a mousedown and a mouseup for the action to still be considered a "quick" click. Default is 5.

•zoomPerClick - The factor by which images should zoom when clicked on. Default is 2.

•zoomPerSecond - The factor by which images should zoom over each second when the zoom buttons are held down. Default is 2.

•sourceUrl - The path for all UI images. This can be absolute or relative. If relative, it must be relative to the HTML page. A change to this value will only affect new viewers. Default is "img/".

•showNavigationControl - Whether navigation buttons should be shown.

Cheers,
By viswesh.

XML Workshop - Inserting elements and attributes to an XML document in SqlServer

Introduction
In this session we will learn how to insert an element or attribute into an XML document using the XML data type method: modify().
Sample XML Document
Here is the sample XML document that we will use for the examples in this session.
Example 1
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
Inserting a new element as the first child of a node
The following example inserts a new "Employee" element with value "Steve" as as the first child element of the root node.
Example 2
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert element Employee {"Steve"}
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The above example created a new XML element using a fixed value specified by a string literal. The next example shows how to create an XML element with value specified in a variable.
Example 3
DECLARE @x xml
SET @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

DECLARE @name VARCHAR(20)
SELECT @name = 'Steve'

SET @x.modify('
insert element Employee {sql:variable("@name")}
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
Both the examples given above, used the 'insert element' command to create a new element. Another way of achieving the same result is by supplying the whole XML fragment to be inserted as a string.
Example 4
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert <Employee>Steve</Employee>
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
If the text value of the element is stored in a variable, the following code will help.
Example 5
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

DECLARE @name VARCHAR(20)
SELECT @name = 'Steve'

SET @x.modify('
insert <Employee>{sql:variable("@name")}</Employee>
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
It is possible to insert the attribute values along with the elements declarations.
Example 6
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert <Employee Team="SQL server">Steve</Employee>
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL server">Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
Even if both the text value of the element as well as the value of the attribute are stored in variables, you still dont have anything to worry. The following code shows how to handle it.
Example 7
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

DECLARE @name VARCHAR(20), @team VARCHAR(20)
SELECT @name = 'Steve', @team = 'SQL Server'

SET @x.modify('
insert
<Employee Team="{sql:variable("@team")}">
{sql:variable("@name")}
</Employee>
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
Not always you would want to insert the new element as the first child. You can insert the new element as the last child by specifying the "as last" command instead of "as first".
Example 8
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert <Employee Team="SQL Server">Steve</Employee>
as last
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
<Employee Team="SQL Server">Steve</Employee>
</Employees>
*/
If you do not include "as first" and "as last" in your XQuery expression, SQL Server will assume "as last". The following code produces the same output as the above example.
Example 9
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert <Employee Team="SQL Server">Steve</Employee>
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
<Employee Team="SQL Server">Steve</Employee>
</Employees>
*/
We saw how to insert an element as first or last within a parent node. It is also possible to insert the child element at a specified position. the following example inserts an element as the second child, using the "insert after" command.
Example 10
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert <Employee Team="SQL Server">Steve</Employee>
after (Employees/Employee[1])[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
You could also use "insert before" to insert the new element above a specified child. The following example shows how to insert a new element as the second last child of a parent node.
Example 11
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
'

SET @x.modify('
insert <Employee Team="XML">Bob</Employee>
before (Employees/Employee[position()=last()])[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="XML">Bob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The position can also be specified by a variable. The following code shows how to insert an element at a position specified by a variable.
Example 12
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
'

DECLARE @pos INT
SELECT @pos = 3 -- insert as the third child

SET @x.modify('
insert <Employee Team="XML">Bob</Employee>
before (Employees/Employee[position()=sql:variable("@pos")])[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="XML">Bob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The following example shows how to insert a new element before "steve".
Example 13
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
'

SET @x.modify('
insert <Employee Team="XML">Bob</Employee>
before (Employees/Employee[. = "Steve"])[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="XML">Bob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The following example demonstrates how to insert an element right after another element having a text value specified by a variable.
Example 14
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
'

DECLARE @name VARCHAR(20)
SELECT @name = 'Steve' -- insert after steve

SET @x.modify('
insert <Employee Team="XML">Bob</Employee>
after (Employees/Employee[. = sql:variable("@name")])[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="XML">Bob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The examples we examined so far inserted relatively simple elements to the XML document. You could even use the "insert" command to add more complex XML fragments having child elements and or attributes.
Example 15
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert
<SpecialTeam name="SQL Azure">
<Employee id="1001">Steve</Employee>
<Employee id="1002">Mike</Employee>
</SpecialTeam>
as first
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<SpecialTeam name="SQL Azure">
<Employee id="1001">Steve</Employee>
<Employee id="1002">Mike</Employee>
</SpecialTeam>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
It is also possible to insert more than one element in a single operation. The "insert" command can accept a set (comma separated list) of XML elements and insert them at the specified position. Here is an example.
Example 16
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert (
<Employee Team="SQL server">Steve</Employee>,
<Employee Team="SQL server">Mike</Employee>
)
into (/Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
<Employee Team="SQL server">Steve</Employee>
<Employee Team="SQL server">Mike</Employee>
</Employees>
*/
In the previous examples, we saw how to insert new XML elements into an XML document using string literals. The next example shows how to creae a new XML element with the results of an XQuery expression. The following example creates a new XML element named "SQLGuys" and adds all the "SQL Server" people into the group.
Example 17
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="SQL Server">Mike</Employee>
</Employees>
'

SET @x.modify('
insert <SQLGuys>{
(/Employees/Employee)
}
</SQLGuys>
into (/Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="SQL Server">Mike</Employee>
<SQLGuys>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee Team="SQL Server">Mike</Employee>
</SQLGuys>
</Employees>
*/
Inserting Attributes
The syntax for adding attributes to an XML node is slightly different from that of elements. While working with attributes, remember that the position of an attribute is not significant in XML. An XML element can not have more than one attribute having the same name. Let us see a few examples that deal with inserting attributes into XML nodes.
The following example adds an attribute named "Team" to the XML node representing employee "Jacob".
Example 18
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>Steve</Employee>
<Employee>Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
'

SET @x.modify('
insert attribute Team {"SQL Server"}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
*/
The value of the attribute can be specified by a variable as given in the following example.
Example 19
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>Steve</Employee>
<Employee>Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
'

DECLARE @team VARCHAR(20)
SELECT @team = 'SQL Server'
SET @x.modify('
insert attribute Team {sql:variable("@team")}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL Server">Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
*/
It is also possible to create a new attribute with the value returned by an XQuery expression. The following example creates a new attribute named "Friend" in the element that represents the employee "Jacob". Interestingly the name of Jacob's friend is "Steve".
Example 20
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>Steve</Employee>
<Employee>Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
'

SET @x.modify('
insert attribute Friend {data(/Employees/Employee[1])}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Friend="Steve">Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
*/
Well, the attribute value can also be a list of values returned by an XQuery expression. Jacob can have more friends, can't he?
Example 21
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>Steve</Employee>
<Employee>Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
'

SET @x.modify('
insert attribute Friends {data(/Employees/Employee[. != "Jacob"])}
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Friends="Steve Smith">Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
*/
Multiple attributes can be created with a single query, by specifying a set containing the required number of attributes.
Example 22
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>Steve</Employee>
<Employee >Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
'

SET @x.modify('
insert (
attribute Team {"SQL"},
attribute Category {"MVP"}
)
as first
into (Employees/Employee[. = "Jacob"])[1]
')

SELECT @x
/*
<Employees>
<Employee>Steve</Employee>
<Employee Team="SQL" Category="MVP">Jacob</Employee>
<Employee>Smith</Employee>
</Employees>
*/
The following example shows how to insert new attributes into XML documents stored in an XML colum. What is interesting here is that the values of the attributes are taken from another table by doing a relational join. The code below uses the "sql:column()" function to create attributes with the value stored in a column.
Example 23
DECLARE @team TABLE (
EmpID INT,
Team VARCHAR(20)
)
DECLARE @emp TABLE (
EmpID INT,
Data XML
)

INSERT INTO @emp (EmpID, Data)
SELECT 1, '<Employee>Jacob</Employee>'
INSERT INTO @emp (EmpID, Data)
SELECT 2, '<Employee>Steve</Employee>'
/*
EmpID Data
----------- ---------------------------
1 <Employee>Jacob</Employee>
2 <Employee>Steve</Employee>
*/

INSERT INTO @team (EmpID, Team) SELECT 1, 'SQL Server'
INSERT INTO @team (EmpID, Team) SELECT 2, 'SQL Azure'
/*
EmpID Team
----------- --------------------
1 SQL Server
2 SQL Azure
*/

UPDATE e
SET Data.modify('
insert attribute Team {sql:column("Team")}
into (/Employee)[1]
')
FROM @emp e
INNER JOIN @team t ON e.EmpID = t.EmpID

SELECT * FROM @emp
/*
EmpID Data
----------- ---------------------------------------------
1 <Employee Team="SQL Server">Jacob</Employee>
2 <Employee Team="SQL Azure">Steve</Employee>
*/
Note that attribute names should be unique within an element. If the attribute you are trying to add already exists, you will get an error which says "XML well-formedness check: Duplicate attribute 'name'. Rewrite your XQuery so it returns well-formed XML."
However, it is possible to check for the existence of an attribute and perform a conditional insert. The following code inserts a "Team" attribute to the employee element specified in a variable. The insert operation takes place only if the employee element does not have a "Team" attribute.
Example 24
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee>Steve</Employee>
<Employee>Smith</Employee>
</Employees>'

DECLARE @name VARCHAR(20), @team VARCHAR(20)
SELECT @name = 'Steve', @team = 'SQL Server'

SET @x.modify('
insert
if (/Employees/Employee[. = sql:variable("@name")]/@Team)
then
()
else
attribute Team {sql:variable("@team")}
as first into (Employees/Employee[. = sql:variable("@name")])[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
<Employee>Smith</Employee>
</Employees>
*/
The following example inserts a comment node.
Example 25
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert <!-- List of employees -->
as first into (Employees)[1]
')

SELECT @x
/*
<Employees>
<!-- List of employees -->
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The following example adds an XSLT processing instruction to an XML document.
Example 26
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert
<?xml-stylesheet href="emp.xsl" type="text/xsl"?>
as first into (Employees)[1]
')

SELECT @x
/*
<Employees>
<?xml-stylesheet href="emp.xsl" type="text/xsl"?>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
The following example shows how to insert a text node into an XML element.
Example 27
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>'

SET @x.modify('
insert text {"Best Employees of 2009"}
as first into (Employees)[1]
')

SELECT @x
/*
<Employees>
Best Employees of 2009
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="ASP.NET">Smith</Employee>
</Employees>
*/
Inserting XML data type values
SQL Server 2005 does not allow XML data type variables in the "insert" operation using modify() method. SQL Server 2008 enhanced the modify() method to support XML data type values in the "insert" operation. The following code (runs only on SQL Server 2008 or above) inserts an XML variable into an XML document.
Example 28
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
</Employees>'

DECLARE @emp XML
SELECT @emp = '<Employee Team="SQL Server">Steve</Employee>'

SET @x.modify('
insert sql:variable("@emp")
into (Employees)[1]
')

SELECT @x
/*
<Employees>
<Employee Team="SQL Server">Jacob</Employee>
<Employee Team="SQL Server">Steve</Employee>
</Employees>
*/
Conclusions
In this article, I tried to cover almost all scenarios of "insert" operations that I could quickly think of. If you have some scenarios that are not listed here, do let me know and I will try to add them to the list.

Cheers,
Origianl Post By Jacob Sebastian.

Making Sense of the XML DataType in SQL Server

In SQL Server 2005, XML becomes a first-class data type. Developers can make minor remote modifications to stored XML documents easily, taking advantage of new support for XML schema-based strong typing, and server-based XML data validation. by Shawn Wildermuth

As database developers, many of us have had to dip our feet into the wide ocean of XML.

It should come as good news that in SQL Server 2005, you can store XML in the database with a new XML datatype. Although this is good news, many developers have been storing XML in the database for some time now. Even without implicit support for XML, developers have been shoving XML documents into text fields since XML's inception.

SQL Server 2000 included some XML features out of the box. Key among these features was the ability to return results as XML using the FOR XML clause. SQL Server 2005's functionality is markedly different. In SQL Server 2005, XML is a genuine data type, which means that you can use XML as a column in tables and views, in T-SQL statements, or as parameters of stored procedures. You can now store, query, and manage XML documents directly in the database.

More importantly, you can also now specify the schema to which your XML must conform. Aside from providing a mechanism to validate your XML in the database, this also allows you to describe complex types of data to be stored and to have an engine that enforces those rules.

Using the XML Datatype
The XML datatype is not substantially different than any other datatype in SQL Server. It can be used in any place you would ordinarily use any SQL datatype. For example, the following creates an XML variable and fills it with a XML:


DECLARE @doc xml
SELECT @doc = '<Team name="Braves" />'

Although literal XML is useful, you can also fill an XML variable using a query and the SQL Server's FOR XML syntax:


SELECT @doc =
(SELECT * FROM Person.Contact FOR XML AUTO)

The XML datatype is not limited to use as a variable. You can also use the XML data type in table columns. You can assign default values and the NOT NULL constraint is supported:


CREATE TABLE Team
(
TeamID int identity not null,
TeamDoc xml DEFAULT '<Team />' NOT NULL
)

SQL Server 2005's XML functionality is markedly different from that in SQL Server 2000.

Inserting XML data into tables is just a matter of specifying the XML to add in the form of a string:


-- Insert a couple of records
INSERT INTO Team (TeamDoc)
VALUES ('
<Team name="Braves">
<Players>
<Pitcher name="John Smoltz" role="Closer"/>
</Players>
</Team>');
INSERT INTO Team (TeamDoc)
VALUES ('<Team name="Red Sox">
<Players>
<Pitcher name="Petro Martinez" role="Starter"/>
</Players>
</Team>');

When creating instances of XML in SQL Server 2005, the only conversion is from a string to XML. Similarly, going in the reverse direction, you can only convert to a string. Converting to and from text and ntext types is not allowed.

Limitations of the XML Data Type:
Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are:


• XML types cannot convert to text or ntext data types.
• No data type other than one of the string types can be cast to XML.
• XML columns cannot be used in GROUP BY statements.
• Distributed partitioned views or materialized views cannot contain XML data types.
• Use of the sql_variant instances cannot include XML as a subtype.
• XML columns cannot be part of a primary or foreign key.
• XML columns cannot be designated as unique.
• Collation (COLLATE clause) cannot be used on XML columns.
• XML columns cannot participate in rules.
• The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types.
• Tables can have only 32 XML columns.
• Tables with XML columns cannot have a primary key with more than 15 columns.
• Tables with XML columns cannot have a timestamp data type as part of their primary key.
• Only 128 levels of hierarchy are supported within XML stored in the database.

XML Type Methods:
Up to this point, the examples have shown the XML datatype being used as just a blob of data, but this is where the real power of the XML data type shows itself. The XML data type supports several methods that can be called using the UDT dot syntax (myXml.operation()) syntax. The supported operations are listed in Table 1.

Table 1: XML Datatype methods.

Method        Name Description


query           Performs a query against the XML and returns the results of the query

exists          Performs a query against the XML and returns the value of 1 if there was a result

value           Evaluates a query to return a simple value from the XML

modify         Executes a change operation on the XML document in place

nodes         Allows you to break apart XML into a table structure



For the following sections, you will use a table called Team that contains a row for every team name. In each row, there is a TeamDoc row that contains XML about the team:


CREATE TABLE Team
(
TeamID int identity not null,
TeamDoc xml DEFAULT '<Team />' NOT NULL
)
In the examples, assume that the following XML document exists in the Braves row of the table:


<Team name="Braves">
<Players>
<Pitcher name="John Smoltz" role="Closer"/>
<Pitcher name="Russ Ortiz" role="Starter" />
<ThirdBase name="Chipper Jones" role="Starter" bats="switch"/>
</Players>
</Team>

Query Method:
You can use XML as a column in tables and views, in T-SQL statements, or as parameters of stored procedures.

The query method allows you to specify an XQuery or XPath expression to evaluate. The result of the query method is an XML data type object. The specific syntax of the query method is:


query(XQuery)
The first parameter is always an XQuery expression. The following example uses a query to return an XML document with information about each team's pitcher:


SELECT TeamDoc.query('/Team/Players/Pitcher')
FROM Team
This produces the following results:


----------------------------------------------
<Pitcher name="John Smoltz" role="Closer" />
<Pitcher name="Russ Ortiz" role="Starter" />
(1 row(s) affected)

The query method allows you to find and return nodes lists that match the XQuery expression you specify. The real power of the query method comes from the XQuery syntax, which is covered in detail later in this article.

Exist Method:
The exist method is similar to the query method except that it is used to determine whether a query yields any results. The syntax for the exist method is:



exist(XQuery)


When you use the exist method, it evaluates the query and returns the value of 1 if the query yields any results. For example, this query finds the rows in the team table where the TeamDoc field has starting pitchers:

-- Simple Exist clause

SELECT Count(*)

FROM Team

WHERE TeamDoc.exist(

'/Team/Players/Pitcher[@role="Starter"]') = 1



Value Method:
value(XQuery, datatype)

Use the value method when you want to get a single scalar value from the XML. You must specify the XQuery statement and the datatype you want it to return and you can return any datatype except the XML datatype. For example, if you want to get the name of the first pitcher on every team, you can write the query like this:


-- Do a Query to get an individual value
SELECT TeamDoc.value(
'(/Team/Players/Pitcher/@name)[1]',
'nvarchar(max)')
AS FirstPitcher
FROM Team
This query results in the scalar value of the first pitcher for each team returned in the result:


FirstPitcher
------------------------------
John Smoltz
(1 row(s) affected)

The difference between the query and value methods is that the query method returns an XML datatype that contains the results of the query, and the value method returns a non-XML datatype with the results of the query. The value method can return only a single (or scalar) value. You will get an error if you try to create an XQuery expression that returns more than one value using the value method.

Modify Method:
Although the XQuery standard does not provide a mechanism for updating XML, SQL Server 2005 supports a way of modifying parts of an XML object in place. This means that you do not have to retrieve an entire XML document just to make changes. To modify a document in place, you use a combination of the modify method and SQL Server 2005's new XML Data Modification Language (XML DML).

The syntax for the Modify method is:

modify(<XMLDML>)
The Modify method takes only a single parameter, the XML DML statement. XML DML is similar, but not identical, to SQL's insert, update and delete syntax. For example, you can modify the XML by using the insert DML statement:


SET @doc.modify('
insert <Pitcher name="Jaret Wright"/> as last
into (/Team/Players)[1]
')
You can do the same thing to modify an XML column by calling modify in an UPDATE statement:


-- Modify an XML doc without replacing
-- it completely!
UPDATE Team
SET TeamDoc.modify('
insert <Pitcher name="Jaret Wright"/> as last
into (/Team/Players)[1]
')
WHERE TeamDoc.exist('/Team[@name="Braves"]') = 1
Notice that the SET clause in this UPDATE statement does not follow the SET x = y pattern that you may be used to from writing SQL in the past. That syntax assumes that you will provide a complete new value to replace the old, which, in the case of XML, means a completely new document to replace the old. When using the XML type, the modify method changes the original document in place. There's no need to generate a completely new and separate document, or for SQL Server to attempt to replace an entire document with every change. The SET syntax in the example reflects the more efficient approach of updating a document in place.

There are three XML DML statements: insert, update, and delete. Not surprisingly, they are used to insert, update, and delete parts of an XML object. The syntax for each is similar to SQL, but with some definite differences.

Here is the syntax for the insert statement:


insert
InsertExpression (
{{as first | as last}
into | after | before} LocationExpression
)
Immediately following the insert statement is the XML that you want to insert (InsertExpression). Next you specify how you want the XML inserted. Your choices are into, after, or before. The before and after clauses instruct the database to insert the InsertExpression as a sibling to the LocationExpression. The use of before or after specifies whether to insert it before or after the LocationExpression:


SET @doc.modify('
insert <Pitcher role="Starter"
name="Jaret Wright"/>
before (/Team/Players/Pitcher)[1]
')
The into clause inserts the InsertExpression as a child of the LocationExpression. The optional clauses of as start and as last are used to specify the position of the insertion within the children:


-- Insertion within Team
SET @doc.modify('
insert <Pitcher role="Starter"
name="Jaret Wright"/>
into (/Team/Players)[1]
')
-- Insertion within Team, specifying it should
-- be inserted as the last element
SET @doc.modify('
insert <Pitcher role="Starter"
name="Jaret Wright"/>
as last into (/Team/Players)[1]
')
The syntax for the delete statement is very straightforward:
delete LocationExpression

The LocationExpression specifies what to delete from the XML data. For example, to delete all the Pitchers:


SET @doc.modify('delete /Team/Player/Pitcher')
Because the query specifies all pitcher elements, they will all be deleted. If you want to delete just a single element, you can specify identifying attributes. To delete just the pitcher named John Smoltz, you write the delete statement like so:


SET @doc.modify('
delete /Team/Players/Pitcher[@name="John Smoltz"]
')
You can also tell the delete statement to remove an individual attribute. For example, to delete the role attribute for the pitcher named John Smoltz, the XML DML looks like this:


SET @doc.modify('
delete /Team/Players/Pitcher[
@name="John Smoltz"]/@role')
Lastly, the replace value statement describes changes to make to the XML data. The syntax of the replace value statement is:


replace value of
OriginalExpression
with
ReplacementValue | if
The replace value statement is used to change discrete values in the XML. The only discrete values possible are the literal contents of a tag or the value of an attribute. The OriginalExpression must resolve to a single node or attribute. The ReplacementValue is usually a literal value to replace. Replacing the literal contents of a node requires the XQuery expression using the text() function to specify that you want to replace the text of a node. For example, to replace the inner text for a pitcher, you write the modify statement like this:


DECLARE @doc xml
SELECT @doc = '
<Team name="Braves">
<Players>
<Pitcher name="John Smoltz" role="Closer">
With team since 1989
</Pitcher>
</Players>
</Team>'
SET @doc.modify('
replace value of (/Team/Players/Pitcher[
@name="John Smoltz"]/text())[1]
with "May start in 2005"
')
Modifying an attribute is straightforward: you just need the XQuery expression to resolve to a single attribute. For example, to replace the value of the role attribute for the pitcher named John Smoltz with "Starter," do this:


SET @doc.modify('
replace value of (/Team/Players/Pitcher[
@name="John Smoltz"]/@role)[1]
with "Starter"
')
The replace value syntax also supports conditional replacement by using the if…then…else syntax within the with clause of the replace value statement. For example, to replace John Smoltz's role to Starter if he is a Closer, but change it to a Closer if he is not a Starter, you could write the code:


SET @doc.modify('
replace value of (/Team/Players/Pitcher[
@name="John Smoltz"]/@role)[1]
with (
if (/Team/Players/Pitcher[
@name="John Smoltz"]/@role = "Closer") then
"Starter"
else
"Closer"
)
')

Nodes Method:
The purpose of the nodes method is to allow normalizing of a set of nodes returned by a query into a set of rows in a table-like result set. The syntax of the nodes method is:

nodes (XQuery) Table(Column)

The XQuery is the expression that picks the nodes to be exposed as a result set. The Table and Column are used to specify names in the result set. Note that you can only have one column and that it is automatically of type XML. For example, to query to get each of the pitchers, write the code like this:


DECLARE @doc xml
SELECT @doc = '
<Team name="Braves">
<Players>
<Pitcher name="John Smoltz" role="Closer">
With team since 1989
</Pitcher>
</Players>
</Team>'
SELECT Team.player.query('.') as Pitcher
FROM @doc.nodes('/Team/Players/Pitcher')
Team(player)
This results in a single result set containing rows for each of the Pitchers' elements:


Pitcher
--------------------------------------------
<Pitcher name="John Smoltz" role="Closer" />
<Pitcher name="Russ Ortiz" role="Starter" />
(2 row(s) affected)
Notice that you used the query method to return these nodes in the result. The reason for this is the results of a nodes method may only be referred to by the XML methods (query, modify, delete, and update) or IS NULL and IS NOT NULL statements.

Gone are the days of needing to pull the entire XML document out of the database as a string, parsing it, making changes, and replacing the entire document.

More ordinarily, you may use the nodes method to break apart XML into a more useful result. For instance, you could get the players' nodes by using the nodes method, and then retrieve them with the value method to get the individual values as scalar data:


SELECT Team.player.value(
'./@name', 'nvarchar(10)') as Name,
Team.player.value('./@role', 'nvarchar(10)') as
PlayerRole
FROM @doc.nodes('/Team/Players/Pitcher')
Team(player)
This results in the following result set:


Name PlayerRole
--------------- ---------------
John Smoltz Closer
Russ Ortiz Starter
(2 row(s) affected)

Count Of Nodes:
SELECT @xmlTarget.value('count(/Team/Players/Pitcher)', 'int')
Cheers
Happy Querying

Generic Way to Bind Enum With Different ASP.NET List Controls

In this post I am going to explain how we can bind a Enum with ASP.NET List Controls. These comprise of four different types of control, CheckBoxList, DropDownList, ListBox and RadioButtonList. This is one of the common requirements during development to bind a Enum with List Control and challenge is when we need to bind the both text and value.
Let’s consider we have the blow Enum. Now I am going to bind it with all the above mentioned type List Controls.



Well, One of the easiest way that we can implement is by Enum.GetNames(Type enumType)which retrieves the name list of enumberation  and Enum.GetValues(Type enumType), which returns the list of values for each names .

Once we have the Names and Values, we can easily bind with any ListControl by just iterating with them.

 

01	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

02

{

03	string[] names;

04	Array values;

05	int countElements, upperBound, lowerBound;

06	names = Enum.GetNames(enumType);

07	values = Enum.GetValues(enumType);

08	for (countElements = 0; countElements <= names.Length - 1; countElements++)

09

{

10	listcontrol.Items.Add(new ListItem(names[countElements].ToString(), values.GetValue(countElements).ToString()));

11

}

12

}

 

You will have the below output



Now let’s have a quick look on how to call BindEnumToListControls() method, yeah very simple

 

1	protected void Page_Load(object sender, EventArgs e)

2

{

3	BindEnumToListControls(typeof(CustomerType), DropDownList1);

4	BindEnumToListControls(typeof(CustomerType), checkboxlist);

5	BindEnumToListControls(typeof(CustomerType), ListBox1);

6	BindEnumToListControls(typeof(CustomerType), RadioButtonList1);

7

}

You must be thinking why iterating through each and every names why not using LINQ instead of that.  Well, it’s time to optimized it .

The easiest way to do it with LINQ is to convert the enum into Dictionary and Set the DataSource of List. Why Dictionary ? Because we want to bind with both Key and Value Pair for Dropdown list.

 

1	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

2

{

3	string [] names = Enum.GetNames(enumType);

4	List.DataSource = strNames.Select((key, value) =>

5	new { key, value }).ToDictionary(x => x.key, x => x.value + 1);

6	listcontrol.DataTextField = "Key";

7	listcontrol.DataValueField = "Value";

8	listcontrol.DataBind();

9

}

 

Above code will do the same as we have in the previous output.  But this approach certainly having a small problem . . If you look into the ViewSource of the above generated html and verify the Value Field for each and every control. All the value field are containing the sequential value.



In some certain case, Enum may contain the Value as well, so that time we need to bind the actual value instead of overriding the value while creating the Dictionary as shown in below snippet .



I was thinking for some different approach but which leads me using for-each only. Then I look for some solution, and found one very interesting post, which handles this in a smart way.

 

01	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

02

{

03	string [] names = Enum.GetNames(enumType);

04	listcontrol.DataSource = Enum.GetValues(typeof(CustomerType)).Cast<Int32>()

05	.ToDictionary(currentItem =>

06	Enum.GetName(typeof(CustomerType), currentItem));

07	listcontrol.DataTextField = "Key";

08	listcontrol.DataValueField = "Value";

09	listcontrol.DataBind();

10

}

 

Now from the below image, you can see the Customer Type value is applied to the List elements.

Looking for simplest and handy  ways, here is two nice post by Suprotim Agarwal
How to Bind an ASP.NET DropDownList to an Enumeration with two lines of code
How to Bind An Enum Name and Value to an ASP.NET DropDownList

Cheers,
original post by
Abhijit Jana

Using ASP.NET 4.0 Chart Control With New Tooling Support for SQL Server CE

In this post I’m going to talk about  how we can use ASP.NET 4.0 Chart Control with SQL CE as back-end data base using Entity Framework. I will also show how Visual Studio 2010 SP1 provides new tooling supports for SQL Server CE 4.0. ASP.NET 4.0 introduced inbuilt chart controls features and Visual Studio 2010 SP1 Came up with nice tooling support for SQL Server CE. SQL CE is a free, embedded, lightweight  database engine that enables easy database storage. This does not required any installation and runs in memory.  Let’s see how we can place this together and create a small apps and deploy it using  new “Web Deployment Tool” which is available with Visual Studio 2010 SP1.

To demonstrate the complete flow  we will do the following steps.

• Creating a New ASP.NET 4.0 Web Forms Application.
• Create a SQL Server CE Database with new tooling of Visual Studio 2010 SP1.
• Adding ASP.NET Chart Control to web forms.
• Building an EF model layer to attaching with ASP.NET 4.0 Chart Control.
• Using Same Model layer with ASP.NET GridView with Enabling  Edit / Delete support of records in SQL CE Data base.
• Reflecting the changes in Chart Controls after editing records from GridView.
• Dynamically changing ASP.NET 4.0 Chart Control Type.
• Deploy using Web Deployment Tool.

After end of the above mentioned exercises we will achieve  something like below :


So Let’s start. First  create a new ASP.NET 4.0 Web Application and navigate to Solution Explorer (Solution Navigator) . In the Project solution hierarchy Right click on the “App_Data” folder  and select   “Add  > New Item” menu command.



This will open “Add Items Dialogs”  and then choose “SQL Server Compact 4.0 Local Database”,  Give the name “Students.sdf” and click on “Add”



Above step will create an empty SQL Server Compact database for local data.



Double click on the “Student.sdf” it will brings up the “Server Explorer” with the same data base “Students.sdf” which we have already created. Visual Studio treats the “Students.sdf” as a new Data Base Connection.


Tip : In this situation if you want to add a new SQL Server CE Connection or any chance you are not able to view the connection for created sdf file or got disconnect,  you can simply brings it back by doing followings
1. Click on “Connect To Data Base Icon”


2.  This will brings up the “Add Connection Dialog” but default Data Source is set to “Microsoft SQL Server ( SqlClient) . Click on “Change..”



3. In “Change Data Source” dialog, select “Microsoft SQL Server Compact 4.0”  as Data Source and by it will have only single Data Provider “.NET Framework Data Provider for Microsoft SQL Server Compact 4.0”



on click of “Ok” it will navigate back to “Add Connection Window”.
4. There you can “Create a New SQL CE DB” or can browse  existing “SQL CE DB”



Above 4 steps talks about create a new connection and SQL CE DB from Server Explore or using Existing one.  But we have already created our “Students.sdf”. So lets back to action.
In Server Explorer , Expand Students.sdf and click on “Create Table”



This will brings up “Add New Table” window where you have to fill the Table name and corresponding column name.  Here We have used table name as “Student” with three column “Roll”, “Name”, “Marks“



On click of “Ok”, you will see a new table with name “Student” has been created under the table folder .


Let’s have a look how we can enter data into the created table. There is two ways by which we  achieve the same.  In Server Explorer, right click on the table. From the context menu you can select either of “New Query” option where you have to write simple SQL Statement to store records or else you can select “Show Tables Data” where enter data in different column. Very straight forward.



below screen shows how we can insert data in SQL CE table using “New Query” mode.



Well, by using any of them just entered few data in Student table.



We will be displaying above data in ASP.NET Chart control. So lets build the ASP.NET Web Forms with Chart Control
ASP.NET 4 introduced inbuilt chart control which you can find in the “Data” section of toolbox .



Drag and Drop the Chart control in web page and choose any of the  Chart Type from Chart control Smart Tag.













Now we have to choose data source for the Chart Control.  So, let create a EF Model to read the data from
SQL CE Data Base.
From Solution Explorer > Add > New Item, Select “ADO.NET Entity Data Model” and give the name “Students.edmx”



Then Select “Generate from Database” option from “Entity Data Model Wizard”



Continue with the Wizard, in the next screen, you have to provide connection and entity name.



Click on “Finish”, you can see the generated “edmx” file



Just Build the solution once, and back to web page with Chart Control where we stopped earlier.
Selectoption from from Chart Control Smart Tag.



This will launch the “Data Source Configuration Wizard” , Select “Entity” type and specify the name “StudentEntityDataSource” and click on OK.
























From the below screen select Named Connection and Default Container name. Both of these dropdown populate automatically as we have already created the entity model. In the next window, select all the column and Enable Automatic insert, updates and deletes.




I just enabled the automatic insert, delete and update for future use with gridview. Click on Finish !
Once done with the data source configuration, provides the value for Chart Series Data Member .



That’s all. Run the application. You will get below output at first look.



Let’s make this stuff more interesting, First I will change the appearance to 3D, don’t worry. This is also inbuilt support !



On changes of above code snippet for the Chart control you will get below output.


Let me know bring one GridView in the seen so that you can see the Edit and update reflection to ASP.NET Chart Control that refers the same SQL Server CE Data Base.
I am referring the same Entity data source that used for Chart Control. This GridView also Enabled with Editing Rows that is the raised while creating the model I made the auto editable.



Below images is the first appearance after putting GridView.



Now, you can edit the records in grid and get the same reflection on Chart. Because  both are referring to same records.

01	<asp:UpdatePanel ID="UpdatePanel1" runat="server">

02	<ContentTemplate>

03	<table border="0" cellpadding="0" cellspacing="0">

04

<tr>

05

<td>

06

<div>

07	<asp:Chart ID="Chart1" runat="server" DataSourceID="StudentEntityDataSource" Palette="Chocolate"

08	BorderlineColor="Window">

09

<Series>

10	<asp:Series Name="Series1" XValueMember="Name" YValueMembers="Marks">

11	</asp:Series>

12

</Series>

13	<ChartAreas>

14	<asp:ChartArea Name="ChartArea1">

15	<Area3DStyle Rotation="15" Perspective="10" Enable3D="True" Inclination="15" IsRightAngleAxes="False"

16	WallWidth="1" IsClustered="False" />

17	</asp:ChartArea>

18	</ChartAreas>

19	<BorderSkin BackColor="Highlight" />

20	</asp:Chart>

21

</div>

22

<div>

23	<asp:Label ID="Label1" Text="Select Chart Type" runat="server" />

24	<asp:DropDownList ID="ddlCharType" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlCharType_SelectedIndexChanged">

25	</asp:DropDownList>

26

</div>

27

</td>

28	<td valign="top">

29	<asp:GridView ID="GridView1" runat="server" CellPadding="4" DataSourceID="StudentEntityDataSource"

30	ForeColor="#333333" GridLines="None">

31	<AlternatingRowStyle BackColor="White" />

32

<Columns>

33	<asp:CommandField ShowEditButton="True" ShowSelectButton="True" />

34	</Columns>

35	<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

36	<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

37	<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

38	<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

39	<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

40	<SortedAscendingCellStyle BackColor="#FDF5AC" />

41	<SortedAscendingHeaderStyle BackColor="#4D0000" />

42	<SortedDescendingCellStyle BackColor="#FCF6C0" />

43	<SortedDescendingHeaderStyle BackColor="#820000" />

44	</asp:GridView>

45

</td>

46

</tr>

47

</table>

48	<asp:EntityDataSource ID="StudentEntityDataSource" runat="server" ConnectionString="name=StudentsEntities"

49	DefaultContainerName="StudentsEntities" EnableDelete="True" EnableFlattening="False"

50	EnableInsert="True" EnableUpdate="True" EntitySetName="Students">

51	</asp:EntityDataSource>

52	</ContentTemplate>

53	</asp:UpdatePanel>

In the above picture I have shown two scenarios. Upper section is the chart before updating records and bottom section after updating the records.  You can also see the snaps of updated  SQL CE Data base. Yes, very simple !
To make it more interesting let’s implement the dynamically change the Chart Type.



If you are wondering how to  implement this, here we go. Let’s put a dropdown control in the web forms.
All the Chart Type for ASP.NET 4.0 Chart control are defined in a public enum SeriesChartType.  So first bind the SeriesChartType enum value in dropdown.  You can read one of my post on Generic Way to Bind Enum With Different ASP.NET List Controls .

01	protected void Page_Load(object sender, EventArgs e)

02

{

03	if (!Page.IsPostBack)

04

{

05	BindEnumToListControls(typeof(SeriesChartType), ddlCharType);

06

}

07

}

08	/// <summary>

09	/// Binds the enum to list controls.

10	/// </summary>

11	/// <param name="enumType">Type of the enum.</param>

12	/// <param name="listcontrol">The listcontrol.</param>

13	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

14

{

15	string[] names = Enum.GetNames(enumType);

16	listcontrol.DataSource = names.Select((key, value) =>

17	new { key, value }).ToDictionary(x => x.key, x => x.value + 1);

18	listcontrol.DataTextField = "Key";

19	listcontrol.DataValueField = "Value";

20	listcontrol.DataBind();

21

}

Change the Chart type on Drop Down Selection index change.

1	protected void ddlCharType_SelectedIndexChanged(object sender, EventArgs e)

2

{

3	this.Chart1.Series["Series1"].ChartType = (SeriesChartType)Enum.Parse(typeof(SeriesChartType), ddlCharType.SelectedItem.Text);

4

}

Finally, let have a look into the deployment. Why I am talking deployment over here ? Yes the only reason of SQL Server CE. As I said, we do not need any installation of SQL Server CE DB if we want to move our data base, but we have to provide some associated dll that helps ASP.NET engine to interact with the DB. Visual Studio 2010 SP1 introduced one new features “Web Deployment Tool”.  Right Click on the Solution file, and select “Add Deployable Dependencies…” .



This is only available for Razor and SQL Server CE.



Select “Select SQL Server Compact”  checkbox and “Click on Ok” . This will add all required assembiles automatcially in your solution and your are good to deploy without any problem



Well, that’s all. To summarizes what I have discussed is using ASP.NET 4.0 Chart Control with SQL Server CE 4.0. I have also discussed how we can use power of SQL Server CE Tool support which is introduced in Visual Server 2010 SP1 along with deployment using Web Deployment Tool.
Hope this will help you !

Cheers,
Original Post by,
Abhijit Jana