Data Access and Manipulation

With the SAP Sybase IQ .NET Data Provider, there are two ways you can access data:

• SACommand object – The SACommand object is the recommended way of accessing and manipulating data in .NET.

  The SACommand object allows you to execute SQL statements that retrieve or modify data directly from the database. Using the SACommand object, you can issue SQL statements and call stored procedures directly against the database.

  Within an SACommand object, an SADataReader is used to return read-only result sets from a query or stored procedure. The SADataReader returns only one row at a time, but this does not degrade performance because the SAP Sybase IQ client-side libraries use prefetch buffering to prefetch several rows at a time.

  Using the SACommand object allows you to group your changes into transactions rather than operating in autocommit mode. When you use the SATransaction object, locks are placed on the rows so that other users cannot modify them.

• SADataAdapter object – The SADataAdapter object retrieves the entire result set into a DataSet. A DataSet is a disconnected store for data that is retrieved from a database. You can then edit the data in the DataSet and when you are finished, the SADataAdapter object updates the database with the changes made to the DataSet. When you use the SADataAdapter, there is no way to prevent other users from modifying the rows in your DataSet. You need to include logic within your application to resolve any conflicts that may occur.

There is no performance impact from using the SADataReader within an SACommand object to fetch rows from the database rather than the SADataAdapter object.

• SACommand: Fetch Data Using ExecuteReader and ExecuteScalar
  The SACommand object allows you to execute a SQL statement or call a stored procedure against an SAP Sybase IQ database. You can use any of the following methods to retrieve data from the database:
• SACommand: Fetch Result Set Schema Using GetSchemaTable
  You can obtain schema information about columns in a result set.
• SACommand: Insert, Delete, and Update Rows Using ExecuteNonQuery
  To perform an insert, update, or delete with the SACommand object, use the ExecuteNonQuery function. The ExecuteNonQuery function issues a query (SQL statement or stored procedure) that does not return a result set.
• SACommand: Retrieve Primary Key Values for Newly Inserted Rows
  If the table you are updating has an autoincremented primary key, uses UUIDs, or if the primary key comes from a primary key pool, you can use a stored procedure to obtain the primary key values generated by the data source.
• SADataAdapter: Overview
  The SADataAdapter retrieves a result set into a DataTable. A DataSet is a collection of tables (DataTables) and the relationships and constraints between those tables. The DataSet is built into the .NET Framework, and is independent of the Data Provider used to connect to your database.
• SADataAdapter: Fetch Data into a DataTable Using Fill
  The SADataAdapter allows you to view a result set by using the Fill method to fill a DataTable with the results from a query and then binding the DataTable to a display grid.
• SADataAdapter: Format a DataTable Using FillSchema
  The SADataAdapter allows you to configure the schema of a DataTable to match that of a specific query using the FillSchema method. The attributes of the columns in the DataTable will match those of the SelectCommand of the SADataAdapter object. Unlike the Fill method, no rows are stored in the DataTable.
• SADataAdapter: Insert Rows using Update
  An example showing how to use the Update method of SADataAdapter to add rows to a table.
• SADataAdapter: Delete Rows Using Update
  An example showing how to use the Update method of SADataAdapter to delete rows from a table.
• SADataAdapter: Update Rows using Update
  An example showing how to use the Update method of SADataAdapter to update rows in a table.
• SADataAdapter: Retrieve Primary Key Values for Newly Inserted Rows
  If the table you are updating has an autoincremented primary key, uses UUIDs, or if the primary key comes from a primary key pool, you can use a stored procedure to obtain the primary key values generated by the data source.
• BLOBs
  When fetching long string values or binary data, there are methods that you can use to fetch the data in pieces. For binary data, use the GetBytes method, and for string data, use the GetChars method. Otherwise, BLOB data is treated in the same manner as any other data you fetch from the database.
• Time Values
  The .NET Framework does not have a Time structure. To fetch time values from SAP Sybase IQ, you must use the GetTimeSpan method. This method returns the data as a .NET Framework TimeSpan object.