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

public partial class CoolProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrSimpleProcedure()
  {
    SqlPipe p = SqlContext.Pipe;
    p.Send("Hello World");    
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrGetVersion()
  {
    SqlConnection conn = new SqlConnection("context connection=true");
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT @@Version";
    SqlPipe p = SqlContext.Pipe;

    conn.Open();
    p.Send(cmd.ExecuteScalar().ToString());
    conn.Close();

  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrGetEmployees()
  {
    SqlConnection conn = new SqlConnection("context connection=true");
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM HumanResources.vEmployee";
    SqlPipe p = SqlContext.Pipe;

    conn.Open();
    p.Send(cmd.ExecuteReader());
    conn.Close();
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrGetCustomRecord()
  {

    SqlMetaData col1Info, col2Info, col3Info;
    SqlDataRecord record;

    // Create the schema

    col1Info = new SqlMetaData("Date", SqlDbType.DateTime);
    col2Info = new SqlMetaData("IP", SqlDbType.NVarChar, 15);
    col3Info = new SqlMetaData("Event", SqlDbType.NVarChar, 50);
    record = new SqlDataRecord(new SqlMetaData[] { col1Info,col2Info,col3Info });

    // Set the record fields.
    record.SetDateTime(0, DateTime.Now);
    record.SetString(1, "127.0.0.1");
    record.SetString(2, "Access Denied");

    // Send the record to the calling program.
    SqlContext.Pipe.Send(record);
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrInputStringParamProcedure(SqlString Name)
  {
    SqlPipe p = SqlContext.Pipe;
    p.Send("Hello " + Name.ToString());
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrOutputStringParamProcedure(out SqlString Name)
  {
    Name = "User";
    SqlPipe p = SqlContext.Pipe;
    p.Send("Hello " + Name.ToString());
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void clrInputOutputStringParamProcedure(ref SqlString Name)
  {
    Name = Name + " User";
    SqlPipe p = SqlContext.Pipe;
    p.Send("Hello " + Name.ToString());
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static int clrReturnValueProcedure()
  {
    SqlPipe p = SqlContext.Pipe;
    p.Send("Hello World");
    return 42;
  }

};
