Dethi and dapan afss

Question 1:                                                                  [1]
– Create a Database C0xxx_ROLLNUMBER        [0.5]
– Use this Database         [0.5]
Question 2:                                                     [3]
–    Using Create XML schema … statement to create the XML schema with name “MobileSpecSchema” that the element root node’s name is “Mobile”. Below is a fragment of schema:         [1]
<xsd:element name=”MobileName” type=”xsd:string” />
<xsd:element name=”Colors” type=”xsd:string” />
<xsd:element name=”OS” type=”xsd:string” />
<xsd:element name=”InternalMemory” type=”xsd:string” />
<xsd:element name=”ScreenSize” type=”xsd:string” />
<xsd:element name=”Weight” type=”xsd:string” />
<xsd:element name=”Price” type=”xsd:string” />

–    Using Create table statement to create an [NewMobiles] table as following:            [1]
o    Column 1: [MobileID] int NOT NULL (do not set primary key here)
o    Column 2: [MobileName] nvarchar(30)
o    Column 3: [Specification] XML (using xml schema “MobileSpecSchema” to validate data for this column)

–    Apply the Primary Key Constraint and IDENTIY(1, 1) for the “MobileID” column of the [NewMobiles] table.                                         [1]

Question 3:                                                      [9]
–    Use sys.databases view to check the status of Service Broker is enabled or not, if broker service status if off then set it on.                                        [1]
–    Using Create message … statement to create a message type with name “OfferingMessageType” for the system. Please check the message  type in order to make sure that the data is valid against the MobileSpecSchema.                        [1]
–    Using Create contract … statement to create a contract with name “BusinessContract” for the system that uses the “OfferingMessageType” message type for both Initiator and Target.    [0.5]
–    Using Create queue … statement to create a queue with name “OfferingQueue” for the system to receive offers from outside suppliers.                                [0.5]
–    Using Create service … statement to create two services with name “SupplierService” and “CentralService” for the system that uses the “BusinessContract” contract and “OfferingQueue” queue.                                        [1]
–    Using Create procedure … statement to create a procedure with name “SendOfferProc” for the system that send an offer message to CentralService from SupplierService. This procedure must fulfill following requirements:                                    [2]
o    Has one parameter @Details with type XML valid against the MobileSpecSchema
o    Create dialog conversation on “BusinessContract” contract
o    Send a message with “OfferingMessageType” message type from service client (SupplierService) to service server (CentralService).
–     Using Create procedure … statement to create a procedure with name “ReceiveOfferProc” for the system. This procedure must fulfill following requirements:                    [2]
o    If the queue is empty then print a message ‘The queue is empty’
o    If the queue is not empty then now:
    Read the queue and use XQuery to extract the MobileName value from message body.
    Inserts a new row into [NewMobiles] table.
    Select data from [NewMobiles] table
–    Run time two above stored procedures with following parameters:                 [1]
<Mobile>
<MobileName>Google Nexus 4</MobileName>
<Colors>Red,Black,Silver</Colors>
<OS>Android 2.3</OS>
<InternalMemory>256MB</InternalMemory>
<ScreenSize>4.1in</ScreenSize>
<Weight>145grams</Weight>
<Price>450</Price>
</Mobile>
Question 4:                                                      [6]
–    Using Create procedure … statement to create a procedure with name “GetLastOffer”. It gets lastest offer in “NewMobiles” table base on MobileID column, then returns following fields:    [2]
o    MobileID
o    MobileName
o    Price ( which is extracted from Price element in [Specification]).
–    Using sp_reserve_http_namespace … statement to register a HTTP endpoint with name http://localhost:8896/Offers                                     [1]
–    Using Create endpoint … statement to create an endpoint with name “GetLastOfferEndPoint” endpoint.                                                [3]
o    PATH Name: /Offers
o    WEBMETHOD Name: GetLastOfferMethod
o    WSDL: Default
o    Stored procedure name : C0xxx_ROLLNUMBER.DBO.GetLastOffer
o    DATABASE: C0xxx_ROLLNUMBER
o    NAMESPACE: http://aprotrain.com/RollNumber
Question 5:                                                      [6]
–    Suppose that:
o     There is a DLL file with name “MobileOffers.dll” that stored in “.\Exam\” directory. The student will be supply this DLL file
o    The source code as following:
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MobileOffers
{
public class RemoveHelper
{
[SqlProcedure]
public static void RemoveMobileOffer(SqlInt32 ID)
{
// Create connection
SqlConnection objConn = new SqlConnection(“context connection=true”);

// Create command with delete query
SqlCommand objComm = new SqlCommand(“Delete From NewMobiles Where MobileID=@ID”, objConn);
objComm.Parameters.AddWithValue(“ID”, ID);

try
{
objConn.Open();

// Execute delete query and get affected rows back
int AffectedRows = objComm.ExecuteNonQuery();
if (AffectedRows != 1)
{
// Something wrong, it must be only one row deleted
SqlContext.Pipe.Send(“More than one records deleted!”);
}
else
{
// OK
SqlContext.Pipe.Send(“Removing offer completed!”);
}
}
catch(SqlException sqlEx)
{
// Send error to SQL Client
SqlContext.Pipe.Send(“Error occured:” + sqlEx.Message);
}
finally{
// Close connection after all
objConn.Close();
}
}
}
}

–    Using Create Assembly … statement to register an assembly with name “MobileOffersAssembly” to SQL Server 2005 from “MobileOffers.dll” file.            [3]
–    Using Create procedure … statement to create a procedure with name “RemoveMobileOffer” with parameter @ID int from external name “MobileOffersAssembly” assembly.        [2]
–    Run time “RemoveMobileOffer” stored procedure with parameter value is an ID in NewMobiles table.                                                 [1]
Result of execute stored procedure:

Tips:  Using sp_configure @configname = ‘clr_enabled’, @configvalue = 1 to enable CLR.

 

Giai

 

create database C0911L_1532
go

use C0911L_1532
go

create xml schema collection MobileSpecSchema
as N'<?xml version=”1.0″ encoding=”utf-16″ ?>
<xs:schema xmlns:xs=”http://www.w3.org/2001/XMLSchema”&gt;
<xs:element name=”Mobile”>
<xs:complexType>
<xs:sequence>
<xs:element name=”MobileName” type=”xs:string” />
<xs:element name=”Colors” type=”xs:string” />
<xs:element name=”OS” type=”xs:string” />
<xs:element name=”InternalMemory” type=”xs:string” />
<xs:element name=”ScreenSize” type=”xs:string” />
<xs:element name=”Weight” type=”xs:string” />
<xs:element name=”Price” type=”xs:string” />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>’
go

create table NewMobiles
(
MobileID int not null identity(1,1),
MobileName nvarchar(30),
Specification xml(MobileSpecSchema)
)
go

alter table NewMobiles add constraint PK_MobileID primary key (MobileID)

–use master drop database C0911L_1532
–drop table NewMobiles

–alter table NewMobiles alter column MobileID identity(1,1)

select is_broker_enabled from sys.databases where database_id=DB_ID()
go

create message type OfferingMessageType
validation=valid_xml with schema collection MobileSpecSchema
go

create contract BusinessContract
(
OfferingMessageType sent by any
)
go

create queue OfferingQueue with status = on
go

create service SupplierService on queue OfferingQueue (BusinessContract)
go

create service CentralService on queue OfferingQueue (BusinessContract)
go

create proc SendOfferProc
@Details xml (MobileSpecSchema)
as
begin
declare @DialogID uniqueidentifier;

begin dialog @DialogID from service SupplierService to service ‘CentralService’
on contract BusinessContract with encryption=off;

send on conversation @DialogID
message type OfferingMessageType
(
@Details
)
end
go

create proc ReceiveOfferProc
as
begin
declare @flag int;
set @flag= (select COUNT(*) from OfferingQueue)
if (@flag = 0)
print ‘The queue is empty’
else
begin
declare @detail xml (MobileSpecSchema);
declare @name nvarchar(30);
receive top(1) @detail = cast(message_body as xml) from OfferingQueue
set @name = @detail.value(‘(/Mobile/MobileName)[1]’, ‘nvarchar(30)’)
insert into NewMobiles values (@name,@detail)
select * from NewMobiles
end
end
go

–select Specification.value(‘(Mobile/MobileName)[1]’, ‘nvarchar(30)’) from NewMobiles

exec SendOfferProc N'<Mobile>
<MobileName>Google Nexus 4</MobileName>
<Colors>Red,Black,Silver</Colors>
<OS>Android 2.3</OS>
<InternalMemory>256MB</InternalMemory>
<ScreenSize>4.1in</ScreenSize>
<Weight>145grams</Weight>
<Price>450</Price>
</Mobile>

go

exec ReceiveOfferProc
go

–question 4
create proc GetLastOffer
as
begin
select top(1) MobileID, MobileName, Specification.value(‘(/Mobile/Price)[1]’,’nvarchar(30)’) Price from NewMobiles order by MobileID desc
end
go

exec GetLastOffer
go

exec sp_reserve_http_namespace N’http://localhost:8896/Offers&#8217;

create endpoint GetLastOfferEndPoint
state = started
as http
(
path = ‘/Offers’,
authentication = (integrated),
ports = (clear),
clear_port = 8080,
site=’localhost’
)
for soap
(
webmethod ‘GetLastOfferMethod’
(name=’C0911L_1532.dbo.GetLastOffer’, schema=standard),
wsdl = default,
schema=standard,
database = ‘C0911L_1532’,
namespace = ‘http://aprotrain.com/RollNumber&#8217;
)
go

create assembly MobileOffersAssembly from ‘C:\Documents and Settings\c0911l1532\Desktop\lab last\MobileOffers\MobileOffers\bin\Debug\MobileOffers.dll’
go

create proc RemoveMobileOffer
@ID int
as external name MobileOffersAssembly.MobileOffers.RemoveMobileOffer
go

declare @id int
set @id = (select top(1) MobileID from NewMobiles)
exec RemoveMobileOffer @id
go

exec sp_configure @configname = ‘clr_enabled’ , @configvalue = 1
go

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s