Access data and schema information using the SADataAdapter object

The SADataAdapter allows you to view the entire result set by using the Fill method to fill a DataTable with the results from a query by binding the DataTable to the display grid.

Using the SADataAdapter, you can pass any string (SQL statement or stored procedure) that returns a result set. When you use the SADataAdapter, all the rows are fetched in one operation using a forward-only, read-only cursor. Once all the rows in the result set have been read, the cursor is closed. The SADataAdapter allows you to make changes to the DataTable. Once your changes are complete, you must reconnect to the database to apply the changes.

You can use the SADataAdapter object to retrieve a result set that is based on a join. However, you can only make changes (inserts, updates, or deletes) to data that is from a single table. You cannot update result sets that are based on joins.

Caution

Any changes you make to the DataTable are made while you are disconnected from the database. This means that your application does not have locks on these rows in the database. Your application must be designed to resolve any conflicts that may occur when changes from the DataTable are applied to the database if another user changes the data you are modifying before your changes are applied to the database.

C# SADataAdapter Fill example using a DataTable

The following example shows how to fill a DataTable using the SADataAdapter. It creates a new DataTable object named Results and a new SADataAdapter object. The SADataAdapter Fill method is used to fill the DataTable with the results of the query. The DataTable is then bound to the grid on the screen.

SAConnection conn = new SAConnection( "Data Source=SQL Anywhere 12 Demo" );
conn.Open();
DataTable dt = new DataTable("Results");
SADataAdapter da = new SADataAdapter("SELECT * FROM Employees", conn);
da.Fill(dt);
conn.Close();
dataGridView1.DataSource = dt;

C# SADataAdapter Fill example using a DataSet

The following example shows how to fill a DataTable using the SADataAdapter. It creates a new DataSet object and a new SADataAdapter object. The SADataAdapter Fill method is used to create a DataTable table named Results in the DataSet and then fill it with the results of the query. The Results DataTable is then bound to the grid on the screen.

SAConnection conn = new SAConnection( "Data Source=SQL Anywhere 12 Demo" );
conn.Open();
DataSet ds = new DataSet();
SADataAdapter da = new SADataAdapter("SELECT * FROM Employees", conn);
da.Fill(ds, "Results");
conn.Close();
dataGridView1.DataSource = ds.Tables["Results"];

C# SADataAdapter FillSchema example using a DataTable

The following example shows how to use the FillSchema method to set up a new DataTable object with the same schema as a result set. The Additions DataTable is then bound to the grid on the screen.

SAConnection conn = new SAConnection( "Data Source=SQL Anywhere 12 Demo" );
conn.Open();
SADataAdapter da = new SADataAdapter("SELECT * FROM Employees", conn);
DataTable schema = new DataTable("Additions");
da.FillSchema(schema, SchemaType.Source);
conn.Close();
dataGridView1.DataSource = schema;

C# SADataAdapter FillSchema example using a DataSet

The following example shows how to use the FillSchema method to set up a new DataTable object with the same schema as a result set. The DataTable is added to the DataSet using the Merge method. The Additions DataTable is then bound to the grid on the screen.