Returning an ASP.NET SQL Server Stored Procedure

Question

I have a stored procedure that does not require any parameters, and returns the value 0 and the message:

(94 row(s) affected) (1 row(s) affected) 

My question is: how to get messages:

 (94 row(s) affected) (1 row(s) affected) 

This is my .NET method that calls the stored procedure:

 public List<MessageClass> ChequesToUpdate() { message = new List<MessageClass>(); MessageClass item = new MessageClass(); try { using (connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection)) { command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); item.message = "message"; } } } catch (Exception e) { item.message = e.Message; } finally { connection.Close(); } message.Add(item); return message; } 

I want to post a message in item.message , how would I do this?

Answer 1

In your stored procedure, you can request @@ ROWCOUNT , which will give you the affected records. Now you can save this in a variable using the SET or SELECT , for example

 SET MyRecordCount = @@RowCount 

or

 SELECT MyRecordCount = @@RowCount 

Alternatively, if you have several operations in one procedure that you need to track, you can either create multi-valued variables, or call SET or SELECT several times or use the TABLE variable, for example.

 DECLARE @recordCount table (Records int not null) --RUN PROCEDURE CODE INSERT INTO @recordCount VALUES (@@ROWCOUNT) --RUN MORE PROCEDURECT CODE INSERT INTO @recordCount VALUES (@@ROWCOUNT) --RETURN THE Row Count SELECT Records FROM @recordCount 

Where will it insert the value @@ROWCOUNT into the table variable @recordCount

Next, to get this information, you will need to output the last row selection from the @recordCount table.

Finally, in your code, instead of the ExecuteNonQuery() method, you should use a data reader like.

 using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection)) { command.CommandType = CommandType.StoredProcedure; using (var reader = command.ExecuteReader()) { while (reader.Read()) { item.message = reader.GetString(0); } reader.Close(); } } } 

Now the message is actually an integer number of lines, which is not affected by the term (98) row affected , but if you really want the exact message to simply format the line as you wish.

 item.message = string.Format("({0}) rows affected", reader.GetInt32(0)) 

Answer 2

ExecuteNonQuery returns the total number of rows affected. So, if you want just the total number of rows, then you can simply get it using the following statement:

 var x = command.ExecuteNonQuery(); 

Otherwise, you need to use the user define RAISERROR in the stored procedure and catch it from the C # connection.InfoMessage event. I installed a test environment and tested it. I created a table and inserted some data to check the SQL and C # code. Please check below SQL and C # code.

SQL:

 Create Table psl_table ( [values] NVarChar(MAX) ) Insert Into psl_table Values('a') Insert Into psl_table Values('a') Insert Into psl_table Values('a') Insert Into psl_table Values('b') Insert Into psl_table Values('b') Insert Into psl_table Values('b') Insert Into psl_table Values('b') Insert Into psl_table Values('b') Insert Into psl_table Values('b') Insert Into psl_table Values('b') Create Proc MyStoredProcedure As Begin -- Declare a variable for Message Declare @Msg NVarChar(MAX) -- 1st SQL Statement Update psl_table Set [Values]='a' Where [Values]!='a' -- Generate the message and print that can get from C# Set @Msg = '(' + Convert(NVarChar,@@RowCount) + ' row(s) affected)' RAISERROR( @Msg, 0, 1 ) WITH NOWAIT -- 2nd SQL Statement Update psl_table Set [Values]='a' -- Generate the message and print that can get from C# Set @Msg = '(' + Convert(NVarChar,@@RowCount) + ' row(s) affected)' RAISERROR( @Msg, 0, 1 ) WITH NOWAIT End 

In this SQL, I declared the @Msg variable to save the message and the built-in RAISERROR function to send the message.

C # CODE:

 public List<MessageClass> ChequesToUpdate() { message = new List<MessageClass>(); MessageClass item = new MessageClass(); try { using (connection = new SqlConnection(connectionString)) { connection.Open(); connection.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e) { item.message = e.Message; }; using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection)) { command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); } } } catch (Exception e) { item.message = e.Message; } finally { connection.Close(); } message.Add(item); return message; } 

I changed the code for the desired output. I used the connection.InfoMessage event to catch a message that throws from SQL.

For testing purposes, I print the output in the console.

Output:

Answer 3

Each end of an instruction in a stored procedure, Insert @@rowcount in a table. Then return it to your method using the dataset.

Answer 4

By default, you cannot receive messages affected by strings in C# . Instead, you need to change your procedure to PRINT/RAISERROR messages, so that you can access them in C # using SqlConnection.InfoMessage , as pointed out by @Julian.

fooobar.com/questions/1264907 / ...

Answer 5

I would return @@ rowcount from the procedure and in the .net code, I would read this value.

Add a return (@@ rowcount) to your procedure as shown below

 create procedure testprocedure as begin update test1 set id =100 return(@@rowcount) end 

And your .net code looks below with parameter direction

  SqlConnection conn; using(conn = new SqlConnection(@"Data Source=;Initial Catalog=;User ID = sa;Password=")) { conn.Open(); using (SqlCommand command = new SqlCommand("testprocedure", conn)) { command.CommandType = CommandType.StoredProcedure; SqlParameter retValue = command.Parameters.Add("return", SqlDbType.Int); retValue.Direction = ParameterDirection.ReturnValue; command.ExecuteNonQuery(); Console.WriteLine("no of records affected " + retValue.Value); Console.ReadLine(); } } 

Answer 6

You cannot capture these specific messages (i.e. (94 row(s) affected) ) because they are not sent by SQL Server; they are sent by the client program, either SQLCMD, or SSMS. However, yes, you can fix the number of rows and without the need to add RAISERROR or PRINT statements to your stored procedure. In addition, you (or many others reading this question) may not even be able to update the stored procedure (s) to introduce additional PRINT / RAISERROR / SELECT that display the number of rows.

But first, two warnings about some of the other suggestions made here:

• You may not be able to use the return value of ExecuteNonQuery , because it does not return values ​​for non-DML statements, including SELECT . It only returns values ​​from the INSERT , UPDATE and DELETE , as indicated by SqlCommand.ExecuteNonQuery :

  For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command .... For all other types of statements, the return value is -1.

  The question does not indicate what types of queries are being executed, so it is possible that you are using only these DML instructions. However, everyone should know that (and not) is reflected in this return value.

• You may not want to capture print messages using the InfoMessage handler, as this will require parsing to ensure that the message is not captured by any other message, such as a warning or other information.

In doing so, you want to configure the SqlCommand.StatementCompleted event handler, as it runs along the lines affected by each statement ( RecordCount is int ).

 internal static void StatementCompletedHandler(object Sender, StatementCompletedEventArgs EventInfo) { // do something with EventInfo.RecordCount return; } 

And then just attach it to the SqlCommand object:

 _Command.StatementCompleted += StatementCompletedHandler; 

If working in ASP.NET limits your ability to store the return value in a static variable (since it is shared and may not be thread safe), then you can define the inline handler as an anonymous delegate, in which case it will be able to access the instance variable. declared in your ChequesToUpdate method.