Saving excel file in application folder and uploading the content to SQL Server using windows forms and C#

Question

I'm working on a simple application which needs to browse an excel file and upload it to SQL server and also copy the file to application folder. By using below code I'm able to do it in web. But there is no alternative for FileUpload tool in winforms. Below is the code I'm trying to replicate in windows forms.

protected void Upload(object sender, EventArgs e)
{
    //Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);

    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;

    }
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Salary",typeof(decimal)) });

        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            oda.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

                sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}

That topic is related with copying files , my question is how to copy the excel file and save the same to SQL server database. — Jobin Joseph
– Jobin Joseph, Commented Jul 10, 2016 at 15:01

score 2 · Accepted Answer · 2016-07-10 15:34:10Z

2

The replacement of FileUpload in WinForm is OpenFileDialog:

string excelPath = "";
using(OpenFileDialog ofd = new OpenFileDialog())
{
  ofd.Filter = "Excel(.xls)|*.xls|Excel(.xlsx)|*.xlsx|All Files (*.*)|*.*";
  ofd.Multiselect = false;
  var result = ofd.ShowDialog();
  if (result == DialogResult.OK)
  {
    excelPath = ofd.FileName;
  }
}
//... continue with your sql code

The code for uploading to sql server is still the same, you can use it also in winform. From the sample above, "excelPath" is in my sample "ofd.FileName"

Collectives™ on Stack Overflow

Saving excel file in application folder and uploading the content to SQL Server using windows forms and C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related