Manipulating data between data adapters

dotnet framework


  • 1. DataRowView
    I have a dataTable(s) that I wish to locate a specific row in, then update a column value within that row. I have created a dataView and applied a rowFilter to the dataView, but am unclear on how I access specific rows, and dataColumns within the row. I believe that a dataRowView is the answer, but have had no success implementing it.
  • 2. Performance better with DataSet or DataReader or ...???
    Hi, I have a business-entity-classes which are constructed of few primitive types and one DataSet type. For Example: Public class MyClass{ private int id; private string name; private DataSet childrenCollection; In my data access layer I want to create an instance based on this class and fill it with data from the DB (MSDE). I have two options in mind and I want to know which would be the best in term of performance. In both options I using a single stored procedure with two select statements one for the single row (primitive types) and one for the children collection. For example: SELECT ID, Name from T_Parents WHERE ID= id; SELECT ID, Name from T_Children WHERE ParentID = id; The options are: 1. Fill all the data into a DataSet. As there are two select statements two tables will be created in the DataSet I would be able to copy the values from the first table to my class primitive and the other table to my class DataSet. 2. Use a DataReader and, by looping over the second ResultsSet, build a DataTable row after row. So instead of using the DataSet.Fill method Il be anuallycreating the rows and inserting the values based on the results coming from the DataReader. I don think it matters, but I using the data access application bloc v2. If there is another (better) option I haven thought about Il be happy to know. Otherwise I would like to know which one should perform better. Thank you, Gwenda
  • 3. urgent: connection pooling error
    Hi all, I have a web service that opens a sqlconnection to the db and does some work. Just recently I have begun getting the following error on a frequent basis: Timeout expired.The timeout period elapsed prior to obtaining a connection from the pool.This may have occurred because all pooled connections were in use and max pool size was reached. I have verified that my web service is closing connections. Why is this happening? Can I increase the max pool size? How? thanks, Param

Manipulating data between data adapters

Postby John » Tue, 29 Aug 2006 13:31:46 GMT


I have two data adapters bound to two separate tables. How can I;

1. Loop through all records one by one in one of them while reading column 
values, and

2. Insert a record from data adapter A into data adapter B

via code?



Re: Manipulating data between data adapters

Postby Cor Ligthert [MVP] » Tue, 29 Aug 2006 13:49:18 GMT


Don't call those things DataAdapters. It are DataSets, to be more precise 
Strongly Typed DataSets.

A datarow in a table has a reference set to that table in the datarow. That 
is needed to know the description of the items which are in the Columns 
collection, even if it is not in the table.

As well is there often not one row, but two rows, the original one and the 
current one in a datatable.

All this makes it difficult to pull a row from one table and to place it in 
the other.

The receiving table to be a clone of the sending one.

Therefore it is mostly only possible to create a datarow in whatever way in 
the new table and than to fill the items for that.

For that I like very much the loaddatarow from the datatable

Others like more the ItemArray  property from the datarow

I hope this gives an idea,


"John" < XXXX@XXXXX.COM > schreef in bericht 

Re: Manipulating data between data adapters

Postby Miha Markic [MVP C#] » Tue, 29 Aug 2006 16:18:27 GMT

Hi John,

I would use two commands and two connections.
One command should read data while the other should write.
But you'll need two connections - one per each command as reading needs a 
conneciton opened all the time.
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development
Blog:  http://www.**--****.com/ 

Re: Manipulating data between data adapters

Postby William (Bill)Vaughn » Wed, 30 Aug 2006 01:30:31 GMT

Ah no. Do not use ADO.NET for this (or any data access query interface). Use 
an INSERT to move the rows on the server (the database engine). Don't bring 
rows to your client to do bulk updates.

William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.

Similar Threads:

1.Data Adapters, Data Sets, Data TableMappings, and XML

Here's what I'm trying to do:

1. Create a data adapter with Select and Insert commands.

2. Use DataTableMappings to map XML tags into columns in a 
data base table.

3. Create a dataset

3. Read in a schema using dataset.ReadXmLSchema

4. Read in an xml file that contains the data using 
dataset.Read XML

5. Add 3 new columns to the dataset. (These columns have 
already been mapped in step 2).

6. Put data in the 3 new columns.

7. Modify some data in the other columns using xpath 
queries to find the nodes that need changing.

8. Use datadapter.update(dataset, tablename)

Everything seems to work fine until step 8. I get all the 
data, the extra columns get added, and the data in other 
columns gets modified. When I execute step 8 I get:

Run-time exception thrown : 
System.InvalidOperationException - Cannot add or remove 
columns from the table once the DataSet is mapped to a 
loaded XML document.

Any ideas about how I can either fix this or do it another 


2.problem with data adapter and data set while inserting and retrieving data

i am using a data adapter and a dataset for filling and retrieving data
into .mdb database.
following is the code.....

for the form load event
  Dim dc(0) As DataColumn
            If OleDbConnection1.State = ConnectionState.Closed Then
                MsgBox("connection can not be established")
            End If
            DA.Fill(DataSet11, "Table1")
            cmd = New OleDbCommandBuilder(DA)
            dc(0) = DataSet11.Tables("Table1").Columns("EmpID")
            DataSet11.Tables("Table1").PrimaryKey = dc
        Catch ex As Exception
        End Try
        Delete.Enabled = False
    End Sub

for  inserting values into the DB

Private Sub Insert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Insert.Click
        Dim dr As DataRow
        dr = DataSet11.Tables("Table1").NewRow
        dr.Item(0) = Val(TB1.Text)
        dr.Item(1) = TB2.Text
        dr.Item(2) = TB3.Text
        dr.Item(3) = Val(TB4.Text)
        dr.Item(4) = TB5.Text
        DA.Update(DataSet11, "Table1")
        MsgBox("data is saved")
        rno = 0
        call filldata()

filldata function consists of the following

 With DataSet11.Tables("Table1").Rows(rno)
         TB1.Text = Trim(.Item(0))
         TB2.Text = Trim(.Item(1))
         TB3.Text = Trim(.Item(2))
         TB5.Text = Trim(.Item(4))
         End With

the error it gives is " there is no row at
0........system.nullreference...........i checked the connection and
its working fine and also the database is getting accessed........the
error is occuring at the line " With
DataSet11.Tables("Table1").Rows(rno) "

3.Manipulate Data in a data set

Sorry if this is a basic question but I was wondering how to test the
dataset information as it is being read.

ex, I have a table that contains file extentions such as .xls, .doc,
.ppt. What I want to do is test the dataset as they are being written
and if the dataset is equal to .xls i want it to be excel..

I am new to this and am able to connect to the table but I just can
seem to get a handle on how to test the current dataset record and make
any changes. any bump in the right direction would be helpful ...
thanks in advance

4.Provider independent data access: creating a data adapter from a connection

I have written a provider independent utility class for 
executing queries. One of the routines looked like this in .net 1.1:

static public DataSet ExecuteQuery(IDbConnection connection, String sql)

   DataSet result  = new DataSet();
   IDbDataAdapter adapter = /* magic to create an adapter from a 

   // The sybase adapter's implement IDisposable, so wrap with a using. 

   using(adapter as IDisposable)
     adapter.SelectCommand.CommandText = sql;
     adapter.SelectCommand.Connection = conn;

   return result;

The magic code to create the adapter was ugly: it essentially performed 
a dictionary lookup on the type of connection, and created the resulting 
adapter type. If IDbConnection had a method called CreateAdapter, 
analagous to CreateCommand, this would have been cleaner.

With the introduction of ADO.NET 2.0 and the DbProviderFactory class, I 
was hoping that some of the ugliness would go away. Unfortunately, I am 
running into some problems. Namely

1- DbConnection still does not have a CreateAdapter method

2- DbConnection does not offer a way to get back to the 
DbProviderFactory that created it.

So I still cannot create the correct adapter given only a generic 
DbConnection. I am looking for suggestions on how to code my routine. So 
far, I have thought of these

1- Make the user pass in the DbProviderFactory that goes with the 
connection. CONS: forces users to use DBProviderFactory. We have a lot 
of code that doesn't.

2- Stick with the dictionary lookup (Dictionary<ConnType, AdapterType>. 
CONS: DLL dependencies explode.

3- Use a different dictionary lookup (Dictionary<ConnType, 
DBProviderFactory>. CONS : A little messy, but better than 2.

4- Don't use provider specific adapters at all, just use a generic one. 
Like this:

class GenericAdapter : System.Data.Common.DbDataAdapter

static public DataSet ExecuteQuery(DbConnection connection, String sql)

   DataSet result  = new DataSet();
   GenericAdapter adapter = new GenericAdapter();
   adapter.SelectCommand = conn.CreateCommand();
   adapter.SelectCommand.CommandText = sql;
   return result;

I have tested this with two databases, Sybase ASE and ASA, and it seems 
to work. Are there any serious downfalls to not using the vendor 
specific adapter type? Is this code safe?


5.Passing parameter for Stored Procedure to Data Adapter/Data Se

6. Passing parameter for Stored Procedure to Data Adapter/Data Set

7. How to change data in grid after you change the SQL select for the data adapter

8. 3rd Tier data not displaying in Data Adapter Preview

Return to dotnet framework


Who is online

Users browsing this forum: No registered users and 27 guest