Dummy question on database updating

[Pavils Jurjans]

Hello,

Here's my code:

static string DbConnStr = "*OLEDB conn str*";

static DataSet LoadDataSet(string sqlQuery)
{
DataSet dataset = new DataSet();
return LoadDataSet(dataset, sqlQuery);
}
static DataSet LoadDataSet(DataSet dataset, string sqlQuery)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlQuery, DbConnStr);
adapter.Fill(dataset);
return dataset;
}

public static void Main()
{
DataSet mySet = LoadDataSet("SELECT * FROM myTable");
foreach (DataRow dataRow in mySet.Tables[0].Rows)
{
Console.WriteLine("\t{0}\t{1}", dataRow["id"], dataRow["txt"]);
if (dataRow["id"] + "" == "2") {
Console.WriteLine("Attempting to update...");
dataRow.BeginEdit();
dataRow["txt"] = dataRow["txt"] + "#";
dataRow.EndEdit();
}
}
mySet.Tables[0].AcceptChanges();
}

I certainly do something wrong here, because I fail to updat the real data
in the database. Please, tell me what's the problem with my code... I know
that I can always build some SQL statement thatt updates the data, when
executed, but I want to find out the possiblities of ADO.NET built-in
features.

Thanks,

Pavils

 

[William Ryan eMVP]

You are never calling Update which is probably the biggest problem. Call
Update on the DataSet/DataTable -- Replace the AcceptChnages line with
adapter.Update(mySet.Tables[0]);
Also, make sure you have a valid update command for your DataAdapter.

Cheers,

Bill

 

[Pavils Jurjans]

Ok, now I have update the code... I was forced to provide my own
"UpdateCommand" property for the adapter, and I find it somewhat
disappointing that I have to provide this manually. I was expecting that
ADO.NET would be able to create the necessary UPDATE, INSERT, and DELETE
statements by himself. What's the reason behind this inability? The old ADO
was very bound to the connection, yet it knew how to update values without
providing any SQL. statements.

Rgds,

-- Pavils

You are never calling Update which is probably the biggest problem. Call
Update on the DataSet/DataTable -- Replace the AcceptChnages line with
adapter.Update(mySet.Tables[0]);
Also, make sure you have a valid update command for your DataAdapter.

Cheers,

Bill

Hello,

Here's my code:

static string DbConnStr = "*OLEDB conn str*";

static DataSet LoadDataSet(string sqlQuery)
{
DataSet dataset = new DataSet();
return LoadDataSet(dataset, sqlQuery);
}
static DataSet LoadDataSet(DataSet dataset, string sqlQuery)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlQuery, DbConnStr);
adapter.Fill(dataset);
return dataset;
}

public static void Main()
{
DataSet mySet = LoadDataSet("SELECT * FROM myTable");
foreach (DataRow dataRow in mySet.Tables[0].Rows)
{
Console.WriteLine("\t{0}\t{1}", dataRow["id"], dataRow["txt"]);
if (dataRow["id"] + "" == "2") {
Console.WriteLine("Attempting to update...");
dataRow.BeginEdit();
dataRow["txt"] = dataRow["txt"] + "#";
dataRow.EndEdit();
}
}
mySet.Tables[0].AcceptChanges();
}

I certainly do something wrong here, because I fail to updat the real data
in the database. Please, tell me what's the problem with my code... I know
that I can always build some SQL statement thatt updates the data, when
executed, but I want to find out the possiblities of ADO.NET built-in
features.

Thanks,

Pavils

 

[William Ryan eMVP]

Ok, now I have update the code... I was forced to provide my own
"UpdateCommand" property for the adapter, and I find it somewhat
disappointing that I have to provide this manually. I was expecting that
ADO.NET would be able to create the necessary UPDATE, INSERT, and DELETE
statements by himself.

You can still get it to generate the logic for you with a CommandBuilder
object, but they have a lot of shortcomings.
What's the reason behind this inability?
A dataadapter is totally indifferent to it's data. It will send any data
that you try to give it, provided the rowstate is added/deleted/modified.
So the same DataAdapter could be used to update 100 different datasets, each
of which go it's data from a different source. That's a lot of power.
However, it doesn't know or care where the data came from, it just cares
about rowstate (and the DB of course cares that the query is legit). It
coudl also take the same 100 datasets and send each one to a different
Database. In order to do give you this, it demands that you tell it where
it's sending the stuff and how to do it.

The old ADO

was very bound to the connection, yet it knew how to update values without
providing any SQL. statements.

Rgds,

-- Pavils

You are never calling Update which is probably the biggest problem. Call
Update on the DataSet/DataTable -- Replace the AcceptChnages line with
adapter.Update(mySet.Tables[0]);
Also, make sure you have a valid update command for your DataAdapter.

Cheers,

Bill

Hello,

Here's my code:

static string DbConnStr = "*OLEDB conn str*";

static DataSet LoadDataSet(string sqlQuery)
{
DataSet dataset = new DataSet();
return LoadDataSet(dataset, sqlQuery);
}
static DataSet LoadDataSet(DataSet dataset, string sqlQuery)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlQuery, DbConnStr);
adapter.Fill(dataset);
return dataset;
}

public static void Main()
{
DataSet mySet = LoadDataSet("SELECT * FROM myTable");
foreach (DataRow dataRow in mySet.Tables[0].Rows)
{
Console.WriteLine("\t{0}\t{1}", dataRow["id"], dataRow["txt"]);
if (dataRow["id"] + "" == "2") {
Console.WriteLine("Attempting to update...");
dataRow.BeginEdit();
dataRow["txt"] = dataRow["txt"] + "#";
dataRow.EndEdit();
}
}
mySet.Tables[0].AcceptChanges();
}

I certainly do something wrong here, because I fail to updat the real data
in the database. Please, tell me what's the problem with my code... I know
that I can always build some SQL statement thatt updates the data, when
executed, but I want to find out the possiblities of ADO.NET built-in
features.

Thanks,

Pavils