sas916 ado net in sql anywhere studio l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SAS916: ADO.NET in SQL Anywhere Studio PowerPoint Presentation
Download Presentation
SAS916: ADO.NET in SQL Anywhere Studio

Loading in 2 Seconds...

play fullscreen
1 / 49

SAS916: ADO.NET in SQL Anywhere Studio - PowerPoint PPT Presentation


  • 722 Views
  • Uploaded on

SAS916: ADO.NET in SQL Anywhere Studio. Alex Reif Technical Product Manager, iAnwhere Solutions alex.reif@ianywhere.com August 7, 2003. Agenda. What is .NET? What is ADO.NET? ADO.NET Data Provider for ASA Tips and Recommended Practices. .NET: Definition.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SAS916: ADO.NET in SQL Anywhere Studio' - salena


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sas916 ado net in sql anywhere studio

SAS916: ADO.NET in SQL Anywhere Studio

Alex ReifTechnical Product Manager, iAnwhere Solutionsalex.reif@ianywhere.comAugust 7, 2003

agenda
Agenda
  • What is .NET?
  • What is ADO.NET?
  • ADO.NET Data Provider for ASA
  • Tips and Recommended Practices
net definition
.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

net framework
.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)
net managed code
.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
net terms
.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”
agenda7
Agenda
  • What is .NET?
  • What is ADO.NET?
  • ADO.NET Data Provider for ASA
  • Tips and Recommended Practices
ado net
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”
ado net provider classes
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
example using datareader c
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();

example using dataadapter vb net
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"

agenda12
Agenda
  • What is .NET?
  • What is ADO.NET?
  • ADO.NET Data Provider for ASA
  • Tips and Recommended Practices
asa interfaces
ASA Interfaces
  • ODBC
  • ESQL
  • OLEDB
  • Open Client
  • JDBC
  • Perl
  • PHP
  • ADO.NET
ado net data providers for asa
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

asa data provider
ASA Data Provider
  • Implements iAnywhere.Data.AsaClient namespace
    • AsaConnection, AsaCommand, AsaDataReader etc.
  • Supports Windows (.NET framework) and CE.NET (Compact .NET framework)
using the asa data provider
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
asa data provider18
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
asaconnection
ASAConnection
  • Purpose
    • Represents a connection to an ASA database
  • Methods
    • Open
    • Close
    • CreateCommand
  • Properties
    • ConnectionString property to specify connect parameters
  • Events
    • InfoMessage
    • StateChange
connection example
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();

asaexception
ASAException
  • Purpose
    • A failed statement will throw an ASAException
  • Methods
    • none
  • Properties
    • Errors (collection of ASAError objects)
    • Message
  • Events
    • none
asaerror
ASAError
  • Purpose
    • Provides errors and exceptions back to the application
  • Methods
    • none
  • Properties
    • Message
    • NativeError
    • Source
    • SqlState
    • ToString
  • Events
    • none
errors and exceptions example
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" );

}

asacommand
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
command example
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;

}

asaparameter
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
asaparameter example
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();

asatransaction
ASATransaction
  • Purpose
    • Represents a SQL transaction
    • No constructor; returned by ASAConnection.BeginTransaction()
  • Methods
    • Commit
    • Rollback
  • Properties
    • IsolationLevel
  • Events
    • none
asadatareader
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
datareader example
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();

datareader example blobs
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();

asadataadapter
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
dataadapter example
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";

ado net dataset
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
dataset example
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";

application deployment
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

updating the asa provider
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
policy files
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
example policy file
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>

application deployment win32
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
application deployment windows ce
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!
agenda42
Agenda
  • What is .NET?
  • What is ADO.NET?
  • ADO.NET Data Provider for ASA
  • Tips and Recommended Practices
miscellaneous topics
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();

miscellaneous topics44
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 )

net compact framework tips
.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
asp net applications
ASP.NET Applications
  • http://www.ianywhere.com/developer/technotes/asp_net.html
  • http://www.ianywhere.com/developer/technotes/asa_asp_net.html
ianywhere solutions at techwave2003
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.
ianywhere solutions at techwave200348
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/