Database, SQL and ADO.NET

  • In this article you will learn:

    • The relational database model.
    • To write basic database queries in SQL.
    • To add data sources to projects.
    • To use the IDE’s drag-and-drop capabilities to display database tables in applications.
    • To use the classes of namespaces System.Data and System.Data.SqlClient to manipulate databases.
    • To use ADO.NET’s disconnected object model to store data from a database in local memory.
    • To create XML documents from data sources
  • Introduction:

    • A database is an organized collection of data. Many strategies exist for organizing data to facilitate easy access and manipulation. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data for many users. Database management systems allow access to and storage of data independently of the internal representation of the data.
    • Today’s most popular database systems are relational databases. A language called SQLpronounced "sequel," or as its individual lettersis the international standard language used almost universally with relational databases to perform queries (i.e., to request information that satisfies given criteria) and to manipulate data
    • Some popular relational database management systems (RDBMS) are Microsoft SQL Server, Oracle, Sybase, IBM DB2 and PostgreSQL
    • A programming language connects to and interacts with a relational database via database interface software that facilitates communication between a database management system and a program. C# programs communicate with databases and manipulate their data through ADO.NET. The current version of ADO.NET is 2.0
  • Relational Databases:

    • A relational database is a logical representation of data that allows the data to be accessed independently of its physical structure. A relational database organizes data in tables
    • Tables are composed of rows and columns in which values are stored. The ID column of each row in this table is the table’s primary key a column (or group of columns) in a table that requires a unique value that cannot be duplicated in other rows. This guarantees that a primary key value can be used to uniquely identify a row. A primary key that is composed of two or more columns is known as a composite key
    • A foreign key is a column in this table that matches the primary key column in another table
    • Foreign keys can be specified when creating a table. A foreign key helps maintain the Rule of Referential Integrityevery foreign key value must appear as another table’s primary key value
  • ADO.NET Object Model:

    • 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#.
    • 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 sources locations 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
    • 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 data 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
  • Programming with ADO.NET: Extracting Information from a Database:

  • A connection string specifies the path to a database file on disk, as well as some additional settings that determine how to access the database. Saving the connection string in a configuration file makes it easy to change the connection settings at a later time
  • A file with the .xsd extension is an XML Schema document, which specifies the structure of a set of XML documents. The IDE uses an XML Schema document to represent a DataSet’s structure, including the tables that comprise the DataSet and the relationships among them
  • How Data Binding Works:

  • The technique through which GUI controls are connected to data sources is known as data binding. The IDE allows controls, such as a DataGridView, to be bound to a data source, such as a DataSet that represents a table in a database. Any changes you make through the application to the underlying data source will automatically be reflected in the way the data is presented in the data-bound control (e.g., the DataGridView). Likewise, modifying the data in the data-bound control and saving the changes updates the underlying data source
  • A DataSet’s structure can be determined at execution time or at design time. An untyped DataSet’s structure (i.e., the tables that comprise it and the relationships among them) is determined at execution time based on the result of a specific query. Tables and column values are accessed using indices into collections of DataTables and DataRows, respectively. The type of each piece of data in an untyped DataSet is unknown at design time. The other type is created by the IDE at design time as a strongly typed DataSet (a derived class of DataSet) contains objects of classes derived from DataTable that represent the tables in the database. Strongly Typed DataSets provides properties corresponding to the objects whose names match those of the underlying tables

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s