SAS916: ADO'NET in SQL Anywhere Studio

1
SAS916 ADO.NET in SQL Anywhere Studio
Alex ReifTechnical Product Manager, iAnwhere
Solutionsalex.reif_at_ianywhere.comAugust 7, 2003
2
Agenda

• What is .NET?
• What is ADO.NET?
• ADO.NET Data Provider for ASA
• Tips and Recommended Practices

3
.NET Definition

• .NET technology enables the creation and use of
  XML-based applications, processes, and Web sites
  as services that share and combine information
  and functionality with each other by design, on
  any platform or smart device, to provide tailored
  solutions for organizations and individual
  people.
• .NET is a comprehensive family of products,
  built on industry and Internet standards, that
  provide for each aspect of developing (tools),
  managing (servers), using (building block
  services and smart clients) and experiencing
  (rich user experiences) Web services.
• http//www.microsoft.com/net/basics/faq.asp

4
.NET Framework

• Infrastructure for the overall .NET platform
• Common Language Runtime (CLR)
• Managed, protected application execution
  environment
• C, Visual Basic, C, J,
• Common Class Libraries
• Windows Forms, ADO.NET, ASP.NET,
• .NET Compact Framework
• Subset of .NET Framework for smart devices
• Part of Visual Studio.NET 2003
• Included on device with CE.NET (CE 4.1) (released)

5
.NET Managed Code

• Code is written in desired language (C, C,
  VB.NET, Pascal, etc.)
• Compiled into Microsoft Intermediate Language
  (MSIL)
• At runtime Common Language Runtime (CLR) compiles
  the MSIL code and executes it

6
.NET Terms

• Namespace
• A logical naming scheme for grouping related
  types
• Analogous to Java packages
• iAnywhere.Data.AsaClient ? iAnywhere.Data is the
  namespace
• Assembly
• A collection of one or more files that are
  versioned and deployed as a unit
• DLLs in .NET-land
• Unlike DLLs, .NET assemblies also include
• Version control information
• Security information
• GAC (Global Assembly Cache)
• A machine-wide code cache that stores assemblies
  specifically installed to be shared by many
  applications on the computer

7
Agenda

• What is .NET?
• What is ADO.NET?
• ADO.NET Data Provider for ASA
• Tips and Recommended Practices

8
ADO.NET

• Microsofts latest data access API
• ODBC, DAO, RDO, OLE DB, ADO
• System.Data namespace
• Data providers manage access to data stores
• Providers from Microsoft
• System.Data.OleDb
• System.Data.Odbc
• System.Data.SQLClient
• System.Data.OracleClient
• Managed provider ? Data provider

9
ADO.NET Provider Classes

• Each managed provider implements the following
  classes
• Connection connects to datasource
• Command executes commands
• DataReader forward-only, read-only access to
  data
• DataAdapter fills DataSet and handles updates
• Parameters parameter to a Command object
• Transaction provides commit/rollback
  functionality
• Error, Exception collect error/warning messages

10
Example using DataReader (C)

• OleDbConnection conn new OleDbConnection(
  ProviderAsaProv.90Data SourceASA 9.0 Sample
  )
• conn.Open()
• OleDbCommand cmd new OleDbCommand(
• select emp_lname from employee, conn )
• OleDbDataReader reader cmd.ExecuteReader()
• while( reader.Read() )
• str reader.GetString( 0 )
• Console.WriteLine( str )
• reader.Close()
• conn.Close()

11
Example using DataAdapter (VB.NET)

• Dim conn As New System.Data.OleDb.OleDbConnection(
  )
• conn.ConnectionString _
• "ProviderAsaProv.90Data SourceASA 9.0
  Sample"
• conn.Open()
• Dim ds As New DataSet()
• Dim da As New OleDb.OleDbDataAdapter("select
  from employee", conn)
• da.Fill(ds, "Employees")
• DGEmployees.DataSource ds
• DGEmployees.DataMember "Employees"

12
Agenda

• What is .NET?
• What is ADO.NET?
• ADO.NET Data Provider for ASA
• Tips and Recommended Practices

13
ASA Interfaces

• ODBC
• ESQL
• OLEDB
• Open Client
• JDBC
• Perl
• PHP
• ADO.NET

14
ADO.NET Data Providers For ASA

• OLEDB
• ODBC
• AsaClient

System.Data.Odbc
System.Data.Oledb
iAnywhere.Data.AsaClient
ASA ODBC Driver
ASA OLEDB Driver
ASA
15
ASA Data Provider

• Implements iAnywhere.Data.AsaClient namespace
• AsaConnection, AsaCommand, AsaDataReader etc.
• Supports Windows (.NET framework) and CE.NET
  (Compact .NET framework)

16
Using the ASA Data Provider

• Reference the provider in your project (required)
• Right-click References folder
• In the .NET tab, find iAnywhere.Data.AsaClient.dll
  
• If the provider is not listed, find it in
  ASANY9\win32
• Reference provider in your code (optional)
• Allows you to use ASA provider classes without
  namespace prefix
• C using iAnywhere.Data.AsaClient
• VB.NET Imports iAnywhere.Data.AsaClient

17
ASA Data Provider Example
18
ASA Data Provider

• Classes
• AsaConnection
• AsaError
• AsaException
• AsaCommand
• AsaParameter
• AsaDataReader
• AsaDataAdapter
• AsaCommandBuilder
• AsaErrorCollection
• AsaInfoMessageEventArgs
• AsaParameterCollection
• AsaPermission
• AsaPermissionAttribute
• AsaRowUpdatedEventArgs
• AsaRowUpdatingEventArgs
• AsaTransaction

• Enumerations
• AsaDbType
• Delegates
• AsaInfoMessageEventHandler
• AsaRowUpdatedEventHandler
• AsaRowUpdatingEventHandler

19
ASAConnection

• Purpose
• Represents a connection to an ASA database
• Methods
• Open
• Close
• CreateCommand
• Properties
• ConnectionString property to specify connect
  parameters
• Events
• InfoMessage
• StateChange

20
Connection Example

• using iAnywhere.Data.AsaClient
• private AsaConnection myConn
• myConn new iAnywhere.Data.AsaClient.AsaConnectio
  n()
• myConn.ConnectionString
• "Data SourceASA 9.0 SampleUIDDBAPWDSQL"
• myConn.Open()

21
ASAException

• Purpose
• A failed statement will throw an ASAException
• Methods
• none
• Properties
• Errors (collection of ASAError objects)
• Message
• Events
• none

22
ASAError

• Purpose
• Provides errors and exceptions back to the
  application
• Methods
• none
• Properties
• Message
• NativeError
• Source
• SqlState
• ToString
• Events
• none

23
Errors and Exceptions Example

• try
• myConn new AsaConnection(
• "Data SourceASA 9.0 SampleUIDDBAPWDSQL
  )
• myConn.Open()
• catch( AsaException ex )
• MessageBox.Show(
• ex.Errors0.Source " "
• ex.Errors0.Message " ("
• ex.Errors0.NativeError.ToString() ")",
• "Failed to connect" )

24
ASACommand

• Purpose
• Represents a SQL statement or stored procedure
  that is executed against an Adaptive Server
  Anywhere database
• Methods
• ASACommand constructor (optionally supply an
  ASAConnection and a SQL string)
• CreateParameter
• ExecuteNonQuery (for inserts/updates/deletes)
• ExecuteReader (returns result set DataReader)
• ExecuteScalar (returns a single result column
  1, row 1)
• Properties
• Connection
• CommandType (StoredProcedure or Text)
• CommandText
• Events
• none

25
Command Example

• myConn new AsaConnection()
• AsaCommand myCmd new AsaCommand(
• "select dept_name from department", myConn)
• AsaDataReader myReader
• int counter
• myConn.ConnectionString "ENGasademoUIDDBAPWD
  SQL"
• myConn.Open()
• myReader myCmd.ExecuteReader()
• counter 0
• while (myReader.Read())
• MessageBox.Show(myReader.GetString(0))
• counter counter 1
• if( counter gt 10 ) break

26
ASAParameter

• Purpose
• Represents a parameter to an AsaCommand and
  optionally, its mapping to a DataSet column
• Methods
• ASAParameter constructor (optionally specify
  parameter value/type)
• Properties
• ASADbType
• Direction (in, out, inout, return value)
• Value
• Precision
• Scale
• Size
• Events
• none

27
ASAParameter Example

• myConn new AsaConnection()
• AsaCommand myCmd new AsaCommand(
• "insert into department(dept_id, dept_name)
  values (?, ?)", myConn)
• AsaParameter parm1 new AsaParameter()
• AsaParameter parm2 new AsaParameter()
• parm1.AsaDbType AsaDbType.Integer
• parm2.AsaDbType AsaDbType.Char
• myCmd.Parameters.Add( parm1)
• myCmd.Parameters0.Value 600
• myCmd.Parameters.Add( parm2 )
• myCmd.Parameters1.Value "Eastern Sales"
• myConn.ConnectionString "ENGasademoUIDDBAPWD
  SQL"
• myConn.Open()
• int recordsAffected myCmd.ExecuteNonQuery()

28
ASATransaction

• Purpose
• Represents a SQL transaction
• No constructor returned by ASAConnection.BeginTra
  nsaction()
• Methods
• Commit
• Rollback
• Properties
• IsolationLevel
• Events
• none

29
ASADataReader

• Purpose
• A read-only, forward-only result set from a query
  or stored procedure (rows are fetched as needed)
• Methods
• GetXXX (get column value as specific data type)
• IsDBNull
• GetName (name of specified column)
• GetOrdinal (ID of speficied column)
• GetSchemaTable (returns metadata)
• Read (move to the next row)
• Properties
• FieldCount
• RecordsAffected
• Events
• none

30
DataReader Example

• myConn new AsaConnection( "ENGasademopwdsqlu
  iddba" )
• AsaCommand cmd new AsaCommand( "select from
  department", myConn )
• AsaDataReader reader
• myConn.Open()
• reader cmd.ExecuteReader()
• while( reader.Read() )
• int dept_id reader.GetInt32(0)
• string dept_name reader.GetString(1)
• MessageBox.Show( "dept_id " dept_id
• "\ndept_name " dept_name )
• reader.Close()

31
DataReader Example BLOBs

• AsaCommand cmd new AsaCommand( "select
  name,description from
• product where id gt 550", myConn )
• AsaDataReader reader
• char buf new char10
• string desc null
• long dataIndex 0
• myConn.Open()
• reader cmd.ExecuteReader()
• while( reader.Read() )
• int charsRead reader.GetChars(1, dataIndex,
  buf, 0, 10)
• while( charsRead gt 0 )
• dataIndex len
• desc buf
• charsRead reader.GetChars(1, dataIndex, buf,
  0, 10)
• MessageBox.Show( "dept_name "
  reader.GetString(0)
• "\ndescription " desc )

32
ASADataAdapter

• Purpose
• Represents a set of commands and a database
  connection used to fill a DataSet and to update a
  database
• Methods
• Fill (all rows are fetched at once cursor is
  closed immediately after rows are fetched)
• FillSchema
• Update
• Properties
• SelectCommand
• InsertCommand
• UpdateCommand
• DeleteCommand
• Events
• FillError
• RowUpdated
• RowUpdating

33
DataAdapter Example

• DataSet ds new DataSet()
• AsaDataAdapter da new AsaDataAdapter(
• "select from department",
• "engasademouiddbapwdsql" )
• da.Fill(ds, "Department")
• DG.DataSource ds
• DG.DataMember "Department"

34
ADO.NET DataSet

• Disconnected data access
• In-memory cache of data retrieved from database
• A collection of DataTables which consist of
• DataRow (data)
• DataColumn (schema)
• DataRelation (relate DataTables via DataColumns)
• Can read/write data/schema as XML documents
• Works with managed providers to load and modify
  data using the providers DataAdapter
• 9.0 DataSet returned to SOAP requests

35
DataSet Example

• DataSet dsout new DataSet()
• DataSet dsin new DataSet()
• AsaDataAdapter da new AsaDataAdapter(
• "select from department", "engasademouiddba
  pwdsql" )
• da.Fill(dsout, "Department")
• dsout.WriteXml( "f\\temp\\dept.xml" )
• dsin.ReadXml( "f\\temp\\dept.xml" )
• dataGrid1.DataSource dsin
• dataGrid1.DataMember "Department"

36
Application Deployment

• ASA ADO.NET Provider has two files
• iAnywhere.Data.AsaClient.dll (managed code)
• dbdata9.dll/dbdata8.dll (native code)
• Both files must be deployed
• Version of files (i.e. build number) must match
  (as of 8.0.2.4255)
• iAnywhere.Data.AsaClient.dll will throw error if
  versions dont match

dbdata89.dll
iAnywhere.Data.AsaClient.dll
ASA
Your Application
.NET Common Language Runtime
37
Updating the ASA Provider

• At compile time, .NET compilers use strong name
  of referenced assemblies
• Strong name includes both name AND version
• Microsofts attempt to eliminate DLL hell
• At run time, .NET looks for assemblies based on
  strong name
• An application was compiled with
  iAnywhere.Data.AsaClient version 9.0.0.1108 will
  only run with version 9.0.0.1108 UNLESS you have
  a publisher policy file in place

38
Policy Files

• Policy files redirect one version of an assembly
  to another
• Installed into GAC
• ASA EBFs install policy files, for example
• Application built against 9.0.0.1108
• EBF applied to machine upgrade to 9.0.0.1200
• EBF installs policy file
• Requests for 9.0.0.0 9.0.0.1199 redirected to
  9.0.0.1200
• ASANY9\win32\iAnywhere.Data.AsaClient.dll.config
  
• Security is built-in to policy files
• Policy files cannot be compiled without private
  key assembly was signed with
• Only iAnywhere can create policy files for
  iAnywhere assemblies

39
Example Policy File

• ltconfigurationgt
• ltruntimegt
• ltassemblyBinding xmlns"urnschemas-micros
  oft-comasm.v1"gt
• ltdependentAssemblygt
• ltassemblyIdentity
• name"iAnywhere.Data.AsaClient"
• publicKeyToken"f222fc4333e0d400" /gt
• ltbindingRedirect
• oldVersion"9.0.0.0 - 9.0.0.1107"
• newVersion"9.0.0.1108"/gt
• lt/dependentAssemblygt
• lt/assemblyBindinggt
• lt/runtimegt
• lt/configurationgt

40
Application Deployment Win32

• Files can go anywhere in the path or program
  directory
• iAnywhere.Data.AsaClient.dll
• Register with gacutil.exe (shipped with .NET)
• dbdata89.dll
• No registration required

41
Application Deployment Windows CE

• One iAnywhere.Data.AsaClient.dll for all CE
  platforms
• Deploy to the Windows or application directory
• Visual Studio.NET will deploy automatically
• 8.0.2 only For Visual Studio.NET 2003, use
  ASANY8\ce\VSNet2003\iAnywhere.Data.AsaClient.dll
  
• Separate dbdata89.dll for each CE platform
• In ASANY89\ce\xxx
• Can go in Windows directory or your applications
  directory on the device
• Policy files are not supported by .NET Compact
  Framework
• .NET will automatically use newest version of
  iAnywhere.Data.AsaClient.dll that it finds
• Make sure to use the CE version of the DLLs!

42
Agenda

• What is .NET?
• What is ADO.NET?
• ADO.NET Data Provider for ASA
• Tips and Recommended Practices

43
Miscellaneous Topics

• Connection Pooling
• Set in connection string
• Enabled by default
• POOLINGTRUEMax Pool Size50Min Pool Size5
• CommandBuilder
• A way to generate single-table SQL statements
  that reconcile changes made to a DataSet with the
  data in the associated database
• Calling stored procedures
• AsaCommand cmd new AsaCommand( sp_myproc,
  conn )
• cmd.CommandType CommandType. StoredProcedure
• cmd.ExecuteXXXXX()

44
Miscellaneous Topics

• Events and Delegates
• Declare the delegate
• public delegate void AsaInfoMessageEventHandler (
  object sender, AsaInfoMessageEventArgs e )
• Define the event handler
• public class HandleMsg
• public void MsgEventHandler(
• object sender,
• AsaInfoMessageEventArgs e)
• Attach the event handler to the event
• msghandler new ASAinfoMessageEventHandler(
  HandleMsg.MsgEventHandler )

45
.NET Compact Framework Tips

• Emulator
• Run ASA on desktop, not emulator
• Deploying ASA to Pocket PC 2002 (or higher)
  Emulator
• Create x86 CAB file, map drive from device to
  desktop
• Device
• Connecting to ASA on device from desktop
  http//www.sybase.com/detail/1,6904,1025441,00.htm
  l

46
ASP.NET Applications

• http//www.ianywhere.com/developer/technotes/asp_n
  et.html
• http//www.ianywhere.com/developer/technotes/asa_a
  sp_net.html

47
iAnywhere Solutions at TechWave2003
Activities for iAnywhere Solutions

• Ask the iAnywhere Experts on the Technology
  Boardwalk
• Drop in during exhibit hall hours and have all
  your questions answered by our technical experts!
• Appointments outside of exhibit hall hours are
  also available to speak one-on-one with our
  Senior Engineers. Ask questions or get your
  yearly technical review ask us for details
• m-Business Pavilion
• Visit the m-Business Pavilion in the exhibit hall
  to see how companies like Intermec have built
  m-Business solutions using iAnywhere Solutions
  technology
• Wi-Fi Hotspots brought to you by Intel
  iAnywhere Solutions
• You can enjoy wireless internet access via a
  Wi-Fi hotspot provided by Intel and iAnywhere
  Solutions. Using either a laptop or PDA that is
  Wi-Fi 802.11b wirelessly-enabled, visitors can
  access personal email, the internet, and
  "TechWave To Go", a My AvantGo channel providing
  up-to-date information about TechWave classes,
  events and more.

48
iAnywhere Solutions at TechWave2003
Activities for iAnywhere Solutions

• Developer Community
• A one-stop source for technical information!
• Access to newsgroups,new betas and code samples
• Monthly technical newsletters
• Technical whitepapers,tips and online product
  documentation
• Current webcast,class,conference and seminar
  listings
• Excellent resources for commonly asked questions
• All available express bug fixes and patches
• Network with thousands of industry experts
• http//www.ianywhere.com/developer/

49
Questions?