2160711 Dot Net Technology Unit 4 ADO NET

2160711 Dot Net Technology Unit - 4 ADO. NET Prof. Naimish R. Vadodariya 8866215253 naimish. vadodariya@darshan. ac. in

Outline § Introduction to ADO. NET § Architecture Of ADO. NET § Typed Data. Set § Comparison to Classic ADO & ADO. NET § Benefits of ADO. NET § Data Binding: Introducing Data Source Control Unit: 4 – ADO. NET 2 Darshan Institute of Engineering & Technology

Introduction to ADO. NET § What is ADO. NET? • ADO stands for Active. X Data Objects. • ADO. NET is a database technology of . NET Framework used to connect application system and database server. • ADO. NET is a part of the. NET Framework. • ADO. NET consists of a set of classes used to handle data access. • ADO. NET uses XML to store and transfer data among applications, which is not only an industry standard but also provide fast access of data for desktop and distributed applications. Unit: 4 – ADO. NET 3 Darshan Institute of Engineering & Technology

Architecture Of ADO. NET § The ADO. NET architecture has two main parts: • Data(Managed) Provider (Connected Objects or Connection oriented objects) • Data. Set (Disconnected objects or connectionless objects) Unit: 4 – ADO. NET 4 Darshan Institute of Engineering & Technology

Architecture Of ADO. NET Cont. . Connected Objects or Connection oriented objects Unit: 4 – ADO. NET Disconnected objects or Connectionless objects 5 Darshan Institute of Engineering & Technology

Data Providers Data Provider Description SQL Server • • • Provides data access for Microsoft SQL server. Uses the System. Data. Sql. Client namespace. Example o SQLServer 2000, 2005, 2008 &Many more versions. OLEDB • • • For data sources exposed by using OLEDB. Uses the System. Data. Ole. Db namespace. Example o For both relational and non-relational databases. (Oracle, Sql. Server, Excel, raw files, etc) ODBC • • • For data sources exposed by using ODBC. Uses the System. Data. Odbc namespace. Example o Only for relational databases (Sql Server, Oracle etc) Oracle • • • For Oracle data sources. Uses the System. Data. Oracle. Client namespace. Example o Oracle 8, 8 i, 9 i & Many more versions Unit: 4 – ADO. NET 6 Darshan Institute of Engineering & Technology

Data Providers Cont. . § The. NET framework Data Provider is a component that has been explicitly designed for data manipulation. §. NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. § The Data Provider has four core objects: 1. Connection 2. Command 3. Data Reader 4. Data Adapter Unit: 4 – ADO. NET 7 Darshan Institute of Engineering & Technology

1) Connection § The Connection object is the first component of ADO. NET. § The Connection objects provider connectivity to a data source. § It establishes a connection to a specific data source. § Connection object helps in accessing and manipulating a database. § The base class for all Connection objects is the Db. Connection class. Unit: 4 – ADO. NET 8 Darshan Institute of Engineering & Technology

Example - Connection String SQL Server Name OR Instance Name string con. String = @"Data Source=DARSHANSQL 2005; Initial Catalog=Master; Integrated Security=true"; it specifies that connection will be established using windows authentication. Database Name string con. String = @"Data Source=DARSHANSQL 2005; Initial Catalog=Master; user id=abc; password=123"; it specifies that connection will be established using sql server authentication. Unit: 4 – ADO. NET 9 Darshan Institute of Engineering & Technology

1) Connection Cont. . § Properties of Connection Object • Connection. String: Connection String is collection of name/value pairs separated by semicolon which gives information of data source with which connection needs to be established. • Data Source: it specifies name of computer and SQL server instance with which connection is required. • Integrated Security: it specifies that connection will be established using windows authentication of SQL Server username/password. • Initial Catalog: it specifies name of database with which connection is required. • User ID: it specifies username to connect with database. • Password: it specifies password to connect with database. Unit: 4 – ADO. NET 10 Darshan Institute of Engineering & Technology

1) Connection Cont. . § Methods of Connection Object ✔ Open(): this method opens connection using information provided by connection string. ✔ Close(): this method closes already opened connection. Unit: 4 – ADO. NET 11 Darshan Institute of Engineering & Technology

2) Command § The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. § It executes a command against a data source. Exposes Parameters and can execute in the scope of a Transaction from a Connection. § You can execute SQL queries to return data in a Data. Set or a Data. Reader object. § Command object performs the standard Select, Insert, Delete and Update T-SQL operations. § The base class for all Command objects is the Db. Command class. Unit: 4 – ADO. NET 12 Darshan Institute of Engineering & Technology

Properties of Command Object § Connection: It specifies that on which connection command executes. § Command. Type: It specifies type of Command to execute • Text – SQL Statement as command type • Stored. Procedure – Stored Procedure as command type • Table. Direct – Table Name as command type § Command. Text: Either SQL Statement or name of Stored procedure or name of Database table. Unit: 4 – ADO. NET 13 Darshan Institute of Engineering & Technology

Methods of Command Object § Execute. Reader(): The Execute. Reader method executes the command specified and returns an instance of Sql. Data. Reader class. It is used to Select data from database. § Execute. Non. Query(): This method executes the command specifies and returns the number of rows affected. It is used for insert, update and delete operations. § Execute. Scalar(): This method executes the command specifies and returns the first column of first row of the result set. The remaining rows and column are ignored. § Execute. Xml. Reader(): This method executes the command specified and returns an instance of Xml. Reader class. This method can be used to return the result set in the form of an XML document. § Create. Command(): This method created new command object on given connection object. Unit: 4 – ADO. NET 14 Darshan Institute of Engineering & Technology

3) Data. Reader § The Data Reader provides a high-performance stream of data from the data source. § It reads a forward-only, read-only stream of data from a data source. § Data. Reader object works in connected way. § The base class for all Data. Reader objects is the Db. Data. Reader class. § Methods of Data. Reader object • Read(): this method reads next row from Data. Reader object, if row exists it returns true otherwise it returns false. Unit: 4 – ADO. NET 15 Darshan Institute of Engineering & Technology

4) Data. Adapter § The Data Adapter provides the bridge between the Data Set object and the data source. § The Data Adapter uses command object to execute SQL commands at the data source to both load the Data Set with data and reconcile changes that were made to the data in the dataset back to the data source. § It populates a Data. Set and resolves updates with the data source. § The base class for all Data. Adapter objects is the Db. Data. Adapter class. Unit: 4 – ADO. NET 16 Darshan Institute of Engineering & Technology

Methods of Data. Adapter object § Fill(): this method takes the results of a database query from a Command object and pushes them into a Data. Set. § Update(): this method will negotiate any changes to a Data. Set back to the original data source. Unit: 4 – ADO. NET 17 Darshan Institute of Engineering & Technology

Example – Database Connectivity //Step 1: Prepare Connection Sql. Connection obj. Connection = new Sql. Connection(); obj. Connection. String = @“Data Source=Computer. NameSQLInstance; Initial Catalog=Database. Name; Integrated Security=False; User ID=Abc; Password=123; "; obj. Connection. Open(); //Step 2: Prepare & Execute Command Sql. Command obj. Command = new Sql. Command(); obj. Command. Connection = obj. Connection; obj. Command. Type = Command. Type. Text; obj. Command. Text = "SELECT Country. ID, Country. Name FROM Country ORDER BY Country. Name"; //Step 3: Collect Data to Data. Reader object which has been received as a result of Command Sql. Data. Reader obj. SDR = obj. Command. Execute. Reader(); gv. Country. Data. Source = obj. SDR; gv. Country. Data. Bind(); obj. Connection. Close(); Unit: 4 – ADO. NET 18 Darshan Institute of Engineering & Technology

Data. Set (Disconnected objects) § Data. Set object is central to supporting disconnected, distributed data scenarios with ADO. NET. § Data. Set is a memory-resident representation of data that provides consistent relational programming model regardless of the data source. § Data. Set represents a complete set of data, including related tables, constraints, and relationship among the table. Unit: 4 – ADO. NET 19 Darshan Institute of Engineering & Technology

Data. Set Cont. . § Dataset has two major objects: 1) Data. Table Collection: • Data table collection contains all the data table objects in a dataset. • A Data table is defined in the System. Data namespace and represents a single table of memory-resident data. • It contains a collection of columns represented by a data column collection, and constraints represented by a constraint collection, which together define the schema of the table. Unit: 4 – ADO. NET 20 Darshan Institute of Engineering & Technology

Data. Set Cont. . 2) Data. Relation Collection: • A relationship represented by the Data relation object, associated rows in one Data table with rows in another Data table. • A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. • A data relation identifies matching columns in two tables of a dataset. • The essential element of a data relation are: o Name of the relationship o Name of the tables being related o Related column in each table • Relationship can be built with more than one column per table by specifying an array of Data Column objects as the key columns. Unit: 4 – ADO. NET 21 Darshan Institute of Engineering & Technology

Example - Data. Set //Step 1: Prepare Connection Sql. Connection obj. Connection = new Sql. Connection(); obj. Connection. String = @"Data Source=Computer. NameSQLInstance; Initial Catalog=Database. Name; Integrated Security=False; User ID=Abc; Password=123; "; //Step 2: Prepare & Execute Command Sql. Command obj. Command = new Sql. Command(); obj. Command. Connection = obj. Connection; obj. Command. Type = Command. Type. Text; obj. Command. Text = "SELECT Country. ID, Country. Name FROM Country ORDER BY Country. Name"; Sql. Data. Adapter sda = new Sql. Data. Adapter(obj. Command); Data. Set ds = new Data. Set(); sda. Fill(ds); Unit: 4 – ADO. NET 22 Darshan Institute of Engineering & Technology

Typed Dataset § Along with late bound access to values through weakly typed variables, the Data. Set provides access to data through a strongly typed metaphor. § Tables and columns that are part of the Data. Set can be accessed using user-friendly names and strongly typed variables. § A typed Data. Set is a class that derives from a Data. Set. § It inherits all the methods, events, and properties of a Data. Set. Additionally, a typed Data. Set provides strongly typed methods, events, and properties. § This means you can access tables and columns by name, instead of using collection-based methods. Unit: 4 – ADO. NET 23 Darshan Institute of Engineering & Technology

Typed Dataset Cont. . § Aside from the improved readability of the code, a typed Data. Set also allows the Visual Studio . NET code editor to automatically complete lines as you type. § Additionally, the strongly typed Data. Set provides access to values as the correct type at compile time. § With a strongly typed Data. Set, type mismatch errors are caught when the code is compiled rather than at run time. Unit: 4 – ADO. NET 24 Darshan Institute of Engineering & Technology

Creating a Typed Data. Set § Open Visual Studio and Click on File New Project and Select Console Application. § Enter name for the project, Say Typed. Data. Set. Test. § Right click on the solution and click on Add New Item § Select XMLSchema from templates pane, give the name (Say Typed. Ds. xsd) and click on Open. This will add file by name Typed. Ds. xsd to the solution. § Click on the Server Explorer browse to the database and drop the table on the Typed. Ds. xsd file. Unit: 4 – ADO. NET 25 Darshan Institute of Engineering & Technology

Classic ADO v/s ADO. NET ADO is based on COM : Component Object Modelling based. ADO. Net is based on CLR : Common Language Runtime based. ADO stores data in binary format. ADO. Net stores data in XML format i. e. parsing of data. ADO can’t be integrated with XML because ADO. Net can be integrated with XML as ADO have limited access of XML. having robust support of XML. In ADO, data is provided by Record. Set. In ADO. Net data is provided by Data. Set or Data. Adapter. ADO is connection oriented means it ADO. Net is disconnected, does not need requires continuous active connection. continuous connection. ADO gives rows as single table view, it scans ADO. Net gives rows as collections so you can sequentially the rows using Move. Next access any record and also can go through a method. table via loop. Using a single connection instance, ADO can Using a single connection instance, ADO. Net not handle multiple transactions. can handle multiple transactions. Unit: 4 – ADO. NET 26 Darshan Institute of Engineering & Technology

Benefits of ADO. NET § ADO. NET brings with it a number of benefits, which fall into the following categories: • Interoperability The ability to communicate across heterogeneous environments. • Scalability The ability to serve a growing number of clients without degrading system performance. • Productivity The ability to quickly develop robust data access applications using ADO. NET's rich and extensible components. • Firewall As In ADO. NET transmission is via XML Format, therefore it can pass through firewalls. Unit: 4 – ADO. NET 27 Darshan Institute of Engineering & Technology

Sql. Data. Source Control § The Sql. Data. Source data source control represents data in an SQL relational database to data-bound controls. § You can use the Sql. Data. Source control in conjunction with a databound control to retrieve data from a relational database and to display, edit, and sort data on a web page with little or no code. § Sql. Data. Source control inherited from Data. Source. Control class, which provides common functionality for all of these data source controls. § The Sql. Data. Source class provides a Filter. Expression property that can be used to filter the results of calling the Sql. Data. Source class‘s Select method. Unit: 4 – ADO. NET 28 Darshan Institute of Engineering & Technology

Sql. Data. Source Control Cont. . § The Sql. Data. Source can support any SQL relational database that can be connected using an ADO. NET provider, such as the Sql. Client, Ole. Db, Odbc, or Oracle. Client. § Let’s See how to Select, Insert, Update data from database using Sql. Data. Source control without writing a single line of code. § Select Data From a Database <asp: Sql. Data. Source ID="sds. Customer" runat="server" Connection. String="<%$Connection. Strings: Customer. Connection. String %>" Select. Command="SELECT [Customer. Id], [CNAME], [CITY] FROM [CUSTOMERS]"> </asp: Sql. Data. Source> Unit: 4 – ADO. NET 29 Darshan Institute of Engineering & Technology

Inserting data into database <asp: Sql. Data. Source ID="sds. Customer" runat="server" Connection. String="<%$ Connection. Strings: Customer. Connection. String %>" Insert. Command="Insert Into [CUSTOMERS] (Customer. Id, CNAME, CITY) VALUES (@Customer. Id, @CNAME, @CITY)"> <Insert. Parameters> <asp: Form. Parameter Name="Customer. Id" Form. Field=“txt. Customer. Id" /> <asp: Form. Parameter Name="CNAME" Form. Field=“txt. CNAME" /> <asp: Form. Parameter Name="CITY" Form. Field=“txt. CITY" /> </Insert. Parameters> </asp: Sql. Data. Source> Unit: 4 – ADO. NET 30 Darshan Institute of Engineering & Technology

Updating data into database <asp: Sql. Data. Source ID="sds. Customer" runat="server" Connection. String="<%$ Connection. Strings: Customer. Connection. String %>“ Update. Command="UPDATE [CUSTOMERS] SET CNAME = @CNAME, CITY = @CITY WHERE Customer. Id = @Customer. Id"> <Update. Parameters> <asp: Form. Parameter Name="Customer. Id" Form. Field=“txt. Customer. Id" /> <asp: Form. Parameter Name="CNAME" Form. Field=“txt. CNAME" /> <asp: Form. Parameter Name="CITY" Form. Field=“txt. CITY" /> </Update. Parameters> </asp: Sql. Data. Source> Unit: 4 – ADO. NET 31 Darshan Institute of Engineering & Technology

Delete data from database <asp: Sql. Data. Source ID="sds. Customer" runat="server" Connection. String="<%$ Connection. Strings: Customer. Connection. String %>“ Delete. Command="Delete FROM [CUSTOMERS] Where Customer. Id = @Customer. Id“ Delete. Command. Type="Text"> <Delete. Parameters> <asp: Parameter Name="Customer. Id” Type="Int 32" /> </Delete. Parameters> </asp: Sql. Data. Source> Unit: 4 – ADO. NET 32 Darshan Institute of Engineering & Technology

Thank you