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;

        public Form1()
        {
            InitializeComponent();
        }

        #region DataReader
        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 Person.Person";

            command.StatementCompleted += new StatementCompletedEventHandler(command_StatementCompleted);

            command.Connection.Open();

            SqlDataReader dataReader = command.ExecuteReader();

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

            dataReader.Close();
            command.Dispose();
            connection.Close();
        }
        #endregion

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

            command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = "waitfor delay '00:00:05';select * from Person.Person;";

            command.Connection.Open();

            IAsyncResult result = command.BeginExecuteReader();

            textBox1.Text += "Bitte warten";
            while (!result.IsCompleted)
            {
                System.Threading.Thread.Sleep(250);
                textBox1.Text += " .";
                Application.DoEvents();
            }
            textBox1.Text += "\r\n";

            SqlDataReader dataReader = null;
            if (result.IsCompleted)
                dataReader = command.EndExecuteReader(result);

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

            dataReader.Close();
            command.Dispose();
            connection.Close();
        }
        #endregion

        private void button3_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 Person.Person where BusinessEntityID = @BusinessEntityID;";

            command.Parameters.AddWithValue("@BusinessEntityID", 1);

            command.Connection.Open();

            SqlDataReader dataReader = command.ExecuteReader();

            while (dataReader.Read())
            {
                textBox1.Text += dataReader["BusinessEntityID"].ToString() + "\r\n";
            }

            dataReader.Close();
            command.Dispose();
            connection.Close();
        }

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

            command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_MyProc";

            command.Parameters.AddWithValue("@BusinessEntityID", 1);

            command.Connection.Open();

            SqlDataReader dataReader = command.ExecuteReader();

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

            dataReader.Close();
            command.Dispose();
            connection.Close();

        }

        void command_StatementCompleted(object sender, StatementCompletedEventArgs e)
        {
            //Tritt auf, wenn Abfrage fertig ist.
            textBox1.Text += e.RecordCount.ToString() + "\r\n";
        }
    }
}