Introduction to XML



CPAN 660

Lecture #9: Active Data Object .NET

is a group of classes and interfaces provided by the .NET framework to interact with any data source. The data source can be RDMS such as Oracle, Microsoft SQL Server, DB2 or MySQL. The data source can be File and directory systems, Excel files, or XML files. In fact and the .NET framework provide native support for XML.

works with a disconnected set of data. After the gets the data from the source data, it closes the connection. This improves the application efficiency and scalability.

In order to talk to a data source, we need a data provider. The following data provider namespaces are included in the :

• System.Data.SqlClient: A provider specific for connecting to Microsoft SQL Server.

• System.Data.OleDb: A provider for connecting to any data source with an available OLEDB provider.

• System.Data.Odbc: A provider for connecting to any data source that has an available ODBC driver.

• System.Data.OracleClient: A provider for connecting to Oracle database server.

Data providers consist of many objects. The important ones are:

• Connection: Used to connect to a data source. The connection string specifies the required information to connect to the data source. It contains information such as the data provider used to make the connection, the name of the data source, and security information.

The following connection string can be used to connect to Microsoft SQL server:

con.ConnectionString = "workstation id=HUCOMP;packet size=4096;user id=sa;password=mypassword;data source=hucomp;persist security info=False"

The following connection string can be used to connect to Oracle database SQL server:

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

Where orcl in the name of the Local Service Name configured using Oracle Net Configuration Assistance tool .

The following connection string can be used to connect to Microsoft Access database:

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=c:/test.mdb"

• Command: Represents an SQL statement or stored procedure to execute against a database.

• DataAdapter: Represents a bridge between a Data set and a data provider object and is used to retrieve and save data. A Data Set is an in-memory cash of data. It exposes a hierarchical object model of tables, rows, and columns with the constraints and relationships defined for the dataset.

• DataReader: Provides a way of reading a forward-only stream of data rows from a data source. Data reader is limited to single table, view or stored procedure. The object can be constructed by calling ExecuteReader method of the command object. Data retrieved using the DataReader cannot be modified.

• CommandBuilder: Automatically generates single-table commands used to save changes made to a Data Set to the database.

The namespace System.Data contains classes that constitute the architecture. Some of these objects are listed below:

• DataTable: Represents one table within a DataSet.

• DataView: Represents a view of data in a DataTable object.

• DataRow: Represents a row of data in a DataTable .

• DataColumn: Represents the schema of a column in a DataTable.

• Constraint: Represents a constraint that can be enforced on one or more DataColumn objects.

• DataRelation: Represents a parent/child relationship between two DataTable objects.

The following diagram describes the architecture of the :

Below we describe how to work with a data source using the DataAdapter along with the DataSet objects:

• To retrieve data from a data source, we can use the DataAdapter to populate a DataSet object. We can bind data in a DataSet to data web control such as: DataGrid, Repeater or DataList.

• To add a new record to the data source, add a new row to the DataTable object in the DataSet, then call update method of the DataAdapter. For update method to work, we need to provide the InsertCommand property of the command object.

• To update data, assign new value to the item in the DataRow object we want to change, then call update method of the DataAdapter. For update method to work, we need to provide the UpdateCommand property of the command object.

• To delete a row from the data source, we can call delete method of the DataRow object, and then call update method of the DataAdapter object. For update method to work, we need to provide the DeleteCommand property of the command object.

We can use the CommandBuidler object to create the InsertCommand, UpdateCommand, and DeleteCommand properties of the Command object based on a SelectCommand. Notice that if you do not call update method, changes will be made to the DataSet only and not to the original data source.

As an alternative approach, we can use the Command object to issue direct SQL commands against the data source. To query a data source, we call the ExecuteReader method of the Command object. The result of this method is placed in a DataReader object. To execute insert, update, or delete commands, we use ExecuteNonQuery method of the Command object.

The ExecuteScalar method of the Command object Executes a query, and returns the first column of the first row in the result set returned by the query as a .NET Framework data type. Extra columns or rows are ignored.

Installing Microsoft SQL Server Desktop Engine:

Perform the following to install SQL server desktop engine:

• Download SQL server desktop engine from

• Run the self extracted file MSDERelA.exe. By Default, it will be extracted to the folder c:/MSDERe1A

• Read the documentation for software and hardware requirements.

• Run the setup by issuing the following command:

C:\MSDERelA> setup SAPWD="muthana" SECURITYMODE=SQL

According to the documentation, we can install up to 16 copies (instances) of the engine on the same machine. One instance has no instance name and is called the default instance. The other instances must have an instance name and are called the name instances. The instance name has the format computer name\instance name

The parameter SAPWD specifies a password for the engine administrator (sa).

The parameter SECURITYMODE=SQL specifies that the engine instance can support both windows and SQL authentications.

• Restart the computer, for the engine to start.

Once you start Visual and view the Server Explorer, you will see the new instance listed under SQL Servers node:

[pic]

We can use visual studio to perform tasks such as : creating a new database, creating and managing database table. We can create and execute SQL commands to perform such tasks.

Installing and configuring Oracle Data Provider for .NET

In this section we will setup a .NET connection to Oracle database server. Our Oracle database is installed under sunerv1.humberc.on.ca. The name of this database is ORCL.

• Download Oracle Data Provider for .NET from

• Execute the installer file odbc_net.exe

• Configure a Service name for your oracle database by performing the following steps.

1- From the start menu ,select Net Configuration Assistant under Configuration and Migration tools of the Oracle Data provider for .NET

2- Select Local Net Service Name Configuration and click Next button.

[pic]

3- Select Add option and click Next button

[pic]

4- Select Oracle 8i or later database service option and click Next button

[pic]

5- Enter orcl in the Service Name text box ( the name of the database under sunserv1) and click Next button

[pic]

6- Select TCP from the protocols list and click Next button

[pic]

7- Enter sunserv1.humberc.on.ca in the Host Name and click Next button

[pic]

8- Select No, do not test option and click Next button

[pic]

9- Enter the name of your choice in the Net Service Name textbox and click Next button. On my machine I gave it the name Sunserv

[pic]

10- Select No option and click Next button

[pic]

11- Confirm the completion of the configuration by clicking Next button

[pic]

12- Click Finish button to exist the configuration tool

[pic]

In order to create a connection to Oracle from Visual , perform the following steps:

• Start Visual Studio .NET and select Server Explorer from the view menu item to view the server explorer window:

• Right click Data Connection and select Add Connection from the popup window

[pic]

• From the Provider tab select Microsoft OLE DB Provider for Oracle and click Next button

[pic]

• Enter the service name in the server name textbox. Also you have to enter your user name and password under the Oracle database server on sunserv1.

[pic]

• Click Test Connection to test the configuration and click OK button. You will be asked to re enter your id and password.

Once you have the connection setup, you can simply drag drop it on the Web Form. An Object of type System.Data.OracleClient.OracleConnection will be created for you.

If you want to create a connection to Oracle database server programmatically, perform the following:

Add the following import statement at the top of your code:

Imports System.Data.OracleClient

Create a connection object as shown below:

Dim myCOnnection As New OracleClient.OracleConnection

myCOnnection.ConnectionString = "user id=abdullah;data source=sunserv;password=oracle"

The Namespace System.Data.OracleClient is deployed in global assembly cash when the Oracle Data provider for .NET is installed. We can refer to this namespace in our application by using add tag to the application configuration file (web.config) as shown below:

Notice that we are referencing another namespace called OracleDataAccess in the configuration file. This is another namespace provided by Oracle for connecting to Oracle database server.

To find the Version, PublicKeyToken and the other attributes can be obtained using the .NET Framework utility gacutil.exe as shown below:

Gacutil –l System.Data.OracleClient

Notice that the value for the PublicKeyToken and the other attributes may be different from machine to another.

As an alternative, we can copy the file System.Data.OracleClient.dll from the folder C:\WINDOWS\\Framework\v1.1.4322 to a folder called

bin under the application root .

Examples:

Following is the configuration file (web.config) to be used with the examples that connect to Oracle Database server:

Ex1: OracleEx.aspx

In this example we will test our connection to Oracle Database Server on Sunserv1. We will use the ExecuteScalar method of the Command object to retrieve the total number of customers in the Customers table.

First we create the user interface for this example:

Then we create the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

'Imports Oracle.DataAccess

Imports System.Data.OracleClient

Public Class OracleEx:Inherits Page

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

con.Open()

Dim cmd As New System.Data.OracleClient.OracleCommand

' an alternative apprach

' Dim con as new Oracle.DataAccess.Client.OracleConnection()

' Dim cmd As Oracle.DataAccess.Client.OracleCommand

' con.Open()

cmd = con.CreateCommand

mandType = CommandType.Text

mandText = "Select count(*) from customers"

Dim res As String

res = cmd.ExecuteScalar()

Response.Write(res.ToString())

con.Close()

End Sub

End Class

Ex2:SQLServerEx.aspx

In this example we will do the same as in example 1, except that we will be connecting to SQL server instead of Oracle Database server. Following is the user interface for this example:

And following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

Imports System.Data.SqlClient

Public Class SQLServerEx:Inherits Page

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

Dim con As New System.Data.SqlClient.SqlConnection

con.ConnectionString = "workstation id=HUCOMP;packet size=4096;user id=sa;password=muthana;data source=hucomp;persist security info=False"

con.Open()

Dim cmd As New System.Data.SqlClient.SqlCommand

cmd = con.CreateCommand

mandType = CommandType.Text

mandText = "Select count(*) from customers"

Dim res As String

res = cmd.ExecuteScalar()

Response.Write(res.ToString())

con.Close()

End Sub

End Class

Ex3:DataSetEx.aspx

In this example we will query records from customers table under Oracle database server and populate a DataSet object. After that we will use the DataTable object and the DataRow object to navigate through the records in the DataSet. Following is the user interface for this example:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

'Imports Oracle.DataAccess

Imports System.Data.OracleClient

Public Class DataSetEx:Inherits Page

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

con.Open()

Dim cmd = con.CreateCommand()

mandType = CommandType.Text

mandText = "Select * from customers"

Dim da As New OracleClient.OracleDataAdapter

da.SelectCommand=cmd

Dim ds As New DataSet

da.Fill(ds, "customers")

Dim add() = ds.Tables("Customers").Select("CustID >= '1'")

Dim outp As String = ""

Dim a As DataRow

For Each a In add

outp = outp & a(0) & a(1) & ""

Next

Response.Write(outp)

' MessageBox.Show(outp);

con.Close()

End Sub

End Class

Ex4:ExecuteReaderEx.aspx

In this example we will query customers table under Oracle database server using the DataReader object. Following is the user interface for this example:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

'Imports Oracle.DataAccess

Imports System.Data.OracleClient

Imports Microsoft.VisualBasic

Public Class ExecuteReaderEx:Inherits Page

Protected WithEvents txtOutput As System.Web.UI.WebControls.TextBox

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

con.Open()

Dim cmd = con.CreateCommand()

mandType = CommandType.Text

mandText = "Select * from customers"

Dim dr As OracleClient.OracleDataReader

dr = cmd.ExecuteReader()

Dim i As Integer

Do While dr.Read()

For i = 0 To dr.FieldCount - 1

Dim t As Type

t = dr.GetFieldType(i)

' If dr.GetFieldType(i).ToString() = "System.String" Then

txtOutput.Text = txtOutput.Text & dr.GetValue(i)

' End If

Next i

txtOutput.Text = txtOutput.Text & ControlChars.CrLf

Loop

con.Close()

End Sub

End Class

Ex5:DataGridEx.aspx

In this example we will bind data from a DataSet to a DataGrid control. Also we will be working with the DataTable, DataRow and DataColumn objects to navigate through records in the DataSet. The example queries the customers table under Oracle database server. Following is the user interface for this example:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

'Imports Oracle.DataAccess

Imports System.Data.OracleClient

Imports Microsoft.VisualBasic

Public Class DataGridEx:Inherits Page

Protected WithEvents txtOutput As System.Web.UI.WebControls.TextBox

Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

con.Open()

Dim cmd = con.CreateCommand()

mandType = CommandType.Text

mandText = "Select * from customers"

Dim da As New OracleClient.OracleDataAdapter

da.SelectCommand = cmd

Dim ds As New DataSet

da.Fill(ds, "customers")

DataGrid1.DataSource = ds

DataGrid1.DataMember = "Customers"

DataGrid1.DataBind()

Dim dt As DataTable

For Each dt In ds.Tables

txtOutput.Text += dt.TableName & ControlChars.CrLf

Dim dr As DataRow

For Each dr In dt.Rows

Dim dc As DataColumn

For Each dc In dt.Columns

txtOutput.Text = txtOutput.Text & dr(dc) & ControlChars.CrLf

Next

Next

Next

con.Close()

End Sub

End Class

Ex6:DataListEx.aspx

In this example we will bind data stored in a DataSet to a DataList control. Following is the user interface for this example:

ID:

Name:

Representative:

Address:

Phone:

Debt Amount:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

Public Class DataListEx:Inherits Page

Protected WithEvents DataList1 As System.Web.UI.WebControls.DataList

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

Dim ds As New DataSet

ds.ReadXml("c://test.xml")

DataList1.DataSource = ds

DataList1.DataBind()

End Sub

End Class

Ex7: XMLDBEX.aspx

This example shows how to create XML document from a DataSet. Following is the user interface for this example:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

'Imports Oracle.DataAccess

Imports System.Data.OracleClient

Public Class XMLDBEx:Inherits Page

Protected WithEvents txtXml As System.Web.UI.WebControls.TextBox

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

con.Open()

Dim cmd As New System.Data.OracleClient.OracleCommand

' an alternative approach

' Dim con as new Oracle.DataAccess.Client.OracleConnection()

' Dim cmd As Oracle.DataAccess.Client.OracleCommand

' con.Open()

cmd = con.CreateCommand

mandType = CommandType.Text

mandText = "Select * from customers"

Dim da As New OracleClient.OracleDataAdapter

da.SelectCommand = cmd

Dim ds As New DataSet

da.Fill(ds, "customers")

Dim doc As New XmlDocument

txtXml.Text = ds.GetXml()

ds.WriteXml("c:\\test.xml")

con.Close()

End Sub

End Class

Ex8: SQLEx.aspx

In this example we will manipulate the customers table under Oracle database server using direct SQL statements. Following is the user interface for this example:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

Imports System.Drawing

Imports System.Data.OracleClient

Public Class SQLEx:Inherits Page

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

Dim cmd As New System.Data.OracleClient.OracleCommand

Dim dataReader As OracleClient.OracleDataReader

Try

con.Open()

cmd = con.CreateCommand()

Dim strSQL As String

strSQL = "insert into votes values ('Linux',0)"

mandType = CommandType.Text

mandText = strSQL

cmd.ExecuteNonQuery()

strSQL = "update votes set voteCount=1 where Candidatename='Unix'"

mandType = CommandType.Text

mandText = strSQL

cmd.ExecuteNonQuery()

strSQL = "delete from votes where candidateName='C'"

mandType = CommandType.Text

mandText = strSQL

cmd.ExecuteNonQuery()

strSQL = "select * from votes"

mandType = CommandType.Text

mandText = strSQL

' data reader is limited to single table, view or stored procedure

dataReader = cmd.ExecuteReader()

' displaying the result in a table

Dim t As Table

Dim r As TableRow

Dim c As TableCell

Dim i As Integer

t = New Table

t.Visible = True

t.BackColor = Color.Cyan

t.BorderStyle = BorderStyle.Solid

t.CellPadding = 10

t.CellSpacing = 1

t.GridLines = GridLines.Both

Do While dataReader.Read

r = New TableRow

For i = 0 To dataReader.FieldCount() - 1

c = New TableCell

c.Controls.Add(New LiteralControl(dataReader.Item(i)))

r.Cells.Add(c)

Next

t.Rows.Add(r)

Loop

' displaying the result in a data grid

Dim dg As new DataGrid

dataReader = cmd.ExecuteReader()

dg.DataSource=dataReader

dg.DataBind()

Me.Controls.Add(t)

Me.Controls.Add(dg)

Catch ex As Exception

Response.Write(ex.ToString)

End Try

End Sub

End Class

Ex9: dbManipulation.aspx

In this example we will manipulate the customers table under Oracle database server using the DataAdapter and the DataSet objects. Following is the user interface for this example:

Following is the code behind:

Imports System

Imports System.Web

Imports System.Web.UI

Imports system.Data

Imports System.Web.UI.WebControls

Imports System.Xml

Imports System.Collections

Imports System.Drawing

Imports System.Data.OracleClient

Public Class dbManipulation:Inherits Page

Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

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

Dim con As New System.Data.OracleClient.OracleConnection

con.ConnectionString = "User Id=abdullah;Password=oracle;Data Source=orcl"

Dim cmd As New System.Data.OracleClient.OracleCommand

Try

con.open

cmd = con.CreateCommand()

mandType = CommandType.Text

mandText = "Select * from customrs"

Dim da As New OracleClient.OracleDataAdapter

da.SelectCommand = cmd

Dim ds As New DataSet

Dim cb As New OracleClient.OracleCommandBuilder(da)

da.UpdateCommand = cb.GetUpdateCommand

da.InsertCommand = cb.GetInsertCommand

da.DeleteCommand = cb.GetDeleteCommand

da.Fill(ds, "customers")

Dim dr As DataRow

For Each dr In ds.Tables("customers").Rows

If dr(0) = 33 Then

dr(1) = "new "

dr(2) = "new "

dr(3) = "new "

dr(4) = "new"

dr(5)=33

Exit For

End If

Next

ds.Tables("Customers").Rows(0).Item("debtamount") = 11

For Each dr In ds.Tables("customers").Rows

If dr(0) = 511 Then

dr.delete()

Exit For

End If

Next

dr = ds.Tables("Customers").NewRow()

dr(0) = 511

dr(1) = "john"

dr(2) = "smith"

dr(3) = "humber"

dr(4) = "111-222-3333"

dr(5) = 3000

ds.Tables("Customers").Rows.Add(dr)

da.Update(ds, "customers")

DataGrid1.DataSource = ds

DataGrid1.DataMember = "Customers"

DataGrid1.DataBind()

Catch ex As Exception

Response.Write(ex.ToString)

End Try

End Sub

End Class

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

Provider Objects

Connection

Command

Parameter

DataAdapter

Data Relation

DataSet

Tables

Relations

DataTable

DataRelation

DataView

Rows

Columnss

Constaints

DataRow

DataColumnn

Constraint

DataSet Objects

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

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

Google Online Preview   Download