Writing code to write your Data Services Layer

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

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

4
The N-Tier Model
User Interface Layer
Business Layer
Data Services Layer
Database
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

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

7
Resource

• ADO Examples and Best Practices
• William R. Vaughn
• Apress ISBN 1-893115-16-X

8
Alternative ways of getting there

• Buy a product
• Lockwood Tech ProcBlaster
• OM Tool
• Carl Franklins Code
• Build
• VBPJ Article

9
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/200
1/06jun01/dd0106/dd0601.asp
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

11
Using Stored Procedures for IUSD

• Insert
• Update
• Select
• Delete

12
Sample Stored Procedure
CREATE PROCEDURE dp_titles_ins _at_title_id varch
ar(6), _at_title varchar(80), _at_Booktype char(12)
NULL OUTPUT , _at_pub_id char(4) NULL
, _at_price money NULL , _at_advance money NULL
, _at_royalty int NULL , _at_ytd_sales int NULL
, _at_Booknotes varchar(200) NULL
, _at_pubdate datetime NULL OUTPUT AS
13
Sample Stored Procedure 2
IF _at_Booktype Is Null SET _at_Booktype
('UNDECIDED') IF _at_pubdate Is Null SET _at_pubdate
(getdate()) INSERT INTO titles WITH (ROWLOCK)
( title_id, title, Booktype, pub_id,
price, advance, royalty, ytd_sales,
Booknotes, pubdate) Values (_at_title_id,
_at_title, _at_Booktype, _at_pub_id, _at_price,
_at_advance, _at_royalty, _at_ytd_sales, _at_Booknotes,
_at_pubdate) SELECT _at_Booktype Booktype,
_at_pubdate pubdate FROM titles WHERE
title_id _at_title_id
14
What do the VB Classes Look Like?

• To long to print here.
• Multiple interfaces to the data.

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

16
What sort of Interface would you like?

• Properties
• Compact Load, Add, Update
• Irec
• Browse

17
Property based Interface

• Property Get and Let pairs for each variable
• oTable.Field1 a new value
• oTable.Field1 another value
• ..
• oTable.Update

18
Compact Functions

• Load, Add, Update send all properties.
• Best when method calls cross machine or context
  boundaries.
• oTable.Add (my field1val, myField2Val
  myField3Val..)

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)

20
Browse

• Writes the SQL to do standard browse access to
  tables.
• Keeps SQL out of the UI and Business layers.

21
Take a look at Views

• Similar to tables
• Usually not updateable

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

23
Does not return a record set
oCMD.Execute RecordsAffectedm_nRAd,
OptionsadExecuteNoRecords
24
Returns XML
With oCMD .Dialect "5D531CB2-E6ED-11D2-B252-0
0C04F681B71" Set .CommandStream
oStream .Properties("Output Stream")
oResultStream .Execute , , adExecuteStream
m_sXML oResultStream.ReadText() End With
25
5 Minutes

• This is the true power of having a custom
  application
• Would work even better as a VB Add-In

26
Alternatives for getting Schema Information

• SQL-DMO
• ADOX
• INFORMATION-SCHEMAs

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.