none
update from datatable to database RRS feed

  • Question

  • I have struggled several days on updating some columns in a table(let's called table A). I need one of the column to join to some tables in a different server(let's call this table B), get some columns values from that server, then update the target table. So far what I have tried:

    1. openrowset using update statement with join statements - failed, AD Hoc distributed queries does not turn on. To turn it on just not possible.

    2. I have been using various ways to get table B into System.Data.DataTable (let's call datatable). Then Loop thru Table A, using datatable's column to set the column in Table A. But this was so slow. every thousand records take about 1.5 mins. I have about roughly 250,000 records to update.

    3.I have tried to use LINQ to object to join table A and datatable into a query. looping through is ok, but when I do submitchanges, it takes more than 2 hours. Finally I quit.

    Do you have any input? Or What is the fastest way to update database using a disconnected datatable?

    Wednesday, February 16, 2011 11:34 PM

Answers

  • using (SqlDataAdapter adapter = new SqlDataAdapter(sqlString,@"Data Source=Server1;Initial Catalog=DB1;trusted_connection=true;"))
    {
    	SqlCommandBuilder builder = new SqlCommandBuilder(adapter );
    	
    	DataTable table = new DataTable();
    	adapter.FillSchema( table, SchemaType.Source );
    	adapter.Fill( table );
    	
    	
    	foreach (DataRow row in table.Rows)
    	{
    		row["SomeColumnName" ] = DBNull.Value; 
    	}
    	adapter.Update( table );
    	
    	table.Dump();
    	
    }
    
    • Marked as answer by Meidi Tuesday, April 5, 2011 6:55 PM
    Tuesday, April 5, 2011 6:55 PM

All replies

  • Sounds like for 1. you are getting a "double-hop" issue.  This is because you are dealing with several remote locals (User A, Server B and Server C) and the OPENROWSET cannot be issued by User A to run something on Server C.  Now, if you ran that OPENROWSET directly on Server B (via a Linkedserver), then it would probably run without error.

    Alternatively, hook User A into Server C instead of going thru Server B to get at Server C.

    Or, create a job to run the OPENROWSET on Server B and store those results there.  Now User A will be able to access the results from the OPENROWSET.

     

     

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, February 17, 2011 5:58 PM
  • Maybe I am not making the question clear, but here is my code:

    //My goals is to update Task table's CASE_WORKER_CD, Task_ID is primary key.
    
    //Could you help me what is the fastest way to update on the Task table?(Linked server is not allowed).
    void Main()
    {
    	string sqlString = @"Select Task_ID, CASE_WORKER_CD, CS_ID, CASE_NM from Task";
    	DataTable table = new DataTable();
    	using (SqlDataAdapter adapter = new SqlDataAdapter(sqlString,@"Data Source=SERVER1;Initial Catalog=DB1;trusted_connection=true;"))
    	{
    		adapter.Fill( table);
    		
    		var query = from t in table.AsEnumerable()
    					join c in GetCISData().AsEnumerable()
    					on t.Field<string>("CS_ID") equals c.Field<string>("cs_id")
    					select new
    		{
    			TASK_ID = t.Field<string>("Task_ID"),
    			WorkerCode = c.Field<string>("worker_code")			
    		};
    		
    		//How do I update back to the database efficiently?
    //		foreach (var q in query)
    //		{
    //			
    //		}
    	}
    }
    DataTable GetCISData()
    {
    	string sqlString = @"select cs_id , nm as worker_code from cs";
    	DataTable table = new DataTable();
    	using (SqlDataAdapter adapter = new SqlDataAdapter(sqlString,@"Data Source=SERVER2;Initial Catalog=DB2;trusted_connection=true;"))
    	{
    		adapter.Fill( table);
    	}
    	return table;
    }
    
    
    
    Thursday, February 17, 2011 8:02 PM
  •   //How do I update back to the database efficiently?

    You do this by issuing .Update on the DataAdapter.  Sounds like you need to take a step back and determine the basic building blocks of ADO.NET data management.  There's lots of resources available here on MSDN.

    As far as not being clear in your question.. I understood your 1. statement that you were using OPENROWSET in a T-SQL statement:

    "1. openrowset using update statement with join statements - failed, AD Hoc distributed queries does not turn on. To turn it on just not possible."

    OPENROWSET is a Transact-SQL command that allows you to manage data from remote sources thru a linkedserver.  This is what I was addressing in my initial post.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by Alan_chenModerator Monday, March 14, 2011 2:57 AM
    • Unmarked as answer by Meidi Tuesday, April 5, 2011 6:54 PM
    Thursday, February 17, 2011 8:51 PM
  • Maybe  I am missing something, but here is the code I have for updating each row:

    int counter = 0 ;
    		foreach (DataRow row in table.Rows)
    		{
    			var q = query.FirstOrDefault (q1 => q1.TASK_ID == Convert.ToDecimal( row[ "Task_ID" ] ) );
    			if( q != null )row["CASE_WORKER_CD" ] = q.WorkerCode;
    			counter++;
    			
    			if( counter %1000 == 0 )
    			{
    				Console.WriteLine (DateTime.Now + " counter = " + counter);
    				Console.WriteLine (DateTime.Now + " Start Update 1000 records");
    				adapter.Update( table );
    				Console.WriteLine (DateTime.Now + " Finish Update 1000 records");
    			}		
    		}
    		adapter.Update( table );
    
    this code just not efficient, every 1000 records takes more than 5 mins...
    Thursday, February 17, 2011 8:57 PM
  • "this code just not efficient, every 1000 records takes more than 5 mins..."

    Then do the calculations in a StoredProcedure on the database.  But maybe I am just having difficulty reading your code (I don't really understand what it is you are attempting to do).


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, February 17, 2011 9:00 PM
  • using (SqlDataAdapter adapter = new SqlDataAdapter(sqlString,@"Data Source=Server1;Initial Catalog=DB1;trusted_connection=true;"))
    {
    	SqlCommandBuilder builder = new SqlCommandBuilder(adapter );
    	
    	DataTable table = new DataTable();
    	adapter.FillSchema( table, SchemaType.Source );
    	adapter.Fill( table );
    	
    	
    	foreach (DataRow row in table.Rows)
    	{
    		row["SomeColumnName" ] = DBNull.Value; 
    	}
    	adapter.Update( table );
    	
    	table.Dump();
    	
    }
    
    • Marked as answer by Meidi Tuesday, April 5, 2011 6:55 PM
    Tuesday, April 5, 2011 6:55 PM
  • Wait a minute ... I thought your goal was to get data from a table on Server2 and update a table on Server1. The code you show above doesn't do that at all ... you're getting data from Server1 and updating one column for all rows back to the same table. Have you changed your requirements?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, April 9, 2011 5:29 PM