how to update multiple based table from datagridview in wpf

Question

public DataTable FillDataGrid()
{
    string CmdString = string.Empty;
    using (SqlCeConnection con = new SqlCeConnection(ConString))
    {
        CmdString = "SELECT categories.categories_id as ID, categories_description.categories_name as name,categories.categories_image as Image,categories.parent_id as parentId,categories.sort_order as sortOrder,categories.date_added as dateAdded,categories.last_modified as lastModified FROM categories INNER JOIN categories_description ON categories.categories_id=categories_description.categories_id where categories_description.language_id=1";

        SqlCeCommand cmd = new SqlCeCommand(CmdString, con);
        DataTable dt = new DataTable("categories");
        SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
        adapter.Fill(dt);

        return dt;
    }   
}

public void FillDataGrid()
{
    DatabaseCore db = new DatabaseCore();
    DataTable dt = db.FillDataGrid();
    show_query.ItemsSource = dt.DefaultView;
}

i'm using sqlserverCe as database and visual studio 2012 (windows presentation forms)

i'm using two table to fill datagrid view and it is working properly.

i need to update databse from datagridview i searched about it but not getting a proper solution for my problem.. most of the search result for single table updation

i used SqlCeCommandBuilder but it gives error that it is not applicable for multiple based table suggest me something for doing this

Answer 1

CommandBuilder is used only for simple SELECT statements i.e for single tables, It wont update when you have used JOIN...

Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited. (MSDN)

You will have to use a parameterized query.

Here is an example of a very simple parameterized query...

string value = DataGrid.Rows[0].Cells["ID"].Value);; // Takes the value from TextBox1
Cmd = new SqlCommand("DELETE FROM Table WHERE CountryMasterId = @ID", Con); // makes a new SqlCommand object for delete query and `@ID` is the parameter for passing the value...
Cmd.Parameters.AddWithValue("@ID", value);

Now you can extend this and set your use your values from data grid to update tables. Alternatively You can use stored procedure. You will just have to pass the parameters and then you can use multiple update statements in the stored procedure to update multiple tables...

These links will be helpful for you...

• Link1
• Link2