The ADO.NET object model provides an API for accessing database systems programmatically. ADO.NET was created for the .NET framework to replace Microsoft's ActiveX Data Objects™ (ADO) technology. As will be discussed in the next section, the IDE features visual programming tools that simplify the process of using a database in your projects. While you may not need to work directly with many ADO.NET objects to develop simple applications, basic knowledge of how the ADO.NET object model works is important for understanding data access in C#.
Namespaces System.Data, System.Data.OleDb and System.Data.SqlClient
Namespace System.Data is the root namespace for the ADO.NET API. The other important ADO.NET namespaces, System.Data.OleDb and System.Data.SqlClient, contain classes that enable programs to connect with and manipulate data sourceslocations that contain data, such as a database or an XML file. Namespace System.Data.OleDb contains classes that are designed to work with any data source, whereas System.Data.SqlClient contains classes that are optimized to work with Microsoft SQL Server databases. The chapter examples manipulate SQL Server 2005 Express databases, so we use the classes of namespace System.Data.SqlClient. SQL Server Express 2005 is provided with Visual C# 2005 Express. It can also be downloaded from msdn.microsoft.com/vstudio/express/sql/default.aspx.
An object of class SqlConnection (namespace System.Data.SqlClient) represents a connection to a data sourcespecifically a SQL Server database. A SqlConnection object keeps track of the location of the data source and any settings that specify how the data source is to be accessed. A connection is either active (i.e., open and permitting data to be sent to and retrieved from the data source) or closed.
An object of class SqlCommand (namespace System.Data.SqlClient) represents a SQL command that a DBMS can execute on a database. A program can use SqlCommand objects to manipulate a data source through a SqlConnection. The program must open the connection to the data source before executing one or more SqlCommands and close the connection once no further access to the data source is required. A connection that remains active for some length of time to permit multiple data operations is known as a persistent connection.
Class DataTable (namespace System.Data) represents a table of data. A DataTable contains a collection of DataRows that represent the table's data. A DataTable also has a collection of DataColumns that describe the columns in a table. DataRow and DataColumn are both located in namespace System.Data. An object of class System.Data.DataSet, which consists of a set of DataTables and the relationships among them, represents a cache of datadata that a program stores temporarily in local memory. The structure of a DataSet mimics the structure of a relational database.
ADO.NET's Disconnected Model
An advantage of using class DataSet is that it is disconnectedthe program does not need a persistent connection to the data source to work with data in a DataSet. Instead, the program connects to the data source to populate the DataSet (i.e., fill the DataSet's DataTables with data), but disconnects from the data source immediately after retrieving the desired data. The program then accesses and potentially manipulates the data stored in the DataSet. The program operates on this local cache of data, rather than the original data in the data source. If the program makes changes to the data in the DataSet that need to be permanently saved in the data source, the program reconnects to the data source to perform an update then disconnects promptly. Thus the program does not require any active, persistent connection to the data source.
An object of class SqlDataAdapter (namespace System.Data.SqlClient) connects to a SQL Server data source and executes SQL statements to both populate a DataSet and update the data source based on the current contents of a DataSet. A SqlDataAdapter maintains a SqlConnection object that it opens and closes as needed to perform these operations using SqlCommands. We demonstrate populating DataSets and updating data sources later in this chapter.
Introduction to Computers, the Internet and Visual C#
Introduction to the Visual C# 2005 Express Edition IDE
Introduction to C# Applications
Introduction to Classes and Objects
Control Statements: Part 1
Control Statements: Part 2
Methods: A Deeper Look
Classes and Objects: A Deeper Look
Object-Oriented Programming: Inheritance
Polymorphism, Interfaces & Operator Overloading
Graphical User Interface Concepts: Part 1
Graphical User Interface Concepts: Part 2
Strings, Characters and Regular Expressions
Graphics and Multimedia
Files and Streams
Extensible Markup Language (XML)
Database, SQL and ADO.NET
ASP.NET 2.0, Web Forms and Web Controls
Networking: Streams-Based Sockets and Datagrams
Searching and Sorting
Appendix A. Operator Precedence Chart
Appendix B. Number Systems
Appendix C. Using the Visual Studio 2005 Debugger
Appendix D. ASCII Character Set
Appendix E. Unicode®
Appendix F. Introduction to XHTML: Part 1
Appendix G. Introduction to XHTML: Part 2
Appendix H. HTML/XHTML Special Characters
Appendix I. HTML/XHTML Colors
Appendix J. ATM Case Study Code
Appendix K. UML 2: Additional Diagram Types
Appendix L. Simple Types