C# / Execute Parameterized SQL StoredProcedure via ODBC

Question

Hello,

from within a C# WinForms-App I must execute a parameterized Stored Procedure on a MS SQL Express Server. The Database Connection works, the Procedure works either, but I get an Error Message:

42000: Missing Parameter '@KundenEmail'

although I'm sure I added the parameter correctly. Maybe some of you could have a look - I don't know what to search for any more...

OdbcConnection ODBCConnection = new OdbcConnection();

try
{
    ODBCConnection.ConnectionString = ODBCConnectionString;
    ODBCConnection.Open();
}
catch (Exception DatabaseConnectionEx)
{
    if (ODBCConnection != null)
        ODBCConnection.Dispose();

    // Error Message

    return null;
}

OdbcParameter ODBCParameter = new OdbcParameter("@KundenEmail", OdbcType.NChar, 50);
ODBCParameter.Value = KundenEmail;

OdbcCommand ODBCCommand = new OdbcCommand("getDetailsFromEmail", ODBCConnection);
ODBCCommand.CommandType = CommandType.StoredProcedure;
ODBCCommand.Parameters.Add(ODBCParameter);

DataTable DataTable = new DataTable();

OdbcDataAdapter ODBCDatadapter = new OdbcDataAdapter(ODBCCommand);
ODBCDatadapter.Fill(DataTable);
ODBCDatadapter.Dispose();

ODBCConnection.Close();
ODBCConnection.Dispose();

This is the error message I get:

ERROR [4200][Microsoft][ODBC SQL Server]The Procedure or method 'getDetailsFromEmail' expects the '@KundenEmail'-parameter, which was not supplied.

Ah, I missed the connection string

private static String ODBCConnectionString = "Driver={SQL Server};Server=TESTSRV\\SQLEXPRESS;Database=TestDatabase;";

Any ideas? Thanks in advance, Daniel

Answer 1

Don't use ODBCConnection to connect to a SQL Server. Use the "normal" SqlConnection, SqlCommand, etc. These are the ones made to work with SQL Server.

EDIT: Also, you should use the SqlConnectionStringBuilder to assembly the connection string. This is far less error prone than putting the entire connection string into a configuration file or creating it by hand.

Answer 2

Well - I now managed to solve the problem on my own, with some help from the MSDN-documentation.

The correct statement to execute a stored procedure via ODBC is as follows:

OdbcCommand ODBCCommand = new OdbcCommand("{call getDetailsFromEmail (?)}", ODBCConnection);
ODBCCommand.CommandType = CommandType.StoredProcedure;
ODBCCommand.Parameters.AddWithValue("@KundenEmail", KundenEmail);

Nevertheless - thanks for your help Thorsten.

Answer 3

Anyway it's better your code would look like this:

using (OdbcConnection connection = new OdbcConnection(connectionString))
using (OdbcCommand command = connection.CreateCommand())
{
    command.CommandText = commandText;
    command.CommandType = CommandType.StoredProcedure;

    command.Paremeters.Add("@KundenEmail", OdbcType.NChar, 50).Value = KundenEmail

    DataTable dataTable = new DataTable();

    connection.Open();    
    using (OdbcDataAdapter adapter = new OdbcDataAdapter(command))
    {
        adapter.Fill(dataTable);
    }
}

But rather better to use SqlConnection/SqlCommand/SqlDataAdapter instead of ODBC types. Syntax still the same