none
DataContext.ExecuteQuery RRS feed

  • Question

  • So this function really executes any Transact-SQL query or stored procedure i like? could i execute queries that make use of full-text search capabilities of sql server like CONTAINS command and return the result set to linq mapped objects? thanks
    Saturday, April 26, 2008 2:29 AM

Answers

  • I don't know if there's a better way (someone yell if there is), but I've been doing adhoc queries like this..

     

    ' declare result set type

    Public Class PrimaryKeys
        Public table_name As String
        Public position As Integer
        Public column_name As String
    End Class

     

    ' query the info schema..

    strSQL = "SELECT Upper(C.table_name) AS 'table_name', " _
           + "       C.ordinal_position AS 'position', " _
           + "       Lower(C.column_name) AS 'column_name' " _
           + "  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK " _
           + "  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C " _
           + "    ON C.table_name = PK.table_name " _
           + "   AND C.constraint_name = PK.constraint_name " _
           + " WHERE PK.constraint_type = 'PRIMARY KEY' " _
           + "   AND C.table_name <> 'dtproperties' " _
           + " ORDER BY C.table_name, " _
           + "       C.ordinal_position "
    Dim Res As IEnumerable(Of PrimaryKeys) = _

               DBc.ExecuteQuery(Of PrimaryKeys)(strSQL)
    For Each PKeys As PrimaryKeys In Res
        ' PKeys.table_name
        ' PKeys.column_name
        ' PKeys.position
    Next

    Saturday, April 26, 2008 11:13 AM