ASP.NET for a Real-World
Problem
Andy Luse
6 November 2004
Information Assurance
Outline
Problem Overview
Components Used
Database Design
ASP.NET Code
Demo
Problem Overview
Debbie in Business Graduate Office
Business thesis archival process
Demonstration for this class
Components Used
Microsoft
– Windows Server 2003 Enterprise Edition
IIS 6.0
.NET Framework 1.1
– Frontpage
– Visio
mySQL
– Only because it was mandatory
Database Design
Keyword Manuscript_Keyword
PK keywordKey LONG
keyword VARCHAR(30) FK1 keywordKey LONG
FK2 manuscriptKey LONG
Student Manuscript
PK studentKey LONG PK manuscriptKey LONG
firstName VARCHAR(20) manuscriptName VARCHAR(200)
middleName VARCHAR(20) FK1 studentKey LONG
lastName VARCHAR(20) FK2 manuscriptTypeKey LONG
yearOfGraduation VARCHAR(10) FK3 majorKey LONG
Major
ManuscriptType
PK majorKey LONG
PK manuscriptTypeKey LONG
major VARCHAR(10)
type VARCHAR(20)
ASP.NET Code (Outline)
Variables needed
Connecting to the database.
Performing a non data-returning operation
Performing a data-returning operation
Variables Needed
Private connectionString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=manuscript;" & _
"UID=****;" & _
"PASSWORD=****;" & _
"OPTION=3"
Private sqlCommand As ODBCCommand 'Used for the SQL commands
Private reader As ODBCDataReader 'Used for the SELECT commands
Private sql As String 'Used for the String representation of
'the SQL commands
Private conn As ODBCConnection 'The database connection
Connecting to the Database
conn = New ODBCConnection(connectionString)
conn.Open()
Performing a non data-returning
operation
conn = New ODBCConnection(connectionString)
conn.Open()
sql = "UPDATE Student " & _
"SET firstName = '" & txtFirstName.Text & "', " & _
"middleName = '" & txtMiddleName.Text & "', " & _
"lastName = '" & txtLastName.Text & "', " & _
"yearOfGraduation = '" & txtYearOfGraduation.Text & "' " & _
"WHERE studentKey = " & cboStudent.SelectedValue
sqlCommand = New ODBCCommand(sql, conn)
sqlCommand.ExecuteNonQuery()
conn.Close()
Performing a data-returning operation
conn = New ODBCConnection(connectionString)
conn.Open()
sql = "Select firstName, COALESCE(middleName, '') AS middleName, lastName,
yearOfGraduation " & _
"FROM Student " & _
"WHERE studentKey = " & cboStudent.SelectedValue
sqlCommand = New ODBCCommand(sql, conn)
reader = sqlCommand.ExecuteReader()
While(reader.Read())
txtFirstName.Text = reader.Item("firstName")
txtMiddleName.Text = reader.Item("middleName")
txtLastName.Text = reader.Item("lastName")
txtYearOfGraduation.Text = reader.Item("yearOfGraduation")
End While
reader.Close()
conn.Close()
Demo
http://example.student.iastate.edu
Questions?
andyluse@iastate.edu