materialy

2-Sty-2013

Do kursu 2781:

http://www.mobilo24.eu/wp-content/uploads/2011/02/Przyklad_Broker_ok.zip

http://www.mobilo24.eu/wp-content/uploads/2013/01/dzien-3.zip

http://www.mobilo24.eu/wp-content/uploads/2013/01/2781-2.zip

http://www.mobilo24.eu/wp-content/uploads/2013/01/skrypt_ns.txt

 

 

ENDPOINT

IF EXISTS (SELECT * FROM sys.objects WHERE name = N’tbl_Players’ AND type = ‚U’)
DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
–Create the table
CREATE TABLE tbl_Players (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
)

–Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚A. Won’,’India’,10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚A. Cricket’,’India’,10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚B. Dhanman’,’India’,10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚C. Barsat’,’India’,10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚A. Mirza’,’India’,2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚M. Karol’,’US’,15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚Z. Hamsa’,’US’,3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚K. Loly’,’US’,6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚S. Summer’,’US’,87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚J.June’,’US’,12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚A.Namaki’,’Australia’,1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚Z. Samaki’,’Australia’,2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚MS. Kaki’,’Australia’,40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚S. Boon’,’Australia’,170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚DC. Shane’,’Australia’,28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚S. Noami’,’Singapore’,165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚Z. Biswas’,’Singapore’,73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚K. Dolly’,’Singapore’,65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚S. Winter’,’Singapore’,7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(‚J.August’,’Singapore’,9,99,98, 890)

If Exists (Select * from sys.objects where name = ‚usp_SelectPlayerRecords’ and type = ‚P’)
Drop Procedure usp_SelectPlayerRecords
Go
— Create the stored procedure
Create Procedure [dbo].[usp_SelectPlayerRecords]
As
Begin
Select
PlayerID
,PlayerName
, BelongsTo
, MatchPlayed
,RunsMade
,WicketsTaken
,FeePerMatch
From
tbl_Players
End

IF EXISTS ( SELECT NAME FROM sys.http_endpoints WHERE NAME = N’PlayerRecord_EP’ )
DROP ENDPOINT PlayerRecord_EP
GO
CREATE ENDPOINT [PlayerRecord_EP]
STATE=STARTED
AS HTTP
(
PATH=N’/PlayerName’
, PORTS = (CLEAR)
,AUTHENTICATION = (INTEGRATED)
, SITE=N’localhost’
, CLEAR_PORT = 8000
)
FOR SOAP
(
WEBMETHOD ‚PlayerList’
( NAME=N'[SSISExperiments].[dbo].[usp_SelectPlayerRecords]’)
, BATCHES=DISABLED
, WSDL=DEFAULT
, DATABASE=N’SSISExperiments’
, NAMESPACE=N’http://SSISExperiments/Players’
)
GO

SZYFROWANIE

CREATE
DATABASE testdb

GO

USE testdb

GO

CREATE
MASTER KEY ENCRYPTION BY PASSWORD=’PassToEncryptDatabaseKey’

GO

CREATE
CERTIFICATE testcert WITH SUBJECT = ‚ToEncrypt’

GO

CREATE
TABLE tcrypt (id INT, name VARCHAR (30),

encryptdata
VARBINARY (4000))

GO

INSERT INTO
tcrypt(id, name, encryptdata)

VALUES (1,
‚testName’, ENCRYPTBYCERT (CERT_ID(‚testcert’), ‚testName’))

GO

SELECT id,
name, CONVERT (VARCHAR, DECRYPTBYCERT (CERT_ID (‚TESTCERT’), encryptdata))

FROM tcrypt

GO

SELECT id,
name, CONVERT (VARCHAR, encryptdata)

FROM tcrypt

GO

USE master

GO