internal static SSBIMessageCollection GetMessageCollection(BrokerService bServ, Database db, Guid handle, ServiceQueue q) { string sqlConn = string.Format("server={0};database={1};Integrated Security='SSPI';", db.Parent.Name, db.Name); string schema; string qName; string sqlExec = @"select q.conversation_group_id, q.conversation_handle, q.message_sequence_number, q.service_name, se.far_service, q.service_contract_name, q.message_type_name, q.validation, message_body from {0}.{1} q join sys.conversation_endpoints se on q.conversation_handle = se.conversation_handle"; //where se.state in ('SO','SI','CO')"; SqlConnection conn = CreateNewConnection(db); SqlCommand cmd = conn.CreateCommand(); if (bServ == null && q != null) { schema = q.Schema; qName = q.Name; } else { schema = bServ.QueueSchema; qName = bServ.QueueName; sqlExec += " and q.service_id = @id"; int id = bServ.ID; cmd.Parameters.Add("@id", SqlDbType.Int, 4); cmd.Parameters["@id"].Value = id; } if (handle != Guid.Empty) { sqlExec += " and q.conversation_handle = @h"; cmd.Parameters.Add("@h", SqlDbType.UniqueIdentifier); cmd.Parameters["@h"].Value = handle; } sqlExec = string.Format(sqlExec, schema, qName); cmd.CommandText = sqlExec; if (conn.Database != db.Name) { conn.ChangeDatabase(db.Name); } SqlDataReader reader = cmd.ExecuteReader(); SSBIMessageCollection scColl = new SSBIMessageCollection(); while (reader.Read()) { Guid cnvHandle; SSBIMessage msg = new SSBIMessage(); msg.ConversationGroupId = reader.GetGuid(0); cnvHandle = reader.GetGuid(1); msg.ConversationHandle = cnvHandle; //msg.Conversation = new Conversation(srv, cnvHandle); msg.SequenceNumber = reader.GetInt64(2); msg.RemoteServiceName = reader.GetString(4); msg.ServiceName = reader.GetString(3); msg.ContractName = reader.GetString(5); msg.Type = reader.GetString(6); msg.Validation = reader.GetString(7); if (!reader.IsDBNull(8)) { SqlBytes sb = reader.GetSqlBytes(8); msg.Body = sb.Stream; } else { msg.Body = null; } scColl.Add(msg); } conn.Close(); return(scColl); }
internal static SSBIMessageCollection GetMessageCollection(BrokerService bServ, Database db, Guid handle, ServiceQueue q) { string sqlConn = string.Format("server={0};database={1};Integrated Security='SSPI';", db.Parent.Name, db.Name); string schema; string qName; string sqlExec = @"select q.conversation_group_id, q.conversation_handle, q.message_sequence_number, q.service_name, se.far_service, q.service_contract_name, q.message_type_name, q.validation, message_body from {0}.{1} q join sys.conversation_endpoints se on q.conversation_handle = se.conversation_handle"; //where se.state in ('SO','SI','CO')"; SqlConnection conn = CreateNewConnection(db); SqlCommand cmd = conn.CreateCommand(); if (bServ == null && q != null) { schema = q.Schema; qName = q.Name; } else { schema = bServ.QueueSchema; qName = bServ.QueueName; sqlExec += " and q.service_id = @id"; int id = bServ.ID; cmd.Parameters.Add("@id", SqlDbType.Int, 4); cmd.Parameters["@id"].Value = id; } if (handle != Guid.Empty) { sqlExec += " and q.conversation_handle = @h"; cmd.Parameters.Add("@h", SqlDbType.UniqueIdentifier); cmd.Parameters["@h"].Value = handle; } sqlExec = string.Format(sqlExec, schema, qName); cmd.CommandText = sqlExec; if (conn.Database != db.Name) conn.ChangeDatabase(db.Name); SqlDataReader reader = cmd.ExecuteReader(); SSBIMessageCollection scColl = new SSBIMessageCollection(); while (reader.Read()) { Guid cnvHandle; SSBIMessage msg = new SSBIMessage(); msg.ConversationGroupId = reader.GetGuid(0); cnvHandle = reader.GetGuid(1); msg.ConversationHandle = cnvHandle; //msg.Conversation = new Conversation(srv, cnvHandle); msg.SequenceNumber = reader.GetInt64(2); msg.RemoteServiceName = reader.GetString(4); msg.ServiceName = reader.GetString(3); msg.ContractName = reader.GetString(5); msg.Type = reader.GetString(6); msg.Validation = reader.GetString(7); if (!reader.IsDBNull(8)) { SqlBytes sb = reader.GetSqlBytes(8); msg.Body = sb.Stream; } else msg.Body = null; scColl.Add(msg); } conn.Close(); return scColl; }