Developing Windows and Web Applications using Visual Studio'NET

1
Developing Windows and Web Applications using
Visual Studio.NET

• Presented by Adam Cogan

2
Admin Stuff

• Attendance
• You initial sheet
• Hands On Lab
• You get me to initial sheet
• Homework
• Certificate
• At end of 10 sessions
• If I say if you have completed successfully ?

3
Session 1 Overview

• Agenda
• What is ADO.NET?
• Whats New in ADO.NET 2005?
• DataBinding
• The DataGrid
• Input Validation

4
The 10 Sessions

• http//www.ssw.com.au/ssw/events/2005UTSNET/defaul
  t.aspx

5
Homework
6
What is ADO.NET?

• What is ADO.NET, anyway?

7
What is a DataSet?

• Think of a DataSet object as a local in memory
  copy of database tables.
• Why is it disconnected?
• Problems we had with the client server model
• client apps held onto connections
• Client apps and updated and added records at
  will
• Many Trips are bad
• Changes can be reconciled against any datasource
  at any time.
• A Dataset is not limited to database tables. It
  can work with XML or for that matter any other
  data.

8

• SqlConnection cnn new SqlConnection("Persist
  Security InfoFalseIntegrated SecuritySSPIdatab
  asenorthwindservertunaConnect Timeout5")
• SqlDataAdapter adp new SqlDataAdapter("SELECT
  FROM Products", cnn)
• DataSet ds new DataSet()
• cnn.Open()
• adp.Fill(ds)
• cnn.Close()
• dataGridView1.DataSource ds.Tables0.DefaultVie
  w

9

• Recordsets are now DataReaders and DataSets
• Data binding controls can bind to these objects
• Listbox
• ComboBox
• DataGridView
• Not
• TreeView

10
DataSet Structure
11

• Untyped is
• A generic DataSet that can store data
• All columns are an object
• Eg. Int I Convert.ToInt32(datarow(Rank))
• Typed is
• Any DataSet that is derived from the base DataSet
  class that applies the information contained in
  the XSD to generate a typed class
• All columns are strongly typed
• Eg. Int I datarow.Rank

12
Typed DataSet Structure
13

• http//www.ssw.com.au/SSW/Standards/Rules/RulesToB
  etterdotNETProjects.aspxDataAdapter

14
What are the controls

• DataSet Represents a in-memory cache of data
• DataGridView Displays rows and columns of data
  in a grid you can customize
• BindingSource Encapsulates a data source for a
  form and provides navigation, filtering, sorting,
  and updating capabilities
• BindingNavigator Provides a user interface
  for navigation and manipulation of data bound to
  controls on a form
• ReportViewer Displays reports

15

• Once Datagridview is on form, you get a smart tag
  for configuration settings

ADO.NET is Cool
16

• Data source wizard allows you to bind to
  different sources
• Even objects

17

• Wizard allows you to choose the data to display

18

• Datagridview support photos straight from
  database with no customization

19
How do I call a SQL stored procedure?

• if(sqlConn.State ConnectionState.Closed)
• sqlConn.Open() SqlCommand cmd new
  SqlCommand("sp_my_stored_procedure",sqlConn)
  cmd.CommandTimeout 180 cmd.CommandType
  CommandType.StoredProcedure
• //Add parameter 1
• SqlParameter parm parm cmd.Parameters.Add(new
  SqlParameter("_at_oid", SqlDbType.VarChar,50))
  parm.Direction ParameterDirection.Input
  cmd.Parameters"_at_oid".Value OrderID
• //Add parameter 2 parm cmd.Parameters.Add(new
  SqlParameter("_at_custName", SqlDbType.VarChar,50))
  parm.Direction ParameterDirection.Output
• //Get the data
• SqlDataAdapter tempDA new SqlDataAdapter()
  tempDA.SelectCommand cmd DataSet dataSet1
  new DataSet() tempDA.Fill(dataSet1)
• DataTable resultTable dataSet1.Tables0

20
What's New in ADO.NET v2

• ADO.NET API is Evolving
• Backward compatible to ADO.NET 1.x
• SqlClient has close ties to SQL Server 2005
• Multiple Active Result Sets (MARS)
• Bulk Copy Operations
• SQL Server Notifications
• Others
• Transactions with SQL Server Snapshot Isolation
• Database Mirroring in SQL Server 2005
• Batch Processing
• Schema Discovery

21
DataTable Grows Up

• No longer dependent on DataSet for
• ReadXML WriteXML
• Transfer via Web Services
• Merge
• Merge multiple DataTables
• Note CANNOT merge DataRow Collections
• DataSets could not be serialised
• Now DataTable becomes iXMLSerializable
• DataTable/DataSet.Load Method
• Convert from and to DataReader
• Dataview.ToTable Method
• Rowstate was readonly
• Rowstate.SetAdded/SetModified
• Provider Independent API

22
Stream Data into DataTable DataSet

• DataSet/DataTable.Load
• Get a DataTable/DataSet from a DataReader
• Load dbDataReaders
• OLEleDbDataReader
• SqlDataReader
• DataTableReader
• Etc.
• Enables Merge functionality with DataReaders
• Like a Union
• LoadOption (also new to DataAdapter) to deal with
  concurrency issues

23
DataTableReader

• Get a DataReader from a DataTable/DataSet
• Inherits dbDataReader
• (SqlDataReader, OleDBDataReader, etc)
• DataSet/DataTable.CreateDataReader Method
• DataReader needs a database
• But not this DataTableReader
• Iteration without a database connection
• Populating list controls and more
• Navigate similar to SqlDataReader
• Handles changes to underlying data source well

24
DataView.ToTable

• Create a DataTable from a DataView!
• Rather than building from iteration or a new
  database query
• DistinctRows parameter is a blessing

25
RowState

• DataRow.RowState is no longer read only
• Unchanged rows can be modified
• SetAdded
• SetModified

26
Provider Independent API

• Database Independent Coding
• System.Data.Common Namespace
• Provider Factory Class
• Flip in database type at run time

example APINameSpaceSystem.Data.SQLClient Publ
ic Function GetDS(byVal APINameSpace as String)as
DataSet Dim provider as DbProviderFactory
dbProviderFactories.GetFactory(APINameSpace)
Dim connection As DbConnection-provider.CreateConn
ection Dim adapter as DbDataAdapter
provider.CreateDataAdapter code to connect
command, connection and dataadapter
adapter.fill(ds) Return ds End Function
27
Evolution Performance

• Batch Updates
• True Binary Serialization for Remoting

28
Batch Updating

• Works with DataAdapter.UpdateCommand
• Sends groups of queries to the database
• Fine Tune based on client and network resources
• Time to build multiple queries
• Network latency
• Processor
• Connection pooling
• Better control on web servers than deployed smart
  client apps

29
Binary Serializaton in Remoting

• V1.x
• DataSet is still xml
• V2.0
• DataSet is truly serialized as binary
• Major performance benefit with large amounts of
  data

30
Batch UpdateBigger is not always Better!
Speed of 10,000 Record Update
Speed of 100 Record Update
Batch Size
Batch Size
Not official benchmarks. Based one developers own
tests LocalSQL2005 Remote Hard WiredSQL2000
WebSQL2000
31
EvolutionSQL Server 2005 Integration

• Flexibility
• SQLDependency and SQLNotification
• Usability
• MARS (Multiple Active Resultsets)
• Reuse Connections
• Performance
• Asynchronous SQL Command
• Bulk Copy

32
SQL Server Query Notification

• Tied into SQL Server Service Broker (2005)
• System.Data.SQLNotification
• System.Data.SqlDependency
• ASP.NET has its own implementation
• System.Web.Caching.SqlCacheDependency
• Backwards compatible to SQL7 through polling
• Many rules and caveats
• This one is not documented (yet)
• Be sure DataBaseCompatibilityLevel is set to
  Version90
• Use wisely

33
MARS

• Enable reuse of database connections
• On by default
• MultipleActiveResultSetsfalse in connection
  string to turn off
• Benefits
• Multiple SqlDataReaders on one connection
• Allows multiple updates in one transaction
• note System.Transactions.TransactionScope
• Caveats
• Possible performance costs compared to connection
  pooling

34
Asynchronous Command Object

• Great for middle tier
• Keep working while waiting for a command to
  complete processing

35
Bulk Copy

• Load data into SQL Server via ADO.NET
• Nearly as fast as DTS!
• Sample Loading 104,225 records into SQL2005

36
Conclusion

• ADO.NET Team says
• We like ADO.NET and want to keep it!
• API stays consistent in its design
• ADO.NET 2 improves on
• Usability
• Flexibility
• Performance
• ADO.NET 2.0 leverages SQL Server 2005

37
Resources

• Whats New in ADO.NET 2.0
• Julia Lerman, MSDN Magazine April 2005
• Caching Improvements in ASP.NET Whidbey G.
  Andrew Duthie, MSDN Online February 2004
• Improved Caching in ASP.NET 2.0 Stephen
  Walther, MSDN Online, June 2004
• Asynchronous Command Execution in ADO.NET 2.0
  Pablo Castro, MSDN Online, July 2004
• ADO.NET and System.Xml v.2.0 The Beta Version
  Alex Homer, Dave Sussman, Mark Fussell,
  Addison-Wesley, March 2005
• New Features for Web Service Developers in Beta 1
  of the .NET Framework 2.0 Elliot Rapp,Yasser
  Shohoud and Matt Travis, MSDN Online, July 2004

38
Resources cont.

• Using CLR Integration in SQL Server 2005
  Multiple Authors from Microsoft Corporation, MSDN
  Online, Nov 2004
• ADO.NET 2.0 Multiple Active Result Sets per
  connection in Sql Server 2005 (MARS) FAQ
  Angel Saenz-Badillos, Weblog Post, Sept 7, 04
  (blogs.msdn.com/angelsb)
• Transaction Processing in ADO.NET 2.0 William
  Ryan, Sept 14, 2004, 15 Seconds website
  (www.15seconds.com)
• Query Notifications in ADO.NET 2.0 Bob
  Beauchemin, MSDN Online, September 2004
• Whats new in ADO.NET 2.0 http//msdn2.microsoft.c
  om/library/ex6y04yf(en-us,vs.80).aspx

39
2 things
AdamCogan_at_ssw.com.au
40
Thank You!