Database Explorer API Overview

The database explorer API has been designed to provide rich and extensive access to schema meta-data of databases in a simple and intuitive manner. The API currently supports SQL Server 2005, but has been designed to provide easy support for other databases via a provider model.

The API uses the core concepts that you'd be used to using when talking about databases such as table, column, index, constraint, primary key and relationship. Another design goal of the API was to reuse as many of the classes as possible where they had similar structures and meaning. This allows code to make better use of polymorphism and reduce the complexity of the code making use of the API.

  Database Explorer API - Provides detailed descriptions of the classes and properties within the API and the class diagram.
Code Smith Samples Installation Tutorial - Provides a brief tutorial on how to get the samples for the API up and running.

Using the API

The API can be used by any language or code generator, the samples presented here currently use Code Smith as that's the tool I've been using for some years. Although I am looking at providing samples using the T4 and the DSL tools which are all part of the Visual Studio Extensions ecosystem. To honour the design goal of making this API easy to use, you only need to write one line of code to retrieve all the meta data for your database.

   1: Database database = new Database(new SQLServerProvider(),
   2:     SourceDatabase.ConnectionString ,null);

The above line of code requires the following three properties:

Parameter Description
provider This is the schema provider to use when accessing the database. In this case we are accessing the an SQL Server database.
connectionString The connection string used to connect to the database.
systemFields This field is used to supply a common set of fields which are to be ignored when calculating many-to-many (M:M) relationships. Examples of which would be IsActive, ModifiedDate, CreatedDate etc. These fields are not 'real' entity or business fields and from a modelling perspective are not material when thinking about relationships.

Learn by example

Once you have a reference to an instance of a populated database object you can begin to utilize the API. The following example taken from one of the provided samples (TableRelationships.cst) demonstrates how you can easily output all the table names and their relationships within your database. Trying to do this with just about any other schema API would require considerable work, however the following code is all that's required.

   1: // Define properties that are to be ignored when calculating M:M relationships.
   2: SystemFieldCollection systemFields = new SystemFieldCollection();
   3: systemFields.Add(new SystemField("ModifiedDate"));
   4: systemFields.Add(new SystemField("rowguid"));
   5:  
   6: Database database = new Database(new SQLServerProvider(), 
   7:     SourceDatabase.ConnectionString, systemFields);
   8:     
   9: foreach(Table t in database.Tables)
  10: {
  11:     Response.WriteLine("Table: " + t.FullName + 
  12:         (t.IsIntersectionTable ? " (Join table)" : ""));
  13:     foreach(Relationship r in t.Relationships)
  14:         Response.WriteLine(string.Format("\t{0} relationship with columns ({1})",
  15:             r.RelationshipType, r.LeftConstraint.Columns.ToString("$Name$", ", ")));
  16:         
  17:     Response.WriteLine();
  18: }

This sample is designed to work against the Adventure Works database which has a few fields that should be ignored when calculating M:M relationships. To run this against your database simply replace the two system field definitions with the fields that are common in your tables; note these fields are not required to exist in all tables, as is the case in the Adventure Works database. Running this sample produces the following result:

   1:  
   2: Table: dbo.AWBuildVersion
   3:  
   4: Table: dbo.DatabaseLog
   5:  
   6: Table: dbo.ErrorLog
   7:  
   8: Table: HumanResources.Department
   9:     OneToMany relationship with columns (DepartmentID)
  10:  
  11: Table: HumanResources.Employee
  12:     Recursive relationship with columns (ManagerID)
  13:     ManyToOne relationship with columns (ContactID)
  14:     ManyToMany relationship with columns (EmployeeID)
  15:     OneToMany relationship with columns (EmployeeID)
  16:     OneToMany relationship with columns (EmployeeID)
  17:     OneToMany relationship with columns (EmployeeID)
  18:     OneToMany relationship with columns (EmployeeID)
  19:  
  20: Table: HumanResources.EmployeeAddress (Join table)
  21:     ManyToOne relationship with columns (EmployeeID)
  22:     ManyToOne relationship with columns (AddressID)
  23:  
  24: Table: HumanResources.EmployeeDepartmentHistory
  25:     ManyToOne relationship with columns (DepartmentID)
  26:     ManyToOne relationship with columns (EmployeeID)
  27:     ManyToOne relationship with columns (ShiftID)
  28:  
  29: Table: HumanResources.EmployeePayHistory
  30:     ManyToOne relationship with columns (EmployeeID)
  31:  
  32: Table: HumanResources.JobCandidate
  33:     ManyToOne relationship with columns (EmployeeID)
  34:  
  35: Table: HumanResources.Shift
  36:     OneToMany relationship with columns (ShiftID)
  37:  
  38: Table: Person.Address
  39:     ManyToMany relationship with columns (AddressID)
  40:     ManyToOne relationship with columns (StateProvinceID)
  41:     OneToMany relationship with columns (AddressID)
  42:     OneToMany relationship with columns (AddressID)
  43:     OneToMany relationship with columns (AddressID)
  44:     OneToMany relationship with columns (AddressID)
  45:  
  46: Table: Person.AddressType
  47:     OneToMany relationship with columns (AddressTypeID)
  48:     OneToMany relationship with columns (AddressTypeID)
  49:  
  50: Table: Person.Contact
  51:     OneToMany relationship with columns (ContactID)
  52:     OneToMany relationship with columns (ContactID)
  53:     ManyToMany relationship with columns (ContactID)
  54:     OneToMany relationship with columns (ContactID)
  55:     OneToMany relationship with columns (ContactID)
  56:     OneToMany relationship with columns (ContactID)
  57:  
  58: Table: Person.ContactType
  59:     OneToMany relationship with columns (ContactTypeID)
  60:     OneToMany relationship with columns (ContactTypeID)
  61:  
  62: Table: Person.CountryRegion
  63:     OneToMany relationship with columns (CountryRegionCode)
  64:     ManyToMany relationship with columns (CountryRegionCode)
  65:  
  66: Table: Person.StateProvince
  67:     OneToMany relationship with columns (StateProvinceID)
  68:     ManyToOne relationship with columns (CountryRegionCode)
  69:     ManyToOne relationship with columns (TerritoryID)
  70:     OneToMany relationship with columns (StateProvinceID)
  71:  
  72: Table: Production.BillOfMaterials
  73:     ManyToOne relationship with columns (ProductAssemblyID)
  74:     ManyToOne relationship with columns (ComponentID)
  75:     ManyToOne relationship with columns (UnitMeasureCode)
  76:  
  77: Table: Production.Culture
  78:     OneToMany relationship with columns (CultureID)
  79:  
  80: Table: Production.Document
  81:     ManyToMany relationship with columns (DocumentID)
  82:  
  83: Table: Production.Illustration
  84:     ManyToMany relationship with columns (IllustrationID)
  85:  
  86: Table: Production.Location
  87:     OneToMany relationship with columns (LocationID)
  88:     OneToMany relationship with columns (LocationID)
  89:  
  90: Table: Production.Product
  91:     OneToMany relationship with columns (ProductID)
  92:     OneToMany relationship with columns (ProductID)
  93:     ManyToOne relationship with columns (ProductModelID)
  94:     ManyToOne relationship with columns (ProductSubcategoryID)
  95:     ManyToOne relationship with columns (SizeUnitMeasureCode)
  96:     ManyToOne relationship with columns (WeightUnitMeasureCode)
  97:     OneToMany relationship with columns (ProductID)
  98:     ManyToMany relationship with columns (ProductID)
  99:     OneToMany relationship with columns (ProductID)
 100:     OneToMany relationship with columns (ProductID)
 101:     OneToMany relationship with columns (ProductID)
 102:     OneToMany relationship with columns (ProductID)
 103:     OneToMany relationship with columns (ProductID)
 104:     OneToMany relationship with columns (ProductID)
 105:     OneToMany relationship with columns (ProductID)
 106:     OneToMany relationship with columns (ProductID)
 107:     OneToMany relationship with columns (ProductID)
 108:     OneToMany relationship with columns (ProductID)
 109:  
 110: Table: Production.ProductCategory
 111:     OneToMany relationship with columns (ProductCategoryID)
 112:  
 113: Table: Production.ProductCostHistory
 114:     ManyToOne relationship with columns (ProductID)
 115:  
 116: Table: Production.ProductDescription
 117:     OneToMany relationship with columns (ProductDescriptionID)
 118:  
 119: Table: Production.ProductDocument (Join table)
 120:     ManyToOne relationship with columns (DocumentID)
 121:     ManyToOne relationship with columns (ProductID)
 122:  
 123: Table: Production.ProductInventory
 124:     ManyToOne relationship with columns (LocationID)
 125:     ManyToOne relationship with columns (ProductID)
 126:  
 127: Table: Production.ProductListPriceHistory
 128:     ManyToOne relationship with columns (ProductID)
 129:  
 130: Table: Production.ProductModel
 131:     OneToMany relationship with columns (ProductModelID)
 132:     ManyToMany relationship with columns (ProductModelID)
 133:     OneToMany relationship with columns (ProductModelID)
 134:  
 135: Table: Production.ProductModelIllustration (Join table)
 136:     ManyToOne relationship with columns (IllustrationID)
 137:     ManyToOne relationship with columns (ProductModelID)
 138:  
 139: Table: Production.ProductModelProductDescriptionCulture
 140:     ManyToOne relationship with columns (CultureID)
 141:     ManyToOne relationship with columns (ProductDescriptionID)
 142:     ManyToOne relationship with columns (ProductModelID)
 143:  
 144: Table: Production.ProductPhoto
 145:     OneToMany relationship with columns (ProductPhotoID)
 146:  
 147: Table: Production.ProductProductPhoto
 148:     ManyToOne relationship with columns (ProductID)
 149:     ManyToOne relationship with columns (ProductPhotoID)
 150:  
 151: Table: Production.ProductReview
 152:     ManyToOne relationship with columns (ProductID)
 153:  
 154: Table: Production.ProductSubcategory
 155:     OneToMany relationship with columns (ProductSubcategoryID)
 156:     ManyToOne relationship with columns (ProductCategoryID)
 157:  
 158: Table: Production.ScrapReason
 159:     OneToMany relationship with columns (ScrapReasonID)
 160:  
 161: Table: Production.TransactionHistory
 162:     ManyToOne relationship with columns (ProductID)
 163:  
 164: Table: Production.TransactionHistoryArchive
 165:  
 166: Table: Production.UnitMeasure
 167:     OneToMany relationship with columns (UnitMeasureCode)
 168:     OneToMany relationship with columns (UnitMeasureCode)
 169:     OneToMany relationship with columns (UnitMeasureCode)
 170:     OneToMany relationship with columns (UnitMeasureCode)
 171:  
 172: Table: Production.WorkOrder
 173:     ManyToOne relationship with columns (ProductID)
 174:     ManyToOne relationship with columns (ScrapReasonID)
 175:     OneToMany relationship with columns (WorkOrderID)
 176:  
 177: Table: Production.WorkOrderRouting
 178:     ManyToOne relationship with columns (LocationID)
 179:     ManyToOne relationship with columns (WorkOrderID)
 180:  
 181: Table: Purchasing.ProductVendor
 182:     ManyToOne relationship with columns (ProductID)
 183:     ManyToOne relationship with columns (UnitMeasureCode)
 184:     ManyToOne relationship with columns (VendorID)
 185:  
 186: Table: Purchasing.PurchaseOrderDetail
 187:     ManyToOne relationship with columns (ProductID)
 188:     ManyToOne relationship with columns (PurchaseOrderID)
 189:  
 190: Table: Purchasing.PurchaseOrderHeader
 191:     OneToMany relationship with columns (PurchaseOrderID)
 192:     ManyToOne relationship with columns (EmployeeID)
 193:     ManyToOne relationship with columns (ShipMethodID)
 194:     ManyToOne relationship with columns (VendorID)
 195:  
 196: Table: Purchasing.ShipMethod
 197:     OneToMany relationship with columns (ShipMethodID)
 198:     OneToMany relationship with columns (ShipMethodID)
 199:  
 200: Table: Purchasing.Vendor
 201:     OneToMany relationship with columns (VendorID)
 202:     OneToMany relationship with columns (VendorID)
 203:     OneToMany relationship with columns (VendorID)
 204:     OneToMany relationship with columns (VendorID)
 205:  
 206: Table: Purchasing.VendorAddress
 207:     ManyToOne relationship with columns (AddressID)
 208:     ManyToOne relationship with columns (AddressTypeID)
 209:     ManyToOne relationship with columns (VendorID)
 210:  
 211: Table: Purchasing.VendorContact
 212:     ManyToOne relationship with columns (ContactID)
 213:     ManyToOne relationship with columns (ContactTypeID)
 214:     ManyToOne relationship with columns (VendorID)
 215:  
 216: Table: Sales.ContactCreditCard (Join table)
 217:     ManyToOne relationship with columns (ContactID)
 218:     ManyToOne relationship with columns (CreditCardID)
 219:  
 220: Table: Sales.CountryRegionCurrency (Join table)
 221:     ManyToOne relationship with columns (CountryRegionCode)
 222:     ManyToOne relationship with columns (CurrencyCode)
 223:  
 224: Table: Sales.CreditCard
 225:     ManyToMany relationship with columns (CreditCardID)
 226:     OneToMany relationship with columns (CreditCardID)
 227:  
 228: Table: Sales.Currency
 229:     ManyToMany relationship with columns (CurrencyCode)
 230:     OneToMany relationship with columns (CurrencyCode)
 231:     OneToMany relationship with columns (CurrencyCode)
 232:  
 233: Table: Sales.CurrencyRate
 234:     ManyToOne relationship with columns (FromCurrencyCode)
 235:     ManyToOne relationship with columns (ToCurrencyCode)
 236:     OneToMany relationship with columns (CurrencyRateID)
 237:  
 238: Table: Sales.Customer
 239:     ManyToOne relationship with columns (TerritoryID)
 240:     OneToMany relationship with columns (CustomerID)
 241:     OneToMany relationship with columns (CustomerID)
 242:  
 243: Table: Sales.CustomerAddress
 244:     ManyToOne relationship with columns (AddressID)
 245:     ManyToOne relationship with columns (AddressTypeID)
 246:     ManyToOne relationship with columns (CustomerID)
 247:  
 248: Table: Sales.Individual
 249:     ManyToOne relationship with columns (ContactID)
 250:     OneToOne relationship with columns (CustomerID)
 251:  
 252: Table: Sales.SalesOrderDetail
 253:     ManyToOne relationship with columns (SalesOrderID)
 254:     ManyToOne relationship with columns (ProductID, SpecialOfferID)
 255:  
 256: Table: Sales.SalesOrderHeader
 257:     OneToMany relationship with columns (SalesOrderID)
 258:     ManyToOne relationship with columns (BillToAddressID)
 259:     ManyToOne relationship with columns (ShipToAddressID)
 260:     ManyToOne relationship with columns (ContactID)
 261:     ManyToOne relationship with columns (ShipMethodID)
 262:     ManyToOne relationship with columns (CreditCardID)
 263:     ManyToOne relationship with columns (CurrencyRateID)
 264:     ManyToOne relationship with columns (CustomerID)
 265:     ManyToOne relationship with columns (SalesPersonID)
 266:     ManyToOne relationship with columns (TerritoryID)
 267:     ManyToMany relationship with columns (SalesOrderID)
 268:  
 269: Table: Sales.SalesOrderHeaderSalesReason (Join table)
 270:     ManyToOne relationship with columns (SalesOrderID)
 271:     ManyToOne relationship with columns (SalesReasonID)
 272:  
 273: Table: Sales.SalesPerson
 274:     OneToMany relationship with columns (SalesPersonID)
 275:     OneToOne relationship with columns (SalesPersonID)
 276:     ManyToOne relationship with columns (TerritoryID)
 277:     OneToMany relationship with columns (SalesPersonID)
 278:     OneToMany relationship with columns (SalesPersonID)
 279:     OneToMany relationship with columns (SalesPersonID)
 280:  
 281: Table: Sales.SalesPersonQuotaHistory
 282:     ManyToOne relationship with columns (SalesPersonID)
 283:  
 284: Table: Sales.SalesReason
 285:     ManyToMany relationship with columns (SalesReasonID)
 286:  
 287: Table: Sales.SalesTaxRate
 288:     ManyToOne relationship with columns (StateProvinceID)
 289:  
 290: Table: Sales.SalesTerritory
 291:     OneToMany relationship with columns (TerritoryID)
 292:     OneToMany relationship with columns (TerritoryID)
 293:     OneToMany relationship with columns (TerritoryID)
 294:     OneToMany relationship with columns (TerritoryID)
 295:     OneToMany relationship with columns (TerritoryID)
 296:  
 297: Table: Sales.SalesTerritoryHistory
 298:     ManyToOne relationship with columns (SalesPersonID)
 299:     ManyToOne relationship with columns (TerritoryID)
 300:  
 301: Table: Sales.ShoppingCartItem
 302:     ManyToOne relationship with columns (ProductID)
 303:  
 304: Table: Sales.SpecialOffer
 305:     OneToMany relationship with columns (SpecialOfferID)
 306:  
 307: Table: Sales.SpecialOfferProduct
 308:     OneToMany relationship with columns (ProductID, SpecialOfferID)
 309:     ManyToOne relationship with columns (ProductID)
 310:     ManyToOne relationship with columns (SpecialOfferID)
 311:  
 312: Table: Sales.Store
 313:     OneToOne relationship with columns (CustomerID)
 314:     ManyToOne relationship with columns (SalesPersonID)
 315:     OneToMany relationship with columns (CustomerID)
 316:  
 317: Table: Sales.StoreContact
 318:     ManyToOne relationship with columns (ContactID)
 319:     ManyToOne relationship with columns (ContactTypeID)
 320:     ManyToOne relationship with columns (CustomerID)
 321: 

If you've had a good look at the source code you may have noticed one line that looked at little peculiar; which was used to output the columns used as part of the relationship.

   1: Response.WriteLine(string.Format("\t{0} relationship with columns ({1})", 
   2:     r.RelationshipType, r.LeftConstraint.Columns.ToString("$Name$", ", ")));

A feature of each collection (all of which are strongly typed) is an extended ToString method which provides a simple expression parser and concatenation function to provide easy text output.

Parameter Description
expression A string expression that contains property names surrounded by $<property name>$ signs. The expression is evaluated for each item in the collection and concatinated by the value supplied by joiner. The parser has the ability to walk the tree but at present does not support indexes ([..]).
Example: "The name of the property is $Name$"
joiner The joiner parameter concatinates the supplied string to the evaluated expression for each item in the collection except the last one. This is most commonly used to easily create property lists or condition statements:

Example: ",\r\n\t" - outputs a comma, followed by a carriage return and a tab for each item in the collection except the last one.

The example uses this routine to provide a comma separated list of column names that are part of the left hand side of the relationship; which is the side of the relationship from the perspective of the current table.

The only other piece of code that might require explanation is the call IsIntersectionTable which determines if the tables existence is merely to support a M:M join (this is where those system fields are used) rather than a typical entity table which contains business data too.

 

You can download the API which is part of the dnp.Framework here (link at the bottom of the page).