Visual Basic to C# Script

[versacestl]

Can anyone help me convert the following VB script to C#

Public Sub Main()
    Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=Test")
    
     AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
     
     conn.Open()
     
     Dim cmd As New SqlCommand()
     cmd.Connection = conn
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "SPWithPrint"
     cmd.ExecuteNonQuery()
     conn.Close()
     Dts.TaskResult.Results.Success
     
End Sub

Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)
    Dim sqlEvent As System.Data.SqlClient.SqlError
     For Each sqlEvent In args.Errors
            Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False)
        Next
End Sub

[versacestl]

This is what i have so far

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_0cfea7721c684e35b7b1a384d3ade7da.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {

            string DBConn = System.Configuration.ConfigurationSettings.AppSettings["DBConn"];
            SqlConnection cn = new SqlConnection(DBConn);
            cn.Open();

            string strSQL = "Schema.StoredProcedure";

            SqlCommand cmd = new SqlCommand(strSQL, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;

            cn.Close();

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        private void OnInfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
        {
            
             foreach(sqlEvent s in e.Errors)
            {
                
            }

        }
    }
}

[dlarkin77]

how about

public void Main()
{
    SqlConnection conn = new SqlConnection("server=(local);Integrated Security=SSPI;database=Test");
    conn.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
    conn.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "SPWithPrint";
    cmd.ExecuteNonQuery();
    conn.Close();
    Dts.TaskResult.Results.Success;
}

private void OnInfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs args)
{
    foreach (System.Data.SqlClient.SqlError sqlEvent in args.Errors)
    {
        Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, false);
    }
}

[Chris_F]

see if this works:

public void Main()
{
    SqlConnection conn = new SqlConnection("server=(local);Integrated Security=SSPI;database=Test");
    
    conn.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
    
    conn.Open();
    
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "SPWithPrint";
    cmd.ExecuteNonQuery();
    conn.Close();
        
    Dts.TaskResult.Results.Success();
}

private void OnInfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs args)
{
    System.Data.SqlClient.SqlError sqlEvent = default(System.Data.SqlClient.SqlError);
    foreach (var sqlEvent in args.Errors) {
        Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, false);
    }
}

if it does, just use http://www.developerfusion.com/tools.../vb-to-csharp/ in the future because that only took 3 seconds.

[David Anton]

Fast is good, but sometimes it has to be fast + correct.
This is what you want for the second method:

private void OnInfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs args)
{
//INSTANT C# NOTE: Commented this declaration since looping variables in 'foreach' loops are declared in the 'foreach' header in C#:
//	System.Data.SqlClient.SqlError sqlEvent = null;
	foreach (System.Data.SqlClient.SqlError sqlEvent in args.Errors)
	{
		Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, false);
	}
}

[Arjay]

You may want to use a 'using' block to help ensure that resources are freed up properly.

 
public void Main()
{
  try
  {
    using( SqlConnection conn = new SqlConnection("server=(local);Integrated Security=SSPI;database=Test") )
    {
      conn.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
    
      conn.Open();
    
      using( SqlCommand cmd = new SqlCommand( conn ) )
      {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SPWithPrint";
        cmd.ExecuteNonQuery();
            
        Dts.TaskResult.Results.Success();
      }
    }
  }
  catch( Exception ex )
  {
    // Report error
  }
}