using System;
using System.Data;
using System.Data.Common;
//using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class LoadCOE : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                    Data Source=" + Server.MapPath("~/App_Data/2007COE_PPLoadData.xls") + ";" +
                                    "Extended Properties=\"Excel 8.0;HDR=YES;\"";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DbDataAdapter adapter = factory.CreateDataAdapter();

        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = 
            "SELECT EventTitle, COE_ID, EventCategory, EventDescription, " +
            "Comments, PrimaryLeadPortfolio, KeyPartners, PrimaryTargetSegment, SecondaryTargetSegment, " +
            "PeopleExpected, ProposedDate_Time, EndDate_Time, Location, " +
            "CreatedBy, ModifiedBy " +
            "FROM [COE2007$] " +
            "WHERE TypeofEvent IS NOT NULL";

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        selectCommand.Connection = connection;

        adapter.SelectCommand = selectCommand;

        DataSet events = new DataSet();

        adapter.Fill(events);

        coeGridView.DataSource = events.Tables[0].DefaultView;
        coeGridView.DataBind();
       
    }
    protected void LoadPPDBButton_Click(object sender, EventArgs e)
    {
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                    Data Source=" + Server.MapPath("~/App_Data/2007COE_PPLoadData.xl_") + ";" +
                            "Extended Properties=\"Excel 8.0;HDR=YES;\"";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;

            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText =
                    "SELECT EventTitle, COE_ID, EventCategory, EventDescription, " +
                    "Comments, PrimaryLeadPortfolio, KeyPartners, PrimaryTargetSegment, SecondaryTargetSegment, " +
                    "PeopleExpected, ProposedDate_Time, EndDate_Time, Location, " +
                    "CreatedBy, ModifiedBy " +
                    "FROM [COE2007$] " +
                    "WHERE TypeofEvent IS NOT NULL";

                connection.Open();

                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        DateTime proposedStartDateTime = (DateTime)dr["ProposedDate_Time"];

                        DateTime proposedEndDateTime = (DateTime)dr["EndDate_Time"];

                        SqlDataSource1.UpdateParameters.Clear();
                        SqlDataSource1.UpdateParameters.Add("ProjectTitle", dr["EventTitle"].ToString());
                        SqlDataSource1.UpdateParameters.Add("COENumber", dr["COE_ID"].ToString());
                        SqlDataSource1.UpdateParameters.Add("EventCategoryID", dr["EventCategory"].ToString());
                        SqlDataSource1.UpdateParameters.Add("ProjectDescription", dr["EventDescription"].ToString());
                        SqlDataSource1.UpdateParameters.Add("ProjectNotes", dr["Comments"].ToString());
                        SqlDataSource1.UpdateParameters.Add("CreatedBy", dr["CreatedBy"].ToString());
                        SqlDataSource1.UpdateParameters.Add("ModifiedBy", dr["ModifiedBy"].ToString());
                        SqlDataSource1.UpdateParameters.Add("PrimaryBoardPortfolioID", dr["PrimaryLeadPortfolio"].ToString());
                        SqlDataSource1.UpdateParameters.Add("KeyPartners", dr["KeyPartners"].ToString());
                        SqlDataSource1.UpdateParameters.Add("PrimaryTargetSegmentID", dr["PrimaryTargetSegment"].ToString());
                        SqlDataSource1.UpdateParameters.Add("SecondaryTargetSegments", dr["SecondaryTargetSegment"].ToString());
                        SqlDataSource1.UpdateParameters.Add("ExpectedParticipants", dr["PeopleExpected"].ToString());
                        
                        SqlDataSource1.UpdateParameters.Add("ProposedStartDateParam", proposedStartDateTime.ToString());
                        SqlDataSource1.UpdateParameters.Add("ProposedStartTimeParam", proposedStartDateTime.ToString());
                        
                        SqlDataSource1.UpdateParameters.Add("ProposedEndDateParam", proposedEndDateTime.ToString());
                        SqlDataSource1.UpdateParameters.Add("ProposedEndTimeParam", proposedEndDateTime.ToString());
                        
                        SqlDataSource1.UpdateParameters.Add("ProposedJKLocationID", dr["Location"].ToString());
                        SqlDataSource1.Update();
                    }
                }

                connection.Close();
            }
        }
    }
}