ASP.NET for a Real-World Problem

1. ASP.NET for a Real-World Problem Andy Luse 6 November 2004 Information Assurance

2. Outline • Problem Overview • Components Used • Database Design • ASP.NET Code • Demo

3. Problem Overview • Debbie in Business Graduate Office • Business thesis archival process • Demonstration for this class

4. Components Used • Microsoft • Windows Server 2003 Enterprise Edition • IIS 6.0 • .NET Framework 1.1 • Frontpage • Visio • mySQL • Only because it was mandatory

5. Database Design

6. ASP.NET Code (Outline) • Variables needed • Connecting to the database. • Performing a non data-returning operation • Performing a data-returning operation

7. 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

8. Connecting to the Database conn = New ODBCConnection(connectionString) conn.Open()

9. 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()

10. 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()

11. Demo • http://example.student.iastate.edu

12. Questions? • andyluse@iastate.edu