Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Data.SqlTypes


Public Class PubsProcs

    ' uses SqlPipe and least expensive method to return rows
    <SqlProcedure()> _
    Public Shared Sub getAuthorsByState(ByVal state As SqlString)
        Dim conn As New SqlConnection("Context Connection=true")
        Dim cmd As New SqlCommand
        conn.Open()
        cmd.Connection = conn
        cmd.CommandText = "select * from authors where state = @state"
        cmd.Parameters.Add("@state", SqlDbType.VarChar)
        cmd.Parameters(0).Value = state
        Dim pipe As SqlPipe = SqlContext.Pipe
        pipe.ExecuteAndSend(cmd)
    End Sub

    ' Gets information about a single author
    ' and returns data in an OUTPUT parameter
    <SqlProcedure()> _
    Public Shared Sub getAuthorInfo(ByVal au_id As SqlString, ByRef au_info As SqlString)
        ' build a command
        Dim conn As New SqlConnection("Context Connection=true")
        Dim cmd As New SqlCommand
        conn.Open()
        cmd.Connection = conn
        ' build command text
        ' note that this will return a single row because
        ' au_id is the primary key
        cmd.CommandText = "select address, city, state, zip from authors where au_id = @au_id"
        ' make a paramter to hold the author id
        cmd.Parameters.Add("@au_id", SqlDbType.VarChar)
        ' put in the value of the author id
        cmd.Parameters(0).Value = au_id
        ' use ExecuteReader, only 
        ' a single row will come back
        Dim rec As SqlDataReader = cmd.ExecuteReader()
        ' au_info is marked 'out' so you must create
        ' a SqlString for it, as it is null when 
        ' getAuthorInfo is called
        au_info = New SqlString
        ' use Format to build the information string
        au_info = String.Format("{0} {1} {2} {3}", rec("address"), rec("city"), rec("state"), rec("zip"))
        ' nothing to return beacuse value is sent back via au_info
    End Sub
    ' Get author info
    ' use a SqlPipe to send info
    ' back as message
    <SqlProcedure()> _
    Public Shared Sub getAuthorMessage(ByVal au_id As SqlString)
        ' build a command
        Dim conn As New SqlConnection("Context Connection=true")
        Dim cmd As New SqlCommand
        conn.Open()
        cmd.Connection = conn
        ' build command text
        ' note that this will return a single row because
        ' au_id is the primary key
        cmd.CommandText = "select address, city, state, zip from authors where au_id = @au_id"
        ' make a paramter to hold the author id
        cmd.Parameters.Add("@au_id", SqlDbType.VarChar)
        ' put in the value of the author id
        cmd.Parameters(0).Value = au_id
        ' use ExecuteReader, only 
        ' a single row will come back
        Dim rec As SqlDataReader = cmd.ExecuteReader()
        ' get the SqlPipe from the SqlContext
        Dim p As SqlPipe = SqlContext.Pipe
        ' use the SqlPipe.Send to send back the
        ' author info as a message
        p.Send(String.Format("{0} {1} {2} {3}", rec("address"), rec("city"), rec("state"), rec("zip")))
    End Sub

    ' Previous function ignored an errors when
    ' query was executed
    ' Gets author info, but returns a customized error
    ' message if ssn passed in does not correspond
    ' to an author in pubs
    <SqlProcedure()> _
    Public Shared Function getAuthorWithErrors(ByVal au_id As SqlString, ByRef au_info As SqlString) As SqlInt32
        ' build a command
        Dim conn As New SqlConnection("Context Connection=true")
        Dim cmd As New SqlCommand
        conn.Open()
        cmd.Connection = conn
        ' build command text
        ' note that this will return a single row because
        ' au_id is the primary key
        cmd.CommandText = "select address, city, state, zip from authors where au_id = @au_id"
        ' make a paramter to hold the author id
        cmd.Parameters.Add("@au_id", SqlDbType.VarChar)
        ' put in the value of the author id
        cmd.Parameters(0).Value = au_id
        ' use ExecuteReader, only 
        ' a single row will come back
        Dim rec As SqlDataReader = cmd.ExecuteReader()
        Dim gotrow As Boolean = rec.Read()

        ' make SqlString to hold result
        ' note that if you do not give this
        ' string a value it will be null
        au_info = New SqlString
        ' check to see if lookup was successful
        If gotrow = False Then
            rec.Close()
            'au_info = String.Format("Author {0} is not found", au_id);
            'cmd.CommandText = "Raiserror ('" + String.Format("Author {0} is not found", au_id) + "', 16, 1)";
            ' lookup was not successful, raise an error
            cmd.CommandText = "Raiserror (50005, 16, 1, '" + au_id.ToString() + "') with seterror"
            SqlContext.Pipe.ExecuteAndSend(cmd)
            Return -50005
        Else
            ' lookup was successful, set au_info to information string
            au_info = String.Format("{0} {1} {2} {3}", rec("address"), rec("city"), rec("state"), rec("zip"))
            rec.Close()
        End If
        ' nothing to return, either success returned author info in au_info
        ' or error was raised
        Return 0
    End Function
End Class
