how to update datatable in c# with code?

Question

i want move data from database to another database.

i write 2 function. fuction 1 :i fill table from database1 into a datatable and named this DT

in function 2 i fill table in database2 with Dt and named it's dtnull

i updat dtnull in database 2

function 2:
{
  SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand();
            sda.SelectCommand.Connection = objconn;
            sda.SelectCommand.CommandText = "Select * from " + TableName + "";
            DataTable dtnull = new DataTable();
            sda.Fill(dtnull);
            SqlCommandBuilder Builder = new SqlCommandBuilder();
            Builder.DataAdapter = sda;
            Builder.ConflictOption = ConflictOption.OverwriteChanges;
            string insertCommandSql = Builder.GetInsertCommand(true).CommandText;
            foreach (DataRow Row in Dt.Rows)
            {
                dtnull.ImportRow(Row);

            }
           sda.Fill(dtnull);
           sda.Update(dtnull);
}

Answer 1

If you need to copy SQL database then just back it up and restore. Alternatively use DTS services.

Answer 2

If it's just a few tables I think you can

1. right click on the table you want in the SQL Management studio
2. generate a create script to your clipboard
3. execute it
4. Go back to your original table and select all the rows
5. copy them
6. go to your new table and paste

No need to make this harder than it is.

Answer 3

You don't really need to use an update for this. You might try out this solution, it might be the easiest way for you do this.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

If you would like a LINQ solution, I could provide you with one.

Answer 4

There is a lot that is left unexplained. For example, do the source table and target table have the same column structure?

Can you see both database from the same SqlConnection (i.e. are they on the same machine)? If so, you can do it all in one SQL statement. Assuming you want to copy the data from table T1 in databse DB1 to table T2 in database DB2, you would write

insert DB2.dbo.T2 select * from DB1.dbo.T1

Excecute using ExecuteNonQuery.

If the databases require different SqlConnections, I would read the data from the source using a SqlDataReader and update the target row by row. I think it would be faster than using a SqlDataAdapter and DataTable since they require more structure and memory. The Update command writes the data row by row in any event.