Database Connectivity

Materials to turn in:

• Lab04 Folder
• Lab04.aspx
• Lab04.aspx.cs
• Lab04b.aspx
• Lab04b.aspx.cs
• other associated project files

Procedures

This lab will give you experience connecting to a SQL Server database in ASP.NET. You will gain more experience using PostBack. You will learn to bind data to HTML controls.

1. Create an ASP.NET page (Lab04.aspx) that connects to a database & populates drop down lists
• In Server Explorer, open a New Query and run this script
  • Connect to the DBMS in Server Explorer of Visual Studio
  • Right click on the DBMS connection in Server Explorer > Select New Query
  • Paste this SQL script
  • Click the 'Execute' button
  • You are done with this script, you can close the SQLQuery window
     
• Finished solution example
   
• Lab04.aspx
• Include one <asp:DropDownList> control
   
• Lab04.aspx.cs
  • Outside the class
    • Add System.Data and System.Data.SqlClient if they do not exist
  • Inside the class, add the code to connect to SQL Server
    • Create objects: SqlConnection, SqlCommand
    • The connection string takes this form:
    • server=cgtweb1.tech.purdue.edu;uid=<yourID>;pwd=<yourPass>;database=<yourID>
  • Select all distinct titles from the Employee table and list in a drop down list
  • Bind that data to the DropDownList
• Code
  • aspx
  • cs
     
    
2. Create a new ASP.NET page (Lab04b.aspx)
• In Server Explorer, open a New Query and run this different script
  • ERD view ---- design view
  • Connect to the DBMS in Server Explorer of Visual Studio
  • Right click on the DBMS connection in Server Explorer > Select New Query
  • Paste this SQL script
  • Click the 'Execute' button
  • You are done with this script, you can close the SQLQuery window
     
• Finished solution example
   
• Lab04b.aspx
• Simple page with 4 buttons on it [ example view ]
  • Drag and drop one ScriptManager from Ajax Extensions in the Toolbox onto your code in between the div tags.
  • Drag and drop one UpdatePanel from Ajax Extensions in the Toolbox onto your code immediately following the ScriptManager.
    • Inside the UpdatePanel, you need a Triggers section and a ContentPanel section.
    • Inside the ContentPanel section:
      • Include four <asp:Button> controls
      • Include one <asp:DataGrid> control
    • Inside the Triggers section:
      • Include four <asp:AsyncPostBackTrigger> elements, one for each Button.
      
       
• Lab04b.aspx.cs
• Outside the class
  • Add System.Data and System.Data.SqlClient if they do not exist
• Inside the class
  • Declare the objects you need to work with
    • SqlConnection, SqlCommand, SqlDataReader, String
  • Declare and Initialize the connection string in one statement
     
  • ExecNonQuery function
    • This function will be called from the insert, update, and delete event handlers
    • Create a connection using SqlConnection
    • Send the sql query using SqlCommand
    • Open the connection
    • Call the ExecuteNonQuery function of the SqlCommand object
    • Close the connection
  • Select event handler
    • Select all the data and bind it to the DataGrid using DataBind
    • Use the ExecuteReader method of the SqlCommand object
    • Code is very similar to the first part above
  • Insert event handler
    • Form a generic insert statement and send it to ExecNonQuery to execute
    • Call the select function to redisplay the data in the browser
  • Update event handler
    • Select the Max ProductID using DataSet and DataTable
    • Update the record with Max ProductID and set Title = 'whatever you want'
    • Send it to ExecNonQuery to execute
    • Call the select function redisplay the data in the browser
  • Delete event handler
    • Delete the record with Max ProductID
    • Send it to ExecNonQuery to execute
    • Call the select function redisplay the data in the browser
  • Page_Load function
    • Do nothing
• Code
  • aspx
  • cs

Grading