If You Have Difficulty With This Tutorial,  You Might Find These Help Prepare You For This Tutorial

1] The TU Project that is used for this tutorial is found below; place it at the root of drive C:\

1] Just a quick check. Start Microsoft SQL Server or SQL Server Express. Note that our server, to which we shall connect, is NET1\SQLEXPRESS (See Below!)

2] It is our intention to connect to fill our faculty form with the Dr. Maurice Eggen information ID = 1. (See Below!)

1] Visual Studio offers many ways to connect data to forms. I am first going to walk through the technique often used by developers and then going to come back and show some of the other options.

2] If you take a look at the Server Explorer, you will see that Net1 currently has no data connectors. We shall add one shortly!

3] According to the Microsoft site:
 

4] We have a Microsoft SQL Server; we plan to connect to the TU database.

5] We have a Microsoft SQL Server; we plan to connect to the TU database. We are going to use the ADO.NET data provider as an interface between our application and the database; it acts as a middleware application.

6] ADO.NET can connect to a number of different database, but we shall concentrate on the MSSQL database. A Connection must be established between the ADO Data Provider and the database. This connection is a two-way connection because information will both come from and be sent to the database.

7] It will be the job of the ADO.NET Data Provider to supply one or more database tables to the application DataSet.

8] The Data Table Adapter shall supply one or more database tables to the application DataSet.

9] SQL Commands shall be used to control the transfer. SQL SELECT, INSERT, UPDATE, DELETE, etc. shall be the basis for the logic.

1]  The strategy used below is the quick and dirty, drag things to the form.

2] In order to create a DataSet, right mouse click on the TU project -> Select New Item..  (See Below!)

3] Select the DataSet template. Name the DataSet FacultyDataSet.xsd. Using the mouse, select/push the Add button. (See Below!)

1] The FacultyDataSet object needs a TableAdapter in order to create a connection to the database. Using the mouse, drag the TableAdapter from the Toolbox to your FacultyDataSet form. (See Below!)

1] Once you create a Connection, you can use it for numerous forms. The drop-down will contain a list of all of the previous connections. Since we have no previous connections, we must push/select the NewConnection.. button. (See Below!)

2] Select the Microsoft SQL Server data source. Using the mouse, select/push the Continue button. (See Below!)

3] Now is the time to use NET1\SQLEXPRESS; enter it for Server name: once it authenticates, you will have an opportunity to Select the TU database in the drop down box at the bottom. (See Below!)

4] Using the mouse, select/push the Test Connection button. (See Below!)

5] Using the mouse, select/push the OK button. (See Below!)

6] Using the mouse, select/push the OK button. (See Below!)

7] Using the mouse, select/push the Next button. (See Below!)

8] This single connection will enable you to access any of the tables in

1] I could have simply entered the query for Dr. Eggen's record, but I wanted to demonstrate how to use the query builder. Using the mouse, select/push the Query Builder..  button. (See Below!)

 

2] Select the Faculty Table.  Using the mouse, select/push the Add  button. (See Below!)

3] Queries often use more than one table, but this one is going to be quite simple.  Using the mouse,  select/push the Close button. (See Below!) 

4] Checking the * pulls all of the fields from the table.

5] Checking the ID will allow us to enter a range of acceptable values for the ID

6] When the filter is set to =1, only Dr. Eggen's record will be pulled from the Faculty table. .

7] As the Query Builder GUI is used, the equivalent SQL is shown in the window. We could have simply entered this query in the window at step 1. (See Below!)

8] There are a lot of equivalent forms for this query. The query I would have entered is

        SELECT First, Last, ID, Gender, SpecialtyID, About
        FROM Faculty
        WHERE ID = 1

9] The actual query builder is shown below.  It would be nice to see if this really does grab Dr. Eggen's record. Test the query. Using the mouse, select/push the Execute Query  button. (See Below!)

10] You can see the record at the bottom; it info looks great.  Using the mouse, select/push the OK  button. (See Below!)

11] Using the mouse, select/push the Finish  button. (See Below!)

12] Using the mouse, select/push the Finish  button. (See Below!)

13] We know that the Connection was successful because the Executed Query rendered Dr. Eggen's record; we have not yet used that data to populate our form. Execute the application and you will see the following: (See Below!)

1] Return to the Toolbox. Note that there are now components for the FacultyDataSet and FacultyTableAdapter. Using the mouse, drag the FacultyDataSet to the Faculty Form.  (See Below!)

2] Note that the FacultyDataSet object fell to a gray area at the bottom of the form; this enables it to be part of the form without getting in the way of the form designers.  Using the mouse, drag the FacultyTable Adapter to the Faculty Form.  (See Below!)

3]  Note that the FacultyTableAdapter object fell to a gray area at the bottom of the form; this enables it to be part of the form without getting in the way of the form designers. (See Below!)

4] It is time to insert some code in file Faculty.cs. Including the facultyDataSet1 and the facultyTableAdapter1 automatically generated function Faculty_Load. Add the code below to that function.

5]  Faculty_Load calls a function, called FillRecord. Add Function FillRecord immediately below the Faculty_Load function. The code is seen below:.

6]  If our query had selected multiple records, the Function FillRecord could be used to pace other records in the form. Our query has only one record, record 0. We are passing Rows[0] into the form. As a result, you can see the following when you execute the form:

7] As a result of the drag and drop, you can see TU Components have been automatically added to the ToolBox; this makes it easier to add the FacultyDataSet or the FacultyTableAdapter to other forms.

8] Once you create the Data Connector, it will be available to all other applications. You can see the TU connector in the Server Explorer below..

1] Using the mouse, right mouse click on the TU connection and select Delete. (See Below!)

2] I am also returning the TU application to it's initial state.

1] Using the mouse, right mouse click on the Data Connections and select Add Connection... (See Below!)

or you can hold down the Tools Menu and select Connect To Database..

2] Either entrance will enable you to enter a Server Name of  NET1\SQLEXPRESS: once it authenticates, you will have an opportunity to Select the TU database in the drop down box at the bottom. (See Below!)

3] Using the mouse, select/push the Test Connection button. (See Below!)

4] Using the mouse, select/push the OK button. (See Below!)

5] Using the mouse, select/push the OK button. (See Below!)

6] Once you create the Data Connector, it will be available to all other applications. You can see the TU Data Connections in the Server Explorer below.
 

1] Let us reconfigure the FacultyDataSet to retrieve all of the Faculty records. Using the mouse, double-click on FacultyDataSet.xsd. In order to reconfigure the SQL associated with the dataset, we need only right mouse click on the dataset and select Configure. (See Below!)

 

2]  Although we could use the Query Builder, I am simply going to modify the query in the window. Try it. Using the mouse, select/push the Finish button. (See Below!) 

 

3] When loaded, our form looks the same, but we transferred much more information from our server. Having this information in the DataSet enables us to easily add some additional functionality to our form. (See Below!)

 

Close the Faculty form.
 

1] Add btnNext and btnPrevious to your form. (See Below!)

2] We are about to use these two buttons to cycle through our records. Declare an integer counter, called Pos, at the top of your Faculty form. (See Below!)

3] The Faculty table of the TU database currently has 5 records, numbered in our facultyDataSet1 as records 0-4. I would like the btnNext too cycle through the record set from record 0, to record 1, to record 2 to record 3 to record 4 and then back to record 0. The code for btnNext_Click can be seen below. (See Below!)

4] The Faculty table of the TU database currently has 5 records, numbered in our facultyDataSet1 as records 0-4. I would like the btnPrevious too cycle through the record set from record 4, to record 3, to record 2 to record 1 to record 0 and then back to record 4. The code for btnPrevious_Click can be seen below. (See Below!)

5] This is a common solution, but one riddled with inefficiency. facultyTableAdapter1.Fill downloads the entire database table each and every time the user pushes the Previous button or the Next button.

1]  The DataGridView container offers a way to easily display a collection of objects. Select the ContactList tab from the Faculty form. Using the mouse, drag the DataGridView from the ToolBox to the tab.  (See Below!)

2]  Open the DataGridView control.  Hold down the Drop-down and select Add Project Data Source. (See Below!)

3]  Using the mouse, select the Database. Using the mouse, push/select the Next button.   (See Below!)

4] We shall use the TUConnectionString again. Using the mouse, push/select the Next button. 

5] We are going to fill the grid with all of  the Faculty data. Select the FacultyTable and check each of the fields. You can name the DataSet if you like. Using the mouse, select/push the Finish button. (See Below!)

6]  Select Dock in Parent Container. Disable Editing, Deleting, and Adding. (See Below!)

7]  When you run the form, it will look like it only somewhat works! (See Below!)

8]  Drag your mouse over some of the grid cells and you will see data. You are not seeing the data because of the default color configuration.    (See Below!)

9]  You can play around with the various component configurations until you find a combination that is palatable. Below you can see where I configured the DefaultCellStyle property.

10]  The DataGridView makes it extremely easy to display a collection of data. As long as the collection is relatively small, this is a neat tool. We did not have to display all of the fields in the table. (See Below!)

1] The TU Project, completed above, is found below.