ADO DOTNET by MindTree Campus

Notes: Commonly, the data source is a data base, such as SQL Server database. But it could also be a text file, an Excel spread sheet, or an XML file.

System.Data: Contains common built-in types to interact with any type of source of data, such as, DataTable, DataSet, DataRow, DataColumn classes System.Data.SqlClient: Contains built-in types to interact with SQL Server database, such as, SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter classes etc. System.Data.OracleClient: Contains built-in types to interact with Oracle database, such as, OracleConnection, OracleCommand, OracleDataReader, OracleDataAdapter classes etc. System.Data.OleDb: Contains built-in types to interact with MS-Access database, such as, OleDbConnection, OleDbCommand, OleDbDataReader, OleDbDataAdapter classes etc.

System.Data.Odbc: Contains built-in types to interact with ODBC (Open Data Base Connectivity) type database, such as, OdbcConnection, OdbcCommand, OdbcDataReader, OdbcDataAdapter classes etc. System.Data.SqlTypes: Contains all the data type information used for SQL Server database table data.

Notes: Difference between the scenarios: 1. In disconnected mode, the application gets connected to database only while retrieving and storing data temporarily into some objects and then again while updating the manipulated data into the actual source of data from that temporary storage. Whereas in connected mode, the application gets connected to the actual source of data first and then manipulates data in actual data source till the connection is closed once again manually. 2.

In disconnected mode, application stores data retrieved from actual source of data into a runtime ADO.NET object and manipulates the copied data in that storage, since the connection is closed automatically after retrieving data. Whereas in connected mode application directly manipulates data in the actual source of data.

In connected scenario, since the application is constantly connected to the database, it adds network overhead. Whereas, in disconnected scenario the application gets connected to the database for a short period of time, it does not add the network overhead.

In connected scenario, the application gets current updated data from actual source of data, since it is constantly connected with the database directly, whereas in disconnected mode the application does not get the current, updated data since in this mode application is connected to the database only while retrieving data from or updating manipulated data to the original source of data.

When you should use which scenario: Use connected mode when you need to access and manipulate small amount of data and disconnected mode when you need to access and manipulate large amount of data.

Notes: In this architecture you will find that total ADO.NET components (or ADO.NET objects) are divided into two core categories â&#x20AC;&#x201C; 1. Data Provider and 2. Data Consumer. Data Provider: The components or ADO.NET objects that are used to interact directly with the source of data, which could be a simple text file, excel file, XML file or any large scale database such as SQL Server, Oracle etc. are known as Data Providers. These objects are used to connect to the source of data, get data from or update, insert, delete data into that source of data by executing some query . So, data provider is very much concerned about what type of source of data it is interacting with. So, data provider is dependent on the source of data. The technique to interact with database varies, depending on what type of database it is working with. Data Provider Components: Connection Object (such as object of SqlConnection class to connect with SQL Server database) Command Object (such as, SqlCommand class object to pass query to database and get it executed) Reader object (to read data as a forward-only, read-only stream of data from database in connected mode) Adapter object ( to manage data in disconnected mode. It is responsible for connecting to the database, get data from database, populate temporary runtime ADO.NET storage object by that data and then close the connection) Etc. Data Consumer: Once, you retrieve data from source of data, you can use some ADO.NET objects to temporarily store that data. They are type of containers. They are known as Data Consumer. Data Consumer objects are not related to or dependent on the source of data, since its job is just to store data temporarily during runtime, supplied by data provider objects, such as data adapter object. Data Consumer Objects: Dataset object (it can store data retrieved from different source of data by data provider objects. It stores data by creating table objects. In this way data set is a collection of different data tables, whereas each and every table is a collection of rows or records as well as collection of columns or fields and collection of constraints that are maintained for a table. Even relationship between two tables in data set object can be created. So, data set object is also collection of data relation objects, where each relation object represents a relation between two tables in the data set object)

Notes: Although ADO.NET allows us to interact with different types of data sources and different types of data bases, there isn't a single set of classes that allows us to accomplish this universally as different data sources expose different protocols.

Notes: The above figure illustrates the data access stack and how ADO.NET relates to other data access technologies, including ADO and OLE DB. It also shows the two managed providers and the principal objects within the ADO.NET model.

.ASPX Page: it is extension for web pages create using ASP.NET technology. In this diagram any application (such as web sites with web pages, as for example) can access data from any type of database using connection (by getting connected) and command object (by passing query) and then can read that data using data reader (in connected model) or data adapter (disconnected model) and then bind (attach) that data to controls present on the web page. Even a customized view of that data can be created, known as data view and then can be displayed through any control.

There are many classes, part of SQL Data Provider for .NET, such as SqlBulkCopy, SqlParameter, SqlCommandBulider etc. Do not think any data provider consists only those 4 classes mentioned in the slide. They are the major classes of SQL data provider for .NET.

Notes: You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute a new or previous command.

Notes: There is another technique to use parameters in the command object. 1.

First Create the SqlCommand object: (consider conn is the object of SqlConnection class) SqlCommand cmd = new SqlCommand( "select * from Customers where city = @City", conn);

2. Then Add the Parameter details in the following manner: cmd.Parameters.AddWithValue(“@City”, inputcity).SqlDbType=SqlDbType.Varchar In this code: ‘Parameters’: Property of SqlCommand class. Returns an instance of SqlParameterCollection class, which stores reference to all the parameters, which are part of the SqlCommand class object. ‘AddWithValue’: Method of SqlParameterCollection class. Used to add parameter details in the collection. ‘SqlDbType’ of left hand side of the equal sign: Property of SqlParameter class.

‘SqlDbType’ of right hand side of the equal sign: Enumeration containing all the data type names used by Sql database as members.

Notes: In the code: Read() method of SqlDataReader class: Used to read data and move the cursor to the nest record. Returns true or false, depending on any record is there to read or not.

Notes: This is a sample implementation of the basic ADO.NET objects. As shown in code, you open a connection by calling the Open() method of the SqlConnection instance, conn. Any operations on a connection that was not yet opened will generate an exception. So, you must open the connection before using it. The SqlCommand object is constructed and the action to be performed by the command is specified in the commandText as a query. A SqlParameter object is constructed and its value is assigned.

Notes: The SqlParameter constructed is then associated with the command object. The ExecuteReader method is then invoked to fetch the records that satisfy the given query and assigned to a SqlDataReader object.

Finally both the reader and the connection objects are closed.

Notes: One of the key characteristics of the DataSet is that it has no knowledge of the underlying data source that might have been used to populate it. It is a disconnected, stand-alone entity used to represent a collection of data, and it can be passed from component to component through the various layers of a multitier application. It can also be serialized as an XML data stream, which makes it ideally suited for data transfer between heterogeneous platforms. --------- Scenarios on where to use Disconnected A couple scenarios illustrate why you would want to work with disconnected data: people working without network connectivity and making Web sites more scalable. Consider sales people who need customer data as they travel. At the beginning of the day, they'll need to sync up with the main database to have the latest information available. During the day, they'll make modifications to existing customer data, add new customers, and input new orders. This is okay because they have a given region or customer base where other people won't be changing the same records. At the end of the day, the sales person will connect to the network and update changes for overnight processing. Another scenario is making a Web site more scalable. With a SqlDataReader, you have to go back to the database for records every time you show a page. This requires a new connection for each page load, which will hurt scalability as the number of users increase. One way to relieve this is to use a DataSet that is updated one time and stored in cache. Every request for the page checks the cache and loads the data if it isn't there or just pulls the data out of cache and displays it. This avoids a trip to the database, making your application more efficient. Exceptions to the scenario above include situations where you need to update data. You then have to make a decision, based on the nature of how the data will be used as to your strategy. Use disconnected data when your information is primarily read only, but consider other alternatives (such as using SqlCommand object for immediate update) when your requirements call for something more dynamic. Also, if the amount of data is so large that holding it in memory is impractical, you will need to use SqlDataReader for read-only data. Really, one could come up with all kinds of exceptions, but the true guiding force should be the requirements of your application which will influence what your design should be.

Notes: The diagram illustrates the Dataset object model. A DataTable represents a collection of rows from a single table. A DataSet represents a collection of DataTable objects, together with the relationships and constraints that bind the various tables together. In effect, the DataSet is an in-memory relational structure with built-in XML support.

Notes: A DataView provides you with a dynamic view of a single set of data to which you can apply different sorting and filtering criteria, similar to the view provided by a database. However, a DataView differs significantly from a database view in that the DataView cannot be treated as a table and cannot provide a view of joined tables. You also cannot exclude columns that exist in the source table, nor can you append columns, such as computational columns, that do not exist in the source table

Notes: The figure shows the .NET data provider exception hierarchy. The .NET data providers translate database-specific error conditions into standard exception types. The database-specific error details are made available through the properties of the relevant exception object. Notice that the OleDbException class is derived from ExternalException, the base class for all COM Interop exceptions. The ErrorCode property of this object stores the COM HRESULT generated by OLE DB.

Note: 1. Atomic: All statements in a group must execute, or no statement in a group must execute. 2.

Consistent: This follows naturally from atomic a group of SQL statements must take the database from a known starting state to a known ending state. If the statements execute, the database must be at the known ending state. If the statements fail, the database must be at the known starting state.

Isolated: A group of statements must execute independently from any other statement groups being executed at the same time. If this wasnâ&#x20AC;&#x2122;t the case, it would be impossible for statement groups to be consistent. The known ending state could be altered by a code you have no control over or knowledge of. This is one of those concepts that are great in theory, but total isolation has important performance implications in the real world. In case of Isolation, a transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

Durable: Once the group of SQL statements execute, the results need to be stored in a permanent media. If the database crashes right after a group of SQL statements execute, it should be possible to restore the database state to the point after the last transaction committed.

Note: 1. Remember, open connection is required for transcation

Notes: The diagram above shows the main elements of the Data Access Application Block.

Notes: Most of these methods are overloaded. There are various ways in which these methods can be invoked.

Reference