ADO.NET

There are following steps to fill a dataset:

(i). Create a connection object.

(ii). Create an adapter by passing the string query and the connection object as parameters.

{iii). Create a new object of dataset.

(iv). Call the Fill method of the adapter and pass the dataset object.

We can check that some changes have been made to dataset since it was loaded?

There are following way to check that some changes have been made to dataset since it was loaded:

(i). GetChanges: gives the dataset that has changed since newly loaded or since Accept changes has been executed.

{ii}. HasChanges: this returns a status that tells if any changes have been made to the dataset since accept changes was executed.

How can we add/remove row’s in “DataTable” object of “DataSet”?

Using NewRow method we can add row's in a data table object of dataset.Remove method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’.

RemoveAt method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’ per the index specified in the DataTable.

The underlying database can be updated directly by passing SQL INSERT/UPDATE/ DELETE statements, or stored procedure calls, through to the managed provider. It We can also be updated using a DataSet.

The steps are:

1. Create and fill the DataSet with one or more DataTables
2. Call DataRow.BeginEdit on a DataRow
3. Make changes to the row’s data
4. Call DataRow.EndEdit
5. Call SqlDataAdapter.Update to update the underlying database
6. Call DataSet.AcceptChanges (or DataTable.AcceptChanges orDataRow.AcceptChanges)

To update the database directly, we can use the SqlCommand object, which allows us to execute SQL INSERT, UPDATE, and DELETE statements against a database.

Using System;
using System.Data;
using System.Data.SqlClient;
public class empdel {
public static void Main() {
SqlConnection con = new SqlConnection(
"server=(local)\computer;database=emp;trusted_connection=yes"
);
string sql = "DELETE FROM emp WHERE emp_name = 'Ashok'";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("{0} record(s) deleted.", i);
}
}

To load multiple tables in a DataSet

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter
             ("Emp", this.Connection);
da.SelectCommand.CommandType =CommandType.
                          StoredProcedure;
da.SelectCommand.Parameters.
    AddWithValue ("@EId", EId);
da.TableMappings.Add 
           ("Table", ds.xval.TableName);
da.Fill (ds); 

ADO.NET Code showing Dataset storing multiple tables.

DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dt3);
ds.Tables.Add(dt4);
ds.Tables.Add(dt5);
..................
.................
..................
ds.Tables.Add(dtn);;