SAS916: ADO.NET in SQL Anywhere Studio

1. SAS916: ADO.NET in SQL Anywhere Studio Alex ReifTechnical Product Manager, iAnwhere Solutionsalex.reif@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 • Microsoft’s 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( “Provider=AsaProv.90;Data Source=ASA 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 = _ "Provider=AsaProv.90;Data Source=ASA 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 iAnywhere.Data.AsaClient System.Data.Oledb System.Data.Odbc ASA OLEDB Driver ASA ODBC 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.AsaConnection(); myConn.ConnectionString = "Data Source=ASA 9.0 Sample;UID=DBA;PWD=SQL"; 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 Source=ASA 9.0 Sample;UID=DBA;PWD=SQL” ); myConn.Open(); } catch( AsaException ex ) { MessageBox.Show( ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].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 = "ENG=asademo;UID=DBA;PWD=SQL"; myConn.Open(); myReader = myCmd.ExecuteReader(); counter = 0; while (myReader.Read()) { MessageBox.Show(myReader.GetString(0)); counter = counter + 1; if( counter >= 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.Parameters[0].Value = 600; myCmd.Parameters.Add( parm2 ); myCmd.Parameters[1].Value = "Eastern Sales"; myConn.ConnectionString = "ENG=asademo;UID=DBA;PWD=SQL"; myConn.Open(); int recordsAffected = myCmd.ExecuteNonQuery();

28. ASATransaction • Purpose • Represents a SQL transaction • No constructor; returned by ASAConnection.BeginTransaction() • 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( "ENG=asademo;pwd=sql;uid=dba" ); 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 > 550", myConn ); AsaDataReader reader; char[] buf = new char[10]; 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 > 0 ) { dataIndex += len; desc += buf; charsRead = reader.GetChars(1, dataIndex, buf, 0, 10); } MessageBox.Show( "dept_name: " + reader.GetString(0) + "\ndescription: " + desc ); } reader.Close();

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", "eng=asademo;uid=dba;pwd=sql" ); 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 provider’s 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", "eng=asademo;uid=dba;pwd=sql" ); 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 don’t match dbdata[8|9].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 • Microsoft’s 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 <configuration> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="iAnywhere.Data.AsaClient" publicKeyToken="f222fc4333e0d400" /> <bindingRedirect oldVersion="9.0.0.0 - 9.0.0.1107" newVersion="9.0.0.1108"/> </dependentAssembly> </assemblyBinding> </runtime> </configuration>

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) • dbdata[8|9].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 dbdata[8|9].dll for each CE platform • In %ASANY[8|9]%\ce\xxx • Can go in Windows directory or your application’s 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 • “…;POOLING=TRUE;Max Pool Size=50;Min Pool Size=5“ • 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.html

46. ASP.NET Applications • http://www.ianywhere.com/developer/technotes/asp_net.html • http://www.ianywhere.com/developer/technotes/asa_asp_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?