none
Pass In Structured Parameter to Typed DataSet

    Question

  • Is there a way to use a SQL Server user defined table type with a typed dataset in VB.NET?

    I want to configure my TableAdapter with the following SQL.  Where @ProductLabor is a user defined table type in SQL Server.  Notice, I also want to do a calculation and call it TotalDeptHours.

    SELECT L.Department, L.HourlyRate, L.MarkUp, L.LaborDescription, 
    	PL.DeptHours * L.HourlyRate * L.MarkUp AS TotalDeptHours
    FROM dbo.Labor AS L
    	INNER JOIN @ProductLabor as PL
    		ON L.Department = PL.Department

    I want to pass in the table type like this for example,

            Dim dt As New DataTable
            Dim ta As New dsPartsLaborTableAdapters.LaborTableAdapter
            Dim ds As New dsPartsLabor
    
            With Parts
                .Columns.Add("Department", GetType(String))
                .Columns.Add("DeptHours", GetType(Integer))
            End With
    
            Dim prm As SqlClient.SqlParameter = New SqlClient.SqlParameter("@ProductLabor", dt)
            With prm
                .SqlDbType = SqlDbType.Structured
                .TypeName = "dbo.udtt_Labor"
            End With
            ta.Fill(ds.Labor, prm)



    Ryan


    • Edited by Ryan0827 Sunday, September 15, 2013 2:47 AM add calculation TotalDeptHours note
    Sunday, September 15, 2013 2:47 AM

Answers

  • The sql parameter token something like @xxx can be only applied after equality expression, something like:

    Select * From xxx where ……=@xxx

    So for your situation, you have to use string combination, which won't make the problem of SQL injection for this situation.


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    • Marked as answer by Ryan0827 Monday, September 16, 2013 1:13 PM
    Sunday, September 15, 2013 4:56 AM

All replies

  • The sql parameter token something like @xxx can be only applied after equality expression, something like:

    Select * From xxx where ……=@xxx

    So for your situation, you have to use string combination, which won't make the problem of SQL injection for this situation.


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    • Marked as answer by Ryan0827 Monday, September 16, 2013 1:13 PM
    Sunday, September 15, 2013 4:56 AM
  • You can use a string format statement like below

            Dim tablename As String = "Table1"
            Dim SQL As String = String.Format( _
                "SELECT L.Department, L.HourlyRate, L.MarkUp, L.LaborDescription," & _
                " PL.DeptHours * L.HourlyRate * L.MarkUp AS TotalDeptHours" & _
                " FROM dbo.Labor AS L" & _
                " INNER JOIN {0} as PL" & _
                " ON L.Department = PL.Department", _
                tablename)


    jdweng

    Sunday, September 15, 2013 8:36 AM