using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataAccess
{

    class Program
    {
        [DllImport("kernel32.dll", EntryPoint = "Beep")]
        public static extern bool MyBeep(uint iFreq, uint iDuration);

        string strConnNorthwind = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Coding\ContosoUniversity\DataAccess\bin\Debug\northwind.mdf;Integrated Security=True;async=true";
        string strConnPubs = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Coding\ContosoUniversity\DataAccess\bin\Debug\pubs.mdf;Integrated Security=True；async=true";

        public void Bridge()
        {
            DataTable dtEmployees = new DataTable();
            using (SqlConnection cnNorthwind = new SqlConnection(strConnNorthwind))
                using (DbCommand cmd = new SqlCommand("select * from Employees --where EmployeeID=7",cnNorthwind))
                {
                cnNorthwind.Open();
                    using (DbDataReader dr = cmd.ExecuteReader())
                    {
                        dtEmployees.Load(dr);
                    }
                }
            using (DbDataReader dr = dtEmployees.CreateDataReader())
                while (dr.Read())
                {
                    Console.WriteLine(dr.GetString(2) + " " + dr.GetString(1));
                }
        }

        public void AsyncCall()
        {
            using (SqlConnection cnNorthwind = new SqlConnection(strConnNorthwind))
            using (SqlConnection cnPubs = new SqlConnection(strConnPubs))
            {
                cnNorthwind.Open();
                SqlCommand cmdEmp = new SqlCommand("select * from Employees", cnNorthwind);
                IAsyncResult arEmp = cmdEmp.BeginExecuteReader();
                cnPubs.Open();
                SqlCommand cmdAuthors = new SqlCommand("select * from authors", cnPubs);
                IAsyncResult arAuthors = cmdAuthors.BeginExecuteReader();
                //here, you can work with the current thread while sql requsts are executed.
                SqlDataReader drEmp = cmdEmp.EndExecuteReader(arEmp);
                SqlDataReader drAuthors = cmdAuthors.EndExecuteReader(arAuthors);
                while (drEmp.Read())
                    Console.WriteLine(drEmp.GetString(2) + " " + drEmp.GetString(1));
                while (drAuthors.Read())
                    Console.WriteLine(drAuthors.GetString(2) + " " + drAuthors.GetString(1));
            }
        }

        public void BulkCopy()
        {
            using (SqlConnection cnEmp = new SqlConnection(strConnNorthwind))
            {
                SqlCommand cmdEmp = new SqlCommand("select * from Employees", cnEmp);
                cnEmp.Open();
                SqlDataReader drEmp = cmdEmp.ExecuteReader();
                //bulk copy of all rows,need employees table prepared first!
                using (SqlBulkCopy bc=new SqlBulkCopy(strConnPubs))
                {
                    bc.DestinationTableName = "EMPLOYEES";
                    bc.WriteToServer(drEmp);
                }
                Console.WriteLine("Over");
            }
        }
        static void Main(string[] args)
        {
            //MyBeep(500, 1000);
            Program p = new Program();
            p.Bridge();
            p.AsyncCall();
            p.BulkCopy();
            Console.ReadKey();
        }
    }
}