How to convert an SQL statement into a XML file

Today I found some functionality which I thought was amazing and will save me loads of time creating some sample XML files to test my webservice with.

I had some sample data in database which I wanted to convert into an XML file.

My original idea was to use excel to concatenate bits of it but this turned out to be to slow and difficult.  I then found out that SQL Server has in built functionality to convert sql statements into XML files.

This article goes through some of the functionality and I would also read this article if you want more detail than my example below

below is my select statement, the important part is at the end
FOR XML AUTO,ELEMENTS
this converts the sql statement into XML elements
  
SELECT
      [Code]"Code"
      ,[Name]"Name"
      ,[Commission %]"Commission"
      ,[E-Mail]"Email"
      ,[Phone No_]"Phone"
      ,[Job Title]"JobTitle"
  FROM [database].[dbo].[Salesperson_Purchaser]salesperson
  FOR XML AUTO,ELEMENTS

this is the result, amazing.  I can't believe I have never heard of this fantastic 
feature before

<salesperson>
  <Code>MT</Code>
  <Name>Metaphorix</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email>metaphorix@metaphorix.co.uk</Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>
<salesperson>
  <Code>TS</Code>
  <Name>tom scott</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email>tom.email.co.uk</Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>
<salesperson>
  <Code>DR</Code>
  <Name>Jerry time</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email></Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>

Visual Studio Default Code Snippets

Today I was creating a class with lots of variables and I was originally using the refactor method to generate getters and setters.  In the end I got bored and thought there must be a quicker way.

I knew about code snippets but hadn’t really used them

To use the code snippet for creating a property you type in prop and then shift twice

it will create this

public int MyProperty { get; set; }

the int and the MyProperty are highlighted and the int is ready to typed over

if you type in string and then press shift again it will go to the MyProperty you can then type over that and if you are finished press enter.

 

there are lots more snippets which could help speed up your coding, I found a list here, which I have pasted below so I can find it later.  There is also the ability to add blocks of code and you can do this by pressing

CTRL K, CTRL S

this will then give you a list of snippets to insert.

Default Code Snippets

The following code snippets are included in Visual Studio by default.

Name (or shortcut) Description Valid locations to insert snippet
#if Creates a #if directive and a #endif directive. Anywhere.
#region Creates a #region directive and a #endregion directive. Anywhere.
~ Creates a destructor for the containing class. Inside a class.
attribute Creates a declaration for a class that derives from Attribute. Inside a namespace (including the global namespace), a class, or a struct.
checked Creates a checked block. Inside a method, an indexer, a property accessor, or an event accessor.
class Creates a class declaration. Inside a namespace (including the global namespace), a class, or a struct.
ctor Creates a constructor for the containing class. Inside a class.
cw Creates a call to WriteLine. Inside a method, an indexer, a property accessor, or an event accessor.
do Creates a do while loop. Inside a method, an indexer, a property accessor, or an event accessor.
else Creates an else block. Inside a method, an indexer, a property accessor, or an event accessor.
enum Creates an enum declaration. Inside a namespace (including the global namespace), a class, or a struct.
equals Creates a method declaration that overrides the Equals method defined in the Object class. Inside a class or a struct.
exception Creates a declaration for a class that derives from an exception (Exception by default). Inside a namespace (including the global namespace), a class, or a struct.
for Creates a for loop. Inside a method, an indexer, a property accessor, or an event accessor.
foreach Creates a foreach loop. Inside a method, an indexer, a property accessor, or an event accessor.
forr Creates a for loop that decrements the loop variable after each iteration. Inside a method, an indexer, a property accessor, or an event accessor.
if Creates an if block. Inside a method, an indexer, a property accessor, or an event accessor.
indexer Creates an indexer declaration. Inside a class or a struct.
interface Creates an interface declaration. Inside a namespace (including the global namespace), a class, or a struct.
invoke Creates a block that safely invokes an event. Inside a method, an indexer, a property accessor, or an event accessor.
iterator Creates an iterator. Inside a class or a struct.
iterindex Creates a “named” iterator and indexer pair by using a nested class. Inside a class or a struct.
lock Creates a lock block. Inside a method, an indexer, a property accessor, or an event accessor.
mbox Creates a call to System.Windows.Forms.MessageBox.Show. You may need to add a reference to System.Windows.Forms.dll. Inside a method, an indexer, a property accessor, or an event accessor.
namespace Creates a namespace declaration. Inside a namespace (including the global namespace).
prop Creates a property declaration and a backing field. Inside a class or a struct.
propg Creates a property declaration with only a “get” accessor and a backing field. Inside a class or a struct.
sim Creates a static int Main method declaration. Inside a class or a struct.
struct Creates a struct declaration. Inside a namespace (including the global namespace), a class, or a struct.
svm Creates a static void Main method declaration. Inside a class or a struct.
switch Creates a switch block. Inside a method, an indexer, a property accessor, or an event accessor.
try Creates a try-catch block. Inside a method, an indexer, a property accessor, or an event accessor.
tryf Creates a try-finally block. Inside a method, an indexer, a property accessor, or an event accessor.
unchecked Creates an unchecked block. Inside a method, an indexer, a property accessor, or an event accessor.
unsafe Creates an unsafe block. Inside a method, an indexer, a property accessor, or an event accessor.
using Creates a using directive. Inside a namespace (including the global namespace).
while Creates a while loop. Inside a method, an indexer, a property accessor, or an event accessor.

Visual Studio Keyboard shortcuts poster

I was trying to figure out some shortcuts in Visual Studio 2010 and then I came across this page and then I find out the visual studio team create a very useful poster, hazaar

you can download the poster here, in fact they have posters for all their languages.  They are A4 in size and the C# is a couple of pages, well worth a look and could save you some time in the long run

I originally found this information on this blog, the mighty ScottGu.