当前文件： SmallBusinessStarterKit/smallbusiness_vb/App_Data/Data.sql[16K，2009-6-12 11:54:22]，打开代码结构图

		            
  1
  2DROP TABLE Categorization
  3DROP TABLE Cateogory
  4DROP TABLE Item
  5DROP TABLE News
  6DROP TABLE People
  7DROP TABLE Testimonials
  8--- Download From 51aspx.com
  9DROP PROCEDURE GetRootCategories
 10DROP PROCEDURE GetChildItems
 11DROP PROCEDURE GetNonRootCategories
 12DROP PROCEDURE GetNews
 13DROP PROCEDURE GetTestimonials
 14DROP PROCEDURE GetPeople
 15
 16CREATE TABLE Item(
 17id                    nvarchar(32), 
 18visible                bit NOT NULL, 
 19title                nvarchar(256) NOT NULL, 
 20description nvarchar(1024), 
 21price                float, 
 22inStock                bit, 
 23imageUrl            nvarchar(256), 
 24imageAltText        nvarchar(256), 
 25displayOrder        int,
 26PRIMARY KEY (id))
 27
 28
 29CREATE TABLE Category(
 30id                nvarchar(32), 
 31visible            bit NOT NULL, 
 32title            nvarchar(256), 
 33description        nvarchar(1024),
 34imageUrl        nvarchar(256), 
 35imageAltText    nvarchar(256), 
 36parentCategoryId nvarchar(32), 
 37displayOrder     int,
 38PRIMARY KEY (id), 
 39FOREIGN KEY(parentCategoryId) REFERENCES Category(id))
 40
 41
 42
 43CREATE TABLE Categorization(
 44itemId                nvarchar(32) NOT NULL, 
 45parentCategoryId    nvarchar(32) NOT NULL,
 46PRIMARY KEY            (itemId, parentCategoryId), 
 47FOREIGN KEY            (itemId) REFERENCES Item(id),
 48FOREIGN KEY            (parentCategoryId) REFERENCES Category(id))
 49
 50
 51CREATE TABLE News(
 52id                    nvarchar(32), 
 53visible                bit NOT NULL, 
 54title nvarchar(256) NOT NULL, 
 55date                DATETIME NOT NULL,  
 56content                nvarchar(4096), 
 57imageUrl            nvarchar(256), 
 58imageAltText        nvarchar(256), 
 59displayOrder        int,
 60PRIMARY KEY (id))
 61
 62
 63CREATE TABLE People(
 64id                    nvarchar(32) NOT NULL, 
 65visible                bit    NOT NULL, 
 66firstName            nvarchar(64) NOT NULL, 
 67middleName            nvarchar(64), 
 68lastName            nvarchar(64) NOT NULL, 
 69title                nvarchar(128), 
 70description            nvarchar(256), 
 71email                nvarchar(256), 
 72phone                nvarchar(64), 
 73fax                    nvarchar(64), 
 74streetAddress        nvarchar(128), 
 75city                nvarchar(128), 
 76state                nvarchar(64), 
 77postalCode            nvarchar(64), 
 78country                nvarchar(64), 
 79imageUrl            nvarchar(256), 
 80imageAltText        nvarchar(256), 
 81displayOrder        int,
 82PRIMARY KEY (id))
 83
 84CREATE TABLE Testimonials(
 85id                 nvarchar(32)    NOT NULL, 
 86visible             bit            NOT NULL, 
 87title             nvarchar(256)    NOT NULL, 
 88date             DateTime        NOT NULL,
 89content             nvarchar(1024),
 90testifier         nvarchar(256),
 91testifierTitle   nvarchar(256),
 92testifierCompany nvarchar(256), 
 93imageUrl         nvarchar(256), 
 94imageAltText     nvarchar(256), 
 95displayOrder     int, 
 96PRIMARY KEY (id))
 97
 98
 99CREATE PROCEDURE GetRootCategories 
100AS 
101SELECT     id, visible, title, description, imageUrl, imageAltText, parentCategoryId
102FROM         Category
103WHERE     (parentCategoryId IS NULL)
104ORDER BY displayOrder
105
106
107CREATE PROCEDURE GetChildItems 
108    @categoryId nvarchar(32)
109AS 
110SELECT Item.id, Item.visible, Item.title, Item.description, Item.price, Item.inStock, Item.imageUrl, Item.imageAltText 
111FROM Item 
112INNER JOIN Categorization ON Item.id = Categorization.itemId 
113WHERE (Categorization.parentCategoryId = @categoryId)
114ORDER BY Item.displayOrder
115
116CREATE PROCEDURE GetItem 
117    @itemId nvarchar(32)
118AS 
119SELECT Item.id, Item.visible, Item.title, Item.description, Item.price, Item.inStock, Item.imageUrl, Item.imageAltText 
120FROM Item 
121WHERE (Item.id = @itemId)
122
123
124
125CREATE PROCEDURE GetNonRootCategories 
126    @categoryId nvarchar(32)
127AS 
128SELECT     id, visible, title, description, imageUrl, imageAltText, parentCategoryId
129FROM         Category
130WHERE     (parentCategoryId = @categoryId)
131ORDER BY   displayOrder
132
133CREATE PROCEDURE GetNews 
134AS SELECT id, visible, title, date, [content], imageUrl, imageAltText  
135FROM News
136ORDER BY   date DESC
137
138CREATE PROCEDURE GetNewsItem
139    @id nvarchar(32)
140AS 
141SELECT id, visible, title, date, [content], imageUrl, imageAltText  
142FROM News 
143WHERE (id = @id)
144
145
146
147CREATE PROCEDURE GetTestimonials 
148AS SELECT     id, visible, title, date, [content], testifier, testifierTitle, testifierCompany, imageUrl, imageAltText
149FROM       Testimonials
150ORDER BY   displayOrder
151
152CREATE PROCEDURE GetPeople 
153AS SELECT     id, visible, firstName, middleName, lastName, title, description, email, phone, fax, streetAddress, city, state, postalCode, country, imageUrl, imageAltText
154FROM          People
155ORDER BY      displayOrder
156
157
158
159INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('01',1,'Category 1','Category 1 Description', 'Category1.jpg', 'image of Category 1', null , 1)
160INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('02',1,'Category 2','Category 2 Description', 'Category2.jpg', 'image of Category 2' , null , 2)
161INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('03',1,'Category 3','Category 3 Description', 'Category3.jpg', 'image of Category 3', null, 3)
162INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('04',1,'Category 4','Category 4 Description', 'Category4.jpg', 'image of Category 4', null, 4)
163INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('05',1,'Category 5','Category 5 Description', 'Category5.jpg', 'image of Category 5', null, 5)
164INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('06',1,'Category 6','Category 6 Description', 'Category6.jpg', 'image of Category 6', '01', 6)
165INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('07',1,'Category 7','Category 7 Description', 'Category7.jpg', 'image of Category 7', '01', 7)
166INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('08',1,'Category 8','Category 8 Description', 'Category8.jpg', 'image of Category 8', '02', 8)
167INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('09',1,'Category 9','Category 9 Description', 'Category9.jpg', 'image of Category 9', '02', 9)
168INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('10',1,'Category 10','Category 10 Description', 'Category10.jpg', 'image of Category 10', '02', 10)
169INSERT INTO Category (id, visible, title, description, imageUrl, imageAltText, parentCategoryId, displayOrder) VALUES ('11',1,'Category 11','Category 11 Description', 'Category11.jpg', 'image of Category 11', '09', 11)
170
171INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('01',1,'Item 1','Item 1 Description', 12.00,1, 'item1.jpg', 'image of item 1', 1)
172INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('02',1,'Item 2','Item 2 Description', 12.00,1, 'item2.jpg', 'image of item 2', 2)
173INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('03',1,'Item 3','Item 3 Description', 12.00,1, 'item3.jpg', 'image of item 3', 3)
174INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('04',1,'Item 4','Item 4 Description', 12.00,1, 'item4.jpg', 'image of item 4', 4)
175INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('05',1,'Item 5','Item 5 Description', 12.00,1, 'item5.jpg', 'image of item 5', 5)
176INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('06',1,'Item 6','Item 6 Description', 12.00,1, 'item6.jpg', 'image of item 6', 6)
177INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('07',1,'Item 7','Item 7 Description', 12.00,1, 'item7.jpg', 'image of item 7', 7)
178INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('08',1,'Item 8','Item 8 Description', 12.00,1, 'item8.jpg', 'image of item 8', 8)
179INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('09',1,'Item 9','Item 9 Description', 12.00,1, 'item9.jpg', 'image of item 9', 9)
180INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('10',1,'Item 10','Item 10 Description', 12.00,1, 'item10.jpg', 'image of item 10', 10)
181INSERT INTO Item (id, visible, title, description, price, inStock, imageUrl, imageAltText, displayOrder) VALUES ('11',1,'Item 11','Item 11 Description', 12.00,1, 'item11.jpg', 'image of item 11', 11)
182
183# a child item may belong to multiple parent categories
184INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('01','01');
185INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('02','02');
186INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('03','03');
187INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('04','04');
188INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('05','05');
189INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('06','06');
190INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('07','07');
191INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('08','08');
192INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('01','02');
193INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('01','04');
194INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('02','06');
195INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('02','07');
196INSERT INTO Categorization (itemId, parentCategoryId) VALUES ('02','08');
197
198INSERT INTO Testimonials (id, visible, title, date, content, testifier, testifierTitle, testifierCompany, imageUrl, imageAltText, displayOrder) VALUES     ('01', 1, 'Northwind Traders sadipscing elitr Fabrikam', '07-12-2006 03:00:00', 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.','Pernille Halberg', 'Director', 'Northwind Traders', 'customer.jpg', 'Pernille Halberg - Northwind Traders', 1);
199INSERT INTO Testimonials (id, visible, title, date, content, testifier, testifierTitle, testifierCompany, imageUrl, imageAltText, displayOrder) VALUES     ('02', 1, 'Contoso Pharmaceuticals sadipscing elitr Fabrikam', '07-12-2006 12:00:00', 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.','Darren Parker', 'Manager', 'Contoso Pharmaceuticals', 'customer.jpg', 'Darren Parker -  Manager, Contoso Pharmaceuticals',2);
200INSERT INTO Testimonials (id, visible, title, date, content, testifier, testifierTitle, testifierCompany, imageUrl, imageAltText, displayOrder) VALUES     ('03', 1, 'Contoso Pharmaceuticals sadipscing elitr Fabrikam', '07-10-2006 02:34:00', 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.','Nielsen Bardenfleth', 'Engineer', 'Contoso Pharmaceuticals', 'customer.jpg', 'Nielsen Bardenfleth-  Manager, Contoso Pharmaceuticals',3);
201
202
203INSERT INTO News (id, visible, title, date, [content], imageUrl, imageAltText, displayOrder) VALUES     ('01', 1, 'Consectetur Fabrikam Dolor Sit Amet', '02-12-2006 12:00:00', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum', 'news1.jpg', 'Fabrikam - anim id est laborum', 1)
204INSERT INTO News (id, visible, title, date, [content], imageUrl, imageAltText, displayOrder) VALUES     ('02', 1, 'Fabrikam Consectetur Adipisicing Elit', '03-12-2006 12:00:00', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum', 'news2.jpg', 'Fabrikam - anim id est laborum', 2)
205INSERT INTO News (id, visible, title, date, [content], imageUrl, imageAltText, displayOrder) VALUES     ('03', 1, 'Mollit Anim Id Est Laborum', '03-10-2006 12:00:00', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum', 'news1.jpg', 'Fabrikam - anim id est laborum', 2)
206
207INSERT INTO People (id, visible, firstName, middleName, lastName,title, description, email, phone, fax, streetAddress, city , state, postalCode, country, imageUrl, imageAltText , displayOrder) VALUES ('01',1,'Jesper','','Aaberg','President','Lorem ipsum dolor sit amet, consectetuer adipiscing elit', 'aaberg@fabrikam.com','2025550100','2025556100', '567 Main St','Buffalo','NY','99887','USA','emp01.jpg','Jesper - President', 1)
208INSERT INTO People (id, visible, firstName, middleName, lastName,title, description, email, phone, fax, streetAddress, city , state, postalCode, country, imageUrl, imageAltText , displayOrder) VALUES ('02',1,'Aalling','','Lene','Director','Lorem ipsum dolor sit amet, consectetuer adipiscing elit', 'lene@fabrikam.com','2025550100','2025556100', '567 Main St','Buffalo','NY','99887','USA','emp01.jpg','Aalling - Manager', 2)
209INSERT INTO People (id, visible, firstName, middleName, lastName,title, description, email, phone, fax, streetAddress, city , state, postalCode, country, imageUrl, imageAltText , displayOrder) VALUES ('03',1,'Abbas','M','Syed','Editor','Lorem ipsum dolor sit amet, consectetuer adipiscing elit', 'syed@fabrikam.com','2025550102','2025556100', '567 Main St','Buffalo','NY','99887','USA','emp01.jpg','Abbas - Editor', 3)
210
211