|
0
|
1 DROP Procedure Person_SelectByKey
|
|
|
2 GO
|
|
|
3 DROP Procedure Person_SelectAll
|
|
|
4 GO
|
|
|
5 DROP Procedure Person_SelectByName
|
|
|
6 GO
|
|
|
7 DROP Procedure Person_SelectListByName
|
|
|
8 GO
|
|
|
9 DROP Procedure Person_Insert
|
|
|
10 GO
|
|
|
11 DROP Procedure Person_Update
|
|
|
12 GO
|
|
|
13 DROP Procedure Person_Delete
|
|
|
14 GO
|
|
|
15 DROP Procedure Patient_SelectAll
|
|
|
16 GO
|
|
|
17 DROP Procedure Patient_SelectByName
|
|
|
18 GO
|
|
|
19 DROP Procedure Scalar_DataReader
|
|
|
20 GO
|
|
|
21 DROP TABLE Dual
|
|
|
22 GO
|
|
|
23 DROP TABLE BinaryData
|
|
|
24 GO
|
|
|
25 DROP TABLE DataTypeTest
|
|
|
26 GO
|
|
|
27 DROP TABLE Doctor
|
|
|
28 GO
|
|
|
29 DROP TABLE Patient
|
|
|
30 GO
|
|
|
31 DROP TABLE Person
|
|
|
32 GO
|
|
|
33
|
|
|
34 CREATE TABLE Person (
|
|
|
35 PersonID Int IDENTITY,
|
|
|
36 FirstName Text(50) NOT NULL,
|
|
|
37 LastName Text(50) NOT NULL,
|
|
|
38 MiddleName Text(50),
|
|
|
39 Gender Text(1) NOT NULL,
|
|
|
40
|
|
|
41 CONSTRAINT PK_Peson PRIMARY KEY (PersonID)
|
|
|
42 )
|
|
|
43 GO
|
|
|
44
|
|
|
45 CREATE TABLE Doctor (
|
|
|
46 PersonID Int NOT NULL,
|
|
|
47 Taxonomy Text(50) NOT NULL,
|
|
|
48
|
|
|
49 CONSTRAINT OK_Doctor PRIMARY KEY (PersonID)
|
|
|
50 )
|
|
|
51 GO
|
|
|
52
|
|
|
53 CREATE TABLE Patient (
|
|
|
54 PersonID Int NOT NULL,
|
|
|
55 Diagnosis Text(255) NOT NULL,
|
|
|
56
|
|
|
57 CONSTRAINT PK_Patient PRIMARY KEY (PersonID)
|
|
|
58 )
|
|
|
59 GO
|
|
|
60
|
|
|
61 ALTER TABLE Doctor
|
|
|
62 ADD CONSTRAINT PersonDoctor FOREIGN KEY (PersonID) REFERENCES Person ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
63 GO
|
|
|
64
|
|
|
65 ALTER TABLE Patient
|
|
|
66 ADD CONSTRAINT PersonPatient FOREIGN KEY (PersonID) REFERENCES Person ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
67 GO
|
|
|
68
|
|
|
69 CREATE TABLE BinaryData (
|
|
|
70 BinaryDataID AutoIncrement,
|
|
|
71 Data Image NOT NULL,
|
|
|
72
|
|
|
73 CONSTRAINT PrimaryKey PRIMARY KEY (BinaryDataID)
|
|
|
74 );
|
|
|
75 GO
|
|
|
76
|
|
|
77 CREATE TABLE DataTypeTest (
|
|
|
78 DataTypeID AutoIncrement,
|
|
|
79 Binary_ Image,
|
|
|
80 Boolean_ Long,
|
|
|
81 Byte_ Byte DEFAULT 0,
|
|
|
82 Bytes_ Image,
|
|
|
83 Char_ Text(1),
|
|
|
84 DateTime_ DateTime,
|
|
|
85 Decimal_ Currency DEFAULT 0,
|
|
|
86 Double_ Double DEFAULT 0,
|
|
|
87 Guid_ Uniqueidentifier,
|
|
|
88 Int16_ SmallInt DEFAULT 0,
|
|
|
89 Int32_ Long DEFAULT 0,
|
|
|
90 Int64_ Long DEFAULT 0,
|
|
|
91 Money_ Currency DEFAULT 0,
|
|
|
92 SByte_ Byte DEFAULT 0,
|
|
|
93 Single_ Single DEFAULT 0,
|
|
|
94 Stream_ Image,
|
|
|
95 String_ Text(50) WITH COMP,
|
|
|
96 UInt16_ SmallInt DEFAULT 0,
|
|
|
97 UInt32_ Long DEFAULT 0,
|
|
|
98 UInt64_ Long DEFAULT 0,
|
|
|
99 Xml_ Text WITH COMP,
|
|
|
100
|
|
|
101 CONSTRAINT PrimaryKey PRIMARY KEY (DataTypeID)
|
|
|
102 );
|
|
|
103 GO
|
|
|
104
|
|
|
105 CREATE TABLE Dual (Dummy Text(10));
|
|
|
106 GO
|
|
|
107
|
|
|
108 INSERT INTO Person (FirstName, LastName, Gender) VALUES ("John", "Pupkin", "M")
|
|
|
109 GO
|
|
|
110 INSERT INTO Person (FirstName, LastName, Gender) VALUES ("Tester", "Testerson", "M")
|
|
|
111 GO
|
|
|
112 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, "Psychiatry")
|
|
|
113 GO
|
|
|
114 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, "Hallucination with Paranoid Bugs' Delirium of Persecution")
|
|
|
115 GO
|
|
|
116
|
|
|
117 INSERT INTO DataTypeTest
|
|
|
118 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
119 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
120 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
|
|
|
121 VALUES
|
|
|
122 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
123 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
124 NULL, NULL, NULL, NULL, NULL, NULL, NULL)
|
|
|
125 GO
|
|
|
126
|
|
|
127 INSERT INTO DataTypeTest
|
|
|
128 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
129 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
130 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
|
|
|
131 Xml_)
|
|
|
132 VALUES
|
|
|
133 (1, True, 255, 1, "B", Now(), 12345.67,
|
|
|
134 1234.567, 1, 32767, 32768, 1000000, 12.3456, 127,
|
|
|
135 1234.123, "12345678", "string", 32767, 32768, 2000000000,
|
|
|
136 "<root><element strattr='strvalue' intattr='12345'/></root>")
|
|
|
137 GO
|
|
|
138
|
|
|
139 INSERT INTO Dual (Dummy) VALUES ('X')
|
|
|
140 GO
|
|
|
141
|
|
|
142 CREATE Procedure Person_SelectByKey(
|
|
|
143 [@id] Long)
|
|
|
144 AS
|
|
|
145 SELECT * FROM Person WHERE PersonID = [@id];
|
|
|
146 GO
|
|
|
147
|
|
|
148 CREATE Procedure Person_SelectAll
|
|
|
149 AS
|
|
|
150 SELECT * FROM Person;
|
|
|
151 GO
|
|
|
152
|
|
|
153 CREATE Procedure Person_SelectByName(
|
|
|
154 [@firstName] Text(50),
|
|
|
155 [@lastName] Text(50))
|
|
|
156 AS
|
|
|
157 SELECT
|
|
|
158 *
|
|
|
159 FROM
|
|
|
160 Person
|
|
|
161 WHERE
|
|
|
162 FirstName = [@firstName] AND LastName = [@lastName];
|
|
|
163 GO
|
|
|
164
|
|
|
165 CREATE Procedure Person_SelectListByName(
|
|
|
166 [@firstName] Text(50),
|
|
|
167 [@lastName] Text(50))
|
|
|
168 AS
|
|
|
169 SELECT
|
|
|
170 *
|
|
|
171 FROM
|
|
|
172 Person
|
|
|
173 WHERE
|
|
|
174 FirstName like [@firstName] AND LastName like [@lastName];
|
|
|
175 GO
|
|
|
176
|
|
|
177 CREATE Procedure Person_Insert(
|
|
|
178 [@FirstName] Text(50),
|
|
|
179 [@MiddleName] Text(50),
|
|
|
180 [@LastName] Text(50),
|
|
|
181 [@Gender] Text(1))
|
|
|
182 AS
|
|
|
183 INSERT INTO Person
|
|
|
184 (FirstName, MiddleName, LastName, Gender)
|
|
|
185 VALUES
|
|
|
186 ([@FirstName], [@MiddleName], [@LastName], [@Gender]);
|
|
|
187 GO
|
|
|
188
|
|
|
189 CREATE Procedure Person_Update(
|
|
|
190 [@id] Long,
|
|
|
191 [@PersonID] Long,
|
|
|
192 [@FirstName] Text(50),
|
|
|
193 [@MiddleName] Text(50),
|
|
|
194 [@LastName] Text(50),
|
|
|
195 [@Gender] Text(1))
|
|
|
196 AS
|
|
|
197 UPDATE
|
|
|
198 Person
|
|
|
199 SET
|
|
|
200 LastName = [@LastName],
|
|
|
201 FirstName = [@FirstName],
|
|
|
202 MiddleName = [@MiddleName],
|
|
|
203 Gender = [@Gender]
|
|
|
204 WHERE
|
|
|
205 PersonID = [@id];
|
|
|
206 GO
|
|
|
207
|
|
|
208 CREATE Procedure Person_Delete(
|
|
|
209 [@PersonID] Long)
|
|
|
210 AS
|
|
|
211 DELETE FROM Person WHERE PersonID = [@PersonID];
|
|
|
212 GO
|
|
|
213
|
|
|
214 CREATE Procedure Patient_SelectAll
|
|
|
215 AS
|
|
|
216 SELECT
|
|
|
217 Person.*, Patient.Diagnosis
|
|
|
218 FROM
|
|
|
219 Patient, Person
|
|
|
220 WHERE
|
|
|
221 Patient.PersonID = Person.PersonID;
|
|
|
222 GO
|
|
|
223
|
|
|
224 CREATE Procedure Patient_SelectByName(
|
|
|
225 [@firstName] Text(50),
|
|
|
226 [@lastName] Text(50))
|
|
|
227 AS
|
|
|
228 SELECT
|
|
|
229 Person.*, Patient.Diagnosis
|
|
|
230 FROM
|
|
|
231 Patient, Person
|
|
|
232 WHERE
|
|
|
233 Patient.PersonID = Person.PersonID
|
|
|
234 AND FirstName = [@firstName] AND LastName = [@lastName];
|
|
|
235 GO
|
|
|
236
|
|
|
237 CREATE Procedure Scalar_DataReader
|
|
|
238 AS
|
|
|
239 SELECT 12345 AS intField, "54321" AS stringField;
|
|
|
240 GO
|
|
|
241
|
|
|
242
|
|
|
243 DROP TABLE Parent
|
|
|
244 GO
|
|
|
245 DROP TABLE Child
|
|
|
246 GO
|
|
|
247 DROP TABLE GrandChild
|
|
|
248 GO
|
|
|
249
|
|
|
250 CREATE TABLE Parent (ParentID int, Value1 int NULL)
|
|
|
251 GO
|
|
|
252 CREATE TABLE Child (ParentID int, ChildID int)
|
|
|
253 GO
|
|
|
254 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
|
|
|
255 GO
|
|
|
256 DROP TABLE LinqDataTypes
|
|
|
257 GO
|
|
|
258
|
|
|
259 CREATE TABLE LinqDataTypes
|
|
|
260 (
|
|
|
261 ID int,
|
|
|
262 MoneyValue decimal(10,4),
|
|
|
263 DateTimeValue datetime,
|
|
|
264 DateTimeValue2 datetime,
|
|
|
265 BoolValue bit,
|
|
|
266 GuidValue uniqueidentifier,
|
|
|
267 BinaryValue OleObject NULL,
|
|
|
268 SmallIntValue smallint,
|
|
|
269 IntValue int NULL,
|
|
|
270 BigIntValue long NULL
|
|
|
271 )
|
|
|
272 GO
|
|
|
273
|
|
|
274 DROP TABLE TestIdentity
|
|
|
275 GO
|
|
|
276
|
|
|
277 CREATE TABLE TestIdentity (
|
|
|
278 ID Int IDENTITY,
|
|
|
279 CONSTRAINT PK_TestIdentity PRIMARY KEY (ID)
|
|
|
280 )
|
|
|
281 GO
|
