consuming SQL Server web services

As everyone knows, it's now possible to create - quickly - a web service on sql server.
This web service can contains several methods which are all linked to one stored procedure.

This is done with an EndPoint
Ex :
We have a table called "buildings" where all the building of our organization are stored
We have 2 stored procedures

• up_building_getall() : gets all the building like the figure above (+ other information)
  
• up_building_getone(buildingid,textid) : gets one building by it's id and/or by it's textid.
  

Creating a web service :

DROP ENDPOINT BuildingsEndPoint;
GO

CREATE ENDPOINT BuildingsEndPoint
STATE = STARTED
AS HTTP(
PATH = '/buildings',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR),
site = 'mydbServer'
)
FOR SOAP (
WEBMETHOD 'GetOne' (NAME='mydb.dbo.Up_Building_GetOne'),
WEBMETHOD 'GetAll' (NAME='mydb.dbo.Up_Building_GetAll'),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'mydb'
);
GO

The WSDL for this web service can be accessed at http://mydbserver/buildings?wsdl and gives this nice output :

Add a web reference to your project :





And it's done.

You now have in your project under "Web References" a folder "mydbserver".
Anywhere in your code you can now create a mydbserver.BuildingsEndPoint as in the example here under :

static void Main(string[] args)
{
try
{
TmpConsole.mydbserver.BuildingsEndPoint be = new TmpConsole.mydbserver.BuildingsEndPoint();
be.UseDefaultCredentials = true;

object[] products = be.GetAll();
if (products[0].ToString() == "System.Data.DataSet")
{
DataSet ds = (System.Data.DataSet)products[0];
DataTable dtt = ds.Tables[0];
//simple console method that display a datatable
interpretDataTable(dtt);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void interpretDataTable(DataTable dtt)
{
if (!(dtt == null || dtt.Rows == null))
{
foreach (DataRow dr in dtt.Rows)
{
Console.Write("- {0}={1}", dtt.Columns[0].ColumnName, dr[0].ToString());
for (int i = 1; i < dtt.Columns.Count; i++)
Console.Write("/{0}={1}", dtt.Columns[i].ColumnName, dr[i].ToString());
Console.WriteLine();
}
}
else
Console.WriteLine("Null or no rows.");
}


Watch out for the "UseDefaultCredentials"
Otherwise, the great HTTP 401 may show it's strength...