SqlDataAdapter Update Method

Introduction

In this article, we will learn about SqlDataAdapter Update Method in ADO.NET

SqlDataAdapter Update Method

1. SqlDataAdapter is a part of the ADO.NET Data Provider.
2. It uses the Fill method to retrieve data from the data sources and fill it in DataSet.
3. The UpdateCommand of the SqlDataAdapter Object updates the database with the data modifications made on a DataSet object.

Diagram

Steps

1. Create an update query string.
2. Create a connection object.
3. Create a SqlDataAdapter object accompanying the query string and connection object.
4. Use the Update command of the SqlDataAdapter object to execute the update query.

Example

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("connetionString");
    string qry = "SELECT * FROM SOMETABLE";
    SqlDataAdapter da = new SqlDataAdapter(qry,con);           
    //Fill the DataSet
    DataSet ds = new DataSet();
    da.Fill(ds, "SomeTable");
    //Update a row in DataSet Table
    DataTable dt = ds.Tables["SomeTable"];
    dt.Rows[0]["SomeColumn"] = "xyz";

    string sql = "update sometable set somecolumn = 10 where ...";
    SqlDataAdapter adapter = new SqlDataAdapter();           
    try
       {
        connection.Open();
        SqlCommand cmd = new SqlCommand(sql, con);
        //select the update command
        adapter.UpdateCommand=cmd;
        //update the data source
        adapter.Update(ds,"SomeTable");
        MessageBox.Show ("DataBase updated !! ");
    }
    catch (Exception ex)
    {
        connection.Close();
    }
}

The above example code is for the C# event handler for the click event of a button control. You can use SQL queries, table names, and messages as per your requirement. When the button is clicked, it performs the following actions:

1. It creates a new SqlConnection object and opens a connection to the database using a connection string.
2. It creates a SqlDataAdapter object and executes a SELECT query on the database to retrieve data from a table named "SOMETABLE".
3. It creates a DataSet object and fills it with the data from the "SOMETABLE" table using the Fill method of the SqlDataAdapter.
4. It retrieves the DataTable object from the DataSet and updates the value of a column named "SomeColumn" in the first row of the table.
5. It creates a SqlDataAdapter object and a SqlCommand object to execute an UPDATE query on the database to update a row in the "SOMETABLE" table.
6. It assigns the SqlCommand object as the UpdateCommand of the SqlDataAdapter and calls the Update method of the SqlDataAdapter to apply the changes made to the DataTable to the database.
7. If the update is successful, it displays a message box with the message "DataBase updated !!"

Conclusion

In this article, we learned about SqlDataAdapter Update Method in ADO.NET