The Manage Files Page

The MngFiles.aspx page allows users to manage their own files. The BindDataToControls() method is used by this page to populate the user interface objects (GroupList and FileList). This method can be seen in Listing 22.4.

The first thing that this method does is create a SqlConnection object. A try/catch/finally construct is used to catch any exceptions that the database access methods might throw. The catch block simply shows the exception message in the ErrorMessage Label object, and the finally block closes the SqlConnection object if it is open.

In the try block, the code that does the work is found. First, the SqlConnection object is opened, then a SQL string is created the will retrieve the ID and Name from the FileGroup table. The SQL is qualified with a where clause that retrieves only the file groups for the currently logged-in user. The following code creates the SQL string:

C#

 string strSql = "Select ID,Name from FileGroup where OwnerID"] ) + " order by name"; 

VB

 Dim strSql as string = "Select ID,Name from FileGroup " + _   where OwnerID") ) + _   " order by name"; 

If the user ID is 5, the SQL will be the following:

 Select ID,Name from FileGroup where OwnerID=5 order by name 

Because we're going to use the data set more than once, a SqlDataReader won't suffice. That's because once you databind with a SqlDataReader, it will not contain any data. The SqlDataReader object is a forward-reading data reader and doesn't hold a recordset. Instead, we'll use a DataSet object. This object will be populated with the SqlDataAdapter.Fill() method and will contain the complete recordset. For this reason, the data will persist after each use until the object is disposed when it goes out of scope. The following code shows how the DataSet object is populated, and then how it is bound to the GroupList object:

 SqlDataAdapter objDataAdapter = new SqlDataAdapter( strSql,   objConnection ); DataSet objDataSet = new DataSet(); objDataAdapter.Fill( objDataSet ); GroupList.DataSource = objDataSet; GroupList.DataBind(); 

After binding the data to the GroupList object, we'll use the DataSet object to retrieve the set of files that belong to a particular group. The code loops through, and for each group that is in the recordset (which is contained in the DataSet object), we'll retrieve the files that belong to the group. But first, we must retrieve the DataGrid object that is a child object of the GroupList object. We'll do this by using the FindControl() method, and we'll specify FileGrid as the control we want to find. The following code retrieves the DataGrid object that corresponds to the current group:

 DataGrid grid = (DataGrid) GroupList.Items[i].FindControl( "FileGrid" ); 

The SQL statement is constructed based in the group ID as follows:

C#

 "select Title,Filename,Version,ID from FileInfo " +     "where Group order by title"; 

VB

 "select Title,Filename,Version,ID from FileInfo " + _     "where Group order by title" 

If the group ID is 5, the following SQL will result:

 select Title,Filename,Version,ID from FileInfo where GroupID=5    order by title 

Once the SQL string is created, a SqlCommand object is instantiated using the SQL string and the SqlConnection object. A SqlDataReader object is returned when the SqlCommand object's ExecuteReader() method is called. The recordset is then bound with the DataGrid object.

Listing 22.4 The BindDataToControls() Method Gets the Group Names, Binds Them to a Control, and Gets the File Information to Bind to a Control.

 private void BindDataToControls() {   // Create a connection object.  SqlConnection objConnection =   new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"]);   try   {     // Open the connection.     objConnection.Open();     string strSql = "Select ID,Name from FileGroup where OwnerID"] ) + " order by name";     SqlDataAdapter objDataAdapter = new SqlDataAdapter( strSql,         objConnection );     DataSet objDataSet = new DataSet();     objDataAdapter.Fill( objDataSet );     GroupList.DataSource = objDataSet;     GroupList.DataBind();     int nCount = objDataSet.Tables[0].Rows.Count;     for( int i=0; i<nCount; i++ )     {       DataGrid grid =         (DataGrid) GroupList.Items[i].FindControl( "FileGrid" );       if( grid != null )       {         strSql = "select Title,Filename,Version,ID from FileInfo " +             "where Group order by title";         // Create a command object.         SqlCommand objCommand = new SqlCommand( strSql, objConnection );         SqlDataReader objReader = objCommand.ExecuteReader();         grid.DataSource = objReader;         grid.DataBind();         objReader.Close();       }     }   }   catch( Exception ex )   {     // Alert the user to the error.     ErrorMessage.Text = ex.Message.ToString();   }   finally   {     // Close the connection.     if( objConnection.State == ConnectionState.Open )     {       objConnection.Close();     }   } } 

Listing 22.5 contains the remaining code that's behind the MngFiles.aspx page (see Listing 22.4 for the first part of the code). The Page_Load() method simply calls the BindDataToControls() method if the IsPostBack property is false.

Another method in Listing 22.5 is the AddGroup_Click() method. This method adds a group to the database for the user who is logged in and takes the group name out of a TextBox object named GroupName.

The AddGroup_Click() method creates a SqlConnection object that the database objects will use. And as with all of the other methods that do database access that we've looked at in this chapter, there's a try/ catch/finally construct to handle any database exceptions that are thrown. In the try block, though, is the code that adds the group to the database. This code opens the SqlConnection object with the Open() method, creates a SQL string containing the group name and the owner ID (the logged-in user's ID), calls the ExecuteNonQuery() method, blanks out the Password and Name TextBox objects, and calls the BindDataToControl() method. The call to BindDataToControl() is so that the user interface will be repopulated with the newly created group.

The last method in Listing 22.5 is the Main_Click() method. This method is fired when the user clicks the Main Menu button. It simply redirects the user back to the main menu.

Listing 22.5 This Code Is behind the MngFiles.aspx page.

 private void Page_Load(object sender, System.EventArgs e) {   if( !IsPostBack )   {     BindDataToControls();   } } private void AddGroup_Click(object sender, System.EventArgs e) {   // Create a connection object.  SqlConnection objConnection =   new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"]);   try   {     // Open the connection.     objConnection.Open();     string strSql = "Insert into FileGroup (Name,OwnerID) VALUES ('" +         SafeSql( GroupName.Text ) + "'," + Convert.ToString( Session["ID"] ) + ")";     // Create a command object.     SqlCommand objCommand = new SqlCommand( strSql, objConnection );     objCommand.ExecuteNonQuery();     Password.Text = "";     GroupName.Text = "";     BindDataToControls();   }   catch( Exception ex )   {     // Alert the user to the error.     ErrorMessage.Text = ex.Message.ToString();   }   finally   {     // Close the connection.     if( objConnection.State == ConnectionState.Open )     {       objConnection.Close();     }   } } private void Main_Click(object sender, System.EventArgs e) {   Response.Redirect( "Default.aspx" ); }