none
How to import data to SQL Database from datagridview which was imported from excel RRS feed

  • Question

  • How do I import the data from my datagridview that was import from excel into my SQL Database?

    public partial class ReadExcel : Form
        {
            public ReadExcel()
            {
                InitializeComponent();
            }
    
            public static DataTable ExcelToDataTable (string fileName)
            {
                using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        var result = reader.AsDataSet(new ExcelDataSetConfiguration()
                        {
                            UseColumnDataType = true,
                            ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
                            {
                                UseHeaderRow = true
                            }
                        });
                        DataTableCollection table = result.Tables;
                        DataTable resultTable = table["Sheet1"];
    
                        return resultTable;
                    }
                }
            }
    
            private void ReadExcel_Load(object sender, EventArgs e)
            {
    
            }
    
            private void btnOpen_Click(object sender, EventArgs e)
            {
                try
                {
                    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel 2003 Worksheet|*.xls|Excel 2007 Worksheet|*.xlsx", ValidateNames = true, Multiselect = false })
                    {
                        if (ofd.ShowDialog() == DialogResult.OK)
                        {
                            dataGridView1.DataSource = ExcelToDataTable(ofd.FileName);                        
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
    
            private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
            {
    
            }
        }
    }

    My datagridview:

          Date    |    EmployeeID1  |   EmployeeID2  |  EmployeeID3
    ---------------------------------------------------------------------
     18/1/2018            1                          2                       3
     19/1/2018            2                          3                       1
     20/1/2018            1                          2                       3



    How I want it to be like in my SQL Database:
    DutyID        |       Date      |          EmployeeID       |      Shift
    ------------------------------------------------------------------------------------
       1               18/1/2018           EmployeeID1                1
       2               18/1/2018               EmployeeID2                2
               3               18/1/2018               EmployeeID3                3
               4               19/1/2018               EmployeeID1                2
               5               19/1/2018               EmployeeID2                3
               6               19/1/2018               EmployeeID3                1
               7               20/1/2018               EmployeeID1                1
               8               20/1/2018               EmployeeID2                2
               9               20/1/2018               EmployeeID3                3 


    • Edited by envals Tuesday, January 16, 2018 6:13 AM
    Tuesday, January 16, 2018 5:49 AM

All replies

  • I have a better idea. You already have the Excel data stored in a DataTable. The DGV is simply rendering that data. Use the DataTable to import into your database, not the UI. The UI is for one thing and one thing only - the user to see stuff. All the data is already in the DataTable so there is no reason to concern yourself with the DGV for anything beyond the UI.

    Importing the data from a DataTable into a database is well documented in MSDN. If the table isn't in the format that the database needs then you'll need to massage it. Depending upon your database this can either be handled via adhoc commands or sproc calls.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, January 16, 2018 2:52 PM
    Moderator
  • Hi envals,

    Thank you for posting here.

    For your question, if you want to import data from Excel to DatagridView, you could refer to the code below.

     private void button1_Click(object sender, EventArgs e)
            {
                string name = "Sheet1";
                string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                @"New.xlsx" +
                                ";Extended Properties='Excel 12.0 XML;HDR=NO;';";
    
                OleDbConnection con = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
                con.Open();
    
                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                DataTable data = new DataTable();
                sda.Fill(data);
                dataGridView1.DataSource = data;
            }

    For more details, you would like to check the thread in StackOverflow.

    https://stackoverflow.com/questions/16293643/import-excel-to-datagridview

    If you want to import data from Datagridview to SQL database, you could refer to the thread below.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/eca619f0-7d35-42b4-8fb5-431ff6260977/importing-data-from-datagridview-to-sql-server?forum=csharpgeneral

    If the format of the data is not same with Datagridview, you need to change teh format.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 6:14 AM
    Moderator
  • Hi envals,

    I make  a simple example about how to import data from datagridview to sql database like what you shows for us.

     private void button1_Click(object sender, EventArgs e)
            {
                dataGridView1.AllowUserToAddRows = false;
                string name = "Sheet2";         
                string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"New.xlsx" + "; Extended Properties = 'Excel 12.0;HDR=YES;IMEX=1;';";
    
                OleDbConnection con = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con);
                con.Open();
    
                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                DataTable data = new DataTable();
                sda.Fill(data);
                dataGridView1.DataSource = data;
    
            }
    
            private void button2_Click(object sender, EventArgs e)
            {         
    
                foreach (DataGridViewRow item in dataGridView1.Rows)
                {
                    List<string> list = new List<string>();
                    DateTime data = DateTime.ParseExact(item.Cells["Date"].Value.ToString(), "dd/M/yyyy", CultureInfo.InvariantCulture);
                    //Convert.ToDateTime(item.Cells["Date"].Value);
                    string empcell1 = item.Cells["EmployeeID1"].Value.ToString();
                    string empcell2 = item.Cells["EmployeeID2"].Value.ToString();
                    string empcell3 = item.Cells["EmployeeID3"].Value.ToString();
    
                    list.Add(empcell1);
                    list.Add(empcell2);
                    list.Add(empcell3);
                    string name = "EmployeeID";
                    string emp1 = dataGridView1.Columns[1].Name;
                    string emp2 = dataGridView1.Columns[2].Name;
                    string emp3 = dataGridView1.Columns[3].Name;
                    for (int i = 1; i <= list.Count; i++)
                    {
                        insertdate(data, name + i, list[i - 1]);
                    }
                }
                MessageBox.Show("Finished");
              
            }
            private void insertdate(DateTime date, string EmployeeID, string Shift)
            {
                string StrQuery;
                string ConnString = @"Data Source=(LOCALDB)\MSSQLLOCALDB;Integrated Security=True";
    
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    using (SqlCommand comm = new SqlCommand())
                    {
                        comm.Connection = conn;
                        conn.Open();
    
                        comm.Parameters.Clear();
                        StrQuery = @"INSERT INTO Test1 (Date,EmployeeID,Shift) VALUES (@Date,@EmployeeID,@Shift)";
    
                        comm.CommandText = StrQuery;
                        comm.Parameters.AddWithValue("@Date", date);
                        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID);
                        comm.Parameters.AddWithValue("@Shift", Shift);
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                }
    
            }

    The value of DutyID generate automatically.  I test for many times, hence it shows like in the picture. When you do in first time, it will be 1,2,3,4,5...

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 30, 2018 1:35 AM
    Moderator