dba221 tsql versus.net database programming: dispelling the myths for dbas fernando g. guerrero s...
TRANSCRIPT
![Page 1: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/1.jpg)
DBA221
TSQL Versus .NET Database Programming: Dispelling The Myths for DBAs
Fernando G. Guerrero
Solid Quality [email protected]
![Page 2: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/2.jpg)
TSQL vs. .NET…is this some kind of war?
Is .NET the “silver bullet” for all our computing needs – what is it?Does “managed” code really handle everything?Is .NET limited to SQL Server access only?Is TSQL becoming obsolete?How does .NET impact existing applications?
Portions of this session is based on a TechEd 2003 USA presentation from Don Awalt and Brian Lawton, from RDA Corporation
![Page 3: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/3.jpg)
Quick info about Fernando(2 milliseconds)
MCSD, MCSE+Internet (W2K), MCDBA, MCT, SQL Server MVP
CEO and principal mentor at Solid Quality Learning
Writing for SQL Sever Magazine and SQL Server Professional
Co-author of Microsoft SQL Server 2000 Programming by Example (ISBN : 0789724499)
Author of the .NET Operations Guide and the .NET Deployment Guide for Microsoft TechNet
![Page 4: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/4.jpg)
Solid Quality Learning (3 ms) An association of SQL Server experts from around the world
Principal Mentors:Itzik Ben-Gan
Kalen Delaney
Fernando G. Guerrero
Michael Hotek
Brian Moran
Ron Talmage
Kimberly L. Tripp
Helping you get the best out of your SQL Server:
Training
Consulting
Mentoring
Stay tuned to
http://www.SolidQualityLearning.com
![Page 5: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/5.jpg)
Objective
To present important concepts about .NET in order for DBAs to remain the stewards of their organization’s databases
Addressing “common myths” about .NET of concern to DBAs
Recommended Sessions:DAT220: “SQL Server Yukon: .NET and the DBA”
DAT234: “SQL Server Yukon: .NET Programming Features”
This is not a talk for .NET experts or application developers!
![Page 6: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/6.jpg)
Agenda
.NET and the .NET Framework
.NET Interoperability with SQL Server
Deployment of applications using the .NET Framework
Data Access versus TSQL
Using Visual Studio.NET to troubleshoot
![Page 7: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/7.jpg)
Agenda
.NET and the .NET Framework
.NET Interoperability with SQL Server
Deployment of applications using the .NET Framework
Data Access versus TSQL
Using Visual Studio.NET to troubleshoot
![Page 8: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/8.jpg)
Terminology
if (application == built with Web Services)Application = a .NET Application;
else if (application == utilizes the .NET Framework)Application = a Windows application built
using the .NET Framework;
elseApplication = a Windows application;
![Page 9: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/9.jpg)
While most people say a “.NET App” or “.NET”,
what they really mean is
“An application built using the .NET Framework”
or
“the .NET Framework”!
![Page 10: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/10.jpg)
Benefits Of .NET Framework
A common runtime engine used by all .NET-aware languages
Optimize and enrich
Resource and memory management
Rich class libraryShelters raw API and COM plumbing
Standard data types
Type-safety
Consistent error handling
Full interoperability with existing (COM) code
![Page 11: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/11.jpg)
Benefits Of .NET Framework
Truly simplified deploymentComponent version management (even the Framework itself!)
Self-describing components
No more binary registrations (COM)
More secure execution modelPlatform independent (Windows 98)
Goes beyond who is running the code
Code -> Security Policy -> Permissions
Verifiable code (itself a permission)
![Page 12: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/12.jpg)
What Is An XML Web Service?
Evolution of applications and Web sitesAccess – beyond just people
Strategy for reuse
Publish and/or consume
Secure interactions across trust boundaries
New integration methodologyXML
Open standards
Interoperability efforts
Can expose existing code as Web service
![Page 13: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/13.jpg)
Why XML Web Services?
Broad industry supportMajor standards authored by Microsoft and IBM
Already 60+ implementations
“Everyone should
build on the XML/SOAP foundation”–Meta Group
Ubiquitous Communications: Ubiquitous Communications: Internet Internet
Universal Data Format:Universal Data Format: XML XML
Service Interactions:Service Interactions: SOAP SOAP
Publish, Find, Use Services: UDDIPublish, Find, Use Services: UDDI
XML Web Services FoundationXML Web Services Foundation
Service Descriptions: Service Descriptions: WSDL WSDL
![Page 14: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/14.jpg)
The .NET Framework Is
A runtime – Common Language Runtime (CLR)
The CLR locates, loads and manages data types – Common Type System (CTS)
Standards have been defined to ensure common types – Common Language Specification (CLS)
The base class library provides a simplified programming model
![Page 15: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/15.jpg)
CLRMulti-language Support
Object system is built-in, not bolted onLanguage of choice
Cross-language inheritance and exceptions
Supports more than 20 languages
Consistent tools across all languagesIDE
Debugger
![Page 16: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/16.jpg)
.NET Framework
Common Language RuntimeCommon Language Runtime
Memory MgmtMemory Mgmt Type SystemType System LifetimeLifetime
System Base ClassesSystem Base Classes
IOIO NetNet SecuritySecurity ServiceProcessServiceProcess
ADO.NETADO.NET XMLXML SQLSQL ThreadingThreading
System.WebSystem.Web
ASP.NET Application ServicesASP.NET Application Services
System.WindowsFormsSystem.WindowsForms
Windows Application ServicesWindows Application Services
ControlsControls DrawingDrawingWeb ServicesWeb Services Web FormsWeb Forms
![Page 17: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/17.jpg)
What Is An Assembly?
A logical container for a set of modules/files
Contents described by a manifest
A managed unit of deploymentMetadata references a specific version of an assembly
Defines security boundaries
![Page 18: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/18.jpg)
What Is MSIL?
Microsoft Intermediate Language
Instructions for an abstract computer
All .NET languages compile to MSIL
Instructions are verifiable or notCan compile "unsafe" languages like C into MSIL
Toolsildasm (.NET Framework SDK)
reflector (http://www.aisto.com/roeder)
![Page 19: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/19.jpg)
CLRCompilation and Execution
CompilationCompilation
Before Before installation or installation or the first time the first time each method each method
is calledis calledExecutionExecution
JIT JIT CompilerCompiler
NativeNativeCodeCode
MSILMSILCodeCode
MetadataMetadataSource Source CodeCode
Language Language CompilerCompiler
![Page 20: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/20.jpg)
Agenda
.NET and the .NET Framework
.NET Interoperability with SQL Server
Deployment of applications using the .NET Framework
Data Access versus TSQL
Using Visual Studio.NET to troubleshoot
![Page 21: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/21.jpg)
.NET Interoperability
.NET Framework (hence code managed by the CLR) is not used today by SQL Server!
Issue: All native SQL Sever 2000 interfaces are COM based; How do we use them from the .NET Framework?
ExamplesData Transformation Services (DTS)
Data Manipulation Objects (SQLDMO)
![Page 22: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/22.jpg)
Calling COM Objects…from managed code
COM: Unmanaged components
Need to expose COM objects as .NET equivalents
COM objects need reference counts
Ideally, it would be transparent…
![Page 23: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/23.jpg)
Runtime Callable Wrapper (RCW)
Acts as a proxy for the COM object
1 per object (ref count)
Generated with a tool (tlbimp.exe)
Handles some COM plumbing interfaces itself (IClassFactory, IDispatch, IErrorInfo)
![Page 24: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/24.jpg)
Calling Managed Code…from COM objects
.NET Assemblies can “fool” the COM runtime
Registered in the system registry to be located
Generate a COM type library
Deploy the assembly where COM can find it (COM client folder or Global Assembly Cache)
Typically this is less of an issue…
![Page 25: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/25.jpg)
.NET Interoperability
Runtime CallableRuntime CallableWrapperWrapper
ObjectObject
IFooIFoo
IUnknownIUnknown Common Language Common Language RuntimeRuntime
COM Server
ClientClient
ReferenceCounted
![Page 26: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/26.jpg)
Agenda
.NET and the .NET Framework
.NET Interoperability with SQL Server
Deployment of applications using the .NET Framework
Data Access versus TSQL
Using Visual Studio.NET to troubleshoot
![Page 27: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/27.jpg)
How To Improve Deployment
Eliminate “DLL Hell”
Installation – registry registration impacts
Moving applications, uninstalling applications, reinstalling applications
Security – track more than just “who’s running the code”
![Page 28: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/28.jpg)
Deployment Of Assemblies
.NET Framework is requiredAvailable for free from Microsoft
Included with Windows Server 2003
Support for multiple versionsFrameworks – .NET 1.0 and 1.1
Side-by-side component execution
No more DLL Hell!
Install – file copy*
Uninstall – file delete*
![Page 29: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/29.jpg)
No Registration Required
Code is self-describingTypes scoped to referenced assemblies
Version-aware
Privately-deployed assemblies
Shared assemblies (GAC)
Strongly-named assemblies – “safe”Filename, version, version, culture, public key token
No weakly-named assemblies in the GAC
![Page 30: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/30.jpg)
Deploying…Deploying…
demodemo
![Page 31: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/31.jpg)
Agenda
.NET and the .NET Framework
.NET Interoperability with SQL Server
Deployment of applications using the .NET Framework
Data Access versus TSQL
Using Visual Studio.NET to troubleshoot
![Page 32: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/32.jpg)
Data AccessADO.NET
Integrated into the .NET FrameworkType-safe standard data types
XML integration
XML is the native data format!!
Designed for disconnected, n-Tier application architectures
Supercedes OLEDB, ADO, and ODBC
![Page 33: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/33.jpg)
ADO.NET…an evolution from ADO
Strong demand for faster, more scalable applications
XML proved to be lighter weight and provides greater flexibility over HTTP
ADO 2.5 introduced propriety support for XML – Advanced Tablegram (ADTG) format
ADO does not handle disconnected recordsets and XML integration well
Better XML support in MSXML 3.0, but not optimal
![Page 34: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/34.jpg)
ADO.NET…an evolution from ADO
ADO focuses on “relational” data
XML focuses on “non-relational” dataWell suited for hierarchies
ADO.NET offers “next generation” ADO.NET offers “next generation”
data access by leveraging the data access by leveraging the best of both worldsbest of both worlds
![Page 35: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/35.jpg)
ADO.NETConcepts: Data classes
Containers for dataKnow nothing about the database
Key ObjectsDataTable – Disconnected/In-memory cache
NOT related to a “database” table!
DataSet – Collection of DataTables, their relations, and constraints
![Page 36: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/36.jpg)
ADO.NETConcepts: Database classes
Interact with data sourcesUtilize managed providers
Key ObjectsDataAdapter – Connects a DataSet to a database
DataReader – Provides the DataAdapter a “bindable” data stream (a.k.a. a forward-only, read-only, client-side cursor)
![Page 37: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/37.jpg)
ADO.NETArchitecture
Business TierBusiness Tier Data TierData Tier
Presentation TierPresentation TierWindows Forms
Web Forms
Business to Business
Data Object (Class)
DataSet
DataSet
DataSet
InternetInternetIntranetIntranet
Data AdapterData Adapter
Data AdapterData Adapter
(BizTalk, for example)
XML
MyApp.Exe
IE
![Page 38: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/38.jpg)
ADO.NETManaged Providers
Provide interaction with data sourcesManaged equivalent of an OLEDB layer
Targeted and optimized for data source
Standard Providers (in-the-box)System.Data.OleDb
legacy support to any data source
System.Data.SqlClient – SQL ServerDesigned to access SQL Server 7.0 or higher
For older versions, use System.Data.OleDb
Oracle, ODBC providers also available
![Page 39: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/39.jpg)
ADO.NETSQL Server Interaction
Virtually transparent to the DBAImpacts applications only!
Requires MDAC 2.6 or laterWindows 2000: MDAC 2.5!
MDAC 2.7 SP1 is recommended
When updating MDAC, also update server catalogs
instcat.sql
![Page 40: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/40.jpg)
SQL Server InteractionConnected versus Disconnected
Disconnected data accessUser interaction is required
If “memory” data is required
Distributed data
Only supports optimistic locking
Typical “Connected” data access is not supported!
No server-side cursors
If required, use a COM-based ADO
![Page 41: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/41.jpg)
Using ADO.NETUsing ADO.NET
demodemo
![Page 42: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/42.jpg)
TSQL Versus .NET
Location of application logicClassic issue remains
No one right answer!
Consolidate in the database (TSQL)Typical 2-tier/cient-server environment
Potential to reduce maintenance costs
Distribute in an application layer (.NET)Typical n-tier architecture
Increases flexibility and scalability
![Page 43: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/43.jpg)
TSQL Versus .NET
Row versus Set-based OperationsClassic issue remains
Leverage their strengthsTSQL – set-based operations on large datasets
.NET – complex procedural logic and CPU intensive operations
Be wary of pulling too much data!DataSets are NOT an in-memoryrelational database
![Page 44: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/44.jpg)
Transactions
Transaction Model.NET introduces no changes!
All existing means of handling transactions, be it through middle-tier code or directly within TSQL remain
![Page 45: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/45.jpg)
Agenda
.NET and the .NET Framework
.NET Interoperability with SQL Server
Deployment of applications using the .NET Framework
Data Access versus TSQL
Using Visual Studio.NET to troubleshoot
![Page 46: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/46.jpg)
Development ToolsVisual Studio
Visual Studio versionsVisual Studio 2002 - .NET Framework 1.0
Visual Studio 2003 - .NET Framework 1.0/1.1
Provides deep database integrationDatabase projects
Version control
Integrated debugging
Stepping stone for Yukon!
![Page 47: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/47.jpg)
Visual Studio…an introduction
Database ProjectUsed to organize “database” objects
Stored procedures, TSQL scripts, functions, etc…
Data ConnectionsIdentifies the target server
Enables access to database objects
Version ControlFull SourceSafe integration!
![Page 48: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/48.jpg)
![Page 49: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/49.jpg)
Visual Studio…an introduction
DebuggingCan be used independently or in conjunction with managed code
To “step” through TSQL, you must set a breakpoint within the code
Limited to code inspection only
Triggers must be fired to be debugged
![Page 50: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/50.jpg)
Debugging AndDebugging AndProfiling ADO.NETProfiling ADO.NET
demodemo
![Page 51: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/51.jpg)
Summary
Is .NET is the “silver bullet” for all our computing needs – what is it?
Does “managed” code really handle everything?
Is .NET limited to SQL Server only?
Is TSQL becoming obsolete?
How does .NET impact existing applications?
![Page 52: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/52.jpg)
.NET Framework Downloads.Net Frameworks 1.0 and 1.1
ODBC Managed Provider
Oracle Managed Providerhttp://msdn.microsoft.com/library/default.asp?url=/downloads/list/netdevframework.asp
Appendix…Appendix…
![Page 53: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/53.jpg)
Appendix
Debugging Stored Procedureshttp://support.microsoft.com/default.aspx?scid=kb;en-us;316549
Data Access Technologies DownloadsMDAC 2.6
MDAC 2.7 SP1http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860
![Page 54: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/54.jpg)
Appendix
Microsoft Patterns and PracticesBest Practices
Reference Architectureshttp://msdn.microsoft.com/practices/
Data Access Application BlockRe-usable subsystem designhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
![Page 55: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/55.jpg)
Summary
SQL Profiler as Management Tool
Looking under the hood of database tools
Defining effective Traces
Scripting SQL Server Traces
Do you love Profiler a bit more? ♥
![Page 56: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/56.jpg)
Ask The ExpertsGet Your Questions Answered
I’ll be at the Ask the Experts area:2 July 2003: 12:00-14:00
3 July 2003: 11:00-13:00
4 July 2003: 12:00-14:00
You’ll see me around some more times
![Page 57: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/57.jpg)
Community Resources
Community Resourceshttp://www.microsoft.com/communities/default.mspx
Most Valuable Professional (MVP)http://www.mvp.support.microsoft.com/
NewsgroupsConverse online with Microsoft Newsgroups, including Worldwidehttp://www.microsoft.com/communities/newsgroups/default.mspx
User GroupsMeet and learn with your peershttp://www.microsoft.com/communities/usergroups/default.mspx
![Page 58: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/58.jpg)
Suggested Reading And Resources
The tools you need to put technology to work!The tools you need to put technology to work!
TITLETITLE AvailableAvailable
Microsoft® SQL Server™ 2000 Microsoft® SQL Server™ 2000 High Availability: 0-7356-1920-4High Availability: 0-7356-1920-4
7/9/037/9/03
TodayTodayMicrosoft® SQL Server™ 2000 Microsoft® SQL Server™ 2000 Administrator's Companion:0-Administrator's Companion:0-7356-1051-77356-1051-7
Microsoft Press books are 20% off at the TechEd Bookstore
Also buy any TWO Microsoft Press books and get a FREE T-Shirt
![Page 59: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/59.jpg)
Thank you! Questions?
Download the source code of this session from:http://www.solidqualitylearning.com/conferences
You can contact me at:[email protected]
![Page 60: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/60.jpg)
evaluationsevaluations
![Page 61: DBA221 TSQL Versus.NET Database Programming: Dispelling The Myths for DBAs Fernando G. Guerrero S olid Q uality L earning fernando@solidqualitylearning.com](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1c5503460f94c330db/html5/thumbnails/61.jpg)
© 2003 Microsoft Corporation. All rights reserved.© 2003 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.