0

I have a simple program written in c#. It's main purpose is for data archiving. Below is a snapshot of how the program looks like.

enter image description here

As shown, whenever a button is clicked, message will be displayed on the big text box on the left side of the program. Right now, I am implementing another button called Row Count. The purpose of this function is when I click that button, it will execute one of my SQL script and will display the number of counts in a table to the text box.

Inside that SQL script contains row count queries for multiple tables, not just one row count query. Here is a snippet of it :

SELECT COUNT(*) AS [PromotionEventRows] FROM PromotionEvent (NOLOCK)
select top 1 EventCode as [PE - PE Code], LastChangeDate FROM PromotionEvent (NOLOCK) ORDER BY LastChangeDate DESC

SELECT COUNT(*) AS [PromotionBenefitRows] FROM PromotionBenefit (NOLOCK)
select top 1 PromotionBenefitCode as [PB - PB Code], LastChangeDate FROM PromotionBenefit (NOLOCK) ORDER BY LastChangeDate DESC

SELECT COUNT(*) AS [CardTypeBenefitRows] FROM CardTypeBenefit (NOLOCK)
select top 1 CardTypeBenefitID, LastChangeDate FROM CardTypeBenefit (NOLOCK) ORDER BY LastChangeDate DESC

I know there is a method ExecuteScalar to accomplish this but the issue here is I don't want to write SQl query inside my c# code. Rather I want my program to read that script and display the count in the text display in the program. Think of it like getting the results in SQL server but instead the results are displayed inside my program text box. Any suggestion would be helpful.

Here is also part of my C# code:

   try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    con.Open();
                    FileInfo file = new FileInfo("Directory of that SQL script");
                    string script = file.OpenText().ReadToEnd();
                    Server server = new Server(new ServerConnection(con));
                    server.ConnectionContext.ExecuteNonQuery(script);
                    Display("Whatever message display on textbox");
                    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 + "message");
                textBox1.AppendText(string.Format("{0}", Environment.NewLine));
                Debug.WriteLine(string.Format("{0} MainPage_Load() exception - {1}", _strThisAppName, ex.Message));


            }
Erik Philips
  • 53,428
  • 11
  • 128
  • 150
Cookie Monster
  • 636
  • 1
  • 12
  • 29
  • So Whats the issue with your code? – CodingYoshi Nov 27 '17 at 05:52
  • No issue, I just can't figure how to implement the way I want it. – Cookie Monster Nov 27 '17 at 05:55
  • Were you able to get result from executing the script? If yes, were you successfully iterating to get the multiple results? -- logically your concept in getting the solution is OK, however the specific details that you are struggling is unclear. – hsoesanto Nov 27 '17 at 05:59
  • Definitely I am can execute the script successfully. For example in my snapshot, when I clicked create tables, it will create a list of tables in my SQL server and alongside the text display, it will display messages like Table A has been created,Table B has been created,..... As I tell my program to display these message on the textbox. Only issue now is executing this script to return row count number in real time on my text box. – Cookie Monster Nov 27 '17 at 06:01
  • If i just run the script, I won't see any message display on my textbox and my goal is to have them display on the textbox as it runs. Am I clear enough? – Cookie Monster Nov 27 '17 at 06:02

1 Answers1

0

Write a Stored Procedure in SQL and return the count to the front end. SP can be called from the below code where Execute_StoredPro is my SP name and runTests is my connection

        SqlCommand spcmd = new SqlCommand("Execute_StoredPro", runTests);
        spcmd.CommandTimeout = 0;
        spcmd.CommandType = CommandType.StoredProcedure;
        spcmd.ExecuteNonQuery();
Chendur
  • 1,099
  • 1
  • 11
  • 23