DataSet not updated with new row inserted in C#

Question

i am trying to insert new row in Emp table in c# (disconnected mode), the new row successfully inserted but the dataset not updated, so when i search for the new row, i don't find it, but when i search for an old row, i find it.

the insertBTN button used to insert new row

the searchByID button used to search for row by its ID

  public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private SqlConnection conn;
    private SqlDataAdapter adapter;
    private DataSet ds;

    private void Form1_Load(object sender, EventArgs e)
    {
        conn = new SqlConnection(@"data source=(local);initial catalog =Test;integrated security = true");
        conn.Open();
        adapter = new SqlDataAdapter("select * from Emp",conn);
        ds = new DataSet();
        adapter.Fill(ds,"Emp");
    }

    private void insertBTN_Click(object sender, EventArgs e)
    {
        try
        {
            int id = int.Parse(textBox1.Text);
            string name = textBox2.Text;
            string address = textBox3.Text;
            int age = int.Parse(textBox4.Text);
            int salary = int.Parse(textBox5.Text);

            SqlCommand command = new SqlCommand("Insert into Emp values(" + id + ",'" + name + "','" + address + "'," + age + "," + salary + ")", conn);
            command.ExecuteNonQuery();


           adapter.Update(ds,"Emp");
            MessageBox.Show("Employee added successfully");
        }
        catch (Exception exception)
        {
            MessageBox.Show(exception.Message);
        }
    }

    private void searchByID_Click(object sender, EventArgs e)
    {
        try
        {
            int id = int.Parse(textBox1.Text);
            foreach (DataRow row in ds.Tables["Emp"].Rows)
            {
                if (Convert.ToInt32(row["id"]) == id)
                {
                    textBox2.Text = Convert.ToString(row["name"]);
                    textBox3.Text = Convert.ToString(row["address"]);
                    textBox4.Text = Convert.ToString(row["age"]);
                    textBox5.Text = Convert.ToString(row["salary"]);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

}

Answer 1

The Update method of the DataAdapter doesn't read again but tries to execute the INSERT, DELETE and UPDATE commands derived from the original SELECT statement against the Rows in the DataTable that have their RowState changed

So, if you want to use the Update method of the adapter you could write

private void insertBTN_Click(object sender, EventArgs e)
{
    try
    {
        DataRow row = ds.Tables["emp"].NewRow();
        row.SetField<int>("id", int.Parse(textBox1.Text));
        row.SetField<string>("name", textBox2.Text));
        row.SetField<string>("address", textBox3.Text));
        row.SetField<int>("age", int.Parse(textBox4.Text));
        row.SetField<int>("salary", int.Parse(textBox5.Text));
        ds.Tables["emp"].Rows.Add(row);
        adapter.Update(ds,"Emp");
        MessageBox.Show("Employee added successfully");
    }
    catch (Exception exception)
    {
        MessageBox.Show(exception.Message);
    }
}

At this point your DataSet contains the added row because you have added it manually and then passed everything to the Update method to write it to the database.

However, keep in mind that the Update method to work requires certain conditions to be present. You could read them in the DbDataAdapter.Update page on MSDN.
Mainly you need to have retrieved the primary key of the table, do not have more than one table joined together and you have used the DbCommandBuilder object to create the required commands (Again the example in the MSDN page explain it)

Not related to your question, but you could change your search method and avoid writing a loop to search for the ID

private void searchByID_Click(object sender, EventArgs e)
{
    try
    {
        int id = int.Parse(textBox1.Text);
        DataRow[] foundList = ds.Tables["Emp"].Select("Id = " + id);
        if(foundList.Length > 0)
        {
            textBox2.Text = foundList[0].Field<string>("name");
            textBox3.Text = foundList[0].Field<string>("address");
            textBox4.Text = foundList[0].Field<int>("age").ToString();
            textBox5.Text = foundList[0].Field<int>("salary").ToString();

        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }