HowTo update database table from new DataTable without do <adapter>.Fill(...) action

Question

Hi @ all,

i have a oracle database and i visual studio prof edit. 2008 and developing in c#.

my problem is that i want to update just  column in the database table without using String based sql command. i want to you dataTables to do that. but it seems to be a problem there: to update the database table from DataTable i have to do <adapter>.Fill(dataTable) first, then update one row and after all <adapter>.update(...) ???? 

ins't there any practical method to to this like this way:

1. create new DataTable

2. get schema for database table

3. just create 3 new rows with values and add them to just created DataTable

4. <adapter>.update(...DataTable...)

but if i do it in this way, i will get exception after exception

the BIG BIG disadvantage seems to be, that my database is in germany and my program runs in the usa and my database table in germany is about 800 MB, i have to transfer 800 MB every 10 minutes, because i have to update row every 10 minutes.

isn't there any other method to just tranfer a dataset with just one row and the other knows what to do autmatically?

any ideas?

regards  gwanndan

Answer 1

Write a stored procedure on your Oracle server and pass the data you want to insert to the SP and let it insert. Have your SP check the database so you are not passing it across the wire.

Answer 2

Hi, can you pls post your code.

---

Hope This One Can Help.
Thanks
To Be Happy Is To Be YourSelf
Please remember to mark my reply as Proposed as Answers if it's the answer to your question.

Answer 3

This cannot be done with a DataTable, you will have to use an approach similar to what Michael suggested.

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 4

mmmhhh,

but didn't micrsosoft think about the fact that sometimes the software and the databases are not on the same maschine and when it is on the same maschine, you should read the hole tables into the main storage?  this is not a very good paradigma.

so, next step: the software has already been developed and there are thousands of code lines. so not possible to change everything to stored procedures.

so i have to create new code which is somwthing like an interface to the database and i have to process a dataTable wich created data in it.

i don't know what todo. write my own "wrapper" klass which constructs its own sql string from the dataTable ?

... and i can't believe that micsrosoft has never thought of

Answer 5

here is my code i just playing arround with.

public DataSet update(DataSet dataSet, string databaseTableName) {

			DbDataAdapter ad = this.dbFactory.CreateDataAdapter();

			DbCommand command = this.dbCon.CreateCommand();
			command.CommandType = CommandType.TableDirect;
			command.CommandText = databaseTableName;
			command.Connection = this.dbCon;

			ad.SelectCommand = command;

			DbCommandBuilder builder = this.dbFactory.CreateCommandBuilder();

			builder.DataAdapter = ad;

			ad.UpdateCommand = builder.GetUpdateCommand();
			ad.DeleteCommand = builder.GetDeleteCommand();
			ad.InsertCommand = builder.GetInsertCommand();

			ad.Update(dataSet.Tables[databaseTableName]);

			return dataSet;

		}

does anyone has an idea what to do or to use instead?

Answer 6

A DataTable wasn't designed to reconstruct your database my friend. It is an offline cache of data. A bucket of data returned from a query. Coupled with a DataAdapter, updates can be made back to the database. But it wasn't designed to effectively redesign your database and change columns and propagate those kind of changes back to the database. NonQuery's can do that however.

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 7

Ok, is there another procedure i can do?  just say a select and an update to the database table. tell me if i am incorrect: i have to create a DataSet / DataTable for 

selecting to get the correct schema from database table and within the correct values.

and for updateting i have to create the sql on my own like

string sql = "UPDATE table set field1 = 'abc' where pkField = 123"

... and of cause i will use parameters :-) !

regards

gwann

Answer 8

Ok, is there another procedure i can do?  just say a select and an update to the database table. tell me if i am incorrect: i have to create a DataSet / DataTable for 

selecting to get the correct schema from database table and within the correct values.

and for updateting i have to create the sql on my own like

string sql = "UPDATE table set field1 = 'abc' where pkField = 123"

... and of cause i will use parameters :-) !

regards

gwann

You can write SQL statements to update the database directly and call a SqlCommand directly. I would recomend you get David Sceppa's book on ADO.NET. It has helped me throughout the years and I know it will help you.

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 9

thank you for your advises. and very nice: i have this book ADO.net here in my handy. just got from the library here in my company.

thank you all

regards

gwann