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
(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
|