CS 112D Lecture Notes - Database Basics with VB.NET (Chapter 1) - Craig Persiko

SQL Server Data Types

The data types used in SQL Server have different names than in Access or VB. Here are the most important ones for us:

boolean - like VB's Boolean
datetime - like VB's Date
float - like VB's Double
int - like VB's Integer
varchar - like VB's String

Building a Database in Visual Studio .NET

You can build a database and its tables, etc. using VS.NET's Server Explorer, as outlined in your textbook.
There is a typo on page 12: column name is Email, not E-mail.
You can create database diagrams using Server Explorer or Visio. Visio is more poweful, but it only comes with the Enterprise Architect edition of VS.NET, so we won't use it in this class.

Database Concept Review

Database - a collection of Tables, Queries, etc. containing all the data relating to a particular area (such as one company's records)
Table - stores a category of data, e.g. Customer
Field - one piece of data from a table, e.g. First Name
Record - one value for each field, e.g. one customer's data from the table
Primary Key - field that acts as unique record identifier (e.g. ID)
Index - speeds up sorting by one field (or a combo of fields)
Relationships (e.g. One-to-One, One-to-Many, Many-to-Many) - when one field in one table corresponds to a field in another table. Many-to-Many relationships require a juncture table.
Referential Integrity - ensures corresponding records exist in related tables. May include Cascading Updates and/or Cascading Deletes
Normalization - guidelines to ensure flexibility of data use and no repetition of information
Query - a piece of SQL code executed on a database to modify or retrieve data.

Using VB.NET to connect to a database

Before .NET, a Data Control was used in Visual Basic to coordinate data connections with GUI controls. The Data control has been removed for .NET, in favor of automatically generated object code (which is more flexible)
In VB.NET, objects are used to connect to a database. We will study several of these classes in detail, but following is a brief overview of the major classes we'll use:
SqlConnection - a class which maintains a connection to a database
SqlDataAdapter - a class which retrieves data from a SqlConnection
DataReader - a class which stores and provides read-only access to data retreived from a SqlDataAdapter.
DataSet - Like a DataReader, but also includes the ability to change the data.
The Fill method of the DataAdapter must be called to link it to a DataReader or DataSet.
Once objects of the above 3 classes have been setup, a Windows control can be bound to a DataReader or DataSet, automatically displaying the data that's been retrieved.
DataGrid - a VB control which provides a grid view of all the data stored in a table.
To bind a DataGrid to a DataSet, set the DataGrid's DataSource property to the name of the DataSet.
The DataGrid has a DataMember property which stores the name of the table to be displayed.
Step-by-step instructions for viewing data in a DataGrid start on page 36 of your textbook.
There is a typo on page 37: in the middle of the page under number 3, the second line of code should read:
ByVal e As System.EventArgs) Handles _
Step-by-step instructions for viewing data one record at a time in TextBoxes start on page 38 of your textbook.
There is also a typo on page 39 in the middle of the page under number 3: the last line of the Sub should read:
Me.BindingContext(DsCustomer1, "tblCustomer").Position -= 1
To bind a TextBox to a DataSet, go to the (DataBindings) section and set the Text property to the field you wish to be displayed. Or in code, call the Add method of the DataBindings property of the TextBox.
To navigate between records in a DataSet, you need to call your form's BindingContext property, which has a Position property. Add to the position to move forward in the DataSet.

Modifying Records using Data-Aware Controls

Using code such as described above, data may be changed, but those changes will not be saved to the database; the DataSet in memory is the only thing that gets changed.
To save changes to a DataSet, the DataAdapter's Update method must be called with that DataSet as the argument.
Changes to the current record aren't saved unless the BindingContext's current edit is finished. This happens automatically if you navigate to a new record, or you can call EndCurrentEdit() on the BindingContext to make it end the current edit. Then calling Update on the DataAdapter will save the latest changes.
To create a new record in a DataSet, use the AddNew() method of the form's BindingContext property. Moving off the new record should save changes, as will a call to the DataAdapter's Update method.
To cancel an edit of a new or existing record, use the CancelCurrentEdit() method ofthe BindingContext.
To delete a record from a DataSet, use the RemoveAt method of the BindingContext, specifying the record number (Position) as the argument.

VB Syntax Note: With ... End With blocks

When you're calling a lot of methods of a particular object, you can use a With ... End With block to specify set a default object for all method calls. Here is an example. The following code will delete the current record from a DataSet from the Customer table:

Me.BindingContext(DsCustomer1, "tblCustomer").RemoveAt(Me.BindingContext(DsCustomer1, "tblCustomer").Position)

And here is the same code using a With...End With block:

With Me.BindingContext(DsCustomer1, "tblCustomer")
  .RemoveAt(.Position)
End With

Validating Data Entry

Whenever changes are being made to data, you must ensure that the data is in the correct format and follows any applicable business rules. This validation can be done in the application, and/or in the database.
To validate data in your VB application, before changes are sent to the database, handle the RowUpdating event of the DataAdapter. It is called just before changes are made to a row of data via that DataAdapter.
The RowUpdating event has a parameter that's a SqlRowUpdatingEventArgs object. That object can be used to find information about the row that is being updated, such as the values of its fields.
To abort an update operation from the RowUpdating event, use the following commands on the SqlRowUpdatingEventArgs object (assume it's called "e"):
```
  e.Status = UpdateStatus.SkipCurrentRow
  e.Row.RejectChanges()
  
```
Note that there is a typo at the bottom of page 45 of the textbook: SkipCurrentRow should be as shown above, without parentheses.
More info on event handlers (such as handling events from an object that instantiated in code) is available on this website from my CS 112B class
Validation should also be done at the database level, as that's the only place from which you can guarantee the data is in the correct format, no matter how it was entered. Examples of such validation include a field's Allow Nulls checkbox, and enforced referential integrity.

Return to the main CS 112D page