locked
ExecuteNonQuery Results

    Question

  • I have some code in C# to run a sql script. When I run this sql script in SQL server manager I get some feed back in the results window, I would like to get the same results back when I run this in my C# windows app displaying the results to a text box on my form.

    The SQL script is basicicly a restore DB and some other small db actions. They do not return a true dataset but status messages. It's these status messages I would like to place in my application text box.
    try
    
                    {
    
                        const string sqlConnectionString = "Data Source=localhost;Initial Catalog=[DatabaseName];Integrated Security=True";
    
                        var file = new FileInfo(Application.StartupPath + "\\MSDE2005Restore.sql");
    
                        var script = file.OpenText().ReadToEnd();
    
                        //textBox1.Invoke(new Updatetextbox(this.Updatetext), new object[] {script.ToString() + "\r\n" + sqlConnectionString.ToString() + "\r\n"});
    
                        var conn = new SqlConnection(sqlConnectionString);
    
                        var server = new Server(new ServerConnection(conn));
    
                        server.ConnectionContext.ExecuteNonQuery(script);
    
                        textBox1.Invoke(new Updatetextbox(Updatetext), new object[] {DateTime.Now+ " - DB Restore Completed \r\n"});
    
                    }
    
                    catch(Exception e)
    
                    {
    
                        textBox1.Invoke(new Updatetextbox(Updatetext), new object[] { DateTime.Now+ " - "+e.Message + "\r\n"});
    
                    }
    
    
    Friday, April 17, 2009 6:27 PM

Answers

  • SqlConnection class has an InfoMessage event.

    Handle that event.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.infomessage(VS.71).aspx
    Thanks, A.m.a.L | [Remember to click "mark as answered" when you get a correct reply to your question]
    • Marked as answer by Cyrun Friday, April 17, 2009 7:53 PM
    Friday, April 17, 2009 6:44 PM
  • class  InfoMessage
    {
       public static  void  InfoMessageHandler ( object mySender, SqlInfoMessageEventArgs myEvent )
       {
         Console.WriteLine ( "The following message was produced:\n"  + myEvent.Errors [ 0 ]) ;
       }

       public static  void  Main (){
         SqlConnection mySqlConnection =  new  SqlConnection ( "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;" ) ;

         mySqlConnection.InfoMessage +=  new  SqlInfoMessageEventHandler ( InfoMessageHandler ) ;

         mySqlConnection.Open () ;

         SqlCommand mySqlCommand = mySqlConnection.CreateCommand () ;

         mySqlCommand.CommandText =  "PRINT 'This is the message from the PRINT statement'" ;
      
         mySqlCommand.ExecuteNonQuery () ;

         mySqlCommand.CommandText =  "RAISERROR('This is the message from the RAISERROR statement', 10, 1)" ;
      
         mySqlCommand.ExecuteNonQuery () ;

         mySqlConnection.Close () ;
       }
    }


    Thanks, A.m.a.L | [Remember to click "mark as answered" when you get a correct reply to your question]
    • Marked as answer by Cyrun Friday, April 17, 2009 7:49 PM
    Friday, April 17, 2009 7:33 PM

All replies

  • SqlConnection class has an InfoMessage event.

    Handle that event.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.infomessage(VS.71).aspx
    Thanks, A.m.a.L | [Remember to click "mark as answered" when you get a correct reply to your question]
    • Marked as answer by Cyrun Friday, April 17, 2009 7:53 PM
    Friday, April 17, 2009 6:44 PM
  • I hate to sound stupid here but C# is still new to me, can you help me by pointing me in a direction of a better example. I understand the concept so far of the link you sent me too but I need an example code. I will keep looking but thank you for your help so far.
    Friday, April 17, 2009 6:56 PM
  • class  InfoMessage
    {
       public static  void  InfoMessageHandler ( object mySender, SqlInfoMessageEventArgs myEvent )
       {
         Console.WriteLine ( "The following message was produced:\n"  + myEvent.Errors [ 0 ]) ;
       }

       public static  void  Main (){
         SqlConnection mySqlConnection =  new  SqlConnection ( "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;" ) ;

         mySqlConnection.InfoMessage +=  new  SqlInfoMessageEventHandler ( InfoMessageHandler ) ;

         mySqlConnection.Open () ;

         SqlCommand mySqlCommand = mySqlConnection.CreateCommand () ;

         mySqlCommand.CommandText =  "PRINT 'This is the message from the PRINT statement'" ;
      
         mySqlCommand.ExecuteNonQuery () ;

         mySqlCommand.CommandText =  "RAISERROR('This is the message from the RAISERROR statement', 10, 1)" ;
      
         mySqlCommand.ExecuteNonQuery () ;

         mySqlConnection.Close () ;
       }
    }


    Thanks, A.m.a.L | [Remember to click "mark as answered" when you get a correct reply to your question]
    • Marked as answer by Cyrun Friday, April 17, 2009 7:49 PM
    Friday, April 17, 2009 7:33 PM
  • WiNd0wS-7,
    You are awsome that helped me so much and I got what I needed. Of course did not use your code exactly but damn if it was not easy enough for me to figure ut and impletment what I needed :-) thank you.
    Friday, April 17, 2009 7:52 PM