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


namespace open_and_send_to_SQL
{
    class ReadFromFile
    {

        static void Main()
        {
            try
            {
                // Create the connection to the Database
                using (SqlConnection conn = new SqlConnection())
                {
                    // Create the connectionString
                    // Trusted_Connection is used to denote the connection uses Windows Authentication
                    conn.ConnectionString = "Server=LT06-TOM;Database=IrishNHS;User Id=sa;Password =THE59dhYu2ye";
                    //Server=LT06-TOM;Database=IrishNHS;User Id=sa;Password =THE59dhYu2ye;

                    // Read each line of the file into a string array. Each element of the array is one line of the file.
                    string[] lines = System.IO.File.ReadAllLines(@"C:\WatchFolder\qa\StaffChanges_20180306.xlsx");

                    //Save File as Temp then you can delete it if you want
                    var path = (@"C:\WatchFolder\qa\StaffChanges_20180306.xlsx");
                    //Save File as Temp then you can delete it if you want

                    //FileUpload1.SaveAs(path);
                    //string path = @"C:\WatchFolder\WriteLines2.txt";

                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", path);

                    // Create Connection to Excel Workbook
                    using (OleDbConnection connection =
                                 new OleDbConnection(excelConnectionString))
                    {
                        OleDbCommand command = new OleDbCommand
                                ("Select * FROM [Sheet1$]", connection);

                        connection.Open();

                        // Create DbDataReader to Data Worksheet
                        using (DbDataReader dr = command.ExecuteReader())
                        {

                            // SQL Server Connection String
                            string sqlConnectionString = @"Server=LT06-TOM;Database=IrishNHS;User Id=sa;Password =THE59dhYu2ye";

                            // Bulk Copy to SQL Server
                            using (SqlBulkCopy bulkCopy =
                                       new SqlBulkCopy(sqlConnectionString))
                            {
                                bulkCopy.DestinationTableName = "VSImportTable";
                                bulkCopy.WriteToServer(dr);
                                Console.WriteLine("The data has been exported succefuly from Excel to SQL");
                            }
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }




                // Keep the console window open in debug mode
                Console.WriteLine("Press any key to exit.");
                System.Console.ReadKey();
            }
        }
    }