ADO NET part II August 2004 Marmagna Desai

ADO. NET – part II August 2004 [ Marmagna Desai]

CONTENTS n n n n ADO vs ADO. NET – Managed providers Connecting to Database n Sql. Connection Selecting Database n Sql. Command n Sql. Data. Reader Inserting Data n Sql. Command Datasets n Selecting Data n Updating Data Conclusion

ADO vs ADO. net Ultimate goal of ADO and ADO. net is same: Providing data access API. Though there are many differences. ADO relies on COM n ADO. net relies on “managed-providers” defined by CLR n

ADO vs ADO. net ……[cont] Main Differences: ADO. NET Connection Model Connection Oriented Model is used mostly. Disconnected Model is used: Messeage-like Model. Disconnected Access Provided by Record. Set Provided by Data. Adapter and Dataset Data Representation One Recordset for one Table One Dataset for many interrelated tables with relationships Data Exchange Binary Mode – Firewall Problem Use XML and XSD schema types are fully supported XML Support Limited Robust Support

ADO vs ADO. net ……[cont] Connection Model: n ADO: n n n Client application needs to be connected always to data-server while working on the data. These results in open connections for long time and thus data can not be accessed in parallel. ADO. NET n Client disconnects connection immediately the data is processed. This will cache data at client side to achieve better performance. Hence ADO. net creates “disconnected version of Record. Set object

ADO vs ADO. net ……[cont] Data Representation: n n Recordsets are generated one per table. This does not support hierarchical structure of data access. It will be programmer’s responsibility to define relationships among different recordsets. Rercordsets can not support data accessing from multiple sources. Above limitations are resolved by implementation of Dataset objects in ADO. net mode

ADO vs ADO. net ……[cont] Data Passing: n ADO objects communicate in binary mode hence it will be really difficult to pass data across firewalls. n ADO. net use XML for passing data

ADO. net Managed Providers ADO. net provides following three classes n n n DBConnection DBCommand DBData. Reader These classes are never used directly. The inherited set of classes called “managed providers” are used for different functionalities of data access

ADO. net Managed Providers These are the different providers n n Provider optimized for SQL 7 OLE – DB provider A generic ODBC provider Provider for Oracle These providers gathers information on various data sources and how to interact with them n E. g. SQL data provider uses private protocol (tabular data stream) to provide information and access methods on SQL server.

Connecting to Database Following methods are very similar to ADO. They are connection oriented and hence leave open connection while retrieving the data from source. n n SQL managed provider contains the class called Sql. Connection. This class is used to connect to the sql database. n Code: [ Please see next slide]

Code…. . Dim conn. Str as String = “server=localhost; uid=uid; pwd=pwd; database=northwind; " Dim conn as New Sql. Connection (conn. Str) Conn. Open() “Access Data Records Conn. Close() This is very similar to ADO program. Only the Sql. Connection will be replaced with ADODB. Connection.

Selecting Data Sql. Command class is defined in ADO. net to provide different functionalities like insert, update, delete etc. on selected database. It is similar to ADODB. Command class in ADO. Sql. Command retrieves and place data on objects called Sql. Data. Reader Sql. Command does this using Execute. Reader() method. [Please see next slide for Code]

Code Dim conn As Sql. Connection = Nothing Try conn = New Sql. Connection(conn. Str) Dim cmd As New Sql. Command("Select State. Code, " & "State. Name From States", conn) conn. Open() Dim reader As Sql. Data. Reader = _ cmd. Execute. Reader(Command. Behavior. Close. Connection) Me. dd. Region. Data. Source = reader Me. dd. Region. Data. Bind() Me. dd. Region. Items. Insert(0, New List. Item("Select One: ", "")) Catch exp As Exception lbl. Output. Text = "Error occurred: " + exp. Message Finally If (conn. State <> Connection. State. Closed) Then conn. Close() End Try

Explanation A connection is made using Sql. Connection Sql. Command is used to construct a query Execute. Reader is used to generate Sql. Data. Reader object Bind data to Drop. Down. List Server name dd. Region

Inserting Data Sql. Command can be used to perform insert, delete, update etc. operations on data. Execute. Non. Query method is used for this purpose. This method does not allow results to return to the database. [Please see code in next slide]

Code Dim conn As New Sql. Connection(conn. Str) Dim cmd As New Sql. Command("Cust. Insert", conn) cmd. Command. Type = Command. Type. Stored. Procedure cmd. Parameters. Add("@Customer. ID", Me. txt. Customer. ID. Text) cmd. Parameters. Add("@Name", Me. txt. Name. Text) cmd. Parameters. Add("@Company", Me. txt. Company. Text) cmd. Parameters. Add("@Title", Me. txt. Title. Text) cmd. Parameters. Add("@Address", Me. txt. Address. Text) cmd. Parameters. Add("@City", Me. txt. City. Text) cmd. Parameters. Add("@Region", Me. dd. Region. Selected. Value) cmd. Parameters. Add("@Zip", Me. txt. Zip. Text)

Code …. [cont] Try conn. Open() cmd. Execute. Non. Query() lbl. Output. Text = "Your information was successfully saved!" Catch exp As Exception lbl. Output. Text = "An error occurred: " + exp. Message Finally 'Ensure connection is closed If (conn. State <> Connection. State. Closed) Then conn. Close() End Try

Datasets n Datasets are new to ADO. net. n It access data in Disconnected in maner n It stores data in-memory and process it.

Datasets…. [cont] Following are main features n Datasets does not interact with data source directly. n Data adapter is used to fill this gap. : Sql. Data. Adapter class n Sql. Data. Adapter class provides functionalies such as insert, delete and update. n Sql. Data. Adapter also provides method called Fill() which is used to fill up the Dataset. n Once dataset is filled with data, it can define relationships among gathered data from different source. n Dataset uses XML to transmit data among different components. n Different views of data stored in dataset can be created using dataview calss.

Code Following code shows how to query database using Sql. Data. Adapter and fill the Dataset. Dim conn As Sql. Connection = Nothing Try conn = New Sql. Connection(conn. Str) ‘Create Data. Adapter Dim da As New Sql. Data. Adapter("Select State. Code, " & _ "State. Name From States", conn) ‘Create Data. Set Dim ds As New Data. Set ‘Fill Data. Set da. Fill(ds, "States")

Code …cont ‘Access a Data. Table in the Data. Set and create a bindable view Dim view As Data. View = ds. Tables(0). Default. View Me. dd. Region. Data. Source = view Me. dd. Region. Data. Bind() Me. dd. Region. Items. Insert(0, New List. Item("Select One: ", "")) Catch exp As Exception lbl. Output. Text = "Error occurred: " + exp. Message Finally If (conn. State <> Connection. State. Closed) Then conn. Close() End Try

Conclusion Basically ADO. net adds following extended capabilities to ADO. n n Disconnected modeling Relational Database query and in-memory storage Hierarchical structure of data XML based transmission of data among components These additional features makes ADO. net considerable advancement in Microsoft Technology

Q? THANKS!!