Developer Guide - SCRUM Process

What is agile scrum (sprint) process?

Scrum is a software development process. In today’s rapid world stakeholders want immediate return on their investments. They don’t want to wait for longer periods to get full featured product. As a result, nowadays new software development and testing framework are catching momentum i.e. Scrum approaches.

In scrum, projects are divided in small features to be developed and tested in specific time-frames called as sprint (small cycles). Features should get developed and tested in specified small time-frames.  This agile scrum team is handled by scrum master.

Scrum is an iterative, incremental framework for projects and products or application development. Scrum has become more and more popular software development and testing framework among organizations. Many small to large sized IT companies have started to embrace Scrum framework, as this can create excellent quality products in less time than other traditional methodologies. This framework can save companies both time and money.

Source: ScrumAlliance

Soft Skills for a Scrum Team:

What Soft Skills are required to be a Successful Scrum Team?

When we start our regular (Agile) sprints (Cycles of work), we usually find some of the challenges with our team members. These challenges are not part of technical difficulties.  It usually occurs with team member’s mindset or their soft skills.  Many successful Scrum projects taught us that the success of scrum depends on how team members support whole heartedly towards the Sprint.

Let us discuss some of the pre-requisite soft skills for a Scrum Team.

Team Spirit

Cross functional Team work is at the heart of Scrum.  There is no “my work”, “I have finished my work” and “your work”. On a Scrum team we find only “Our work”, “we have completed our Sprint”.  Individuals will have helping tendency for sharing technical knowledge. Scrum Members are always available to team members rather than locked away behind closed doors.  Scrum Master will always motivate the teams and create a Supporting learning environment. Team will always be sprint-oriented and often discuss smooth run of the sprint. A scrum team’s job is to self-organize around the challenges and management’s job is to remove impediments to self-organization.

Communication

Good communication must exist among team members of development team, testing team, business analysts and stake holders. There must be highly collaborative interaction between client and the delivery teams. More client involvement implies more suggestions or changes from the client.  It implies more bandwidth for communication.

Commitment

Agile Teams needs periodic re-energizing to renew their commitments to their purpose and to each other.  Scrum Masters can help by ensuring that the team embraces the concept of whole-team responsibility and whole-team commitment to deliver working software at the end of each sprint. With the whole-team commitment, the team member who has completed his tasks will help the one who has not completed so that hopefully each finishes on time.

Problem Solving

Scrum does not simply focus on developing just any type of end product.  Instead, the Scrum method allows the team to focus on creating a product that fulfils the customer’s highest value priorities which are defined by product owners.

Transparency

Transparency among team members and management gives a real momentum to the scrum team. Scrum Master encourages people to ask for help, surface roadblocks, and give public recognition for those brave enough to do so. At the same time, Scrum Master also understands the time wasted and impact on the team when individuals sit on or ignore problems.

Scrum Result

If scrum team follows some of above said soft skills, team velocity will increase significantly.  In turn, customers will appreciate the results or updates – and also can react quickly to any potential problems. Team can deliver high value software features in a short time period keeps everyone on top of changing business conditions.

Useful Links and Files

Common

C# Coding Standards

WCF

WCF Introduction

ADO .NET Entity Framework

ASP.NET AJAX

WCF Features

WCF Security

WCF Transactions

WCF COM+, MSMQ Integration

WCF

AJAX

Getting Started With AJAX

This is the first installment of a new online column for Visual Studio Magazine. The column will focus on several key .NET technologies, including ASP.NET AJAX, Silverlight, XML and Web services. It'll provide short to-the-point articles from which you can quickly and easily glean information.
In this article, I'll introduce AJAX, walking you through the concepts behind it, demonstrating how it works, and explaining why you may want to use it in your ASP.NET Web sites.

What Is AJAX?

AJAX, or Asynchronous JavaScript and XML, is an acronym for many technologies, rather than a single technology. It has been around for many years. Back in the old days, you could implement AJAX principles by using remote scripting (which relied on a Java Applet), the Internet Explorer 5 Web Service Behavior (which relied on JavaScript and the XMLHttpRequest object), or IFrames.

Today, most Web applications send data to a server by performing postback operations that reload the entire page. For example, if you click on a button or select an item from a drop-down list, you will see the page reload, even though you may have only requested a small amount of new information.

When Internet Explorer 5 was released, it enabled you to build Web applications that sent requests to the server without requiring full postback operations and page reloads. Instead, you could use it for "partial page updates" or updates to specific parts of a page. Partial page updates offered better performance times and created happier end users.

All modern browsers (including Internet Explorer, FireFox, Safari and Opera) provide partial page updates by supporting AJAX capabilities. AJAX describes the process during which Web pages leverage JavaScript, the XMLHttpRequest and Dynamic HTML (DHTML) to make asynchronous calls to a server and update parts of a page when a response is received. Asynchronous AJAX calls are one or more calls made to a server while a user interacts with a browser that is performing other actions. Making these calls results in richer applications that look and feel more like desktop applications.

While AJAX sounds good in principle, it can be problematic to implement across multiple browsers. Internet Explorer 5 and Internet Explorer 6 rely on built-in ActiveX controls to make AJAX calls, whereas FireFox, Safari, Opera and Internet Explorer 7 use a built-in JavaScript XMLHttpRequest object. To write cross-browser AJAX code, you must understand JavaScript and the different ways browsers can call a server asynchronously.

Listing 1 shows a simple example of how you can make a cross-browser AJAX call to add two numbers and show the result. As you look through the code in Listing 1, you'll see that the GetXmlHttp() function performs several checks to determine how to properly create the XMLHttpRequest object and use it based upon the browser. You can write this code several ways, including using try/catch blocks to load a particular XMLHttpRequest object.

XMLHttpRequest Object in Different Browsers
Listing 1. Accounting for differences in how browsers use the XMLHttpRequest object can be painful. This listing shows a simple cross-browser example of how you can make AJAX requests using the XMLHttpRequest object. The complete code for this example is available in the article's code download.

<script type="text/javascript">

    var xmlHttp = null;

    function Add()

    {

       var x = document.getElementById("txtX").value;

       var y = document.getElementById("txtY").value;

       //Create params string to be posted to server

       var params  = encodeURI("x=" + x + "&y=" + y);

       xmlHttp = GetXmlHttp("POST","XmlHttpHandler.ashx");

       xmlHttp.onreadystatechange = OnStateChange;

       xmlHttp.send(params);

    }

    function OnStateChange()

    {

       if (xmlHttp.readyState == 4)

       {   

           if (xmlHttp.status == 200)

           {

              document.getElementById("lblResult").innerHTML = 

                xmlHttp.responseText;

           }

       }

    }

    function GetXmlHttp(verb,url)

    {

       var request = null;

       //Create XmlHttpxmlHttp object

       var ua = navigator.userAgent.toLowerCase();

       if (window.XMLHttpRequest) //IE7, Safari, Mozilla

       {

           request = new XMLHttpRequest();

       }

       else //IE5, IE6

       {

           if (ua.indexOf('msie 5') == -1)

             request = new ActiveXObject("Msxml2.XMLHTTP");

           else

             request = new ActiveXObject("Microsoft.XMLHTTP");

       }

       request.open(verb.toUpperCase(),url,true);

       if (verb.toUpperCase() == "POST") 

           request.setRequestHeader("Content-type", 

             "application/x-www-form-urlencoded");

       return request;

    }

</script>

Fortunately, you can rely on one of many AJAX frameworks to avoid the complexities of writing AJAX code. Microsoft's recently released ASP.NET AJAX Extensions is a very useful framework. It can greatly simplify the amount of work you have to complete to AJAX-enable a Web page.

ASP.NET AJAX Options

Microsoft has three options for adding AJAX functionality into new or existing Web sites, all of which can be downloaded for free at http://ajax.asp.net. These options include ASP.NET AJAX Extensions, ASP.NET AJAX Control Toolkit and ASP.NET AJAX Futures.

ASP.NET AJAX Extensions provides a complete framework for working with AJAX technologies. It includes a client-side script library as well as several server-side ASP.NET controls. This framework extends existing ASP.NET functionality and provides a familiar development environment for existing ASP.NET application developers. By using the framework, you can AJAX-enable new or existing Web sites with limited understanding of JavaScript and the XMLHttpRequest object.

With the ASP.NET AJAX Control Toolkit, Microsoft released over 30 AJAX-enabled ASP.NET server controls. These controls can perform a variety of AJAX functions, including showing specialized modal pop-ups, reordering lists using drag-and-drop functionality, adding watermarks to textboxes, providing hover menus and more. The toolkit controls are available in a single assembly that can be dropped into your ASP.NET application's bin folder.

Finally, for those of you who like to stay on the cutting edge, Microsoft has also released ASP.NET AJAX Futures. This download contains future AJAX technologies that aren't officially supported in production environments yet. If you're interested in seeing what may be coming, then you can try out this release, but keep in mind that its documentation is limited at this point.


While AJAX is only an acronym, the technologies it references provide a great way to enhance new or existing Web sites, making them more rich, user-friendly and efficient. AJAX can be difficult to implement across different browsers, but AJAX frameworks, such as ASP.NET AJAX, are available to ease the development process. In the next column, I'll show you how to get started using the ASP.NET AJAX Extensions. 

Glossary for WCF

A

Address

The location at which a WCF service can be found. The address incorporates a protocol and URI.

Asynchronous

Not dependent on timing. Each application or command runs in the specified order, but the specified item does not wait for any previously started processes to finish before an application or command runs.

Asynchronous method

A method call that returns to the caller immediately regardless of whether processing has completed. The results of processing are returned through another call on another thread. Asynchronous methods free the caller from having to wait until processing has finished.

Authentication

In .NET Framework security, the process of discovering and verifying the identity of a principal by examining the user's credentials against some authority.

Authorization

In .NET Framework security, the process of limiting access rights by granting or denying specific permissions to an authenticated identity or principal.

---

B

Behavior

In WCF, a way of customizing the behavior of a client or service.

Binding

In WCF, the transport-level details that define how a client interacts with a service.

---

C

Claim

In security, claims are pieces of information held within a credential.

Contract

The behavior and state that a class provides, which is matched with what a client of that class can expect to hold. A contract is expressed partly by the signatures for all public fields, methods, properties, and events of that class. This is augmented by a description (usually in simple descriptive text) of what each field or property represents, together with what each method does.

Credential

In security, a credential carries information about a caller, and is used for authentication.

---

D

Duplex

When applied to a WCF contract, indicates independent two-way communication between client and service.

---

E

Encoder

A class that converts a message into bytes for transmission.

Endpoint

A combination of address, binding and contract.

---

N

NAT

Network Address Translator. Hardware or software that hides local network addresses behind a single IP address, translating incoming and outgoing packets as necessary.

---

S

SAML

Security Access Markup Language provides a way to attach credentials to a message.

Security mode

In WCF, the way in which a message is secured. Transport mode uses the security of the transport, while message mode secures the message itself.

Service-oriented applications

Applications that expose services through interfaces and contracts. Service oriented applications benefit from being loosely coupled.

SOAP

A simple, XML-based protocol for exchanging structured and type information on the Web. The protocol contains no application or transport semantics, which makes it highly modular and extensible.

---

T

Transport

The underlying communication protocol. WCF supports three transports: HTTP, TCP and Named Pipes.

Introduction to WCF Security

How to Configure Message-Based Security

In this demo, we will see how to secure a Windows Communication Foundation Service.

We will also see how to use the service trace utility provided by the Windows SDK to inspect service messages.

Now, what we have here is a very simple application for applying customer credits. This application makes a call to a WCF service for performing the transaction.

And if we look at the application, we can see in the btnApplyCredit_Click event handler, that we are creating a request object which contains account, amount, and transaction ID information.

We then invoke the service and receive a response as to whether or not the transaction was approved.

Let’s run the application. Now we will enter an account number, an amount to be credited, hit apply, and we see that the transaction was successful.

Now let’s take a quick look at the service.

Our service utilizes data contracts for the request and response objects.

As to the service itself, we see that we have an ICredit that defines a credit account method.

Now, our credit service implements the ICredit interface, credit account simply takes the request passed in and creates a response that approves the transaction.

Let’s go enable encryption for our service and also view our service messages in the service trace viewer.

We will go to the binding, we will go to the security tab, and we will specify message level security.

Now, because we are working within a windows domain, we can leave the MessageClientCredentialType set to Windows, and allow Windows to handle the encryption scheme for us.

We will save the config, and now we will go set the security on our client.

We will go to the security tab, we will set the mode to message, just as we did for the service, and now we will enable MessageLogging under Diagnostics.

Once we have enabled message logging it will automatically create a diagnostic source and listener.

If we want to change the location of the output files, we can click on the listener link and change the name.

In this case, we will change it now to c:\wcf logs\credit_service.svclog.

Also, we are going to set message logging to log the entire message so that we can see both the header and the message content.

Now we can save our configuration changes, compile, and run the application.

Now we can see that the application runs successfully without throwing an exception and that our transaction was approved.

Now let’s take a look at our log file in the service trace utility.

When we look at the messages now, we can see that the data for our request and our response is no longer being passed in clear text, but is now encrypted.

So now we have taken care of encrypting our message. However, anyone can access our service, so let’s use WCF to secure the service so only authorised users can invoke it.

So what I am going to do now is add a using statement, for System.Security.Permissions, and now what I am going to do is set permissions on my credit account method, so that only those who are members of the service users group can access it.

And I will do this by using a principal permission attribute setting the security action to demand, and the role equal to service users.

Now because we are not a member of the service users group, the call to this method should fail at runtime.

I will save my application, recompile, then I will run my application; perform my transaction for one final time.

So now you see we have received a security exception because we did not have the proper permissions to invoke this method on the service.

Customizing WCF with Behaviors

How to Configure WCF Behaviors

In this demonstration, you will see how to apply the instancing attributes to your behaviors for your Windows Communication Foundation service. For this demonstration we have an existing application that consists of a service and a client.

The service is hosted on Internet Information Server as a web service.

We have two contracts, or two interfaces in our service, one is a calculator, which has the four standard mathematical methods, add, subtract, multiply, and divide.

We also have an ICalculator instance which implements the ICalculator and this will be responsible for providing us with our ContextMode InstanceId and operationCount values.

Notice that each one of these two contracts of the interfaces in our service have the SessionMode equals SessionMode.Required.

We specify this so that when a client accesses it, we indicate the necessary type of session mode that we wish in our service, and the client will then see that behavior from our service as it is executed.

So very quickly we will take a look at our CalculatorService class which implements our ICalculator instance interface, and we see that we have a static instanceCount variable, and we will see how that plays into our client application as we execute it and we will see those values increment accordingly.

The CalculatorService in the constructor will simply implement our instanceCount and sets the InstanceId equal to the instanceCount and then we have our four methods add, subtract, multiply, and divide, and then for our ICalculator instance, we implement the GetInstanceContextMode method here, our GetInstanceId method, and our GetOperationCount method in here.

So, one of the first things that we need to do is to make sure that we have the appropriate attribute uncommented in our code.

The first one we will make use of is the PerSession attribute, where the PerSession will create an instance per channel session.

So, if we take a look at our client application, it is a console based application, we create an instance of our calculator instance client.

We then get the instance mode from the GetInstanceContextMode, write that value out to our screen, and then start calling the methods and new calculations.

Notice in the first one, that we are using a client called client, so this is our first instance, and for the second instance we will have a client called client2.

We will call the new calculations with client1 and then client2, closing outer clients here, and writing out the necessary information to terminate the client information.

And the new calculations simply call each one of our operations, add service, subtract service, multiply service, we pass in the necessary values, we call the add method, or we call the subtract method as appropriate.

In our output we will write out values that we are going to be adding, subtracting, multiplying or dividing, the result. We will grab the client.InstanceId and GetOperationCount.

So, let’s go ahead and run our client application, and we will see what our first generated output results in.

So here we have used PerSession, as you can see, InstanceContextMode indicates PerSession, we see that we have called the add method, passed in the two values, got a result back, notice our InstanceId is one, and our operationCount is one.

So we have called add, subtract, multiply, and divide, all from instance one and we can see that with the instance IDs.

For each one of these same instances that we have called, we see that our operationCount increments.

Then we went ahead and used client2 to call the same methods again, notice that our instance ID has now changed to two, and that each of our operationCounts has been incremented again within that instance.

So you can see that we have really established two sessions here, and each one of the sessions maintained its own operationCount.

Let’s go ahead and terminate that client, we will come back to our service, and we will comment out the PerSession call and we will now change our attribute to a PerCall InstanceContextMode.

Note that when we change these because we have made changes in the actual attribute that is applied to the service, we need to rebuild that service one more time, and now we can go ahead and run our client application on a PerCall basis.

Notice that our InstanceContextMode indicates that we have executed a PerCall, and you will also note as we take a look at the InstanceId for each method call, regardless of the client that called it, the InstanceId has changed.

So each time we have made a call with a client, regardless of which client it was, we have actually generated a new InstanceId for each and every call.

Also take note, that because the instance changes for every call, the operationCount never gets incremented and always remains at zero. So, once again this is the client using the PerCall method.

Let’s go ahead and terminate that client. And for the final part, comment out our PerCall attribute, uncomment the Single, rebuild our service one more time, and we will start the client application again.

So our InstanceContextMode now, has been changed to Single, again we are still calling the same operations, but you will notice that regardless of the client that is making the call, and regardless of the method that is being called, our InstanceID remains at one, so this is a single instance that is servicing all clients, and you will also notice that the operationCount increments from beginning to end regardless of the number of clients that are actually calling it.

So if we were to add client3, and have client3 call one of these methods again, our InstanceID will still remain as one, but we would also notice the values of nine, ten, eleven, and twelve in our operationsCounts incremented.

So in this demonstration, you have seen how to apply the three different attributes to a service behaviour for your Windows Communication Foundation service.

Creating and Invoking a WCF Client

How to Create a Client by Generating a Proxy

In this demonstration you will see how to create a client application to access a Windows Communication Foundation Service by generating a proxy.

In our client application, we have a label, text box, and a button. When a user enters a name into the text box, and clicks the button, a message box will be displayed showing a greeting with a name entered into the text box.

We need to add a reference to our client application, and in order to do so, we need to have our Endpoint available. This requires us to go in and edit the service behavior that we created in our previous demonstration.

Another serviceMetadata element, we notice that our HttpGetEnabled tag is set to False. We need to enable that, so we will set it to True, save our changes, and come back to Visual Studio.

We will start the service application, and once our service application is up and running, we switch back to Visual Studio. We can now add a service reference to our client application.

We simply type in the URL that was specified in our service application, and provide a service namespace reference, and call this GreetingServices. And this will cause our client application to go and search to the endpoint that we have specified, locate the necessary metadata, and generate the appropriate proxy for us.

So you can see that our client application now contains a System.ServiceModel reference, as well as our new service reference GreetingServices.app.

The proxy has been generated for us automatically, GreetingServices.cs. And as we scroll through our code we can see that it has actually generated a greeting client partial class for us, and we can utilise that in our application.

So let’s go ahead and start debugging, and we will switch back to the code for our client application.

Let’s go ahead and add a using statement here, WCFClient.GreetingServices, so we will add that namespace.

Now we want to add a new proxy client variable, so we will call this GreetingClient proxy. We need to create the proxy to the service, so proxy equals new GreetingClient.

And you will notice that we have five options available to us for the constructor for our GreetingClient. For this demonstration, we are only going to use the default constructor.

So now that we have our proxy created, we are going to call the necessary service, and store the value into the response variable, so the response equals proxy.GetGreeting, and of course the value that we are concerned with, will be displayed in the text box txtName.

And we have a little catch block in the event that we have in the issue we are trying to get the data from our service. The message box will show the response, and of course, when our form is closing, we want to go through the process of closing out our proxy service, so we will close that here.

Now in order to make both of these function, we need to go and make some changes to our solution. We will set our start-up project, so we will have our client and our demo both starting simultaneously, save our changes, and let’s go ahead and run our application and see how it works.

So we see our services running in the background. Here is our client application.

So let’s say hello to Fred Flinstone this morning. We click on Display Greeting, an error message box pops up with the greeting displayed.

So that it is it for our client application that we have generated within Visual Studio. For finer control, Visual Studio also makes available the svcutil client. The svcutil is a tool that you can execute from the command line, and it contains multiple options based on the specific type of configuration you will want to create.

Notice that we have some common options available, some code generation, metadata export options, service validation options, metadata download options, XmlSerializer type generation options, and a few examples in Help file that will show you how to make use of the svcutil command.

This allows a much finer control over the generation of the proxy. For our purposes, in this demonstration, we are simply going to generate the same files that Visual Studio did. So we will use the svcutil connecting to our local host. Note that our service is actually still running.

We will connect to that endpoint, and we will have the svcutil go ahead and download the metadata from our service, and then it will generate the two files that we have available, the app.config and our Greeting.cs.

Notice that it is called an output config.

So if we use notepad to look at our Greeting.cs file, you can see that it has created the same code that was generated in Visual Studio.

We can also use notepad to view the output.config file, and see the values that were generated in the config that we would apply to our client application. This was generated using the svcutil class.

So in this demonstration you have seen how to create a client application for accessing your Windows Communication Foundation Service by generating proxy information.

Creating a WCF Service

How to Create a Basic WCF Service

In this demonstration you will see how to create a basic Window’s Communication Foundation service.

Let’s go ahead and create new console-based application using C# language called WCF demo.

We need to add a reference to the system.ServiceModel name space, as well as a using statement at the top of our code module.

Then we can get started by first creating an interface in our program, and we will call this interface IGreeting. IGreeting will contain one method GetGreeting that will return a string value and accept a string value.

In order to make our interface a contract for communication service, we need to apply the necessary attributes. In this case, we apply the ServiceContract attribute to the actual interface itself, and any methods that we have inside of our interface need to have the OperationContract attribute applied to those as well.

So now our interface is complete, and we essentially have the workings of our contract in place. We will now create a class called Greeting that will implement the IGreeting interface.

And we will add a functionality here to simply return a simple text string concatenated with the value that gets passed in, and we are all set to go ahead and start creating our service host.

So we will create a ServiceHost instance. Note that we have two options available here: we can use a singleton instance of an object, or we can get at the type of our specific service and utilize that.

We will use the typeofGreeting, and we will create a new URI because we are actually hosting this on our local machine. The URI will consist of local host with a port number, and of course, our service name.

We can now open our host, and we can let the user know that the host is actually up and running.

And we can inform the user how they can shut down our host, and wait for Visual Studio or the user rather to press the Enter key, and at which point in time, we will close our host and the service will shut down gracefully.

Now that we have completed writing the code for this, we need to go ahead and build our project so that we can actually create the executable file, which will be required in our next step.

So now that we have created that functionality, we need to go to our project and we need to add an App.config file.

So we will add the Application Configuration file to our project, and you will currently notice that App.config is very generic and only has the opening and closing tags with nothing in between.

We are going to go ahead now, and go through the process of running the Edit WCF Configuration wizard, which will allow us to apply the necessary settings to our App.config file.

The first step is to create a new service. This is where we had to build our project first so that our WCFDemo.exe does exist, and we can get at our service that we have created inside. Also note that we now want to look at the service contract that we will be using because we only created the one interface within our application with the attributes applied to it, IGreeting is the only one that shows up.

This time we will base our communication mode on TCP rather than HTTP. Again it was local host. We are going to specify a port of 1234 this time around, and Greeting is the address for the Endpoint of our particular service.

So the wizard has created that for us. We will click on finish. Note that we have our hosting Endpoints available.

We need to next create a Service Behavior, and our Service Behavior Configuration we are going to utilise the serviceMetadata element.

We will add the serviceMetadata element to our Service Behavior. Note that it is called NewBehavior. We come back up to our WCFDemo.Greeting service, apply the new behaviour to that, save the file, and then go back to Visual Studio and very quickly open our App.config file, and you can see the changes that have taken place inside our App.config file.

So let’s go ahead and run this and see how our application behaves. Currently we have no clients that will be accessing this. The service just simply comes up and runs. It says the service host is running, press ENTER to close the host, and we are finished.

Type of Keys in SQL

Types of SQL Keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

1.   Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys.

2.   Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

3.   Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

4.   Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

5.   Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

6.   Unique Key

Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer the article Difference between primary key and unique key.

7.   Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.

Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Pivot Query:

select * from

(

select c.rolename as rolename ,b.username as username from dbo.aspnet_UsersInRoles as A

inner join aspnet_users as b on A.userid=b.userid

inner join aspnet_roles as c on c.roleid=a.roleid

) as SourceTable

pivot

(

count(username)

for rolename in ([admin],[learner])

) as pivottable

Common Table Expressions(CTE) in SQL SERVER 2008:

With T(Address, Name, Age)  --Column names for Temporary table

AS

(

SELECT A.Address, E.Name, E.Age from Address A

INNER JOIN EMP E ON E.EID = A.EID

)

SELECT * FROM T  --SELECT or USE CTE temporary Table

WHERE T.Age > 50

ORDER BY T.NAME

 When to Use Common Table Expressions :

·         Create a recursive query. 

WITH ShowMessage(STATEMENT, LENGTH)

AS

(

SELECT STATEMENT = CAST('I Like ' AS VARCHAR(300)), LEN('I Like ')

UNION ALL

SELECT

CAST(STATEMENT + 'CodeProject! ' AS VARCHAR(300))

, LEN(STATEMENT) FROM ShowMessage

WHERE LENGTH < 300

)

SELECT STATEMENT, LENGTH FROM ShowMessage

• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement. 

LINQ Questions & Answers

What is Language Integrated Query (LINQ)?

LINQ is a programming model that is the composition of general-purpose standard query operators that allow you to work with data, regardless of the data source in any .NET based programming language. It is the name given to a set of technologies based on the integration of query capabilities into any .NET language.

What are LINQ query expressions?

A LINQ query, also known as a query expression, consists of a combination of query clauses that identify the data sources for the query. It includes instructions for sorting, filtering, grouping, or joining to apply to the source data. The LINQ query expressions syntax is similar to the SQL syntax. It specifies what information should be retrieved from the data source.

Write the basic steps to execute a LINQ query.

The following are the three basic steps to execute a LINQ query:

• Obtain the data source (The data source can be either an SQL database or an XML file)
• Create a query
• Execute the query

Write the basic syntax of a LINQ query in Visual Basic as well as in C#.

In Visual Basic, the basic syntax of a LINQ query starts with the From clause and ends with the Select or Group By clause. In addition, you can use the Where, Order By, and Order By Descending clauses to perform additional functions, such as filtering data and generating the data in a specific order.

In C#, the basic syntax of a LINQ query starts with the From clause and ends with the Select or group by clause. In addition, you can use the where, orderby, and Orderby descending clauses to perform additional functions, such as filtering data and generating the data in a specific order.

In which statement the LINQ query is executed?

A LINQ query is executed in the For Each statement in Visual Basic and in the foreach statement in C#.

In LINQ, lambda expressions underlie many of the standard query operators. Is it True or False?

It is true.

What is PLINQ?

PLINQ stands for Parallel Language Integrated Query. It is the parallel implementation of LINQ, in which a query can be executed by using multiple processors. PLINQ ensures the scalability of software on parallel processors in the execution environment. It is used where data grows rapidly, such as in telecom industry or where data is heterogeneous.

PLINQ also supports all the operators of LINQ. In addition, you can query 'collections by using PLINQ. It can also run several LINQ queries simultaneously and makes use of the processors on the system. Apart from this, PLINQ uses parallel execution, which helps in running the queries quickly. Parallel execution provides a major performance improvement to PLINQ over certain types of legacy code, which takes too much time to execute.

What are the different Visual Basic features that support LINQ?

Visual Basic includes the following features that support LINQ:

• Anonymous types - Enables you to create a new type based on a query result.
• Implicitly typed variables - Enables the compiler to infer and assign a type when you declare and initialize a variable.
• Extension method - Enables you to extend an existing type with your own methods without modifying the type itself.

What is the function of the DISTINCT clause in a LINQ query?

The DISTINCT clause returns the result set without the duplicate values.

What is the DataContext class and how is it related to LINQ?

After you add a LINQ to SQL Classes item to a project and open the O/R Designer, the empty design surface represents an empty DataContext class ready to be configured. The DataContext class is a LINQ to SQL class that acts as a conduit between a SQL Server database and the LINQ to SQL entity classes mapped to that database. This class contains the connection string information and the methods for connecting to a database and manipulating the data in the database. It is configured with connection information provided by the first item that is dragged onto the design surface.

What is the difference between the Take and Skip clauses?

The Take clause returns a specified number of elements. For example, you can use the Take clause to return two values from an array of numbers. The Skip clause skips the specified number of elements in the query and returns the rest. For example, you can use the Skip clause to skip the first four strings in an array of strings and return the remaining array of string.

What is Object Relational Designer (0/R Designer)?

The 0/R Designer provides a visual design surface to create LINQ to SQL entity classes and associations (relationships) that are based on objects in a database.

Which interface implements the standard query operators in LINQ?

The standard query operators implement the IEnumerable<T> or the IQueryable<T> interface in C# and the IEnumerable(Of T) or the IQueryable(Of T) interface in Visual Basic.

What are standard query operators in LINQ?

The standard query operators in LINQ are the extension methods that form the LINQ pattern. These operators form an API that enables querying of any .NET array or collection. It operates on sequences and allows you to perform operations, such as determining if a value exists in the sequence and performing an aggregated function, such as a summation over a sequence.

On what parameter does the GroupBy clause group the data?

The GroupBy clause groups the elements that share a common attribute.

What is a LinqDataSource control?

The LinqDataSource control enables you to use LINQ. in an ASP.NET Web page by setting the properties in the markup text. You can use the control retrieve or modify data. It is similar to the SqIDataSource and ObjectDataSource controls in the sense that it can be used to declaratively bind other ASP.NET controls on a page to a data source. The difference is that instead of binding directly to a database or to a generic class, the LinqDataSource control is designed to bind a LINQ enabled data model.

How can you open the O/R Designer?

You can open the O/R Designer by adding a new LINQ to SQL Classes item to a project.

The standard query operators are themselves a set of extension methods that provide the LINQ query functionality for any type that implements the IEnumerable<T> interface in Visual Basic. Is it True or False?

False, as it implements the IEnumerable(T) interface in Visual Basic and the IEnumerable<T> interface is implemented in C#.

What are lambda expressions in LINQ?

A lambda expression is a function without a name that calculates and returns a single value. All lambda expressions use the lambda operator =>, which read as goes to. The left side of the lambda operator specifies the input parameters and the right side holds the expression or statement block.

Before you query a DataSet object by using LINQ to DataSet, you must first populate the dataset How can you do this?

You can load the data into the dataset by using different methods, such as:

• Using the DataAdapter class
• Using LINQ to SQL

What are the different implementations of LINQ?

The different implementations of LINQ are:

• LINQ to SQL - Refers to a component of.NET Framework version 3.5 that provides a run-time infrastructure to manage relational data as objects.
• LINQ to DataSet - Refers to a component that makes it easier and faster to query over data cached in a DataSet object.
• LINQ to XML - Provides an in-memory XML programming interface.
• LINQ to Objects - Refers to the use of LINQ queries with any IEnumerable or IEnumerable(T) collection directly, without the use of an intermediate LINQ provider or API, such as LINQ to SQL or LINQ to XML.

Which command-line tool generates code and mapping for the LINQ to SQL component of .NET Framework?

The SqlMetal.exe command-line tool generates code and map the LINQ to SQL component.

Name the control that exposes the LINQ features to Web developers through the ASP.NET data-source control architecture.

The LinqDataSource control exposes the LINQ features to Web developers through the ASP.NET data-source control architecture.

What is the difference between the Select clause and SelectMany() method in LINQ?

Both the Select clause and SelectMany() method are used to produce a result value from a source of values. The difference lies in the result set. The Select clause is used to produce one result value for every source value. The result value is a collection that has the same number of elements from the query. In contrast, the SelectMany() method produces a single result that contains a concatenated collection from the query.

Which extension method do you need to run a parallel query in PLINQ?

The AsParallel extension method is required to run a parallel query in PLINQ.