Converting SQL command from vb .net to C#

Question

I am trying to analyze SQL command text in vb.net and integrate it to be compatible with C#.

I am having issues converting SQL command text from vb.net to c#.

I used Telerik: http://converter.telerik.com/

and got these results (shown below). I was wondering if someone who is familiar with both VB.NET and C# could help me re-write the full SQL command text.

Original vb.net command text:

Dim r As SqlDataReader = sqlCommand.ExecuteReader()

Do While True
    If r.Read() Then
         SQL2 = "SELECT * from Invoices "
         SQL2 = SQL2 & " WHERE Num = '" & r("Num") & "'"

         conPubs2 = New SqlConnection(<ConnectionString>)
         sqlCommand2 = New SqlCommand(SQL2, conPubs2)
         conPubs2.Open()

         Dim r2 As SqlDataReader = sqlCommand2.ExecuteReader()

         Do While True
             If r2.Read() Then
                 MsgBox(RTrim(r("Num")) & ": " & RTrim(r2("ItemID")))
             Else
                 Exit Do
             End If
         Loop

    Else
        Exit Do
    End If
Loop

conPubs.Close()
conPubs2.Close()

End Sub

Here's what I got in C# when I used Telerik's online converter:

SqlDataReader r = sqlCommand.ExecuteReader();

while (true)
{
    if (r.Read())
    {
        SQL2 = "SELECT * from Invoices ";
        SQL2 = SQL2 + " WHERE Num = '" + r("Num") + "'";

/* Cannot convert AssignmentStatementSyntax, CONVERSION ERROR: Conversion for XmlElement not implemented, please report this issue in ')
sql...' at character 314

at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.NodesVisitor.DefaultVisit(SyntaxNode node)
at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.VisitXmlElement(XmlElementSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.XmlElementSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor)
at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.Visit(SyntaxNode node) at ICSharpCode.CodeConverter.CSharp.CommentConvertingNodesVisitor.DefaultVisit(SyntaxNode node) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.VisitXmlElement(XmlElementSyntax node)
at Microsoft.CodeAnalysis.VisualBasic.Syntax.XmlElementSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.NodesVisitor.VisitSimpleArgument(SimpleArgumentSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.SimpleArgumentSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor)
at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.Visit(SyntaxNode node) at ICSharpCode.CodeConverter.CSharp.CommentConvertingNodesVisitor.DefaultVisit(SyntaxNode node) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.VisitSimpleArgument(SimpleArgumentSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.SimpleArgumentSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.NodesVisitor.<>c__DisplayClass83_0.<ConvertArguments>b__0(ArgumentSyntax a, Int32 i) at System.Linq.Enumerable.<SelectIterator>d__52.MoveNext() at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() at Microsoft.CodeAnalysis.CSharp.SyntaxFactory.SeparatedList[TNode](IEnumerable1 nodes) at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.NodesVisitor.VisitArgumentList(ArgumentListSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.ArgumentListSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.Visit(SyntaxNode node) at ICSharpCode.CodeConverter.CSharp.CommentConvertingNodesVisitor.DefaultVisit(SyntaxNode node) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.VisitArgumentList(ArgumentListSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.ArgumentListSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.NodesVisitor.VisitObjectCreationExpression(ObjectCreationExpressionSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.ObjectCreationExpressionSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.Visit(SyntaxNode node) at ICSharpCode.CodeConverter.CSharp.CommentConvertingNodesVisitor.DefaultVisit(SyntaxNode node) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.VisitObjectCreationExpression(ObjectCreationExpressionSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.ObjectCreationExpressionSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at ICSharpCode.CodeConverter.CSharp.VisualBasicConverter.MethodBodyVisitor.VisitAssignmentStatement(AssignmentStatementSyntax node) at Microsoft.CodeAnalysis.VisualBasic.Syntax.AssignmentStatementSyntax.Accept[TResult](VisualBasicSyntaxVisitor1 visitor) at Microsoft.CodeAnalysis.VisualBasic.VisualBasicSyntaxVisitor1.Visit(SyntaxNode node) at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.ConvertWithTrivia(SyntaxNode node) at ICSharpCode.CodeConverter.CSharp.CommentConvertingMethodBodyVisitor.DefaultVisit(SyntaxNode node)

I am trying ultimately, to expand on a different query that has the following command text; the reason why is I need to expand upon it is to be able to count the total number of lines associated with the Num (lines returned, e.g. if more than 1):

dbConnection.Open();
var sqlCmd = dbConnection.CreateCommand();
sqlCmd.CommandText = @"SELECT [ItemID], [Num]
                       FROM [Test].[dbo].[Invoices]
                       WHERE Num = '" + orderNumber.ToString() + "'";

New SqlConnection(<ConnectionString>) isn't even valid syntax for VB.NET, so fix that (passing "" should be sufficient for this purpose) and retry the conversion. However, this is very bad VB.NET code to begin with, so you're going to get equally bad C# code out of a conversion. You'd be much better off figuring out the end goal of that code and rewriting the C# code from scratch. — madreflection
– madreflection, Commented May 29, 2020 at 16:57
Agreed. It looks like <ConnectionString> is a placeholder which is replaced, in the code, as some sort of precompile step. — Steven Doggart
– Steven Doggart, Commented May 29, 2020 at 16:59
gosh! I need to think about what you are telling me. I will think on this and get back soon. thank you. — Michele
– Michele, Commented May 29, 2020 at 17:04
Please post the entire method. Then @ me to let me know it's all there. I'm confident I can convert this, but it would be malpractice to do it without fixing the crazy sql injection security issue at the same time, and that's better done if I can see the surrounding code. There's a good chance I can improve performance by an order of magnitude, also. — Joel Coehoorn
– Joel Coehoorn, Commented May 30, 2020 at 1:12
Please don't convert this code to anything! This is a long time to hold a connection open. For heavens sake, the code even shows a message box while the connection is open. What if the user goes to lunch. Just start over. — Mary
– Mary, Commented May 30, 2020 at 1:48

Evry1falls · Accepted Answer · 2020-05-29 18:29:39Z

OK as for the first part concerning conversion, first of all, you did not copy the whole code of VB.Net, it was missing many parts IN ORDER TO be correctly converted to C# code, I did some implementations and converted it in the same website you used. The VB.Net [As it should be], it doesn't mean that is written in a correct way or won't generate errors.

Sub Hello()
        Dim conPubs As SqlClient.SqlConnection = New SqlClient.SqlConnection With {.ConnectionString = "Your_ConnectionString"}
        conPubs.Open()
        Dim ThisCommand1 As New SqlClient.SqlCommand 'This line was missing from the example you copied this from.
        Dim r As SqlClient.SqlDataReader = ThisCommand1.ExecuteReader
        Do While True
            If r.Read() Then
                Dim SQL2 As String = "SELECT * from Invoices WHERE Num = '" & r("Num") & "'"
                Dim conPubs2 = New SqlClient.SqlConnection("")
                Dim sqlCommand2 = New SqlClient.SqlCommand(SQL2, conPubs2)
                conPubs2.Open()
                Dim r2 As SqlClient.SqlDataReader = sqlCommand2.ExecuteReader()
                Do While True
                    If r2.Read() Then
                        MsgBox(RTrim(r("Num")) & ": " & RTrim(r2("ItemID")))
                    Else
                        Exit Do
                    End If
                Loop
                conPubs2.Close()       'Is a second SqlServer Connection
            Else
                Exit Do
            End If
        Loop
        conPubs.Close()      'Is SqlServer Connection
    End Sub

The Conversion to C# :

public void Hello()
{
    SqlClient.SqlConnection conPubs = new SqlClient.SqlConnection() { ConnectionString = "Your_ConnectionString" };
    conPubs.Open();
    SqlClient.SqlCommand ThisCommand1 = new SqlClient.SqlCommand(); // This line was missing from the example you copied this from.
    SqlClient.SqlDataReader r = ThisCommand1.ExecuteReader;
    while (true)
    {
        if (r.Read())
        {
            string SQL2 = "SELECT * from Invoices WHERE Num = '" + r("Num") + "'";
            var conPubs2 = new SqlClient.SqlConnection("");
            var sqlCommand2 = new SqlClient.SqlCommand(SQL2, conPubs2);
            conPubs2.Open();
            SqlClient.SqlDataReader r2 = sqlCommand2.ExecuteReader();
            while (true)
            {
                if (r2.Read())
                    MsgBox(RTrim(r("Num")) + ": " + RTrim(r2("ItemID")));
                else
                    break;
            }
            conPubs2.Close();       // Is a second SqlServer Connection
        }
        else
            break;
    }
    conPubs.Close();      // Is SqlServer Connection
}

Again, note :

This answers to your conversion issues, but doesn't mean that VB .Net code is not going to generate Errors.

In VB.Net :

VB.Net code needs to be re-written.
Consider using [Using ... End Using] method with Database Connection.

Collectives™ on Stack Overflow

Converting SQL command from vb .net to C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related