Lesson 11: Developing Database Applications

Objectives
What will you learn
What is ADO .Net What are .Net Data Providers How to connect to a database using a wizard How to retrieve data from a database using a wizard

Lesson 11: Developing Database Applications

Introduction
ADO.NET provides access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB Applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data

Lesson 11: Developing Database Applications

Introduction
ADO .NET Namespaces System.Data - The root namespace for the ADO .NET API System.Data.OleDb - It contains classes that are designed to work with any data source System.Data.SqlClient - Contains classes that are optimized to work with Microsoft SQL Server databases

Lesson 11: Developing Database Applications

.NET Data Providers

.NET Framework Data Provider for SQL Server - for Microsoft SQL Server version 7.0 or later .NET Framework Data Provider for OLE DB - for data sources exposed using OLE DB .NET Framework Data Provider for ODBC - for data sources exposed using ODBC .NET Framework Data Provider for Oracle - for Oracle data sources, the .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later

Lesson 11: Developing Database Applications

Connecting to Data Sources

ADO .Net Architecture .Net Data Provider Database Connection DataAdapter Command DataReader
Lesson 11: Developing Database Applications 6

DataSet Data Consumers Win Forms Web Forms

Connecting to Data Sources

Connection Class In ADO.NET you use a Connection object to connect to a specific data source. For example, to connect to Microsoft SQL Server version 7.0 or later, use the SqlConnection object of the .NET Framework Data Provider for SQL Server. Use the corresponding Connection object to connect to a data source.

Lesson 11: Developing Database Applications

Connecting to Data Sources

The Data Source Configuration Wizard Using the Data Source Configuration Wizard is an easy way of connecting applications to databases When the wizard is finished, data will be available in the Data Sources Window and is ready for dragging it onto forms

Lesson 11: Developing Database Applications

Connecting to Data Sources

Create a new project: Select New then Project in the File menu Select Windows Application and click OK

Lesson 11: Developing Database Applications

Connecting to Data Sources

Create a connection to a database: Select Add New Data Source on the Data menu

Lesson 11: Developing Database Applications

10

Connecting to Data Sources

Select Database on the Choose a Data Source Type page then select Next

Lesson 11: Developing Database Applications

11

Connecting to Data Sources

Select New Connection to configure a new data connection if there are no connections yet

Lesson 11: Developing Database Applications

12

Connecting to Data Sources

Check Yes if you want to save the connection string then click Next

Lesson 11: Developing Database Applications

13

Connecting to Data Sources

Expand the Tables node and select the tables you want to add to your Data Source and then click Finish

Lesson 11: Developing Database Applications

14

Connecting to Data Sources

Add controls to the form: Drag the table nodes you want from the Data Sources window onto the form

Lesson 11: Developing Database Applications

15

Connecting to Data Sources

Run the application: Press F5 to run the application. The form should show the data from the table you just dragged.

Lesson 11: Developing Database Applications

16

Connecting to Data Sources

Using the Dataset Designer After this lesson, you will be able to create tables based on tables in a database without using a wizard The Dataset Designer is a tool for creating and editing typed datasets like TableAdapters, TableAdapter Queries and Data Tables

Lesson 11: Developing Database Applications

17

Connecting to Data Sources

Add a new dataset item to the project: Click Add New Item on the Project menu Click DataSet in the Templates box

Lesson 11: Developing Database Applications

18

Connecting to Data Sources

The Dataset you added will be opened in the Dataset Designer

Lesson 11: Developing Database Applications

19

Connecting to Data Sources

Create a connection to the database: Click Server Explorer on the View menu Click the Connect to Database button in the Server Explorer Add a new connection to your database

Lesson 11: Developing Database Applications

20

Connecting to Data Sources

Add tables in the dataset: Drag tables from the Server Explorer and drop it in the dataset designer You should see the ERD of the tables in the dataset designer

Lesson 11: Developing Database Applications

21

Connecting to Data Sources

Creating Data- bound controls Data bound controls are controls which can easily bind with data components Some examples of controls that can bind data are DataGrids, ListBoxes and ComboBoxes

Lesson 11: Developing Database Applications

22

Connecting to Data Sources

Create a connection: Create a new Windows Application Project Connect it to a database Expand the Tables node and select tables in the Choose Your Database Objects dialog box and press finish You should now see the table nodes in the Data Sources Window that you just selected
Lesson 11: Developing Database Applications 23

Connecting to Data Sources

Create a Data- bound control: Drag a ComboBox, a ListBox, or a DataGridView from the Toolbox Tab and drop it in the form. This will be your data- bound control In the Data Sources Window, drag the table node you want to view and drop it in the control
Lesson 11: Developing Database Applications 24

Connecting to Data Sources

Adding a navigator: For you to be able to navigate through the results, drag a BindingNavigator from Data in the Toolbox Tab and drop it in the form Change the BindingSource property in the Properties Window so that your data binds to your control
Lesson 11: Developing Database Applications 25

Connecting to Data Sources

Run the application: Press F5 to run the application. You should be able to see the data in the table you selected in your data- bound control. Also, you should be able to navigate through the results by pressing the Move Next button

Lesson 11: Developing Database Applications

26

Connecting to Data Sources

Creating a TableAdapter with multiple queries TableAdapters are objects that are used to simplify the interaction of data objects to a database It encapsulates the objects needed to communicate with a database and presents methods to access the data

Lesson 11: Developing Database Applications

27

Connecting to Data Sources

Use the Dataset Designer: Create a new project and connect it to a database Right-click the Dataset in the Data Sources window Choose Edit DataSet with Designer

Lesson 11: Developing Database Applications

28

Connecting to Data Sources

Add your queries: Add a query to the TableAdapter by dragging a Query from the DataSet tab of the Toolbox onto the table you want to query The TableAdapter Query Configuration Wizard should appear

Lesson 11: Developing Database Applications

29

Connecting to Data Sources

Choose what Command type you want to use

Lesson 11: Developing Database Applications

30

10

Connecting to Data Sources

Choose what type of query you want to use

Lesson 11: Developing Database Applications

31

Connecting to Data Sources

You can add conditions in your query

Lesson 11: Developing Database Applications

32

Connecting to Data Sources

You can change the name of your method

Lesson 11: Developing Database Applications

33

11

Connecting to Data Sources

Execute the query: Drag the table node you want from the Data Sources window to the form To call the method you created, you should have a code that looks like this: <table_adapter_name>.<method_name>(<table_ name>,[<parameter>]);

Lesson 11: Developing Database Applications

34

Connecting to Data Sources

Press F5 to run the application. The grid should be filled with your query

Lesson 11: Developing Database Applications

35

Connecting to Data Sources

Reading XML Data into a DataSet XML Data can be read into a DataSet The output can be a table consisting of all the attributes of the given XML data and its values In the following example, assume that the given XML file looks like this:

Lesson 11: Developing Database Applications

36

12

Connecting to Data Sources

<My_XML_Table> <person> <lname>Dominador</lname > <fname>Bogart</fname> <phone>4332533</phone> <address>10932 Bigge Rd.</address> <city>Quezon City</city> <zip>94025</zip> </person> </My_XML_Table>

The <person> is the Table Name Attributes like <lname> and <fname> are the Table columns

Lesson 11: Developing Database Applications

37

Connecting to Data Sources

Create the XML file that will be read into the dataset: Choose Add New Item from the Project Menu Select XML File and click Add The XML file should load into the designer and should be ready for edit Type the previous code into the editor below the XML declaration

Lesson 11: Developing Database Applications

38

Connecting to Data Sources

Adding an XML file

Lesson 11: Developing Database Applications

39

13

Connecting to Data Sources

The following codes will read data from the XML file to the Dataset myDataSet.ReadXml(<filePath>); dataGridView1.DataSource = myDataSet; dataGridView1.DataMember = "person"; Note that the DataSource is the dataset and the DataMember is the table name

Lesson 11: Developing Database Applications

40

Connecting to Data Sources

Creating Lookup Tables A lookup table is used to display information from one table based on the value of a foreign-key field in another table For example, there are three tables, Publisher, Publishes and Product. The Publishes table consists of a pubID which is a foreign key pointing to the Publisher and a prodID which is a foreign key pointing to the Product. Since the product name is in the Product table and the publisher name is in the Publisher table, and you are presenting data from the Publishes table, you need to create a lookup table which takes the pubID and prodID so that you can output the product name and its corresponding publisher.
Lesson 11: Developing Database Applications 41

Connecting to Data Sources

Publisher, Publishes, Product Relationship

Lesson 11: Developing Database Applications

42

14

Connecting to Data Sources

Create the Data Source: Connect to SampleDB database Select the Publisher, Publishes, and Product tables, and then click Finish The DataSet should be added to your project and the three tables should appear in the Data Sources window

Lesson 11: Developing Database Applications

43

Connecting to Data Sources

Create data-bound controls on the form: Expand the Product node in the Data Sources window Change the drop type of the related Publishes table to Details by selecting Details from the control list on the Orders node

Lesson 11: Developing Database Applications

44

Connecting to Data Sources

Expand the related Publishes node and change the prodID and pubID columns drop type to a combo box by selecting ComboBox from the control list on the prodID and pubID nodes

Lesson 11: Developing Database Applications

45

15

Connecting to Data Sources

Drag the related Publishes node from the Data Sources window onto Form1

Lesson 11: Developing Database Applications

46

Connecting to Data Sources

Create the lookup table functionality: Drag the main Product node from the Data Sources window directly onto the prodID combo box on Form1 Drag the main Publisher node from the Data Source window directly onto the pubID combo box on Form 1 Run the application: Press F5 The output in the prodID combo box should display the Product name and the output in the pubID combo box should display the Publisher name that corresponds to the given Product
Lesson 11: Developing Database Applications 47

16