Creating Read and Write Methods with IBinarySerialize

When you choose UserDefined serialization format, you also must implement the IBinarySerialize interface and create your own Read and Write methods. The following procedures from the Currency UDT use the System.IO.BinaryReader and System.IO.BinaryWriter to read from and write to the UDT

// IBinarySerialize methods
// The binary layout is as follow:
//    Bytes 0 - 19:Culture name, padded to the right 
//    with null characters, UTF-16 encoded
//    Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
    if (this.IsNull)
    {
        w.Write(nullMarker);
        w.Write((decimal)0);
        return;
    }

    if (cultureName.Length > cultureNameMaxSize)
    {
        throw new ApplicationException(string.Format(
            CultureInfo.InvariantCulture, 
            "{0} is an invalid culture name for currency as it is too long.", 
            cultureNameMaxSize));
    }

    String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
    for (int i = 0; i < cultureNameMaxSize; i++)
    {
        w.Write(paddedName[i]);
    }

    // Normalize decimal value to two places
    currencyValue = Decimal.Floor(currencyValue * 100) / 100;
    w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
    char[] name = r.ReadChars(cultureNameMaxSize);
    int stringEnd = Array.IndexOf(name, '\0');

    if (stringEnd == 0)
    {
        cultureName = null;
        return;
    }

    cultureName = new String(name, 0, stringEnd);
    currencyValue = r.ReadDecimal();
}

CLR Integration

Requirements for Implementing UDTs

To run in SQL Server, your UDT must implement the following requirements in the UDT definition:

The UDT must specify the Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute. The use of the System.SerializableAttribute is optional, but recommended.

• The UDT must implement the System.Data.SqlTypes.INullable interface in the class or structure by creating a public static (Shared in Microsoft Visual Basic) Nullmethod. SQL Server is null-aware by default. This is necessary for code executing in the UDT to be able to recognize a null value.
• The UDT must contain a public static (or Shared) Parse method that supports parsing from, and a public ToString method for converting to a string representation of the object.
• A UDT with a user-defined serialization format must implement the System.Data.IBinarySerialize interface and provide a Read and a Write method.
• The UDT must implement System.Xml.Serialization.IXmlSerializable, or all public fields and properties must be of types that are XML serializable or decorated with the XmlIgnore attribute if overriding standard serialization is required.
• There must be only one serialization of a UDT object. Validation fails if the serialize or deserialize routines recognize more than one representation of a particular object.
• SqlUserDefinedTypeAttribute.IsByteOrdered must be true in order to ensure that the server uses byte-ordered comparisons for UDT values.
• A UDT defined in a class must have a public constructor that takes no arguments. You can optionally create additional overloaded class constructors.
• The UDT must expose data elements as public fields or property procedures.
• Public names cannot be longer than 128 characters, and must conform to the SQL Server naming rules for identifiers as defined in Identifiers.
• sql_variant columns cannot contain instances of a UDT.
• Inherited members are not accessible from Transact-SQL because the SQL Server 2005 type system is not aware of the inheritance hierarchy among UDTs. However, you can use inheritance when you structure your classes and you can call such methods in the managed code implementation of the type.
• Members cannot be overloaded, except for the class constructor. If you do create an overloaded method, no error is raised when you register the assembly or create the type in SQL Server. Detection of the overloaded method occurs at run time, not when the type is created. Overloaded methods can exist in the class as long as they are never invoked. Once you invoke the overloaded method, an error is raised.
• Any static (or Shared) members must be declared as constants or as read-only. Static members cannot be mutable.
• The serialized UDT cannot be larger than 8000 bytes

Sample:

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
     IsByteOrdered=true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
    private bool is_Null;
    private Int32 _x;
    private Int32 _y;

    public bool IsNull
    {
        get
        {
            return (is_Null);
        }
    }

    public static Point Null
    {
        get
        {
            Point pt = new Point();
            pt.is_Null = true;
            return pt;
        }
    }

    // Use StringBuilder to provide string representation of UDT.
    public override string ToString()
    {
        // Since InvokeIfReceiverIsNull defaults to 'true'
        // this test is unneccesary if Point is only being called
        // from SQL.
        if (this.IsNull)
            return "NULL";
        else
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(_x);
            builder.Append(",");
            builder.Append(_y);
            return builder.ToString();
        }
    }

    [SqlMethod(OnNullCall = false)]
    public static Point Parse(SqlString s)
    {
        // With OnNullCall=false, this check is unnecessary if 
        // Point only called from SQL.
        if (s.IsNull)
            return Null;

        // Parse input string to separate out points.
        Point pt = new Point();
        string[] xy = s.Value.Split(",".ToCharArray());
        pt.X = Int32.Parse(xy[0]);
        pt.Y = Int32.Parse(xy[1]);

        // Call ValidatePoint to enforce validation
        // for string conversions.
        if (!pt.ValidatePoint()) 
            throw new ArgumentException("Invalid XY coordinate values.");
        return pt;
    }

    // X and Y coordinates exposed as properties.
    public Int32 X
    {
        get
        {
            return this._x;
        }
        // Call ValidatePoint to ensure valid range of Point values.
        set 
        {
            Int32 temp = _x;
            _x = value;
            if (!ValidatePoint())
            {
                _x = temp;
                throw new ArgumentException("Invalid X coordinate value.");
            }
        }
    }

    public Int32 Y
    {
        get
        {
            return this._y;
        }
        set
        {
            Int32 temp = _y;
            _y = value;
            if (!ValidatePoint())
            {
                _y = temp;
                throw new ArgumentException("Invalid Y coordinate value.");
            }
        }
    }

    // Validation method to enforce valid X and Y values.
    private bool ValidatePoint()
    {
        // Allow only zero or positive integers for X and Y coordinates.
        if ((_x >= 0) && (_y >= 0))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    // Distance from 0 to Point method.
    [SqlMethod(OnNullCall = false)]
    public Double Distance()
    {
        return DistanceFromXY(0, 0);
    }

    // Distance from Point to the specified point method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFrom(Point pFrom)
    {
        return DistanceFromXY(pFrom.X, pFrom.Y);
    }

    // Distance from Point to the specified x and y values method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFromXY(Int32 iX, Int32 iY)
    {
        return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
    }
}

http://chiragrdarji.wordpress.com/2008/03/11/clr-stored-procedure-in-sql-server-2005/

SQL Service Brokers

SQL Service Brokers
http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/

ervice Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as “services.” If the applications need to avail the functionality of these services, then it sends message to the particular “service.”

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

Let us understand Service Broker with simple script. Script contains necessary comments to explain what exactly script is doing.

---------------------------- Service Broker -----------------------
-- In this exercise we will learn how to cofigure Servie Broker and send and recieve messages.
-------------------------------------------------------------------CREATE DATABASE ServiceBrokerTest
GOUSE ServiceBrokerTest
GO-- Enable Service BrokerALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
GO-- Create Message TypeCREATE MESSAGE TYPE SBMessage
VALIDATION = NONE
GO-- Create ContractCREATE CONTRACT SBContract(SBMessage SENT BY INITIATOR)GO-- Create Send QueueCREATE QUEUE SBSendQueue
GO-- Create Receive QueueCREATE QUEUE SBReceiveQueue
GO-- Create Send Service on Send QueueCREATE SERVICE SBSendServiceON QUEUE SBSendQueue (SBContract)GO-- Create Receive Service on Recieve QueueCREATE SERVICE SBReceiveServiceON QUEUE SBReceiveQueue (SBContract)GO-- Begin Dialog using service on contractDECLARE @SBDialog uniqueidentifierDECLARE @Message NVARCHAR(128)BEGIN DIALOG CONVERSATION @SBDialogFROM SERVICE SBSendServiceTO SERVICE 'SBReceiveService'ON CONTRACT SBContractWITH ENCRYPTION = OFF-- Send messages on DialogSET @Message = N'Very First Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)SET @Message = N'Second Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)SET @Message = N'Third Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)GO-- View messages from Receive QueueSELECT CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Receive messages from Receive QueueRECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Receive messages from Receive QueueRECEIVE CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Clean UpUSE master
GODROP DATABASE ServiceBrokerTest
GO

You can download the above script from here.

Let me know what do you think of this script and how simply one can configure service broker.

Reference : Pinal Dave (http://blog.sqlauthority.com)