Displaying Row Count From Sql Server To Program

I am working on a program on C# VS 2015. In my program, there is a big text display box that generates message when I click a certain button to indicate an action is being performe

Solution 1:

If you have multiple queries in your script file, then you should enhance your script with @rowsAffected variable as shown in T-SQL below. Then, in your C# code you will need to call ExecuteScalar to get the detailed rows affected by your script.

**Script file with@rowsAffected variable logic**--add following variable at start of your scriptDECLARE@rowsAffectedVARCHAR(2000);

INSERTINTO [dbo].[Products] ([ProductName]) VALUES ('sun1'),('sun2'),('sun3');

--after each query that you want to track, include the following lineSET@rowsAffected='Products : '+CAST(@@rowcountASvarchar(20));

UPDATE [dbo].[newTable]   SET [ColB] ='b' ,[ColC] ='d',[ColD] ='e'  ,[ColE] ='f'WHERE ColA='a';

 --after each query that you want to track, include the following lineSET@rowsAffected=@rowsAffected+', newTable : '+CAST(@@rowcountASvarchar(20));

-- add the query below at end of your script SELECT@rowsAffected;

You will have to read the text from your script file, as you are doing in your code, and then create a command object using the text read from file before executing the code in snippet below.

C# code to execute above script

string rowsAffected =(string) command.ExecuteScalar();
//you can now use rowsAffected variable in any way you like//it will contain something like Table1 : 4, Table2 : 6

Detailed C# code using your original code

    using (SqlConnectioncon=newSqlConnection(constr))
    {

        FileInfofile=newFileInfo(DIRECTORY OF THE SCRIPT);
        stringscript= file.OpenText().ReadToEnd();

        SqlCommandcommand=newSqlCommand(script, con);
        command.CommandType = CommandType.Text;
        try
        {
            con.Open();
            stringrowsAffected=(string) command.ExecuteScalar();
            Display( rowsAffected);
            con.Close();
        }
        catch (Exception ex)
        {
            con.Close();
            Display(ex.Message);
        }
    }

Solution 2:

As Tetsuya Yamamoto said in his comment, what you are looking for is the use of the ExecuteScalar method. Your code change may look something like the below

int numOfRows = (int)server.ConnectionContext.ExecuteScalar(script);
     string displayText =  numOfRows.ToString();
     Display(displayText); -- to display message on text display
     con.Close();

The cast ToString is just for safety purposes as I am unsure how your Display will handle an int value