SQL Server 2005 with XML Parameters

-
We frequently have requirement like

Passing IDs in comma saperated value to the sql server for further processing.

Generally what we do is just create loop and get the value from it and ...

But SQL 2005 comes up with XQuery. Using this you can easly pass such type of data also some complex thing too.

For example...

DECLARE @productIds xml
SET @productIds ='<Products><id>9</id><id>18</id><id>27</id></Products>'

SELECT ParamValues.ID.value
('.','VARCHAR(20)')FROM @productIds.nodes('/Products/id')as ParamValues(ID)


Which gives us the following three rows:

9
18
27

Comments

Post a Comment

Popular posts from this blog

Auto Refresh .aspx page - ASP.NET

-
Method 1: Response.AddHeader To Refresh Web page After every 5 Seconds You can add following code, Response.AddHeader("Refresh", "5"); Cricket Sites, Stock Exchange sites use similar logic :) Method 2: In body Tag, window.setTimeout The 1000 = 1 Second... < body onload ="window.setTimeout('window.location.reload()',1000);" > Method 3: In Meta Tag Theres also a meta tag that you can define on the head, but not sure wheter it refreshes even if the content has not finished loading or if it starts counting when the head section loaded. Code would be something like that: < meta content ="600" http-equiv ="refresh" > Method 4: Timer Control : Microsoft ASP.NET 2.0 AJAX Extensions server control In following example, the timer interval is set to 10 seconds <%@ Page Language="C#" AutoEventWireup="true" %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN&q
Read more

Auto Sequence Number in Grid Control with Paging

-
we are going to see how to generate sequence number (row number) in DataGrid. To generate sequence number, we will use the DataGridItem’s ItemIndex property. But For example if we are going to second page with Pagesize 3, then sequence number should start from 4. Code snippet for implementing this method is... < asp:DataGrid id ="DataGrid1" runat ="server" PagerStyle-Mode ="NumericPages" PageSize ="10" AutoGenerateColumns ="False" AllowPaging ="True" > < Columns > < asp:templatecolumn headertext ="Row Number" > < itemtemplate > <% # (DataGrid1.PageSize*DataGrid1.CurrentPageIndex)+ Container.ItemIndex+1 %> </ itemtemplate > </ asp:templatecolumn > < asp:boundcolumn runat ="server" Da
Read more

MVC Request Execution Stages - Life Cycle

-
Image
Back to Home Execution Stages: For web: HTTP request for Application Incoming request routed to Controller (Perform Routing) The controller processes request and create presentation Model The controller also selects appropriate result (view) Model is passed to View View transforms Model into an appropriate output format (HTML) The response is rendered (HTTP response) Image Courtesy: SlideShare.net  Back to Home
Read more

Paged Data Source - Custom Paging

-
PagedDataSource , is a class that encapsulates the paging related properties for data-bound controls such as DataGrid, GridView, DataList, DetailsView and FormView that allow it to perform paging. And this article is going to combine both DataList control and PagedDataSource class to explain dynamic or custom paging methods for Asp.Net developers. protected void Page_Load( object sender, EventArgs e) { try { if (!IsPostBack) { LoadPhoto(); } } catch (Exception Exc) { HandleException = Exc; } } // LoadPhoto Function private void LoadPhoto() { PagedDataSource objPhotoPage = new PagedDataSource(); objPhotoPage.AllowPaging = true ; SOBLImageGallery _SOBLImageGallery = new SOBLImageGallery(); DataSet dsImageGallery = _SOBLImageGallery.Select6Records( new SOENTImageGallery()); if (dsImageGallery != null && dsImageGallery.Tables.Count > 0 && dsImageGallery.Tables[0].
Read more

Overview of MVC

-
Overview of the ASP.NET MVC - Introduction to ASP.NET MVC -  ASP.NET MVC Version History -  Life Cycle of MVC request -  Role of Model, View, and Controller -  Works Behaviour of ASP.NET MVC -  Benefits MVC Architecture - Razor View Engine - Essential Language Features & Helpers - Basic Helpers - Strongly-Typed Helpers - Creating Custom Helpers - Declarative Helpers The MVC Pattern - The MVC Design Pattern - Working with Controllers - Model Templates - MVC State Management - Working with URLs and Routing (URL Routing) - Bundling and Minification Working with Controllers - Controller Actions - Controller and ActionResult Types - Passing Data from View to Controller - Action Filters & Custom Action Filter - Action Selectors HttpGet and HttpPost Razor Views - Razor View Engines and Syntax - Working with Layout Pages - Views & Partial Views - ViewData and ViewBag - Strongly-Typed Views - Working with Areas Model Binders - Behaviour
Read more

LINQ - C# Programming

-
Get DataSource In C# as in most programming languages a variable must be declared before it can be used. In a LINQ query, the from clause comes first in order to introduce the data source (customers) and the range variable (cust). //QEmp is an IEnumerable var QEmp = from e in Emp select e; Filter Probably the most common query operation is to apply a filter in the form of a Boolean expression. The filter causes the query to return only those elements for which the expression is true. The result is produced by using the where clause. The filter in effect specifies which elements to exclude from the source sequence. var QEmp = from e in Emp where e.Country == "India" select e ----AND Condition---- where e.Country == "India" && e.Name == "Ashvin" ----OR Condition---- where e.Country == "India" || e.Name == "Ashvin" Ordering The orderby clause will cause the elements in the
Read more

ASP.NET MVC Version History

-
Back to Home ASP.NET MVC 1.0 | .NET Framework : 3.5 | Visual Studio: 2008  Released on 13 March 2009 WebForm View Engine HTML Helpers View Data URL Routing Partial View  Action Filters ASP.NET MVC 2.0  | .NET Framework : 3.5 & 4.0 | Visual Studio: 2008 & 2010 Released on 10 March 2010 Data Annotation Async Controller ASP.NET MVC 3.0  | .NET Framework : 4.0 | Visual Studio: 2010  Released on 13 January 2011 Razor View Engine View Bag ASP.NET MVC 4.0  | .NET Framework : 4.0 & 4.5 | Visual Studio: 2010 & 2012  Released on15 August 2012 WebAPI Bundling & Minification DB Migration Mobile ASP.NET MVC 5.0  | .NET Framework : 4.5 & 4.5.1 | Visual Studio: 2013  Released on17 October 2013 Bootstrap ASP.NET Indentity Back to Home
Read more

How to Edit More than 200 Rows in SQL Server 2008

-
How to Edit More than 200 Rows in SQL Server 2008 Management Studio Step 1: In SQL Server 2008 Management Studio, go to "Tools" -> "Options" -> "SQL Server Object Explorer" -> "Commands". Step 2: Now in "Table and View Options" section, you can change either: #Value for Edit Top Rows command, to a value greater than or less than 200. #Value for Select Top Rows command, to a value greater than or less than 1000. Step 3: By specifying a value of 0, SQL Server will return all rows. (If you sql tables are really large data volume, you should NOT set these values to 0.) Step 4: Click OK to save your changes.
Read more

LINQ to XML : XElement and XAttribute

-
var _CountryLists = (from c in SW_Countries select new { c.ID, c.Name, c.Abbreviation }); var xml = new XElement("root",from c in _CountryLists select new XElement("nodes", new XAttribute("id", c.ID), new XAttribute("abb", c.Abbreviation), new XAttribute("name", c.Name))); return xml.ToString(); xml will return result like this... <root> <nodes id="13" abb="AUS" name="Australia" /> <nodes id="28" abb="MMR" name="Burma" /> <nodes id="33" abb="BRA" name="Brazil" /> <nodes id="35" abb="BTN" name="Bhutan" /> <nodes id="40" abb="CAN" name="Canada" /> <nodes id="43" abb="LKA" name="Sri Lanka" /> <nodes id="46" abb="CHN" name="China&qu
Read more