ADO.NET: A quick revision

It is used for data manipulation from datasource. Data manipulation is done using XML internally.

ADO.NET Namespaces:

1. System.Data (For defining ADO.NET objects )
2. System.Data.Common (Contains common classes for System.Data.OleDb and System.Data.SqlClient namespaces)
3. System.Data.OleDb (For connecting to oracle ODBC)
4. System.Data.SqlClient (For connecting to sql server)
5. System.Data.SqlTypes (Contains SQL Server types)

ADO.NET Objects

1. Objects that manage and store data (DataSet, DataTable, DataRow, DataColumn, DataView, DataReader, DataAdapter).

2. Objects that connect with specific DataSource (Connection, Command).

1.  Objects that manage and store data (DataSet, DataTable, DataRow, DataColumn, DataView, DataReader, DataAdapter)

DATASET: DataSets use disconnected access to DataSource using DataAdapters. Theses contain DataTable, DataRow, DataColumn and DataRelation. We can use ReadXML, WriteXML, Merge, Clear, AcceptChanges, GetChanges, RejectChanges commands on DataSets. But there can be concurrency problems with datasets if more than two users are updating the same record at the same time in disconnected mode. When we use DataSets, we never fire update, delete and insert query directly on database, instead we post updated database.

Retrieve logic: DataSource -> Connection Object -> Command Object -> DataAdapter -> DataSet -> UI
Write Logic:  DataSource <- Connection Object <- Command Object <- DataAdapter <- DataSet <- UI

DATAREADER: DataReaders use connected and direct access to DataSource and no DataAdapter is required. These can be SqlDataReader and OleDbDataReader.

Retrieve logic: DataSource -> Connection Object -> Command Object -> DataReader -> UI
Write Logic:  DataSource <- Connection Object <- Command Object <- DataReader <- UI

DATAADAPTER: DataAdapters are the bridges between DataSet and Command Object. These can be SqlDataAdapter and OleDbDataAdapter. We can use Fill, FillSchema and Update commands on DataAdapers.

2. Objects that connect with specific DataSource (Connection, Command)

CONNECTION: These can be SqlConnection and OleDbConnection. ConnectionString is its basic property.

COMMAND: These can be SqlCommand and OleDbCommand. We can use ExecuteNonQuery, ExecuteReader, ExecuteScaler on it.

Code snippet using DataReader (Connected Access)

Making Connection:

myConnectionString = '';
sqlConnection myConnection = new sqlConnection();
myConnection.ConnectionString = myConnectionString;

or

myConnectionString = '';
sqlConnection myConnection = new sqlConnection(myConnectionString);

myConnection.Open();
myConnection.Close();

Making Command:

sqlCommand myCommand = new sqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = myQuery;

or

sqlCommand myCommand = new sqlCommand(myQuery, myConnection);

Making DataReader:

myConnection.Open();

sqlDataReader myReader = new sqlDataReader;
myReader = myCommand.ExecuteReader();  //For reading
myReader.Read(); //Read first row. For reading all rows, iterate it with while loop.
myReader.Close();

myConnection.Close();

anyInteger = myCommand.ExecuteNonQuery(); //For inserting, updating, deleting
anyInteger = myCommand.ExecuteScaler();

Code using DataSet(Disconnected Access)

Making Connection:

myConnectionString = '';
sqlConnection myConnection = new sqlConnection(myConnectionString);

myConnection.Open();
myConnection.Close();

Making Command:

sqlCommand myCommand = new sqlCommand(myQuery, myConnection);

Making DataAdapter:

DataSet myDataSet = new DataSet();
sqlDataAdapter myAdapter = new sqlDataAdapter(myCommand);
myAdapter.Fill(myDataSet, 'myTable');  //Reading

Updating DataSet and Posting:

DELETE A ROW: myDataSet.Tables['myTable'].Rows[2].Delete
ADD A ROW: myDataSet.Tables['myTable'].Rows.Add(myDataRow);

sqlCommandBuilder cb = new sqlCommandBuilder(myAdapter);

myNewAdapter = cb.DataAdapter();

anyIntegerRowAffected = myNewAdapter.Update(myDataSet, 'myTable');

How to create a new DataRow and adding it to DataTable

DataRow dr = new DataRow();
dr = myDataSet.Tables['myTable'].NewRow();
dr['ColumnName1'] = '';
dr['ColumnName2'] = '';
myDataSet.Tables['myTable'].Rows.Add(myDataRow);