views:

1138

answers:

1

Sorry for the probably stupid question. Since I found nothing about it on the internets, its probably totally obvious and I'm just to blind to see?!

I'm trying to update a table in a database from a dataset via DataAdapter.Update(dataset)

But there is no possiblity to set the connection, the DA should use.

Where does the DA know how to connect to the DB? Or do I misunderstand the concept of the dataadapter?

my current code is like:

protected DataSet UpdateDataSet(DataSet ds)
{
   DataSet dsChanges = new DataSet();
   SqlDataAdapter da = new SqlDataAdapter();

   dsChanges = ds.GetChanges();

   //Update DataSet
   da.Update(dsChanges);

   ds.Merge(dsChanges);
   return ds;
}

I just wrote this and became suspicious how (or if) it works... I havent tested it so far, since I gotta write some other code before I can test it properly

Thank you ppl, StackOVerflow FTW!

+3  A: 

A SqlDataAdapter needs to take in a SqlCommand object, which has a SqlConnection object tied to it. That's pretty much how the hierarchy breaks down.

As for how you go about doing that, there are options for passing them into the constructor, as well as setting the various properties after construction.

Here's an msdn article with examples of selecting, inserting, updating, and deleting.

FTA:

public static SqlDataAdapter CreateCustomerAdapter(
    SqlConnection connection)
{
    SqlDataAdapter adapter = new SqlDataAdapter();

    // Create the SelectCommand.
    SqlCommand command = new SqlCommand("SELECT * FROM Customers " +
        "WHERE Country = @Country AND City = @City", connection);

    // Add the parameters for the SelectCommand.
    command.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
    command.Parameters.Add("@City", SqlDbType.NVarChar, 15);

    adapter.SelectCommand = command;

    // Create the InsertCommand.
    command = new SqlCommand(
        "INSERT INTO Customers (CustomerID, CompanyName) " +
        "VALUES (@CustomerID, @CompanyName)", connection);

    // Add the parameters for the InsertCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 
        40, "CompanyName");

    adapter.InsertCommand = command;

    // Create the UpdateCommand.
    command = new SqlCommand(
        "UPDATE Customers SET CustomerID = @CustomerID, " + 
        "CompanyName = @CompanyName " +
        "WHERE CustomerID = @oldCustomerID", connection);

    // Add the parameters for the UpdateCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 
        40, "CompanyName");
    SqlParameter parameter = command.Parameters.Add(
        "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    adapter.UpdateCommand = command;

    // Create the DeleteCommand.
    command = new SqlCommand(
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

    // Add the parameters for the DeleteCommand.
    parameter = command.Parameters.Add(
        "@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    adapter.DeleteCommand = command;

    return adapter;
}
Joseph
ok, thats what I do when I try to retrieve data. I pass a command object. But the update method of DataAdapter does only accept a DataSet (and other stuff, but no command object)
MAD9
@MAD9 The SqlDataAdapter also has an UpdateCommand property that you can assign with a SqlCommand object to suite your needs. This also works for insertion and deletion as well.
Joseph
Ok, thanks again ppl. The problem was, that I didn't know where I should get that command object from since I not explicitly had a SQL string or so.
MAD9