Data Classes On ADO.NET

Introduction

The DataAdapter class connects a DataSet to a data source for retrieving and updating the information. The DataAdapter class establishes this connection using the connection string. The data manipulation takes place using the SQL commands. The .NET data providers have their own DataAdapter with common functionalities, but existing in different namespaces. For example the adapter class defined for SQL database is SqlDataAdapter and it is defined in the System.Date.SqlClient namespace.

Properties and Methods of DataAdapter Class

AcceptChangesDuringUpdate

This property specifies or retrieves whether the AcceptChanges() method is called while the data source is being updated.

ContinueUpdateOnError

This property specifies or retrieves a value indicating whether to generate an exception. The exception is generated when an error occurs while updating a row.

FillLoadOption

This property specifies or retrieves a value of the LoadOption enumeration. This value determines how the SQLDataAdapter fills the DataTable using the object of the DbDataReader class. For example, the SqlDataAdapter can fill the DataTable without losing any changes made to the table.

MissingMappingAction

This property identified the action to be performed when the fetched data does not have a matching table or column.

MissingSchemaAction

This property identifies the action to be performed when the DataSet schema is not matching with the fetched data.

ExecuteBatch

This method executes a set of executable commands.

FillSchema

This method inserts a particular DataTable to the given DataSet.

ResetFillLoadOption

This method resets FillLoadOption to its default state. It also causes the Fill() method to honor AcceptChangesDuringFill.

SQLDataAdapter Class

The SqlDataAdapter class is used to link datasets to the SQL Server database. This class is a sealed class. This means this class cannot have a child class.

The property UpdateBatchSize specifies or retrieves the number of rows affected in each round-trip to the server. The following source code demonstrates how to update a row of a table in the database using the SqlDataAdapter class.

SqlConnection sqlconTransJournShipping=new SqlConnection(“Data Source=MYDATA\\SQLEXPRESS;Initial Catalog=TransJournShipping;IntegratedSecurity=”ASSPII”);
SqlconTransJournShipping.Open();
SqlDataAdapter sqldaSuppliers=new SqlDataAdapter(“Select * from Suppliers”,sqlconTransJourShipping);
SqlCommandBuilder sqlcombSupplier=new SqlCommandBulider(sqldaSuppliers);
DataSet dsetTransJournShipping=new DataSet();
sqldaSuppliesr.FillLoadOption=LoadOption.overwriteChanges;
sqldaSuppliers.Fill(dsetTransJournShipping.Tables[“Suppliers”].NewRow();
drowSuppliers[“SID”]=10;
drowSuppliers[“Name”]=”ABElectrodes”;
drowSuppliers[“City”]=”Noida”;
drowSuppliers[“MobNo”]=”5857584585”;
dsetTransJournShipping.Tables[“Suppliers”].Rows.Add(drowSuppliers);
sqldaSuppliers.Update(dsetTransJournShipping,”Suppliers”);

In this code, the SqlDataAdapter object is passed to the constructor of SqlCommandBulider class. This class generates commands to update the table in the database with the changes made to the DataSet. The FillLoadOption property is set to OverwriteChanges value defined in the LoadOption enumeration to overwrite any changes made. The DataSet is filled with the records of Supplier table. The NewRow() method of DataSet class is used to create a new row having four columns storing the ID, name,city and MobNo. This new row is added to the table in the DataSet . The newly created row is added to the database table using the Update() method of SqlDataAdapter class.

OleDbDataAdapter Class

The OleDbDataAdapter class is useful for connecting to multiple types of databases. If the developer is not aware of the kind of database to be used, the OleDbDataAdapter object can be used. This is because the object can connect to any kind of database. Further, if there are chances of frequently changing the database, the OleDbDataAdapter object is the best choice to maintain steady performance.

The following source code demonstrate how to use the RowUpdated event of the OleDbDataAdapter class.

OleDbConnection oledbconEmployees = new OleDbConnection(“Provider = Microsoft.Jet.OLEDB .4 .0; Data Source = Employees.mdb”);
oledbconEmployees.Open();
DataSet dSetEmployees = new DataSet();
OleDbDataAdapter oledbaEmployees = new OleDbDataAdapter(“SELECT * FROM Employees”, oledbconEmployees);
OleDbCommandBuilder oledbcomb = new OleDbCommandBuilder(oledbdaEmployees);
oledbdaEmployees.ContinueUpdateOnError = true;
oledbdaEmployees.AcceptChangesDuringFill = true;
oledbdaEmployees.Fill(destEmployees, ”Employees”);
dsetEmployees.Tables[“Employees”].Rows.RemoveAt(1);
oledbdaEmployees.Update(destEmployees, “Employees”);
private void oledbdaEmployees_RowUpdates(object sender, OleDbRowUpdatedEventArgs e) {
if (e.Status == UpdateStatus.ErrorOccurred) {
MessageBox.Show(“Error: “+e.Errors.Message);
}
}

In this source code , the OleDbDataAdapter object is passed to the constructor of OleDbCommandBuilder class. This class generates commands to update the table with the changes made to the DataSet. The ContinueUpdateOnError property is set to true. This means that when an error occurs while updating a row, the RowError property of that row is filled with error details. The AcceptChangesDuringFill property is set to true to accept any changes made. The DataSet object is filled with the record of Employees table present in Microsoft Access. Now the second row of the table is removed from the DataSet. The same row is deleted from the table in the database using the Update() method of the OleDbDataAdapter class. This triggers the RowUpdated event . If an error occurs while updating a row, the update for the row will be skipped and the remaining updates will take place. Finally, the error information is set in the RowError property of the row and is also displayed in the message box.

SqlDataReader Class

The main function of the SqlDataReader class is to read a stream of rows from a SQL Server database in a forward-only manner. The class object is created by calling the ExecuteReader() method of the SqlCommand class. The SqlConnection object associated with the SqlDataReader object cannot perform any other operation till the time it is being used by the SqlDataReader object. Few properties and methods of SqlDataReader class are,

Connection

This property retrieves the SqlConnection provided for the SqlDataReader.

Depth

This property retrieves a value, which indicates the nesting depth of the current row.

VisibleFieldCount

This property retrieves the number of fields, which are visible in the SqlDataReader.

GetDecimal

This property retrieves the value of a particular column as a Decimal object.

GetDataTypeName

This method retrieves the name of the source data type.

GetDateTime

This method retrieves the value of a particular column as a DateTime object.

GetOrdinal

This method retrieves the ordinal of a column when the column name is specified.

GetSqlValues

This method retrieves the attribute columns of the current row.

GetSqlXml

This method retrieves the value of a particular column as an XML value.

IsDBNull

Retrieves a value that identifies whether a column contains missing or non-existent values.

The source code demonstrate how to retrieve the order date of a particular order by selecting the ordered from the ComboBox.

SqlConnection sqlcon = new SqlConnection(“Data Source = MYDATA\\ SQLEXPRESS; Initial Catalog = TransJournShipping; IntegratedSecurity = ”ASSPII”);
TextBox txtOrderDate;
ComboBox cboOrderID;
SqlCommand sqlcomOrders;
SqlDataReader sqldreaderOrders;
cboOrderID = new ComboBox();
txtOrderDate = new TextBox();
sqlcon.Open();
sqlcomOrders = new SqlCommand(“SELECT * from Orders”, sqlcon);
sqldreaderOrders = sqlcomOrders.ExecuteReader();
while (sqldreaderOrders.Read()) {
if (!sqldreaderOrders.IsDBNull(0)) cboOrderID.Items.Add(sqkdreaderOrders.GetInt32(0));
}
private void cboOrderID_SelectedIndexChanged(object sender, EventArgs e) {
sqlcomOrders = new SqlCommand(“SELECT * FROM Orders WHERE OrderID = ”+cboOrderID.Text, sqlcon);
if (!sqldreaderOrders.IsClosed) sqldreaderOrders.Close();
sqldreaderOrders = sqlcomOrders.ExecuteReader());
sqldreaderOrders.Read();
if (!sqldreaderOrders.IsDBNull(3)) {
txtOrderDate.Text = sqldreaderOrders.getDateTime(3).ToShortDateString();
}
}

In this source code, an object of the SqlCommand class is created, namely, sqlcomOrders. It accepts two arguments, the SELECT statement and the SqlConnection object. An instance of SqlDataReader class is created using the ExecuteReader() method of SqlCommand object. In the while loop , the Read() method of the SqlDataReader class is used to move to the next record. The IsDBNull() method returns value indicating whether the first column of the first row is null. If not null, all the order IDs are retrieved and displayed in the combo box. When the user selects an order ID from the combo box, the SelectedIndexChanged event is raised. When this event is raised , the IsClosed property checks whether the SqlDataReader object is open. If it is Open, it is closed using the Close() method. To execute a new query with the SqlCommand object, the SqlDataReader object must be first closed and then repopulated. Therefore, the SqlDataReader is closed and then repopulated with the record, whose order ID is selected in the combo box. Using the Read(), IsDBNull() and GetDateTime() methods, the order date of the selected order ID is retrieved and displayed in the text box. The GetDateTime() method returns the date and time value of the fourth column, which is the column for order date.

OleDbDataReader Class

The main function of the OleDbDtatReader class is to read a stream of data from a particular data source in a forward-only manner. It is a sealed class. The OleDbDataReader class allows the user to view the changes that are being made to the data source by some another process. Few Properties and Methods of OleDbDataReader class are given as,

RecordAffected

This property retrieves the total number of rows that are inserted, updated, or deleted by the execution of the Transact-SQL statements.

GetData

It returns the OleDbDataReader object for specified column ordinal.

GetDataTypeName

This method retrieves the name of the source data type.

GetDateTime

This method retrieves the value of a particular column as a DateTime object.

GetFieldType

This method retrieves the Type representing the data type of the object.

GetSchemaTable

It returns a DataTable which shows the column metadata of the OleDbDataReader.

IsDBNull

Retrieves a value that identifies whether a column contains missing or non-existent values.

The following source code demonstrate how to retrieve values of an updated column in the table using the OleDbDataReader class.

TextBox txtSupplierID = new TextBox();
TextBox txtName = new TextBox();
OleDbCommand oledbcomSupplier = new OleDbCommand(“Select * from Suppliers”, oledbconSuppliers);
oledbconSuppliers.Open();
oleDbDataReader oledbdreaderSuppliers = oledbcomSupplier.ExecuteReader();
oledbcomSupplier = new OleDbCommand(“UPDATE Suppliers SET SupplierID = ’SUP637’ WHERE Name = ’AB Info’”, oledbconSuppliers) l
oledbcomSupplier.ExecuteNonQuery();
if (oledbreaderSuppliers.RecordAffected > 0) oledbdreaderSuppliers = oledbcomSupplier.ExecuteReader();
oledbdreaderSuppliers.Read();
if (!oledbdreaderSuppliers.IsDBNull(0) && !oledbdreaderSuppliers.IwsDBNull(1)) {
txtSupplierID.Text = oledbdreaderSuppliers.GetString(0);
txtName.Text = oledbreaderSuppliers.GetSring(1);
} else MessageBox.Show(“Columns contains Null Values..”, “Error”);

This code illustrates an OleDbDataReader object is created using the ExecuteReader() method of oleDbCommand object. This populates the OleDbDataReader object with the records of the Suppliers table. The OleDbCommand object is used to update the Suppliers table. The RecordsAffected property returns the number of rows affected after the update operation. If the affected rows are more than 0, the OleDbDataReader object is repopulated with the updated records. The Read() method is used to point to the next record. The IsDBNull method identifies whether the values in the first and second columns are null. If they are not null, the supplier ID and name are displayed in the text boxes using the GetString() method. Otherwise, an error message box is displayed stating that the columns contain null values.

Summary

The FillSchema() method of the DataAdapter class adds a DataTable in a DataSet. The ExecuteBatch() method of the SqlDataAdapter class executes a series of commands.

The Connection property retrieves the SqlConnection provided for the SqlDataReader. The GetSchemaTable() method returns a DataTable that describe the column metadata of the OleDbDataReader.