using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.Xml;

namespace GetXmlOnClient
{
    class Program
    {
        static void Main(string[] args)
        {
          GetXMLColumn();
          AddARow();
          GetXMLColumn2();
          InsertXMLFromClient();

          FigureX();
        }

      static void GetXMLColumn() {
        string connect_string = GetConnectStringFromConfigFile();
        using (SqlConnection conn = new SqlConnection(connect_string))
        using (SqlCommand cmd = new SqlCommand(
               "select * from xmltab", conn))
         {
          conn.Open();
          SqlDataReader rdr = cmd.ExecuteReader();
          DataTable t = rdr.GetSchemaTable();

          while (rdr.Read())
            {
              SqlXml sx = rdr.GetSqlXml(1);
              XmlReader xr = sx.CreateReader();
              xr.Read(); 
              Console.WriteLine(xr.ReadOuterXml());
            }
         }
        } 

      static void AddARow() {
        string connect_string = GetConnectStringFromConfigFile();
        using (SqlConnection conn = new SqlConnection(connect_string))
        using (SqlCommand cmd = new SqlCommand(
               "INSERT xmltest(xmlcol) VALUES(@x)", conn))
         {
          conn.Open();
          cmd.Parameters.Add("@x", SqlDbType.Xml);

          // connect the parameter value to a file
          XmlReader xr = XmlReader.Create("somexml.xml");
          cmd.Parameters[0].Value = new SqlXml(xr);
          int i = cmd.ExecuteNonQuery();
         }
        }

      static void GetXMLColumn2()
      {
        string connect_string = GetConnectStringFromConfigFile();
        using (SqlConnection conn = new SqlConnection(connect_string))
        using (SqlCommand cmd = new SqlCommand(
               "select * from xmltest", conn))
        {
          conn.Open();
          SqlDataReader rdr = cmd.ExecuteReader();
          // prints "System.String"
          Console.WriteLine(rdr[1].GetType());

          // fails, invalid cast
          XmlReader xr = (XmlReader)rdr[1];

          // this works
          string s = (string)rdr[1];
        }
     
      }

    static void InsertXMLFromClient() {
    string connect_string = GetConnectStringFromConfigFile();
    using (SqlConnection conn = new SqlConnection(connect_string))
    using (SqlCommand cmd1 = new SqlCommand(
           "INSERT xmltab (xmlcol) VALUES(@x)", conn))
    using (SqlCommand cmd2 = new SqlCommand(
           " insert_xml", conn))
     {
      string s = "<somedoc/>";

      conn.Open();
      
      // server-side conversion
      cmd1.Parameters.Add("@x", SqlDbType.NVarChar);
      cmd1.Parameters[0].Value = s;
      cmd1.ExecuteNonQuery();

      // client-side conversion works too
      cmd2.CommandType = CommandType.StoredProcedure;
      cmd2.Parameters.Add("@x", SqlDbType.Xml);
      cmd2.Parameters[0].Value = s;
      cmd2.ExecuteNonQuery();
     }
    }

      static void UseExecXmlReader()
      {
        string connect_string = GetConnectStringFromConfigFile();
        using (SqlConnection conn = new SqlConnection(connect_string))
        using (SqlCommand cmd1 = new SqlCommand(
               "SELECT * FROM pubs..authors FOR XML AUTO,ROOT('root')", conn))
        using (SqlCommand cmd2 = new SqlCommand(
               "SELECT * FROM pubs..authors FOR XML AUTO", conn))
        using (SqlCommand cmd3 = new SqlCommand(
               "SELECT * FROM xmltab", conn))
        {
          conn.Open();
          // contains fragment
          XmlReader xr1 = cmd1.ExecuteXmlReader();
          // contains document
          XmlReader xr2 = cmd2.ExecuteXmlReader();
          // contains contents of first row in xmltab only
          XmlReader xr3 = cmd3.ExecuteXmlReader();
          // use XmlReaders, then
          xr1.Close(); xr2.Close(); xr3.Close();
        }
      }


        static void FigureX()
        {
            using (SqlConnection conn = new SqlConnection("server=.;integrated security=sspi;database=adventureworks"))
            using (SqlCommand cmd = new SqlCommand("select additionalcontactinfo from person.Contact", conn))
            {
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                rdr.Read();
                Console.WriteLine(rdr.GetProviderSpecificFieldType(0));
                Object o = rdr.GetProviderSpecificValue(0);
                Console.WriteLine(o);
                    
            }
        }
      static string GetConnectStringFromConfigFile()
      {
        return "server=.;integrated security=true;database=xmldb";
      }
    }
}
