Update sql server from a dataset

Question

I have code that is designed to select four columns from a table on my sql server database then populate two of them with information from the internet and then update the database with the data from the newly populated columns. I can do all of this except the updating the database.

Current Method:

create sql connection and use select command to get data from database

use dataAdapter to fill a dataSet with the result of the select command

populate columns based on web data and save changes to dataSet

???? how do i put this data into the database from the DataSet

alec · Answer

ok I have raised the visibility of the SqlDataAdapter and added a try block with da.update(need_html_list products);

Vulpes · Answer

It has to be the same SqlDataAdapter that was used to fill the DataSet in the first place. This is necessary because only this adapter will have knowledge of the mappings between the rows in the database and the DataRows in the DataSet and therefore be in a position to update the former with changes to the latter.

alec · Answer

this may be a moronic question or an insightful one ... does it have to be the same sqlDataAdapter used to select the data (da in my code I posted)

Sam Hobbs · Answer

I forgot to mention that if you use a TableAdapter or an entity then your proramming will be easier. TableAdapters are older technology but they are still very useful. Data entites are newer technology. You can create entities for your tables and then many of the details are done for you.

Sam Hobbs · Answer

Well apparently there was a lot of discussion occuring while I was typing my previous reply. I will revise it. When providing sample code it helps very much to have a simple sample that is the minimum to re-create the problem. For example the following updates a table using a SqlDataAdapter. This code works. I am not sure what you need to do but I hope this helps. using (SqlConnection Connection = new SqlConnection(Properties.Settings.Default.ConnectonStringSetting)) { try { Connection.Open(); } catch (Exception ex) { Console.WriteLine(Open error: {0} ex); return; } DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(Select * From Table1; Connection); DataTable Table = new DataTable(); da.Fill(Table); foreach (DataRow r in Table.Rows) Console.WriteLine({0} {1} r.Field (Key) r.Field (1)); // Table.Rows[3].SetField (Value DateTime.Now.ToShortTimeString()); SqlCommand UpdateCommand = new SqlCommand(UPDATE Table1 SET Value = @Value WHERE [Key] = @Key Connection); // Add the parameters for the UpdateCommand. UpdateCommand.Parameters.Add(@Key SqlDbType.Int 4 Key); UpdateCommand.Parameters.Add(@Value SqlDbType.NChar 10 Value); da.UpdateCommand = UpdateCommand; da.Update(Table); // foreach (DataRow r in Table.Rows) Console.WriteLine({0} {1} r.Field (Key) r.Field (1));

Sam Hobbs · Answer

What do you mean by dataAdapter? Do you mean SqlDataAdapter? I see you posted your code while I was typing this reply originally so I see now that you do mean SqlDataAdapter. If you look at the SqlDataAdapter class then you will see an UpdateCommand property. If you need sample uses just search for them. For example: http://www.c-sharpcorner.com/Search/?sKeywords=DataAdapter returns many articles but you should find a relevant one easy enough. For example Creating a Custom Data Adapter should help at least indirectly.

Vulpes · Answer

The crux of the matter is that the DataAdapter keeps track of all changes that were made to the DataSet since the Fill() method was called. It does this by maintaining a mapping between the DataRows in the DataSet and the rows in the database table(s). The DataRows themselves have a property called RowState which records their current status (unchanged added deleted modified) and so all the Update() method needs to do is to look at these to see what updates are needed to the database. You can call Fill() several times on the same DataSet but using queries for different tables in the database which will then be mapped to corresponding DataTables within the DataSet. Calling the Update method on the Dataset will then update all its DataTables or you can choose to update just one of them as Satyapriya indicated.

alec · Answer

so I select data alter it and then do the update and then my up dates will be applied to the table I selected from originally matching up the primary key? I included the primary key in the select statement to aid navigating the table but what if I want to update a different table from the one I originally selected from would they both have to have the same primary keys and what if the column names differ

Vulpes · Answer

The DataAdapter's Update method examines each row in the DataTable (and in each DataTable of the DataSet) and writes to the database those rows which were inserted updated or deleted since Update() was last called or since the DataTable's AcceptChanges method was last called. So there's nothing further for you to do.

alec · Answer

but how will it know what to put where surely I need a more detailed update statement to specify which rows are to be updated with what

Satyapriya Nayak · Answer

Hi alec Vulpes is correct but mention the table name also as below. SqlDataAdapter1.Update(ds tablename); Thanks If this post helps you mark it as answer

alec · Answer

that doesn't really answer the question. I want to know If I have a DataSet how do I use the data within it to perform an update on a table in my sql server database? here is the code //Update HTML Button //****************** private void update_html_button_Click(object sender EventArgs e) { //conect to test_db using (SqlConnection test_db_conn = new SqlConnection( test_db_ConnectionStrin ) ) try { //open connection test_db_conn.Open(); DataSet need_html_list = new DataSet(); try { //execute sql command using (SqlCommand get_need_html_list = new SqlCommand( UPDATE products + SET rs_web_product_data_sorcedata_url = + (SELECT TOP 1 rs_web_url FROM rs_web_urls ORDER BY date_entered DESC) + rs_id + SELECT rs_id rs_web_product_data_sorcedata_url rs_web_product_html date_html_retrieved + FROM test_db.dbo.products + WHERE rs_web_product_html IS NULL  ebay_test_db_conn)) { need_html_list = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = get_need_html_list; da.Fill(need_html_list); DataTable need_html_table = new DataTable(); da.Fill(need_html_table); } //MessageBox.Show(HTML Update Succesful); //close conection test_db_conn.Close(); } catch (Exception exUpdateHtml) { MessageBox.Show(Update failed but connection was established
 + exUpdateHtml); } try //this is not yet working { //get html //string url; foreach (DataRow url_row in need_html_list.Tables[0].Rows ) { HttpWebRequest myWebRequest = (HttpWebRequest)HttpWebRequest.Create(url_row[rs_web_product_data_sorcedata_url].ToString()); myWebRequest.Method = GET; // make request for web page HttpWebResponse myWebResponse = myWebRequest.GetResponse() as HttpWebResponse; StreamReader myWebSource = new StreamReader(myWebResponse.GetResponseStream()); string myPageSource = string.Empty; myPageSource = myWebSource.ReadToEnd(); myWebResponse.Close(); //check this //********** url_row.BeginEdit(); url_row[rs_web_product_html] = myPageSource; url_row[date_html_retrieved] = System.DateTime.Now; url_row.EndEdit(); url_row.AcceptChanges(); //********** } } catch (Exception exFetchFail) { MessageBox.Show(Could not retrive data from internet
+exFetchFail); } //Update products table on test_db from DataSet need_html_list try { //open connection test_db_conn.Open(); } catch (Exception exUpdateDB) { MessageBox.Show(Could not update Database:
+exUpdateDB); } } catch (Exception exConectToUpdateHtml) { MessageBox.Show(Could not connect to Database
+exConectToUpdateHtml); } }

Vulpes · Answer

Without seeing your existing code I'd have said something like: adapter.Update(ds);