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


public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void ReturnData1()
  {
    SqlConnection conn = new SqlConnection("context connection=true");
    conn.Open();
    SqlCommand cmd = conn.CreateCommand();

    // 1. this is a user-defined function
    // returning a single value (authorname) as VARCHAR
    cmd.CommandText = "GetFullAuthorNameById";
    // required from procedure or UDF
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", "172-32-1176");

    String fullname = (String)cmd.ExecuteScalar();
    // use fullname
    cmd.Parameters.Clear();

    // 2. returns one row
    cmd.CommandText = "GetAuthorInfoById";
    // required from procedure or UDF
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", "172-32-1176");

    SqlDataReader rdr1 = cmd.ExecuteReader();
    // use fields in SqlDataReader
    rdr1.Close();
    cmd.Parameters.Clear();


    // 3. returns multiple rows
    cmd.CommandText = "select * from authors";
    cmd.CommandType = CommandType.Text;

    SqlDataReader rdr2 = cmd.ExecuteReader();
    while (rdr2.Read())
      // process rows in SqlDataReader     
      { }
    rdr2.Close();

  }
};
