@page "/file-upload-1"
@using System 
@using System.IO
@using Microsoft.AspNetCore.Hosting
@using Microsoft.Extensions.Logging
@inject ILogger<FileUpload1> Logger
@inject IWebHostEnvironment Environment
@inject IConfiguration Configuration
@using System.Data.OleDb;
@using System.Data.SqlClient;
@using System.Data
<h3>Upload Files</h3>

<p>
    <label>
        Max file size:
        <input type="number" @bind="maxFileSize" />
    </label>
</p>

<p>
    <label>
        Max allowed files:
        <input type="number" @bind="maxAllowedFiles" />
    </label>
</p>

<p>
    <label>
        Upload up to @maxAllowedFiles of up to @maxFileSize bytes:
        <InputFile OnChange="@LoadFiles" multiple />
    </label>
</p>

@if (isLoading)
{
    <p>Uploading...</p>
}
else
{
    <ul>
        @foreach (var file in loadedFiles)
        {
            <li>
                <ul>
                    <li>Name: @file.Name</li>
                    <li>Last modified: @file.LastModified.ToString()</li>
                    <li>Size (bytes): @file.Size</li>
                    <li>Content type: @file.ContentType</li>
                </ul>
            </li>
        }
    </ul>
}

@code {
    private List<IBrowserFile> loadedFiles = new();
    private long maxFileSize = 1024 * 15;
    private int maxAllowedFiles = 3;
    private bool isLoading;
 

    private async Task LoadFiles(InputFileChangeEventArgs e)
    {
        isLoading = true;
        loadedFiles.Clear();
        string path = "";
        foreach (var file in e.GetMultipleFiles(maxAllowedFiles))
        {
            try
            {
                loadedFiles.Add(file);

                var trustedFileNameForFileStorage = file.Name;
                path = Path.Combine(Environment.ContentRootPath,
                      Environment.EnvironmentName, "unsafe_uploads",
                      trustedFileNameForFileStorage);

                await using FileStream fs = new(path, FileMode.Create);
                await file.OpenReadStream(maxFileSize).CopyToAsync(fs);
                string conString = Configuration.GetConnectionString("ExcelConString");
                DataTable dt = new DataTable();
                conString = string.Format(conString, path);
 
            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;
 
                        //Get the name of First Sheet.
                        connExcel.Open();
                        DataTable dtExcelSchema;
                        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        connExcel.Close();
 
                        //Read Data from First Sheet.
                        connExcel.Open();
                        cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                        odaExcel.SelectCommand = cmdExcel;
                        odaExcel.Fill(dt);
                        connExcel.Close();
                    }
                }
            }
             //Insert the Data read from the Excel file to Database Table.
            conString = this.Configuration.GetConnectionString("constr");
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name.
                    sqlBulkCopy.DestinationTableName = "ExcelTable";
 
            
                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }


            }
            catch (Exception ex)
            {
                Logger.LogError("File: {Filename} Error: {Error}", 
                    file.Name, ex.Message);
            }
        }

        isLoading = false;
    }
}