Transcript My talk at a Fawcette conference XXXXX VSLIVE TEMPLATE XXXX

ADO.NET Tips and Tricks
How to get the most out of your
data access…
Who I Am
 Shawn Wildermuth ([email protected])
 Independent Consultant (http://adoguy.com)
 C# MVP
 MCSD.NET
 INETA Speaker
 Book Author
– “Pragmatic ADO.NET”
 Editor of http://ONDotnet.com
 This Presentation can be found at:
– http://adoguy.com/presentations
Overview
 Can you do ADO.NET Wrong?
 Unmanaged Resources
 Connections
 DataSets
 Typed DataSets
 CommandBuilders
 DataAdapters
 DataSets & ASP.NET
 Distributed DataSets
 Transactions
 Vendor Neutral Code
Are You Doing Anything Wrong?
 Can you write bad ADO.NET Code?
 Yes…but:
– It is usually intuitive about what is right
– Disconnected nature makes it harder to break
 Areas of confusion about ADO.NET
– Easier to scale, but not fool-proof
– Disconnected does not mean scalable
– Concurrency is harder than older systems
 (e.g. ADO)
– “Why Can’t I Lock Rows”
Unmanaged Resources
 Most ADO.NET objects are IDisposable
– Make sure and Dispose()
– Inheritance from Component means Dispose()
– Not disposing objects will cause leaky code
Dim conn As New SqlConnection("...")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "..."
conn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
Console.WriteLine(rdr.GetString(0))
End While
conn.Close()
' potential leaky code even though you closed the connection!
Unmanaged Resources (3)
 Use Try…Finally
– No “using” in VB.NET, so must do it all in finally
– Ensures always disposed, even if exception
Dim conn as new SqlConnection("...")
Dim cmd as SqlCommand = conn.CreateConnection
Try
cmd.CommandText = "..."
conn.Open()
Dim rdr as SqlDataReader = cmd.ExecuteReader()
While rdr.Read() = True
Console.WriteLine(rdr.GetString(0))
End While
Finally ' Clean Up
conn.Close()
rdr.Dispose()
cmd.Dispose()
conn.Dispose()
End Try
Connections
 Connections are precious
– In DataReaders, let it close it when it is done:
Try
conn.Open()
Dim rdr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)
While rdr.Read()
' Closes Here on last read
Console.WriteLine(rdr.GetString(0))
End While
Finally
' Make sure we didn’t throw an exception and leak a connection
conn.Close()
End Try
Connections (2)
 Connections are still precious
– With DataSets, never open them:
Dim ds As New DataSet
Dim conn As New SqlConnection("...")
Dim adapter As New SqlDataAdapter("...")
' The adapter will always preserve the state
' of the connection during the fill
' No Need to Open Or Close the connection because the adapter will
' close it as soon as possible since it is already closed
adapter.Fill(ds)
' If it is open, it will leave it open
conn.Open()
adapter.Fill(ds)
conn.Close()
Connections (3)
 Don’t Hardcode Connection Strings
– VS.NET Designer Does it Wrong
– Store Them in .Config Files
<configuration>
<appSettings>
<add key="SqlConnString"
value="Server=localhost;Database=Northwind;..." />
</appSettings>
</configuration>
<configuration>
<appSettings>
<!-- Better yet, encrypt them in the .config file -->
<add key="SqlConnString"
value="E234998F98A98F..." />
</appSettings>
</configuration>
Connections (4)
 Use Connection Factories
– Isolates Connection String Storage
– Increases Security by Controlling Access
Public Class ConnectionStrings
Public Shared Function GetConnection() _
As OleDbConnection
return new OleDbConnection( _
ConfigurationSettings.AppSettings("ConnString"))
End Function
End Class
Connections (5)
 Secure Connection Strings with Encryption
– You could write your own Encryption Library
– Better would be if MS supplied one
 In Windows 2000 and later, use DPAPI
 DPAPI Eliminates the Key Management Problem
 Encrypts on a Per Machine or Per User Basis
 Caveat: May Require Encrypting on Every Machine
 Described in Detail in a Microsoft PAP:
– http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetHT07.asp
DataSets
 DataSets have full database schema
– Tables
– Columns
– Primary and Foreign Keys
– Constraints
– Even Triggers
 Using Full Schema
– Allows validation without a network roundtrip
– Relationships allows hierarchical view of data
DataSets (2)
 Use DataSets for data caching
– Most data is not volatile
– Saves roundtrips to the database
– Less code than writing custom classes
Public Class _default
Inherits Page
Dim _dataSet As DataSet
Const MYDATASET As String = "MYDATASET"
Sub OnLoad(...)
If Session(MYDATASET) Is Nothing Then
_dataSet = New DataSet
Else
_dataSet = DirectCast(Session(MYDATASET), DataSet)
End If
'...
Session(MYDATASET) = _dataSet
End Sub
End Class
DataSets (3)
 DataSets can incrementally grow
– DataAdapter.Fill adds to DataSet, not replace
– Allows cache to be built up as needed
' Fill DataSet with Product #1
adapter.SelectCommand.Parameters("@product").Value = 1
adapter.Fill(_dataSet)
'...
' Fill DataSet with Product #2
adapter.SelectCommand.Parameters("@product").Value = 2
adapter.Fill(_dataSet)
' Now DataSet has both products in, not just one or the other
DataSets (4)
 Use DataSets for intermittent connectivity
– Store locally between times of connectivity
– Stored DataSets preserve state of changes
– Great for mobile apps (not just CF)
Public Class MainForm
Inherits Form
Dim _dataSet as DataSet
Sub OnClose(...)
_dataSet.WriteXml("foo.xml", XmlWriteMode.DiffGram)
End Sub
Sub OnLoad(...)
_dataSet.ReadXml("foo.xml", XmlReadMode.DiffGram)
End Sub
End Class
Typed DataSets
 Use Typed DataSets for Stable Schemas
– Data Validate at Compile Time
– Improves Developer Productivity with
Intellisense
– Easier to maintain in light of schema changes
than code
– Loads XSD Schema in with Code (faster)
– Demo
CommandBuilders
 Good for Simple Optimistic Concurrency
– Concurrency based on original values
– Robust, but Inefficient
– Update and Deletes are huge
– No support for Stored Procedures
DELETE FROM CUSTOMER WHERE ( (CustomerID = @p3) AND
((FirstName IS NULL AND @p4 IS NULL) OR (FirstName = @p5)) AND
((LastName IS NULL AND @p6 IS NULL) OR (LastName = @p7)) AND
((MiddleName IS NULL AND @p8 IS NULL) OR (MiddleName = @p9)) AND
((Address IS NULL AND @p10 IS NULL) OR (Address = @p11)) AND
((Apartment IS NULL AND @p12 IS NULL) OR (Apartment = @p13)) AND
((City IS NULL AND @p14 IS NULL) OR (City = @p15)) AND
((State IS NULL AND @p16 IS NULL) OR (State = @p17)) AND
((Zip IS NULL AND @p18 IS NULL) OR (Zip = @p19)) AND
((HomePhone IS NULL AND @p20 IS NULL) OR (HomePhone = @p21)) AND
((BusinessPhone IS NULL AND @p22 IS NULL) OR (BusinessPhone = @p23)) AND
((DOB IS NULL AND @p24 IS NULL) OR (DOB = @p25)) AND
((Discount IS NULL AND @p26 IS NULL) OR (Discount = @p27)) AND
((CheckedOut IS NULL AND @p28 IS NULL) OR (CheckedOut = @p29)) )
CommandBuilders (2)
 Conclusion
– Great for Prototyping
– Bad for Production Code
– Designer can achieve same at compile time
DataAdapters
 Use different adapters to load and update
– Batch Adapters to load multiple tables
– Single table adapters to update tables
 Use Designer Support
–
–
–
–
Add a Component to project to hold Adapters
Use designer to create Mappings
Get CommandBuilder Behavior at Compile-time
Or Map to Stored Procedures (preferred)
 Demo
DataSets & ASP.NET
 Cache DataSets for non-volatile data
– Keep DataSets around for longer than a Page
– Store them in session/global/cache
– Expiring Caches are great for DataSets
– Growing the Cache as needed with DataSets
Public Class _default
Inherits Page
Dim _dataSet As DataSet
Const MYDATASET As String = "MYDATASET"
Sub OnLoad(...)
If Session(MYDATASET) Is Nothing Then
_dataSet = New DataSet
Else
_dataSet = DirectCast(Session(MYDATASET), DataSet)
End If
'...
Session(MYDATASET) = _dataSet
End Sub
End Class
DataSets and ASP.NET (2)
 “What caching should I use?”
– Session for user specific data
 E.g. User preferences, shopping cart, etc.
– Application for app-level data
 E.g. e-commerce catalog, historical data, etc.
– Cache objects for volatile data
 E.g. stock prices, weather data, etc.
Distributed DataSets
 Treat DataSets as messages
– Same in Remoting DataSets as Web Services
– Remote the factory that delivers DataSets
– When updating, only return changes
– Minimize the data across the wire with Merge
Class DataSetFactory 'Remoted Class
' Remotely returns the DataSet
Public Function GetData() as DataSet
'...
End Function
Public Function SaveChanges(ByVal ds as DataSet) as DataSet
'...
End Function
End Class
Dim newDS as DataSet = factory.SaveChanges(ds.GetChanges())
ds.Merge(newDs)
Distributed DataSets (2)
 Is fine to use DataSets in Web Services
– Default serialization is incorrect
 DiffGram Format is Platform Specific
– You must make it an XML Document manually
 Can use GetXml() or XmlDataDocument
<WebMethod> _
Public Function GetData() as XmlDocument
Dim ds as new DataSet()
' Fill the DataSet
return new XmlDataDocument(ds)
End Function
Distributed DataSets (3)
 Typed DataSets are great in Web Services
– Can refer to .xsd in hand-coded WSDL
– While allowed, some toolkits don’t support it
– No way to make ?wsdl do it (at least not yet)
<definitions
...
xmlns:tds="http://yoursvr/typedds.xsd">
<types>
<xsd:complexType ref="tds:yourTypedDataSet" />
</types>
</definitions>
Distributed DataSets (4)
 Remoting may not work as expected
– DataSets Remoted by Value
 DataSet derive from MarshalByValueComponent
 So always remoted by value!
– Remoting DataTables/Rows does not help
 References to part of DataSet data transmit entire
DataSet
– Remoting DataSets can work
 Must transmit XML or subset of XML (of the
DataSet)
Transactions
 Server Transactions are usually preferable
– Generally shorter, therefore better
– Server Tx allows results to be returned
– Server Tx are not server cursors
Transactions (2)
 Client Transactions Have their Place
– Allows a client lock of rows (connected model)
– Connection must be maintained for length of Tx
– Be careful when using them for locking
 Scalability and performance will suffer
 Must have recovery mechanism
Transactions (3)
Dim tx As SqlTransaction
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
Try
conn.Open()
tx = conn.BeginTransaction()
' Get some Data
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM Authors WHERE au_id = '172-32-1176'"
cmd.Transaction = tx ' Don’t forget to set the transaction into the cmd
rdr = cmd.ExecuteReader()
' Get the zip and close the reader
rdr.Read()
Dim zip As String = rdr("zip").ToString()
rdr.Close()
' Make a change
cmd.CommandText = "UPDATE authors SET zip = ‘12345’"
cmd.ExecuteNonQuery()
' Commit the Tx
tx.Commit()
Catch
tx.Rollback()
Finally
cmd.Dispose()
rdr.Dispose()
conn.Close()
conn.Dispose()
End Try
Should You Write Vendor Neutral Code?
 The Promise of Vendor Neutral SQL is wrong
– Tuning databases too important
– Standard SQL isn’t complete
– Only do it if you have a compelling need
 You can get half the promise
– Write your ADO.NET in a vendor neutral way
– You may need to port the back-end code
– Saves you from rewriting it all
Using Vendor Neutral Code
 Interfaces are key
– IDbConnection is a great place to start
– Connection Factory to return IDbConnection
– Commands: IDbConnection.CreateCommand()
– Readers: IDbCommand.ExecuteReader()
Sub GetReader(SQL as String) as IDataReader
' Connection String Factory
Dim conn as IDbConnection =
ConnectionStrings.GetConnection()
' Follow the Rest of the model
Dim cmd as IDbCommand = conn.CreateCommand()
cmd.CommandText = SQL
Dim reader as IDataReader = cmd.ExecuteReader()
return reader
End Sub
Using Vendor Neutral Code (2)
 Works with DataAdapters too (mostly)
– IDbConnection is a still a great place to start
– No Factory for Data Adapters unfortunately
– Can use the connection to create the command
Sub GetAdapter(SQL as String) as IDbDataAdapter
' Connection String Factory
Dim conn as IDbConnection =
ConnectionStrings.GetConnection()
' Create the DataAdapter
Dim adapter as new SqlDataAdapter()
' Follow the Rest of the model
adapter.SelectCommand = conn.CreateCommand()
adapter.SelectCommand.CommandText = SQL
return adapter
}
Using Vendor Neutral Code (3)
 Vendor Specific Features
– Can cast to get specific features of an engine
– The more you do, the less vendor neutral it is
– This should be the exception, not the rule
Dim cmd as IDbCommand = GetCommand("SELECT * FROM AUTHORS FOR XML;")
If cmd is SqlCommand Then
Dim sqlCmd as SqlCommand = DirectCast(cmd, SqlCommand)
Dim xmlReader as XmlReader = sqlCmd.ExecuteXmlReader()
End If
Conclusion
 You can write ADO.NET badly
 Bad ADO.NET not nearly as toxic as bad
ADO
 DataSets are your friend…really
 Relationship of DataSets and XSD is good
 Distributed DataSets are not that hard
 CommandBuilders are vaguely evil
Questions?