ASE135 .NET Connectivity to Adaptive Server Enterprise

1
ASE135 - .NET Connectivity to Adaptive Server
Enterprise
Ashish MahajanDevelopment Manager and
Architectashishm_at_sybase.comAugust 7, 2003
2
Agenda

• Introduction to the .NET Framework
• Introduction to ADO.NET
• ADO.NET Concepts
• Sneak preview - Sybase ASE .NET Data Provider
• Sybase ASE .NET provider classes
• Code Examples
• Coding recommendations
• Visual Studio .NET support
• XML Support
• Product Roadmap
• Where to get more information
• Questions

3
What is the .NET Framework

• Integral Windows component for building and
  running the next generation of software
  applications and Web services
• The .NET Framework
• Supports over 20 different programming languages
• Manages much of the plumbing involved in
  developing software, enabling developers to focus
  on the core business logic code
• Makes it easier than ever before to build,
  deploy, and administer secure, robust, and
  high-performing applications
• The .NET Framework is composed of the common
  language runtime and a unified set of class
  libraries

4
What is the .NET Framework cont

• The common language runtime (CLR)
• Execution Engine (Virtual Machine)
• run-time services such as language integration,
  security enforcement, and memory, process, and
  thread management
• life-cycle management, strong type naming,
  cross-language exception handling, and dynamic
  binding
• Class Libraries
• Base classes - input/output, string manipulation,
  security management, network communications,
  thread management, text management, and user
  interface design features
• The ADO.NET classes enable developers to interact
  with data
• ASP.NET classes support the development of
  Web-based applications and Web services
• Common, consistent development interface across
  all languages supported by the .NET Framework.

5
Introducing ADO.NET

• Data Access Framework for .NET applications
• A set of classes and interfaces defined in
  System.Data namespace. Examples DataTable,
  DataSet, IDbConnection, IDbCommand
• MS SQL Server and OLEDB provider implementations
  are in the System.Data.SqlClient and
  System.Data.OleDb namespaces respectively.
• Other provider implementations like Sybase ASE
  are in their own namespace. The namespace for
  Sybase ASE provider implementation is
  Sybase.Data.AseClient
• Stream Based Data Access
• Use Provider specific DataReader, like
  AseDataReader to retrieve a read-only,
  forward-only stream of data from the database.
  The application has to be connected to the
  database server while traversing the result.
• Disconnected Data Access
• Use DataSet class. DataSet is an in-memory data
  cache with which you can work with while
  disconnected from the database server. To use the
  DataSet you create an instance of the DataSet and
  populate the data in it using provider specific
  DataAdapter, like AseDataAdapter.

6
Introducing ADO.NET - Managed Provider overview
UI - Controls,Forms
DataSet
DataReader
DataAdapter
Command
Connection
Managed Provider
7
ADO.NET concepts

• Provider agnostic classes and interfaces
• System.Data namespace
• IDbConnection
• IDbCommand
• IDbTransaction
• IDataReader
• IDataParameter and IDbDataParameter
• IDataAdapter and IDbDataAdapter
• DataSet
• Provider specific classes
• Sybase.Data.AseClient namespace
• AseConnection
• AseCommand
• AseTransaction
• AseDataReader
• AseParameter
• AseDataAdapter

8
ADO.NET concepts

• For the connected world
• Use AseConnection to connect to the ASE Server
• Optionally start a Transaction by calling
  BeginTransaction() function on AseConnection
• Create a AseCommand by calling CreateCommand
  function on AseConnection
• Set the CommandText property
• Call ExecuteReader for a SELECT or
  ExecuteNonQuery for UPDATE or INSERT SQL
  commands
• Use AseDataReader to walk through the result

9
ADO.NET concepts

• For the Disconnected world
• Use AseDataAdapter to fill DataSet object
• Disconnect from Database
• Use the DataSet to insert new rows and modify
  data
• Apply the updates back to the database
• Watch out!!
• As you are disconnected there are no locks held
  on the rows which are being updated on the
  DataSet. When these changes are applied back to
  the database you might be overriding changes by a
  different user. Your application must be designed
  to resolve any such conflicts.

10
Introducing the Sybase ASE .NET Data Provider

• Sybase.Data.AseClient namespace
• Accessible by any language that supports CLR, C,
  VB.NET, Managed C etc
• No open client! No config files
• Written over TDS - Sybase native protocol

11
CodeSample
using Sybase.Data.AseClient AseConnection
conn new AseConnection (ServerMANGOPort5
000User IDsapwdwelcomeDatabasepubs2") try
conn.Open () AseCommand cmd new
AseCommand ("select title from titles", conn)
AseDataReader reader cmd.ExecuteReader ()
while (reader.Read ()) Console.WriteLine
(reader.GetString(0)) catch (AseException ex)
Console.WriteLine (ex.Message) finally
conn.Close ()
12
AseConnection

• Encapsulates database connection to ASE
• Typical usage
• Create AseConnection
• Specify connection string in constructor, or
• Assign connection string to ConnectionString
  property
• Open the connection
• Use the connection
• Close the connection

13
AseConnection - Properties

• ConnectionString
• Data SourceServerAddressAddrNetwork Address
• Port
• DatabaseInitial Catalog
• Connect TimeoutConnection Timeout
• User IDuseriduid
• Passwordpwd
• ConnectionState (ReadOnly)
• ConnectionTimeout (ReadOnly)
• Database (ReadOnly)

14
AseConnection - Methods

• Open() Authenticates and connects to the ASE
  server
• Close() Logs out and disconnects from the ASE
  server
• ChangeDatabase() Requests the ASE server to set
  the current database
• BeginTransaction() Begins a database
  transaction and returns an AseTransaction object
• CreateCommand() Creates and returns an
  AseCommand object

15
AseCommand

• Represents commands performed on the ASE Server
• Encapsulates one of the following
• Dynamic SQL statements
• Stored procedures
• Execute methods execute commands
• ExecuteNonQuery INSERT/UPDATE/DELETE
• ExecuteScalar SQL functions
• ExecuteReader - Queries

16
AseCommand - Properties

• CommandText
• CommandType
• Connection
• Transaction
• Parameters
• CommandTimeout
• UpdatedRowSource

17
AseCommand - Methods

• ExecuteNonQuery() Executes the CommandText and
  returns number of rows affected. Typically used
  for INSERT/UPDATE/DELETE
• ExecuteReader() Executes the CommandText as a
  query and returns AseDataReader to traverse the
  result of the query
• ExecuteScalar() Executes the CommandText and
  returns the value of the first column of the
  first row in the result. Typically used to
  execute functions like COUNT, AVG, MIN, MAX, SUM
  etc

18
Commands with Parameters

• Commands with replaceable parameters
• Usage
• Create AseCommand object
• Call Add on commands Parameters collection to
  add parameter values
• Set the Parameter Value
• Execute the command

update titles set title _at_title where title_id
_at_title_id
update titles set title ? where title_id ?
cmd.Parameters.Add(new AseParameter("_at_title",
AseDbType.VarChar))
19
Example
AseConnection conn new AseConnection
("Server'ASHISHMW2K'Port5000User
IDsaDatabasepubs2) try conn.Open
() AseCommand cmd new AseCommand
( update titles set title _at_title, notes
_at_notes where title_id
_at_title_id, conn) cmd.Parameters.Add(new
AseParameter(_at_title, AseDbType.VarChar))
cmd.Parameters.Add(new AseParameter(_at_notes,
AseDbType.VarChar)) cmd.Parameters.Add(new
AseParameter(_at_title_id, AseDbType.VarChar))
cmd.Parameters_at_title.Value Harry
Potter cmd.Parameters_at_notes.Value
The most popular series cmd.Parameters_at_ti
tle_id.Value BU3345 cmd.ExecuteNonQuery
() catch (AseException ex) //Handle the
exception finally conn.Close ()
20
Stored Procedures

• Precompiled database commands
• Execute faster than dynamic SQL commands
• Usage
• Create AseCommand object
• Set CommandText to the name of the
  StoredProcedure
• Set CommandType to StoredProcedure
• Add parameters to Parameter collection
• Optionally set ParameterDirection properties
• Execute the command

21
Example Executing a Stored Proc
AseConnection conn new AseConnection
("Server'ASHISHMW2K'Port5000User
IDsaDatabasepubs2) try conn.Open ()
AseCommand cmd new AseCommand
("storename_proc", conn) cmd.CommandType
CommandType.StoredProcedure / // You can also
execute the stored proc as a statement
AseCommand cmd new AseCommand
( call storename_proc ?", conn) /
cmd.Parameters.Add ("_at_stor_name",
Bookbeat) cmd.ExecuteNonQuery() catch
(AseException) //Handle the
exception finally conn.Close ()
22
AseDataReader

• Encapsulates a read-only forward-only stream of
  data which is returned as a result of a query
• You have to be connected to the ASE Server when
  traversing the result
• Allows type safe access via Getter methods
• You must close the AseDataReader when finished

23
AseDataReader - Properties

• FieldCount
• IsClosed
• Item
• RecordsAffected

24
AseDataReader - Methods

• Get() Gets the value of the specified column
  as a .NET type safe object. Example GetString()
  returns a .NET String object
• IsDBNull() returns whether the value of the
  specified column is set to null in the ASE Server
• NextResult() Advances the data reader to the
  next result
• Read() Advances the data reader to the next
  record

25
Example Walking a result
AseConnection conn new AseConnection
("Server'ASHISHMW2K'Port5000User
IDsaDatabasepubs2) try conn.Open ()
AseCommand cmd new AseCommand ("select title
from titles", conn) AseDataReader reader
cmd.ExecuteReader () while (reader.Read ())
Console.WriteLine (reader.GetString(0))
catch (AseException) //Handle the
exception finally conn.Close ()
26
DataSet

• Disconnected cache of data
• Defined in System.Data namespace
• Provider agnostic
• Is a collection of DataTable, another provider
  agnostic class
• Can model relationships between tables
• Data can be read, inserted, updated and deleted
  while there is no connection to the database
  server

27
DataSet
DataSet
DataTable
AseDataAdapter
AseConnection
Adaptive Server Enterprise
DataRelation
DataTable
AseDataAdapter
AseConnection
28
AseDataAdapter

• The link between the DataSet class and Adaptive
  Server Enterprise
• Two important methods
• Fill() a DataSet with data from the server
• Update() or apply the changes in a DataSet back
  to the server
• When using AseDataAdapter Fill or Update methods,
  it can open the connection if it is not already
  open

29
AseDataAdapter - Properties

• SelectCommand
• UpdateCommand
• InsertCommand
• DeleteCommand

30
AseDataAdapter - Methods

• Fill() Adds or refreshes rows in a DataSet from
  the ASE Server
• FillSchema() Adds a DataTable and configures
  the schema from the ASE Server
• Update() Calls INSERT, UPDATE, or DELETE
  statements for each inserted, updated, or deleted
  row in the DataSet

31
Coding Recommendations

• Have a separate Data Access Layer in your
  application
• If you do not want your application to be tied to
  a particular database
• stick to using ADO interfaces like IDbConnection,
  IDbTransaction etc
• Isolate factory code that creates vendor specific
  classes
• Use named parameters
• For executing stored procedures, use
  CommandType.StoredProcedure instead of using a
  SQL statement

32
Coding Recommendations

• //Factory code to create an AseConnection object
• IDbConnection myConn new AseConnection
• ("Server'ASHISHMW2K'Port5000User
  IDsaDatabasepubs2)
• //Use the provider agnostic interfaces instead of
  Provider dependent
• //classes
• IDbCommand myCommand myConn.CreateCommand()
• myCommand.CommandText "SELECT FROM
  Customers"
• IDataReader myReader myCommand.ExecuteReader()
• while (myReader.Read())
• Console.WriteLine("0\t1,
• myReader.GetString(0),
• myReader.GetString(1))

33
Support for Visual Studio .NET

• Supports Drag and Drop from Visual Studio .NET
  for
• AseConnection
• AseCommand
• AseDataAdapter
• Usage
• Drop objects on the form
• Configure their properties
• Code is generated

34
XML Support

• .NET Framework brings a tight integration between
  the XML classes and ADO.NET
• XML and ADO.NET technologies converge in the
  DataSet object
• DataSets are remoted as XML
• Strongly typed DataSets
• Use DataSet to Read/Write Schema and Data to XML

35
Product Roadmap

• Beta program starts August 18th, 2003
• General Availability Q4 2003

36
Where to get more information

• For Beta program
• Ask your technical support contact for the beta
  kit
• Register on the web at http//www.sybase.com/conne
  ctivity/adonetbeta
• For more technical documents on .NET and ADO.NET
  development http//msdn.microsoft.com/library/defa
  ult.asp?url/library/en-us/dnanchor/html/netdevanc
  hor.asp

37
Q A

• Questions ??