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

From: mp (plamir_at_volja.net)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 16:32:31 +0100

Normaly we a not able to made changes when is no connection.

I like SQL but I would like to learn some other approach to change data.
As you described I want to give the data and primary key and get the updated
data acording on changes in DataTable.

I am litle confusing with SqlBuilder class. How I can use it in the example?

Thanks

"Dmitriy Lapshin [C# / .NET MVP]" <x-code@no-spam-please.hotpop.com> wrote
in message news:uR1aiYODEHA.3344@tk2msftngp13.phx.gbl...
> > 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: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Re: User not associated with trusted SQL Server connection
    ... > using the osql utility. ... > associated with a trusted SQL Server connection. ... > database or is it just for that instance. ...
    (microsoft.public.sqlserver.security)
  • RE: Manipulating MS Access records with excel VBA + ADO
    ... While I cannot see how a working example of a connection to a database is ... Select the control button view dataor edit query. ... Look for SQL button to get the SQL ...
    (microsoft.public.excel.programming)
  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... has been done with parameterised SQL, ... bit of code into my test which closes and reopens the database ... opening and closing the DB connection is not a good idea. ... // Display all error messages ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ... It wouldn't seem to me that you'd need a new connection to ...
    (microsoft.public.sqlserver.ce)