Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
The advance computer programming may not be a piece of cake for every one, but these slides really help you to understand the concept of the programming. Database, Introduced, Technologies, Familiar, Accessing Databases, Retrieve Database Data, Dataadapter Classes, Dataset, Datareader Class, Connect to Data Sources
Typology: Study notes
1 / 15
This page cannot be seen from the preview
Don't miss anything!
♦ Chapter Overview
♦ Chapter Objectives
♦ Instructor Notes
♦ Quick Quizzes
♦ Discussion Questions
♦ Projects to Assign
♦ Key Terms
Chapter Overview
This chapter introduces databases and the .NET technologies used for accessing and storing data in database tables. A number of new namespaces collectively called ActiveX Data Objects (ADO.NET) are introduced. ADO.NET consists of a managed set of library classes that allow you to interact with databases. ADO.NET is used to retrieve and update data in databases such as those created using Microsoft Access or SQL Server.
The first part of this chapter illustrates programmatically accessing and updating databases. The focus of the last half of the chapter is on using the visual tools and wizards available with Visual Studio to create and access data stored in databases. Language-Integrated Query (LINQ) expressions are also introduced in the chapter.
Chapter Objectives
In this chapter, students will:
Instructor Notes
Explain to students that as data needs increase, text files become less viable options. Many applications revolve around reading and updating information in databases. Databases store information in records, fields, and tables. A database is a collection of records stored in a computer in a systematic way so that a computer program can consult it to answer questions. The computer programs that are used to manage and query a database are known as the database management system (DBMS).
Database Management Systems SQL Server, Oracle, DB2, and Microsoft Access are examples of DBMSs. Many DBMSs that store data in a tabular format are called relational databases. In a relational database, the rows are referred to as records and the columns are called the fields.
Data Providers ADO.NET does not provide a single set of classes that work with all types of database management systems. You can use the ADO.NET library of code to access data stored in many different proprietors’ database management systems. The ADO.NET architecture encapsulates the details of differing database structures such as Oracle, as opposed to Access, in the form of data providers.
The four data providers currently included with .NET are:
Oracle has its own unique features, just as Access does. But accessing each of the database management systems involves common sets of functionality: connecting to a database, executing commands, and retrieving results. Each of the data provider classes is encapsulated into a different namespace. The System.Data.OleDb namespace includes the classes for the Data Provider for Microsoft Access databases.
Each namespace includes classes for the following:
The two additional namespaces used with ADO.NET classes to access databases are:
Connecting to the Database A connection object is instantiated based on the type of database or type of database provider you are using. .NET includes within the System.Data.OleDb namespace the OleDbConnection class for connection. It represents an open connection. To instantiate an object of this class, specify a connection string that includes the actual database provider and the data source. The data source is the name of the database. The following is an example: string sConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” + “Data Source=member.mdb”; OleDbConnection dbConn; dbConn = new OleDbConnection(sConnection);
Retrieving Data from the Database One way to retrieve records programmatically, once connected to the database, is to issue an SQL query. The OleDbCommand class is used to hold the SQL statement used to retrieve the records.
Using the CommandText, set the SQL statement to be used for accessing the records as shown here: string sql = "Select * From memberTable Order By LastName Asc, firstName Asc;" OleDbCommand dbCmd = new OleDbCommand(); dbCmd.CommandText = sql;
Set the connection object to use the command as shown here: dbCmd.Connection = dbConn;
SQL Queries Select * From memberTable Order By LastName Asc, FirstName Asc; retrieves every row and column in the memberTable table. The asterisk (*) is used in the SQL query to specify all fields. The Asc is an abbreviation for ascending, indicating that the result of the query is to be returned with the listing in ascending order. Notice the SQL query is placed inside double quotes. To retrieve a single row or just some of the rows from the table, you add a Where clause (WHERE columnName = value) to the SQL query.
Compound expressions (AND, OR, or NOT) can be used. The relational operators used are = >, <, >=, <=, and <>. The keyword BETWEEN (with AND) can be inserted to test a specified range. Enclose a date with the # symbol for Microsoft Access and use single apostrophes as date delimiters with SQL Server database access. WHERE (aDate BETWEEN #10/12/2006# AND #10/12/2007#) ─Microsoft Access WHERE (aDate < BETWEEN ‘10/12/2006’ AND ‘10/12/2006’) ─Microsoft SQL Server
Use the SELECT statement to retrieve results from multiple tables by joining them using a common field. Instead of using a WHERE clause to identify the rows to be returned, the keywords INNER JOIN and ON are used with the two tables. SELECT memberTable.FirstName, memberTable.LastName, departmentTable.major_Name FROM memberTable INNER JOIN departmentTable ON memberTable.major_ID = departmentTable.major_ID
SQL statements can also be written to create tables or to insert, delete, and update data in table.
Processing the Data You can retrieve one record at a time in memory and process that record before retrieving another or store the entire result of the query in a temporary data structure similar to an array and disconnect from the database. A data reader class can be used to read rows of data from a database.
Retrieving Data Using a Data Reader The data reader class enables read-only access to the database. The OleDbDataReader class allows forward retrieval of data from the database. Results are returned as the query executes. You can then sequentially loop through the query results. By default, only one row is stored in memory at a time.
Command Builder Object So that you do not have to do additional SQL programming beyond the initial Select statement, an object of the OleDbCommandBuilder class is instantiated. This object automatically generates SQL statements for updates once you set the SelectCommand property of the OleDbDataAdapter class so it can only be used for datasets that map to a single database table. Instantiation of the class and setting the property are shown: private OleDbCommandBuilder cBuilder; : cBuilder = new OleDbCommandBuilder(memberDataAdap); memberDataAdap.SelectCommand = dbCmd;
Filling the Dataset Using the Data Adapter After objects are instantiated of the data adapter, dataset, and command builder classes, fill the dataset using the data adapter by specifying the name of the table to use as the data source, as follows: memberDataAdap.Fill(memberDS, “memberTable”);
The command builder automatically generates SQL statements for the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter class.You could set the value for each of these properties in exactly the same manner you set the SelectCommand property, by using a string containing a SQL statement.
To show the contents of the table and enable the user to make changes, a presentation user interface layer is needed. The table values could be displayed on the console screen or on a control in a Windows application. The grid control is especially well suited to dataset objects.
Adding a DataGrid Control to Hold the Dataset The DataGrid control creates a two-dimensional structure that enables you to navigate around in the control and make changes by inserting, deleting, or editing existing records. To tie the DataGrid object to the dataset, the SetDataBinding( ) method is used. An example is shown here: this.dataGrid1.SetDataBinding(memberDS, “memberTable”);
Updating the Database When you change the values of individual fields (columns) in the data grid, the changes are to the local copy of the database. To update the live database, those changes must be posted back to the database. You can flush the changes back up to the live database using the Update( ) method of the DataAdapter class. Write a statement as shown here: memberDataAdap.Update(memberDS, “memberTable”);
Quick Quiz
Visual Studio includes a number of features that make it easier to develop applications that access data. Data Source Configuration wizard simplifies connecting applications to a data source. Wizards guide the process, automatically generating the connection string, creating datasets and table adapter objects, and bringing the data into applications.
Add New Data Source To connect to a database, it is helpful to have the Data Sources window open on the desktop. This window shows the dataset objects available to the project. Use the Data menu to open the Data Sources window.
Begin by selecting Add New Data Source from the Data Sources window, and identify Database as the Data Source Type. A typed dataset object is created, which corresponds directly to the underlying database table(s).
Once a dataset object is created, the DataSet Designer can be used. The Dataset Designer provides a visual representation of the objects contained in the dataset object. It enables relationships to be set between tables and gives the capability of adding additional queries to an application.
New Connection Use the option New Connection if the database is not already attached. A dialog box enables the provider or name details to be changed. The Refresh button should be pressed after the server name is entered.
SQL Server The default server name is (local)\SqlExpress.
Creating SQL Server Databases From within Visual Studio, a SQL Server database can be created and populated. Display the Server Explorer Window from the View menu. Right-clicking on the Data Connection icon displays a pop-up menu with the option Create new SQL Server database. Add tables to the database by first clicking the plus sign to the left of the database name. This expands or reveals a number of database features including Database Diagrams , Tables , Views , and Stored Procedures. Right-mouse click on the Tables node in the Server Explorer window and select Add New Table.
DataGridView Control The DataGridView is intended to replace the DataGrid with additional functionality. The DataGrid is still retained and can be used, but since it is being deprecated, in order to use the DataGrid control, it must be added to the Toolbox.
Placing a DataGridView Control on the Form The easiest approach to add a DataGridView control to an application is to drag a table from the Data Sources window onto your form. Dragging a table over not only instantiates the DataGridView control, but also automatically creates TableAdapter, DataSet, BindingNavigator, and BindingSource objects. The BindingNavigator object places a strip of navigational buttons below the title bar. A TableAdapter and DataSet object is instantiated with details relating to the table brought onto the form.
Customize the DataGridView object The Edit Columns option available from the smart tag (forward arrow at the top-right corner on the DataGridView control) displays the columns available in the dataset, allowing you to remove or reorder them. Format the appearance of individual columns using the Bound Columns Properties window.
BindingNavigator control This control, which was automatically placed in the component tray when the table from the Data Sources window was dragged onto the form, provides a standardized way to move through and process the data. The user interface for the BindingNavigator control includes several buttons and a text box object.
A BindingSource control was also added. It is normally paired with the BindingNavigator control to simplify binding controls on a form to data. It does this by first attaching the BindingSource control to the data source, and then each of the individual controls placed on the form can be bound to the BindingSource control object, as opposed to the actual data source.
Much functionality is automatically programmed into the navigational tool strip. The move first, last, forward, and backward arrows work properly. If you click Save , you may run into a problem. Either an exception is thrown or nothing happens. The database from which the dataset object was built does not get updated.
TableAdapterManager In a disconnected mode, a data adapter or table adapter fills a dataset from a data source (database). If changes are made to the data, the adapter’s update method is used to send the changes back to the database. They do this using SELECT, INSERT, DELETE, and UPDATE SQL statements. The reason the Save button does not function properly is because the DELETE and UPDATE SQL statements are not automatically generated. So they are not available for the TableAdapter’s Update( ) method to use. With Visual Studio 2010, a TableAdapterManager object is generated when you create a dataset in a project.
TableAdapters TableAdapters are designer-generated components, which are not technically part of ADO.NET, but like data adapters, they provide a communication link between an application and a database. They provide all of the functionality of a data adapter. They also have a built-in connection object and enable the InsertCommand, UpdateCommand, and DeleteCommand SQL
queries to be configured manually from the Properties window. The CommandText for each of the InsertCommand, UpdateCommand, and DeleteCommand properties can be set to a SQL query.
Select the TableAdapter object in the component tray to view its properties. Once selected, SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand are displayed as properties in the Properties window. Use the DataSet Designe r to view and modify the CommandText for these properties.
DataSet Designer To start the designer, double-click a dataset in the Solution Explorer window OR right-mouse click on the dataset in the Data Sources window. Visual representations of the dataset and table adapter are presented.
In order to set the UpdateCommand and the DeleteCommand properties, first select the TableAdapter object in the DataSet Designer. Select the value box beside the UpdateCommand property, and New is revealed as an option. Once New is selected, three new rows are added below the UpdateCommand property. Open the Query Builder by clicking in the CommandText value box. SQL DELETE and UPDATE statements can be generated using the Query Builder.
Query Builder First select the table from which the data is to be retrieved. The SQL statement can be typed directly in the SQL pane, or the Diagram pane can be used to select specific columns. The Grid pane in the center is used to filter and enter parameterized expressions. For values that might be provided at run time, while the form is being displayed, an ampersand (@) is placed in front of an identifier for SQL Server databases. Access uses a question mark (?) in place of the identifier for a parameter.
Add Queries to TableAdapter Objects Additional queries, other than Fill and Update, can be added for TableAdapter objects. The initial SELECT SQL query, used by the Fill( ) method, is stored in the SelectCommand CommandText property. When other queries are added, they are stored as public methods, containing SQL statements. These methods are called like any other method or function on an object.
To add another query, right-mouse click on the TableAdapter in the DataSet Designer window and select Add Query to add the additional queries. A TableAdapter Query Configuration tool is displayed.
Connecting Multiple Tables It is best to originally select all of the tables needed by the application when you create the dataset object. However, other options are available when the dataset is created using a single table. The Query Builder can be used to add additional tables. An INNER JOIN clause is added to the SELECT statement for the TableAdapter’s SelectCommand. The graphical capabilities of the tool on the Diagram pane can be used, or an SQL statement can be typed straight into the SQL pane. Test the SQL statement using the ExecuteQuery button.
Modifying connection strings There are a number of ways to change the connection string. The XML app.config file, which is created when the connection string is saved with the application, can be changed. Another approach is to use the Settings page of the Property Designer to modify the project’s settings. Access this page through the Solution Explorer window. The Settings.settings file is located inside the Properties folder. Double-clicking on the Settings.settings file opens the application settings file. From there, click on the Value box and Browse to attach the database file.
Quick Quiz
LINQ enables you to write a query for the database using the programming language. The goal in adding LINQ to .NET was to provide a general-purpose query facility to the framework that could be used with not only relational data sources, but XML data and any class of data that implemented the IEnumerable
Query Expressions Most query expressions begin with a from clause and end with either a select or group clause. Review Table 14-9 with the students and describe each of the contextual keywords used as LINQ query operators.
Implicitly Typed Local Variables Use the keyword var so that the type that is determined from the expression.
LINQ with Databases Instead of embedding a SQL statement in a string argument or using the Query Builder to store SQL statements with the SELECT, INSERT, DELETE, or UPDATE SQL commands, you can include your query expression directly in your C# program.
Comment [ACS1]: What does “That” refer to in this question?
LINQ to SQL The LINQ to SQL is used to query SQL Server databases.
Database tables should be designed with a primary key. You should retrieve the key as one of the fields from your SQL query.
Use uppercase characters for SQL keywords.
Discussion Questions
Some interesting topics of discussion in this chapter include:
Projects to Assign
All of the Multiple Choice Exercises, Problems 1- Odd-numbered Short Answer Exercises, Problems 21- Programming Exercises, Problems 2, 6, 8, and 9
Key Terms
¾ database : collection of records stored in a computer in a systematic way, so that a computer program can consult it to answer questions ¾ database management system (DBMS) : the computer programs used to manage and query a database ¾ data providers : ADO.NET architecture encapsulates the details of differing database structures by provider for connect, execute commands, and retrieval of results from a database ¾ dataset : a cache of records retrieved from some data source that may contain one or more tables from the data source ¾ field : single piece of information, such as student ID or major; normally associated with a column in a database table ¾ foreign key : column (field) that refers to a column another table; the foreign key is used to link the two tables ¾ key : a column (field) that is used to identify a record in a database table ¾ Language-Integrated Query (LINQ) : .NET components that enables you to write queries independent of the data source
