using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class PubsProcs
{
    // uses SqlPipe and least expensive way to return rows
	[SqlProcedure]
	public static void getAuthorsByState(SqlString state)
	{
		SqlConnection conn = new SqlConnection("Context Connection=true");
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		conn.Open();
		cmd.CommandText = "select * from authors where state = @state";
		cmd.Parameters.Add("@state", SqlDbType.VarChar);
		cmd.Parameters[0].Value = state;
		SqlContext.Pipe.ExecuteAndSend(cmd);
	}

    // Gets information about a single author
    // and returns data in an OUTPUT parameter
	[SqlProcedure]
	public static void getAuthorInfo(SqlString au_id, out SqlString au_info)
	{
        // build a command
		SqlConnection conn = new SqlConnection("Context Connection=true");
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		conn.Open();
        // 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
		SqlDataReader rdr = cmd.ExecuteReader();
		rdr.Read();
        // 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}", rdr["address"], rdr["city"], rdr["state"], rdr["zip"]);
        // nothing to return beacuse value is sent back via au_info
	}
    // Get author info
    // use a SqlPipe to send info
    // back as message
	[SqlProcedure]
	public static void getAuthorMessage(SqlString au_id)
	{
        // build a command
		SqlConnection conn = new SqlConnection("Context Connection=true");
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		conn.Open();
        // 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 because you know only 
        // a single row will come back
		SqlDataReader rdr = cmd.ExecuteReader();
		rdr.Read(); ;
        // get the SqlPipe from the SqlContext
		SqlPipe p = SqlContext.Pipe;
        // use the SqlPipe.Send to send back the
        // author info as a message
		p.Send(String.Format("{0} {1} {2} {3}", rdr["address"], rdr["city"], rdr["state"], rdr["zip"]));
	}

    // 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 static SqlInt32 getAuthorWithErrors(SqlString au_id, out SqlString au_info)
	{
        // build a command
		SqlConnection conn = new SqlConnection("Context Connection=true");
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		conn.Open();
        // 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 + Read because you know only 
        // a single row will come back
		SqlDataReader rdr = cmd.ExecuteReader();
		bool gotrow = rdr.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 != true)
		{
			rdr.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}", rdr["address"], rdr["city"], rdr["state"], rdr["zip"]);
			rdr.Close();
		}
        // nothing to return, either success returned author info in au_info
        // or error was raised
		return 0;
	}
}
