NetTiers学习总结2--数据库

第二次数据库建库原则:表名称采用单数和Pascal命名法。当然。例如:FirstName,LastName,MiddleInitial;为表提供表描述，列和键等扩展属性;使用默认值，默认情况下，您的实体属性使用您的数据库默认值。紧记，默认值只有简单的常量和少数职能是支持,如getdate();.nettiers不能建议关系，可以使用建立外键关联。如:建立以下表:Person和Contact1--CreatetheBaseRelationship,hasauniqueprimarykeyamongstbothtables.2CREATETABLE[dbo].[Person](3[PersonId][int]NOTNULL,4[Name][varchar](50)NOTNULL,5CONSTRAINT[PK_Person]PRIMARYKEYCLUSTERED6(7[PersonId]ASC8)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]9)ON[PRIMARY]1012--Createtheothersideofthe1:1relationship13CREATETABLE[dbo].[Contact](14[ContactPersonId][int]NOTNULL,15[ContactNumber][varchar](30),16CONSTRAINT[PK_Contact]PRIMARYKEYCLUSTERED17(18[ContactPersonId]ASC19)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]20)ON[PRIMARY]212223--Exampleof1:1relationship24--ThecontactprimarykeyistheisalsoaforeignkeyrelationshiptothePersonId.25--Thereforecreatinga1:1relationship.2627ALTERTABLE[dbo].[Contact]WITHCHECKADDCONSTRAINT[FK_Contact_Person]28FOREIGNKEY([ContactPersonId])29REFERENCES[dbo].[Person]([PersonId])30GO创建Contact实体:1///summary2///HoldsaContactobject3///whichisrelatedtothisobjectthroughtherelationContact4////summary5[BindableAttribute()]6publicContactContact7{8get{returnentityData.Contact;}9set{entityData.Contact=value;}10}ForeveryContactentity,sinceyouareworkingwiththeactualforeignkey,thatisyourprimarykey,youwouldgetaPersonIdandaPersonIdSourceoftypeParent.1privatePerson_contactPersonIdSource=null;23///summary4///Getsorsetsthesourceseecref=Person/.5////summary6///valueThesourcePersonforContactPersonId./value7[Browsable(false),BindableAttribute()]8publicvirtualPersonContactPersonIdSource9{10get{returnthis._contactPersonIdSource;}11set{this._contactPersonIdSource=value;}12}索引的作用:Indexesarenotonlyapowerfulfeaturetohelpimproveyourqueryexecutionbecomemoreperformant,it'salsousedtocreatehandydataaccessAPImethodsbasedonthoseindexes.Theimportantfeaturesofanindexisthatitcancontain1-ncolumnsandhashastheabilitytobesetasaUNIQUEindex.Forexample,inthePersonTablewecreatedabove,ifweaddedanindextotheNamecolumn,andsaidthatcolumnwasUnique.TheninmydataaccessAPIIwouldgetamethodcalledPersonGetByName(stringname).Ifihadnotaddedthattheindexwasunique,thenthemethodwouldreturnacollectionoftypeTList.SQL:1CREATEUNIQUENONCLUSTEREDINDEX[IX_Person]ON[dbo].[Person]2(3[Name]ASC4)WITH(SORT_IN_TEMPDB=OFF,DROP_EXISTING=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF)ON[PRIMARY]OUTPUT:1///summary2///GetsrowsfromthedatasourcebasedonIX_Personindex.3////summary4///paramname=name/param5///returnsReturnsaninstanceoftheseecref=Person//returns6publicPersonGetByName(System.Stringname)7{8intcount=-1;9//CallsProviderspecificImplementation1011returnGetByName(null,name,0,int.MaxValue,outcount);12}存储过程ThereareseveraltimeswhenyouwanttoextendthedataaccessAPI,butyoustillwanttoleveragemuchofthegeneratedapproachtothedatalayer..netTiersofferestheabilitytowriteyourownproceduresandbeabletodothingsthat.netTierscannotdooutoftheboxorarespecializedtoyourapplication.Aneasyexamplewouldbeifyouwantedtocreateacustomstoredproceduretohandlegettingallproductsbelowacertaininventory.Whenyoubeginthegenerationprocess,theCodeSmithSchemaExplorerwillattempttodiscoveralloftherichmetadataprovidedtodetermineofthisprocedurereturnsaresultset,whichparametersittakes(input&output).1--Gettheproductsthathavelessunitsinstockthanthe@UnitsInStockparameter.2CREATEPROCEDUREdbo._Products_GetWithStockBelow3@UnitsInStocksmallint4AS56SELECT7[ProductID],8[ProductName],9[SupplierID],10[CategoryID],11[QuantityPerUnit],12[UnitPrice],13[UnitsInStock],14[UnitsOnOrder],15[ReorderLevel],16[Discontinued]17FROM18[dbo].[Products]19WHERE20[UnitsInStock]@UnitsInStock21GOOutput:1publicTListProductGetWithStockBelow(System.Int16unitsInStock)2{34intcount=-1;5//CallsProviderspecificImplementation67returnGetWithStockBelow(8null,unitsInStock,0,int.MaxValue,outcount);910}NOTE:Therearesomesituationswheretheprocedurewillnotreturnresultsasexpected.TheCustomStoredproceduresdon'tworkwhenusingtemptableswithinthecustomstoredprocedure.ThisisbecausewhenCodeSmith'sSchemaExplorerisdiscoveringthisinformation,itdoesn'thavenecessarypriveldgestocreateatemptable.Oneworkaroundistouseatablevariableinthestoredprocedureinsteadofatemptable.Warning:Tablevariablesareheldinmemoryontheserversodonotloadtoomanyrowsinthetablevariableortheserversperformancewillbeimpacted.ImportantInorderfor.netTierstoreturnanentitythatmapstothetableyou'vecreatedtheCustomStoredProcedurefor,allofthecolumnsbeingreturnedmustmatchtypeandbeinthecorrectorder.IfyounoticethatthegeneratedcodeforyourcspstartsreturningvoidinsteadofwhatyouexpectcheckthatyoucspisstillvalidEnumTables.netTierswillcreateenumsbasedontabledataforthetablesthatyoudesignate.Thisisusefulforfairlystaticdataortypetables.Rules:Thetablesyouwouldliketogenerateasenumsmustmeetthefollowingrules.1.Thefirstcolumnmustbeaprimarykey(typicallythiswouldbeanintIdentitycolumn),2.Thesecondcolumnmusthaveauniquecolumnconstraintindex,theoptionalthirdcolumnwillbethedescriptionofthegeneratedenum.3.YoumustalsoselectthistableintheSourceTableaswel