How to read data from SAP database via SQL in C#

Question

I have the task to refurbish a very old VBA macro into a .NET program. This macro is collecting data from our SAP database. Unfortunately I don't know anything about SAP and how to deal with it from my C# program. For this program I'm using .NET 4.7.2. This is the old code:

Option Private Module
Global db_005SAPTABLES As DAO.Database

Public Sub ECadSQL_Abfrage(ByVal lngZeile As Long)

Dim rsETV As DAO.Recordset
Dim MatNr As String
Dim strSQL As String

'MatNr einlesen
MatNr = Range("E" & lngZeile).Value

If MatNr = "" Then
    Range("F" & lngZeile).Value = ""
    Range("G" & lngZeile).Value = ""
    Range("H" & lngZeile).Value = ""
    Range("I" & lngZeile).Value = ""
    Range("J" & lngZeile).Value = ""
    Range("K" & lngZeile).Value = ""
GoTo weiter1
End If

'SQL abfrage
strSQL = "select * FROM T_ECAD_SAP_Daten where ArticleNumber = '" & MatNr & "'"

'Verbinden mit Datenbank
Connect_005SAPTABLES

Set rsETV = db_005SAPTABLES.OpenRecordset(strSQL)

'Daten auslesen
    Range("F" & lngZeile).Value = rsETV.Fields("PP_Status").Value
    Range("G" & lngZeile).Value = rsETV.Fields("Class").Value
    Range("H" & lngZeile).Value = rsETV.Fields("Gerätetyp").Value
    Range("I" & lngZeile).Value = rsETV.Fields("Kenngroesse_1").Value
    Range("J" & lngZeile).Value = rsETV.Fields("Kenngroesse_2").Value
    Range("K" & lngZeile).Value = rsETV.Fields("Kenngroesse_3").Value

rsETV.Close

weiter1:        
End Sub




Sub Connect_005SAPTABLES()

Dim Fehlermeldung As String
Dim constr As String

On Error GoTo Connect_005SAPTABLESErrorHandler

'Verbindungsangaben zur Datenbank
constr = "DRIVER={SQL Server};SERVER=*******.patec.group;"
constr = constr & "DATABASE=005SAPTables;UID=005SAP*****;PWD=******;APP=F-Kalkulation"

'Datenbank öffnen
Set db_005SAPTABLES = OpenDatabase("005SAPTABLES", dbDriverNoPrompt, True, constr)

Exit Sub

'ErrorHandler
Connect_005SAPTABLESErrorHandler:

Fehlermeldung = "Unbehandelter Fehler in Prozedur Connect_005SAPTABLES aufgetreten " & Chr(13) & "FehlerNr. " & Err.Number & " " & Err.Description
MsgBox Fehlermeldung, vbExclamation, "Fehlermeldung"

End Sub

Maybe there is the possibility to translate this code into C# based on actual technology. The most easiest way to get (readonly) data from that DB, will be great for me. Which library do I have to implement into my program? Or what is the easiest and latest technology nowadays?

Thank you in advance.

EDIT: ===============================================

The command.ExecuteReader() fires an exception: Invalid object name 'T_ECAD_SAP_Daten' --- But Server Explorer shows me exactly that table name.

Solution: I don't know why but VBA SQL syntax is much different to C# one

// from VBA
SELECT * FROM T_ECAD_SAP_Daten WHERE ArticleNumber = 'E54990198'
// at C#
SELECT * FROM [005SAPTables_DBO].[T_ECAD_SAP_Daten] WHERE ArticleNumber = '54990198'

I would start with establishing the connection to the database. You have the connection string with server name, database name and credentials, which is half of the success. Try to connect to the database from Visual Studio in View > Server Explorer. Your sql query is pretty straight forward, one parameter, so you can get the inspiration e.g. here: https://stackoverflow.com/a/25739883/4429029. — Tomas Paul, Apr 16 '20 at 07:20
Does this answer your question? [Step by step tutorial to use SAP. net Connector with VS 2008](https://stackoverflow.com/questions/5300049/step-by-step-tutorial-to-use-sap-net-connector-with-vs-2008) — Suncatcher, Apr 16 '20 at 08:32
Within a few tries I got a connection with Server Explorer. Thank you very much Tomas. Now I'm also able to establish a connection programmatically. But I get an exception at this line: using (SqlDataReader reader = command.ExecuteReader()) Exception: Invalid object name 'T_ECAD_SAP_Daten' But Server Explorer shows me exactly that table name. Watch my EDIT above — prototype0815, Apr 17 '20 at 04:57

score 0 · Answer 1 · answered Apr 17 '20 at 08:57

I don't know whether this is a real SAP database, or SAP is using a common MSSQL/mySQL database.

My solution:

private static SqlConnection conn;

    public static DataTable ECadSQL_Abfrage(double number)
    {
        Connect();

        SqlCommand command = conn.CreateCommand();
        command.CommandText = "SELECT * FROM [005SAPTables_DBO].[T_ECAD_SAP_Daten] WHERE ArticleNumber = '" + number + "'";

        DataSet ds = new DataSet();
        var da = new SqlDataAdapter(command);
        //  .Fill opens and closes the connection by itself, therefore the connect() and close() functions are unnecessary
        da.Fill(ds);
        DataTable dt = ds.Tables[0];
        // string st1 = dt.Rows[0]["PP_Status"].ToString();
        // string st2 = dt.Rows[0]["Class"].ToString();

        Close();

        return dt;
    }


    public static void Connect()
    {
        string server = "******.patec.group";
        string database = "005SAPTABLES";
        string username = "********";
        string password = "*****";

        conn = new SqlConnection("Data Source=" + server + ";Initial Catalog=" + database + ";User Id=" + username + ";Password=" + password + ";");
        //conn.Open();
    }


    public static void Close()
    {
        //conn.Close();
        conn.Dispose();
    }

How to read data from SAP database via SQL in C#

1 Answers1