Database Programming With C# - Thomsen_ Carsten.pdf - Database Programming With C# (301) - superktos86

Database Programming

CARSTEN THOMSEN

with C#

Database Programming with C#

any means, electronic or mechanical, including photocopying, recording, or by any information

storage or retrieval system, without the prior written permission of the copyright owner and

the publisher.

ISBN (pbk): 1-59059-010-4

Printed and bound in the United States of America 12345678910

Trademarked names may appear in this book. Rather than use a trademark symbol with every

occurrence of a trademarked name, we use the names only in an editorial fashion and to the

beneﬁt of the trademark owner, with no intention of infringement of the trademark.

Technical Reviewer: Douglas Milnes

Editorial Directors: Dan Appleman, Peter Blackburn, Gary Cornell, Jason Gilmore,

Karen Watterson, John Zukowski

Managing Editor: Grace Wong

Copy Editors: Nicole LeClerc, Ami Knox

Production Editor: Tory McLearn

Compositor: Impressions Book and Journal Services, Inc.

Indexer: Valerie Haynes Perry

Cover Designer: Tom Debolski

Marketing Manager: Stephanie Rodriguez

Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 175 Fifth

Avenue, New York, NY, 10010 and outside the United States by Springer-Verlag GmbH & Co. KG,

Tiergartenstr. 17, 69112 Heidelberg, Germany.

In the United States, phone 1-800-SPRINGER, email orders@springer-ny.com , or visit

http://www.springer-ny.com .

Outside the United States, fax +49 6221 345229, email orders@springer.de , or visit

http://www.springer.de .

For information on translations, please contact Apress directly at 2560 9th Street, Suite 219,

Berkeley, CA 94710.

Email info@apress.com or visit http://www.apress.com .

The information in this book is distributed on an “as is” basis, without warranty. Although every

precaution has been taken in the preparation of this work, neither the author nor Apress shall

have any liability to any person or entity with respect to any loss or damage caused or alleged to

be caused directly or indirectly by the information contained in this work.

The source code for this book is available to readers at http://www.apress.com in the

Downloads section.

You will need to answer questions pertaining to this book in order to successfully download

the code.

CHAPTER 6

Using Stored

Procedures, Views,

and Triggers

How to Use Stored Procedures,

Views, and Triggers

S ERVER - SIDE PROCESSING , which is when you let a server process your queries and

the like, is probably a concept you have heard of and it’s the very topic of this

chapter. Well, to some extent anyway. I discuss three speciﬁc ways of doing

server-side processing: stored procedures, triggers, and views. The good thing

about server-side processing is that you can use the power and resources of your

server for doing purely data-related processing and thus leave your client free to

do other stuff, and your network clearer of data that the client doesn’t want. It’s

not always appropriate to do so, but in many cases you can beneﬁt from it.

This chapter includes several hands-on exercises that will take you through

creating stored procedures, views, and triggers. See the Exercise items that

appear throughout the text.

Although this chapter primarily focuses on SQL Server 2000 features, some

of the functionality can certainly be reproduced in the other DBMSs I cover in

this book:

• SQL Server 7.0: All functionality shown in this chapter can be reproduced.

However, SQL Server 7.0 doesn’t support the INSTEAD OF triggers

described in “Using Triggers.”

• Microsoft Access: Microsoft Access doesn’t support stored procedures or

triggers. However, views can be reproduced as queries in Microsoft Access,

but you can’t do this from within the VS .NET IDE; you have to use other

means, like the Microsoft Access front-end. If you are unfamiliar with

367

Part Two: Database Programming

Microsoft Access, I can recommend you read the following book to get you

up to speed: From Access to SQL Server , by Russell Sinclair. Published by

Apress, September 2000. ISBN: 1893115-240.

• Oracle: Oracle supports all the server-side processing described in

this chapter.

• MySQL: For the examples in this book, I have been using MySQL version

3.23.45, which doesn’t support triggers, views, or stored procedures, mean-

ing there is no example code for MySQL in this chapter. However, at the

time of writing (March 2002), an alpha version (4.0) of MySQL is available

for download from http://www.mysql.com . The ﬁnal version 4.0 is supposed

to support stored procedures, views, and triggers. Even when these server-

side processing means are available in MySQL, it’s still not possible to

create any of these items from within the VS .NET IDE.

The code for this chapter has examples for all the listed DBMSs

where appropriate.

Optimization Issues

When I talk about optimizing performance of an application, there are a number

of things to consider, but let’s just make one thing clear before I go on: I am only

talking distributed applications and not stand-alone applications that sit nicely

on a possibly disconnected single PC. These stand-alone applications are also

called single tier or monolithic applications. 1 The applications I discuss here use

a network of some sort to access data and business services.

Okay, now that the basics are out of the way, I can focus on the obstacles that

can lead to decreasing performance and how you need to know these obstacles

well when you start the optimization process. You should keep such obstacles in

mind when you design your application. However, the various resources, such as

network bandwidth, processor power, available RAM, and so on, most often

change over time, and then you’ll have to reconsider if your application

needs changing.

Table 6-1 lists all the varying factors that can inﬂuence the performance of

your application, which could be a topic for an entire book. However, although

I only describe these factors brieﬂy, I want you to be aware of the resources

mentioned; they have great inﬂuence on what server-side processing resources

you should choose when you design your application. In general, it’s often

the client queries and not the server itself that create the biggest

performance problems.

1 .Stand-alone applications don’t have to be single tier, but they generally are.

368

Chapter 6: Using Stored Procedures, Views, and Triggers

Table 6-1. Performance Resources Optimization

RESOURCE NAME

DESCRIPTION

Network resources

When speaking of network resources, I am referring to

the actual bandwidth of the network. Consider your

network setup—whether you are on a LAN or you are

accessing resources over a WAN such as the Internet,

and so on. If you have a low bandwidth, it’s obvious

that you want to transfer as little data across the

network as possible. If on the other hand you have

plenty of bandwidth, you might want to transfer large

amounts of data across the network. However, best

practices prescribe that you only transfer the data

needed across your network, even when you have

wide bandwidth.

Local processing resources

If you have the raw power available on your local box,

it can be good to do most of the data processing there.

Mind you, it all depends on the available bandwidth

and the processing resources on the server.

Server processing resources

Server-side processing is desirable, if the server has

resources to do so. Another thing you should consider

is whether it has the resources to serve all your clients,

if you let the server do some of the data processing.

Data distribution

Although strictly speaking this isn’t a resource as such,

it’s deﬁnitely another issue you might need to

consider. If your data comes from various different

and even disparate data sources, it often doesn’t make

too much sense to have one server process data from

all the data sources, just to send the result set to the

client. In most cases, it makes sense to have all the

data delivered directly to the client.

Table 6-1 just provides a quick overview. Table 6-2 shows you some different

application scenarios.

369

Database Programming With C# - Thomsen_ Carsten.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: