Aggiornamento di un database di SQL Server con l'oggetto SqlDataAdapter in Visual C# .NET

In questo articolo sono contenuti esempi di codice Microsoft Visual C# .NET che spiegano come utilizzare l'oggetto SqlDataAdapter per aggiornare un database di SQL Server in base alle modifiche apportate ai dati ed eseguite su un oggetto DataSet compilato con dati provenienti da una tabella del database.

Requisiti

Nell'elenco che segue vengono indicati l'hardware, il software, l'infrastruttura di rete, le conoscenze, le abilità e i service pack richiesti:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server o Windows NT 4.0 Server
• Microsoft Visual Studio .NET
• Microsoft Visual C# .NET
• Microsoft SQL Server 7.0 o versione successiva

In questo articolo si presume la conoscenza dei seguenti argomenti:

• Visual C# .NET
• Nozioni fondamentali e sintassi di ADO.NET

Descrizione della tecnica

L'oggetto SqlDataAdapter funge da ponte tra un oggetto ADO.NET DataSet e un database di SQL Server. SqlDataAdapter è un oggetto intermedio che consente di compilare un oggetto ADO.NET DataSet con dati recuperati da un database di SQL Server, quindi di aggiornare il database in base alle modifiche (inserimenti, aggiornamenti ed eliminazioni) apportate ai dati utilizzando l'oggetto DataSet.

Le proprietà InsertCommand, UpdateCommand e DeleteCommand dell'oggetto SqlDataAdapter aggiornano il database con le modifiche ai dati eseguite su un oggetto DataSet. Tali proprietà sono oggetti SqlCommand che specificano i comandi INSERT, UPDATE e DELETE di Transact SQL, utilizzati per inserire le modifiche al dataset nel database di destinazione. Gli oggetti SqlCommand assegnati a queste proprietà possono essere creati manualmente nel codice o generati automaticamente mediante l'oggetto SqlCommandBuilder.

Nel primo esempio di codice di questo articolo viene mostrato come utilizzare l'oggetto SqlCommandBuilder per generare automaticamente la proprietà UpdateCommand dell'oggetto SqlDataAdapter. Nel secondo esempio viene presentato un caso in cui non è possibile utilizzare la generazione automatica dei comandi. Vi viene mostrato come creare e utilizzare manualmente un oggetto SqlCommand come la proprietà UpdateCommand di un oggetto SqlDataAdapter.

Creazione della tabella di SQL Server di esempio

Per creare una tabella di SQL Server di esempio utilizzabile negli esempi di codice di Visual C# .NET riportati in questo articolo, attenersi alla seguente procedura:

1. Aprire l'Analizzatore query di SQL Server e connettersi al database in cui creare la tabella di esempio. Per gli esempi di codice riportati in questo articolo viene utilizzato il database Northwind fornito con Microsoft SQL Server.
2. Per creare una tabella di esempio denominata CustTest e per inserirvi un record, eseguire le seguenti istruzioni di Transact SQL:

   Create Table CustTest
   (
    CustID int primary key,
    CustName varchar(20)
   )
   
   Insert into CustTest values(1,'John')
   					

Esempio di codice 1: Comandi generati automaticamente

Se l'istruzione SELECT utilizzata per recuperare i dati che popolano un DataSet si basa su una singola tabella di database, è possibile utilizzare l'oggetto CommandBuilder per generare automaticamente le proprietà DeleteCommand, InsertCommand e UpdateCommand di DataAdapter. In tal modo si semplifica e si riduce il codice necessario per eseguire operazioni INSERT, UPDATE e DELETE.

Il requisito minimo esige l'impostazione della proprietà SelectCommand in modo che i comandi vengano generati automaticamente. Lo schema della tabella recuperato da SelectCommand determina la sintassi delle istruzioni INSERT, UPDATE e DELETE generate automaticamente.

La proprietà SelectCommand deve inoltre restituire una chiave primaria o una colonna univoca. In assenza di tale chiave o colonna, verrà restituita un'eccezione InvalidOperation e i comandi non verranno generati.

Per creare un'applicazione console di esempio di Visual C# .NET che dimostri l'utilizzo dell'oggetto SqlCommandBuilder per generare automaticamente le proprietà DeleteCommand, InsertCommand e UpdateCommand dell'oggetto SqlCommand per un oggetto SqlDataAdapter, attenersi alla procedura seguente:

1. Per creare una nuova applicazione console di Visual C# .NET, attenersi alla procedura seguente:
   1. Avviare Microsoft Visual Studio .NET.
   2. Scegliere Nuovo dal menu File, quindi fare clic su Progetto.
   3. Fare clic su Progetto di Visual C# in Tipi progetto, quindi fare clic su Applicazione console in Modelli.
2. Sostituire il contenuto predefinito di Class1 con il codice riportato di seguito:

   using System.Data;
   using System.Data.SqlClient;
   using System;
   namespace Q308507 {
   
     class Class1 {
        static void Main(string[] args)	{
     
           SqlConnection cn = new SqlConnection();
           DataSet CustomersDataSet = new DataSet();
           SqlDataAdapter da;
           SqlCommandBuilder cmdBuilder;
     
           //Set the connection string of the SqlConnection object to connect
           //to the SQL Server database in which you created the sample
           //table.
           cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;";
   
           cn.Open();      
   
           //Initialize the SqlDataAdapter object by specifying a Select command 
           //that retrieves data from the sample table.
           da = new SqlDataAdapter("select * from CustTest order by CustId", cn);
   
           //Initialize the SqlCommandBuilder object to automatically generate and initialize
           //the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
           cmdBuilder = new SqlCommandBuilder(da);
   
           //Populate the DataSet by running the Fill method of the SqlDataAdapter.
           da.Fill(CustomersDataSet, "Customers");
   
           //Display the Update, Insert, and Delete commands that were automatically generated
           //by the SqlCommandBuilder object.
           Console.WriteLine("Update command Generated by the Command Builder : ");
           Console.WriteLine("==================================================");
           Console.WriteLine(cmdBuilder.GetUpdateCommand().CommandText);
           Console.WriteLine("         ");
   
           Console.WriteLine("Insert command Generated by the Command Builder : ");
           Console.WriteLine("==================================================");
           Console.WriteLine(cmdBuilder.GetInsertCommand().CommandText);
           Console.WriteLine("         ");        
   
           Console.WriteLine("Delete command Generated by the Command Builder : ");
           Console.WriteLine("==================================================");
           Console.WriteLine(cmdBuilder.GetDeleteCommand().CommandText);
   	Console.WriteLine("         ");
   
           //Write out the value in the CustName field before updating the data using the DataSet.
           Console.WriteLine("Customer Name before Update : " + CustomersDataSet.Tables["Customers"].Rows[0]["CustName"]);
   
           //Modify the value of the CustName field.
           CustomersDataSet.Tables["Customers"].Rows[0]["CustName"] = "Jack";
   
           //Post the data modification to the database.
           da.Update(CustomersDataSet, "Customers");        
   
           Console.WriteLine("Customer Name updated successfully");
   
           //Close the database connection.
           cn.Close();
   
           //Pause
           Console.ReadLine();
         }
      }
   
   }

3. Modificare la stringa di connessione secondo le esigenze del proprio ambiente.
4. Salvare ed eseguire l'applicazione. Si apre una finestra della console in cui viene visualizzato il seguente output:

   Update command Generated by the Command Builder: 
   ==================================================
   UPDATE CustTest SET CustID = @p1 , CustName = @p2 WHERE ( CustID = @p3 AND CustName = @p4 )
            
   Insert command Generated by the Command Builder: 
   ==================================================
   INSERT INTO CustTest( CustID , CustName ) VALUES ( @p1 , @p2 )
            
   Delete command Generated by the Command Builder: 
   ==================================================
   DELETE FROM  CustTest WHERE ( CustID = @p1 AND CustName = @p2 )
         
   Customer Name before Update: John
   Customer Name updated successfully
   						

5. Premere un tasto per chiudere la finestra della console e arrestare l'applicazione.

Esempio di codice 2: Creazione ed inizializzazione manuale della proprietà UpdateCommand

L'output generato dall'esempio di codice 2 indica che la logica per la generazione automatica di comandi per le istruzioni UPDATE è basata sulla concorrenza ottimistica. Ossia, i record non vengono bloccati per la modifica, e gli altri utenti o processi possono modificarli in qualsiasi momento.

Poiché un record può essere modificato dopo essere stato restituito dall'istruzione SELECT ma prima dell'esecuzione dell'istruzione UPDATE, l'istruzione UPDATE generata automaticamente contiene una clausola WHERE tale da consentire l'aggiornamento di una riga solo se contiene tutti i valori originali. In tal modo si evita la sovrascrittura dei nuovi dati. Se un'istruzione UPDATE generata automaticamente tenta di aggiornare una riga eliminata o non contenente i valori originali di DataSet, il comando non incide su alcun record, e viene generata l'eccezione DBConcurrencyException. Per provare tutto ciò con il codice dell'esempio di codice 1, eseguire il codice in Visual Studio Debugger, impostare un punto di interruzione dopo la compilazione di DataSet ma prima dell'aggiornamento del database, quindi eliminare la riga della tabella derivante dall'Analizzatore query di SQL. La chiamata Update genera quindi l'eccezione.

Se si desidera completare l'istruzione UPDATE a prescindere dai valori originali, è necessario impostare esplicitamente la proprietà UpdateCommand per DataAdapter senza fidarsi della generazione automatica dei comandi.

Per creare e inizializzare manualmente la proprietà UpdateCommand dell'oggetto SqlDataAdapter utilizzato nell'esempio di codice 1, attenersi alla procedura seguente:

1. Sostituire il codice esistente nella funzione Main di Class1 nell'applicazione console di Visual C# .NET creata nella sezione Esempio di codice 1: Comandi generati automaticamente con il codice seguente:

   SqlConnection cn = new SqlConnection();
   DataSet CustomersDataSet = new DataSet();
   SqlDataAdapter da;
   SqlCommand DAUpdateCmd;
   
   cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;";
   cn.Open();
   
   da = new SqlDataAdapter("select * from CustTest order by CustId", cn);
   
   //Initialize the SqlCommand object that will be used as the UpdateCommand for the DataAdapter.
   
   //Note that the WHERE clause uses only the CustId field to locate the record to be updated.
   DAUpdateCmd = new SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection);
   
   //Create and append the parameters for the Update command.
   DAUpdateCmd.Parameters.Add(new SqlParameter("@pCustName", SqlDbType.VarChar));
   DAUpdateCmd.Parameters["@pCustName"].SourceVersion = DataRowVersion.Current;
   DAUpdateCmd.Parameters["@pCustName"].SourceColumn = "CustName";
   
   DAUpdateCmd.Parameters.Add(new SqlParameter("@pCustId", SqlDbType.Int));
   DAUpdateCmd.Parameters["@pCustId"].SourceVersion = DataRowVersion.Original;
   DAUpdateCmd.Parameters["@pCustId"].SourceColumn = "CustId";
   
   //Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter.
   da.UpdateCommand = DAUpdateCmd;        
   da.Fill(CustomersDataSet, "Customers");        
   
   Console.WriteLine("Customer Name before Update : " + CustomersDataSet.Tables["Customers"].Rows[0]["CustName"]);
   CustomersDataSet.Tables["Customers"].Rows[0]["CustName"] = "Jack";
   da.Update(CustomersDataSet, "Customers");        
   
   Console.WriteLine("Customer Name updated successfully");
   
   cn.Close();
   Console.ReadLine();
   					

2. Modificare la stringa di connessione secondo le esigenze del proprio ambiente.
3. Ripetere i passaggi 1-4 della sezione Esempio di codice 1: Comandi generati automaticamente. Si noti che non viene più generata l'eccezione DBConcurrencyException.