none
C# Commit deleted Rows from DataGridView to Database Source - SQL RRS feed

  • Question

  • Hi,

    I have succeeded to get the databse records inot my DataGridView , and succeeded to delete every row which is checked ( First column is DataGridViewCheckBox). Now I want , and after deleting for example all rows, and getting an empty DataGridView , want to update that in SQL Database, and delete all rows which were deleted in DataGridView , and delete them from SQL Database.


           public void DeleteAllCheckedRows()
            {
                //delete all the checked rows
                listOfCheckedRows.Clear();
                foreach (DataGridViewRow row in this.EmployeesDataGridView.Rows)
                {
                    DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;
                    if (cell != null && row.IsNewRow != true && (bool)cell.FormattedValue)
                    {
                        int index = row.Index;
                        listOfCheckedRows.Add(index);
                    }
                }

                for (int j = listOfCheckedRows.Count - 1; j >= 0; j--)
                {
                    DataGridViewRow theRowToDelete = EmployeesDataGridView.Rows[Convert.ToInt32(listOfCheckedRows[j])];
                    EmployeesDataGridView.Rows.Remove(theRowToDelete);
                    listOfCheckedRows.RemoveAt(j);
                }

                listOfCheckedRows.Clear();
                m_NumberOfSelectedCheckBoxes = 0;

             //Deleting Checked CheckBoxes , rows was succeeded.

     

             //Now trying to commit this changes to SQL Database , and updating the SQL table ( I am not succeeding here)
                DataSet ds = new DataSet();
                OpenConnection();
                string tableName = this.GetDatabaseEmployeesDataSet().TableEmployee.TableName.ToString();
                string sql = @"SELECT * FROM " + tableName;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(sql, conn);
                da.Fill(ds, tableName);
                DataTable dt = ds.Tables[tableName];
                this.EmployeesDataGridView.BindingContext[dt].EndCurrentEdit();
                da.Update(dt);

                //Refreshing DataGridView after changes
                EmployeesDataGridView.Show();
            }


    Please help me to update teh SQL Table after deleting or removing the checked rows, and delete them also from teh SQL table.

    Thank you
    Wael

    Wednesday, June 3, 2009 9:45 PM

All replies

  • You have only provided the adapter with a SelectCommand.  You will need a DeleteCommand.

    A convenient way to set this up is to use SqlCommandBuilder to create the command for you.  See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx
    Friday, June 5, 2009 8:29 PM
  • Hi BinaryCode,

    Thank you for the help, but It is still not working.

    I have added

                SqlCommandBuilder Builder = new SqlCommandBuilder(adapter);
                adapter.DeleteCommand = Builder.GetDeleteCommand();


    But I still cannot understand how the v will know which checkboxes were selected in order to delete them??

    Thank  you
    Saturday, June 6, 2009 4:10 PM
  • When you removed the row from the grid, the grid ultimately performed a Delete operation on the DataRow.  This does not cause the DataSet to forget about the DataRow.  On the contrary, it transitions the row to DataRowState.Deleted but leaves it in the database.  The SqlDataAdapter processes a row in the DataRowState.Deleted state by issuing a delete command to the database.

    You can loop over all rows in the DataTable (foreach var row in dataSet.dataTable.Rows) to observe that some of them will have their DataRow.DataRowState property as DataRowState.Deleted.

    For some further information on how row states work, see my post that is marked as the answer in the following thread: http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/65eea503-f709-4f50-9eac-9492bd1813bd

    Saturday, June 6, 2009 4:34 PM
  • Ok - I iwll have a look on that , and back soon if there is still a problem
    Saturday, June 6, 2009 4:36 PM
  • Heloo BinaryCode,

    Can you tell me what wrong with this method. I can delete the checked rows from the DataGridView , but even by using the commandBuilder - It is not deleted from teh databse.


           public void DeleteAllCheckedRows()
            {
                //delete all the checked rows
                listOfCheckedRows.Clear();
                foreach (DataGridViewRow row in this.EmployeesDataGridView.Rows)
                {
                    DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;
                    if (cell != null && row.IsNewRow != true && (bool)cell.FormattedValue)
                    {
                        int index = row.Index;
                        listOfCheckedRows.Add(index);
                    }
                }

                for (int j = listOfCheckedRows.Count - 1; j >= 0; j--)
                {
                    DataGridViewRow theRowToDelete = EmployeesDataGridView.Rows[Convert.ToInt32(listOfCheckedRows[j])];

                    int rowIndex = Convert.ToInt32(listOfCheckedRows[j]);
                    //DeleteRowsfromDatabase(rowIndex);

                    EmployeesDataGridView.Rows.Remove(theRowToDelete);
                    listOfCheckedRows.RemoveAt(j);

                }

                listOfCheckedRows.Clear();
                m_NumberOfSelectedCheckBoxes = 0;           


                //Now trying to commit this changes to SQL Database , and updating the SQL table
                string tableName = GetDatabaseEmployeesDataSet().TableEmployee.ToString();
                //Creating empty dataset
                DataSet ds = new DataSet();
                //set up the connection string and connection object
                OpenConnection();
                //Setup select comand string
                string sqlSelect = "SELECT * FROM " + tableName;
                //set up the connection object using the conn string
                SqlConnection conn = new SqlConnection(Properties.Settings.Default.EmployeeDatabaseConnectionString);
                //set up the data adapter using the select statement and the connection object
                SqlDataAdapter adapter = new SqlDataAdapter(sqlSelect, conn);
                //fill the dataset with a new datatable of all the results
                adapter.Fill(ds);
                //now, let "Table" point to the datatable with our results
                //DataTable datatable = ds.Tables["TempTable"];
                //and you can use the table as needed
                //EmployeesDataGridView.DataSource = datatable;


                //Now, Table is pointing to a DataTable that contains all the data retrieved from your query.
                //You can also modify this data, and easily reflect your changes back to the database
                //set up the command builder using the data adapter
                SqlCommandBuilder Builder = new SqlCommandBuilder(adapter);
                //use the builder to create update, insert, and delete commands
                //adapter.UpdateCommand = Builder.GetUpdateCommand();
                //adapter.InsertCommand = Builder.GetInsertCommand();
                adapter.DeleteCommand = Builder.GetDeleteCommand();

                //commit changes of table "TempTable" in the dataset back to the database 
                adapter.Update(ds);

                DataColumn column = new DataColumn();
                column = ds.Tables[0].Columns["ID"];
                DataColumn[] keys = new DataColumn[1];
                keys[0] = column;
                ds.Tables[0].PrimaryKey = keys;

                //Refreshing DataGridView after changes
                EmployeesDataGridView.Show();


            }

    Saturday, June 6, 2009 4:57 PM
  • Why are you creating a new DataSet and re-filling it with the rows?  Generally, you call adapter.Update on the same DataSet that the grid was originally bound to.  (The one that would now contain the rows in the DataRowState.Deleted).  You probably dragged this DataSet on your form in the designer, so you could use that name.  Otherwise, put the DataSet in a class-level member variable when you first create it.



    Saturday, June 6, 2009 5:14 PM
  • Actually I am using Visual Studion express 2008 , sql 2008 express , and Management studion 2008 express.
    I have dragged DataGridView to my form , added the colums accroding to the database that I have created.

    The Visual environment creates

      1. employeeDatabasedataset
      2. tableEmployeeBindingsource
      3. tableEmployeeTableDapter
     

    In the Load method teh visual creates automatically teh Fill method , but this did not work for me , and I do not know why , so I have  created my own FillDataIntoDataGrid(), whcih I call from teh load method.


            private void ClientsDataGridForm_Load(object sender, EventArgs e)
            {
                //this.tableEmployeeTableAdapter.Fill(this.employeeDatabaseDataSet.TableEmployee); // Did not work at all
                FillDataIntoDataGrid();
           }


           public void FillDataIntoDataGrid()
            {
                // Open connection
                using (conn
                    //using (SqlConnection c = new SqlConnection(Properties.Settings.Default.DatabaseEmployeesConnectionString)
                )
                {
                    conn.Open();
                    // 2
                    // Create new DataAdapter
                    using (SqlDataAdapter a = new SqlDataAdapter(
                        "SELECT * FROM TableEmployee" , conn))
                    {
                        // 3
                        // Use DataAdapter to fill DataTable
                        DataTable t = new DataTable();
                        a.Fill(t);
                        // 4
                        // Render data onto the screen
                        EmployeesDataGridView.DataSource = t;
                    }
                }
            }



    Also, I have the following methods , that I have created :


           public BindingSource GetEmployeesBindingSource()
            {
                return tableEmployeeBindingSource;
            }

            public EmployeeDatabaseDataSetTableAdapters.TableEmployeeTableAdapter GetEmployeestableAdapter()
            {
                return tableEmployeeTableAdapter;
            }

            public EmployeeDatabaseDataSet GetDatabaseEmployeesDataSet()
            {
                return employeeDatabaseDataSet;
            }


    Can yuou give me your own email so I can contact you please , I may send you zip file so you can take alook on that. I am new to C# , and got lost :-)

    Thank you
    Wael

    Saturday, June 6, 2009 5:33 PM