writing code to write your data services layer andrew novick december 6, 2001
TRANSCRIPT
![Page 1: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/1.jpg)
Writing code to write your Data Services Layer
Andrew Novick
December 6, 2001
![Page 2: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/2.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Agenda
The Task: Creating a Data Services Layer Stored Procedure Based DSL Writing the code that writes the code Getting Metadata from SQL Server
![Page 3: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/3.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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
![Page 4: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/4.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
The N-Tier Model
UserInterface
Layer
BusinessLayer
DataServices
Layer
Database
![Page 5: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/5.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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
![Page 6: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/6.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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
![Page 7: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/7.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Resource
ADO Examples and Best Practices William R. Vaughn
Apress ISBN 1-893115-16-X
![Page 8: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/8.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Alternative ways of getting there
Buy a product Lockwood Tech – ProcBlaster OM Tool Carl Franklin’s Code
Build VBPJ Article
![Page 9: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/9.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
VBPJ June 2001Automate Writing Stored Procedures
Use SQL Server's Distributed Management Objects to generate standardized stored procedures. by David Rabb
URL: http://www.devx.com/premier/mgznarch/vbpj/2001/06jun01/dd0106/dd0601.aspTry 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).
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
![Page 10: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/10.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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
![Page 11: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/11.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Using Stored Procedures for IUSD
Insert Update Select Delete
![Page 12: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/12.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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
![Page 13: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/13.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Sample Stored Procedure 2IF @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
![Page 14: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/14.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
What do the VB Classes Look Like?
To long to print here….
Multiple interfaces to the data.
![Page 15: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/15.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Some Caveats
Naming convention for SQL Objects required 30 characters name limit No spaces in names No use of VB Reserved words Special Filed
![Page 16: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/16.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
What sort of Interface would you like?
Properties Compact Load, Add, Update Irec Browse
![Page 17: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/17.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Property based Interface
Property Get and Let pairs for each variable
oTable.Field1 = ‘a new value’ oTable.Field1 = “another value’ ….. oTable.Update
![Page 18: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/18.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Compact Functions
Load, Add, Update send all properties. Best when method calls cross machine or
context boundaries.
oTable.Add (my field1val, myField2Val myField3Val…..)
![Page 19: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/19.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
IRec Interface
Generic, works with all tables.
Dim oMyTable as cMyTableDim oRec as IRecSet oRec = oMyTablenFieldIndex = oRec.FieldIdx(“Name”)myVariable = oRec.FieldValue(nFieldIndex)
![Page 20: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/20.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Browse
Writes the SQL to do standard browse access to tables.
Keeps SQL out of the UI and Business layers.
![Page 21: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/21.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Take a look at Views
Similar to tables Usually not updateable
![Page 22: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/22.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Classes Stored Procedures
3 Types of stored procedures Does not return a record set Returns a record set Returns an XML stream
Never updateable
![Page 23: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/23.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Does not return a record set
oCMD.Execute RecordsAffected:=m_nRAd, Options:=adExecuteNoRecords
![Page 24: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/24.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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
![Page 25: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/25.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
5 Minutes
This is the true power of having a custom application
Would work even better as a VB Add-In
![Page 26: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/26.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
Alternatives for getting Schema Information
SQL-DMO ADOX INFORMATION-SCHEMAs
![Page 27: Writing code to write your Data Services Layer Andrew Novick December 6, 2001](https://reader035.vdocuments.us/reader035/viewer/2022062511/5519b5d755034660578b47ad/html5/thumbnails/27.jpg)
Andrew Novick [email protected] December 6, 2001 VB Pro User Group
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.