Export (0) Print
Expand All

Stored Procedure Object Interface Layer

 

Keith Hamilton, Software Architect
Antero Koskinen, Senior Developer, (retired)
Microsoft Corporation

February 2006

Applies to:
   Stored Procedure Object Interface Layer (SPOIL)
   Application Architecture
   Software Development

Summary: Transform your code from an object-orientation to a relational paradigm and back with the stored procedure object interface layer (SPOIL). (24 printed pages)

Click here to download the code sample for this article.


Contents

Abstract
Introduction
Example Stored Procedure
Example ADO.NET Code
SPOIL
Comparing SPOIL to Microsoft DAAB
Extending the Concept
Conclusion
Appendix A - SPOIL Code
Appendix B - SqlCommandGenerator Class Programmer's Reference
Public Methods:
Attributes

Abstract

In this paper we introduce the stored procedure object interface layer (SPOIL). SPOIL interfaces with stored procedures, using a method call with attributes, to transform from an object-orientation to a relational paradigm and back. There are three advantages to this:

  1. SPOIL essentially creates an objected-oriented (OO) layer on the database so the application can stay in the OO paradigm.
  2. The application developer never writes ADO.NET code to call stored procedures or parse the returning data.
  3. SPOIL creates strong typing over stored procedures, which isn't provided by ADO.NET. This reduces coding and runtime errors.

Introduction

A stored procedure is a T-SQL program that executes on SQL Server and returns parameter values and/or a ResultSet. Business rules are often encoded in stored procedures, so many applications involve calling stored procedures and processing the data they return. Invoking Stored Procedures in ADO.NET requires a lot of code for each instance. It also causes the developer to switch from an OO paradigm into a functional paradigm with relational data. This is time-consuming and error-prone. Instead, SPOIL creates an OO layer of abstraction between the application and the database. At the highest level, SPOIL creates an object abstraction exposing a stored procedure as methods on managed classes.

Example Stored Procedure

Here is a sample stored procedure, SendLog_Update.

CREATE PROCEDURE [dbo].[SendLog_Update] 
@BatchId uniqueidentifier,
@PartnerId nvarchar(50),
@Mailbox nvarchar(50),
@RootName nvarchar(100),
@Directory nvarchar(100),
@StartPackaging datetime,
@TotalBytes bigint,
@SendBytes bigint

AS
begin
...
End

Example ADO.NET Code

The standard way to access a stored procedure is to write ADO.NET code. Here is sample ADO.NET code to call the stored procedure SendLog_Update.

SqlParameter sqlParameter;

SqlConnection connection = new SqlConnection("connection string");
connection.Open();

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = " SendLog_Update";

sqlParameter = new SqlParameter("@BatchId", SqlDbType.UniqueIdentifier);
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@PartnerId", SqlDbType.NVarChar);
sqlParameter.Size = 50;
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@Mailbox", SqlDbType.NVarChar);
sqlParameter.Size = 50;
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@RootName", SqlDbType.NVarChar);
sqlParameter.Size = 100;
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@Directory", SqlDbType.NVarChar);
sqlParameter.Size = 100;
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@StartPackaging", SqlDbType.DateTime);
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@TotalBytes", SqlDbType.BigInt);
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@SendBytes", SqlDbType.BigInt);
sqlParameter.Value = ???;
command.Parameters.Add(sqlParameter);

command.Connection.Open();
command.ExecuteNonQuery();
connection.Dispose();
connection.Close();

Though this sample doesn't demonstrate out parameters or a ResultSet being returned, SPOIL will marshal the data between .NET types and SQL types. It can also return a ResultSet as an array of populated .NET objects.

SPOIL

Using SPOIL, all of the ADO.NET code in the previous example is replaced with an empty method with the same signature as the stored procedure and decorated with the [SqlCommandMethod] attribute. This obviates the need to write ADO.NET code in the application. This method works like any member of the .NET class library and for a small performance hit, the stored procedure now looks like a method to the application.

To use SPOIL you do the following:

  • Add a reference to SqlCommand.dll.
  • Include a using statement for Microsoft.StoredProcedureObjectInterfaceLayer.
  • Create a method, with the same signature as the stored procedure that calls the SqlCommandGenerator.ExecuteCommand, and decorate it with the [SqlCommandMethod] attribute.
  1. For common cases in which there are only in parameters, this is all that must be done. Spoil creates a SQL connection, manages it, and calls the corresponding stored procedure.
  2. SPOIL does handle more complex scenarios when there are out parameters or when the application may want to control the SQL connection or transaction. If out parameters are defined, then those parameters must be initialized in the method. If the application wants to pass in a connection object or transaction object, then those parameters must be defined as arguments in the method with appropriate attributes. If a result set is to be returned as an array of objects, then the object array must be defined as an argument on the method with appropriate attributes. The application can also explicitly control the execution of the call to the stored procedure.

Example Call to Stored Procedure Using SPOIL

The following code demonstrates how you would access the SendLog_Update stored procedure using SPOIL. You simply define a method using the same names as are used in the stored procedure and decorate it with the SPOIL attributes. This code replaces the previous ADO.NET code entirely for accessing the stored procedure.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.StoredProcedureObjectInterfaceLayer;

namespace SPOIL.Example1
{
    class SendLog
    {
        [SqlCommandMethod(CommandType.StoredProcedure, "SendLog_Update")]
        public int SendLog_Update(
            Guid BatchId,
            [SqlParameter(50)] string PartnerId,
            [SqlParameter(50)] string Mailbox,
            [SqlParameter(100)] string RootName,
            [SqlParameter(100)] string Directory,
            DateTime StartPackaging,
            long TotalBytes,
            long SendBytes)
        {
            int rc = -1;

            SqlConnection sqlConn = new SqlConnection(
@"Trusted_Connection=true;server=.;database=PartnerStore");
            sqlConn.Open();

            rc = SqlCommandGenerator.ExecuteCommand(sqlConn,
this.GetType().GetMethod("SendLog_Update"),
                     BatchId, PartnerId, Mailbox, RootName, Directory,
                     StartPackaging, TotalBytes, SendBytes);

            sqlConn.Close();

            return rc;
        }
    }
}

Depending upon the needs of your application and how you may wish to structure the object abstraction over the stored procedures, you can also implicitly use the object's properties as the arguments for the stored procedure call. In the following example, a client application would instantiate the object, set the properties of the object, and then call the Update method. The SqlCommandGenerator.ExecuteCommand will do the mapping between the object's properties and the stored procedure call's parameters. This paradigm integrates the object's persistence into the object itself and provides a more natural object orientation.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.StoredProcedureObjectInterfaceLayer;

namespace SPOIL.Example2
{
    class SendLog
    {
        public Guid BatchId;
        public string PartnerId;
        public string Mailbox;
        public string RootName;
        public string Directory;
        public DateTime StartPackaging;
        public long TotalBytes;
        public long SendBytes;

         //Client code will call this method.
        public int Update()
        {
            int rc = -1;

            SqlConnection sqlConn = new SqlConnection(
@"Trusted_Connection=true;server=.;database=PartnerStore");
            sqlConn.Open();

            rc = SqlCommandGenerator.ExecuteCommand(sqlConn, this,
this.GetType().GetMethod("SendLog_Update"));

            sqlConn.Close();

            return rc;
        }

         // This method should not be called by anyone.
        [SqlCommandMethod(CommandType.StoredProcedure, "SendLog_Update")]
        public void SendLog_Update(
            Guid BatchId,
            [SqlParameter(50)] string PartnerId,
            [SqlParameter(50)] string Mailbox,
            [SqlParameter(100)] string RootName,
            [SqlParameter(100)] string Directory,
            DateTime StartPackaging,
            long TotalBytes,
            long SendBytes)
        {
        }
    }
}

Comparing SPOIL to Microsoft DAAB

The Microsoft Data Application Access Block (DAAB) is a SQL helper class that creates a function call to a stored procedure by bundling SQL parameters to Helper function parameters. Their nine parameters would be a method with nine attributes in our code.

Compared to SPOIL, DAAB:

  • Doesn't do object abstraction.
  • Doesn't do data conversion.
  • Doesn't do strong typing.

Extending the Concept

There is value in creating an abstraction layer on top of data and Web services (WS). The reason is data and Web services are implementation and you don't want tight coupling between an application consuming a resource and the implementation of that resource. So it's preferred to define a proxy/agent class library as this layer of abstraction so the consuming application only writes against a signature (method), and the implementation is separate. This class library should also be delivered in a DLL that is separate from the application.

SPOIL simplifies the creation of this object abstraction layer on top of data by using a method call that maps to a stored procedure. Such a layer of abstraction creates a loose coupling between the application logic and the data. Since SPOIL abstracts away the implementation, the paradigm is generic; that is, the method doesn't have to sit on top of a stored procedure. Conceptually, this method acts as a service agent for the application code. For example, if instead of making a call to a stored procedure you suddenly want to get the data from a Web service, you could change the proxy/agent by removing the SPOIL tag and putting in code to call the Web service, all without recompiling the application. Likewise, SPOIL could connect to any other source, such as some foreign (line of business) system.

Smart Clients

By enhancing this model with caching, SPOIL as a service agent could be a key component in a smart client architecture. Among the advantages of such an agent is that the data provider could change without having to redistribute the smart client code to the client.

Web Services

As previously mentioned, the SQL provider could also be a WS provider.

Spoil and SOA

By creating a layer of abstraction from objects to relational, SPOIL essentially becomes an interface between the application and any database or other application. This in turn decouples the application from the database and forms the foundation for service orientation.

Conclusion

It is a basic tenet of programming to separate out application logic from the data. The layer of abstraction created by SPOIL does just this in a lightweight and elegant package. This not only simplifies your code, its maintenance, and its testing, but it also provides a required building block for achieving distributed architectures.

Appendix A - SPOIL Code

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using Microsoft.StoredProcedureObjectInterfaceLayer;

namespace MusicStore
{
   public class StoreService : System.Web.Services.WebService
   {
      private string connectionString =
@"Trusted_Connection=true;server=.;database=MusicStore";

      public StoreService()
      {
         InitializeComponent();
      }

      private IContainer components = null;
            
      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent()
      {
      }

      /// <summary>
      /// Clean up any resources being used.
      /// </summary>
      protected override void Dispose( bool disposing )
      {
         if(disposing && components != null)
         {
            components.Dispose();
         }
         base.Dispose(disposing);      
      }

      [WebMethod(EnableSession=true)]
      public int EnumAlbums(string artist, string album, int maxItems,
out AlbumInfo[] albums, ref int enumHandle)
      {
         if(artist == null)
            throw new ArgumentNullException("artist");
         if(album == null)
            throw new ArgumentNullException("album");
         else if(maxItems <= 0)
            throw new ArgumentException("maxItems must be > 0",
               "maxItems");

         int ret = (int)HttpStatusCode.InternalServerError;
         SqlConnection sqlConn = new SqlConnection(ConnectionString);
         try
         {
            sqlConn.Open();
            ret = EnumAlbums(sqlConn, out albums, artist, album, maxItems,
enumHandle);
            if(ret == 0 && albums.Length > 0)
               enumHandle = albums[albums.Length-1].Id;
         }
         catch(Exception se)
         {
            if(se.GetType() == typeof(SqlException))
               ret = ((SqlException)se).Number;
            albums = new AlbumInfo[0];
            throw;
         }
         finally
         {
            if(sqlConn.State == ConnectionState.Open)
               sqlConn.Close();
         }
         return ret;
      }

      /*
      Execute the following stored procedure 
      CREATE PROCEDURE dbo.EnumAlbums
      (
      @Artist NVARCHAR(100),
      @Title NVARCHAR(100),
      @MaxAlbums INT,
      @EnumHandle INT
      )
      AS
      BEGIN
         SET NOCOUNT ON
         DECLARE @err INT
         
         -- Assume case-insensitive character sort order 
         SET ROWCOUNT @MaxAlbums
         SELECT [Id], Artist, Title, ReleaseDate, Genre, Type, ArtistId,
            [Size] FROM Album WHERE(Artist LIKE @Artist AND Title
LIKE @Title AND [Id] > @EnumHandle) ORDER BY Id ASC
         SELECT @err = @@ERROR
         SET ROWCOUNT 0
         RETURN @err
      END
      GO
      */

      [SqlCommandMethod(CommandType.StoredProcedure, "EnumAlbums")]
      private int EnumAlbums(
[NonCommandParameter(ResultSet=false)]SqlConnection sqlConn, 
         [NonCommandParameter(ResultSet=true)] out AlbumInfo[] Albums,
         [SqlParameter(100)] string Artist, 
         [SqlParameter(100)] string Title,
int MaxAlbums,
int EnumHandle) 
      {
         Albums = new AlbumInfo[0];
         object[] arguments = {Artist,Title,MaxAlbums,EnumHandle};
         object[] resultSet;
         int ret = SqlCommandGenerator.ExecuteCommandQuery(sqlConn, null,
               (MethodInfo)MethodBase.GetCurrentMethod(),
out resultSet, arguments);
         if(ret == 0)
         {
            Albums = (AlbumInfo[])resultSet;
         }
         return ret;
      }
   }

   [Serializable()]
   public class AlbumInfo : IComparable
   {
      public int Id;
      public string Title;
      public string Artist;
      public DateTime ReleaseDate;
      public string Genre;
      public byte Type;
      public int ArtistId;
      public int Size;            // Album cover size

      public AlbumInfo()
      {
         Id = 0;
      }

      public int CompareTo(object obj)
      {
         if(this.Id < ((AlbumInfo)obj).Id)
            return -1;
         else if(this.Id > ((AlbumInfo)obj).Id)
            return 1;
         else return 0;
      }
   }
}

Appendix B - SqlCommandGenerator Class Programmer's Reference

Overview

The SqlCommandGenerator class takes the metadata of a C# or Visual Basic .NET method, applies overrides through custom attributes (described later), and generates a ready-to-execute SqlCommand object.

The SqlCommandGenerator class has several static methods. The first parameter to each method is a SqlConnection object. It is the application's responsibility to manage (open, close) this connection.

The second common parameter of each method supplies the MethodInfo of the method whose metadata should be used to generate the SQL command. If you set this parameter to null (or Nothing in Visual Basic .NET), SqlCommandGenerator will automatically discover and use the immediate caller's metadata. This convenient feature comes with a performance penalty; for the best performance, you should supply the metadata (either with Type.GetMethod or MethodBase.GetCurrentMethod).

Public Methods:

public static int ExecuteCommand(SqlConnection connection, 
               MethodInfo method,
               params object[] values)

Generates a SqlCommand object based on the method metadata and parameter values and then executes the SqlCommand object.

Parameters:

SqlConnection connection:

Supplies the SQL connection to be used with the generated SqlCommand.

MethodInfo method:

Supplies the metadata for building the SqlCommand object. If null (Nothing in Visual Basic .NET) the method uses immediate caller's metadata.

params object[] values:

Supplies the values for the stored procedure's or parameterized query's parameters.

Return value:

Sql Stored Procedure RETURN_VALUE or number of rows affected if command type is SqlCommandType.Text.
public static int ExecuteCommand(SqlConnection connection,
               SqlTransaction transaction, 
               MethodInfo method,
               params object[] values)

Generates a SqlCommand object based on the method metadata and parameter values and then executes the SqlCommand object within the supplied transaction.

Parameters:

SqlConnection connection:

Supplies the SQL connection to be used with the generated SqlCommand.

SqlTransaction transaction:

Supplies the transaction within which the generated SqlCommand executes.

MethodInfo method:

Supplies the metadata for building the SqlCommand object. If null (Nothing in Visual Basic .NET) the method uses immediate caller's metadata.
   params object[] values

Supplies the values for the stored procedure's or parameterized query's parameters.

Return value:

SQL Stored Procedure RETURN_VALUE or number of rows affected if command type is SqlCommandType.Text.
public static int ExecuteCommand(SqlConnection connection,
               SqlTransaction transaction, 
               object valueSource,
               MethodInfo method)
               

Generates a SqlCommand object based on the method metadata and the values of the properties and fields of the valueSource parameter and then executes the SqlCommand object within the supplied transaction.

Parameters:

SqlConnection connection:

Supplies the SQL connection to be used with the generated SqlCommand.

SqlTransaction transaction:

Supplies the transaction within which the generated SqlCommand executes. This parameter can be null (Nothing in Visual Basic .NET).

object valueSource:

Supplies an object whose properties and fields supply the values for the stored procedure's or the SQL query's parameters. The output parameter values are copied to valueSource fields.

MethodInfo method:

Supplies the metadata for building the SqlCommand object. If null (Nothing in Visual Basic .NET) the method uses immediate caller's metadata.

Return value:

SQL Stored Procedure RETURN_VALUE or number of rows affected if command type is SqlCommandType.Text.
public static int ConnectAndExecuteCommand(string connectionString, 
               MethodInfo method,
               params object[] values)

First opens a database connection using the supplied connection string. If the connection open succeeds it generates a SqlCommand object based on the method metadata and parameter values and then executes the SqlCommand object. The database connection is closed after the SqlCommand execution completes.

Parameters:

string connection string:

The connection string that includes the source database name and other parameters needed to establish a connection with a database.

MethodInfo method:

Supplies the metadata for building the SqlCommand object. If null (Nothing in Visual Basic .NET) the method uses immediate caller's metadata.

params object[] values:

Supplies the values for the stored procedure's or parameterized query's parameters.

Return value:

Sql Stored Procedure RETURN_VALUE or number of rows affected if command type is SqlCommandType.Text.
public static int ExecuteCommandQuery(SqlConnection connection,
               SqlTransaction transaction, 
               MethodInfo method,
               out object[] dataRows
               params object[] values)

Generates a SqlCommand object based on the method metadata and parameter values and then executes the SqlCommand object within the supplied transaction.

The optional row set returned by the stored procedure is converted to an array of row class objects. Each column of the row set is mapped to a public property or field of the row class. If no match is found, the column is ignored.

Parameters:

SqlConnection connection:

Supplies the SQL connection to be used with the generated SqlCommand.

SqlTransaction transaction:

Supplies the transaction within which the generated SqlCommand executes. This parameter can be null (Nothing in Visual Basic .NET).

MethodInfo method:

Supplies the metadata for building the SqlCommand object. If null (Nothing in Visual Basic .NET) the method uses immediate caller's metadata.

out object[] dataRows:

params object[] values:

Supplies the values for the stored procedure's or parameterized query's parameters.

Return value:

SQL Stored Procedure RETURN_VALUE or number of rows affected if command type is SqlCommandType.Text.
public static int ExecuteCommandQuery(SqlConnection connection,
               SqlTransaction transaction, 
               object valueSource,
               MethodInfo method,
               out object[] dataRows)

Generates a SqlCommand object based on the method metadata and the values of the properties and fields of the valueSource parameter and then executes the SqlCommand object within the supplied transaction.

The optional row set returned by the stored procedure is converted to an array of row class objects. Each column of the row set is mapped to a public property or field of the row class, if no match is found the column is ignored.

Parameters:

SqlConnection connection:

Supplies the SQL connection to be used with the generated SqlCommand.

SqlTransaction transaction:

Supplies the transaction within which the generated SqlCommand executes. This parameter can be null (Nothing in Visual Basic .NET).

object valueSource:

Supplies an object whose properties and fields supply the values for the stored procedure's or the SQL query's parameters. The output parameter values are copied to valueSource fields.

MethodInfo method:

Supplies the metadata for building the SqlCommand object. If null (Nothing in Visual Basic .NET) the method uses immediate caller's metadata.

out object[] dataRows:

Return value:

Sql Stored Procedure RETURN_VALUE or number of rows affected if command type is SqlCommandType.Text.

Attributes

SqlCommandMethodAttribute

Overview:

The SqlCommandMethodAttribute attribute tags a C# or Visual Basic .NET function as targeting a database-stored procedure or a parameterized SQL command. It is a mandatory attribute on methods that are passed to SqlCommandGenerator class.

You must specify the type of the command being represented by the function, either a stored procedure (SqlCommandType.StoredProcedure) or a parameterized SQL command (SqlCommandType.Text).

SqlCommandMethodAttribute has a CommandText property that lets you specify the name of the target stored procedure or an SQL statement. In the former case, you only need to supply the stored procedure name if it is different from the wrapper function in your code. In the latter case you must always supply the text for the SQL command.

[C#]

[AttributeUsage(AttributeTargets.Method)]
public sealed class SqlCommandMethodAttribute : Attribute

Constructors:

public SqlCommandMethodAttribute(CommandType commandType)

Initializes a new instance of SqlCommandMethodAttribute class.

Arguments:

      CommandType commandType 

Specifies the type of the command being represented by the method.

public SqlCommandMethodAttribute(CommandType commandType, string                   commandText)

Initializes a new instance of SqlCommandMethodAttribute class.

Arguments:

      CommandType commandType 

Specifies the type of the command being represented by the method.

      string commandText

Specifies the stored procedure name or text for the parameterized SQL command.

public SqlCommandMethodAttribute(CommandType commandType, string commandText, int commandTimeout)

Initializes a new instance of SqlCommandMethodAttribute class.

Arguments:

      CommandType commandType 

Specifies the type of the command being represented by the method.

      string commandText

Specifies the stored procedure name or text for the parameterized SQL command.

   int commandTimeout

Specifies the wait time before terminating the attempt to execute a command and generating an error.

Public Properties:

   string CommandText {get, set}
   

Gets or sets the stored procedure name or text for the parameterized SQL command.

   int CommandTimeout {get, set}

Gets or sets the command timeout in seconds, default 30 seconds.

NonCommandParameterAttribute

Overview:

The NonCommandParameterAttribute serves two purposes. First, you can use it as a tag to tell the SqlCommandGenerator to ignore the parameter that it is applied to. It is useful when you want to prevent certain payload parameters in your C# or Visual Basic .NET function from being included in the generated SqlCommand.

Second, you can use NonCommandParameterAttribute to designate one (and only one) parameter as a result set parameter by setting the ResultSet property to true. This is useful when a Stored Procedure or a SQL Command returns a row set as return value. The ResultSet property can only be applied to an array parameter.

[AttributeUsage(AttributeTargets.Parameter)]
public sealed class NonCommandParameterAttribute : Attribute

Constructors:

   public NonCommandParameterAttribute()

Initializes a new instance of NonCommandParameterAttribute class.

public NonCommandParameterAttribute(bool ResultSet)

Initializes a new instance of NonCommandParameterAttribute class.

Arguments:

   bool ResultSet

Specifies if the method parameter should be treated as a result set parameter.

Public Properties:

   public bool ResultSet {get, set}

Gets or sets the result set value.

SqlParameterAttribute

Overview:

SqlParameterAttribute allows you to resolve any discrepancies between a C# or Visual Basic .NET method's parameter and the target command's parameter.

SqlParameterAttribute is not used only for tagging a parameter, even though that is possible using its default constructor. It is only required when there are differences involved; otherwise SqlCommandGenerator tries to get as much information as possible from the metadata of a method's parameter.

SqlParameterAttribute has six properties that can be controlled individually: the Name, SqlDbType, Size, Precision, Scale, and Direction of a parameter. Its constructor is overloaded in several ways to allow you to specify the Name, SqlDbType, Size, or a combination of these.

If you need to specify the direction, precision, or scale of a parameter, you have to manually set the Direction, Precision, and Scale properties.

[AttributeUsage(AttributeTargets.Parameter)]

public class SqlParameterAttribute : Attribute

Constructors:

   public SqlParameterAttribute(string name)

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      string name

Specifies the name of the stored procedure or sql command parameter that the tagged method parameter maps to.

   public SqlParameterAttribute(int size)

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      int size

Specifies the size of the corresponding stored procedure/command parameter.

   public SqlParameterAttribute(SqlDbType paramType)

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      SqlDbType paramType

Specifies the type of corresponding stored procedure/command parameter.

   public SqlParameterAttribute(string name, SqlDbType paramType)   

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      string name

Specifies the name of the stored procedure or SQL command parameter that the tagged method parameter maps to.

      SqlDbType paramType

Specifies the type of the corresponding stored procedure/command parameter.

   public SqlParameterAttribute(SqlDbType paramType, int size)

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      SqlDbType paramType

Specifies the type of corresponding stored procedure/command parameter.

      int size

Specifies the size of the corresponding stored procedure/command parameter.

   public SqlParameterAttribute(string name, int size)

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      string name

Specifies the name of the stored procedure or sql command parameter that the tagged method parameter maps to.

      int size

Specifies the size of the corresponding stored procedure/command parameter.

   public SqlParameterAttribute(string name, SqlDbType paramType,                      int    size)

Initializes a new instance of SqlCommandParameterAttribute class.

Arguments:

      string name

Specifies the name of the stored procedure or sql command parameter that the tagged method parameter maps to.

      SqlDbType paramType

Specifies the type of the corresponding stored procedure/command parameter.

      int size

Specifies the size of the corresponding stored procedure/command parameter.

Public Properties:

   public string Name {get, set}

Gets or sets the corresponding stored procedure parameter name.

   public int Size {get, set}

Gets or sets the size of the stored procedure parameter.

   public SqlDbType SqlDbType {get, set}

Gets or sets the data type of the stored procedure parameter.

   public byte Precision {get, set}
   

Gets or sets the precision of the stored procedure parameter.

   public byte Scale {get, set}

Gets or sets the scale of the stored procedure parameter.

   public ParameterDirection Direction {get, set}

Gets or sets the direction of the stored procedure parameter.

Show:
© 2014 Microsoft