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...
Read more

SOLID Principles with C#

-
SOLID Principles are coding standards that all developers should have a clear concept for developing software in a proper way to avoid a bad design. S - Single Responsibility Principle (SRP): A class should have one, and only one, a reason to change In Single Responsibility Principle Each class and module should focus on a single task at a time Everything in the class should be related to that single purpose There can be many members in the class as long as they related to the single responsibility With SRP, classes become smaller and cleaner Code is less fragile  Here  UserAccount  class created which have below operations like, Login, Register, LogError and Send Mail. According to SRP, ideally, LogError and SendEmail are should not be part of  UserAccount  operations.      public class UserAccount     {         public bool Login( string username, ...
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

Page Life Cycle in ASP.NET

-
Image
Life cycle when the page processed during a postback The events associated with the relevant page cycle phases are: Page Initialization: Page_Init View State Loading:LoadViewState Postback data processing: LoadPostData Page Loading: Page_Load PostBack Change Notification: RaisePostDataChangedEvent PostBack Event Handling: RaisePostBackEvent Page Pre Rendering Phase: Page_PreRender View State Saving: SaveViewState Page Rendering: Page_Render Page Unloading: Page_UnLoad The processing sequence in which a page is processed during a postback event is: Initializing: During this phase, the server creates an instance of the server control Loading view state: During this phase, the view state of the control posted by the client is reloaded into the new instance of the control. Loading : During this phase, the instance of the control is loaded onto the page object in which it is defined. Loading the postback data : During this phase, the server s...
Read more

How to Update Dependent Excel Cells Automatically

-
Image
I was facing some issue to update excel cell which was depended on some formula, I need to change 'Comparison_Rate_Calculator' by Programming and need to read Calculated value by formula '=+HLOOKUP(C3,Workings!$D$1:$AJ$2,2,FALSE)' over 'Comparison_Rate_Calculator_Values' column. I didn't get calculated value even i was able to change 'Comparison_Rate_Calculator' column's value. So I got solutions by refreshing Excel WorkSheet and Here is the Solutions. File: Web.Config < connectionstrings >   < add       name = " xls "       connectionstring = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AshvinWorkspace\POCs\ReadExcelSheet\ReadExcelSheet\Files\HLComparisonRateCalculator.xls;Extended Properties=Excel 8.0 " >   </ add > </ connectionstrings > File: Default.aspx.cs public partial class _Default : System.Web.UI. Page {     protected void ...
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

Applying Dynamic Themes to WebPage

-
Applying Dynamic Themes to Web Page (.aspx) in ASP.NET Themes and Skins. The themes and skins features in ASP.NET 2.0 allow for easy customization of your site's look-and-feel. You can define style information in a common location called a "theme", and apply that style information globally to pages or controls in your site. Like Master Pages, this improves the maintainability of your site and avoid unnecessary duplication of code for shared styles. protected void Page_PreInit( object sender, EventArgs e) { Page.Theme = Session[ "Theme" ] as string ?? "Theme1" ; } protected void Page_Load( object sender, EventArgs e) { } protected void Button1_Click( object sender, EventArgs e) { if (Session[ "Theme" ] != null ) { if (Session[ "Theme" ].ToString() == "Theme1" ) Session[ "Theme" ] = "Theme2" ; else Session[ "Theme" ] = "Theme1...
Read more