using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using System.Configuration;

namespace ConnectionApp
{
   public partial class Form1 : Form
   {
      SqlConnection connection;
      SqlCommand command;
      ConnectionStringSettings connectionInfo;
      DataSet dataSet;
      SqlDataAdapter dataAdapter;

      public Form1()
      {
         InitializeComponent();
      }

      #region Fill
      private void button1_Click(object sender, EventArgs e)
      {
         connectionInfo = ConfigurationManager.ConnectionStrings["ConnectionOne"];
         connection = new SqlConnection(connectionInfo.ConnectionString);

         command = new SqlCommand();
         command.Connection = connection;
         command.CommandType = CommandType.Text;
         command.CommandText = "select * from Customers";

         SqlCommand command2 = new SqlCommand();
         command2.Connection = connection;
         command2.CommandType = CommandType.Text;
         command2.CommandText = "select * from Orders";

         //command.StatementCompleted += new StatementCompletedEventHandler(command_StatementCompleted);
         command.StatementCompleted += delegate(object _sender, StatementCompletedEventArgs _e)
         {
            System.Diagnostics.Debug.WriteLine(_e.RecordCount.ToString());
         };

         SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
         SqlDataAdapter dataAdapter2 = new SqlDataAdapter(command2);

         if (dataSet != null)
            dataSet.Clear();

         dataSet = new DataSet("Easy");
         
         //Soll im Schema noch der PrimaryKey gespeichert werden,
         //dann das hier machen
         dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                  
         dataAdapter.Fill(dataSet, "Kunden");
         dataAdapter2.Fill(dataSet, "Bestellungen");

         dataSet.Relations.Add
            (
               "NameOfRelation",
               dataSet.Tables["Kunden"].Columns["CustomerID"],
               dataSet.Tables["Bestellungen"].Columns["CustomerID"]
            );


         dataSet.WriteXml(Application.StartupPath + "\\daten2.xml");
         dataSet.WriteXmlSchema(Application.StartupPath + "\\schema2.xsd");

         dataGridView1.DataSource = dataSet.Tables["Kunden"];
      }
      #endregion

      #region FillError
      private void button2_Click(object sender, EventArgs e)
      {
         connectionInfo = ConfigurationManager.ConnectionStrings["ConnectionOne"];
         connection = new SqlConnection(connectionInfo.ConnectionString);

         command = new SqlCommand();
         command.Connection = connection;
         command.CommandType = CommandType.Text;
         command.CommandText = "select top 10 * from Customers";

         command.StatementCompleted += delegate(object _sender, StatementCompletedEventArgs _e)
         {
            System.Diagnostics.Debug.WriteLine(_e.RecordCount.ToString());
         };

         SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

         if (dataSet != null)
            dataSet.Clear();

         dataSet = new DataSet("MyDataSet");

         dataAdapter.FillError += delegate(object _sender, FillErrorEventArgs _e)
         {
            textBox1.Text += _e.Errors.Message + "\r\n";
            textBox1.Text += _e.Values[0].ToString() + "\r\n";
            textBox1.Text += _e.Values[1].ToString() + "\r\n";
            textBox1.Text += _e.Values[2].ToString() + "\r\n";

            object[] alternativ = _e.Values;

            alternativ[1] = 0;
            _e.DataTable.Rows.Add(alternativ);
            _e.Continue = true;
         };

         dataSet.ReadXmlSchema(Application.StartupPath + "\\schema.xsd"); 
         //In Ausgabeverz. kopieren bei Eigenschaften von schema.xsd

         dataAdapter.Fill(dataSet, "Kunden");

         dataGridView1.DataSource = dataSet.Tables["Kunden"];
      }
      #endregion

      #region Fill for Update
      private void button3_Click(object sender, EventArgs e)
      {
         connectionInfo = ConfigurationManager.ConnectionStrings["ConnectionOne"];
         connection = new SqlConnection(connectionInfo.ConnectionString);

         SqlCommand selectcommand = new SqlCommand();
         selectcommand.Connection = connection;
         selectcommand.CommandType = CommandType.Text;
         selectcommand.CommandText = "select * from Customers";

         SqlCommand updatecommand = new SqlCommand();
         updatecommand.Connection = connection;
         updatecommand.CommandType = CommandType.Text;
         updatecommand.CommandText = "update Customers set CompanyName = @CompanyName where CustomerID = @CustomerID";
         updatecommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
         updatecommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

         updatecommand.Parameters["@CustomerID"].SourceVersion = DataRowVersion.Original;

         dataAdapter = new SqlDataAdapter();
         dataAdapter.SelectCommand = selectcommand;
         dataAdapter.UpdateCommand = updatecommand;

         //Tritt während der Ausführung von Update ein, bevor ein Befehl für die Datenquelle ausgeführt 
         //wird. Es wird eine Aktualisierung versucht, also wird das Ereignis ausgelöst.
         dataAdapter.RowUpdating += delegate(object _sender, SqlRowUpdatingEventArgs _e)
         {
            object[] columnItems = _e.Row.ItemArray;

            if (columnItems[1].ToString() == "Alfreds Futterkistens")
            {
               _e.Status = UpdateStatus.SkipCurrentRow;
               _e.Row.RejectChanges();
            }
         };

         //Tritt während der Ausführung von Update ein, nachdem ein Befehl für die Datenquelle ausgeführt 
         //wurde. Es wird eine Aktualisierung versucht, also wird das Ereignis ausgelöst. 
         dataAdapter.RowUpdated += delegate(object _sender, SqlRowUpdatedEventArgs _e)
         {
            object[] columnItems = _e.Row.ItemArray;

            if (columnItems[2].ToString() == "Maria Anderss")
            {
               _e.Status = UpdateStatus.SkipCurrentRow;
               _e.Row.RejectChanges();
            }

            MessageBox.Show(_e.RecordsAffected.ToString());
         };

         if (dataSet != null)
            dataSet.Clear();

         dataSet = new DataSet("Easy");

         dataAdapter.Fill(dataSet, "Kunden");

         dataGridView1.DataSource = dataSet.Tables["Kunden"];
      }
      #endregion

      #region Update
      private void button4_Click(object sender, EventArgs e)
      {
         dataAdapter.Update(dataSet,"Kunden");
      }
      #endregion

      #region DataTable
      private void button5_Click(object sender, EventArgs e)
      {
         connectionInfo = ConfigurationManager.ConnectionStrings["ConnectionOne"];
         connection = new SqlConnection(connectionInfo.ConnectionString);

         command = new SqlCommand();
         command.Connection = connection;
         command.CommandType = CommandType.Text;
         command.CommandText = "select * from Customers";

         SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

         if (dataSet != null)
            dataSet.Clear();

         dataSet = new DataSet("Easy");

         dataAdapter.Fill(dataSet, "Kunden");


         //DataTable dt = dataSet.Tables["Kunden"];

         //DataTableReader dtr = dt.CreateDataReader();

         //while (dtr.Read())
         //{
         //   textBox1.Text += dtr["CustomerID"].ToString() + "\r\n";
         //}

         dataSet.WriteXml(Application.StartupPath + "\\daten2.xml");
         dataSet.WriteXmlSchema(Application.StartupPath + "\\schema2.xsd");

         DataView dv = dataSet.Tables["Kunden"].DefaultView;
         dv.Sort = "CompanyName";
         dv.RowFilter = "CompanyName like '%ab%'";

         //dataGridView1.DataSource = dataSet.Tables["Kunden"];
         dataGridView1.DataSource = dv;
      }
      #endregion

      private void button6_Click(object sender, EventArgs e)
      {
         connectionInfo = ConfigurationManager.ConnectionStrings["ConnectionOne"];
         connection = new SqlConnection(connectionInfo.ConnectionString);

         connection.Open();

         SqlTransaction transaction = connection.BeginTransaction("NameOfTransaction");

         SqlCommand command = connection.CreateCommand();

         command.Connection = connection;
         command.CommandType = CommandType.Text;
         command.Transaction = transaction;

         try
         {
            command.CommandText = 
               "insert into Customers(CustomerID,CompanyName)values('abcde','a')";
            command.ExecuteNonQuery();
            command.CommandText =
               "insert into Customers(CustomerID,CompanyName)values('fghijk','b')";
            command.ExecuteNonQuery();

            transaction.Commit();
         }
         catch
         {
            transaction.Rollback();
         }
      }
   }
}