|
 Practical Learning: Introducing Views
|
|
- Start Microsoft Visual Basic and create a new Windows Application named
YugoNationalBank1
- In the Solution Explorer, right-click Form1.vb and click Rename
- Type Central.vb and press Enter
- Double-click the middle of the form and implement the Load event as
follows:
Imports System.Data.SqlClient
Public Class Central
Friend Sub CreateDatabase()
Dim strAction As String
Dim Command As SqlCommand = Nothing
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Integrated Security='SSPI';")
strAction = "IF EXISTS ( " & _
"SELECT name " & _
"FROM sys.databases " & _
"WHERE name = N'YugoNationalBank1') " & _
"DROP DATABASE YugoNationalBank1; " & _
"CREATE DATABASE YugoNationalBank1"
Command = New SqlCommand(strAction, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A database named YugoNationalBank1 " & _
"has been created.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='YugoNationalBank1'; " & _
"Integrated Security='SSPI';")
strAction = "CREATE TABLE dbo.AccountTypes( " & _
"AccountTypeID int Identity(1,1) NOT NULL, " & _
"AccountType nvarchar(40) NOT NULL, " & _
"Notes ntext NULL, " & _
"CONSTRAINT PK_AccountTypes PRIMARY " & _
" KEY (AccountTypeID));"
Command = New SqlCommand(strAction, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named AccountTypes " & _
"has been added to the database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='YugoNationalBank1'; " & _
"Integrated Security='SSPI';")
strAction = "CREATE TABLE dbo.Employees( " & _
"EmployeeID int identity(1,1) NOT NULL, " & _
"EmployeeNumber char(6), " & _
"FirstName nvarchar(32), " & _
"LastName nvarchar(32) NOT NULL, " & _
"Title nvarchar(50), " & _
"CanCreateNewAccount bit, " & _
"HourlySalary nvarchar(50), " & _
"Username nvarchar(20), " & _
"Password nvarchar(20), " & _
"EmailAddress nvarchar(100), " & _
"Notes ntext, " & _
"CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));"
Command = New SqlCommand(strAction, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named Employees has " & _
"been added to the database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='YugoNationalBank1'; " & _
"Integrated Security='SSPI';")
strAction = "CREATE TABLE dbo.Customers( " & _
"CustomerID int Identity(1,1) NOT NULL, " & _
"EmployeeID int Constraint FK_Employee " & _
" References Employees(EmployeeID), " & _
"DateCreated nvarchar(50), " & _
"AccountTypeID int Constraint FK_TypeOfAccount " & _
" References AccountTypes(AccountTypeID), " & _
"AccountNumber nvarchar(12), " & _
"CustomerName nvarchar(50) NOT NULL, " & _
"Address nvarchar(100), " & _
"City nvarchar(50), " & _
"State nvarchar(50), " & _
"ZIPCode nvarchar(50), " & _
"AccountStatus nvarchar(50), " & _
"Username nvarchar(20), " & _
"Password nvarchar(20), " & _
"EmailAddress nvarchar(100), " & _
"Notes ntext, " & _
"CONSTRAINT PK_Customers PRIMARY KEY (CustomerID));"
Command = New SqlCommand(strAction, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named Customers has " & _
"been added to the database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='YugoNationalBank1'; " & _
"Integrated Security='SSPI';")
strAction = "CREATE TABLE dbo.AccountsTransactions( " & _
"AccountTransactionID int identity(1, 1) NOT NULL, " & _
"EmployeeID int Constraint FK_Clerk " & _
" References Employees(EmployeeID), " & _
"CustomerID int Constraint FK_Depositor " & _
" References Customers(CustomerID) NOT NULL, " & _
"TransactionDate nvarchar(50), " & _
"TransactionType nvarchar(50), " & _
"CurrencyType nvarchar(50), " & _
"DepositAmount nvarchar(50), " & _
"WithdrawalAmount nvarchar(50), " & _
"ChargeAmount nvarchar(50), " & _
"ChargeReason nvarchar(50), " & _
"Balance money, " & _
"Notes ntext, " & _
"CONSTRAINT PK_AccountTransactions PRIMARY KEY " & _
" (AccountTransactionID));"
Command = New SqlCommand(strAction, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named AccountTransactions " & _
"has been added to the database.")
End Using
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strTimesheets As String = "CREATE TABLE dbo.Timesheets ( " & _
"TimesheetID int identity(1, 1) NOT NULL, " & _
"EmployeeNumber nvarchar(5), " & _
"StartDate nvarchar(50), " & _
"TimesheetCode nvarchar(15), " & _
"Week1Monday nvarchar(6), " & _
"Week1Tuesday nvarchar(6), " & _
"Week1Wednesday nvarchar(6), " & _
"Week1Thursday nvarchar(6), " & _
"Week1Friday nvarchar(6), " & _
"Week1Saturday nvarchar(6), " & _
"Week1Sunday nvarchar(6), " & _
"Week2Monday nvarchar(6), " & _
"Week2Tuesday nvarchar(6), " & _
"Week2Wednesday nvarchar(6), " & _
"Week2Thursday nvarchar(6), " & _
"Week2Friday nvarchar(6), " & _
"Week2Saturday nvarchar(6), " & _
"Week2Sunday nvarchar(6), " & _
"Notes ntext, " & _
"CONSTRAINT PK_Timesheets PRIMARY KEY (TimesheetID));"
Command = New SqlCommand(strTimesheets, Connect)
Connect.Open()
Command.ExecuteNonQuery()
msgbox("A table named Timesheets has been created.")
End Using
End Sub
Private Sub Central_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
CreateDatabase()
End Sub
End Class
|
- Execute the application to create the database
- Close the form and return to your programming environment
- To create a data source, on the main menu, click Data -> Add New Data
Source...
- In the first page of the wizard, make sure Database is selected and
click Next
- In the combo box
- If you see a YugoNationalBank1, select it
- If you do not have YugoNationalBank1, click New
Connection... In the Server combo box, select the server or type
(local). In the Select Or Enter A Database Name combo box,
select YugoNationalBank1. Click Test Connection. Click OK twice. In the
Data Source Configuration Wizard, make sure the new connection is
selected and click Next. Change the Connection String to
csYugoNationalBank and click Next. Click the check box of Tables. Change the DataSet Name to
DsYugoNationalBank
- Click Finish
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to AccountTypes and click Add
- From the Data Sources window, drag the AccountTypes node and drop it on the form
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| DataGridView |
|
DgvProperties |
Anchor: Top, Bottom, Left, Right |
| Button |
Close |
BtnClose |
Anchor: Bottom, Right |
|
- Double-click the Close button and implement its even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnAccountTypes
Text: Account Types...
- Double-click the Account Types button and implement its event as
follows:
Private Sub Central_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
' CreateDatabase()
End Sub
Private Sub btnAccountTypes_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnAccountTypes.Click
Dim Types As AccountTypes = New AccountTypes
Types.ShowDialog()
End Sub
|
- Execute the application and open the Account Types form
- Create the following records:
| AccountType |
| Saving |
| Checking |
| Certificate of Deposit |
|
 |
- Close the forms and return to your programming environment
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to Employees and click Add
- In the Data Sources window, click Employees and click the arrow on its
right side to drop the combo box
- Select Details
- Drag the Employees node and drop it on the form
- Design the form as follows:
- Double-click the Close button and implement its even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnEmployees
Text: Employees...
- Double-click the Account Types button and implement its event as
follows:
Private Sub btnEmployees_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnEmployees.Click
Dim Staff As Employees = New Employees()
Staff.ShowDialog()
End Sub
|
- Execute the application and open the Employees form
- Create the following records:
- Close the forms and return to your programming environment
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to Timesheet and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Employee #: |
|
|
| MaskedTextBox |
|
TxtEmployeeNumber |
Mask: 00000 |
| Label |
. |
LblEmployeeName |
|
| Label |
Start Date: |
|
|
| DateTimePicker |
|
DtpStartDate |
|
| Label |
End Date: |
|
|
| Label |
. |
LblEndDate |
|
| Label |
Mon |
|
|
| Label |
Tue |
|
|
| Label |
Wed |
|
|
| Label |
Thu |
|
|
| Label |
Fri |
|
|
| Label |
Sat |
|
|
| Label |
Sun |
|
|
| Label |
Week 1: |
|
|
| TextBox |
0.00 |
TxtWeek1Monday |
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek1Tuesday |
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek1Wednesday |
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek1Thursday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek1Friday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek1Saturday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek1Sunday
|
TextAlign: Right |
| Label |
Week 2: |
|
|
| TextBox |
0.00 |
TxtWeek2Monday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek2Tuesday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek2Wednesday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek2Thursday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek2Friday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek2Saturday
|
TextAlign: Right |
| TextBox |
0.00 |
TxtWeek2Sunday
|
TextAlign: Right |
| Label |
Notes |
|
|
| TextBox |
|
TxtNotes |
Multiline: true |
| Button |
Submit |
BtnSubmit |
|
| Button |
Reset |
BtnReset |
|
| Button |
Close |
BtnClose |
|
|
- Right-click the form and click View Code
- In the Class Name combo box, select (TimeSheet Events)
- In the Method Name combo box, select Load and make changes as follows:
Imports System.Data.SqlClient
Public Class Timesheet
Dim EmployeeID As Integer
Dim IsNewRecord As Boolean
Dim ValidTimeSheet As Boolean
Dim StrTimeSheetCode As String
Private Sub Timesheet_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
EmployeeID = 0
IsNewRecord = True
ValidTimeSheet = False
StrTimeSheetCode = ""
End Sub
End Class
|
- In the Class Name combo box, select TxtEmployeeNumber
- In the Method Name combo box, select Leave and implement the even as follows:
Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtEmployeeNumber.Leave
If TxtEmployeeNumber.Text = "" Then
ValidTimeSheet = False
Exit Sub
End If
Dim strSelect As String = "SELECT * FROM Employees " & _
"WHERE EmployeeNumber = '" & _
TxtEmployeeNumber.Text & "';"
Dim Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Database='YugoNationalBank1';" & _
"Integrated Security=true")
Dim cmdDatabase As SqlCommand = New SqlCommand(strSelect, Connect)
Dim dsEmployees As DataSet = New DataSet
Dim sda As SqlDataAdapter = New SqlDataAdapter
sda.SelectCommand = cmdDatabase
sda.Fill(dsEmployees)
Try
Dim recEmployee As DataRow = dsEmployees.Tables(0).Rows(0)
If recEmployee.IsNull("EmployeeNumber") Then
ValidTimeSheet = False
Throw New System.IndexOutOfRangeException("Bad Employee Number!")
Else
ValidTimeSheet = True
EmployeeID = CType(recEmployee("EmployeeID"), Integer)
Dim strFullName As String = _
CStr(recEmployee("FirstName")) & _
" " & CStr(recEmployee("LastName"))
LblEmployeeName.Text = "Welcome " & strFullName
End If
Catch Exc As IndexOutOfRangeException
MsgBox("There is no employee with that number!")
ValidTimeSheet = False
LblEmployeeName.Text = ""
TxtEmployeeNumber.Text = ""
End Try
DtpStartDate.Value = Date.Today
TxtWeek1Monday.Text = "0.00"
TxtWeek1Tuesday.Text = "0.00"
TxtWeek1Wednesday.Text = "0.00"
TxtWeek1Thursday.Text = "0.00"
TxtWeek1Friday.Text = "0.00"
TxtWeek1Saturday.Text = "0.00"
TxtWeek1Sunday.Text = "0.00"
TxtWeek2Monday.Text = "0.00"
TxtWeek2Tuesday.Text = "0.00"
TxtWeek2Wednesday.Text = "0.00"
TxtWeek2Thursday.Text = "0.00"
TxtWeek2Friday.Text = "0.00"
TxtWeek2Saturday.Text = "0.00"
TxtWeek2Sunday.Text = "0.00"
Connect.Close()
End Sub
|
- In the Class Name combo box, select BtnReset button
- In the Method Name combo box, select Click and implement the event as follows:
Private Sub BtnReset_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnReset.Click
TxtWeek1Monday.Text = "0.00"
TxtWeek1Tuesday.Text = "0.00"
TxtWeek1Wednesday.Text = "0.00"
TxtWeek1Thursday.Text = "0.00"
TxtWeek1Friday.Text = "0.00"
TxtWeek1Saturday.Text = "0.00"
TxtWeek1Sunday.Text = "0.00"
TxtWeek2Monday.Text = "0.00"
TxtWeek2Tuesday.Text = "0.00"
TxtWeek2Wednesday.Text = "0.00"
TxtWeek2Thursday.Text = "0.00"
TxtWeek2Friday.Text = "0.00"
TxtWeek2Saturday.Text = "0.00"
TxtWeek2Sunday.Text = "0.00"
IsNewRecord = True
End Sub
|
- In the Class Name combo box, select DtpStartDate
|
To implement the electronic time, we will use two pieces of information
are required: an employee's number
and a starting period. After an employee has opened a time sheet:
- The employee must first provide an employee number, which we will check in
the Employees table. If the employee
provides a valid employee number, we can continue with the time sheet. If
the employee number is invalid, we will let the user know and we cannot
continue with the time sheet
- After the employee has provided a valid employee number, we will request
the starting period. After entering a (valid) date, we will check the time.
If there is a record that holds both the employee number and the start date,
this means that the employee had previously worked on a time sheet and we
will open that existing time sheet.
After the the employee or contractor has entered a valid
employee number and a start date, we will create a number called a time sheet
code, represented in the TimeSheet as the TimeSheetCode column. This number is
created as follows:
0000000000000
The first 5 digits represent the employee's number. The
second 4 digits represent the year of the start date. The next 2 digits
represent the month, and the last 2 digits represent the day. This number must
be unique so that there would not be a duplicate number throughout the time
sheet.
To make sure the value of the TimeSheetCode is unique for
each record, after the employee has provided a valid employee number and a start
date, we will create the time sheet code and check if that number exists in the
TimeSheet table already:
- If that number exists already, this means that the employee has previously
worked on that time sheet and he or she simply wants to verify or update it.
We will then open the time values for that record and let the user view or
change it
- If there is no record
with the specified time sheet code, we will conclude that the employee is working
on a new time sheet
|
- In the Method Name combo box, select CloseUP and
implement the event as follows:
Private Sub DtpStartDate_CloseUp(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles DtpStartDate.CloseUp
LblEndDate.Text = DtpStartDate.Value.AddDays(14).ToString()
If TxtEmployeeNumber.Text.Equals("") Then
ValidTimeSheet = False
Exit Sub
End If
Dim StrMonth As String, StrDay As String
Dim IntMonth As Integer, IntDay As Integer
Dim StartDate As DateTime
StartDate = DtpStartDate.Value
IntMonth = StartDate.Month
IntDay = StartDate.Day
If IntMonth < 10 Then
StrMonth = StartDate.Year & "0" & CStr(IntMonth)
Else
StrMonth = StartDate.Year & CStr(IntMonth)
End If
If IntDay < 10 Then
StrDay = StrMonth & "0" & CStr(IntDay)
Else
StrDay = StrMonth & CStr(IntDay)
End If
strTimeSheetCode = TxtEmployeeNumber.Text & StrDay
Dim ConnectTimeSheet As SqlConnection = Nothing
Dim StrSQL As String = _
"SELECT * FROM dbo.Timesheets WHERE TimeSheetCode = '" & _
StrTimeSheetCode & "';"
ConnectTimeSheet = _
New SqlConnection("Data Source=(local); " & _
"Database='YugoNationalBank1';" & _
"Integrated Security=true")
Dim CmdTimeSheet As SqlCommand = _
New SqlCommand(StrSQL, ConnectTimeSheet)
Dim dsTimeSheet As DataSet = New DataSet("TimeSheetSet")
Dim sdaTimeSheet As SqlDataAdapter = New SqlDataAdapter
sdaTimeSheet.SelectCommand = CmdTimeSheet
sdaTimeSheet.Fill(dsTimeSheet)
ConnectTimeSheet.Close()
Try
Dim RecordTimeSheet As DataRow = dsTimeSheet.Tables(0).Rows(0)
StrTimeSheetCode = CStr(RecordTimeSheet("TimeSheetCode"))
If RecordTimeSheet.IsNull("TimeSheetCode") Then
IsNewRecord = True
Throw New System.IndexOutOfRangeException( _
"No TimeSheet with that number exists!")
Else
TxtWeek1Monday.Text = CStr(RecordTimeSheet("Week1Monday"))
TxtWeek1Tuesday.Text = CStr(RecordTimeSheet("Week1Tuesday"))
TxtWeek1Wednesday.Text = _
CStr(RecordTimeSheet("Week1Wednesday"))
TxtWeek1Thursday.Text = _
CStr(RecordTimeSheet("Week1Thursday"))
TxtWeek1Friday.Text = CStr(RecordTimeSheet("Week1Friday"))
TxtWeek1Saturday.Text = _
CStr(RecordTimeSheet("Week1Saturday"))
TxtWeek1Sunday.Text = CStr(RecordTimeSheet("Week1Sunday"))
TxtWeek2Monday.Text = CStr(RecordTimeSheet("Week2Monday"))
TxtWeek2Tuesday.Text = CStr(RecordTimeSheet("Week2Tuesday"))
TxtWeek2Wednesday.Text = _
CStr(RecordTimeSheet("Week2Wednesday"))
TxtWeek2Thursday.Text = _
CStr(RecordTimeSheet("Week2Thursday"))
TxtWeek2Friday.Text = CStr(RecordTimeSheet("Week2Friday"))
TxtWeek2Saturday.Text = _
CStr(RecordTimeSheet("Week2Saturday"))
TxtWeek2Sunday.Text = CStr(RecordTimeSheet("Week2Sunday"))
IsNewRecord = False
End If
Catch Exc As IndexOutOfRangeException
BtnReset_Click(sender, e)
End Try
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnTimesheet
Text: Employee's Time Sheet...
- Double-click the Account Types button and implement its event as
follows:
Private Sub BtnTimeSheet_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnTimeSheet.Click
Dim Sheet As Timesheet = New Timesheet
Sheet.ShowDialog()
End Sub
|
- Save all
|
Fundamentals of Creating and Using a View |
|
To create a view, you can use the Object Explorer (Microsoft
SQL Server Management Studio), a query window (Microsoft SQL Server Management
Studio), or the Server Explorer (Microsoft Visual Studio).
Before starting the view, you would have to specify the table(s) that will be involved.
To create a view from the Object Explorer or the Server Explorer,
you can expand the database, right-click Views and click
New View or Add New View. This would open the Add Table dialog box:
The basic functionality of this dialog box is
exactly the same as we reviewed for data analysis:
- To specify the table that would be used as the source, you
can click it in the list box of the Tables property page
- If you will be using
another existing view, from the Views property page, you can click the name of
the desired view
- If a function will be used to generate the records, you can
locate it in the Functions property page. After selecting the source object, you
can either double-click it or you can click it once and click Add. In the
previous lesson, we saw that you could add more than one existing table. In the same
way, you can add more than one view or functions
- After
selecting the source(s), you can click Close on the Add Table dialog box
- After
selecting the objects, as we saw in the previous lesson, they would display in the
window
- As seen in the previous lesson, if you are using more than one table and
they are not (yet) related, you can drag a column from one table and drop it
on another table to create a JOIN between them
- As we saw in previous lessons, to select a column, you can click its check box in the top
list. This would display it in the first empty box under the Column column and
would add its name to the SELECT statement. Alternatively, you can click
an empty box in the Column column to reveal its combo box, then click the arrow
of the combo box and select the desired column from the list
- After selecting the column, its check box would be checked
in the top section of the window, its name would be displayed in the Column
column, and it would be added to the SELECT statement. If you know the
name of the column you want to add, you can manually type it in the SELECT
statement.
The structure of a view can be considered complete when the
SELECT statement is as complete as possible. At any time, to test the results of a view, you can run it.
To do this, in the Microsoft SQL Server Management Studio you can click the Execute SQL button 
or in Microsoft Visual Studio, you can right-click the view and click Execute
SQL.
This would cause the bottom section of the view to display the results of the
query. Here is an example:
As reviewed during data analysis and when creating joins in
the previous lesson, you can add conditions in a view to make it isolate only some
records. Here is an example:
As stated already, one of the reasons for creating a view is to be able to use it over and over again. To
achieve this, the view must be saved. Like most objects in Microsoft SQL Server, a view
must have a name and it is saved as its own object. To save a view from the view window, you can click the Save button on the toolbar. You can also attempt
to close the window. You would then be prompted to save it. When saving a view,
you should give it a name that follows the rules and suggestions of SQL. In our lessons, here are the rules we will use to name our
views:
- A name will start with a letter
- After the first letter, the name will have combinations of underscores,
letters, and digits. Examples are n24, act_52_t
- A name will not include special characters such as !, @, #, $, %, ^,
&, or *
- A name will not have spaces
- If the name is a combination of words, each word will start in uppercase
After saving a view, it becomes part of the Views node of
its database: a node would be created for it and its name would appear in the
Views node of its database.
As stated already, a view is a technique of selecting
records to view or use over an over again. After a view has been created,
you can open it. You have two main options.
- To see the structure of a view, such as the table(s) on which it is
based and the relationships, if any that compose it, in the Object
Explorer, right-click the view and click Design
- To see the SQL code that makes up a view, in the Object Explorer,
right-click the view and click Edit
Executing a view consists of seeing its results. To do this,
you have various options. To view the results of a view:
- Open an empty query window associated with the database that contains the
view. In the query window, write a SELECT statement using the same
formulas and rules we saw for tables. Here is an example:
- From the Object Explorer,
expand the database and its Views node. Right-click the name of the
view and click Open View
|
Practical Learning: Visually Creating a View
|
|
- In the Server Explorer, expand the YugoNationalBank1 node if necessary.
Right-click Views and click Add New View
- In the Add Table dialog box, click Employees, click Add, and
click Close
- In the Diagram section of the view, click the check boxes of EmployeeID
and EmployeeNumber
- In the Criteria section, click the empty box under EmployeeNumber and
type LastName + ', ' + FirstName
- Set its Alias to EmployeeName
- In the Diagram section, click the check box of CanCreateNewAccount
- Close the view
- When asked whether you want to save it, click Yes
- In the Choose Name dialog box, set the name to
EmployeeIdentification
and click OK
- In the Data Sources window, right-click DsYugoNationalBank and click
Configure DataSet With Wizard...
- Click the check box of Views
- Click Finish
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to Customers and click Add
- In the Data Sources window, click Customers and click the arrow on its
right side to drop the combo box
- Select Details
- Drag the Customers node and drop it on the form
- Under the form, click the objects and, using the Properties window,
change their names as follows:
| Object |
Name |
| customersBindingSource |
BsCustomers |
| customersTableAdapter |
TaCustomers |
| customersBindingNavigator |
BnCustomers |
- Once again, from the Data Sources window, drag EmployeeIdentification and drop
it on the form
- While the data grid view is still selected, press Delete to remove it
- Under the form, click the objects and, using the Properties window,
change their names as follows:
| Object |
Name |
Filter |
| employeeIdentificationBindingSource |
BsAccountManagers |
CanCreateNewAccount = True |
| employeeIdentificationTableAdapter |
TaAccountManagers |
|
- Once again, from the Data Sources window, drag AccountTypes and drop it
on the form
- While the data grid view is still selected, press Delete to remove it
- Under the form, click the objects and, using the Properties window,
change their names as follows:
| Object |
Name |
| accountTypesBindingSource |
BsAccountTypes |
| accountTypesTableAdapter |
TaAccountTypes |
- On the form, click the text box on the right side of Employee ID and
press Delete
- On the form, click the text box on the right side of Date Created and
press Delete
- On the form, click the text box on the right side of Account Type ID and
press Delete
- On the form, click the text box on the right side of Account Number and
press Delete
- On the form, click the text box on the right side of Account Status and
press Delete
- Design the form as follows:
 |
| New Control |
Text |
Name |
Other Properties |
| ComboBox |
|
CbxEmployeeID |
DropDownStyle: DropDownList |
| DataSource: BsAccountManagers |
| DisplayMember: EmployeeName |
| ValueMember: EmployeeID |
| (DataBindings) -> Selected Value: bsCustomers -
EmployeeID |
| ComboBox |
|
CbxAccountTypeID |
DropDownStyle: DropDownList |
| DataSource: bsAccountTypes |
| DisplayMember: AccountType |
| ValueMember: AccountTypeID |
| (DataBindings) -> Selected Value: bsCustomers -
AccountTypeID |
| MaskedTextBox |
|
TxtAccountNumber |
Mask: 00-000000-00 |
| (DataBindings) -> Text: bsCustomers -
AccountNumber |
| ComboBox |
|
CbxAccountStatus |
DropDownStyle: DropDownList |
| (DataBindings) -> Text: bsCustomers - DateCreated |
Items:
Active
Closed
suspended |
| Button |
Close |
BtnClose |
Anchor: Bottom, Right |
|
- Double-click the Close button and implement its even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnCustomers
Text: Customers...
- Double-click the Account Types button and implement its event as
follows:
Private Sub btnCustomers_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles btnCustomers.Click
Dim Clients As Customers = New Customers
Clients.ShowDialog()
End Sub
|
- Execute the application and open the Employees form
- Create the following records:
- Close the forms and return to your programming environment
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to NewDeposit and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Transaction Date: |
|
|
| DateTimePicker |
|
DtpTransactionDate |
|
| Label |
Processed By: |
|
|
| MaskedTextBox |
|
TxtEmployeeNumber |
Mask: 00000 |
| TextBox |
|
TxtEmployeeName |
|
| Label |
Processed For: |
|
|
| MaskedTextBox |
|
TxtAccountNumber |
Mask: 00-000000-00 |
| TextBox |
|
TxtCustomerName |
|
| Label |
Currency Type: |
|
|
| ComboBox |
|
CbxCurrencyTypes |
DropDownStyle: DropDownList |
Items:
Cash
Check
Money Order |
| Label |
Amount Deposited: |
|
|
| TextBox |
|
TxtAmount |
TextAlign: Right |
| Label |
Notes |
|
|
| TextBox |
|
TxtNotes |
Multiline: True
ScrollBars: Vertical |
| Button |
Submit |
BtnSubmit |
|
| Button |
Close |
BtnClose |
|
|
- Right-click the form and click View Code
- In the Class Name combo box, select (New Deposit Events)
- In the Method Name combo box, select Load and make the following changes:
Imports System.Data.SqlClient
Public Class NewDeposit
Dim EmployeeID As Integer
Dim CustomerID As Integer
Private Sub NewDeposit_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
EmployeeID = 0
CustomerID = 0
End Sub
End Class
|
- In the Class Name combo box, select TxtEmployeeNumber
- In the Method Name combo box, select Leave and implement the even as follows:
Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtEmployeeNumber.Leave
If TxtEmployeeNumber.Text.Length = 0 Then
MsgBox("You must specify the employee number " & _
"of the clerk who is processing the deposit.")
Exit Sub
Else
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strYNB As String = _
"SELECT EmployeeID, FirstName, LastName " & _
"FROM Employees WHERE EmployeeNumber = '" & _
TxtEmployeeNumber.Text & "';"
Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
Connect.Open()
Dim rdrEmployees As SqlDataReader = Command.ExecuteReader()
While rdrEmployees.Read()
EmployeeID = CInt(rdrEmployees.GetSqlInt32(0))
TxtEmployeeName.Text = rdrEmployees.GetString(1) & " " & _
rdrEmployees.GetString(2)
End While
If EmployeeID = 0 Then
MsgBox("The employee number you entered " & _
"is not recognized in our database.")
TxtEmployeeNumber.Text = ""
End If
End Using
End If
End Sub
|
- In the Class Name combo box, select TxtAccountNumber
- In the Method Name combo box, select Leave and implement the even as follows:
Private Sub TxtAccountNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtAccountNumber.Leave
If TxtAccountNumber.Text.Length = 0 Then
MsgBox("You must specify the account number " & _
"of the customer whose deposit you are entering.")
Exit Sub
Else
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strYNB As String = _
"SELECT CustomerID, CustomerName FROM " & _
"Customers WHERE AccountNumber = '" & _
TxtAccountNumber.Text & "';"
Dim Command As SqlCommand = _
New SqlCommand(strYNB, Connect)
Dim sdaYNB As SqlDataAdapter = New SqlDataAdapter
sdaYNB.SelectCommand = Command
Dim dsCustomers As DataSet = New DataSet("CustomersSet")
sdaYNB.Fill(dsCustomers)
Connect.Open()
For Each Record As DataRow In dsCustomers.Tables(0).Rows
CustomerID = Record("CustomerID")
TxtCustomerName.Text = Record("CustomerName")
Exit For
Next
If CustomerID = 0 Then
MsgBox("The account number you entered " & _
"is not recognized in our database.")
TxtAccountNumber.Text = ""
End If
End Using
End If
End Sub
|
- In the Class Name combo box, select BtnSubmit
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnSubmit_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
Dim DateTransaction As DateTime = DateTime.Today
Dim StrCurrencyType As String = "Unknown"
Dim Amount As Double = 0.0
If EmployeeID = 0 Then
MsgBox("You must specify the employee number " & _
"of the clerk who is processing the deposit.")
Exit Sub
End If
If CustomerID = 0 Then
MsgBox("You must enter an account number " & _
"for the new customer.")
Exit Sub
End If
StrCurrencyType = CbxCurrencyTypes.Text
Try
Amount = CDbl(TxtAmount.Text)
Catch ex As Exception
MsgBox("Invalid Amount.")
End Try
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim StrSQL As String = _
"INSERT INTO AccountsTransactions(" & _
"EmployeeID, CustomerID, " & _
"TransactionDate, TransactionType, " & _
"CurrencyType, DepositAmount, Notes) " & _
"VALUES('" & CStr(EmployeeID) + "', '" & _
CStr(CustomerID) + "', '" & _
DtpTransactionDate.Value.ToString("d") & _
"', 'Deposit', '" & CbxCurrencyTypes.Text & _
"', '" & CStr(Amount) & "', '" & TxtNotes.Text & "');"
Dim cmdEmployees As SqlCommand = _
New SqlCommand(StrSQL, Connect)
Connect.Open()
cmdEmployees.ExecuteNonQuery()
DtpTransactionDate.Value = DateTime.Today
TxtEmployeeNumber.Text = ""
TxtEmployeeName.Text = ""
TxtAccountNumber.Text = ""
TxtCustomerName.Text = ""
CbxCurrencyTypes.SelectedIndex = 0
TxtAmount.Text = "0.00"
TxtNotes.Text = ""
End Using
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnNewDeposit
Text: New Deposit...
- Double-click the Account Types button and implement its event as
follows:
Private Sub BtnNewDeposit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnNewDeposit.Click
Dim Deposit As NewDeposit = New NewDeposit
Deposit.ShowDialog()
End Sub
|
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to NewWithdrawal and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Transaction Date: |
|
|
| DateTimePicker |
|
DtpTransactionDate |
|
| Label |
Processed By: |
|
|
| MaskedTextBox |
|
TxtEmployeeNumber |
Mask: 00000 |
| TextBox |
|
TxtEmployeeName |
|
| Label |
Processed For: |
|
|
| MaskedTextBox |
|
TxtAccountNumber |
Mask: 00-000000-00 |
| TextBox |
|
TxtCustomerName |
|
| Label |
Currency Type: |
|
|
| ComboBox |
|
CbxCurrencyTypes |
DropDownStyle: DropDownList |
Items:
Cash
Check
Money Order |
| Label |
Amount Withdrawn: |
|
|
| TextBox |
|
TxtAmount |
TextAlign: Right |
| Label |
Notes |
|
|
| TextBox |
|
TxtNotes |
Multiline: True
ScrollBars: Vertical |
| Button |
Submit |
BtnSubmit |
|
| Button |
Close |
BtnClose |
|
|
- Right-click the form and click View Code
- In the Class Name combo box, select (New Deposit Events)
- In the Method Name combo box, select Load and make the following
changes:
Imports System.Data.SqlClient
Public Class NewWithdrawal
Dim EmployeeID As Integer
Dim CustomerID As Integer
Private Sub NewWithdrawal_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
EmployeeID = 0
CustomerID = 0
End Sub
End Class
|
- In the Class Name combo box, select Txt EmployeeNumber
- In the Method Name combo box, select Leave and implement the even as
follows:
Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtEmployeeNumber.Leave
If TxtEmployeeNumber.Text.Length = 0 Then
MsgBox("You must specify the employee number " & _
"of the clerk who is processing the transaction.")
Exit Sub
Else
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strYNB As String = _
"SELECT EmployeeID, FirstName, LastName " & _
"FROM Employees WHERE EmployeeNumber = '" & _
TxtEmployeeNumber.Text & "';"
Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
Connect.Open()
Dim rdrEmployees As SqlDataReader = Command.ExecuteReader()
While rdrEmployees.Read()
EmployeeID = rdrEmployees.GetSqlInt32(0)
TxtEmployeeName.Text = rdrEmployees.GetString(1) & " " & _
rdrEmployees.GetString(2)
End While
If EmployeeID = 0 Then
MsgBox("The employee number you entered " & _
"is not recognized in our database.")
TxtEmployeeNumber.Text = ""
End If
End Using
End If
End Sub
|
- In the Class Name combo box, select Txt AccountNumber
- In the Method Name combo box, select Leave and implement the even as
follows:
Private Sub TxtAccountNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles TxtAccountNumber.Leave
If TxtAccountNumber.Text.Length = 0 Then
MsgBox("You must specify the account number " & _
"of the customer whose withdrawal you are processing.")
Exit Sub
Else
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strYNB As String = _
"SELECT CustomerID, CustomerName FROM " & _
"Customers WHERE AccountNumber = '" & _
TxtAccountNumber.Text & "';"
Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
Dim sdaYNB As SqlDataAdapter = New SqlDataAdapter
sdaYNB.SelectCommand = Command
Dim dsCustomers As DataSet = New DataSet("CustomersSet")
sdaYNB.Fill(dsCustomers)
Connect.Open()
For Each RecordCustomer As DataRow In dsCustomers.Tables(0).Rows
CustomerID = RecordCustomer("CustomerID")
TxtCustomerName.Text = RecordCustomer("CustomerName")
Exit For
Next
If CustomerID = 0 Then
MsgBox("The account number you entered " & _
"is not recognized in our database.")
TxtAccountNumber.Text = ""
End If
End Using
End If
End Sub
|
- In the Class Name combo box, select Btn Submit
- In the Method Name combo box, select Click and implement the even as
follows:
Private Sub BtnSubmit_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
Dim DateTransaction As DateTime = DateTime.Today
Dim Amount As Double = 0.0
If CustomerID = 0 Then
MsgBox("You must enter a valid account number " & _
"for the new customer.")
Exit Sub
End If
Try
Amount = CDbl(TxtAmount.Text)
Catch ex As Exception
MsgBox("Invalid Amount.")
Exit Sub
End Try
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strCharges As String = _
"INSERT INTO AccountsTransactions(" & _
"CustomerID, " & _
"TransactionDate, TransactionType, " & _
"ChargeAmount, ChargeReason, Notes) " & _
"VALUES('" & CStr(CustomerID) & "', '" & _
DtpTransactionDate.Value.ToString("d") & _
"', 'Charge', '" & CStr(Amount) & "', '" & _
CbxChargeReasons.Text & "', '" & _
TxtNotes.Text & "');"
Dim cmdCharges As SqlCommand = _
New SqlCommand(strCharges, Connect)
Connect.Open()
cmdCharges.ExecuteNonQuery()
DtpTransactionDate.Value = DateTime.Today
TxtAccountNumber.Text = ""
TxtCustomerName.Text = ""
CbxChargeReasons.SelectedIndex = 0
TxtAmount.Text = "0.00"
TxtNotes.Text = ""
End Using
End Sub
|
- In the Class Name combo box, select Btn Close
- In the Method Name combo box, select Click and implement the even as
follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnNewWithdrawal
Text: New Withdrawal...
- Double-click the Account Types button and implement its event as
follows:
Private Sub BtnNewWithdrawal_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnNewWithdrawal.Click
Dim Withdraw As NewWithdrawal = New NewWithdrawal
Withdraw.ShowDialog()
End Sub
|
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to NewCharge and click Add
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Transaction Date: |
|
|
| DateTimePicker |
|
DtpTransactionDate |
|
| Label |
Processed For: |
|
|
| MaskedTextBox |
|
TxtAccountNumber |
Mask: 00-000000-00 |
| TextBox |
|
TxtCustomerName |
|
| Label |
Charge Reason: |
|
|
| ComboBox |
|
CbxChargeReason |
DropDownStyle: DropDownList |
Items:
Overdraft
Money Order
Check Stopping
Monthly Charge |
| Label |
Amount Charged: |
|
|
| TextBox |
|
TxtAmount |
TextAlign: Right |
| Label |
Notes |
|
|
| TextBox |
|
TxtNotes |
Multiline: True
ScrollBars: Vertical |
| Button |
Submit |
BtnSubmit |
|
| Button |
Close |
BtnClose |
|
|
- Right-click the form and click View Code
- In the Class Name combo box, select (New Deposit Events)
- In the Method Name combo box, select Load and make the following
changes:
Imports System.Data.SqlClient
Public Class NewCharge
Dim CustomerID As Double
Private Sub NewCharge_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
CustomerID = 0
End Sub
End Class
|
- In the Class Name combo box, select Txt AccountNumber
- In the Method Name combo box, select Leave and implement the even as
follows:
Private Sub txtAccountNumber_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles txtAccountNumber.Leave
If txtAccountNumber.Text.Length = 0 Then
MsgBox("You must specify the account number " & _
"of the customer whose withdrawal you are processing.")
Exit Sub
Else
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strYNB As String = _
"SELECT CustomerID, CustomerName FROM " & _
"Customers WHERE AccountNumber = '" & _
txtAccountNumber.Text & "';"
Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
Dim sdaYNB As SqlDataAdapter = New SqlDataAdapter
sdaYNB.SelectCommand = Command
Dim dsCustomers As DataSet = New DataSet("CustomersSet")
sdaYNB.Fill(dsCustomers)
Connect.Open()
For Each RecordCustomer as DataRow in dsCustomers.Tables(0).Rows)
CustomerID = RecordCustomer("CustomerID")
txtCustomerName.Text = RecordCustomer("CustomerName")
Exit For
Next
If CustomerID = 0 Then
MsgBox("The account number you entered " & _
"is not recognized in our database.")
txtAccountNumber.Text = ""
End If
End Using
End If
End Sub
|
- In the Class Name combo box, select Btn Submit
- In the Method Name combo box, select Click and implement the even as
follows:
Private Sub BtnSubmit_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
Dim DateTransaction As DateTime = DateTime.Today
Dim Amount As Double = 0.0
If CustomerID = 0 Then
MsgBox("You must enter a valid account number " & _
"for the new customer.")
Exit Sub
End If
Try
Amount = CDbl(TxtAmount.Text)
Catch ex As Exception
MsgBox("Invalid Amount.")
Exit Sub
End Try
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim strCharges As String = _
"INSERT INTO AccountsTransactions(" & _
"CustomerID, " & _
"TransactionDate, TransactionType, " & _
"ChargeAmount, ChargeReason, Notes) " & _
"VALUES('" & CustomerID + "', '" & _
DtpTransactionDate.Value.ToString("d") & _
"', 'Charge', '" & Amount + "', '" & _
CbxChargeReasons.Text & "', '" & _
TxtNotes.Text & "');"
Dim cmdCharges As SqlCommand = _
New SqlCommand(strCharges, Connect)
Connect.Open()
cmdCharges.ExecuteNonQuery()
DtpTransactionDate.Value = DateTime.Today
TxtAccountNumber.Text = ""
TxtCustomerName.Text = ""
CbxChargeReasons.SelectedIndex = 0
TxtAmount.Text = "0.00"
TxtNotes.Text = ""
End Using
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as
follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnNewCharge
Text: New Charge...
- Double-click the Account Types button and implement its event as
follows:
Private Sub BtnNewCharge_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnNewCharge.Click
Dim Charge As NewCharge = New NewCharge
Charge.ShowDialog()
End Sub
|
- Execute the application
- Open the New Deposit form to create the first three
transactions
- Then open the New Withdrawal form to create the withdrawal
transactions
- Create the other transactions
- Close the forms and return to your programming environment
- To create a new view, in the Server Explorer, under YugoNationalBank1,
right-click Views and click Add New View
- In the Add Table dialog box, double-click Customers and AccountTypes
- Click Close
- In the Diagram section, click the check boxes of CustomerID, CustomerName,
AccountNumber, AccountType, DateCreated, and AccountStatus
- Close the view
- When asked whether you want to save it, click Yes
- Set the Name to CustomerIdentification and click OK
- In the Data Sources window, right-click dsYugoNationalBank and click
Configure DataSet With Wizard...
- Click the check box of Views to remove the check mark
- Click it again to put the check mark and click Finish
- To create a new view, in the Server Explorer, under YugoNationalBank1,
right-click Views and click Add New View
- In the Add Table dialog box, double-click AccountsTransactions and Customers
- Click Close
- In the Diagram section, click the check boxes of AccountNumber, TransactionDate,
TransactionType, CurrencyType, DepositAmount, WithdrawalAmount, ChargeAmount,
ChargeReason, and Balance
- Close the view
- When asked whether you want to save it, click Yes
- Set the Name to AccountTransactions and click OK
- In the Data Sources window, right-click dsYugoNationalBank and click
Configure DataSet With Wizard...
- Click the check box of View to remove the check mark
- Click it again to put the check mark
- Click Finish
- To create a new form, on the main menu, click Project -> Add Windows
Form...
- Set the Name to AccountTransactions and click Add
- From the Data Sources window, drag AccountTransactions and drop it on
the form
- Under the form, click accountTransactionsBindingNavigator and press
Delete
- Under the form, click the objects and, using the Properties window,
change their names as follows:
| Object |
Name |
| accountTransactionsBindingSource |
BsAccountTransactions |
| accountTypesTableAdapter |
TaAccountTransactions |
- Design the form as follows:
 |
| Control |
Text |
Name |
Other Properties |
| Label |
Account Number: |
|
|
| MaskedTextBox |
|
TxtAccountNumber |
Mask: 00-000000-00 |
| Button |
Locate |
BtnLocate |
|
| Label |
Customer Name: |
|
|
| TextBox |
|
TxtCustomerName |
|
| Label |
Account Type: |
|
|
| TextBox |
|
TxtAccountType |
|
| Label |
Account Status: |
|
|
| TextBox |
|
TxtAccountStatus |
|
| Label |
Date Created: |
|
|
| DateTimePicker |
|
DtpDateCreated |
|
| DataGridView |
|
DgvAccountProperties |
|
| Label |
Total Deposits |
|
|
| TextBox |
|
TxtTotalDeposits |
Text: 0.00
TextAlign: Right |
| Label |
Total Charges |
|
|
| TextBox |
|
TxtTotalCharges |
Text: 0.00
TextAlign: Right |
| Button |
Close |
BtnClose |
|
| Label |
Total Withdrawals |
|
|
| TextBox |
|
TxtTotalWithdrawals |
Text: 0.00
TextAlign: Right |
| Label |
Balance |
|
|
| TextBox |
|
TxtBalance |
Text: 0.00
TextAlign: Right |
|
- Right-click the form and click View Code
- Just above the Public Class line, import the System.Data.SqlClient
namespace
Imports System.Data.SqlClient
Public Class AccountTransactions
. . . No Change
|
- Locate the Load event and add the following line:
Private Sub AccountTransactions_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
'TODO: This line of code loads data into
' the 'DsYugoNationalBank.AccountTransactions' table.
' You can move, or remove it, as needed.
Me.TaAccountTransactions.Fill(Me.DsYugoNationalBank.AccountTransactions)
BsAccountTransactions.Filter = "AccountNumber = '00-000000-00'"
End Sub
|
- In the Class Name combo box, select BtnLocate
- In the Method Name combo box, select Click and implement the event the as
follows:
Private Sub BtnLocate_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnLocate.Click
Dim CustomerID As Integer = 0
If TxtAccountNumber.Text.Length = 0 Then
MsgBox("You must specify the account number " & _
"of the customer whose transactions you want to view.")
Exit Sub
Else
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim StrYNB As String = _
"SELECT * FROM " & _
"CustomerIdentification WHERE AccountNumber = '" & _
TxtAccountNumber.Text & "';"
Dim Command As SqlCommand = New SqlCommand(StrYNB, Connect)
Dim SdaYNB As SqlDataAdapter = New SqlDataAdapter
SdaYNB.SelectCommand = Command
Dim dsCustomers As DataSet = New DataSet("CustomersSet")
SdaYNB.Fill(dsCustomers)
Connect.Open()
For Each Record As DataRow In dsCustomers.Tables(0).Rows
CustomerID = Record("CustomerID")
TxtCustomerName.Text = Record("CustomerName")
TxtAccountType.Text = Record("AccountType")
TxtAccountStatus.Text = Record("AccountStatus")
DtpDateCreated.Value = Record("DateCreated")
Next
BsAccountTransactions.Filter = _
"AccountNumber = '" & TxtAccountNumber.Text & "'"
End Using
End If
If CustomerID <> 0 Then
Dim Deposits As Double = 0.0, Withdraws As Double = 0.0
Dim Charges As Double = 0.0, Balance As Double = 0.0
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim StrYNB As String = _
"SELECT SUM(CAST(DepositAmount AS money)), " & _
"SUM(CAST(WithdrawalAmount AS money)), " & _
"SUM(CAST(ChargeAmount AS money)) FROM " & _
"AccountsTransactions WHERE CustomerID = '" & _
CStr(CustomerID) & "';"
Dim Command As SqlCommand = New SqlCommand(StrYNB, Connect)
Connect.Open()
Dim rdrTransactions As SqlDataReader = _
Command.ExecuteReader
While rdrTransactions.Read()
Try
Deposits = CDbl(rdrTransactions(0))
Catch ex As Exception
End Try
Try
Withdraws = CDbl(rdrTransactions(1))
Catch ex As Exception
End Try
Try
Charges = CDbl(rdrTransactions(2))
Catch ex As Exception
End Try
TxtTotalDeposits.Text = FormatNumber(Deposits)
TxtTotalWithdrawals.Text = FormatNumber(Withdraws)
TxtTotalCharges.Text = FormatNumber(Charges)
Balance = Deposits - (Withdraws + Charges)
TxtBalance.Text = FormatNumber(Balance)
End While
End Using
End If
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Access the Central form, add a button and change its properties as
follows:
(Name): BtnAccountTransactions
Text: View an Account's Transactions...
- Double-click the Account Types button and implement its event as
follows:
Private Sub BtnAccountTransactions_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnAccountTransactions.Click
Dim Transactions As AccountTransactions = New AccountTransactions
Transactions.ShowDialog()
End Sub
|
- Execute the application
- Open the Account's Transactions form, enter an account number and click
Locate
- Close the forms and return to your programming environment
|
Programmatically Creating and Using a View |
|
To programmatically create a view, you use the following SQL
syntax:
CREATE VIEW ViewName
AS
SELECT Statement
If you are using Microsoft SQL Server Management Studio, it can generate skeleton code of a view
for you. To use it, first open an empty query window. Display the Template
Explorer. In the Template Explorer, expand the View node. From the View node,
drag Create View and drop it in the query window.
The creation of a view starts with the CREATE VIEW
expression followed by a name. The name of a view follows the rules
and suggestions we reviewed for view names. After the name of the view, use the AS
keyword to indicate that you are ready to define the view.
Because a view is primarily a SQL statement, it is defined using a SELECT
statement, using the same rules we studied for data analysis. Here is an example
of a view:
CREATE VIEW dbo.ListOfMen
AS
SELECT dbo.Sexes.Sex,
dbo.Persons.FirstName, dbo.Persons.LastName
FROM dbo.Genders INNER JOIN dbo.Persons
ON dbo.Sexes.SexID = dbo.Persons.SexID
WHERE (dbo.Sexes.Sex = 'Male');
GO
After creating the SQL statement that defines the view, you
must execute the statement. If using a query window in Microsoft SQL Server
Management Studio, you can do this by pressing
F5. Once the statement is executed, its name is automatically added to the Views
node of its database even if you do not save its code.
|
Practical Learning:
Programmatically Creating a View
|
|
- In the Solution Explorer, right-click Central.vb and click View Code
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the event as follows:
Private Sub BtnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=SSPI;")
Dim StrTimesheet As String = _
"CREATE VIEW dbo.Timesheet " & _
"AS " & _
"SELECT EmployeeNumber, StartDate, " & _
" TimesheetCode, Week1Monday, " & _
" Week1Tuesday, Week1Wednesday, " & _
" Week1Thursday, Week1Friday, " & _
" Week1Saturday, Week1Sunday, " & _
" Week2Monday, Week2Tuesday, " & _
" Week2Wednesday, Week2Thursday, " & _
" Week2Friday, Week2Saturday, " & _
" Week2Sunday, Notes " & _
"FROM dbo.Timesheets;"
Dim Command As SqlCommand = _
New SqlCommand(StrTimesheet, Connect)
Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A view named Timesheet has been created.")
End Using
Close()
End Sub
|
- Execute the application and click the Close button
- Change the code of the Close event of the Central form as follows:
Private Sub BtnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
End
End Sub
|
- Save all
After creating a view, it shares many of the characteristics
of a table. For example, a view has its own columns although the columns are
actually tied to the table(s) that hold(s) the original data. Treated as a
table, you can access the columns of a view using a SELECT
statement. This means that you can access one, a few, or all of the columns.
Here is an example that accesses all columns of a view: SELECT PayrollPreparation.* FROM PayrollPreparation;
In Transact-SQL, a view is considered an object. As
such, it can be viewed, changed, or deleted. Like any regular object, a
view has its own characteristics. To see them in Microsoft SQL Server
Management Studio, you can right-click the
view and click Properties. A View Properties dialog box would come up. It
can give you information such as the name of the database the view belongs
to, the date the view was created, etc.
After a view has been created, either by you or
someone else, you may find out that it has an unnecessary column, it needs
a missing column, it includes unnecessary records, or some records are
missing. Fortunately, you can change the structure or the code of a view. This is
referred to as altering a view. You have various options:
- To visually change a view, in the Object Explorer of Microsoft SQL Server
Management Studio, you can right-click the view
and click Design. In the Server Explorer of Microsoft Visual Studio, you can
right-click the view and click Open View Definition.
From the view window, you can add or remove the columns.
You can also change any options in one of the sections of the window. After
modifying the view, save it and close it
- To change the code of a view, in the Object Explorer of Microsoft SQL Server
Management Studio, right-click it and
click Edit. After editing the
view's code, you can save it
- From the Object Explorer of Microsoft SQL Server Management Studio, you can right-click the view, position the mouse on
Script View As -> ALTER To -> New Query Editor Window
The basic formula to programmatically modify a view
is:
ALTER VIEW ViewName
AS
SELECT Statement
You start the alteration with the ALTER VIEW
expression followed by the name of the view. After the name of the view,
use the AS keyword to specify that you are ready to perform the
change. After the AS keyword, you can then define the view as you see fit.
For example, you can create a SELECT statement that includes a
modification of the existing code or a completely new statement.
In the view we created to show a list of men of a table, we
included a column for the sex. This column is useless or redundant because we
already know that the list includes only men. Here is an example of altering the
view to remove (or rather omit) the Sex column of the Persons table:
ALTER VIEW dbo.ListOfMen
AS
SELECT dbo.Persons.FirstName, dbo.Persons.LastName
FROM dbo.Sexes INNER JOIN dbo.Persons
ON dbo.Sexes.SexID = dbo.Persons.SexID
WHERE (dbo.Sexes.Sex = 'Male');
Instead of modifying a view, if you find it altogether
useless, you can remove it from its database. You have various options. To delete a view:
- In the Object Explorer in Microsoft SQL Server Management Studio,
right-click the name of the view and click Delete. The Delete Object dialog
box would display to give you the opportunity to confirm your intention or
to change your mind
- In the Object Explorer in Microsoft SQL Server Management Studio, right-click the view, position the mouse on Script
View As -> DROP To New Query Editor Window
- In Microsoft SQL Server Management Studio, you can open an empty query window associated with the database that has the
undesired view. From the Template Explorer, in the View node, drag Drop View
and drop it in the query window
- In the Server Explorer in Microsoft Visual Studio, under the Views node
of the database, you can right-click the view and click Delete. A message
box would display, asking you whether you are sure you want to delete the
view. You can decide to continue or change your mind
The formula to programmatically delete a view is:
DROP VIEW ViewName
On the right side of the DROP VIEW expression, enter
the name of the undesired view and execute the statement. You will not be
warned before the interpreter deletes the view. If you are
programmatically creating a Windows Forms application, of course you can use a
conditional statement to assist the user with deciding whether to
continue deleting the view or not.
As seen so far, a view is a selected list of records from a
table. As you may suspect, the easiest view is probably one created from one
table. Imagine you have a table of employees and you want to create a view that
lists only their names. You may create a view as follows:
CREATE VIEW dbo.EmployeesNames
AS
SELECT FirstName,
LastName,
LastName + ', ' + FirstName AS FullName
FROM Persons;
GO
On such a view that is based on one table, you can perform
data entry, using the view, rather than the table. To do this, you follow the
same rules we reviewed for table data entry. Here is an example:
INSERT INTO dbo.EmployeesNames(FirstName, LastName)
VALUES('Peter', 'Justice');
If you perform data entry using a view, the data you provide
would be entered in the table from which the view is based. This means that the table would be updated
automatically. Based on this feature, you can create a view purposely intended
to update a table so that, in the view, you would include only the columns that
need to be updated.
|
Practical Learning: Performing Data Entry Using a View
|
|
- In the Solution Explorer, right-click Timesheet.vb and click View Code
- In the Class Name combo box, select BtnSubmit
- In the Method Name combo box, select Click and implement the event as follows:
Private Sub BtnSubmit_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles BtnSubmit.Click
Dim StrTimeSheet As String = ""
' If this is new record, then create a new time sheet
If IsNewRecord = True Then
strTimeSheet = "INSERT INTO dbo.Timesheet " & _
"VALUES('" & _
TxtEmployeeNumber.Text & "', '" & _
DtpStartDate.Value.ToString("MM/dd/yyyy") & "', '" & _
StrTimeSheetCode + "', '" & _
TxtWeek1Monday.Text & "', '" & _
TxtWeek1Tuesday.Text & "', '" & _
TxtWeek1Wednesday.Text & "', '" & _
TxtWeek1Thursday.Text & "', '" & _
TxtWeek1Friday.Text & "', '" & _
TxtWeek1Saturday.Text & "', '" & _
TxtWeek1Sunday.Text & "', '" & _
TxtWeek2Monday.Text & "', '" & _
TxtWeek2Tuesday.Text & "', '" & _
TxtWeek2Wednesday.Text & "', '" & _
TxtWeek2Thursday.Text & "', '" & _
TxtWeek2Friday.Text & "', '" & _
TxtWeek2Saturday.Text & "', '" & _
TxtWeek2Sunday.Text & "', '" & _
TxtNotes.Text & "');"
End If
' If this is an existing record, then, only update it
If IsNewRecord = False Then
StrTimeSheet = "UPDATE dbo.Timesheets SET Week1Monday = '" & _
TxtWeek1Monday.Text & "', Week1Tuesday = '" & _
TxtWeek1Tuesday.Text & "', Week1Wednesday = '" & _
TxtWeek1Wednesday.Text & "', Week1Thursday = '" & _
TxtWeek1Thursday.Text & "', Week1Friday = '" & _
TxtWeek1Friday.Text & "', Week1Saturday = '" & _
TxtWeek1Saturday.Text & "', Week1Sunday = '" & _
TxtWeek1Sunday.Text & "', Week2Monday = '" & _
TxtWeek2Monday.Text & "', Week2Tuesday = '" & _
TxtWeek2Tuesday.Text & "', Week2Wednesday = '" & _
TxtWeek2Wednesday.Text & "', Week2Thursday = '" & _
TxtWeek2Thursday.Text & "', Week2Friday = '" & _
TxtWeek2Friday.Text & "', Week2Saturday = '" & _
TxtWeek2Saturday.Text & "', Week2Sunday = '" & _
TxtWeek2Sunday.Text & "', Notes = '" & TxtNotes.Text & _
"' WHERE TimeSheetCode = '" & StrTimeSheetCode & "';"
End If
If ValidTimeSheet = True Then
Dim Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='YugoNationalBank1';" & _
"Integrated Security=true")
Dim cmdTimeSheet As SqlCommand = _
New SqlCommand(StrTimeSheet, Connect)
Connect.Open()
cmdTimeSheet.ExecuteNonQuery()
Connect.Close()
MsgBox("Your time sheet has been submitted")
' Reset the timesheet
TxtEmployeeNumber.Text = ""
DtpStartDate.Value = DateTime.Today
BtnReset_Click(sender, e)
Else
MsgBox("The time sheet is not valid" & vbCrLf & _
"either you didn't enter a valid employee number, " & _
"or you didn't select a valid start date\n" & _
"The time sheet will not be saved")
End If
End Sub
|
- In the Class Name combo box, select BtnClose
- In the Method Name combo box, select Click and implement the even as follows:
Private Sub BtnClose_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles BtnClose.Click
Close()
End Sub
|
- Execute the application
- Open the employees timesheet and create a few entries
- Close the form and return to your programming environment:
To create more complex or advanced views, you can
involve functions. As always, probably the easiest functions to use are
those built-in. If there is no built-in function that performs the
operation you want, you can create your own. Here is an example: USE People;
GO
CREATE FUNCTION dbo.GetFullName
(
@FName varchar(20),
@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
RETURN @LName + ', ' + @FName;
}
GO
Once you have a function you want to use, you
can call it in the body of your view as you judge it necessary. Here is an
example: CREATE VIEW dbo.MyPeople
AS
SELECT dbo.GetFullName(FirstName, LastName) AS [Full Name],
dbo.Genders.Gender
FROM Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID;
This would produce:
It is important to know that a view is more of a table
type than any other object. This means that a view is not a function but
it can use a function. The word argument here only means that some values
can be passed to a view but these values can be specified only when
creating the view. They are not real arguments.
When structuring a view, you can create placeholders for
columns and pass them in the parentheses of the view. This would be done
as follows:
CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
. . .
If you use this technique, the names passed in the
parentheses of the view are the captions that would be displayed in place
of the columns of the view. This technique allows you to specify the
strings of your choice for the columns. If you want a column header to
display the actual name of the column, write it the same. Otherwise, you
can use any string you want for the column. If the name is in one word,
you can just type it. If the name includes various words, include them
between an opening square bracket "[" and a closing square
bracket "]".
After listing the necessary strings as the captions of
columns, in your SELECT statement of the view, you must use the
exact same number of columns as the number of arguments of the view. In
fact, each column of your SELECT statement should correspond to an
argument of the same order.
Here is an example:
CREATE VIEW dbo.MenAndWomen([First Name], [Last Name], Gender)
AS
SELECT dbo.Persons.FirstName,
dbo.Persons.LastName,
dbo.Genders.Gender
FROM dbo.Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID;
GO
Because, as we stated already, a view is not a
function and the values passed to the view are not real arguments, when
executing the view, do not specify the names of arguments. Simply create a
SELECT statement and specify the name of the view as the source.
|
Views and Conditional Statements |
|
Besides its querying characteristics that allow it to
perform data analysis, probably the most important feature of a query is
its ability to be as complex as possible by handling conditional
statements. This makes it possible to use a view instead of a table in
operations and expressions that would complicate the code or the structure of
a table. When creating a view, in its SELECT statement, you can
perform column selections, order them, and set criteria to exclude some
records.
Here is an example:
|
|
