Department of Computer Graphics

CGT 456 Hypermedia Authoring II

ASP.NET SELECT, INSERT, UPDATE, DELETE, TRUNCATE

Instructions

This lab will help you finish the ramp-up to applying your full knowledge from ASP 3.0 to the .NET environment. Your controls should be the only thing that exists in the forward facing page. All of your C# code should exist in the .cs file. This lab will give you practice using DataGrid, ExecuteReader, Data binding, ExecuteNonQuery, and SqlCommand.Parameters. In addition, TRUNCATE will be introduced to demonstrate its difference from DELETE.

Materials to turn in:

• Lab06 Project Folder
• default.aspx
• default.aspx.cs
• defaultParams.aspx
• defaultParams.aspx.cs
• other associated project files

Procedures

1. Open SQL Server Query Analyzer and run this script
   • ERD view ---- design view
   • Open Query Analyzer
   • Load this SQL script
   • Click the 'Execute Query' button
   • You are done with this script
      
2. default.aspx
• Simple page with 4 buttons on it [ example view ]
  • Include four <asp:Button> controls
  • Include one <asp:DataGrid> control
     
3. default.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, SqlDataAdapter, DataSet, DataAdapter, String
  • Declare and Initialize the connection string in one statement
  • Declare the web controls defined in the aspx
     
  • Select function
    • Select all the data and bind it to the DataGrid using DataBind
    • Use the ExecuteReader method of the SqlCommand object
  • Insert function
    • Form a generic insert statement and ExecuteNonQuery
    • Call the select function to redisplay the data in the browser
  • Update function
    • Select the Max ProductID using DataSet and DataTable
    • Update the record with Max ProductID and set Title = 'whatever you want'
      • Add a check for the boundary case when no records exist
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function redisplay the data in the browser
  • Delete function
    • Select the Max ProductID using DataSet and DataTable
    • Delete the record with Max ProductID
      • Add a check for the boundary case when no records exist
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function redisplay the data in the browser
  • Page_Load function
    • Do nothing
       
4. defaultParams.aspx
• Simple page with 7 buttons on it [ example view ]
  • Include seven <asp:Button> controls
  • Include one <asp:DataGrid> control
     
5. defaultParams.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, SqlDataAdapter, DataSet, DataAdapter, String
  • Declare and Initialize the connection string in one statement
  • Declare the web controls defined in the aspx
     
  • Select function
    • Select all the data and bind it to the DataGrid using DataBind
    • Use the ExecuteReader method of the SqlCommand object
  • SelectOne function
    • Select the Max ProductID using DataSet and DataTable
    • Select the record with the Max ProductID and bind it to the DataGrid
      • Add a check for the boundary case when no records exist
    • Use SqlCommand Parameters
    • Use the ExecuteReader method of the SqlCommand object
  • Insert function
    • Form a generic insert statement
    • Use SqlCommand Parameters
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function to redisplay the data in the browser
  • Update function
    • Select the Max ProductID using DataSet and DataTable
    • Update the record with Max ProductID and set Title = 'whatever you want'
      • Add a check for the boundary case when no records exist
    • Use SqlCommand Parameters
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function to redisplay the data in the browser
  • Delete function
    • Select the Max ProductID using DataSet and DataTable
    • Delete the record with Max ProductID
      • Add a check for the boundary case when no records exist
    • Use SqlCommand Parameters
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function to redisplay the data in the browser
  • DeleteAll function
    • Delete all the records from the table
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function to redisplay the data in the browser
  • Truncate function
    • Truncate the table
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select function to redisplay the data in the browser
    • Notice the difference between performing a Delete All vs. Truncate

Grading