0

I'm trying to convert legacy (VB.net) code that is using SqlConnection() to send parameters to a Stored Procedure. My main goal here is to rewrite this so that it utilizes OLEDB instead but I've come across multiple errors due to one of the parameters being a SqlDbType.Structured.

Is there a way to make SqlDbType.Structured pass through in a OLEDB connection?

Original Code:

mySQLConn = New SqlConnection()
mySQLConn.ConnectionString = strConn
mySQLConn.Open()

SQLcommand= New SqlCommand("stored_procedure", mySQLConn)
SQLcommand.CommandType = CommandType.StoredProcedure
SQLcommand.CommandTimeout = 0

mySqlTableTypeParam = SQLcommand.Parameters.Add("@InBoundData", SqlDbType.Structured) ' This line for reference
mySqlTableTypeParam.Value = AddUpdate

mySqlCleanupParam = SQLcommand.Parameters.Add("@sResponse", SqlDbType.VarChar)
mySqlCleanupParam.Value = strResponse

SQLcommand.ExecuteNonQuery()

My New OLEDB Code:

If oConnection Is Nothing Then oConnection = New Connection(Me.strConnection)
oConnection.Open()

cmd = New OleDbCommand
With cmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "stored_procedure"
    .Connection = oConnection.Connection
    .CommandTimeout = 0
            
    .Parameters.Add("@sResponse", OleDbType.VarChar, 25)
    .Parameters("@sResponse").Value = strResponse ' Expected to either be "N" or "Y"

    .Parameters.Add("@InBoundData", System.Data.SqlDbType.Structured)  ' I've run out of ideas
    .Parameters("@InBoundData").Value = AddUpdate
End With

cmd.ExecuteNonQuery()

Errors:

Run Results: System Error:System.InvalidOperationException: Provider encountered an error while sending command parameter[0] '' value and stopped processing. Command parameter[1] '' data value could not be converted for reasons other than sign mismatch or data overflow. ---> System.Data.OleDb.OleDbException: Invalid character value for cast specification --- End of inner exception stack trace ---

John Ramirez
  • 1
  • The original code very unhelpfully uses `mySql` prefix, when MySql does not support table-valued parameters. The name of the user-defined type must be specified, which the original code does not do. – GSerg Jan 11 '22 at 18:55
  • https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters. As GSerg points out I think the property typename is missing , eg .TypeName = "dbo.CategoryTableType" There's a "VB" button at the top of the page – Ole EH Dufour Jan 11 '22 at 18:55
  • @OleEHDufour That is using `SqlCommand`. The OP want to use `OleDbCommand`. – GSerg Jan 11 '22 at 18:59
  • `SqlConnection` works only with SQL Server, so it directly supports an SQL Server specific feature such as table-valued parameters. `OleDbConnection` is supposed to work with various database providers, so it doesn't. – GSerg Jan 11 '22 at 19:04
  • Does this answer your question? [Can I pass table-valued parameters agnostically?](https://stackoverflow.com/questions/66894117/can-i-pass-table-valued-parameters-agnostically) – GSerg Jan 11 '22 at 19:04
  • A better update would be to use Using for the connection, as shown in [SqlConnection Class](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection). In general, do not re-use database connections in .NET: they are intended to be used with connection pooling. – Andrew Morton Jan 11 '22 at 19:11
  • @GSerg Your comment was the best out of all which was still on topic. Unfortunately, the page you've referenced does not use OLEDB and it looks like there may not be a solution. I wonder what kind of workaround there is out there so I can remain using OLEDB. – John Ramirez Jan 11 '22 at 19:17
  • @JohnRamirez If you don't want database-specific optimisations then perhaps you are looking for an ORM: [What is the difference between an orm and ADO.net?](https://stackoverflow.com/questions/40506382/what-is-the-difference-between-an-orm-and-ado-net) – Andrew Morton Jan 11 '22 at 19:30
  • The reason why I'm using OLEDB is because I'm utilizing legacy code on SQL Server 2016 which uses the `datatypecompatability=80` keyword/parameter in the connection string. To the best of my knowledge, I don't think I can achieve that with the alternative options. – John Ramirez Jan 11 '22 at 19:39
  • 1
    What does this parameter do, and why do you think you need it with the native client? – GSerg Jan 11 '22 at 21:23
  • 1
    That parameter allows the code to map to some of the "newer" features of sql server 2005 but I recommend you stop using OleDb and use the SqlClient types instead. This would support everything natively in Sql Server. If it is a question of code migration, which can be complex if you have a lot of code that is OleDb dependent, then do a phased migration where you write new code using SqlClient and phase over existing code as you need to or when you have to "touch" that code. – Igor Jan 12 '22 at 14:08
  • 1
    Again, I see no benefit of using Ole unless you have different providers (like Oracle, MySql, etc) that you need to support using the same ado.net types. Even then though you could use abstraction and use provider specific code as these types all implement interfaces. – Igor Jan 12 '22 at 14:09

0 Answers0