//Source code from the Code Associate C# code library, Full documentation and latest updates can be found
//@ http://www.codeassociate.com/caapi/
using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using NUnit.Framework;

namespace CA.Examples.System_Data
{
    [TestFixture]
    public class DataTable_Examples
    {


        [Test]
        public void SelectDistinctValuesExample()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Col1", typeof (int));
            dt.Columns.Add("Col2", typeof(int));
            dt.Columns.Add("Col3", typeof(int));
            dt.AcceptChanges();

            dt.LoadDataRow(new object[] {1,1,1}, true);
            dt.LoadDataRow(new object[] {1,1,2}, true);
            dt.LoadDataRow(new object[] {1,1,3}, true);
            dt.LoadDataRow(new object[] {1,2,1}, true);
            dt.LoadDataRow(new object[] {1,2,2}, true);
            dt.LoadDataRow(new object[] {1,2,3}, true);
            dt.LoadDataRow(new object[] {1,3,1}, true);
            dt.LoadDataRow(new object[] {1,3,2}, true);
            dt.LoadDataRow(new object[] {1,3,3}, true);

            dt.LoadDataRow(new object[] {2,1,1}, true);
            dt.LoadDataRow(new object[] {2,1,2}, true);
            dt.LoadDataRow(new object[] {2,1,3}, true);
            dt.LoadDataRow(new object[] {2,2,1}, true);
            dt.LoadDataRow(new object[] {2,2,2}, true);
            dt.LoadDataRow(new object[] {2,2,3}, true);
            dt.LoadDataRow(new object[] {2,3,1}, true);
            dt.LoadDataRow(new object[] {2,3,2}, true);
            dt.LoadDataRow(new object[] {2,3,3}, true);

            DataView distinctTableView = new DataView(dt);
            DataTable distinctResult;

            // Example 1 Select Distinct on Col1
            distinctResult = distinctTableView.ToTable(true, new[] { "Col1" });
            // assert result is  
            // 1
            // 2 
            Assert.AreEqual(2, distinctResult.Rows.Count);
            Assert.AreEqual(1, distinctResult.Rows[0][0]);
            Assert.AreEqual(2, distinctResult.Rows[1][0]);


            // Example 2 Select Distinct on Col2
            distinctResult = distinctTableView.ToTable(true, new[] { "Col2" });
            // assert result is  
            // 1
            // 2 
            // 3
            Assert.AreEqual(3, distinctResult.Rows.Count);
            Assert.AreEqual(1, distinctResult.Rows[0][0]);
            Assert.AreEqual(2, distinctResult.Rows[1][0]);
            Assert.AreEqual(3, distinctResult.Rows[2][0]);

            distinctResult = distinctTableView.ToTable(true, new[] { "Col1" , "Col2" });
            Assert.AreEqual(6, distinctResult.Rows.Count);
            // assert result is
            // 1,1
            // 1,2
            // 1,3
            // 2,1
            // 2,2
            // 2.3
            Assert.AreEqual(1, distinctResult.Rows[0][0]);
            Assert.AreEqual(1, distinctResult.Rows[1][0]);
            Assert.AreEqual(1, distinctResult.Rows[2][0]);
            Assert.AreEqual(1, distinctResult.Rows[0][1]);
            Assert.AreEqual(2, distinctResult.Rows[1][1]);
            Assert.AreEqual(3, distinctResult.Rows[2][1]); // only do 6 cells assume rest are correct
        }


        [Test]
        public void UsingWriteXmlSchema()
        {
            // This sets up a table as follows
            // TestTable
            // (
            //  testInt int PrimaryKey not null
            //  testString string null
            //  testStringWithConstraint MaxLength = 100 null
            //  testNonNullString not null
            //  testDateTime DateTime
            //  )
            DataTable dt1 = new DataTable("TestTable");
            DataColumn testIntPk = dt1.Columns.Add("testInt", typeof(int));
            dt1.PrimaryKey = (new DataColumn[] { testIntPk });
            dt1.Columns.Add("testString", typeof (string));
            DataColumn testStringWithConstraint = dt1.Columns.Add("testStringWithConstraint", typeof(string));
            testStringWithConstraint.MaxLength = 100;
            DataColumn testNonNullString = dt1.Columns.Add("testNonNullString", typeof(string));
            testNonNullString.AllowDBNull = false;
            dt1.Columns.Add("testDateTime", typeof (DateTime));
            dt1.AcceptChanges();
            // Done setting up the test table..


            // Now we can write the schema of the test table to a string writer using the WriteXmlSchema method
            StringBuilder sb = new StringBuilder();
            StringWriter writer = new StringWriter(sb);
            dt1.WriteXmlSchema(writer);
            string schemaAsString = sb.ToString();
            Trace.WriteLine(schemaAsString);
            writer.Close();


            //Now we can reverse the process..  read the schema from the string into a datatable
            DataTable dt2 = new DataTable();
            StringReader reader = new StringReader(schemaAsString);
            dt2.ReadXmlSchema(reader);


            // now asseert dt = dt2
            Assert.AreEqual(dt1.TableName, dt2.TableName);
            Assert.AreEqual(dt1.Columns.Count, dt2.Columns.Count);
            foreach (DataColumn dc1 in dt1.Columns)
            {
                Assert.IsNotNull(dt2.Columns[dc1.ColumnName]);
            }
            Assert.AreEqual(dt1.PrimaryKey[0].ColumnName, dt2.PrimaryKey[0].ColumnName);
            Assert.AreEqual(dt1.Columns["testStringWithConstraint"].MaxLength,
                            dt2.Columns["testStringWithConstraint"].MaxLength);

            Assert.AreEqual(dt1.Columns["testNonNullString"].AllowDBNull,
                           dt2.Columns["testNonNullString"].AllowDBNull);
        }

    }
}