Introduction to XML s1

CPAN 660 ASP.NET

Lecture #9: Active Data Object .NET

ADO.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 ADO.NET and the .NET framework provide native support for XML.

ADO.NET works with a disconnected set of data. After the ADO.NET 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 ADO.NET:

· 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 ADO.NET 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 ADO.NET:

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 http://www.microsoft.com/download

· 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 Studio.NET and view the Server Explorer, you will see the new instance listed under SQL Servers node:

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 http://otn.oracle.com/software/index.html

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

3- Select Add option and click Next button

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

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

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

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

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

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

10- Select No option and click Next button

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

12- Click Finish button to exist the configuration tool

In order to create a connection to Oracle from Visual Studio.NET, 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

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

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

· 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:

<system.web>

</assemblies>

</compilation>

</system.web>

</configuration>

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\Microsoft.NET\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:

<system.web>

</assemblies>

</compilation>

</system.web>

</configuration>

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:

<%@ Page Language="vb" AutoEventWireup="false" Src="OracleEx.aspx.vb" Inherits="OracleEx"%>

<html>

<body>

</body>

</html>

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

cmd.CommandType = CommandType.Text

cmd.CommandText = "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:

<%@ Page Language="vb" AutoEventWireup="false" Src="SQLServerEx.aspx.vb" Inherits="SQLServerEx"%>

<html>

<body>

</body>

</html>

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

cmd.CommandType = CommandType.Text

cmd.CommandText = "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:

<%@ Page Language="vb" AutoEventWireup="false" Src="DataSetEx.aspx.vb" Inherits="DataSetEx"%>

<html>

<body>

</body>

</html>

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()

cmd.CommandType = CommandType.Text

cmd.CommandText = "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) & "<br/>"

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:

<%@ Page Language="vb" AutoEventWireup="false" Src="ExecuteReaderEx.aspx.vb" Inherits="ExecuteReaderEx"%>

<html>

<body>

<asp:TextBox id="txtOutput" style="Z-INDEX: 101; LEFT: 141px; POSITION: absolute; TOP: 73px"

runat="server" Width="252px" Height="245px" TextMode="MultiLine"</asp:TextBox>

</form>

</body>

</html>

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