When It Comes to Deploy Pages That Are Actually Useful for a Business We Need to Be Able

ADO.NET Objects

(DataSet, DataAdapter & Data view)

When it comes to deploy pages that are actually useful for a business we need to be able to work with the data .Any practical work on the Web will involve reading and writing to various data stores. The .Net Framework provides a set of data access technologies called ADO.NET which make it easy for us to connect to data sources, access their data, display and alter it .Before we discuss in detail about ADO.NET we are going to discuss few things about database.

A Database is a data store which is collection of data that’s been organized in a way that we can easily access and manipulate it contents. The Relational data base management system provides software that’s used to store, retrieve and modify data in a database.

ADO.NET [Page 407]:

ADO.NET is the name for a group of object class provided by .NET Framework for interacting with data in data stores. The ADO.NET object class is not only used to interact between data stored in database but also stored in non-database containers like

• Desktop RDBMS Such as Access
• File and directory systems such as windows FAT32
• Non database files such as Excel
• Comma-delimited or fixed length text files
• XML-Based data
• Microsoft Exchange Server 2000 data such as e-mail

The most and out standing feature of ADO.NET is that, we can write drivers, providers and adapters for the data source. If your database provides permission to use single view of data .Even without having access to the rest of the data source we can still use ADO.NET to bring data into your ASP.NET pages. Using ADO.NET we are actually working on disconnected data .i.e. when a visitor requests for data then a connection is made and the data is transferred, after which the connection is closed .The visitor after getting the data from the data source works on the data and manipulates but the changes are not taken place in the data source to update this changes in the data store we need to open the connection one more time. If we didn’t use this process then the connection need to be present until the visitor want to disconnect it .Usually there are thousands of users requesting for connection concurrently .Keeping a connection until his session expires is expensive .Using disconnected data makes our application more efficient and will also provide it the ability to handle greater work loads.

There are some Problems using disconnected data.

1) The changes we make are to the disconnected data and not to the original data source, so we can not get the changes back into the data source

2) If two people want to update same data, at the same time then the first updated data is overwritten by secondly updated data

The first problem can be solved by updating the changes made on the disconnected data in the datasource.The second problem is Far more complex.

Managed Providers [Page 408]:

To get data or to interact with data source we need some way to talk to data source. The data provider is like a translator which can translate things to make the communication possible between two groups which use different languages. In ADO we have OLEDB providers and ODBC drivers for communicating with database. In ADO.NET we have Managed providers to do this job for us which conform to .NET standard for use of memory and isolation from other processes

The Two managed provider in .NET are:

• Managed provider for SQL Server
• Managed provider for OLEDB

Flow of Data from Data source to ASP.NET page[409]:

Database: Data base is a storage device where you can store all your data .Most commonly a relational data base like SQL Server, Oracle or Access acts like a data source

Managed Data Provider: The Managed providers as discussed above are going to facilitate the conversing with the data base

Connection Object: The connection object is used to connect to a data source. It represents the actual connection between the data source and data consumer. The connection string is made up of three parts which contains the information we need to connect to a data source.

• The first part specifies the kind of provider or driver we want to use
• The second specifies which database to use
• The last string usually contains security information

We often come across the following connection strings:

• Access
• SQL Server
• Managed SQL-Server

Connection Strings[Page 410]:

Access:

• “provider=Microsoft.Jet.OLEDB.4.0;data source=MyDrive:Mypath/File.mcb”

Microsoft SQL Server:

• “Provider=SQLOLEDB.1;server=myServername;

Database=Mydatabase; initial catalog=Mycatalog;

Uid=Myuserid; pwd=Mypassword”

ManagedSQLServer:

• “server=Myservername;database=Mydatabase;uid=Muserid;pwd=Mypassword”

DataSet[425]:

The dataset represents the data in the database, and unlike the data reader it can hold several tables and relationships between them.

We have four ADO.NET objects at our disposal when working with a dataset. They are:

• Dataset
• DataTable
• Data Adapter
• Data View

The flow of data is similar to the datareader. We get out the data from a datasource by using a managed data provider .But in this case the data flows out of data connection through the dataadapter which modifies the formatting of the data which fits in to .NET Dataset. From a dataset it is possible to build a dataview that provides a specific subset of data. Last, we display the data in the HTML section using a datagrid

Dataset: The central object we deal with is the Dataset, which can contain multiple tables and establish ad hoc relationships between them. These relationships associate a row in one table with another row in a different table.

Creating a dataset[4]:

An instance of data set is created by calling dataset constructor. If we don’t specify the name for the dataset, the name of the dataset is set to “NewDataSet”

Code for creating a new dataset:

Dim custDS As DataSet = New DataSet("CustomerOrders")

We have three ways to create a new dataset that is:

1) We can create exact copy of the Dataset .The exact copy includes schema, data, row state information, and row versions.

To create an exact copy of dataset which includes both data and schema we need to use the copy method of the dataset .The code below explains how to create an exact copy of the dataset

Dim copyDS As DataSet = custDS.Copy ()

2) Using second method we can create a Dataset that can create exact schema of the dataset you want but can only copy rows that have been modified .To create such copy of a Dataset we use Getchanges method of the dataset which will return all the rows to which the changes have been made. We can also use Getchanges to return only rows with specific row states by passing a Datarowstate value when calling Getchanges.The code below explains both Getchanges and Datarowstate with getchanges

Copy all changes.

Dim changeDS As DataSet = custDS.GetChanges ()

Copy only new rows.

Dim addedDS As DataSet = custDS.GetChanges (DataRowState.Added)

3) The third method is to copy the schema or structure of the dataset in this method the data present in the dataset is not copied. To create this type of dataset we use clone method of the dataset. The existing rows in the dataset can be added to the clone dataset by using Importrow method of the datatable.

ImportRow will add data, row version, and row state information to the specified table. The data in the column’s for which the column name matches and data type is compatible will be added .The following code explains how to create a clone of a dataset then add the rows from the original Dataset to the customer table in the clone dataset for customers whose country is America

Dim custGermanyDS As DataSet = custDS.Clone ()

Dim copyRows () As DataRow = custDS.Tables ("Customers").Select ("Country = 'America'")

Dim custTable As DataTable = custGermanyDS.Tables ("Customers")

Dim copyRow As DataRow

For Each copyRow In copyRows

custTable.ImportRow (copyRow)

Next

Adding a Datatable to a dataset:

ADO.NET facilitates us to create a datatable objects and allow us to add them to the existing dataset. We also have the provision to assign primary key and using this we can set constraint information for a datatable, which are added to the column collection of the datatable

The example below builds a dataset and adds new datatable object to the dataset and then adds three datacolumn objects to the datatable. Finally in the code we set one column as primary key column

Dim custDS As DataSet = New DataSet("CustomerOrders")

Dim ordersTable As DataTable = custDS.Tables.Add("Orders")

Dim pkCol As DataColumn = ordersTable.Columns.Add("OrderID", Type.GetType("System.Int32"))

ordersTable.Columns.Add("OrderQuantity", Type.GetType("System.Int32"))

ordersTable.Columns.Add("CompanyName", Type.GetType("System.String"))

ordersTable.PrimaryKey = New DataColumn() {pkCol}

While referring to a table or relations in a dataset we need to be very careful

Because references by names to tables and relations in a dataset are case sensitive. If we have two or more tables or relations with same name, but differ in case .i.e. if we have two tables with names Table1 and table1 then while referring to one of the table we need to see that the case exactly matches other wise exception occurs. The case sensitivity does not apply if only one table or relation exists with a particular name. You can reference the object by name using any case and then no exception is thrown.

The case sensitivity does not affect the behavior. The case sensitivity affects data in the dataset and affects sorting, searching, and filtering and so on. The relations and references in the dataset are not affected by case sensitivity.

Adding Relations between tables:

Adding a relation to a dataset will automatically create a unique constraint in the parent table and a foreign constraint in the child table. The code below creates a data relation with two datatable objects in a dataset. Both the data tables in the dataset have a column named custid, which serves as a link between the two datatable objects. The example below adds a single relation between the tables. The first argument specifies the name of datarelation being created. The second argument sets the parent datacolumn and the third argument sets the child Datacolumn.

custDS.Relations.Add ("CustOrders", _

custDS.Tables ("Customers").Columns ("CustID"), _

custDS.Tables ("Orders").Columns ("CustID"))

Dataset and Datatables:

The datareader is limited to retrieving data based upon a single table; query or stored procedure .The dataset on the other hand had the advantage of being able to deal with multiple set of data. The code below shows that the dataset can handle multiple set of data:

<script language=”vb” runat=”server”>

Sub page_load ()

Dim strConnection as String = “Provider=Microsoft.Jet.OLEDB.4.0;”

strConnection += “Data Source=C:\begASPNET\ch12\Northwind.mdb”

Data_src.text = Strconnection

Dim strsql as string = “Select Firstname, Last name from employees;”

Dim objdataset as new dataset ()

Dim objconnection as oledbconnection

Dim objadapter as oledbdataadapter

Dim objconnection as new oledbconnection (strconnection)

Dim objadapter as new oledbdataadapter (strsql, objconnection)

Objadapter.fill (objdataset, “employees”)

Dgnamelist.datasource=objdataset.tables (“Employees”).Defaultview

Dgnamelist.databind ()

Dim strsql as string = “Select categoryname, Description from categories;”

Dim objadapter as new oledbdataadapter (strsql, objconnection)

Objadapter.fill (objdataset, “categories”)

Dgnamelist.datasource=objdataset.tables (“categories”).Defaultview

Dgnamelist.databind ()

End sub

</script>

<html>

<body>

<asp: Datagrid id=”dgnamelist” runat=”server” />

<asp: Datagrid id=”dgcategorylist” runat=”server”/>

< /body>

</html>

The important point in the above example is:

Objadapter.fill (objdataset, “employees”)

.

.

Objadapter.fill (objdataset, “categories”)

In the above example we are retrieving two tables from the database and storing them in a single dataset.

We are providing different names to the datatable if the name is not provided then the name used will be that of the source table. The reason we get it name is that a dataset can contain more than one datatable.

Each datatable is held as part of the table collection, which allows us to store multiple sets of data within a dataset.

Data Adapter[452]:

This modifies and passes results from the connection into the data set. The data adapter .fill method copies the data into the data set, and the data adapter. Update method copies the data in the data set back into the data source. Well the command is designed to run a command, and the dataadapter is designed to provide a storage space for multiple commands, which provide two way interaction between actual data store and the dataset .

The following code example creates an instance of a DataAdapter that uses a Connection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and Connection arguments passed to the DataAdapter constructor are used to create the SelectCommand property of the DataAdapter. If your DataTable maps to or is generated from a single database table, you can take advantage of the CommandBuilder object to automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter.

Dim nwindConn As OleDbConnection = New OleDbConnection ("Provider=SQLOLEDB; Data Source=localhost;" & _

"Integrated Security=SSPI; InitialCatalog=northwind")

Dim selectCMD As OleDbCommand = New OleDbCommand ("SELECT CustomerID, CompanyName FROM Customers", nwindConn)

selectCMD.CommandTimeout = 30

Dim custDA As OleDbDataAdapter = New OleDbDataAdapter

custDA.SelectCommand = selectCMD

Dim custDS As DataSet = New DataSet

custDA.Fill (custDS, "Customers")

CommandBuilder Object:

The command Builder object generates the commands for updates, insertions and deletions. It uses the select command property to work out for what the sql statements should be for other commands

The example below explains the usage of Commandbuilder:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim strConnection As String

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objConnection As OleDb.OleDbConnection

Dim objAdapter As OleDb.OleDbDataAdapter

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("emp.mdb")

strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees"

objConnection = New OleDb.OleDbConnection(strConnection)

objAdapter = New OleDb.OleDbDataAdapter(strSQL, objConnection)

objAdapter.Fill(objDataSet, "Employees")

dgNameList1.DataSource = objDataSet.Tables("Employees").DefaultView

dgNameList1.DataBind()

Dim objTable As DataTable

Dim objNewRow As DataRow

objTable = objDataSet.Tables("Employees")

objNewRow = objTable.NewRow()

objNewRow("FirstName") = "Norman"

objNewRow("LastName") = "Blake"

objTable.Rows.Add(objNewRow)

objNewRow = objTable.NewRow()

objNewRow("FirstName") = "Kasey"

objNewRow("LastName") = "Chambers"

objTable.Rows.Add(objNewRow)

dgNameList2.DataSource = objTable.DefaultView

dgNameList2.DataBind()

Dim objRow As DataRow

objRow = objTable.Rows(3)

objRow("FirstName") = "John"

objRow("LastName") = "Hartford"

dgNameList3.DataSource = objTable.DefaultView

dgNameList3.DataBind()

objTable.Rows(objTable.Rows.Count - 2).Delete()

dgNameList4.DataSource = objTable.DefaultView

dgNameList4.DataBind()

Dim objBuilder As OleDb.OleDbCommandBuilder

objBuilder = New OleDb.OleDbCommandBuilder(objAdapter)

objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()

objAdapter.InsertCommand = objBuilder.GetInsertCommand()

objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()

objAdapter.Update(objDataSet, "Employees")

strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees"

objConnection.Open()

Dim objCmd As New OleDb.OleDbCommand(strSQL, objConnection)

dgUpd.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

dgUpd.DataBind()

End Sub

Output:

Explanation:

The top three and bottom first datagrid output shows the changes with in the Dataset, before these changes are sent back to the data store. In the above example we actually add two rows, Which allow us to delete one of them. If we delete any existing record we need to delete all the records related to this record in other tables.

The other thing we need to notice is there is no number in the EmployeeID field for the new rows. This is because this field is an auto number field in the database, and it’s the database that generates this number. we haven’t yet updated the database there’s no number. Once we have done the updates to the dataset, after updating into the database the EmployeeID for the newly inserted record is appeared

Data View[Page425]: This represents a specific view of the data table held in the data set. It produces a description of records and columns you want to read from the entire data set.

<script language=”vb” runat=”server”>

Sub page_load ()

Dim strConnection as String = “Provider=Microsoft.Jet.OLEDB.4.0;”

strConnection += “Data Source=C:\begASPNET\ch12\Northwind.mdb”

Data_src.text = Strconnection

Dim strsql as string = “Select Firstname, Last name from employees;”

Dim objdataset as new dataset ()

Dim objconnection as new oledbconnection (strconnection)

Dim objadapter as new oledbdataadapter (strsql, objconnection)

Objadapter.fill (objdataset, “employees”)

Dim objdataview as new dataview (objdataset.tables (“employees”))

Dgnamelist.datasource=objdataview

Dgnamelist.databind ()

End sub

</script>

<html>

<body>

<h4>Reading data from the connection</h4>

<asp: datagrid id=”dgnamelist” runat=”server” />

</body>

</html>

Output:

Reading data from the connection

Provider=Microsoft.Jet.OLEDB.4.o; Data

Source=C:\begASPNET\ch12\Northwind.mdb to the datagrid

How it works: