Aggiornamenti di dati in un database SQL

In questa sezione viene illustrato l'utilizzo di un'applicazione Web per l'aggiornamento di campi di dati in un database esistente.

Nell'esempio seguente viene mostrata una pagina con una colonna sul lato sinistro di DataGrid contenente un collegamento denominato Edit in ogni riga. Se si fa clic sul collegamento, tutti i campi della riga selezionata si trasformano in caselle di testo contenenti i dati esistenti. È possibile modificare i campi, in modo che contengano nuove informazioni. Una volta completata la modifica di un campo, fare clic sul collegamento Update per aggiornare nel database i dati relativi a tale riga.

Per visualizzare l'esecuzione di un esempio simile, eseguire l'esempio DataGrid6.aspx in Guide rapide di ASP.NET.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
   Dim myConnection As SqlConnection
   '  Create a connection to the "pubs" SQL database located on the 
   ' local computer. 
   Sub Page_Load(Src As Object, E As EventArgs) 
      myConnection = New SqlConnection("server=localhost;" _ 
         & "database=pubs;Trusted_Connection=Yes")
      ' Determine whether this page is a postback. If it is not a
      ' postback, call BindGrid.
      If Not IsPostBack Then 
         BindGrid()
      End If
   End Sub

   ' Create an index to the DataGrid row that is clicked and 
   ' call BindGrid.
   Sub MyDataGrid_Edit(sender As Object, E As DataGridCommandEventArgs)
      MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
      BindGrid()
   End Sub

   ' Cancel resets the index to the row's previous settings.
   Sub MyDataGrid_Cancel(sender As Object, E As DataGridCommandEventArgs)
      MyDataGrid.EditItemIndex = -1
      BindGrid()
   End Sub

   ' When the  Update link is clicked, build a SQL UPDATE command,    
   ' connect to the database, update the row's information in the 
   ' database, and rebind the DataGrid to show the updated information.
   Public Sub MyDataGrid_Update(sender As Object, _
      E As DataGridCommandEventArgs)
      Dim updateCmd As String = "UPDATE Authors SET au_id = @Id," _
         & " au_lname = @LName, au_fname = @FName, phone = @Phone," _
         & " address = @Address, city = @City, state = @State," _
         & " zip = @Zip, contract = @Contract WHERE au_id = @Id;"
      Dim  myCommand As SqlCommand = New SqlCommand(updateCmd, _
         myConnection)
      myCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@LName", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@FName", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Phone", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Address", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@City", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@State", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Zip", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Contract", _
         SqlDbType.VarChar))
      
      ' Initialize the SqlCommand "@ID" parameter to the ID of the row 
      ' that must be clicked.
      myCommand.Parameters("@Id").Value = _
         MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
      ' Create an array of column names.
      Dim cols() As String = {"@Id","@LName","@FName","@Phone", _
         "@Address", "@City","@State","@Zip","@Contract"}
      ' Skipping the first, second, and last columns, iterate through the 
      ' columns, checking for empty values. If an empty value is found, 
      '  display a message box. Also initialize the SqlCommand 
      ' parameter values.
      Dim numCols As Integer = E.Item.Cells.Count
      Dim i As Integer
      Dim colvalue As String
      Dim txtBox As Textbox
      For i = 2 To numCols-1
         txtBox = E.Item.Cells(i).Controls(0)
         colvalue = txtBox.Text
         If (i<6 And colvalue = "") 
            Message.InnerHtml = "ERROR: Null values not allowed for " _
               & "Author ID, Name or Phone"
            Message.Style("color") = "red"
            Exit Sub
         End If
         myCommand.Parameters(cols(i-1)).Value = colvalue
      Next i
      ' Append the last field, converting true/false values to 0/1.
      txtBox=E.Item.Cells(numCols-1).Controls(0)
      If (String.Compare(txtBox.Text, "true", true) = 0) Then
         myCommand.Parameters("@Contract").Value =  "1"
      Else
         myCommand.Parameters("@Contract").Value =  "0"
         ' Connect to the database and update the information.
         myCommand.Connection.Open()
         ' Test  whether the data was updated, and display the 
         ' appropriate message to the user.
         Try 
            myCommand.ExecuteNonQuery()
            Message.InnerHtml = "<b>Record Updated.</b><br>"
            MyDataGrid.EditItemIndex = -1
         Catch ex As SqlException
            If ex.Number = 2627 Then
               Message.InnerHtml = "ERROR: A record already exists" _
                  & " with the same primary key"
            Else
               Message.InnerHtml = "ERROR: Could not update record," _
                  & " please ensure the fields are correctly filled out."
               Message.Style("color") = "red"
            End If
         End Try

         ' Close the connection.
         myCommand.Connection.Close()
         ' Rebind the DataGrid to show the updated information.
         BindGrid()
      End If
   End Sub

   ' The BindGrid procedure connects to the database and implements
   ' a SQL SELECT query to get all the data in the "Authors" table.
   Public Sub BindGrid() 
      Dim myConnection As SqlConnection = _
         New SqlConnection("server=localhost;" _
         & "database=pubs;Trusted_Connection=Yes")
      Dim myCommand As SqlDataAdapter = New SqlDataAdapter("SELECT *" _
         & " FROM authors", myConnection)
      Dim ds As DataSet= New DataSet()
      myCommand.Fill(ds)
      MyDataGrid.DataSource = ds
      MyDataGrid.DataBind()
   End Sub
</script>

<%--  Display the data in the body of the page. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Updating a Row of Data.</font></h3>
      <span id="Message" EnableViewState="false" 
         style="font:arial 11pt;" runat="server"/><p>
      <ASP:DataGrid id="MyDataGrid" runat="server"
         Width="800"
         BackColor="#ccccff" 
         BorderColor="black"
         ShowFooter="false" 
         CellPadding=3 
         CellSpacing="0"
         Font-Name="Verdana"
         Font-Size="8pt"
         HeaderStyle-BackColor="#aaaadd"
         OnEditCommand="MyDataGrid_Edit"
         OnCancelCommand="MyDataGrid_Cancel"
         OnUpdateCommand="MyDataGrid_Update"
         DataKeyField="au_id"
      >
      <Columns>
         <ASP:EditCommandColumn EditText="Edit" CancelText="Cancel" 
            UpdateText="Update"/>
      </Columns>
   </ASP:DataGrid>
</form>
</body>
</html>

[C#]
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Text"%>
<html>
<script language="C#" runat="server">
   SqlConnection myConnection;

   protected void Page_Load(Object Src, EventArgs E) 
   {
      //  Create a connection to the "pubs" SQL database located on the
      // local computer.
      myConnection = new SqlConnection ("server=localhost;" +
         "database=pubs;Trusted_Connection=Yes");
      // Determine whether the page is a postback. If it is not a 
      // postback, call BindGrid.
      if (!IsPostBack) BindGrid();
   }
   // Create an index to the DataGrid row that is clicked and 
   // call BindGrid.
   public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs E)
   {
      MyDataGrid.EditItemIndex = (int)E.Item.ItemIndex;
      BindGrid();
   }

   // Cancel resets the index to the row's previous settings.
   public void MyDataGrid_Cancel(Object sender, 
      DataGridCommandEventArgs E) 
   {
      MyDataGrid.EditItemIndex = -1;
      BindGrid();
   }
   // When the  Update link is clicked, build a SQL UPDATE command,    
   // connect to the database, update the row's information in the 
   // database, and rebind the DataGrid to show the updated information.
   public void MyDataGrid_Update(Object sender, 
      DataGridCommandEventArgs E) 
   {
      String updateCmd = "UPDATE Authors SET au_id = @Id," +
         " au_lname = @LName, au_fname = @FName, phone = @Phone," +
         " address = @Address, city = @City, state = @State," +
         " zip = @Zip, contract = @Contract WHERE au_id = @Id;";
      SqlCommand myCommand = new SqlCommand(updateCmd, myConnection);
      myCommand.Parameters.Add(new SqlParameter("@Id",
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@LName", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@FName", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Phone", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Address", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@City", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@State", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Zip", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Contract", 
         SqlDbType.VarChar));
      // Initialize the SqlCommand "@Id" parameter to the ID of the
      // row that must be clicked.
      myCommand.Parameters["@Id"].Value = 
         MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
      // Create an array of column names.
      String[] cols = {"@Id","@LName","@FName","@Phone",
         "@Address", "@City","@State","@Zip","@Contract"};
      // Iterate through the columns, checking for empty values. 
      // Skip the first, second, and last columns. If an empty value 
      // is found,  display an error message box. Also initialize the 
      // SqlCommand parameter values.
      int numCols = E.Item.Cells.Count;
      for (int i=2; i<numCols-1; i++) 
      {
         String colvalue = ((TextBox)E.Item.Cells[i].Controls[0]).Text;
         if (i<6 && colvalue == "") 
         {
            Message.InnerHtml = "ERROR: Null values not allowed for" +
               " Author ID, Name or Phone";
            Message.Style["color"] = "red";
            return;
         }
         myCommand.Parameters[cols[i-1]].Value = colvalue;
      }
      // Append the last field, converting true/false values to 0/1.
      if (String.Compare(((TextBox)E.Item.Cells
         [numCols-1].Controls[0]).Text, "true", true)==0)
         myCommand.Parameters["@Contract"].Value =  "1"; 
      else
         myCommand.Parameters["@Contract"].Value =  "0"; 
         // Connect to the database and update the information.
         myCommand.Connection.Open();
         // Test  whether the data was updated and  display the 
         //appropriate message to the user.
         try 
         {
            myCommand.ExecuteNonQuery();
            Message.InnerHtml = "<b>Record Updated.</b><br>";
            MyDataGrid.EditItemIndex = -1;
         }
         catch (SqlException e) 
         {
            if (e.Number == 2627)
               Message.InnerHtml = "ERROR: A record already exists" +
                  " with the same primary key";
            else
               Message.InnerHtml = "ERROR: Could not update record," +
                 " please ensure the fields are correctly filled out";
               Message.Style["color"] = "red";
         }
      // Close the connection.
      myCommand.Connection.Close();
      // Show the updated information.
      BindGrid();
   }

   // The BindGrid procedure connects to the database and implements
   // a SQL SELECT query to get all data in the "Authors" table.
   public void BindGrid() 
   {
      SqlConnection myConnection = new SqlConnection(
         "server=localhost;database=pubs;Trusted_Connection=Yes");
      SqlDataAdapter myCommand = new SqlDataAdapter(
         "SELECT * FROM authors", myConnection);
      DataSet ds = new DataSet();
      myCommand.Fill(ds);
      MyDataGrid.DataSource=ds;
      MyDataGrid.DataBind();
   }
</script>

<%--  Display the data in the body of the page. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Updating a Row of Data.</font></h3>
      <span id="Message" EnableViewState="false" 
         style="font:arial 11pt;" runat="server"/><p>
      <ASP:DataGrid id="MyDataGrid" runat="server"
         Width="800"
         BackColor="#ccccff" 
         BorderColor="black"
         ShowFooter="false" 
         CellPadding=3 
         CellSpacing="0"
         Font-Name="Verdana"
         Font-Size="8pt"
         HeaderStyle-BackColor="#aaaadd"
         OnEditCommand="MyDataGrid_Edit"
         OnCancelCommand="MyDataGrid_Cancel"
         OnUpdateCommand="MyDataGrid_Update"
         DataKeyField="au_id"
      >
      <Columns>
         <ASP:EditCommandColumn EditText="Edit" CancelText="Cancel" 
            UpdateText="Update"/>
      </Columns>
   </ASP:DataGrid>
</form>
</body>
</html>

Vedere anche

Accesso ai dati tramite ASP.NET | Accesso ai dati tramite ADO.NET | Spazio dei nomi System.Web.UI.WebControls | Classe DataGrid