Selecting data from two different servers in SQL Server

exec sp_addlinkedserver @server = 'MainServer'

select * from [MainServer].[TestDB].[dbo].[Emp]

Selecting SQL Data with Non-Duplicate Column Values

  select A.ClaimNo
from ClaimsDetails A
join ClaimsDetails B
on A.ClaimNo=B.ClaimNo
where B.IsActive='N'
group by A.ClaimNo
having COUNT(A.ClaimNo) = 1

select from another database dynamic query sql server

DECLARE @sql NVARCHAR(1000), @dbName varchar(50),@someValue INT SET @dbName ='OperationalDB' SET @sql = 'SELECT top 10 * FROM ' + QUOTENAME(@dbName) + '..Employee' --EXEC @sql - It will not work EXEC sp_ExecuteSQL @sql _ It will work

EXISTS in sql server

SELECT Name FROM Production.Product WHERE EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')

How to use table name in dymanic query

Declare @tableName VARCHAR(100), @SQLString NVARCHAR(MAX) SET @tableName ='employee' SELECT @SQLString ='Select * from ' + QuoteName(@tableName) EXEC @SQLString

How to get max record from each department SQL SERVER

with cte as ( select *, rank() over (partition by User_Name order by Start_Timestamp desc) as [r] from AUDIT_EVENT ) select User_Name,Start_Timestamp from cte where [r] = 1;

Check services on remote server

class Program { static void Main() { ManagementScope scope = new ManagementScope("\\\\172.29.0.213"); //LONRMS0028 scope.Connect(); ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Process"); ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); foreach (ManagementObject oReturn in searcher.Get()) { string state = oReturn.Properties["State"].Value.ToString().Trim(); } } }

LINQ To XML Tutorials with Examples

For this article, we will be using a sample file called ‘Employees.xml’ for all our samples which is available with the source code. So make sure you keep it handy with you while are practicing these examples. The mark up for Employees.xml is as follows: 1 Sam Male 423-555-0124 424-555-0545

7A Cox Street Acampo CA 95220 USA

2 Lucy Female 143-555-0763 434-555-0567

Jess Bay Alta CA 95701 USA

3 Kate Female 166-555-0231 233-555-0442

23 Boxen Street Milford CA 96121 USA

4 Chris Male 564-555-0122 442-555-0154

124 Kutbay Montara CA 94037 USA

The application is a console application targeting .NET 3.5 framework, although you can use the latest .NET 4.0 framework too. I have also used ‘query expressions’, instead of Lambda expression in these samples. It is just a matter of preference and you are free to use any of these. This tutorial has been divided into 2 sections: Section 1: Read XML and Traverse the Document using LINQ To XML Section 2: Manipulate XML content and Persist the changes using LINQ To XML The following namespaces are needed while testing the samples: System; System.Collections.Generic; System.Linq; System.Text; System.Xml; System.Xml.Linq; Go grab a hot cup of coffee, put on your developer cap and let us get started: Section 1: Read XML and Traverse the XML Document using LINQ To XML 1. How Do I Read XML using LINQ to XML There are two ways to do so: Using the XElement class or the XDocument class. Both the classes contain the ‘Load()’ method which accepts a file, a URL or XMLReader and allows XML to be loaded. The primary difference between both the classes is that an XDocument can contain XML declaration, XML Document Type (DTD) and processing instructions. Moreover an XDocument contains one root XElement. Using XElement C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable employees = xelement.Elements(); // Read the entire XML foreach (var employee in employees) { Console.WriteLine(employee); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xelement.Elements() ' Read the entire XML For Each employee In employees Console.WriteLine(employee) Next employee Output: Using XDocument C# XDocument xdocument = XDocument.Load("..\\..\\Employees.xml"); IEnumerable employees = xdocument.Elements(); foreach (var employee in employees) { Console.WriteLine(employee); } VB.NET (Converted Code) Dim xdocument As XDocument = XDocument.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xdocument.Elements() For Each employee In employees Console.WriteLine(employee) Next employee Output: Note 1: As you can observe, XDocument contains a single root element (Employees). Note 2: In order to generate an output similar to the one using XElement, use “xdocument.Root.Elements()” instead of “xdocument.Elements()” Note 3: VB.NET users can use a new feature called XML Literal. 2. How Do I Access a Single Element using LINQ to XML Let us see how to access the name of all the Employees and list them over here C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable employees = xelement.Elements(); Console.WriteLine("List of all Employee Names :"); foreach (var employee in employees) { Console.WriteLine(employee.Element("Name").Value); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xelement.Elements() Console.WriteLine("List of all Employee Names :") For Each employee In employees Console.WriteLine(employee.Element("Name").Value) Next employee Output: 3. How Do I Access Multiple Elements using LINQ to XML Let us see how to access the name of all Employees and also list the ID along with it C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable employees = xelement.Elements(); Console.WriteLine("List of all Employee Names along with their ID:"); foreach (var employee in employees) { Console.WriteLine("{0} has Employee ID {1}", employee.Element("Name").Value, employee.Element("EmpId").Value); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim employees As IEnumerable(Of XElement) = xelement.Elements() Console.WriteLine("List of all Employee Names along with their ID:") For Each employee In employees Console.WriteLine("{0} has Employee ID {1}", employee.Element("Name").Value, employee.Element("EmpId").Value) Next employee Output: 4. How Do I Access all Elements having a Specific Attribute using LINQ to XML Let us see how to access details of all Female Employees C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var name = from nm in xelement.Elements("Employee") where (string)nm.Element("Sex") == "Female" select nm; Console.WriteLine("Details of Female Employees:"); foreach (XElement xEle in name) Console.WriteLine(xEle); VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim name = _ From nm In xelement.Elements("Employee") _ Where CStr(nm.Element("Sex")) = "Female" _ Select nm Console.WriteLine("Details of Female Employees:") For Each xEle As XElement In name Console.WriteLine(xEle) Next xEle Output: 5. How Do I access Specific Element having a Specific Attribute using LINQ to XML Let us see how to list all the Home Phone Nos. C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var homePhone = from phoneno in xelement.Elements("Employee") where (string)phoneno.Element("Phone").Attribute("Type") == "Home" select phoneno; Console.WriteLine("List HomePhone Nos."); foreach (XElement xEle in homePhone) { Console.WriteLine(xEle.Element("Phone").Value); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim homePhone = _ From phoneno In xelement.Elements("Employee") _ Where CStr(phoneno.Element("Phone").Attribute("Type")) = "Home" _ Select phoneno Console.WriteLine("List HomePhone Nos.") For Each xEle As XElement In homePhone Console.WriteLine(xEle.Element("Phone").Value) Next xEle Output: 6. How Do I Find an Element within another Element using LINQ to XML Let us see how to find the details of Employees living in 'Alta' City C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var addresses = from address in xelement.Elements("Employee") where (string)address.Element("Address").Element("City") == "Alta" select address; Console.WriteLine("Details of Employees living in Alta City"); foreach (XElement xEle in addresses) Console.WriteLine(xEle); VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim addresses = _ From address In xelement.Elements("Employee") _ Where CStr(address.Element("Address").Element("City")) = "Alta" _ Select address Console.WriteLine("Details of Employees living in Alta City") For Each xEle As XElement In addresses Console.WriteLine(xEle) Next xEle Output: 7. How Do I Find Nested Elements (using Descendants Axis) using LINQ to XML Let us see how to list all the zip codes in the XML file C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); Console.WriteLine("List of all Zip Codes"); foreach (XElement xEle in xelement.Descendants("Zip")) { Console.WriteLine((string)xEle); } VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Console.WriteLine("List of all Zip Codes") For Each xEle As XElement In xelement.Descendants("Zip") Console.WriteLine(CStr(xEle)) Next xEle Output: 8. How do I apply Sorting on Elements using LINQ to XML Let us see how to List and Sort all Zip Codes in ascending order C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); IEnumerable codes = from code in xelement.Elements("Employee") let zip = (string)code.Element("Address").Element("Zip") orderby zip select zip; Console.WriteLine("List and Sort all Zip Codes"); foreach (string zp in codes) Console.WriteLine(zp); VB.NET (Converted Code) Dim xelement As XElement = XElement.Load("..\..\Employees.xml") Dim codes As IEnumerable(Of String) = _ From code In xelement.Elements("Employee") _ Let zip = CStr(code.Element("Address").Element("Zip")) _ Order By zip _ Select zip Console.WriteLine("List and Sort all Zip Codes") For Each zp As String In codes Console.WriteLine(zp) Next zp Output: Section 2: Manipulate XML content and Persist the changes using LINQ To XML 9. Create an XML Document with Xml Declaration/Namespace/Comments using LINQ to XML When you need to create an XML document containing XML declaration, XML Document Type (DTD) and processing instructions, Comments, Namespaces, you should go in for the XDocument class. C# XNamespace empNM = "urn:lst-emp:emp"; XDocument xDoc = new XDocument( new XDeclaration("1.0", "UTF-16", null), new XElement(empNM + "Employees", new XElement("Employee", new XComment("Only 3 elements for demo purposes"), new XElement("EmpId", "5"), new XElement("Name", "Kimmy"), new XElement("Sex", "Female") ))); StringWriter sw = new StringWriter(); xDoc.Save(sw); Console.WriteLine(sw); VB.NET (Converted Code) Dim empNM As XNamespace = "urn:lst-emp:emp" Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-16", Nothing), _ New XElement(empNM + "Employees", _ New XElement("Employee", _ New XComment("Only 3 elements for demo purposes"), _ New XElement("EmpId", "5"), _ New XElement("Name", "Kimmy"), _ New XElement("Sex", "Female")))) Dim sw As New StringWriter() xDoc.Save(sw) Console.WriteLine(sw) 10. Save the XML Document to a XMLWriter or to the disk using LINQ to XML Use the following code to save the XML to a XMLWriter or to your physical disk C# XNamespace empNM = "urn:lst-emp:emp"; XDocument xDoc = new XDocument( new XDeclaration("1.0", "UTF-16", null), new XElement(empNM + "Employees", new XElement("Employee", new XComment("Only 3 elements for demo purposes"), new XElement("EmpId", "5"), new XElement("Name", "Kimmy"), new XElement("Sex", "Female") ))); StringWriter sw = new StringWriter(); XmlWriter xWrite = XmlWriter.Create(sw); xDoc.Save(xWrite); xWrite.Close(); // Save to Disk xDoc.Save("C:\\Something.xml"); Console.WriteLine("Saved"); VB.NET (Converted Code) Dim empNM As XNamespace = "urn:lst-emp:emp" Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-16", Nothing),_ New XElement(empNM + "Employees", _ New XElement("Employee", _ New XComment("Only 3 elements for demo purposes"), _ New XElement("EmpId", "5"), _ New XElement("Name", "Kimmy"), _ New XElement("Sex", "Female")))) Dim sw As New StringWriter() Dim xWrite As XmlWriter = XmlWriter.Create(sw) xDoc.Save(xWrite) xWrite.Close() ' Save to Disk xDoc.Save("C:\Something.xml") Console.WriteLine("Saved") 11. Load an XML Document using XML Reader using LINQ to XML Use the following code to load the XML Document into an XML Reader C# XmlReader xRead = XmlReader.Create(@"..\\..\\Employees.xml"); XElement xEle = XElement.Load(xRead); Console.WriteLine(xEle); xRead.Close(); VB.NET (Converted Code) Dim xRead As XmlReader = XmlReader.Create("..\\..\\Employees.xml") Dim xEle As XElement = XElement.Load(xRead) Console.WriteLine(xEle) xRead.Close() 12. Find Element at a Specific Position using LINQ to XML Find the 2nd Employee Element C# // Using XElement Console.WriteLine("Using XElement"); XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emp1 = xEle.Descendants("Employee").ElementAt(1); Console.WriteLine(emp); Console.WriteLine("------------"); //// Using XDocument Console.WriteLine("Using XDocument"); XDocument xDoc = XDocument.Load("..\\..\\Employees.xml"); var emp1 = xDoc.Descendants("Employee").ElementAt(1); Console.WriteLine(emp); VB.NET (Converted Code) ' Using XElement Console.WriteLine("Using XElement") Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emp1 = xEle.Descendants("Employee").ElementAt(1) Console.WriteLine(emp) Console.WriteLine("------------") '// Using XDocument Console.WriteLine("Using XDocument") Dim xDoc As XDocument = XDocument.Load("..\..\Employees.xml") Dim emp1 = xDoc.Descendants("Employee").ElementAt(1) Console.WriteLine(emp) 13. List the First 2 Elements using LINQ to XML List the details of the first 2 Employees C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Take(2); foreach (var emp in emps) Console.WriteLine(emp); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Take(2) For Each emp In emps Console.WriteLine(emp) Next emp 14. List the 2nd and 3rd Element using LINQ to XML List the 2nd and 3rd Employees C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Skip(1).Take(2); foreach (var emp in emps) Console.WriteLine(emp); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Skip(1).Take(2) For Each emp In emps Console.WriteLine(emp) Next emp 15. List the Last 2 Elements using LINQ To XML We have been posting the entire elements as output in our previous examples. Let us say that you want to display only the Employee Name, use this query: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Reverse().Take(2); foreach (var emp in emps) Console.WriteLine(emp.Element("EmpId") + "" + emp.Element("Name")); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Reverse().Take(2) For Each emp In emps Console.WriteLine(emp.Element("EmpId") + emp.Element("Name")) Next emp To display only the values without the XML tags, use the ‘Value’ property C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Reverse().Take(2); foreach (var emp in emps) Console.WriteLine(emp.Element("EmpId").Value + ". " + emp.Element("Name").Value); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Reverse().Take(2) For Each emp In emps Console.WriteLine(emp.Element("EmpId").Value & ". " & emp.Element("Name").Value) Next emp If you notice, the results are not ordered i.e. the Employee 4 is printed before 3. To order the results, just add call Reverse() again while filtering as shown below: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee").Reverse().Take(2).Reverse(); foreach (var emp in emps) Console.WriteLine(emp.Element("EmpId").Value + ". " + emp.Element("Name").Value); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee").Reverse().Take(2).Reverse() For Each emp In emps Console.WriteLine(emp.Element("EmpId").Value & ". " & emp.Element("Name").Value) Next emp 16. Find the Element Count based on a condition using LINQ to XML Count the number of Employees living in the state CA C# XElement xelement = XElement.Load("..\\..\\Employees.xml"); var stCnt = from address in xelement.Elements("Employee") where (string)address.Element("Address").Element("State") == "CA" select address; Console.WriteLine("No of Employees living in CA State are {0}", stCnt.Count()); VB.NET (Converted Code) XElement xelement = XElement.Load("..\\..\\Employees.xml"); var stCnt = from address in xelement.Elements("Employee") where (string)address.Element("Address").Element("State") == "CA" select address; Console.WriteLine("No of Employees living in CA State are {0}", stCnt.Count()); 17. Add a new Element at runtime using LINQ to XML You can add a new Element to an XML document at runtime by using the Add() method of XElement. The new Element gets added as the last element of the XML document. C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.Add(new XElement("Employee", new XElement("EmpId", 5), new XElement("Name", "George"))); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.Add(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Name", "George"))) Console.Write(xEle) 18. Add a new Element as the First Child using LINQ to XML In the previous example, by default the new Element gets added to the end of the XML document. If you want to add the Element as the First Child, use the ‘AddFirst()’ method C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.AddFirst(new XElement("Employee", new XElement("EmpId", 5), new XElement("Name", "George"))); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.AddFirst(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Name", "George"))) Console.Write(xEle) 19. Add an attribute to an Element using LINQ to XML To add an attribute to an Element, use the following code: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.Add(new XElement("Employee", new XElement("EmpId", 5), new XElement("Phone", "423-555-4224", new XAttribute("Type", "Home")))); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.Add(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Phone", "423-555-4224", _ New XAttribute("Type", "Home")))) Console.Write(xEle) 20. Replace Contents of an Element/Elements using LINQ to XML Let us say that in the XML file, you want to change the Country from “USA” to “United States of America” for all the Elements. Here’s how to do so: C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var countries = xEle.Elements("Employee").Elements("Address").Elements("Country").ToList(); foreach (XElement cEle in countries) cEle.ReplaceNodes("United States Of America"); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim countries = xEle.Elements("Employee").Elements("Address").Elements("Country").ToList() For Each cEle As XElement In countries cEle.ReplaceNodes("United States Of America") Next cEle Console.Write(xEle) 21. Remove an attribute from all the Elements using LINQ to XML Let us say if you want to remove the Type attribute ( ) attribute for all the elements, then here’s how to do it. C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var phone = xEle.Elements("Employee").Elements("Phone").ToList(); foreach (XElement pEle in phone) pEle.RemoveAttributes(); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim phone = xEle.Elements("Employee").Elements("Phone").ToList() For Each pEle As XElement In phone pEle.RemoveAttributes() Next pEle Console.Write(xEle) To remove attribute of one Element based on a condition, traverse to that Element and SetAttributeValue("Type", null); You can also use SetAttributeValue(XName,object) to update an attribute value. 22. Delete an Element based on a condition using LINQ to XML If you want to delete an entire element based on a condition, here’s how to do it. We are deleting the entire Address Element C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var addr = xEle.Elements("Employee").ToList(); foreach (XElement addEle in addr) addEle.SetElementValue("Address", null); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim addr = xEle.Elements("Employee").ToList() For Each addEle As XElement In addr addEle.SetElementValue("Address", Nothing) Next addEle Console.Write(xEle) SetElementValue() can also be used to Update the content of an Element. 23. Remove ‘n’ number of Elements using LINQ to XML If you have a requirement where you have to remove ‘n’ number of Elements; For E.g. To remove the last 2 Elements, then here’s how to do it C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); var emps = xEle.Descendants("Employee"); emps.Reverse().Take(2).Remove(); Console.Write(xEle); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") Dim emps = xEle.Descendants("Employee") emps.Reverse().Take(2).Remove() Console.Write(xEle) 24. Save/Persists Changes to the XML using LINQ to XML All the manipulations we have done so far were in the memory and were not persisted in the XML file. If you have been wondering how to persist changes to the XML, once it is modified, then here’s how to do so. It’s quite simple. You just need to call the Save() method. It’s also worth observing that the structure of the code shown below is similar to the structure of the end result (XML document). That’s one of the benefits of LINQ to XML, that it makes life easier for developers by making it so easy to create and structure XML documents. C# XElement xEle = XElement.Load("..\\..\\Employees.xml"); xEle.Add(new XElement("Employee", new XElement("EmpId", 5), new XElement("Name", "George"), new XElement("Sex", "Male"), new XElement("Phone", "423-555-4224", new XAttribute("Type", "Home")), new XElement("Phone", "424-555-0545", new XAttribute("Type", "Work")), new XElement("Address", new XElement("Street", "Fred Park, East Bay"), new XElement("City", "Acampo"), new XElement("State", "CA"), new XElement("Zip", "95220"), new XElement("Country", "USA")))); xEle.Save("..\\..\\Employees.xml"); Console.WriteLine(xEle); Console.ReadLine(); VB.NET (Converted Code) Dim xEle As XElement = XElement.Load("..\..\Employees.xml") xEle.Add(New XElement("Employee", _ New XElement("EmpId", 5), _ New XElement("Name", "George"), _ New XElement("Sex", "Male"), _ New XElement("Phone", "423-555-4224", _ New XAttribute("Type", "Home")), _ New XElement("Phone", "424-555-0545", _ New XAttribute("Type", "Work")), _ New XElement("Address", _ New XElement("Street", "Fred Park, East Bay"), _ New XElement("City", "Acampo"), _ New XElement("State", "CA"), _ New XElement("Zip", "95220"), _ New XElement("Country", "USA")))) xEle.Save("..\..\Employees.xml") Console.WriteLine(xEle) Console.ReadLine() Well with that, we conclude this long article of some 'How Do I' operations while using LINQ to XML. Through this article, we have only attempted to scratch the surface of what can be done using LINQ to XML. LINQ to XML is an amazing API and I hope this set of examples has demonstrated that. The entire source of the article in C# and VB.NET can be downloaded over here. The VB.NET code has been translated using a C# to VB.NET Converting tool.

Insert an explicit value into a timestamp column

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. Suppose we have created OrderHistory table in current database. CREATE TABLE OrderHistory( OrderId BIGINT PRIMARY KEY, OrderDate TIMESTAMP ) Now if will try to insert some records into the OrderHistory table: INSERT INTO OrderHistory(OrderId,OrderDate) VALUES(100,GETDATE()) We will get error message : Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. Cause: We cannot insert explicit value in timestamp column in sql server Solution: Correct way to insert : INSERT INTO OrderHistory(OrderId,OrderDate) VALUES(100,DEFAULT) Or INSERT INTO OrderHistory(OrderId) VALUES(100)

WCF Interview Questions and Answers

Q1. What is WCF? WCF stands for Windows Communication Foundation. It is a Software development kit for developing services on Windows. WCF is introduced in .NET 3.0. in the System.ServiceModel namespace. WCF is based on basic concepts of Service oriented architecture (SOA) Q2. What is endpoint in WCF service? The endpoint is an Interface which defines how a client will communicate with the service. It consists of three main points: Address,Binding and Contract. Q3. Explain Address,Binding and contract for a WCF Service? Address:Address defines where the service resides. Binding:Binding defines how to communicate with the service. Contract:Contract defines what is done by the service. Q4. What are the various address format in WCF? a)HTTP Address Format:–> http://localhost: b)TCP Address Format:–> net.tcp://localhost: c)MSMQ Address Format:–> net.msmq://localhost: Q5. What are the types of binding available in WCF? A binding is identified by the transport it supports and the encoding it uses. Transport may be HTTP,TCP etc and encoding may be text,binary etc. The popular types of binding may be as below: WCF supports nine types of bindings. Basic binding Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services. TCP binding Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF. Peer network binding Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it. IPC binding Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding. Web Service (WS) binding Offered by the WSHttpBinding class, this uses HTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet. Federated WS binding Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security. Duplex WS binding Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client. MSMQ binding Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls. MSMQ integration binding Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients. For WCF binding comparison, see http://www.pluralsight.com/community/blogs/aaron/archive/2007/03/22/46560.aspx Q6. What are the types of contract available in WCF? The main contracts are: a)Service Contract:Describes what operations the client can perform. b)Operation Contract : defines the method inside Interface of Service. c)Data Contract:Defines what data types are passed d)Message Contract:Defines whether a service can interact directly with messages Q7. What are the various ways of hosting a WCF Service? a)IIS b)Self Hosting c)WAS (Windows Activation Service) Q8. WWhat is the proxy for WCF Service? A proxy is a class by which a service client can Interact with the service. By the use of proxy in the client application we are able to call the different methods exposed by the service. Q9. How can we create Proxy for the WCF Service? We can create proxy using the tool svcutil.exe after creating the service. We can use the following command at command line. svcutil.exe *.wsdl *.xsd /language:C# /out:SampleProxy.cs /config:app.config Q10.What is the difference between WCF Service and Web Service? Web service is a part of WCF. WCF offers much more flexibility and portability to develop a service when comparing to web service. The following point provides the detailed differences between them : 1. Hosting : Webservices can be host in IIS, whereas WCF services can be hosted in IIS, Windows Activation Service, Self Hosting. 2. Encoding : Webservices uses XML 1.0, MTOM(Message Transmission Optimization Mechanism), DIME, Custom. WCF uses XML 1.0, MTOM, Binary, Custom. 3. Transports : Webservices can be accessed using HTTP, TCP, Custom. WCF services can be accessed using HTTP, TCP, Named Pipes, MSMQ, P2P, Custom. 4. Protocols : Webservices uses Security porotocols only. Whereas WCF services uses Security, Reliable Messaging, Transactions protocols. Q11.What is DataContract and ServiceContract?Explain Data represented by creating DataContract which expose the data which will be transefered /consumend from the serive to its clients. **Operations which is the functions provided by this service. To write an operation on WCF,you have to write it as an interface,This interface contains the “Signature” of the methods tagged by ServiceContract attribute,and all methods signature will be impelemtned on this interface tagged with OperationContract attribute.To implement these serivce contract you have to create a class which implement the interface and the actual implementation will be on that class.