1 / 27

Writing code to write your Data Services Layer

Writing code to write your Data Services Layer. Andrew Novick December 6, 2001. Agenda. The Task: Creating a Data Services Layer Stored Procedure Based DSL Writing the code that writes the code Getting Metadata from SQL Server. Task: Create a Data Services Layer.

heller
Download Presentation

Writing code to write your Data Services Layer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Writing code to write your Data Services Layer Andrew Novick December 6, 2001

  2. Agenda • The Task: Creating a Data Services Layer • Stored Procedure Based DSL • Writing the code that writes the code • Getting Metadata from SQL Server Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  3. Task: Create a Data Services Layer • Write a Data Services Layer for a medium to large database that is rapidly evolving • Use the fastest possible ADO techniques Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  4. The N-Tier Model User Interface Layer Business Layer Data Services Layer Database Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  5. Data Services Layer • Responsible for all I/O with database • Holds all the SQL • Uses Stored Procedures for routine: • Insert • Update • Delete • Select by Key Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  6. Why Stored Procedures for I/O • Reduced round-trips to the database. • About 1/4th the trips used by ADO Recordsets • Reduced client CPU and Memory Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  7. Resource • ADO Examples and Best Practices • William R. Vaughn • Apress ISBN 1-893115-16-X Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  8. Alternative ways of getting there • Buy a product • Lockwood Tech – ProcBlaster • OM Tool • Carl Franklin’s Code • Build • VBPJ Article Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  9. URL: http://www.devx.com/premier/mgznarch/vbpj/2001/06jun01/dd0106/dd0601.asp Try not to give your users direct access to your database tables—they can poke around in your database and cause all kinds of trouble. Microsoft SQL Server provides one of the best methods for isolating users from your tables: stored procedures. However, unless you own a middle-tier database-modeling tool, you've probably been building and maintaining your stored procedures by hand. I'll show you how to use the metadata stored in SQL Server to create and maintain a set of standardized stored procedures for any SQL Server database (see Figure 1). VBPJ June 2001 Automate Writing Stored Procedures Use SQL Server's Distributed Management Objects to generate standardized stored procedures. by David Rabb In this column's sample project, I'll show you how to create four procedures for each user table in the Pubs database: Select, Insert, Update, and Delete (download the code project). Select contains one parameter for each member of the table's primary key, and a select statement. It returns all columns for a single row in the table. URL: http://www.devx.com/premier/mgznarch/vbpj/2001/06jun01/dd0106/dd0601.asp Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  10. Why Build • Total control • Interfaces the way you want them • Naming Conventions the way you want them. • Error handling the way you want it. • Products require extensive customization and “script” writing. • Might as well write it in VB Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  11. Using Stored Procedures for IUSD • Insert • Update • Select • Delete Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  12. Sample Stored Procedure CREATE PROCEDURE [dp_titles_ins] @title_id varchar(6), @title varchar(80), @Booktype char(12) = NULL OUTPUT , @pub_id char(4) = NULL , @price money = NULL , @advance money = NULL , @royalty int = NULL , @ytd_sales int = NULL , @Booknotes varchar(200) = NULL , @pubdate datetime = NULL OUTPUT AS Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  13. Sample Stored Procedure 2 IF @Booktype Is Null SET @Booktype = ('UNDECIDED') IF @pubdate Is Null SET @pubdate = (getdate()) INSERT INTO [titles] WITH (ROWLOCK) ( [title_id], [title], [Booktype], [pub_id], [price], [advance], [royalty], [ytd_sales], [Booknotes], [pubdate]) Values (@title_id, @title, @Booktype, @pub_id, @price, @advance, @royalty, @ytd_sales, @Booknotes, @pubdate) SELECT @Booktype = [Booktype], @pubdate = [pubdate] FROM [titles] WHERE [title_id] = @title_id Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  14. What do the VB Classes Look Like? • To long to print here…. • Multiple interfaces to the data. Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  15. Some Caveats • Naming convention for SQL Objects required • 30 characters name limit • No spaces in names • No use of VB Reserved words • Special Filed Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  16. What sort of Interface would you like? • Properties • Compact Load, Add, Update • Irec • Browse Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  17. Property based Interface • Property Get and Let pairs for each variable • oTable.Field1 = ‘a new value’ • oTable.Field1 = “another value’ • ….. • oTable.Update Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  18. Compact Functions • Load, Add, Update send all properties. • Best when method calls cross machine or context boundaries. • oTable.Add (my field1val, myField2Val myField3Val…..) Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  19. IRec Interface • Generic, works with all tables. Dim oMyTable as cMyTable Dim oRec as IRec Set oRec = oMyTable nFieldIndex = oRec.FieldIdx(“Name”) myVariable = oRec.FieldValue(nFieldIndex) Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  20. Browse • Writes the SQL to do standard browse access to tables. • Keeps SQL out of the UI and Business layers. Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  21. Take a look at Views • Similar to tables • Usually not updateable Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  22. Classes Stored Procedures • 3 Types of stored procedures • Does not return a record set • Returns a record set • Returns an XML stream • Never updateable Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  23. Does not return a record set oCMD.Execute RecordsAffected:=m_nRAd, Options:=adExecuteNoRecords Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  24. Returns XML With oCMD .Dialect = "{5D531CB2-E6ED-11D2-B252-00C04F681B71}" Set .CommandStream = oStream .Properties("Output Stream") = oResultStream .Execute , , adExecuteStream m_sXML = oResultStream.ReadText() End With Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  25. 5 Minutes • This is the true power of having a custom application • Would work even better as a VB Add-In Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  26. Alternatives for getting Schema Information • SQL-DMO • ADOX • INFORMATION-SCHEMAs Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

  27. Real World Results • 8000 Lines of Code in AppGenerator • Writes 150,000 Lines of DSL Code • 80 Hours vs. about 400 Hours • Change in effort level takes away an important disincentive for using stored procedures. Andrew Novick anovick@world.std.com December 6, 2001 VB Pro User Group

More Related