DataReader

Last updated

In ADO.NET, a DataReader is a broad category of objects used to sequentially read data from a data source. [1] DataReaders provide a very efficient way to access data, and can be thought of as a Firehose cursor from ASP Classic, except that no server-side cursor is used. A DataReader parses a Tabular Data Stream from Microsoft SQL Server, and other methods of retrieving data from other sources.

Contents

A DataReader is usually accompanied by a Command object that contains the query, optionally any parameters, and the connection object to run the query on.

DataReader types

There is no DataReader class in ADO.NET, but there are a number of classes that implement the IDataReader interface:

DataReaders have a small footprint and good performance because each is tailor-made to the task at hand, however this makes it more difficult to write an application that can be moved from one backend data source to another. Some provider-specific DataReaders expose types used by the underlying database - for example, int values can be null in Microsoft SQL Server, but not in the .NET Framework prior to version 2.0.

Strong vs weak typing

When using a DataReader to retrieve data, the developer can choose to read field values in strongly typed manner ( example: myReader.GetString(12) ) or a weakly typed manner, returning then as System.Objects ( example:myReader.GetValue(12) ). Both approaches have their pros and cons.

Using the strongly typed retrieval methods can be more cumbersome, especially without specific knowledge of the underlying data. Numeric values in the database can translate to several .NET types: Int16, Int32, Int64, Float, Decimal , or Currency . Trying to retrieve a value using the wrong type results in an exception being thrown, which stops code from running further, and slows the application down. This is also true when you use the right type, but encounter a DbNull value ( this can be avoided by using the IsDbNull boolean function of the DataReader class ). The benefit to this retrieval method is that data validation is performed sooner, improving the probability of data correction being possible.

Weakly typed data retrieval allows for quick code writing, and allows for the data to be used in some fashion when the developer doesn't know beforehand what types will be returned. Further, with some effort, the programmer can extract the value into a variable of the proper type by using the GetFieldType or GetDataTypeName methods of the DataReader.

Common errors

A DataReader can in some cases be used in place of a DataTable, however many programmers have experienced connection bloat when following this approach. A DataReader can only be used against an (already) open database connection; that connection isn't closed until the DataReader's Dispose method is called. If an exception is thrown while the data is being processed, for example as described in Strong and weak typing , above, the Dispose method will never be called if the developer writes code explicitly declaring and disposing the DataReader without the use of a try-finally block. The C# using construct is a good way to avoid this problem, as shown below in the code example.

Sample code

Sample of accessing SQL Data using DataReader

voidDataTest(){using(SqlConnectionconn1=newSqlConnection(...)){conn1.Open();SqlCommandmycommand=newSqlCommand("select * from someTable",conn1);using(SqlDataReadermyreader=mycommand.ExecuteReader()){if(myreader!=null)while(myreader.Read())Console.WriteLine(myreader.GetValue(0).ToString()+":"+myreader.GetTypeName(0));}mycommand.Dispose();}}
usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Data.Odbc;usingMySql.Data.MySqlClient;namespaceConsoleApplication1;classProgram{staticvoidMain(string[]args){stringConn="Server=localhost;Uid=root;Pwd=thiru;Database=Employee";MySql.Data.MySqlClient.MySqlConnectionconn=newMySql.Data.MySqlClient.MySqlConnection(Conn);MySqlCommandcomm=newMySqlCommand("select * from emp",conn);conn.Open();MySqlDataReaderAda=comm.ExecuteReader();while(Ada.Read()){Console.WriteLine(Ada[0]);}}}

Related Research Articles

<span class="mw-page-title-main">PostgreSQL</span> Free and open-source object relational database management system

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transactions with atomicity, consistency, isolation, durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is supported on all major operating systems, including Linux, FreeBSD, OpenBSD, macOS, and Windows, and handles a range of workloads from single machines to data warehouses or web services with many concurrent users.

In computing, Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems. An application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

Memcached is a general-purpose distributed memory-caching system. It is often used to speed up dynamic database-driven websites by caching data and objects in RAM to reduce the number of times an external data source must be read. Memcached is free and open-source software, licensed under the Revised BSD license. Memcached runs on Unix-like operating systems and on Microsoft Windows. It depends on the libevent library.

In computer science, the Boolean is a data type that has one of two possible values which is intended to represent the two truth values of logic and Boolean algebra. It is named after George Boole, who first defined an algebraic system of logic in the mid 19th century. The Boolean data type is primarily associated with conditional statements, which allow different actions by changing control flow depending on whether a programmer-specified Boolean condition evaluates to true or false. It is a special case of a more general logical data type—logic does not always need to be Boolean.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

<span class="mw-page-title-main">Dependency injection</span> Software programming technique

In software engineering, dependency injection is a programming technique in which an object or function receives other objects or functions that it requires, as opposed to creating them internally. Dependency injection aims to separate the concerns of constructing objects and using them, leading to loosely coupled programs. The pattern ensures that an object or function that wants to use a given service should not have to know how to construct those services. Instead, the receiving 'client' is provided with its dependencies by external code, which it is not aware of. Dependency injection makes implicit dependencies explicit and helps solve the following problems:

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

Essbase is a multidimensional database management system (MDBMS) that provides a platform upon which to build analytic applications. Essbase began as a product from Arbor Software, which merged with Hyperion Software in 1998. Oracle Corporation acquired Hyperion Solutions Corporation in 2007. Until late 2005 IBM also marketed an OEM version of Essbase as DB2 OLAP Server.

In computer science, a database cursor is a mechanism that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

<span class="mw-page-title-main">Microsoft Data Access Components</span> Framework

Microsoft Data Access Components is a framework of interrelated Microsoft technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). There have been several deprecated components as well, such as the Jet Database Engine, MSDASQL, and Remote Data Services (RDS). Some components have also become obsolete, such as the former Data Access Objects API and Remote Data Objects.

In relational databases, the information schema is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information that some databases make available through non-standard commands, such as:

 => SELECT count(table_name) FROM information_schema.tables;  count   -------  99    => SELECT column_name, data_type, column_default, is_nullable  FROM information_schema.columns WHERE table_name='alpha';  column_name | data_type | column_default | is_nullable   -------------+-----------+----------------+-------------  foo | integer | | YES  bar | character | | YES    => SELECT * FROM information_schema.information_schema_catalog_name;  catalog_name   --------------  johnd  
<span class="mw-page-title-main">Navicat</span> SQL database management software

Navicat is a series of graphical database management and development software produced by CyberTech Ltd. for MySQL, MariaDB, Redis, MongoDB, Oracle, SQLite, PostgreSQL and Microsoft SQL Server. It has an Explorer-like graphical user interface and supports multiple database connections for local and remote databases. Its design is made to meet the needs of a variety of audiences, from database administrators and programmers to various businesses/companies that serve clients and share information with partners.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

An embedded database system is a database management system (DBMS) which is tightly integrated with an application software; it is embedded in the application. It is a broad technology category that includes:

Apache Empire-db is a Java library that provides a high level object-oriented API for accessing relational database management systems (RDBMS) through JDBC. Apache Empire-db is open source and provided under the Apache License 2.0 from the Apache Software Foundation.

The computer programming language, C#, introduces several new features in version 2.0. These include:

Java Database Connectivity (JDBC) is an application programming interface (API) for the Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation. It provides methods to query and update data in a database, and is oriented toward relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.

<span class="mw-page-title-main">XQuery API for Java</span> Application programming interface

XQuery API for Java (XQJ) refers to the common Java API for the W3C XQuery 1.0 specification.

In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

References

  1. "DataAdapters and DataReaders". docs.microsoft.com. Microsoft. Retrieved 4 September 2017.