SQL SERVER: Check if Node exists in XML or not

Today, I have one requirement to check dynamically if a node exists in my xml or NOT.

I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic.

I figure it out by using EXISTS.

This is my XML, that I got as parameter.

DECLARE @ExportData  XML
SELECT @ExportData =
'<Data Number="A123">
  <BulkData>
    <EachData Parts="Test1" />
    <EachData Parts="Test2" />
    <EachData Parts="Test3" />
  </BulkData>
</Data>'

Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result.
So, I used this

SELECT @ExportData.exist('(//BulkData)')

This will return "1" if node is exists else return "0".

That's it. I can write based on the return result by this statement.

Let me know if it helps you.

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.
SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.
Let me show you how to use it. Syntax to use SP:

EXEC SP_PROCOPTION     
@ProcName = 'SPNAME',    
@OptionName = 'startup',    
@OptionValue = 'true/false OR on/off'

• @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
• @OptionName, should be "startup" always.
• @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.

SQL SERVER: Generate Comma Separated List with SELECT statement

Scenario:

Create a table and insert some data like:

create table tblProductCategory (categoryId int,categoryName varchar(max))

insert into tblProductCategory values (1,'Computer')

insert into tblProductCategory values (2,'Mobile')

Create next table and insert some data like:

create table tblProductDetails (id Int, categoryID int, productName varchar(max))

insert into tblProductDetails values (1,1,'Intel')

insert into tblProductDetails values (2,1,'CMD')

insert into tblProductDetails values (3,1,'Samsung')

insert into tblProductDetails values (4,1,'LG')

insert into tblProductDetails values (5,2,'Ecron')

insert into tblProductDetails values (6,2,'iPhone')

insert into tblProductDetails values (7,2,'Nokia')

Method (1) :

select C.categoryID,C.categoryName,    (Stuff((SELECT ', ' + productName AS [text()]

            FROM (SELECT DISTINCT PD.productName FROM tblProductDetails PD where PD.categoryID= C.categoryId) x       

                For XML PATH ('')),1,1,''))as ProductDetails

       from tblProductCategory C

Method (2) :

create function dbo.commaSeparatedString (@categoryID int)

returns nvarchar(max)

as

begin

    declare @return nvarchar(max)=''

        SELECT @return = Stuff((SELECT ', ' + productName

        FROM (SELECT DISTINCT productName FROM tblProductDetails where categoryID=@categoryID) x       

            For XML PATH ('')),1,1,'')

    return @return

end

select C.categoryID,C.categoryName ,dbo.commaSeparatedString(C.categoryID)as ProductDetails from tblProductCategory C

Method (3) :

SELECT categoryId,categoryName,SUBSTRING(( SELECT ( ', ' + productName)FROM tblProductDetails t2  WHERE t1.categoryId = t2.categoryId  ORDER BY t1.categoryId, t2.categoryId  FOR XML PATH('')), 3, 1000)as ProductDetails

FROM tblProductCategory t1

Output :

CategoryID	CategoryName	ProductDetails
1	Computer	CMD, Intel, LG, Samsung
2	Mobile	Ecron, iPhone, Nokia

Please make comments, if this helps you in any way 

mailto using mail id,subject, body in asp.net

<a href = "mailto:opjodhpur@gmail.com?subject=my profile &body=Please have a look attached my resume.....">click here</a>

Asp.net real time MVC Interview Quesitons with answers

What is MVC?

MVC is a framework methodology that divides an application’s implementation into three component roles: models, views, and controllers.

“Models” in a MVC based application are the components of the application that are responsible for maintaining state. Often this state is persisted inside a database (for example: we might have a Product class that is used to represent order data from the Products table inside SQL).
“Views” in a MVC based application are the components responsible for displaying the application’s user interface. Typically this UI is created off of the model data (for example: we might create an Product “Edit” view that surfaces textboxes, dropdowns and checkboxes based on the current state of a Product object).
“Controllers” in a MVC based application are the components responsible for handling end user interaction, manipulating the model, and ultimately choosing a view to render to display UI. In a MVC application the view is only about displaying information – it is the controller that handles and responds to user input and interaction.

What are the 3 main components of an ASP.NET MVC application?
1. M - Model
2. V - View
3. C - Controller

In which assembly is the MVC framework defined?
System.Web.Mvc

Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.

What does Model, View and Controller represent in an MVC application?
Model: Model represents the application data domain. In short the applications business logic is contained with in the model.

View: Views represent the user interface, with which the end users interact. In short the all the user interface logic is contained with in the UI.

Controller: Controller is the component that responds to user actions. Based on the user actions, the respective controller, work with the model, and selects a view to render that displays the user interface. The user input logic is contained with in the controller.

What is the greatest advantage of using asp.net mvc over asp.net webforms?
It is difficult to unit test UI with webforms, where views in mvc can be very easily unit tested.

Which approach provides better support for test driven development - ASP.NET MVC or ASP.NET Webforms?
ASP.NET MVC
What is Razor View Engine?

Razor view engine is a new view engine created with ASP.Net MVC model using specially designed Razor parser to render the HTML out of dynamic server side code. It allows us to write Compact, Expressive, Clean and Fluid code with new syntaxes to include server side code in to HTML.

What are the advantages of ASP.NET MVC?
1. Extensive support for TDD. With asp.net MVC, views can also be very easily unit tested.
2. Complex applications can be easily managed
3. Seperation of concerns. Different aspects of the application can be divided into Model, View and Controller.
4. ASP.NET MVC views are light weight, as they donot use viewstate.

Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.
What is namespace of asp.net mvc?

ASP.NET MVC namespaces and classes are located in the System.Web.Mvc assembly.

System.Web.Mvc namespace 
Contains classes and interfaces that support the MVC pattern for ASP.NET Web applications. This namespace includes classes that represent controllers, controller factories, action results, views, partial views, and model binders.

System.Web.Mvc.Ajax namespace 
Contains classes that support Ajax scripts in an ASP.NET MVC application. The namespace includes support for Ajax scripts and Ajax option settings.

System.Web.Mvc.Async namespace 
Contains classes and interfaces that support asynchronous actions in an ASP.NET MVC application

System.Web.Mvc.Html namespace 
Contains classes that help render HTML controls in an MVC application. The namespace includes classes that support forms, input controls, links, partial views, and validation.

Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple controllers.

What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to execute and alse selecting the view to render.

Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3. RedirectResult
4. ContentResult
5. JsonResult
What is the ‘page lifecycle’ of an ASP.NET MVC?

Following process are performed by ASP.Net MVC page:
1) App initialization
2) Routing
3) Instantiate and execute controller
4) Locate and invoke controller action
5) Instantiate and render view

What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent this default behaviour, just decorate the public method with NonActionAttribute.

What is the significance of ASP.NET routing?
ASP.NET MVC uses ASP.NET routing, to map incoming browser requests to controller action methods. ASP.NET Routing makes use of route table. Route table is created when your web application first starts. The route table is present in the Global.asax file.
How route table is created in ASP.NET MVC?

When an MVC application first starts, the Application_Start() method is called. This method, in turn, calls the RegisterRoutes() method. The RegisterRoutes() method creates the route table.

What are the 3 segments of the default route, that is present in an ASP.NET MVC application?
1st Segment - Controller Name
2nd Segment - Action Method Name
3rd Segment - Parameter that is passed to the action method

Examplehttp://nareshkamuni.blogspot.in/search/label/MVC
Controller Name = search
Action Method Name = label
Parameter Id = MVC

ASP.NET MVC application, makes use of settings at 2 places for routing to work correctly. What are these 2 places?
1. Web.Config File : ASP.NET routing has to be enabled here.
2. Global.asax File : The Route table is created in the application Start event handler, of the Global.asax file.
How do you avoid XSS Vulnerabilities in ASP.NET MVC?

Use thesyntax in ASP.NET MVC instead of usingin .net framework 4.0.

What is the adavantage of using ASP.NET routing?
In an ASP.NET web application that does not make use of routing, an incoming browser request should map to a physical file. If the file does not exist, we get page not found error.

An ASP.NET web application that does make use of routing, makes use of URLs that do not have to map to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are descriptive of the user's action and therefore are more easily understood by users.

What are the 3 things that are needed to specify a route?
1. URL Pattern - You can include placeholders in a URL pattern so that variable data can be passed to the request handler without requiring a query string.
2. Handler - The handler can be a physical file such as an .aspx file or a controller class.
3. Name for the Route - Name is optional.

Is the following route definition a valid route definition?
{controller}{action}/{id}
No, the above definition is not a valid route definition, because there is no literal value or delimiter between the placeholders. Therefore, routing cannot determine where to separate the value for the controller placeholder from the value for the action placeholder.

What is the use of the following default route?
{resource}.axd/{*pathInfo}
This route definition, prevent requests for the Web resource files such as WebResource.axd or ScriptResource.axd from being passed to a controller.

What is the difference between adding routes, to a webforms application and to an mvc application?
To add routes to a webforms application, we use MapPageRoute() method of the RouteCollection class, where as to add routes to an MVC application we use MapRoute() method.

How do you handle variable number of segments in a route definition?
Use a route with a catch-all parameter. An example is shown below. * is referred to as catch-all parameter.
controller/{action}/{*parametervalues}

What are the 2 ways of adding constraints to a route?
1. Use regular expressions
2. Use an object that implements IRouteConstraint interface

Give 2 examples for scenarios when routing is not applied?
1. A Physical File is Found that Matches the URL Pattern - This default behaviour can be overriden by setting the RouteExistingFiles property of the RouteCollection object to true.
2. Routing Is Explicitly Disabled for a URL Pattern - Use the RouteCollection.Ignore() method to prevent routing from handling certain requests.

What is the use of action filters in an MVC application?
Action Filters allow us to add pre-action and post-action behavior to controller action methods.

If I have multiple filters impleted, what is the order in which these filters get executed?
1. Authorization filters
2. Action filters
3. Response filters
4. Exception filters

What are the different types of filters, in an asp.net mvc application?
1. Authorization filters
2. Action filters
3. Result filters
4. Exception filters

Give an example for Authorization filters in an asp.net mvc application?
1. RequireHttpsAttribute
2. AuthorizeAttribute

Which filter executes first in an asp.net mvc application?
Authorization filter


What are the levels at which filters can be applied in an asp.net mvc application?
1. Action Method
2. Controller
3. Application
[b]Is it possible to create a custom filter?[/b]
Yes

What filters are executed in the end?
Exception Filters

Is it possible to cancel filter execution?
Yes

What type of filter does OutputCacheAttribute class represents?
Result Filter

What are the 2 popular asp.net mvc view engines?
1. Razor
2. .aspx
What is difference between Viewbag and Viewdata in ASP.NET MVC?

The basic difference between ViewData and ViewBag is that in ViewData instead creating dynamic properties we use properties of Model to transport the Model data in View and in ViewBag we can create dynamic properties without using Model data.

What symbol would you use to denote, the start of a code block in razor views?
@

What symbol would you use to denote, the start of a code block in aspx views?
<%= %>

In razor syntax, what is the escape sequence character for @ symbol?
The escape sequence character for @ symbol, is another @ symbol

When using razor views, do you have to take any special steps to proctect your asp.net mvc application from cross site scripting (XSS) attacks?
No, by default content emitted using a @ block is automatically HTML encoded to protect from cross site scripting (XSS) attacks.

When using aspx view engine, to have a consistent look and feel, across all pages of the application, we can make use of asp.net master pages. What is asp.net master pages equivalent, when using razor views?
To have a consistent look and feel when using razor views, we can make use of layout pages. Layout pages, reside in the shared folder, and are named as _Layout.cshtml

What are sections?
Layout pages, can define sections, which can then be overriden by specific views making use of the layout. Defining and overriding sections is optional.

What are the file extensions for razor views?
1. .cshtml - If the programming lanugaue is C#
2. .vbhtml - If the programming lanugaue is VB

How do you specify comments using razor syntax?
Razor syntax makes use of @* to indicate the begining of a comment and *@ to indicate the end. An example is shown below.

What is Routing?

A route is a URL pattern that is mapped to a handler. The handler can be a physical file, such as an .aspx file in a Web Forms application. Routing module is responsible for mapping incoming browser requests to particular MVC controller actions.

How to render partialview in mvc3 razor through jquery ajax

<script type="text/javascript">
    $.get(
        "/MyController/MyAction",
        null,
        function (data) { $("#target").html(data) }
     );
</script>

public ActionResult MyAction() {
    return PartialView("SomeView");
}
 

Image uploading/saving in mvc3 with razor

View : 
@using (Html.BeginForm("ImageSaveAction", "Landmark", FormMethod.Post,
    new { enctype = "multipart/form-data" }))
{   
    <table width="500px">
        <tr>
            <td width="30%">
                File Name :
            </td>
            <td>
                <input type="file" name="file1" id="fileUploader" />
                <input type="file" name="file2" id="fileUploader1" />
            </td>
        </tr>
        <tr>
            <td>
            </td>
            <td>
                <input type="submit" id="btnSubmit" value="Submit" />
            </td>
        </tr>
    </table>
}
Controller class :
[HttpPost]
        public ActionResult ImageSaveAction(HttpPostedFileBase file)
        {
            if (Request.Files.Count > 0)
            {
                HttpPostedFileBase _file = Request.Files["file1"];
                var fileName = this.Server.MapPath("~/uploads/"
                                + System.IO.Path.GetFileName(_file.FileName));
                _file.SaveAs(fileName);

                HttpPostedFileBase _file2 = Request.Files["file2"];
                var fileName2 = this.Server.MapPath("~/uploads/"
                                + System.IO.Path.GetFileName(_file2.FileName));
                _file2.SaveAs(fileName2);
            }
            return View("Index");
        }