Updating DATAGRID row to save to SQL

Question

Hi Guys I am trying to understand how to save and edited row to the database

private void BudgetGrid_RowEditEnding(object sender,
    DataGridRowEditEndingEventArgs e)
{
    SqlCommand gridcmd = new SqlCommand();
    SqlConnection rwConn = null;
    rwConn = new SqlConnection("server=localhost;" +
    "Trusted_Connection=yes;" + "database=Production; " + "connection
    timeout=30");
    gridcmd.Connection = rwConn;
    rwConn.Open();
    //gridcmd.CommandText =
    //"SELECT Id, Name, Quantity, Rate, Time FROM Budget";
    gridcmd.CommandText =
    "UPDATE Budget SET Id = @id, Name = @Name, " +
    "Quantity = @Qty, Rate = @Rte WHERE Time = @Time";

    SqlDataAdapter gridda = new SqlDataAdapter(gridcmd);
    string strId = "@id".ToString();
    int intID;
    bool bintID = Int32.TryParse(strId, out intID);
    string strName = "@Name".ToString();
    string strQty = "@Qty".ToString();
    int intQty;
    bool bintQty = Int32.TryParse(strQty, out intQty);
    string strRte = "@Rte".ToString();
    int intRte;
    bool bintRte = Int32.TryParse(strRte, out intRte);
    string strTime = "@Time".ToString();
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@id", SqlDbType.Int));
    gridda.SelectCommand.Parameters["@id"].SqlValue = intID;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Name", SqlDbType.VarChar));
    gridda.SelectCommand.Parameters["@Name"].SqlValue = strName;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Qty", SqlDbType.Int));
    gridda.SelectCommand.Parameters["@Qty"].SqlValue = strQty;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Rte", SqlDbType.Int));
    gridda.SelectCommand.Parameters["@Rte"].SqlValue = strRte;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Time", SqlDbType.VarChar));
    gridda.SelectCommand.Parameters["@Time"].SqlValue = strTime;
    DataTable griddt = new DataTable("Budget");
    gridda.Fill(griddt);
    gridda.UpdateCommand =
        new SqlCommandBuilder(gridda).GetUpdateCommand();
    BudgetGrid.ItemsSource = griddt.DefaultView;
    gridda.Update(griddt);
    rwConn.Close();
}

it displays fine. I can edit its but when I click on the other tab it does not update it goes back to the original data.

Most of the code I have been going through its either out dated.. or not what I am looking for.

so here is the database

and here is the app

so basically if i hit tab to the next row. under the event BudgetGrid_RowEditEnding it should update the database.. but now its not.

Answer 1

Just copy below codes. I've created all the thing of you and tested successfully. Rather than the first way, I tried to let you go more popular way. Therefore, it took me time to adopt..

Hope this helps you !

SqlDataAdapter da;
DataTable dt;

    private void Window_Loaded(object sender, RoutedEventArgs e)
    {
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = yourConnectionString;
        Conn.Open();

        SqlCommand gridcomm = new SqlCommand();
        gridcomm.Connection = Conn;

        gridcomm.CommandText = "SELECT Id, Name, Quantity, Rate, Time FROM Budget";

        da = new SqlDataAdapter(gridcomm);

        SqlDataReader gridreader = gridcomm.ExecuteReader();
        while (gridreader.Read())
        {
        }
        gridreader.Close();

        dt= new DataTable("Budget");
        da.Fill(dt);

        dataGrid_Budget.ItemsSource = dt.DefaultView;

        Conn.Close();

    }

    private void dataGrid_Budget_RowEditEnding(object sender, System.Windows.Controls.DataGridRowEditEndingEventArgs e)
    {
        DataGridRow editedrow = e.Row;

        int row_index = (DataGrid)sender).ItemContainerGenerator.IndexFromContainer(editedrow);

        for (int k=0;k< 5;k++)
        {
            DataGridCell cell = GetCell(row_index, k);
            TextBlock tb = cell.Content as TextBlock;

            if (k==1)
            {
                dt.Rows[row_index][k] = tb.Text;
            }
            else if (k == 4)
            {
                if (tb.Text != "")
                {
                    dt.Rows[row_index][k] = Convert.ToDateTime(tb.Text);
                }
            }
            else
            {
                dt.Rows[row_index][k] = Convert.ToInt32(tb.Text);
            }
        }

        da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();

        da.Update(dt);
    }




    public DataGridCell GetCell(int row, int column)
    {
        DataGridRow rowContainer = GetRow(row);

        if (rowContainer != null)
        {
            DataGridCellsPresenter presenter = GetVisualChild<DataGridCellsPresenter>(rowContainer);

            DataGridCell cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(column);
            if (cell == null)
            {
                dataGrid_Budget.ScrollIntoView(rowContainer, dataGrid_Budget.Columns[column]);
                cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(column);
            }
            return cell;
        }
        return null;
    }

    public DataGridRow GetRow(int index)
    {
        DataGridRow row = (DataGridRow)dataGrid_Budget.ItemContainerGenerator.ContainerFromIndex(index);
        if (row == null)
        {
            dataGrid_Budget.UpdateLayout();
            dataGrid_Budget.ScrollIntoView(dataGrid_Budget.Items[index]);
            row = (DataGridRow)dataGrid_Budget.ItemContainerGenerator.ContainerFromIndex(index);
        }
        return row;
    }

    public static T GetVisualChild<T>(Visual parent) where T : Visual
    {
        T child = default(T);
        int numVisuals = VisualTreeHelper.GetChildrenCount(parent);
        for (int i = 0; i < numVisuals; i++)
        {
            Visual v = (Visual)VisualTreeHelper.GetChild(parent, i);
            child = v as T;
            if (child == null)
            {
                child = GetVisualChild<T>(v);
            }
            if (child != null)
            {
                break;
            }
        }
        return child;
    }