using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Dimensions;

namespace UseLDimOnClient
{
  class Program
  {
    static void Main(string[] args)
    {
      Listing1();
      Listing2();
      Listing3();
      Listing4();
      Listing5();

      Listing7and8();
    }

    static void Listing1()
    {
      string connect_string = GetConnectStringFromConfigFile();
      SqlConnection conn = new SqlConnection(connect_string);
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = conn;

      conn.Open();
      cmd.CommandText = 
        "INSERT INTO dbo.Tiles VALUES(1, '10 ft, 8 in')";
      int i;
      i = cmd.ExecuteNonQuery();
      cmd.CommandText = 
        @"UPDATE dbo.Tiles 
           SET Length.SetIn()
           WHERE id = 1";
      i = cmd.ExecuteNonQuery();
      cmd.Dispose(); conn.Dispose();

    }
  static void Listing2()
  {
    // Use the same Tiles table as in the first example.
    string connect_string = GetConnectStringFromConfigFile();
    SqlConnection conn = new SqlConnection(connect_string);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    conn.Open();

    // get the entire LDim column
    cmd.CommandText = "SELECT id, length FROM dbo.Tiles";
    SqlDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
      {
       // rdr["Length"] contains an instance of a LDim class
       // easiest access, WriteLine calls ToString() implicitly
       Console.WriteLine("column 1 is {0}", rdr["Length"]);
      }
    rdr.Close();
    cmd.Dispose();
    conn.Dispose();
    }

  static void Listing3()
  {
    // Use the same Tiles table as in the first example.
    string connect_string = GetConnectStringFromConfigFile();
    SqlConnection conn = new SqlConnection(connect_string);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    conn.Open();

    // get the entire LDim column
    cmd.CommandText = "SELECT id, length FROM dbo.Tiles";
    SqlDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
      // rdr["length"] contains an instance of a LDim class
      // get a strongly typed instance
      LDim ld = (LDim)rdr["length"];
    }

    rdr.Close();
    cmd.Dispose();
    conn.Dispose();
  }

    static void Listing4()
    {
      // Use the same Tiles table as in the first example.
      string connect_string = GetConnectStringFromConfigFile();
      SqlConnection conn = new SqlConnection(connect_string);
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = conn;
      conn.Open();

      // get the entire LDim column
      cmd.CommandText = "SELECT id, length FROM dbo.Tiles";
      SqlDataReader rdr = cmd.ExecuteReader();

      // SqlDataReader obtained from the previous code

      LDim p1, p2;
      while (rdr.Read())
      {
        // rdr[1] contains an instance of a LDim class
        // p1 is a copy of rdr[1]
        p1 = (LDim)rdr[1];

        // attempting to update this will work
        p1.Inches = 1000;

        // retrieve the value in the DataReader again
        p2 = (LDim)rdr[1];
        // now p1 != p2
      }


      rdr.Close();
      cmd.Dispose();
      conn.Dispose();
    }

    static void Listing5()
    {
    using (SqlConnection conn = new SqlConnection("server=.;integrated security=true;database=pubs; Type System Version=SQL Server 2000"))
    using (SqlCommand cmd = new SqlCommand("select id, length from dbo.tiles", conn))
    {
      // Try it both ways
      //conn.ConnectionString += ";Type System Version=SQL Server 2000";

      conn.Open();

        SqlDataReader rdr = cmd.ExecuteReader();
  DataTable t = rdr.GetSchemaTable();

  // either of these print Dimensions.LDim, .NET class name
  // prints byte[] if "Type System Version=SQL Server 2000"
  Console.WriteLine(t.Rows[1]["DataType"]);
  // prints SqlBinary if "Type System Version=SQL Server 2000"
  Console.WriteLine(t.Rows[1]["ProviderSpecificDataType"]);

  // this prints demo.dbo.LDim, SQL Server name for UDT
  // prints varbinary if "Type System Version=SQL Server 2000"
  Console.WriteLine(t.Rows[1]["DataTypeName"]);

  // this prints 4-part assembly name,
  // blank if "Type System Version=SQL Server 2000"
  Console.WriteLine(t.Rows[1]["UdtAssemblyQualifiedName"]);
  // Prints 21 (varbinary) if "Type System Version=SQL Server 2000"
  // else prints 29
  Console.WriteLine(t.Rows[1]["ProviderType"]);
  // Always prints 29 (SQLCLR UDT)
  Console.WriteLine(t.Rows[1]["NonVersionedProviderType"]);
    }

  }

    static void Listing7and8()
    {
      string connect_string = GetConnectStringFromConfigFile();
      SqlConnection conn = new SqlConnection(connect_string);
      SqlCommand cmd = new SqlCommand("dbo.AddLDim", conn);
      cmd.CommandType = CommandType.StoredProcedure;

      // define two LDims to add
      LDim p1 = LDim.Parse("2 ft");
      LDim p2 = LDim.Parse("10 in");

      // now, define the Parameters
      // use the overload that takes a parameter name 
      // and type for the input parms
      cmd.Parameters.Add("@a", SqlDbType.Udt);
      cmd.Parameters[0].UdtTypeName = "demo.dbo.LDim";
      cmd.Parameters[0].Value = p1;
      cmd.Parameters.Add("@b", SqlDbType.Udt);
      cmd.Parameters[1].Value = p2;
      cmd.Parameters[1].UdtTypeName = "demo.dbo.LDim ";

      // define the output parameters. This parameter need not be initialized
      cmd.Parameters.Add("@c", SqlDbType.Udt);
      cmd.Parameters[2].UdtTypeName = "demo.dbo.LDim";
      cmd.Parameters[2].Direction = ParameterDirection.ReturnValue;
    }

  static void ListingX()
  {
    using (SqlConnection conn = new SqlConnection("server=.;integrated security=true;database=pubs; Type System Version=SQL Server 2000"))
    using (SqlCommand cmd = new SqlCommand("select id, length from dbo.tiles", conn))
    {
      conn.Open();

      SqlDataReader rdr1 = cmd.ExecuteReader();

      while (rdr1.Read())
          Console.WriteLine("{0} {1}", rdr1["id"], rdr1["length"]);

        DataTable t = rdr1.GetSchemaTable();
        for (int i = 0; i < t.Columns.Count; i++)
          Console.WriteLine("{0} {1}", t.Columns[i].ColumnName, t.Rows[1][i]);
        
        rdr1.Close();

        SqlDataReader rdr2 = cmd.ExecuteReader();
        while (rdr2.Read())
        {
            LDim ld = (LDim)rdr2["length"];
            Console.WriteLine("{0} {1}", rdr2["id"], ld);
        }
        rdr2.Close();

        SqlDataReader rdr3 = cmd.ExecuteReader();
        LDim ld3 = new LDim();

        SqlBytes b = new SqlBytes();
        
        while (rdr3.Read())
        {
            b = rdr3.GetSqlBytes(1);
            ld3.Read(new BinaryReader(b.Stream));
            Console.WriteLine(ld3);
        }
      }
    }
    static string GetConnectStringFromConfigFile()
    {
      return "server=.;integrated security=true;database=pubs";
    }
  }
}
