none
How do I synchronize a DataSet to a database? RRS feed

  • Question

  • Hi, I'm using ADO.NET for the first time and I simply want to set up a SqlDataAdapter for downloading/uploading a database table. So I tried this:

        SqlDataAdapter da = new SqlDataAdapter();

        SqlParameter[] ps = new SqlParameter[] {
            new SqlParameter("@ID", SqlDbType.Int),
            new SqlParameter("@Name", SqlDbType.NVarChar, 80),
            new SqlParameter("@SourceMapSet", SqlDbType.NVarChar, 260),
            new SqlParameter("@OutFilename", SqlDbType.NVarChar, 260),
            new SqlParameter("@XMin", SqlDbType.Float),
            new SqlParameter("@YMin", SqlDbType.Float),
            new SqlParameter("@XMax", SqlDbType.Float),
            new SqlParameter("@YMax", SqlDbType.Float),
        };
        foreach (SqlParameter p in ps)
            p.SourceColumn = p.ParameterName.Substring(1);

        da.SelectCommand = new SqlCommand("SELECT * FROM Sites", db);
        da.InsertCommand = new SqlCommand(
            "INSERT INTO Sites (Name, SourceMapSet, OutFilename, XMin, YMin, XMax, YMax) " +
            "VALUES (@Name, @SourceMapSet, @OutFilename, @XMin, @YMin, @XMax, @YMax);" +
            "SELECT * FROM Sites WHERE (ID = @@IDENTITY)", db);
        da.InsertCommand.Parameters.AddRange(ps);
        da.UpdateCommand = new SqlCommand(
            "UPDATE Sites SET Name = @Name, SourceMapSet = @SourceMapSet, OutFilename = @OutFilename, " +
            "XMin = @XMin, YMin = @YMin, XMax = @XMax, YMax = @YMax WHERE ID = @ID", db);
        da.UpdateCommand.Parameters.AddRange(ps);
        da.DeleteCommand = new SqlCommand("DELETE FROM Sites WHERE ID = @ID", db);
        da.DeleteCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));

    I got the error "The SqlParameter is already contained by another SqlParameterCollection." I see no obvious way to duplicate the SqlParameter array, and my Google searches suggest no one else has asked about this problem. So tell me, am I approaching this the wrong way? For example, do I even have to write the SQL at all?
    Friday, March 28, 2008 6:29 PM

Answers

  • That you can't use the same SqlParameter object in multiple SqlCommands is more feature than bug:  there are all kinds of terrible side-effects that you could experience if executing one command changed the value of a parameter used by another command.

     

    One thing you can do:  extract your assignment of ps into a method that returns a SqlParameter[] array, and use the method call as an argument to the AddRange calls.

     

    As far as your second question:  you don't want to call RejectChanges on the DataRow, you want to call AcceptChanges.  That'll change the row state from New/Modified/Deleted to Unchanged.  (It will also move the values in each column's Current version to the Original version, which should be what you want it to do.)

    Friday, March 28, 2008 11:33 PM
  • Look at the SqlBulkCopy class, it might make this task easier, not sure.

     

    For the second question, I don't think there is a way to temporarily muck with the RowState of the rows.  I have thought this would be nice too.  You could make a copy of the DataTable (using ImportRow) that excludes the local rows (using some filter expression), this might be one way to ensure you are safe.

    Friday, March 28, 2008 11:44 PM
  • Sorry, I knew that, or would have known that if I'd thought about it for a few seconds.  But it may still be worth doing:  create a TableAdapter, configure it against the underlying table, then remove your original DataTable and rename the new one.  (Be smart and back your project up first.)

     

    You can set the TableAdapter's connection at runtime.  First, when you create the TableAdapter, uncheck the box that says "Yes, save the connection string as...".  That will prevent the designer from creating an application setting for the connection string.  Then at runtime, create the Connection object using whatever method presently are using, and when you instantiate a TableAdapter, set its Connection property to it.

     

    The TableAdapter has a property called ConnectionModifier, which governs whether the Connection property of the generated object will be internal (the default), public, private, etc.  So you can write code like this:

     

    Code Snippet

    using (SqlConnection c = new SqlConnection(connectionString))

    {

       DataSet1TableAdapters.testTableAdapter ta = new DataSet1TableAdapters.testTableAdapter();

       c.Open();

       ta.Connection = c;

       ta.Update(myDataSet.test);

    }

     

     

    Monday, March 31, 2008 7:06 PM

All replies

  • And another question. I have some special rows that only exist locally, not in the database. How can I mark them unmodified so they are not sent to the database, without calling RejectChanges (because I want to keep the changes, of course)?

    Edit: Oh, the answer is to call AcceptChanges (duh)
    Friday, March 28, 2008 9:00 PM
  • That you can't use the same SqlParameter object in multiple SqlCommands is more feature than bug:  there are all kinds of terrible side-effects that you could experience if executing one command changed the value of a parameter used by another command.

     

    One thing you can do:  extract your assignment of ps into a method that returns a SqlParameter[] array, and use the method call as an argument to the AddRange calls.

     

    As far as your second question:  you don't want to call RejectChanges on the DataRow, you want to call AcceptChanges.  That'll change the row state from New/Modified/Deleted to Unchanged.  (It will also move the values in each column's Current version to the Original version, which should be what you want it to do.)

    Friday, March 28, 2008 11:33 PM
  • Look at the SqlBulkCopy class, it might make this task easier, not sure.

     

    For the second question, I don't think there is a way to temporarily muck with the RowState of the rows.  I have thought this would be nice too.  You could make a copy of the DataTable (using ImportRow) that excludes the local rows (using some filter expression), this might be one way to ensure you are safe.

    Friday, March 28, 2008 11:44 PM
  • Thanks, Robert, I wonder how I managed not to figure out what you said by myself.

    Does the .NET framework have a way to generate SQL and SqlParameters or other DbParameters for me, given my DataSet's schema, or assist me somehow?
    Saturday, March 29, 2008 1:21 AM
  • Hell yes.  Go into your DataSet, click on a table, right-click, and add a TableAdapter.  Then configure it.

     

    Saturday, March 29, 2008 7:16 AM
  •  Robert Rossney wrote:
    Hell yes.  Go into your DataSet, click on a table, right-click, and add a TableAdapter.  Then configure it.

    Unfortunately, "TableAdapter..." in the Add menu is grayed out (in Visual Studio 2008). Looks like I'm only allowed to create a new TableAdapter with a new table schema--I can remove the TableAdapter from a schema, but not add it. Anyway, I had already decided not to use a TableAdapter because the user can choose the connection string at run-time, whereas TableAdapter makes me choose it at compile-time.
    Monday, March 31, 2008 3:37 PM
  • Sorry, I knew that, or would have known that if I'd thought about it for a few seconds.  But it may still be worth doing:  create a TableAdapter, configure it against the underlying table, then remove your original DataTable and rename the new one.  (Be smart and back your project up first.)

     

    You can set the TableAdapter's connection at runtime.  First, when you create the TableAdapter, uncheck the box that says "Yes, save the connection string as...".  That will prevent the designer from creating an application setting for the connection string.  Then at runtime, create the Connection object using whatever method presently are using, and when you instantiate a TableAdapter, set its Connection property to it.

     

    The TableAdapter has a property called ConnectionModifier, which governs whether the Connection property of the generated object will be internal (the default), public, private, etc.  So you can write code like this:

     

    Code Snippet

    using (SqlConnection c = new SqlConnection(connectionString))

    {

       DataSet1TableAdapters.testTableAdapter ta = new DataSet1TableAdapters.testTableAdapter();

       c.Open();

       ta.Connection = c;

       ta.Update(myDataSet.test);

    }

     

     

    Monday, March 31, 2008 7:06 PM