Database Connectivity Part II

Materials to turn in:

• Lab05 Project Folder
• Params.aspx
• Params.aspx.cs
• DbConn.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. In Server Explorer, open a New Query and run this 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
      
2. Finished solution example
   • Notice that there are not any page refreshes. When you hit the back button on the browser, you go to the previous page. This is a result of the Ajax extensions (ScriptManager and UpdatePanel)
   • You may need to start with Insert because somebody may have deleted all the data.
      
3. Params.aspx
• Simple page with 7 buttons on it
  • 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 seven <asp:Button> controls
      • Include one <asp:DataGrid> control
    • Inside the Triggers section:
      • Include seven <asp:AsyncPostBackTrigger> elements, one for each Button.
         
4. DbConn.cs
• Right click on the solution name > add > new item
  • Make sure C# is selected in the left column so that all possible options appear in the middle
  • Choose Class
  • Name it DbConn.cs
• Outside the class
  • Add System.Data and System.Data.SqlClient if they do not exist
• Inside of class DbConn
• declare and initialize your connection string
• declare: SqlDataAdapter dbSDA; DataSet dbDS; SqlDataReader dbDR; SqlConnection dbConnection; SqlCommand dbCmd
• Write public DbConn() constructor method (it will be empty)
• Create the function: public System.Data.DataSet createDataSet(String sqlPassedIn)
• Instantiate an instance of SqlConnection
• Open it
• Instantiate an instance of SqlCommand
• Instantiate an instance of SqlDataAdapter
• Set the SelectCommand
• Instantiate an instance of DataSet
• Fill the DataSet
• return the DataSet object
• Create the function: public System.Data.SqlClient.SqlDataReader createDataReader(String sqlPassedIn)
• Instantiate an instance of SqlConnection
• Open it
• Instantiate an instance of SqlCommand
• Set the DataReader object (dbDR) equal to the result of ExecuteReader
• return the DataReader object
   
5. Params.aspx.cs
• Outside the class
  • Add System.Data and System.Data.SqlClient if they do not exist
• Inside the class
  • To instantiate an instance of the DbConn class: DbConn myDbConnection = new DbConn();
  • Then you can access: myDbConnection.createDataReader(), and myDbConnection.createDataSet() and myDbConnection.connStr
     
  • selectAll_Click event handler
    • Select all the data and bind it to the DataGrid using DataBind
    • Use the myDbConnection.createDataReader()
  • selectOne_Click event handler
    • 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 myDbConnection.createDataSet()
    • Use SqlCommand Parameters
    • Use the ExecuteReader method of the SqlCommand object
  • insert_Click event handler
    • Form a generic insert statement
    • Use SqlCommand Parameters
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select_Click event handler to redisplay the data in the browser
  • update_Click event handler
    • 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 myDbConnection.createDataSet()
    • Use SqlCommand Parameters
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select_Click event handler to redisplay the data in the browser
  • deleteOne_Click event handler
    • 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 myDbConnection.createDataSet()
    • Use SqlCommand Parameters
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select_Click event handler to redisplay the data in the browser
  • deleteAll_Click event handler
    • Delete all the records from the table
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select_Click event handler to redisplay the data in the browser
  • truncate_Click event handler
    • Truncate the table
    • Use the ExecuteNonQuery method of the SqlCommand object
    • Call the select_Click event handler to redisplay the data in the browser
    • Notice the difference between performing a Delete All vs. Truncate

• Code
  • dbConn class
  • aspx
  • cs
     

Grading