A Hand Book On Ado

A Hand Book on Paladn.com

1 www.paladn.com

TABLE OF CONTENTS

1. The ADO.Net Model - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - -- -- - - - - - - 3

2. The ADO.net Command Object - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6

3. The ADO.net Connection Object - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 9

4. The ADO.net Datareader - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 14

5. The ADO.net Dataset Object Part 1 - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - 16

6. The ADO.net Dataset Object Part 2- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 20

2 www.paladn.com

THE ADO.NET MODEL ADO.Net is the latest in a series of technologies from Microsoft which focus on the connection of applications to databases of one sort or another. From the DAO which (and is) was the native mode of connection for MSAccess, through the short-lived RDO, and the now comparatively long-in-the-tooth ADO, this is the next generation of technology. And, although it is not likely that there will not be some future add-ons, enhancements, and upgrades, it appears that this structure of database connectivity is a keeper. It is not a COM technology, so it can be used on other platforms in addition to Windows, and agnostic when it comes to the brand of database it facilitates connection to. In addition, it allows more extensive support to the XML paradigm. The .Net platform will continue to allow you to use the older ADO connection technology, but, under most circumstances, this is a poor choice because of the performance penalty , which comes from using the unmanaged code in the COM object. ADO.Net requires some new methods of accomplishing some of the simple tasks of interacting with data. For example, server-side cursors and are not supported any more because of the increased overhead and the potentially large number of lacks required on the server. Accordingly, the only connection s allowed are forward only, read- read-only result sets, and disconnected result sets. There are rumors of server side cursors being planned for future releases, probably due to the loud complaint from the developer community. However, there are a number of techniques and tools provided which greatly lessen the need for server side cursors, so by the time of the next release, there may be less need for them. To gain access to the ADO.Net class libraries, you must add the following statements to the top of your source files: Imports System.Data Imports System.Data.OleDb* or, if you are connecting to SQLServer

3 www.paladn.com

Imports System.Data.SqlClient There is also support for the ODBC connections through Imports System.Data.ODBC These commands expose the objects needed to connect to the data source.

Data Retreival Like ADO, ADO.Net uses a connection object to point to external data. Under the .Net model, a connection is opened, data is retrieved, and then the connection is closed. The closing of the connection is necessary to free up resources. The connection string (the part of the comment which identifies the source of the data, as well as access to it through username and password) is identical to the connection string grammar under the old model ADO. The first way to access data is after you have defined and opened the connection, invoke the command object providing it with a SELECT statement, or storedprocedure name with parameters. The Data Reader will allow the application to gain access to the returned resultset. An ExecuteReader method will allow a line by line reading of the data retrieved. However, be aware that this is a forward only dataset – once a line is read, unless you save its contents somewhere, somewhere the data can be lost. The only way to make it available again is to re-establish the connection and read it again. The second method opens a connection, retrieves a recordset, then stores that recordset in an object called a DataSet. The DataSet acts and functions like a local database, storing the data retrieved – even from multiple sources. It can even link and establish relationships between multiple tables. At the conclusion of the data retrieval, the connection is closed, so that in processing the DataSet is completely disconnected from the data source(s). The mapping of data between the DataSet and the outside data sources is handled by the DataAdapter object. In addition to keeping track of the connections to the data sources, the DataAdapter also facilitates the updating, deleting, and insertion of data back to the source.

4 www.paladn.com

XML XML is the native format for ADO.Net. It is so tightly integrated that you can define and read schemas, and can seamlessly exchange data in the XLM format, both reading and writing with any application on any platform.

5 www.paladn.com

THE ADO.NET COMMAND OBJECT

In a connected mode environment, after a connection is established with the data, the data is manipulated and returned using the command object. The command object is passed a SQL query SELECT statement, which is run by one of the Execute methods. The three Execute methods are ExecuteNonQuery (for updates or deletes or appends), Execute Reader (for returning datasets to the client), and ExecuteScalar (returns a single value). The command objects themselves are either of SqlCommand, OleDbCommand, or ODBCCommand types. And the principal properties are the command text (the sql statement, and the connection object previously created. ) Other properties are the CommandType, Transaction, CommandTimeout, Parameters, and UpdatedRowSource. Dim cmd as New SqlCommand cmd.connection=conn cmd.CommandText=”SELECT * FROM tblBooks;” Dim dr as SqlDataReader = cmd.ExecuteReader() The preceding code will execute a SQL command and put the results into a DataReader after having made and opened a connection. Had the SQL statement been an INSERT statement, the appropriate method would have been ExecuteNonQuery(), rather than ExecuteReader(). There is an additional Execute XMLReader() method which is new in SQL2000,

and

is

used

for

processing

SELECT

queries

from

an

XML

dataset.

ExecuteScalar() is another method used for returning a single value from a dataset.

Parameters In the OleDb model, you can define parameters in the CommandText using question marks.

6 www.paladn.com

SELECT * from tblBooklist where YearPublished = ?; Then you need to create and define the parameter prior to passing them into the command object. Dim Parm as New OleDBParameter(“YearPublished”, OleDbType.Integer) Parm.Value=1994 cmd.Parameters.Add(parm) You can even have ADO.Net populate the list of parameters for you using the DeriveParameters method. Dim cmd as New SqlCommand(“up_getshotgun”, conn) cmd.CommandType=CommandType.StoredProcedure SqlCommandBuilder.DeriveParameters(cmd) Debug.writeline(cmd.parameters.count & “parameters”) For i = 0 to cmd.parameters.count-1 Debug.writeline(cmd.parameters(i).ParameterName) Next

Stored Procedures Stored Procedures are handled using the parameters in the previous section and setting them with the Parameters.Add method. The only difference from the previous code is the calling of the stored procedure itself. This is accomplished using the CommandType property, rather than the CommandText property. Dim cmd as New SqlCommand(“up_getshotgun”, conn) cmd.CommandType=CommandType.StoredProcedure

7 www.paladn.com

cmd.Parameters.Add(“@Brand”,”Remington”) Dim dr as SqlDataReader=cmd.ExecuteReader()

8 www.paladn.com

THE ADO.NET CONNECTION OBJECT Either in connected or disconnected mode, the first thing one needs to do is to connect to the database(s). This is accomplished in ADO.net by creating a connection object that points to the subject database.

The properties of the connection object are: Connection string

A string used to connect to the database.

Connection Timeout

The number of seconds till a connection times out (Read Only)

Database

Returns the database name as specified in connection string (Read Only)

DataSource

Returns the source attribute as specified in connection string (Read Only)

ServerVersion

Returns version of connected server.

State

Returns state of current database in integers. Values can be

Closed,

Connecting,

Open,

Executing,

Fetching,

Broken Provider

Returns the value of provider attribute as specified in connection string (Read Only) (OleDb Only)

PacketSize

Returns size in bytes of network packets (SQL Server only)

WorkstationID

Identifies client, as specified in connection string (Read Only)

9 www.paladn.com

In the above table, the only property that is NOT read only is the connection string. Some folks say that it is the connection string that is the most difficult aspect of ADO and ADO.Net. If so, it is an easily learned one. A typical connection string consists of 4 items: The Provider, which specifies the name of the underlying OLEDB provider. Appropriate values are SQLOLEDB (for SQLServer), Microsoft.Jet.OLEDB.4.0 (for Microsoft Access) and MSDORA (for Oracle); The Data Source attribute, which shows the location of the database. It can be a path on a network, or the IP address of a machine on the net; The UserID and Password, which grant access permission to the database; The Initial Catalog, which specifies the name of the database in the data source. Here are some common configurations:

For SQL Server – Data Source=Jupiter;Initial Catalog=pubs;User Id=ElmerFudd;Password=wabbitt; Server=Jupiter;Database=pubs;Trusted_Connection=True;Connection Timeout=10 Data Source=200.192.23.155;Network Library=Wiley3301;Initial Catalog=pubs;User ID=ElmerFudd;Password=wabbitt;

C#: using System.Data.SqlClient; objqlConnection oSQLConn = new SqlConnection();oSQLConn.ConnectionString=connectstring;;oSQLConn.Open();

obj VB.NET: Imports System.Data.SqlClient Dim objSQLConn As SqlConnection = New SqlConnection() objSQLConn.ConnectionString="connectstring" objSQLConn.Open()

10 www.paladn.com

For Oracle: Provider=OraOLEDB.Oracle;Data Source=mydatabase;User Id=ElmerFudd;Password=wabbitt; Provider=OraOLEDB.Oracle;Data Source= mydatabase;OSAuthent=1;

C#: using System.Data.OracleClient; OracleConnection objOracleConn = new OracleConnection(); objOracleConn.ConnectionString = my connectionstring; objOracleConn.Open();

VB.NET: Imports System.Data.OracleClient Dim objOracleConn As OracleConnection = New OracleConnection() objOracleConn.ConnectionString = myconnectionstring objOracleConn.Open()

For MS Access: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\pathname\biblio.mdb;User Id=ElmerFudd;Password=wabbitt; Notice that the last instruction in the code using the method ‘open()’. After the connection has been made, and the data retrieved, you need to close the connection using the connection method ‘close()’. This should be done within an ‘if’ statement which first checks whether the connection is, in fact, open: If (objConnection.state and ConnectionState.Open) <>0 Then objConnection.Close End If Note that the state property is ‘0’ if the connection is already closed. Testing for a closed connection is necessary to prevent an error when you are invoking the ‘close’ method.

11 www.paladn.com

The connection objects methods are: Open

Opens connection

Close

Closes connection

BeginTransaction

Begins database transaction

ChangeDatabase

Changes the name of database connected to

CreateCommand

Creates a command object

GetOleDbSchemaTable Returns

schema

tables

and

associated

restricted

columns ReleaseObjectPool

Shared method which allows closing of connection pool when last connection is closed

Exception Handling All ADO connection procedures should be protected with a Try/Catch Block. When dealing with a connection to another server, this is especially important to let your users know that it was the connection that failed, rather than the application code. Try connSQLNorthwind.ConnectionString = _ "Server=Jupiter;Database=pubs;Trusted_Connection=True;Connection Timeout = 10" Catch ExSQL As System.Data.SqlClient.SqlException Dim strErrorMsg As String Dim strerror As System.Data.SqlClient.SqlError For Each strerror In ExSQL.Errors Select Case strerror.Number Case 17 strErrorMsg = "Missing server" Case 4060 strErrorMsg = "Missing database"

12 www.paladn.com

Case 18456 strErrorMsg = "Missing user name or password" Case Else strErrorMsg = strerror.Message End Select MessageBox.Show(sErrorMsg, "SQL Server Error: " & strerror.Number, MessageBoxButtons.OK MessageBoxIcon.Error) Next

Catch ExcpInvOp As System.InvalidOperationException MessageBox.Show("Close the connection first!", _ "Invalid Operation MessageBoxButtons.OK, MessageBoxIcon.Error) Catch Excp As System.Exception ' generic exception handler MessageBox.Show(Excp.Message, "Unexpected Exception MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

13 www.paladn.com

THE ADO.NET DATAREADER The Data Reader is a simple object – a use one time and throw away container for holding the results of an ExecuteReade()r method of the command object. The Data Reader’s principal method is the Read() method, which reads one record from the result set. Since in a client server environment, it is considered good programming practice to be frugal in retrieving records, the DataReader at any one time will contain relatively small amounts of data. For instance, in a typical form procedure, the trip to the server may only return a single record, or perhaps a single record with associated child records needed to populate one screen. When multiple records are retrieved, they are read from the data reader in loop structure one record at a time. Do while dr.Read Process stuff Loop Each record is processed and/or stored in turn, because the DataReader has ho no capacity for returning to any record after the application has passed it. The only way to revisit a record in the DataReader is to re-connect and request the data again. In more sophisticated applications, the DataReader may contain multiple records which may be read and mapped into a DataGrid. The Grid may be equipped with edit, append and delete buttons to allow changing of the data and eventual uploading of the changed results back to the server through the command object. It is also possible to use the GetSchemaTable() method to acquire characteristics of the data columns and built a local DataTable on the client – either virtually or in an auxiliary Access, or XML table. The System.Data.DataTable() method is used to built the structure. A key property is the Item property, which will allow you to get field names like the fields collection in a DAO recordset. It is also possible to retrieve multiple resultsets at the same time into a DataReader. This is frequently the result of a batch SQL Server stored procedure with multiple SELECT

14 www.paladn.com

statements. The NextResult method allows you to advance to the next recordset within the DataReader. Do while dr.NextResult Do while dr.Read Process stuff Loop Loop

15 www.paladn.com

THE ADO.NET DATASET OBJECT PART 1 The DataSet is an in-memory database – a collection of virtual tables with means to populate, examine, modify and deleting delete the tables and the data within them. You can also define and manipulate the relationships between the tables and tables, create and enforce constraints to maintain those relationships. Surprisingly, the data that populates the tables within a DataSet can and frequently does come from different data sources, leading to very powerful flexibility in connecting and maintaining data in various server sources. The primary purpose of a DataSet is to allow the user to operate on data from a server in a disconnected mode. The temporary separation of data from the server is a desirable thing because it frees memory on the server, as well as connection and network resources. The DataSet allows data to be ‘borrowed’ from the server in relatively small usable chunks. The DataSet then ‘manages’ the connection between the data and allows the user to edit, update, delete, append, or select portions of that data. The DataSet is first and foremost a collection of table objects. These table objects can be imported from various and diverse data sources such as SQL Server, Oracle, XML files, and the like, or they can be entered programmatically or through a convenient User Interface (UI) and then added to the DataSet programmatically. The most useful methods are to Accept Changes, AcceptChanges, RejectChanges, Merge, Clone, a few functions for reading and writing XML and XML schema, and – most important of all- the GetChanges method, which gets a DataSet which contains all of the data changes made to the current one since it was loaded. The table objects look like tables in a regular database, though they are virtual tables, which reside only in memory. The table objects have collections of DataColumns, DataRows,

Constraints,

DataRelations,

Property

Collections,

DataRowView,

and

a

PrimaryKey, which may be a collection of DataColumns. DataTable Class Members

16 www.paladn.com

Constructors Visibility

Constructor

Parameters

public

DataTable

()

public

DataTable

( String tableName )

Properties Visibility

Name

Value Type

Accessibility

public

CaseSensitive

Boolean

[ Get , Set ]

public

ChildRelations

DataRelationCollection [ Get ]

public

Columns

DataColumnCollection

[ Get ]

public

Constraints

ConstraintCollection

[ Get ]

public

Container

IContainer

[ Get ]

public

DefaultView

DataView

[ Get ]

public

DesignMode

Boolean

[ Get ]

public

DisplayExpression

String

[ Get , Set ]

public

ExtendedProperties PropertyCollection

[ Get ]

public

HasErrors

Boolean

[ Get ]

public

Locale

CultureInfo

[ Get , Set ]

public

MinimumCapacity

Int32

[ Get , Set ]

public

Namespace

String

[ Get , Set ]

public

ParentRelations

DataRelationCollection [ Get ]

public

Prefix

String

[ Get , Set ]

public

PrimaryKey

DataColumn

[ Get , Set ]

public

Rows

DataRowCollection

[ Get ]

public

Site

ISite

[ Get , Set ]

public

TableName

String

[ Get , Set ]

17 www.paladn.com

Methods Visibility

Name

Parameters

Return Type

public

AcceptChanges

()

Void

public

BeginInit

()

Void

public

BeginLoadData

()

Void

public

Clear

()

Void

public

Clone

()

DataTable

public

Compute

( String expression,

Object

String filter ) public

Copy

()

DataTable

public

EndInit

()

Void

public

EndLoadData

()

Void

public

GetChanges

( DataRowState

DataTable

rowStates ) public

GetChanges

()

DataTable

public

GetErrors

()

DataRow

public

ImportRow

( DataRow row )

Void

public

LoadDataRow

( Object values ,

DataRow

Boolean fAcceptChanges ) public

NewRow

()

DataRow

public

RejectChanges

()

Void

public

Reset

()

Void

public

Select

( String

DataRow

filterExpression, String sort, DataViewRowState recordStates ) public

Select

( String

DataRow

filterExpression ) public

Select

()

DataRow

18 www.paladn.com

public

Select

( String

DataRow

filterExpression , String sort ) public

ToString

()

String

Events Multicast

Name

Type

multicast

ColumnChanged

DataColumnChangeEventHandler

multicast

ColumnChanging

DataColumnChangeEventHandler

multicast

Disposed

EventHandler

multicast

RowChanged

DataRowChangeEventHandler

multicast

RowChanging

DataRowChangeEventHandler

multicast

RowDeleted

DataRowChangeEventHandler

multicast

RowDeleting

DataRowChangeEventHandler

Rows are added programmatically using the DataRows object:

Dim dr as DataRow=tblEmployee.NewRow() dr(“dcFirstName”)=”Elmer” dr(“dcLastName”)=”Fudd” tblEmployee.Rows.Add(dr)

19 www.paladn.com

THE ADO.NET DATASET OBJECT PART 2

DataRow Class Members Properties Visibility

Name

Value Type

Accessibility

public

HasErrors

Boolean

[ Get ]

public

Item ( Int32 columnIndex )

Object

[ Get ]

( DataRowVersion version ) public

Item ( DataColumn column )

Object

[ Get , Set ]

public

Item ( DataColumn column ) (

Object

[ Get ]

Object

[ Get ]

DataRowVersion version ) public

Item ( String columnName ) ( DataRowVersion version )

public

Item ( Int32 columnIndex )

Object

[ Get , Set ]

public

Item ( String columnName )

Object

[ Get , Set ]

public

ItemArray

Object

[ Get , Set ]

public

RowError

String

[ Get , Set ]

public

RowState

DataRowState [ Get ]

public

Table

DataTable

[ Get ]

20 www.paladn.com

Methods Visibility

Name

Parameters

Return Type

public

AcceptChanges

()

Void

public

BeginEdit

()

Void

public

CancelEdit

()

Void

public

ClearErrors

()

Void

public

Delete

()

Void

public

EndEdit

()

Void

public

GetChildRows

( String

( String

relationName )

relationName )

( String

DataRow

public

GetChildRows

relationName , DataRowVersion version ) DataRow

GetChildRows

( DataRelation

DataRow

relation , DataRowVersion version ) public

GetChildRows

( DataRelation

DataRow

relation ) public

GetColumnError

( Int32 columnIndex

String

) public

GetColumnError

( String

String

columnName ) public

GetColumnError

( DataColumn

String

column ) public

GetColumnsInError

()

DataColumn

public

GetParentRow

( String

DataRow

relationName , DataRowVersion version )

21 www.paladn.com

public

GetParentRow

( String

DataRow

relationName ) public

GetParentRow

( DataRelation

DataRow

relation , DataRowVersion version ) public

GetParentRow

( DataRelation

DataRow

relation ) public

GetParentRows

( DataRelation

DataRow

relation , DataRowVersion version ) public

GetParentRows

( DataRelation

DataRow

relation ) public

GetParentRows

( String

DataRow

relationName , DataRowVersion version ) public

GetParentRows

( String

DataRow

relationName ) public

HasVersion

( DataRowVersion

Boolean

version ) public

IsNull

( String

Boolean

columnName ) public

IsNull

( DataColumn

Boolean

column , DataRowVersion version ) public

IsNull

( DataColumn

Boolean

column ) public

IsNull

( Int32 columnIndex

Boolean

) public

RejectChanges

()

Void

22 www.paladn.com

public

SetColumnError

( Int32 columnIndex

Void

, String error ) public

SetColumnError

( DataColumn

Void

column , String error ) public

SetColumnError

( String

Void

columnName , String error ) public

SetParentRow

( DataRow

Void

parentRow , DataRelation relation ) public

SetParentRow

( DataRow parentRow )

The DataSet is a much more powerful and complicated object than the DataReader. The DataReader is a read-only forward-only object wherein once a record is read it is gone, unless it is saved into an array. The DataSet and DataTable objects are virtualls datasets and tables which are used in client-side operations. Though there is no ‘current record’ concept, the user is able to navigate between the rows using loops, either using the Rows collection of the DataTable object, or the DataRow object. For i=1 to 3 tblEmployee.Rows(i)(“FirstName”)=”Daffy” or dim dr as DataRow=tblEmployee.Rows(i) dr(“LastName”)=”Duck” Next

23 www.paladn.com

Don’t forget that the DataSet is disconnected from the original server-side source of the data. So any changes in the DataSet will not be reflected in the source unless and until the DataSet and the Source are reconciled. You can find the status of any record by querying the RowState property of the DataRow object. The result will be one of the following: Detached, Added, Modified, Deleted, or Unchanged. Though the RowState property is read only, the DataRow has two properties which can change it: the AcceptChanges, and RejectChanges Properties. Of further note are the DataTable.ColumnChanging, the DataTable.ColumnChanged, the DataTable.RowChanging, and the DataTable.RowChanged events which allow you to trap and code for these events within your code. The DataView Object The function of the DataView object is to facilitate data binding of data to Windows forms and web pages. Additionally, it can be used along with the Select method od the DataTable Object to present subsets of records from tables. Use the RowFilter property to select which records are visible, and use the Sort property to order their presentation.

DataView Class Members

Constructors

Visibility

Constructor

Parameters

public

DataView

()

public

DataView

( DataTable table )

public

DataView

(

DataTable

table

,

String

RowFilter

,

String

Sort

,

DataViewRowState RowState )

24 www.paladn.com

Properties Visibility

Name

Value Type

Accessibility

public

AllowDelete

Boolean

[ Get , Set ]

public

AllowNew

Boolean

[ Get , Set ]

public

ApplyDefaultSort

Boolean

[ Get , Set ]

public

Container

IContainer

[ Get ]

public

Count

Int32

[ Get ]

public

DataViewManager

DataViewManager

[ Get ]

public

DesignMode

Boolean

[ Get ]

public

Item

(

Int32 DataRowView

[ Get ]

recordIndex ) public

RowFilter

String

[ Get , Set ]

public

RowStateFilter

DataViewRowState

[ Get , Set ]

public

Site

ISite

[ Get , Set ]

public

Sort

String

[ Get , Set ]

public

Table

DataTable

[ Get , Set ]

Methods Visibility

Name

Parameters

Return Type

public

AddNew

()

DataRowView

public

BeginInit

()

Void

public

CopyTo

(

Array

array

, Void

Int32 index ) public

Delete

( Int32 index )

Void

public

EndInit

()

Void

public

Find

( Object key )

Int32

public

Find

( Object key )

Int32

public

FindRows

( Object key )

DataRowView

public

FindRows

( Object key )

DataRowView

public

GetEnumerator

()

IEnumerator

25 www.paladn.com

Events

Multicast

Name

Type

multicast

Disposed

EventHandler

multicast

ListChanged

ListChangedEventHandler

26 www.paladn.com