SQL Server 2005 XML Integration2




using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{

 [Microsoft.SqlServer.Server.SqlProcedure]
public static void GetEmployeeNameByID(int id)
{

 string retValue = “”;
using (SqlConnection conn = new

SqlConnection(“context connection=true”))

{

 conn.Open();
//Prepare query to select xml data
SqlCommand cmd = conn.CreateCommand();
string sql = “SELECT xml_data.query “ +

“(‘declare namespace ns=\”http://www.wrox.com/books\”;” +
“ <Employee Name=\”{/ns:employee/ns:name}\”/>’) as Result “ +
“ FROM Employee WHERE id = “ + id.ToString();

cmd.CommandText = sql;
//Execute query and retrieve incoming data
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{

 //Get the XML value as string
retValue = (string)reader.GetValue(0);

}
else

retValue = “No Value”;

}
//Send the output XML back to the caller
SqlContext.Pipe.Send(retValue);

}

};

The in-proc provider is optimized for working with data inside the SQL Server process. Using the classes
and methods of the in-process managed provider, you can easily submit queries to the database, execute
DML and DDL statements, and return result sets and messages to client applications. The Microsoft
.Data.SqlServer namespace groups the types that make up the in-proc provider. This namespace
shares many similarities and interfaces with ADO.NET’s SqlClient namespace, which is used by devel-
opers accessing SQL Server data from managed client and middle-tier applications. Because of this simi-
larity, you can easily migrate code from client applications to server libraries and back again.

There are three important classes in the Microsoft.SqlServer.Server namespace that are specific to
the in-proc provider:

  SqlContext —This class encapsulates the other extensions. In addition it provides the transac-

tion and database connection, which are part of the environment in which the routine executes

  SqlPipe —This class enables routines to send tabular results and messages to the client. This

class is conceptually similar to the Response class found in ASP.NET in that it can be used to
send messages to the callers.

Now that you have an understanding of the important classes, walk through the code of Listing 10-3.


learn guitarphysics learnteliphonyxmlphysicsenjoylife