update database using SqlDataAdapter in C#

Question

I have below code to update my database table when button is clicked but it doesn't work.

protected void Button_Click(object sender, EventArgs e)
{
    HasinReservation.Entities.Db.Transaction dt = new Transaction();
    SqlConnection connection = new SqlConnection(
        @"Data Source=192.x.x.x\Sql2008;Initial Catalog=GardeshgariKish;User ID=cms;Password=xxxxx;MultipleActiveResultSets=True;Application Name=EntityFramework");
    connection.Open();
    SqlCommand sqlCmd = new SqlCommand(
        "Update Transaction SET IsCancelled = 1 WHERE BarCodeNumber = @Value1", connection);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
    string barcode = dgvData.Rows[0].Cells[12].Text;
    sqlCmd.Parameters.AddWithValue("Value1", barcode);
    connection.Close();
}

don't see any part of EF here. SqlDataAdapter is part of ADO.NET, used in old database-driven applications. It's used to connect between the disconnected parts (DataTable, DataSet) and the database. If you don't store your data in DataTable or DataSet, then don't use SqlDataAdapter. SqlCommand is all you need. — Hopeless
– Hopeless, Commented Sep 20, 2015 at 12:22

Chris Schaller · Accepted Answer · 2015-09-20 12:56:59Z

I am troubled by your implementation of Entity Framework but then not using the framework for what it was designed...

You have configured your data adapter and the command, and even opened the connection... but you have not actually executed the command.

sqlCmd.ExecuteNonQuery();

I understand that your actual business logic may have been replaced with this simple CRUD operation, but the main reason that we use the Entity Framework is to avoid writing any T-SQL in our business logic. Why didn't you use the framework to commit the change:

protected void Button3_Click(object sender, EventArgs e)
{
    // cancel the selected transaction
    string selectedBarcode = dgvData.Rows[0].Cells[12].Text;
    using(var dataContext = new HasinReservation.Entities.Db())
    {
        var transaction = dataContext.Transaction.Single(t => t.Barcode == selectedBarcode);
        transaction.IsCancelled = true;
        dataContext.SaveChanges();
    }
}

This in itself may not be a great solution but it uses the framework to do exactly what you attempted to do manually.

David · Accepted Answer · 2015-09-20 11:48:09Z

1

Why are you trying to use a SqlDataAdapter to execute an UPDATE statement? When constructing a SqlDataAdapter with a SqlCommand object, that command object represents the SELECT command for the adapter. An UPDATE statement doesn't select anything, and a SELECT command doesn't update anything.

Get rid of the SqlDataAdapter entirely and just execute the command:

sqlCmd.ExecuteNonQuery();

You'll probably also want to add some error handling so exceptions don't reach the UI (and to ensure the connection is properly closed on error conditions). You also don't seem to be doing anything with that Transaction object, so you can probably get rid of that too.

answered Sep 20, 2015 at 11:48

David

221k42 gold badges245 silver badges337 bronze badges

1 Comment

saeed garmsiri Over a year ago

dear David thanks for your concern, i forgot that i am using model builder entity framework not data set.

Collectives™ on Stack Overflow

update database using SqlDataAdapter in C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related