INF: Handling SQL Server Error Messages When a Stored Procedure is Used in the SqlDataAdapter UpdateCommand Method (811482)

MORE INFORMATION

The SqlDataAdapter object serves as a bridge between a DataSet object and a
SQL Server database. SqlDataAdapter is an intermediary object that populates
a DataSet object with data that is retrieved from a SQL Server database. By using the
UpdateCommand method of SqlDataAdapter, you can update the database to reflect
the changes (such as inserts, updates, and deletes) made to the DataSet object.
You can use a Transact-SQL command or a stored procedure in the UpdateCommand
method. If a stored procedure is used in the UpdateCommand method of SqlDataAdapter,
the client may not receive the error messages that are raised by the stored procedure
after the result set is returned.

Note: Manipulating the SET NOCOUNT or the severity level of the RAISERROR message in
the stored procedure does not change this behavior.

You can use SqlDataReader as an alternative solution to this problem. The SqlDataReader
provides more flexibility in handling store procedures that return multiple result sets. The sample code, in the section that follows, demonstrates how to use SqlDataReader to update a data source and trap the error message that is raised by the stored procedure.

Create a Sample SQL Server Table and Stored Procedure

To create a SQL Server table and stored procedure that you can use in the Visual C# .NET
code samples, follow these steps:

1. Open SQL Query Analyzer, and then connect to the database where you want to create the sample table. The code samples in this article use the Northwind database that is included with Microsoft SQL Server.
2. To create a sample table that is named CustTest, and to insert a record into the table, run the following Transact-SQL statements:

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

3. Create a stored procedure named updateCustTest. You can use this stored procdure to reflect the modified rows in the DataSet back to the source database. The updateCustTest stored procedure returns a recordset, updates the database and raises an error by using RAISERROR. To create the updateCustTest stored procedure, run the following Transact-SQL statements:

   Create procedure updateCustTest(@Custid varchar(20),  @custName varchar(20))as
   Begin
       select custid, custName from custTest where custid= @custid
   		  update custTest set custName = @custName 
   			             where custId = @custid
   		  RAISERROR('Test error raised by Raiserror',16,1)
   End
   

Visual C# .NET Client Application

This section describes the steps to follow to reproduce the behavior and the steps to follow to resolve the behavior.

1. To create a new Visual C# .NET console application, follow these steps:
   1. Start Microsoft Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. Click Visual C# Project under Project Types, and then click Console Application under Templates.
2. Replace the default contents of Class1 with the following code:

   using System;
   using System.Data;
   using System.Data.SqlTypes;
   using System.Data.SqlClient;
   using System.Data.Common;
   
   namespace SQLDataAdapterConsole
   {
   	class Class1
   	{
   		
   		public static void Main()
   		{
   			SqlConnection cn = new SqlConnection();
   			SqlDataAdapter sqlDa;
   		
   			//Set the connection string of the SqlConnection object to connect
   			//to the SQL Server database where you created the sample table.
   			cn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=northwind;Data Source=ServerName"; 
   			cn.Open();
   
   			//Initialize the SqlDataAdapter object by specifying a Select command 
   			//that retrieves data from the sample table.
   			sqlDa = new SqlDataAdapter("select * from custTest", cn);
   			
   			//This function uses Uptade method of DataAdapter to update the modified DataSet data in the source database
   			//Notice that the error raised by the Update Command Stored Procedure cannot be trapped
   			UpdateUsingDataAdapter(sqlDa);
   			
   			//This function uses DataReader to update the modified data in the source database
   			//Notice that the error raised by the Update Command Stored Procedure can be trapped
   			UpdateUsingDataReader(sqlDa);
   
   			cn.Close();
   			Console.ReadLine();
   		}
   
   		// This function uses Update method of SqlDataAdapter for Updating the data
   		public static void UpdateUsingDataAdapter(SqlDataAdapter da)
         {
   			Console.WriteLine("Updating Database using Update Command of SqlDataAdapter");
   			SqlCommand updateCmd;
   			DataSet CustomersDataSet = new DataSet();
   			
   			//Create update command for SqlDataAdapter
   			updateCmd = new SqlCommand("exec updateCustTest @pCustId, @pCustName", da.SelectCommand.Connection);
   			
   			//Add parameters for the stored procedure
   			updateCmd.Parameters.Add(new SqlParameter("@pCustName", SqlDbType.VarChar));
   			updateCmd.Parameters["@pCustName"].SourceVersion = DataRowVersion.Current;
   			updateCmd.Parameters["@pCustName"].SourceColumn = "CustName";
   
   			updateCmd.Parameters.Add(new SqlParameter("@pCustId", SqlDbType.VarChar));
   			updateCmd.Parameters["@pCustId"].SourceVersion = DataRowVersion.Original;
   			updateCmd.Parameters["@pCustId"].SourceColumn = "CustId";
   
   			//Initialize the UpdateCommand of SqlDataAdapter
   			da.UpdateCommand = updateCmd;
   
   			//Populate the DataSet by running the Fill method of the SqlDataAdapter.
   			da.Fill(CustomersDataSet,"custTest");
   			
   			//Write out the value in the CustName field before updating the data using the DataSet.
   			Console.WriteLine("Customer Name before Update : " + CustomersDataSet.Tables["CustTest"].Rows[0]["CustName"]);
   
   			//Modify the value of the CustName field.
   			CustomersDataSet.Tables["CustTest"].Rows[0]["CustName"] = "Bill";
   
   			// Get the dataSet that is modified.
   			DataSet modifiedDS = CustomersDataSet.GetChanges(DataRowState.Modified);
   			
   			try
   			{
   				da.Update(modifiedDS,"CustTest");
   				Console.WriteLine("Customer name is updated to 'Bill'.");
   				Console.WriteLine("The Stored Procedure error is not trapped.");
   			}
   			catch
   			{
   				Console.WriteLine("Stored Procedure error trapped.");
   			}
   		}
   
   		//This function uses DataReader for updating the  
   		public static void UpdateUsingDataReader(SqlDataAdapter da)
   		{
   			Console.WriteLine("\n\nUpdating Database using DataReader to trap the Stored Procedure error.");
   
   			DataSet CustomersDataSet = new DataSet();
   			SqlDataReader mySqlDataReader;
   			
   			//Populate the DataSet by running the Fill method of the SqlDataAdapter.
   			da.Fill(CustomersDataSet,"custTest");
   
   			//Modify the value of the CustName field.
   			CustomersDataSet.Tables["CustTest"].Rows[0]["CustName"] = "Michel";
   			
   			// Get the dataSet that is modified.
   			DataSet modifiedDS = CustomersDataSet.GetChanges(DataRowState.Modified);
   			SqlCommand updateCmd;
   			foreach (DataRow dr in modifiedDS.Tables[0].Rows)
   			{
   				// Set the update command for modified data in the DataSet.
   				updateCmd = new SqlCommand("updateCustTest '" + dr[0] +"', '" + dr[1] + "'", da.SelectCommand.Connection);
   				try
   				{
   					// Execute the update command.
   					mySqlDataReader = updateCmd.ExecuteReader();
   					while(!mySqlDataReader.IsClosed)
   					{
   						while (mySqlDataReader.Read())
   						{
   							Console.WriteLine("Customer name returned by the procedure :" + mySqlDataReader.GetValue(1));
   						}
   						// Get the results set returned by the update command.
   						mySqlDataReader.NextResult();
   						Console.WriteLine("\n");	
   					}
   					mySqlDataReader.Close();
   					Console.WriteLine("Customer Name updated successfully");
   				}
   				catch(SqlException e)
   				{
   					// Get the error collection and write the error on the console.
   					Console.WriteLine("Error Number is : " + e.Number);
   					Console.WriteLine("Error Message is : " + e.Message);
   
   				}
   			}
   		}
   	}
   }
   

3. Modify the connection string as appropriate for your environment.
4. Save, and then run the application. Watch the message as the program executes.
5. Press any key to dismiss the console window and stop the application.