none
Updating Database from Datagridview in C#

    Question

  • Can anyone guide me! I am displaying data into data grid view from dataset/datatable. Now, I want to update data (which is display in datagridview) into SQL Database. How should I do ? I am using C#.NET and creating windows application.

    Rushabh

    • Moved by CoolDadTxMVP Wednesday, April 04, 2012 5:11 PM Winforms related (From:Visual C# General)
    Wednesday, April 04, 2012 4:15 PM

Answers

All replies

  • You can use ODBC for that, like this for example

     // using System.Data.Odbc;<--- put this on code.
            
            // Odbc conection and command;
            private OdbcConnection conn;
            private OdbcCommand command = new OdbcCommand(); 
            
            public void UpdateData()
            {
                command.CommandText = string.Format(@"update clients set name = '{0}',where cod = '{1}'", param1,param2);
                command.Connection = conn;
                conn.Open();
                command.ExecuteNonQuery();
                conn.Close();
            }

    Hope this helps

    Wednesday, April 04, 2012 4:38 PM
  • Use SqlDataAdapter class to update each row of dgv to database. Check here how this shouldbe done.

    Here is my example that did last year:

            public void DAL_UpdateStudentsTable(DataTable table)
            {
                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = @"UPDATE Students SET " +
                                           "StudentID = @id, " +
                                           "FirstName = @first, " +
                                           "LastName = @last, " +
                                           "Birthday = @birthday, " +
                                           "PersonalNo = @personal " +
                                           "WHERE StudentID = @oldId";
                        cmd.Parameters.Add("@id", SqlDbType.Int, 5, "StudentID");
                        cmd.Parameters.Add("@first", SqlDbType.VarChar, 50, "FirstName");
                        cmd.Parameters.Add("@last", SqlDbType.VarChar, 50, "LastName");
                        cmd.Parameters.Add("@birthday", SqlDbType.DateTime, 1, "Birthday");
                        cmd.Parameters.Add("@personal", SqlDbType.VarChar, 50, "PersonalNo");
                        SqlParameter param = cmd.Parameters.Add("@oldId", SqlDbType.Int, 5, "StudentID");
                        param.SourceVersion = DataRowVersion.Original;
                        cmd.Connection = sqlConn;
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            da.UpdateCommand = cmd;
                            da.Update(table);
                        }
                    }
                }
            }

    If your DGV is databound to dataTable, simply pass datatable to upper method, and do as I did (just rename column names).


    Mitja

    Wednesday, April 04, 2012 4:45 PM
  • My DGV is not data bound to dataTable. Do, I need to convert DGV into datatable and if so then how should I convert DGV into datatable. I have 14columns  and 5 Rows in DGV . 


    Rushabh

    Wednesday, April 04, 2012 4:56 PM
  • My DGV is not data bound to dataTable. Do, I need to convert DGV into datatable and if so then how should I convert DGV into datatable. I have 14columns  and 5 Rows in DGV . 

    Your better bind it. It will not harm, but it can only do good. You see now, when you wanna do updates, you can simply use binding source.

    Anyway, you can still pass a DataGridView class instead of DataTable if you wlike to, but I strongly suggest you to use DataTable for data source of DGV from now on. You will see there is many, many benefits in it.


    Mitja

    Wednesday, April 04, 2012 5:05 PM
  • My DGV is not data bound to dataTable. Do, I need to convert DGV into datatable and if so then how should I convert DGV into datatable. I have 14columns  and 5 Rows in DGV . 

    Your better bind it. It will not harm, but it can only do good. You see now, when you wanna do updates, you can simply use binding source.

    Anyway, you can still pass a DataGridView class instead of DataTable if you wlike to, but I strongly suggest you to use DataTable for data source of DGV from now on. You will see there is many, many benefits in it.


    Mitja

    I was think Mitja, its better fill the DGV with type DataTable, instead other ways? If so, he dont need to make this conversion, but i dont know what the advantages or disadvantages.
    Wednesday, April 04, 2012 5:07 PM
  • Best way is:

    1. fill datatable (from database, or from any other source)

    2. bind datatable to dgv (as binding source)

    This means all changes in dgv will automatically reflect in dataTable as well. So you can then take dataTable for further tasks (like updating back to database) or some other calculations if needed, or ...).

    DataTable is specialy made for this kind of things, so I defenatelly suggest to use it.


    Mitja

    Wednesday, April 04, 2012 5:10 PM
  • Ok. Let me explain you what's my final outcome or design. I have one dataset with datatable. when user enter values say- item no it will search into dataset and display into datagridview. Datagridview can have many rows. Now user want to update information of particular column then it will type in column name and new value. Depending on column name it will update database.

    Now, i have data in datagridview which is not bound to any datatable then how should i update datagridview/database.


    Rushabh

    Wednesday, April 04, 2012 5:22 PM
  • Do you have any unique column in dgv? So you can be sure to do an update on the correct row in datatable.

    Now user want to update information of particular column then it will type in column name and new value. Depending on column name it will update database.

    Are you sure, that you dont think Row, not column?


    Mitja

    Wednesday, April 04, 2012 5:53 PM
  • I do have a unique column and I want to do column wise update.

    Rushabh

    Wednesday, April 04, 2012 6:22 PM
  • Hi guy,

     After I had researched this topic on internet , I found out there are several  threads have mentioned this topic as follows:

    updating datagridview

     

    http://social.msdn.microsoft.com/Forums/nl-BE/winformsdatacontrols/thread/ac8417c9-e928-4549-825e-dbe66e5ee353

     

    Insert / Deleting / Updating Records into Database using Datagridview in visual C#

     

    http://social.msdn.microsoft.com/Forums/en/csharplanguage/thread/8db841fc-ffa7-4519-b6f5-d054c7190948

     

    Sincerely,

    Jason Wang


    Jason Wang [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by ShahRushabh Tuesday, April 10, 2012 7:56 PM
    Friday, April 06, 2012 1:23 AM
    Moderator