NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Using stored procedures with a Command

When you are expecting to return only a single row, using a stored procedure with output parameters is probably the quickest way. When using SQL Server, or other procedure based databases, parameters can be used to retrieve a singleton row. Using parameters works just as in ADO; you can pass the string in the command, or use the parameters collection.

When using parameters with the SQLCommand, the names of the parameters added to the command's parameters collection must match the names of the parameter markers in the stored procedure. The SQL managed provider treats these as "named" parameters and will look for the matching marker.

[VB]

Dim myConnection As SQLConnection = New SQLConnection _
   ("server=delphi;uid=sa;pwd=;database=northwind")
Dim myCommand As SQLCOmmand = New SQLCommand _
   ("GetCustomerListbyState 'WA'", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Try
   myConnection.Open()
   myCommand.Execute(myReader)
   While myReader.Read
      Console.WriteLine(myReader("CompanyName").ToString))
   End While
   Catch e As Exception
      Console.WriteLine(e.ToString)
   Finally
      myReader.Close()
      myConnection.Close()
End Try

[C#]

SQLConnection myConnection = new 
SQLConnection("server=delphi;uid=sa;pwd=;database=northwind");
SQLCommand myCommand = new SQLCommand("GetCustomerListbyState 'WA'", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

try
{
 myConnection.Open();
 myCommand.Execute(out myReader);

 while (myReader.Read())
 {
      Console.WriteLine(myReader["CompanyName"].ToString());
 }      
}
catch(Exception e)
{
 Console.WriteLine(e.ToString());
}
finally
{
 myReader.Close();
 myConnection.Close();
}

In the following code, we build a command that executes a stored procedure call named "GetComanyName." This stored procedure is declared in SQL Server as:

CREATE PROCEDURE GetCompanyName
@CustomerID nvarchar(5),
@CompanyName nvarchar(40) output
as
Select @CompanyName = CompanyName from Customers where CustomerID = @CustomerID

To execute this stored procedure, we need to do three things: (1) Create the Command, (2) set the command type to Stored procedure, and (3) create and set the parameters.

SQL

[VB]

Dim myConnection As SQLConnection = new _
SQLConnection("server=delphi;uid=sa;pwd=;database=northwind")
Dim myCommand As SQLCommand = New SQLCommand("GetCompanyName", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim workPara As SQLParameter = Nothing
workParam = myCommand.Parameters.Add
(New SQLParameter("@CustomerID", SQLDataType.NChar, 5))
workPara.Direction = ParameterDirection.Input
workParam.Value = "ALFKI"
workParam.myCommand.Parameters.Add
(New SQLParameter("@CompanyName", SQLDataType.NChar, 40))
workParam.Direction = ParameterDirection.Output
Try
   myConnection.Open()
   myConnection.Execute()
   Console.WriteLine("CompanyName = " & _
   myCommand.Parameters("@CompanyName").Value
Catch e As Exception
   Console.WriteLine(e.ToString)
Finally
   myConnection.Close()
End Try

[C#]

SQLConnection myConnection = new 
SQLConnection("server=delphi;uid=sa;pwd=;database=northwind");

SQLCommand myCommand = new SQLCommand("GetCompanyName", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

SQLParameter workParam = null;

workParam = myCommand.Parameters.Add(new SQLParameter("@CustomerID", 
SQLDataType.NChar, 5));
workParam.Direction = ParameterDirection.Input;
workParam.Value = "ALFKI";
   
workParam = myCommand.Parameters.Add(new SQLParameter("@CompanyName", 
SQLDataType.NChar, 40));
workParam.Direction = ParameterDirection.Output;

try
{
 myConnection.Open();
 myCommand.Execute();
 Console.WriteLine("CompayName= " + 
myCommand.Parameters["@CompanyName"].Value);
}
catch(Exception e)
{
 Console.WriteLine(e.ToString());
}
finally
{
 myConnection.Close();
}

ADO

[VB]

Dim myCommand As ADOCommand = New ADOCommand("GetCompanyName", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim workPara As ADOParameter = Nothing
workParam = myCommand.Parameters.Add("@CustomerID", SQLDataType.NChar, 5)
workPara.Direction = ParameterDirection.Input
workParam.Value = "ALFKI"
workParam.myCommand.Parameters.Add("@CompanyName", SQLDataType.NChar, 40)
workParam.Direction = ParameterDirection.Output
Try
   myConnection.Open()
   myConnection.Execute()
   Console.WriteLine("CompanyName = " & _
   myCommand.Parameters("@CompanyName").Value
Catch e As Exception
   Console.WriteLine(e.ToString)
Finally
   myConnection.Close()
End Try

[C#]

ADOCommand myCommand = new ADOCommand("GetCompanyName", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

ADOParameter workParam = null;

workParam = myCommand.Parameters.Add("@CustomerID", ADODBType.Char, 5);
workParam.Direction = ParameterDirection.Input;
workParam.Value = "ALFKI"
   
workParam = myCommand.Parameters.Add("@CompanyName", ADODBType.Char, 40);
workParam.Direction = ParameterDirection.Output;

try
{
 myConnection.Open();
 myCommand.Execute();
 Console.WriteLine("CompayName= " + 
myCommand.Parameters["@CompanyName"].Value);
}
catch(Exception e)
{
 Console.WriteLine(e.ToString());
}
finally
{
 myConnection.Close();
}

We create the command as we've done before, pointing it to our connection. We set the commandtext property of the command through the class's constructor to the name of the stored procedure.

ADOCommand myCommand = new ADOCommand("GetCompanyName", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

The other options would be to simply create a command and assign the connection and commandtext through their property accessors

ADOCommand myCommand = new ADOCommand();
myCommand.ActiveConnection = myConnection;
myCommand.CommandText = "GetCompanyName";
myCommand.CommandType = CommandType.StoredProcedure;

Next, we create two parameter objects and add them to the command's parameters collection.

SQL

SQLParameter workParam = null;

workParam = myCommand.Parameters.Add(new SQLParameter("@CustomerID", 
SQLDataType.NChar, 5));
workParam.Direction = ParameterDirection.Input;
workParam.Value = "ALFKI";

workParam = myCommand.Parameters.Add(new SQLParameter("@CompanyName", 
SQLDataType.NChar, 40));
workParam.Direction = ParameterDirection.Output;

ADO

workParam = myCommand.Parameters.Add("@CustomerID", ADODBType.Char, 5);
workParam.Direction = ParameterDirection.Input;
workParam.Value = "ALFKI";
   
workParam = myCommand.Parameters.Add("@CompanyName", ADODBType.Char, 40);
workParam.Direction = ParameterDirection.Output;

We do three things here: build and add the parameters, set the parameter direction and set the value for the input parameter.

You'll note that there is a difference in doing this between the SQLCommand and the ADOCommand. This simply because the models are not exactly alike at this moment.

Next, we open the connection and execute the command. Once finished, we simply retrieve the output parameter and write it to the console.

[VB]

myConnection.Open()
myCommand.Execute()
Console.WriteLine("CompanyName = " & _
myCommand.Parameters("@CompanyName").Value)

[C#]

myConnection.Open();
myCommand.Execute();
Console.WriteLine("CompanyName= " + myCommand.Parameters["@CompanyName"].Value);

The last important thing to note is the finally block. The finally block of the try-catch statement is always guaranteed to execute no matter what the exception.

[VB]

Finally
   myConnection.Close

[C#]

finally
{
 myConnection.Close();
}

With connection and datareaders, you should always use a finally block to ensure that if anything fails, they will still be closed.

Note that in this example we used an overload of the command's Execute method that doesn't return a DataReader. If you used return a datareader, and have output parameters, they won't be populated until after the datareader is closed.