Excel to SQL Server Express on c# button click

Question

On a button click I am trying to export a excel file to a SQL Server table. First I created my first button which would create the table upon a click:

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
    string query = "CREATE TABLE [dbo].[" + textBox1.Text + "](" + "[Code] [varchar] (7) NOT NULL," +
       "[Description] [varchar] (38) NOT NULL," + "[NDC] [varchar] (12) NULL, " +
       "[Supplier Code] [varchar] (38) NOT NULL," + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL,)";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

My next function is what should be the one which would grab the excel file through OpenFileDialog, select it and then upload the relevant contents to the table created from my button click above.This is the important section of the function:

private void button2_Click(object sender, EventArgs e)
{
    OpenFileDialog ope = new OpenFileDialog();
    ope.Filter = "Excel Files |*.xlsx;*.xlsm";

    if (ope.ShowDialog() == DialogResult.Cancel)
        return;

    FileStream stream = new FileStream(ope.FileName, FileMode.Open);
    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    DataSet result = excelReader.AsDataSet();

    DataClasses1DataContext conn = new DataClasses1DataContext();

    foreach (DataTable table in result.Tables)
    {
        foreach (DataRow dr in table.Rows)
        {
            test addtable = new test()
                {
                    test_id = Convert.ToString(result[0]);
                    test_name = Convert.ToString(dr[1]);
                };
            conn.tests.inInsertOnSubmit(addtable);
        }
    }
}

My issue is that it will work but currently it works only if I create a relationship with the table created from my first button click with my c# code. For eg. let's say I have column names entitled "test_id" and "test_name" from my SQL Server table. I have to then grab that table, put in in my C# code and match the column names. This will be very inconvenient as what I am aiming for is that of the user clicking the button to create a table and then exporting to that table each time they want to read data from some records. I want to eliminate interaction from the database admin. What I want to do it create a table and export an excel file to that same table without having to create some kind of relationship before hand within my code. Additional info: All the individual excel sheets will have the same set of columns each time. Stays the same. Don't know if that helps

Answer 1

I finally got it working, for anyone who might come across this. What I did was just change my file type to a .csv file instead of the excel extension. Excel was giving too much issues. Here is the below function that exports to a MSSQL table on a button click:

 private void button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True");
        string filepath = textBox2.Text; //"C:\\Users\\jdavis\\Desktop\\CRF_105402_New Port Maria Rx.csv";
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }

        while (!sr.EndOfStream)
        {
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = textBox1.Text;
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close();
    }