Codeusing 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
Code-- 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
*/
