显示从SQL Server到程序的行数 [英] Displaying row count from SQL server to program

问题描述

我正在C＃VS 2015上的一个程序上工作。在我的程序中，有一个大文本显示框，当我单击某个按钮以指示正在执行某项操作时，该消息会生成消息。

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.

无论如何，我有一个SQL脚本，其中有一些表对 COUNT 的查询。我可以通过程序运行该脚本。但是，由于行数显示的是表的总行数，因此只能在SQL Server内部查看。我想知道是否有一种方法可以执行我的脚本，并且还可以在程序的大文本显示框中显示行数？

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?

这是我的代码片段运行该SQL脚本：

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));


    }

这是我程序的快照：

  https://i.stack.imgur.com/uKP99.png

推荐答案

如果脚本文件中有多个查询，则应使用 @rowsAffected 变量，如下面的T-SQL所示。然后，在C＃代码中，您将需要调用 ExecuteScalar 来获取受脚本影响的详细行。

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＃代码以在脚本上方执行

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

使用您的原始代码的详细C＃代码

    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);
        }
    }

这篇关于显示从SQL Server到程序的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！