Displaying row count from SQL server to program

Question

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 performed.

Anyway, I have a SQL script where it has queries for COUNT for some tables. I can run that script through my program. However , since row count displays total number of rows for a table and can only be viewed inside SQL server. I was wondering is there a way to execute my script and also display the ROW COUNTS inside my program at the big text display box?

Here is a snippet of my code to run that SQL script:

    /*  open sql connection to execute SQL script: Row count script*/
    try
    {
        using (SqlConnection con = new SqlConnection(constr))
        {
            con.Open();
            FileInfo file = new FileInfo(DIRECTORY OF THE SCRIPT);
            string script = file.OpenText().ReadToEnd();
            Server server = new Server(new ServerConnection(con));
            server.ConnectionContext.ExecuteNonQuery(script);
            Display("ABCDG"); -- to display message on text display
            con.Close();



        }

    }
    catch (Exception ex)
    {

        textBox1.AppendText(string.Format("{0}", Environment.NewLine));
        textBox1.AppendText(string.Format("{0} MainPage_Load() exception - {1}{2}", _strThisAppName, ex.Message, Environment.NewLine));
        Display(ex.Message + ""); -- display message to textbox
        textBox1.AppendText(string.Format("{0}", Environment.NewLine));
        Debug.WriteLine(string.Format("{0} MainPage_Load() exception - {1}", _strThisAppName, ex.Message));


    }

Here is a snapshot of my program:

  https://i.sstatic.net/uKP99.png

the script just contains a list of row count. for example, select count() from table A , select count() from table B , etc etc — Cookie Monster
– Cookie Monster, Commented Nov 24, 2017 at 9:01
Just use ExecuteScalar: int numOfRows = (int)server.ConnectionContext.ExecuteScalar(script);. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Nov 24, 2017 at 9:03
Possible duplicate of How to count the number of rows from sql table in c#? — mjwills
– mjwills, Commented Nov 24, 2017 at 9:05

Sunil · Accepted Answer · 2017-11-27 11:17:59Z

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 script
DECLARE @rowsAffected VARCHAR(2000);

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

--after each query that you want to track, include the following line
SET @rowsAffected = 'Products : ' + CAST(@@rowcount AS varchar(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 line
SET @rowsAffected = @rowsAffected + ', newTable : ' + CAST(@@rowcount AS varchar(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 (SqlConnection con = new SqlConnection(constr))
    {

        FileInfo file = new FileInfo(DIRECTORY OF THE SCRIPT);
        string script = file.OpenText().ReadToEnd();

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

Comments are not for extended discussion; this conversation has been moved to chat.

bknight · Accepted Answer · 2017-11-24 12:05:17Z

0

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

edited Nov 24, 2017 at 12:05

answered Nov 24, 2017 at 10:00

bknight

217 bronze badges

2 Comments

Daniel Manta Over a year ago

Yes, but OP suggests he's running several count queries in that script, not just one select count.

Cookie Monster Over a year ago

I completely know this is one of the way it could be done but not in my situation. You can see the snapshot of my program. On the left side, is a big text box display. My goal is to create a button and when pressed, it will run this script , say script name is countrow.sql. Then, inside the script , it has many queries for counting records for different tables but that's not the issue. I just want the result to display in real time on the text box , just like how it display on the result screen in SQL server when you run a count query. ExecuteScarlar method will not do this.

Collectives™ on Stack Overflow

Displaying row count from SQL server to program

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related