sql procedure send msmq by .Net

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Messaging;

namespace ECI.Sql
{
    public class SqlMSMQ
    {
        [SqlProcedure]
        public static void Send_New(SqlString queue, SqlString msg)
        {
            MessageQueueTransaction mqt = new MessageQueueTransaction();
            MessageQueue mq = new MessageQueue(queue.ToString());
            System.Messaging.Message msg1 = new System.Messaging.Message();
            mq.Formatter = new System.Messaging.ActiveXMessageFormatter();
            msg1.Formatter = new System.Messaging.ActiveXMessageFormatter();
            msg1.Body = msg.ToString();
            mqt.Begin();
            mq.Send(msg1, mqt);
            mqt.Commit();
        }

        /// <summary>
        /// Sends message to queue
        /// </summary>
        /// <param name="queue">Queue path</param>
        /// <param name="msg">Message</param>
        [SqlProcedure]
        public static void Send(SqlString queue, SqlString msg)
        {
            //using (MessageQueue msgQueue = new MessageQueue(queue.ToString(), QueueAccessMode.Send))
            //{
            //    msgQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
            //    msgQueue.Send(msg.Value);
            //}
            MessageQueueTransaction mqt = new MessageQueueTransaction();
            MessageQueue mq = new MessageQueue(queue.ToString());
            System.Messaging.Message msg1 = new System.Messaging.Message();
            mq.Formatter = new System.Messaging.ActiveXMessageFormatter();
            msg1.Formatter = new System.Messaging.ActiveXMessageFormatter();
            msg1.Body = msg.ToString();
            mqt.Begin();
            mq.Send(msg1, mqt);
            mqt.Commit();
        }

        /// <summary>
        /// Peeks message from queue
        /// </summary>
        /// <param name="queue">Queue path</param>
        /// <param name="msg">Message</param>
        [SqlProcedure]
        public static void Peek(SqlString queue, out SqlString msg)
        {
            Message queueMsg = null;
            using (MessageQueue msgQueue = new MessageQueue(queue.ToString(), QueueAccessMode.Peek))
            {
                msgQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
                try
                {
                    queueMsg = msgQueue.Peek(TimeSpan.FromMilliseconds(10));
                }
                catch (MessageQueueException ex)
                {
                    msg = new SqlString();
                    return;
                }
                msg = new SqlString(queueMsg.Body.ToString());
            }
        }

        /// <summary>
        /// Receives message from queue
        /// </summary>
        /// <param name="queue">Queue path</param>
        /// <param name="msg">Message</param>
        [SqlProcedure]
        public static void Receive(SqlString queue, out SqlString msg)
        {
            Message queueMsg = null;
            using (MessageQueue msgQueue = new MessageQueue(queue.ToString(), QueueAccessMode.Receive))
            {
                msgQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
                try
                {
                    queueMsg = msgQueue.Receive(TimeSpan.FromMilliseconds(10));
                }
                catch (MessageQueueException ex)
                {
                    msg = new SqlString();
                    return;
                }
                msg = new SqlString(queueMsg.Body.ToString());
            }
        }



        public static void Receive_New(SqlString queue, out SqlString msg1)
        {
            //System.Messaging.Message msg_value = null;
            //MessageQueueTransaction myTransaction = new MessageQueueTransaction();
            //System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
            //MessageQueue mq = new MessageQueue(@"FormatName:Direct=TCP:192.168.106.138\private$\Test_MQ");

            //try
            //{
            //    myTransaction.Begin();
            //    msg_value = mq.Receive(new TimeSpan(0, 0, 5, 0), myTransaction);
            //    myTransaction.Commit();
            //    msg_value.Formatter = new System.Messaging.XmlMessageFormatter(new Type[] { typeof(string) });
            //    SqlString msg = new SqlString(msg_value.Body.ToString());
            //}
            //catch (Exception exc)
            //{
            //    Console.WriteLine(exc.Message);
            //}

            //首先需要判断消息是否为空

            MessageQueueTransaction myTransaction = new
                    MessageQueueTransaction();
            System.Messaging.Message msg = null;
            //DataSet ds=new DataSet();
            MessageQueue mq = new MessageQueue(queue.ToString());
            //mq.Transactional=MQTransactional;
            int i_count = mq.GetAllMessages().Length;

            if (i_count > 0)
            {
                try
                {
                    myTransaction.Begin();
                    msg = mq.Receive(new TimeSpan(0, 0, 5, 0), myTransaction);
                    myTransaction.Commit();
                }
                catch (MessageQueueException e)
                {
                    //Log.Save(e.Message);
                    myTransaction.Abort();

                    throw;
                }

                //msg.Formatter=new System.Messaging.BinaryMessageFormatter();
                System.IO.Stream stream = msg.BodyStream;


                byte[] buffer = new byte[stream.Length];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(buffer, 0, (int)stream.Length);
                //string str=System.Text.Encoding.UTF8.GetString(buffer,0,(int)stream.Length);
                //string str = System.Text.Encoding.Default.GetString(buffer, 0, (int)stream.Length);
                string str = System.Text.Encoding.Unicode.GetString(buffer, 0, (int)stream.Length);
                msg1 = new SqlString(str);
                return;
            }
            else
            {
                msg1 = new SqlString();
                return;
            }

            //Console.WriteLine(str);


            //MQlable = msg.Label;
            //ds=(DataSet)msg.Body;
            //this.MQcontext = msg.BodyStream; 


            //msg=


        }

    }
}

 

sql procedure

 

-- Enable CLR Integration
sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

USE fuzhou_eport_Haiguan
GO

-- Set TRUSTWORTHY database's option ON
ALTER DATABASE fuzhou_eport_Haiguan SET TRUSTWORTHY ON
GO

-- Add System.Messaging and other assemblies to database
-- remember to check the path to System.Messaging.dll
CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- Add SqlMSMQ assembly
-- remember to set the path to SqlMSMQ.dll correctly
CREATE ASSEMBLY SqlMSMQ
AUTHORIZATION dbo
FROM '{set path}\SqlMSMQ\Bin\SqlMSMQ.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- Create procedures
CREATE PROCEDURE uspMSMQSend
@queue  nvarchar(200),
@msg    nvarchar(MAX)
AS EXTERNAL NAME SqlMSMQ.[ECI.Sql.SqlMSMQ].Send
GO

CREATE PROCEDURE uspMSMQPeek
@queue  nvarchar(200),
@msg    nvarchar(MAX) OUTPUT
AS EXTERNAL NAME SqlMSMQ.[ECI.Sql.SqlMSMQ].Peek
GO

CREATE PROCEDURE uspMSMQReceive
@queue  nvarchar(200),
@msg    nvarchar(MAX) OUTPUT
AS EXTERNAL NAME SqlMSMQ.[ECI.Sql.SqlMSMQ].Receive
GO



/*
-- Uncomment this to test SqlMSMQ
EXEC uspMSMQSend 'lap101\private$\iris_anls_order', '<MESSAGE>Mr. Watson, come here, I need you</MESSAGE>'
GO

DECLARE @text nvarchar(1024)
EXEC uspMSMQPeek 'lap101\private$\iris_anls_order', @msg = @text OUTPUT
PRINT @text
GO

DECLARE @text nvarchar(1024)
EXEC uspMSMQReceive 'lap101\private$\iris_anls_order', @msg = @text OUTPUT
PRINT @text
GO

*/


/*
-- Run this after rebuilding assembly 
ALTER ASSEMBLY SqlMSMQ
FROM 'D:\Projekty\moje\SqlMSMQ\Bin\Debug\SqlMSMQ.dll'
WITH PERMISSION_SET = UNSAFE
*/


/*
-- Remove procedures and SqlMSMQ from database
DROP PROCEDURE uspMSMQSend
GO
DROP PROCEDURE uspMSMQPeek
GO
DROP PROCEDURE uspMSMQReceive
GO
DROP ASSEMBLY SqlMSMQ
GO
*/