© 2004 Microsoft Corporation. All rights reserved.

Figure 1 GetAnnouncements (from IBuySpy)
CREATE PROCEDURE GetAnnouncements
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    CreatedByUser,
    CreatedDate,
    Title,
    MoreLink,
    MobileMoreLink,
    ExpireDate,
    Description

FROM 
    Announcements

WHERE
    ModuleID = @ModuleID
  AND
    ExpireDate > GetDate()
Figure 2 ADO Calling GetAnnouncements
Function GetAnnouncements(ByVal Connection As ADODB.Connection, _
    ByVal ModuleID As Integer) As ADODB.Recordset

    Dim Command As ADODB.Command
    Set Command = New ADODB.Command

    Set Command.ActiveConnection = Connection
    Command.CommandText = "GetAnnouncements"
    Command.CommandType = adCmdStoredProc

    Dim Parameters As ADODB.Parameters
    Set Parameters = Command.Parameters

    Parameters.Append Command.CreateParameter("@RETURN_VALUE", _
        adInteger, adParamReturnValue, 0)
    Parameters.Append Command .CreateParameter("@ModuleID", adInteger, _
        adParamInput, 0)

    Parameters("@ModuleID").Value = ModuleID

    Dim Recordset As ADODB.Recordset
    Set Recordset = New ADODB.Recordset

    Recordset.CursorLocation = adUseClient
    Recordset.Open Command, , adOpenStatic, adLockReadOnly
    Set Recordset.ActiveConnection = Nothing

    Set GetAnnouncements = Recordset

End Function
Figure 3 AnnouncementsDB.GetAnnouncements
public class AnnouncementsDB {
  •••
  public DataSet GetAnnouncements(int moduleId) {

    // Create Instance of Connection and Command Object
    SqlConnection myConnection = new SqlConnection(
      ConfigurationSettings.AppSettings["connectionString"]);
    SqlDataAdapter myCommand = new SqlDataAdapter("GetAnnouncements", 
      myConnection);

    // Mark the Command as a SPROC
    myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

    // Add Parameters to SPROC
    SqlParameter parameterModuleId = new SqlParameter("@ModuleId", 
      SqlDbType.Int, 4);
    parameterModuleId.Value = moduleId;
    myCommand.SelectCommand.Parameters.Add(parameterModuleId);

    // Create and Fill the DataSet
    DataSet myDataSet = new DataSet();
    myCommand.Fill(myDataSet);

    // Return the DataSet
    return myDataSet;
  }
  •••
}
Figure 4 C# Sample for Reflection
using System;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

sealed class PortalDatabase
{
    public static DataSet GetAnnouncements(
        SqlConnection connection, 
        int moduleId)
    {
        return null;
    }
}

sealed class ReflectionSample
{
    static void Main()
    {
        MethodInfo[] methods = typeof(PortalDatabase).GetMethods(
            BindingFlags.Static | BindingFlags.Public | 
            BindingFlags.DeclaredOnly);

        foreach (MethodInfo method in methods)
        {
            Console.WriteLine("{0}.{1} [{2}]",
                method.DeclaringType.Name, 
                method.Name, method.ReturnType);
                
            foreach (ParameterInfo paramInfo in method.GetParameters())
            {
                Console.WriteLine("\t{0} {1}", 
                    paramInfo.ParameterType,
                    paramInfo.Name);
            }
        }
    }
}
Figure 5 Generating a SqlCommand from a Method
sealed class SqlCommandGenerator
{
    private SqlCommandGenerator() {}

    public static SqlCommand GenerateCommand(SqlConnection connection, 
        MethodInfo method, object[] values)
    {
        SqlCommand command = new SqlCommand(method.Name, connection);
        command.CommandType = CommandType.StoredProcedure;

        ParameterInfo[] parameters = method.GetParameters();

        for (int i = 1; i < parameters.Length; i++)
        {
            SqlParameter sqlParameter = new SqlParameter();
            sqlParameter.ParameterName = "@" + parameters[i].Name;
            sqlParameter.Value = values[i];

            command.Parameters.Add(sqlParameter);
        }

        return command;
    }
}
Figure 6 Type Mappings

SqlDbType
System
C#
Visual Basic .NET
(not supported)
System.Char
char
Char
BigInt
System.Int64
long
Long
Binary
System.Array of System.Byte
byte
Byte
Bit
System.Boolean
bool
Boolean
Char
System.String
string
String
DateTime
System.DateTime
System.DateTime
Date
Decimal
System.Decimal
decimal
Decimal
Float
System.Double
double
Double
Image
System.Array of System.Byte
byte
Byte
Int
System.Int32
int
Integer
Money
System.Decimal
decimal
Decimal
NChar
System.String
string
String
NText
System.String
string
String
NVarChar
System.String
string
String
Real
System.Single
float
Single
SmallDateTime
System.DateTime
System.DateTime
Date
SmallInt
System.Int16
short
Short
SmallMoney
System.Decimal
decimal
Decimal
Text
System.String
string
String
Timestamp
System.DateTime
DateTime
Date
TinyInt
System.Byte
byte
Byte
UniqueIdentifier
System.Guid
System.Guid
System.Guid
VarBinary
System.Array of System.Byte
byte[]
Byte()
VarChar
System.String
string
String
Variant
System.Object
object
Object
Figure 7 Method's Metadata
public static DataSet GetAnnouncements(SqlConnection connection, 
    int moduleId)
{
    MethodInfo methodInfo = 
        typeof(PortalDatabase).GetMethod("GetAnnouncements", 
        new Type[] { typeof(SqlConnection), typeof(int) });

    SqlCommand command = 
        SqlCommandGenerator.GenerateCommand(connection, 
        methodInfo, new object[] { moduleId });

    DataSet dataSet = new DataSet();
    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
    dataAdapter.Fill(dataSet);
    return dataSet;
}
Figure 8 Using SqlParameterAttribute
[ SqlCommandMethod(CommandType.StoredProcedure) ]
public static DataSet GetCustomers(
    [ NonCommandParameter ] SqlConnection connection) 
{ ... }

[SqlCommandMethod(CommandType.StoredProcedure) ]
public static DataSet GetCustomersByState(
    [ NonCommandParameter ] SqlConnection connection,
    [ SqlParameter(2) ] string state) 
{ ... }
 
[SqlCommandMethod(CommandType.StoredProcedure) ]
public static DataSet GetCustomerById(
    [ NonCommandParameter ] SqlConnection connection,
    int customerId) 
{ ... }

[SqlCommandMethod(CommandType.StoredProcedure) ]
public static int AddOrderItem(
    [ NonCommandParameter ] SqlConnection connection,
    [ SqlParameter("PartNr", 20) ] string partNumber,
    [ SqlParameter(SqlDbType.Decimal, Scale = 9, Precision = 4) ] decimal 
    unitPrice, int quantity) 
{ ... }
Figure 9 Insert Row and Return Identity
CREATE PROCEDURE AddAnnouncement
(
    @ModuleID       int,
    @UserName       nvarchar(100),
    @Title          nvarchar(150),
    @MoreLink       nvarchar(150),
    @MobileMoreLink nvarchar(150),
    @ExpireDate     DateTime,
    @Description    nvarchar(2000),
    @ItemID         int OUTPUT
)
AS

INSERT INTO Announcements
(
    ModuleID,
    CreatedByUser,
    CreatedDate,
    Title,
    MoreLink,
    MobileMoreLink,
    ExpireDate,
    Description
)

VALUES
(
    @ModuleID,
    @UserName,
    GetDate(),
    @Title,
    @MoreLink,
    @MobileMoreLink,
    @ExpireDate,
    @Description
)

SELECT
    @ItemID = @@Identity
Figure 10 C# Call Stored Procedure with Output
[ SqlCommandMethod ]
public static void AddAnnouncement(
    [ NonCommandParameter ] SqlConnection connection,    int moduleId,
    [ SqlParameter(100) ] string userName,
    [ SqlParameter(150) ] string title,
    [ SqlParameter(150) ] string moreLink,
    [ SqlParameter(150) ] string mobileMoreLink,
    DateTime expireDate,
    [ SqlParameter(2000) ] string description,
    out int itemId)
{
    itemId = 0; // Suppresses: Use of unassigned local variable 'itemId'

    SqlCommand command = SqlCommandGenerator.GenerateCommand(connection, 
                         null, new object[] { moduleId, userName, 
                         title, moreLink, mobileMoreLink, 
                         expireDate, description, itemId });

    command.ExecuteNonQuery();

    itemId = (int) command.Parameters["@ItemID"].Value;
}
Figure 11 Reflection and byref Parameters
class Sample
{
    public static void Swap(ref int a, ref int b)
    {
        int temp = b;
        b = a;
        a = temp;
    }

    public static void SwapProxy(ref int a, ref int b)
    {
        object[] parameters = new object[] { a, b };
        typeof(Sample).GetMethod("Swap").Invoke(null, parameters);

        a = (int) parameters[0];
        b = (int) parameters[1];
    }

    static void Main(string[] args)
    {
        int a = 1;
        int b = 2;
        
        SwapProxy(ref a, ref b);
        System.Console.WriteLine("a = {0}, b = {1}", a, b);
    }
}
Figure 12 Visual Basic .NET Late Binding to Swap
Class Sample

    Public Sub Swap(ByRef a As Integer, ByRef b As Integer)

        Dim temp As Integer = b
        b = a
        a = temp

    End Sub

End Class

Module Module1

    Sub Main()

        Dim a As Integer = 1
        Dim b As Integer = 2
        Dim o As Object = New Sample()
        o.Swap(a, b)
        System.Console.WriteLine("a = {0}, b = {1}", a, b)

    End Sub

End Module
Figure 13 ILDASM Dump of Main
.method public static void  Main() cil managed
{
  .entrypoint
  .custom instance void [mscorlib]System.STAThreadAttribute::.ctor() = 
    ( 01 00 00 00 ) 
  // Code size       114 (0x72)
  .maxstack  6
  .locals init ([0] int32 a,
           [1] int32 b,
           [2] object o,
           [3] object[] _Vb_t_array_2,
           [4] object[] _Vb_t_array_1,
           [5] bool[] _Vb_t_array_0)
  .language '{3A12D0B8-C26C-11D0-B442-00A0244A1DD2}', '{994B45C4-E6E9-11D2-
903F-00C04FA302A1}', '{00000000-0000-0000-0000-000000000000}'
// Source File 'C:\Documents and Settings\atifa\My Documents\
     Visual Studio Projects\vbref\Module1.vb' 
//000015:     Sub Main()
  IL_0000:  nop
//000016: 
//000017:         Dim a As Integer = 1
  IL_0001:  ldc.i4.1
  IL_0002:  stloc.0
//000018:         Dim b As Integer = 2
  IL_0003:  ldc.i4.2
  IL_0004:  stloc.1
//000019:         Dim o As Object = Sample()
  IL_0005:  newobj     instance void vbref.Sample::.ctor()
  IL_000a:  stloc.2
//000020:         o.Swap(a, b)
  IL_000b:  ldloc.2
  IL_000c:  ldnull
  IL_000d:  ldstr      "Swap"
  IL_0012:  ldc.i4.2
  IL_0013:  newarr     [mscorlib]System.Object
  IL_0018:  stloc.s    _Vb_t_array_1
  IL_001a:  ldloc.s    _Vb_t_array_1
  IL_001c:  ldc.i4.0
  IL_001d:  ldloc.0
  IL_001e:  box        [mscorlib]System.Int32
  IL_0023:  stelem.ref
  IL_0024:  ldloc.s    _Vb_t_array_1
  IL_0026:  ldc.i4.1
  IL_0027:  ldloc.1
  IL_0028:  box        [mscorlib]System.Int32
  IL_002d:  stelem.ref
  IL_002e:  ldloc.s    _Vb_t_array_1
  IL_0030:  stloc.3
  IL_0031:  ldloc.3
  IL_0032:  ldnull
  IL_0033:  ldloca.s   _Vb_t_array_0
  IL_0035:  call       void [Microsoft.VisualBasic]
Microsoft.VisualBasic.Helpers.LateBinding::LateCall
(object,class [mscorlib]System.Type,string,object[],string[],bool[]&)
  IL_003a:  nop
  IL_003b:  ldloc.s    _Vb_t_array_0
  IL_003d:  ldc.i4.1
  IL_003e:  ldelem.i1
  IL_003f:  brfalse.s  IL_004a
  IL_0041:  ldloc.3
  IL_0042:  ldc.i4.1
  IL_0043:  ldelem.ref
  IL_0044:  call       int32 [Microsoft.VisualBasic]
Microsoft.VisualBasic.Helpers.IntegerType::FromObject(object)
  IL_0049:  stloc.1
  IL_004a:  ldloc.s    _Vb_t_array_0
  IL_004c:  ldc.i4.0
  IL_004d:  ldelem.i1
  IL_004e:  brfalse.s  IL_0059
  IL_0050:  ldloc.3
  IL_0051:  ldc.i4.0
  IL_0052:  ldelem.ref
  IL_0053:  call       int32 [Microsoft.VisualBasic]
Microsoft.VisualBasic.Helpers.IntegerType::
FromObject(object)
  IL_0058:  stloc.0
//000021:         System.Console.WriteLine("a = {0}, b = {1}", a, b)
  IL_0059:  ldstr      "a = {0}, b = {1}"
  IL_005e:  ldloc.0
  IL_005f:  box        [mscorlib]System.Int32
  IL_0064:  ldloc.1
  IL_0065:  box        [mscorlib]System.Int32
  IL_006a:  call       void [mscorlib]System.Console::WriteLine(string,
                                                                object,
                                                                object)
  IL_006f:  nop
//000022: 
//000023:     End Sub
  IL_0070:  nop
  IL_0071:  ret
} // end of method Module1::Main
Figure 14 Reporting all Stored Procedure Proxies
using System;
using System.Reflection;
using Sample.Data.Sql;

class Sample
{
    static void Main(string[] args)
    {
        Assembly assembly = Assembly.LoadFrom(args[0]);

        foreach (Type type in assembly.GetExportedTypes())
        {
            foreach (MethodInfo methodInfo in type.GetMethods())
            {
                SqlCommandMethodAttribute attribute =        
                (SqlCommandMethodAttribute) Attribute.GetCustomAttribute
                (methodInfo, typeof(SqlCommandMethodAttribute));

                if (attribute != null && attribute.CommandType == 
                    System.Data.CommandType.StoredProcedure)
                {
                    Console.Write("{0}.{1}",
                        methodInfo.DeclaringType.FullName, 
                        methodInfo.Name);

                    if (attribute.CommandText.Length != 0)
                        Console.Write(" -> {0}", attribute.CommandText);

                    Console.WriteLine();
                }
            }
        }
    }
}
Figure 15 SQL Script to Generate C# Proxy Signature
set nocount on

declare @sp varchar(100)
set @sp = '<< stored procedure name goes here >>'

declare @oid int
select @oid = o.id from sysobjects o where o.name = @sp

declare @last int

-- function signature

select  @last = max(c.colid)
from    dbo.syscolumns c 
where   c.id = @oid 

select  case c.colid 
            when 1 then '[ SqlCommandMethod(CommandType.StoredProcedure) ]' 
            + char(13) + 
                        'public static SqlCommand ' + @sp + '(' + char(13) +
                        '    [ NonCommandParameter ] SqlConnection 
                        ' connection '
            + char(13) +
            else ''
        end
        + '    ' +
        case t.name 
            when 'char' then 
                '[ SqlParameter(' + convert(nvarchar(10), c.length) + ')  ' 
            when 'varchar' then 
                '[ SqlParameter(' + convert(nvarchar(10), c.length) + ') ] ' 
            when 'nchar' then 
              '[ SqlParameter(' + convert(nvarchar(10), c.length / 2) + ') ] ' 
            when 'nvarchar' then 
                '[ SqlParameter(' + convert(nvarchar(10), c.length / 2) + ') ] ' 
            else ''
        end 
        +
        case t.name
            when 'char' then 'string'
            when 'nchar' then 'string'
            when 'varchar' then 'string'
            when 'nvarchar' then 'string'
            when 'bit' then 'bool'
            when 'datetime' then 'DateTime'
            when 'float' then 'double'
            when 'real' then 'float'
            when 'int' then 'int'
            else 'object /* ' + t.name + ' */'
        end
        + ' ' + lower(substring(c.name, 2, 1)) + substring(c.name, 3, 100) 
        +
        case c.colid 
            when @last then ')' + char(13) + '{'
            else ',' 
        end     
from            dbo.syscolumns c 
left outer join dbo.systypes t on c.xusertype = t.xusertype 
where           c.id = @oid 
order by        c.colid

-- call to generator

select  case c.colid 
            when 1 then 
            '    return SqlCommandGenerator.GenerateCommand(connection,' + 
                     char(13)
            else ''
        end
        + '        ' + lower(substring(c.name, 2, 1)) + substring(c.name, 
                          3, 100) 
        +
        case c.colid 
            when @last then ' + char(13) + '}'
            else ',' 
        end
from            dbo.syscolumns c 
where           c.id = @oid 
order by        c.colid