How to transfer error (noncritical) using RAISERROR or something else, to the C# code and handle it without interrupting the stored procedure

Question

0.00/5 (No votes)

See more:

creating and populating the test table

declare @jj nvarchar(MAX) = N'[
{"text":"10","f_date":"1/11/20"},
{"text":"11","f_date":"1/11/21"},
{"text":"12","f_date":"1/11/22"}]';

select * into tt from OPENJSON(@jj) with ([text] nvarchar(50),f_date smalldatetime)

here is the stored procedure

CREATE or alter PROCEDURE PTest
	-- Add the parameters for the stored procedure here
	--@Param1 int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * from ttable
		 RAISERROR (N'Message %s', -- Message text.  
			   10, -- Severity,  
			   1, -- State,
			   N'first'
			   )
			   with nowait;
	SELECT * from ttable
		 RAISERROR (N'Message %s', -- Message text.  
           10, -- Severity,  
           1, -- State,
		   N'second'
		   )
		   with nowait;
END
GO

the question is:

how to transfer error (noncritical) using RAISERROR or something else, to the C# code and handle it without interrupting the stored procedure

What I have tried:

MSDN, C#, Transact-SQL, .NET, Microsoft SQL Server

Posted 16-Sep-19 23:44pm

po_saa

Updated 17-Sep-19 0:27am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

po_saa · Accepted Answer · 2019-09-17T00:28:00

solved

here is the code

class Program
  {
      static string sqlconn = string.Empty;
      static SqlConnection conn = new SqlConnection();
      static void Main(string[] args)
      {
          try
          {
              conn.ConnectionString = ConfigurationManager.ConnectionStrings["TestDBConnection"].ConnectionString;
          }
          catch (ConfigurationErrorsException ex)
          {
              Console.WriteLine(ex.Message);
          }
          DataSet dataset = new DataSet();
          using (var adapter = new SqlDataAdapter("PTest", conn))
          {
              adapter.FillError += Adapter_FillError;
              adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
              adapter.Fill(dataset);
          }

      }

      private static void Adapter_FillError(object sender, FillErrorEventArgs e)
      {
          Console.WriteLine($"Message {e.Errors}");
          e.Continue = true;
      }
  }

for the moment - the severity of an RAISERROR has to be between 11 and 19

How to transfer error (noncritical) using RAISERROR or something else, to the C# code and handle it without interrupting the stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

How to transfer error (noncritical) using RAISERROR or something else, to the C# code and handle it without interrupting the stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us