Executing SQL Queries (SQLXML Managed Classes)

This example demonstrates:

• Creating parameters (SqlXmlParameter objects).
• Assigning values to the properties (Name and Value) of SqlXmlParameter objects.

In this example, a simple SQL query is executed to retrieve the first name, last name, and birth date of the employee whose last name value is passed as a parameter. In specifying the parameter (LastName), only the Value property is set. The Name property is not set, because in this query the parameter is positional and no name is required.

The CommandType property of the SqlXmlCommand object by default is Sql. Therefore, the property is not explicitly set.

Note

In the code, you must provide the name of the instance of Microsoft SQL Server in the connection string.

This is the C# code:

using System;

using Microsoft.Data.SqlXml;
using System.IO;
class Test
{
      static string ConnString = "Provider=SQLOLEDB;Server=(local);database=AdventureWorks;Integrated Security=SSPI";
      public static int testParams()
      {
         Stream strm;
         SqlXmlParameter p;
         SqlXmlCommand cmd = new SqlXmlCommand(ConnString);      
         cmd.CommandText = "SELECT FirstName, LastName FROM Person.Contact WHERE LastName=? For XML Auto";
         p = cmd.CreateParameter();
         p.Value = "Achong";
         string strResult;
         try 
         {
            strm = cmd.ExecuteStream();
            strm.Position = 0;
            using(StreamReader sr = new StreamReader(strm))
            {
               Console.WriteLine(sr.ReadToEnd());
            }
         }
         catch (SqlXmlException e)
         {
            //in case of an error, this prints error returned.
            e.ErrorStream.Position=0;
            strResult=new StreamReader(e.ErrorStream).ReadToEnd();
            System.Console.WriteLine(strResult);
         }
         
         return 0;
   }
public static int Main(String[] args)
{
    testParams();
    return 0;
}
}

To test the application

To test this example, you must have the Microsoft .NET Framework installed on your computer.

1. Save the C# code (DocSample.cs) provided in this topic in a folder.

2. Compile the code. To compile the code at the command prompt, use:

   csc /reference:Microsoft.Data.SqlXML.dll DocSample.cs
   

   This creates an executable (DocSample.exe).

3. At the command prompt, execute DocSample.exe.

Instead of specifying SQL queries as the command text, you can specify a template (as shown in the following code fragment) that executes an updategram (which is also a template) to insert a customer record. You can specify templates and updategrams in files and execute files. For more information, see Executing Template Files by Using the CommandText Property.

   SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLOLEDB;Data Source=SqlServerName;Initial Catalog=Database; Integrated Security=SSPI;");
   Stream stm;
   cmd.CommandType = SqlXmlCommandType.UpdateGram;
   cmd.CommandText = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql' xmlns:updg='urn:schemas-microsoft-com:xml-updategram'>" +
         "<updg:sync>" +
          "<updg:before/>" +
          "<updg:after>" +
            "<Customer CustomerID='aaaaa' CustomerName='Some Name' CustomerTitle='SomeTitle' />" +
          "</updg:after>" +
          "</updg:sync>" +
          "</ROOT>";

   stm = cmd.ExecuteStream();
   stm = null;
   cmd = null;

Using ExecuteToStream

If you have an existing stream, you can use the ExecuteToStream method instead of creating a Stream object and using the Execute method. The code from the preceding example is revised here to use the ExecuteToStream method:

using System;
using Microsoft.Data.SqlXml;
using System.IO;
class Test
{
   static string ConnString = "Provider=SQLOLEDB;Server=SqlServerName;database=AdventureWorks;Integrated Security=SSPI;";
   public static int testParams()
   {
      SqlXmlParameter p;
      MemoryStream ms = new MemoryStream();
      StreamReader sr = new StreamReader(ms);
      ms.Position = 0;
      SqlXmlCommand cmd = new SqlXmlCommand(ConnString);
      cmd.CommandText = "select FirstName, LastName from Person.Contact where LastName = ? For XML Auto";
      p = cmd.CreateParameter();
      p.Value = "Achong";
      cmd.ExecuteToStream(ms);
      ms.Position = 0;
      Console.WriteLine(sr.ReadToEnd());
      return 0;      
   }
   public static int Main(String[] args)
   {
      testParams();   
      return 0;
   }
}

Note

You can also use the ExecuteXMLReader method that returns an XmlReader object. For more information, see Executing SQL Queries by Using the ExecuteXMLReader Method.