Update sql server from a dataset

alec

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

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

alec15y ago
ok I have raised the visibility of the SqlDataAdapter and added a try block with da.update(need_html_list, "products");
Vulpes15y ago
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.
alec15y ago
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 Hobbs15y ago
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 Hobbs15y ago
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 Hobbs15y ago
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.
Vulpes15y ago
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.
alec15y ago
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
Vulpes15y ago
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.
alec15y ago
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 Nayak15y ago
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
alec15y ago
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\n" + 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\n"+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:\n"+exUpdateDB);
}
}
catch (Exception exConectToUpdateHtml)
{
MessageBox.Show("Could not connect to Database\n"+exConectToUpdateHtml);
}
}
Vulpes15y ago
Without seeing your existing code, I'd have said something like:

adapter.Update(ds);