locked
update 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:14 PM

Answers

  • this is what I need to do to update a datatable to database..

    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:51 PM
    Tuesday, April 5, 2011 6:51 PM

All replies

  • Basically, your datatable works offline? and you need a sync functionality for online mode?
    Cornel Croitoriu - Senior Software Developer - www.Biz-Forward.com | www.Croitoriu.NET
    Thursday, February 17, 2011 1:56 PM
  • No, datatable is not working offline, this datatable was created by joining table A and table B locally using linq to objects.
    Thursday, February 17, 2011 5:09 PM
  • Sorry I am not making it 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:05 PM
  • this is what I need to do to update a datatable to database..

    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:51 PM
    Tuesday, April 5, 2011 6:51 PM