When it comes to deploy pages that are actually useful for ...



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 which make it easy for us to connect to data sources, access their data, display and alter it .Before we discuss in detail about 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.

[Page 407]:

is the name for a group of object class provided by .NET Framework for interacting with data in data stores. The 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 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 to bring data into your pages. Using 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 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 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 objects at our disposal when working with a dataset. They are:

• Dataset

• DataTable

• Data Adapter

• Data View

[pic]

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:

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:

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

< /body>

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)

mandTimeout = 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:

[pic]

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.

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

Reading data from the connection

Output:

Reading data from the connection

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

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

|First name | Lastname |

|Nancy |Davolio |

|Andrew |Fuller |

|Janet |Leverling |

|John |Hartford |

|Steven |Buchanan |

|Mike |jake |

|Luke |Tom |

How it works:

In the two strings declared in the program the first specifies the provider and database we want to use. The second one holds the description of the data we want to read from the database:

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;”

The code that follows is our objects, First we start with creating dataset which is used store our result set. This is so that we can retain the full structure of the data returned from the database:

Dim objdataset as new dataset ()

The next thing we need to do is to create a dataadapter that will allow the data from our database to be modified for our result set:

Dim objconnection as new oledbconnection (strconnection)

Dim objadapter as new oledbdataadapter (strsql, objconnection)

The data adapter created above will take two arguments:

• The query string that will define the exact set of data we want from the database

• The connection object

We now instruct the dataadapter to output all its data to the dataset object, and to give the newly created table the name “Employees”. It is important for this table to have a well-defined name since dataset object can contain multiple tables and create ad hoc relationships between them

Objadapter.fill (objdataset, “employees”)

lets us create various views of the information within dataset. This allows us, for example, to load several tables to a page, giving each its own set of columns and records as determined by various views. In our case we just want to view the entire Employees table as created in the last line:

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

Now we have the desired view of our data loaded safely in our dataset within the code, we can focus on displaying that on the page. The next line establishes the datagrid control should get us data from the dataview object; we then call the databind method, which is responsible for assigning data to the grid control and the result is displayed on the page using datagrid

Dgnamelist.datasource=objdataview

Dgnamelist.databind ()

References:

[1] Goode, Kauffman,Mike, Raybould, Beginning 1.0 with Visual Basic

.NET, Wrox Press Ltd, June 2002.

[2] Source Code:



[3] .NetDataAccessArchitecture

Guide.(

y/en-us/dnbda/html/daag.asp)

[4] DataSet Members :



us/cpguide/html/cpconcreatingdatasetprogrammatically.asp

Section1:

Short Answer Questions:

• Write a code to fill the dataset from any data source, create a dataview of the Tables in the dataset and bind it to a datagrid control to display the results?

• Discuss the problems that might occur when working with disconnected data?

• Write a .NET code that will built a data relation between two tables in the dataset.

• Name three non-database containers to which Objects can interact?

• What is the main difference between datareader and dataset .

Section2:

• Name the data access technology provided by .NET Framework to connect to data sources.

• For Access we use Jet provider. What does Jet refer to?

• Explain the functionality of dataadapter?

• The benefits of using disconnected data in are?

• What does Data view Represent?

Section3:

1) If you have two tables in your dataset with names “Table1” and “table1”. Which of the following Reference will produce an exception?

• a)Table1

• b)table1

• c)TABLE1

• d)None

2) Which Command property does commandbuilder object uses to work out for the sql statements for other commands

• a)SelectCommand

• b)Insert command

• c)Updatecommand

• d)Deletecommand

3) The connection string is made up of three parts which contains the information we need to connect to a data source. What does part two specify?

• a)Provider or Driver we want to use

• b)security information

• c)Database to use

• d)none

4) Dataset can contain:

• a)one table

• b)two table’s

• c)multiple table’s

• d)none

5) If we do not provide a name to the datatable while filling it into dataset from a data source. What name does it take?

• a)Newtable1

• b)Same name as source table

• c)produces an error

• d)none

Answers:

Section1:

1. 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

2 The Problems using disconnected data are:

• 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

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

3 Objdataset.Relations.Add ("CUSTOMERID", _

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

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

4 Desktop RDBMS Such as Access

File and directory systems such as windows FAT32

Non database files such as Excel

5 DataReader is limited to retrieving data based upon a single table, query, or stored procedure. The dataset on the other hand, has the advantage of being able to deal with multiple set of data.

Section2

1)

2) Jet refers to the data engine within access

3) The dataadapter modifies and passes results from the connection into the dataset

4) Efficiency, scalability

5) Data view represents a specific view of the data table held in the dataset

Section3:

• C

• A

• C

• C

• B

[pic]

-----------------------

Application

Data Set

Data Reader

Command Object

Connection Object

Managed Data Provider

Database

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download