Re: How I can sysnchronize changes in DataTable with my Database?

From: Dmitriy Lapshin [C# / .NET MVP] (x-code_at_no-spam-please.hotpop.com)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 14:40:01 +0200

> I understand but it is not a point.
> What is the purpose of this apporach if we use plain SQL commands?

The point is that we can make all the changes without having to keep the
connection to the database open, and then propagate all the changes back to
the database within a single method call. This ensures the connection is
open only when necessary, and this in turn facilitates scalability.

Keyword: disconnected architecture

> As we can see in the example, we can change data without SQL commands
> (backroud is not important) with DataTable.

Do you mean whether ADO .NET can generate the commands for you? Yes it can,
through the SqlBuilder class.
Does your question actually mean "I don't want to bother with all that SQL.
I want the framework to take care of all this SQL stuff. I just want to give
it the data, the primary key and have the data updated in the DB according
to the changes I've made in memory to the DataTable"? Then the answer is the
SqlBuilder class. 

-- 
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE
"mp" <plamir@volja.net> wrote in message
news:uv89YYMDEHA.712@tk2msftngp13.phx.gbl...
> I understand but it is not a point.
> What is the purpose of this apporach if we use plain SQL commands?
>
> As we can see in the example, we can change data without SQL commands
> (backroud is not important) with DataTable.
>
> Question is why is not possible to sychronize these changes??? May be is
> possible and if it is, HOW?
>
> I would like to be sure is that possible or not. If not I would like to
know
> what is purpose of data changes trough DataTable?
>
> Thanks
>
> "Nicholas Paldino [.NET/C# MVP]" <mvp@spam.guard.caspershouse.com> wrote
in
> message news:%23YZvZiDDEHA.2768@tk2msftngp13.phx.gbl...
> > mp,
> >
> >     The DataTable is disconnected, you can't make changes on the table
and
> > expect the database to update.  Updates are accomplished through the
data
> > adapter.
> >
> >     Basically, your data table stores the state of each row.  When you
> call
> > update, passing in the data table, the adapter cycles through the rows.
> If
> > the row is unchanged, then nothing is done.  If the row is added, it
> > executes the InsertCommand.  If it is edited, then it executes the
> > UpdateCommand.  If it was deleted, then it executes the delete command.
> >
> >     Now, when you call AcceptChanges, it changes the state of all the
rows
> > to unchanged, so when you call update, it doesn't do anything (there are
> no
> > changes).
> >
> >     To get around the error, you will have to create a SqlCommand
> statement
> > (parameterized) which you set the InsertCommand property to (as well as
> the
> > Update and Delete commands), and then your adapter should work.
> >
> >
> > -- 
> >                - Nicholas Paldino [.NET/C# MVP]
> >                - mvp@spam.guard.caspershouse.com
> >
> > "mp" <plamir@volja.net> wrote in message
> > news:ejgB1bDDEHA.240@tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Thank you for help.
> > > Unfortunately it doesn't work.
> > >
> > > I have removed Fill() and AcceptChanges.
> > > Now after Update;
> > > myOleDbDataAdapter.Update(myDataSet, "CustomersTable");
> > >
> > > I got following error message:
> > >
> > > Unhandled Exception: System.InvalidOperationException: Update requires
a
> > > valid InsertCommand when passed DataRow collection with new rows.
> > > at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
> > > DataTableMapping tableMapping)
> > > at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
> > srcTable)
> > > at ModifikacijePrekoDataTable.Example23_3.Main() in c:\documents and
> > > settings
> > >
> \mywork\c#\databases\modifikacijeprekodatatable\datatablemodifikacija.cs:
> > > line 147
> > > Press any key to continue
> > >
> > > If I use INSERT (SQL) command it is not what I need. I know How I can
do
> > > that on that way.
> > > I would like to make changes on my data using DataTable, not plain SQL
> > > commands.
> > >
> > > may be I need to do connection on some different way?
> > >
> > > Here is complete code. This code is based on example from Mastering
> Visual
> > > C@.NET book by Jason Price. Obviously there is lot of errors.
Originaly
> > Code
> > > is prepared for SQL connection and I have adapted on OleDb.
> > >
> > > Thanks.
> > >
> > >
> > > Example:
> > > using System;
> > > using System.Data;
> > > using System.Data.OleDb;
> > > namespace ModifikacijePrekoDataTable
> > > {
> > > class Example23_3
> > > {
> > >     public static void DisplayDataTable(DataTable myDataTable)
> > >     {
> > >
> > > // Display the columns for each row in the DataTable using a
> > > // DataRow object to access each row in the dataTable
> > > foreach(DataRow myDataRow in myDataTable.Rows)
> > > {
> > > Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]);
> > > Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);
> > > Console.WriteLine("ContactName = " + myDataRow["ContactName"]);
> > > Console.WriteLine("Address = " + myDataRow["Address"]);
> > > } // DisplayDataTable
> > > }
> > >
> > > public static void AddRow(DataTable myDataTable)
> > > {
> > > Console.WriteLine("\nAdding a new row with CustomerID of 'T1'");
> > > // Step 1: Use the NewRow() method of the DataRow object to create a
new
> > row
> > > in the DataTable
> > > DataRow myNewDataRow = myDataTable.NewRow();
> > > // Step 2: Set the values for the columns of the new row
> > > myNewDataRow["CustomerID"] = "T1";
> > > myNewDataRow["CompanyName"] = "T1 Company";
> > > myNewDataRow["ContactName"] = "Jason Price";
> > > myNewDataRow["Address"] = "1 Main Street";
> > > // Step 3: Use the Add() method through the Rows property to add the
new
> > > DataRow to the DataTable
> > >
> > > myDataTable.Rows.Add(myNewDataRow);
> > > // Step 4: Use the AcceptChanges() method of the DataTable to commit
> > > //myDataTable.AcceptChanges();
> > > }
> > >
> > > public static void ModifyRow(DataTable myDataTable)
> > > {
> > > Console.WriteLine("\nModifying a row with CustomerID of 'T1'");
> > > // Step 1: Set the PrimaryKey property for DataTable object
> > > DataColumn[] myPrimaryKey = new DataColumn[1];
> > > myPrimaryKey[0] = myDataTable.Columns["CustomerID"];
> > > myDataTable.PrimaryKey = myPrimaryKey;
> > > // Step 2: Use the Find() method to locate the DataRow in the
DataTable
> > > using the primary key value
> > > DataRow myEditDataRow = myDataTable.Rows.Find("T1");
> > > // Step 3: Change the column values
> > > myEditDataRow["CompanyName"] = "Widgets Inc.";
> > > myEditDataRow["ContactName"] = "John Smith";
> > > myEditDataRow["Address"] = "1 Any Street";
> > > // Step 4; Use the AcceptChanges() method of the DataTable to commit
the
> > > changes
> > > //myDataTable.AcceptChanges();
> > > Console.WriteLine("myEditDataRow.RowState = " +
myEditDataRow.RowState);
> > > }
> > >
> > > public static void RemoveRow(DataTable MyDataTable)
> > > {
> > > Console.WriteLine("\nRemoving a row with CustomerID of 'T1'");
> > > // Step 1: Set the PrimaryKey property for the DataTable object
> > > DataColumn[] myPrimaryKey = new DataColumn[1];
> > > myPrimaryKey[0] = MyDataTable.Columns["CustomerID"];
> > > MyDataTable.PrimaryKey = myPrimaryKey;
> > > // Step2: Use the Find() method to locate the DataRow
> > > DataRow myRemoveDataRow = MyDataTable.Rows.Find("T1");
> > > // Step 3: Use the Delete() method to remove the DataRow
> > > myRemoveDataRow.Delete();
> > > // step 4: Use the AcceptingChanges() method of the DataTable to
commit
> > the
> > > changes
> > > //MyDataTable.AcceptChanges();
> > > }
> > >
> > > public static void Main()
> > > {
> > > string connectionString =
> > > @"Provider=Microsoft.Jet.OLEDB.4.0;" +
> > > @"Data Source=" + // Izvor podataka = putanja .mdb datoteke
> > > @"C:\base\Customers.mdb";
> > >
> > >
> > > // Create a OleDBConnection object to connect to the DB
> > > OleDbConnection MyConn = new OleDbConnection(connectionString);
> > > // Formulate a SELECT statement to retrieve the database, passing the
> > > connection string to the constructor
> > > string selectString =
> > > "SELECT CustomerID, CompanyName, ContactName, Address " +
> > > "FROM CustomersTable";
> > > // Create OleDB Command to hold the SELECT statement
> > > OleDbCommand myCommand = MyConn.CreateCommand();
> > > myCommand.CommandText=selectString;
> > > // Set the command text property of the Sqlcommand object to the
SELECT
> > > string
> > > OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter();
> > > // Set the SelectCommand property of the OleDbAdapter object ot the
> > > OleDbCommand object
> > > myOleDbDataAdapter.SelectCommand = myCommand;
> > >
> > > // Create a DataSet object to store the results of the SELECT
statement
> > > DataSet myDataSet = new DataSet();
> > > // Open the database connection using the Open() method of the
> > SqlConnection
> > > object
> > > MyConn.Open();
> > > // Use the Fill() method of the OleDbDataAdapter objekt to retreive
the
> > > // rows from the table, storing the rows localy in the DataTable
> > > // of the dataSet object
> > > Console.WriteLine("Retrieving a row from the CustomersTable...");
> > >
> > > myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
> > > // Get the DataTable object from the DataSet object
> > > DataTable myDataTable = myDataSet.Tables["CustomersTable"];
> > > // Display the rows in the DataTable object
> > > DisplayDataTable(myDataTable);
> > > // Add a new row
> > > AddRow(myDataTable);
> > > DisplayDataTable(myDataTable);
> > > // Modify a row
> > > ModifyRow(myDataTable);
> > > DisplayDataTable(myDataTable);
> > > // Removing the new row
> > > //RemoveRow(myDataTable);
> > >
> > > //DisplayDataTable(myDataTable);
> > > // Use the Fill() to the OleDbDataAdapter object to synchronize the
> > changes
> > > with the data base
> > > // myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
> > >
> > > myOleDbDataAdapter.Update(myDataSet, "CustomersTable");
> > > // myOleDbDataAdapter.Update(myDataTable);
> > >
> > > // Close database connection
> > > MyConn.Close();
> > > }
> > > }
> > > }
> > >
> > >
> > >
> > > "Nicholas Paldino [.NET/C# MVP]" <mvp@spam.guard.caspershouse.com>
wrote
> > in
> > > message news:O6EGEtCDEHA.1600@tk2msftngp13.phx.gbl...
> > > >     The problem seems to come from the fact that you add the row,
and
> > then
> > > > call fill again, which effectively gets rid of any changes that you
> had
> > > > before.  Also, as Dmirty pointed out, you are calling AcceptChanges
in
> > the
> > > > wrong place.  You shouldn't call it at all, the adapter will do it
> when
> > > you
> > > > call Update.
> > > >
> > > >     If you remove the call to Fill before the update, it should
work.
> > > >
> > > >     Hope this helps.
> > > >
> > > >
> > > > -- 
> > > >                - Nicholas Paldino [.NET/C# MVP]
> > > >                - mvp@spam.guard.caspershouse.com
> > > >
> > > > "mp" <plamir@volja.net> wrote in message
> > > > news:uIZNeqCDEHA.3784@TK2MSFTNGP10.phx.gbl...
> > > > > Unfortunately it doesn't work.
> > > > > I have removed all AcceptChanges and I have used
> > > > > myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
> > > > >
> > > > >
> > > > >
> > > > > "Dmitriy Lapshin [C# / .NET MVP]"
<x-code@no-spam-please.hotpop.com>
> > > wrote
> > > > > in message news:uozbTaCDEHA.1456@TK2MSFTNGP09.phx.gbl...
> > > > > > Hi,
> > > > > >
> > > > > > > How I can sysnchronize changes in DataTable with my
Database???
> > > > > > >
> > > > > > > What is wrong here ???
> > > > > >
> > > > > > The call to AcceptChanges is wrong. It marks all the rows as
> > > "Unchanged"
> > > > > and
> > > > > > that effectively prevents the DataAdapter to propagate any
changes
> > to
> > > > the
> > > > > > database.
> > > > > >
> > > > > > -- 
> > > > > > Dmitriy Lapshin [C# / .NET MVP]
> > > > > > X-Unity Test Studio
> > > > > > http://www.x-unity.net/teststudio.aspx
> > > > > > Bring the power of unit testing to VS .NET IDE
> > > > > >
> > > > > > "mp" <plamir@volja.net> wrote in message
> > > > > > news:OAOOPVCDEHA.1604@TK2MSFTNGP11.phx.gbl...
> > > > > > > I am working with OleDB Access DB and CSharp.
> > > > > > >
> > > > > > > How I can sysnchronize changes in DataTable with my
Database???
> > > > > > >
> > > > > > > What is wrong here ???
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
============================================================================
> > > > > > >
> > > > > > > public static void AddRow(DataTable myDataTable)
> > > > > > > {
> > > > > > >  Console.WriteLine("\nAdding a new row with CustomerID of
> 'T1'");
> > > > > > >  // Step 1: Use the NewRow() method of the DataRow object to
> > create
> > > a
> > > > > new
> > > > > > > row in the DataTable
> > > > > > >
> > > > > > >  DataRow myNewDataRow = myDataTable.NewRow();
> > > > > > >  // Step 2: Set the values for the columns of the new row
> > > > > > >  myNewDataRow["CustomerID"] = "T1";
> > > > > > >  myNewDataRow["CompanyName"] = "T1 Company";
> > > > > > >  myNewDataRow["ContactName"] = "Jason Price";
> > > > > > >  myNewDataRow["Address"] = "1 Main Street";
> > > > > > >
> > > > > > >  // Step 3: Use the Add() method through the Rows property to
> add
> > > the
> > > > > new
> > > > > > > DataRow to the DataTable
> > > > > > >  myDataTable.Rows.Add(myNewDataRow);
> > > > > > >
> > > > > > >  // Step 4: Use the AcceptChanges() method of the DataTable to
> > > commit
> > > > > > >  myDataTable.AcceptChanges();
> > > > > > > }
> > > > > > >
> > > > > > >
> > > > > > > In main
> > > > > > > open etc...
> > > > > > >
> > > > > > > // Call Method and add a new row
> > > > > > > AddRow(myDataTable);
> > > > > > > DisplayDataTable(myDataTable);
> > > > > > >
> > > > > > > I have try to use the Fill() to the OleDbDataAdapter object to
> > > > > synchronize
> > > > > > > the changes with the data base but without success.
> > > > > > > myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
> > > > > > >
> > > > > > > I have try to use update as follows but without success.
> > > > > > > myOleDbDataAdapter.Update(myDataSet, "CustomersTable");
> > > > > > >
> > > > > > > // Close database connection
> > > > > > > MyConn.Close();
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: How I can sysnchronize changes in DataTable with my Database?
    ... What is the purpose of this apporach if we use plain SQL commands? ... >> DataTableMapping tableMapping) ... >> foreach(DataRow myDataRow in myDataTable.Rows) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Post Revised: A Desperate Plea for Help
    ... I got your point that generated sql is often inadequate. ... generated commands may be inadequate" but "Use the dataadapter's Update ... That's why my advice was to call the data adapter's Update method. ... But the bottom line here is that you need to create the SQL Statements ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: DataGrid problem.
    ... (and SQL) ... as well as the Insert and Update commands? ... > dataAdapters sql commands parameters Value field, ... >> to the grid, it is automatically assigned a NULL value when a new row is ...
    (microsoft.public.dotnet.framework.windowsforms.databinding)
  • Re: loading tables via SQL script
    ... and all the SQL commands used are valid for MS Access, ... from Wordpress used on a now closed team website. ... of the Wordpress blog used to maintain the website. ...
    (microsoft.public.access.gettingstarted)
  • Re: Mixing select and print statements using DBI, DBD::Sybase
    ... Isql does not have its own commands. ... in the sql batch has the print executing later. ...
    (perl.dbi.users)