System.Data.SQLite

Hi all...I have a table that looks like:

CREATE TABLE [itemDefinition] ([assetID] INTEGER PRIMARY KEY NOT NULL,[name] VARCHAR(100)  NULL,[number] VARCHAR(100) NULL, [description] VARCHAR(200) NULL,[uom] VARCHAR(5) NULL,[type] VARCHAR(100)  NULL,[unit_cost] VARCHAR(10) NULL, [weight] INTEGER  NULL,[material] VARCHAR(20) NULL)

I have an XML file that has "name" and "uom" as common attributes with my table

I've tried creating a command as such:  "Update itemDefinition set uom=@uom where name=@name";
and then assigning that to my DataAdapter.UpdateCommand.  This however, does not cause any update in my database table when I know that there are matching name values between the xml and the table.  My C# code is as follows:

           DataSet ds=new DataSet();
            ds.ReadXml(filename);
            DBSingletonConnectionManager cm = DBSingletonConnectionManager.GetInstance();
            if (cm.SqlConnection.State.ToString() == "Closed")
                cm.SqlConnection.Open();

           DataTable dt= ds.Tables[0];
           string qry = "Select * from ItemDefinition";
           try
           {
               SQLiteDataAdapter da = new SQLiteDataAdapter();
               SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da);
               da.SelectCommand = new SQLiteCommand(qry, DBSingletonConnectionManager.GetInstance().SqlConnection);
 
               string cmd = "UPDATE itemDefinition SET uom=@uom WHERE name=@name";
               SQLiteCommand sqlcmd = new SQLiteCommand(cmd);
               da.UpdateCommand = sqlcmd;
               da.MissingSchemaAction = MissingSchemaAction.Ignore;
               da.MissingMappingAction = MissingMappingAction.Passthrough;

               foreach (DataRow row in dt.Rows)
               {
                   row.BeginEdit();
                   row["name"] = row["name"].ToString().Trim();
                   row.EndEdit();
                   row.AcceptChanges();
               }
               dt.AcceptChanges();
               using (DbTransaction trans = DBSingletonConnectionManager.GetInstance().SqlConnection.BeginTransaction())
               {
                   try
                   {
                       da.Update(dt);
                       trans.Commit();
                   }
                   catch (SQLiteException)
                   {
                       trans.Rollback();
                       trans.Dispose();
                   }
                   finally
                   {
                       DBSingletonConnectionManager.GetInstance().SqlConnection.Close();
                   }
               }
           }
           catch (Exception e)
           {
               Console.WriteLine("Error: " + e);
           }

Can anyone tell me what I'm doing wrong that would cause my database not to update?  I'm using similar code to copy data from one database to another and that is working fine.

Thanks!
Wayne

So long as a CommandBuilder is associated with a data adapter, you cannot change the Update/Delete/Insert commands on the adapter.  See this KB Article.  Furthermore, your update command is incomplete -- you've created a parameterized query, but added no parameters to the command.  The paramters added to the command are what map the columns in the datatable to the columns in the database.

Robert

 

 

Hi Robert...

I did try adding parameters to the command after I made the initial post but that had no effect either.  I tried going this route of overriding the update command because the default update command was failing to do anything either.  Would that have been because the "name" column is not a primary key in my DB table?

 And so the only efficient way to do this is to loop the dataset or the XML directly and make mupltiple calls to an ExecuteNonQuery after my command has been built, as you show in your bulk loading howto?

Thanks!
Wayne

First, are you trying to copy the data from its XML source into the destination DB, or are you trying to update the database based on the XML file?

If you're inserting the XML into the table, then you need to completely rewrite this code -- which is actually pretty simple and straightforward.

If you're really intending to update, then you need to remove those pesky AcceptChanges() lines in your code.

AcceptChanges() erases the DataTable's internal "to do" list of what has been altered, inserted or deleted from the datatable.  By the time you call the DataAdapter's Update() command, because you've already accepted all the changes, the DataTable thinks there's nothing left to do.

Robert

 

The intended workflow is to update data in the DB table based on data from the XML file.  Any node block with a "name" that matches a "name" in the database should be updated with the information from the XML.

I tried removing the AcceptChanges() and just calling the da.Update() without manually specifiying an update command, but that failed to cause my table to update.  I then tried setting the update command with the GetUpdateCommand(true) method.  Again, no effect.  Then I tried using my manual update command with the parameters added like follows:

               string cmd = "Update itemDefinition set uom=@uom where name=@name";
               SQLiteCommand sqlcmd = new SQLiteCommand(cmd);
               sqlcmd.Parameters.Add(new SQLiteParameter("@uom", SqlDbType.VarChar));
               sqlcmd.Parameters["@uom"].SourceVersion = DataRowVersion.Current;
               sqlcmd.Parameters["@uom"].SourceColumn = dt.Columns["uom"].ColumnName;
               sqlcmd.Parameters.Add(new SQLiteParameter("@name", SqlDbType.VarChar));
               sqlcmd.Parameters["@name"].SourceVersion = DataRowVersion.Current;
               sqlcmd.Parameters["@name"].SourceColumn = dt.Columns["name"].ColumnName;

Also, no effect on my DB table.

I know I can do this by looping through my XML, building a hashtable and then sending the hashtable to another method which builds the correct command, but I was really hoping to do this via the dataset psudeo introspection that is built in.  The goal is to be able to select pretty much any XML file, and as long as there is a "name" node for the xml to match against what's in the DB table, it will attempt to update the database where ever the two schemas match up.

Wayne

You need to not assign the dataadapter to the commandbuilder.  When you do that, the CommandBuilder will automatically reset the UpdateCommand to its computed default value as the KB article explains.

Fill your dataset with the select command, then manually assign your update command.  Don't use CommandBuilder at all.  Also, you can't use SqlDbType -- that's a SqlClient enum.  Use DbType instead. 

Robert

 

Hi Robert...

I tried doing what you suggested and still...no effect.  Right now my code looks like:

            DataSet ds=new DataSet();
            ds.ReadXml(filename);
            DBSingletonConnectionManager cm = DBSingletonConnectionManager.GetInstance();
            if (cm.SqlConnection.State.ToString() == "Closed")
                cm.SqlConnection.Open();

           DataTable dt= ds.Tables[0];
           string qry = "Select * from ItemDefinition";
           try
           {
               SQLiteDataAdapter da = new SQLiteDataAdapter();
               da.SelectCommand = new SQLiteCommand(qry, DBSingletonConnectionManager.GetInstance().SqlConnection);
               string cmd = "Update itemDefinition set uom=@uom where name=@name";
               SQLiteCommand sqlcmd = new SQLiteCommand(cmd);
               sqlcmd.Parameters.Add(new SQLiteParameter("@uom", DbType.String));
               sqlcmd.Parameters["@uom"].SourceVersion = DataRowVersion.Current;
               sqlcmd.Parameters["@uom"].SourceColumn = dt.Columns["uom"].ColumnName;
               sqlcmd.Parameters.Add(new SQLiteParameter("@name", DbType.String));
               sqlcmd.Parameters["@name"].SourceVersion = DataRowVersion.Current;
               sqlcmd.Parameters["@name"].SourceColumn = dt.Columns["name"].ColumnName;

               da.UpdateCommand = sqlcmd;
               da.MissingSchemaAction = MissingSchemaAction.Ignore;
               da.MissingMappingAction = MissingMappingAction.Passthrough;
               da.ContinueUpdateOnError = true;
               foreach (DataRow row in dt.Rows)
               {
                   row.BeginEdit();
                   row["name"] = row["name"].ToString().Trim();
                   row.EndEdit();
                   if (row.RowState == DataRowState.Unchanged)
                       row.SetAdded();
               }

              using (DbTransaction trans = DBSingletonConnectionManager.GetInstance().SqlConnection.BeginTransaction())
              {
                   try
                   {
                       da.Update(dt);
                       trans.Commit();
                   }
                   catch (SQLiteException)
                   {
                       trans.Rollback();
                       trans.Dispose();
                   }
                   finally
                   {
                       DBSingletonConnectionManager.GetInstance().SqlConnection.Close();
                   }
               }
           }
           catch (Exception e)
           {
               Console.WriteLine("Error: " + e);
           }

Is there something I can query on or watch to see if there is some set of properties which report what is supposed to have been updated?

Wayne

Update....without the Commandbuilder, I get an error about an insert statement being required and with the Commandbuilder, I get an error about: Missing the DataColumn 'assetID' in the DataTable 'component' for the SourceColumn 'assetID'.

Component is the table name in the dataset after I load the XML and assetID is the primary key in the table of the DB where I'm tyring to perform the update. 

I've set the flags for MissingSchemaAction and MissingMappingAction, so I don't know why I'd be getting that error.

ADO.NET won't let you insert without referencing some kind of primary key.  Here's my recommendation:

• Change your schema to make the "name" column "unique not null"
• Change your SELECT clause to select all the columns except the AssetID primary key, and I think ADO.NET will use the "name" column as a primary key instead.
• Create your commandbuilder, assign it to the DataAdapter, then call GetInsertCommand() and manually assign it to the InsertCommand for the dataadapter.
• Reset the DataAdapter property of the CommandBuilder to NULL so CommandBuilder won't overwrite your UpdateCommand with its own.
• Set the UpdateCommand with your homebrew command.

 

Unfortunately, I can't set name to "unique".  There are a few rare circumstances where name may actually contain a duplicate value.  I could set name and assetID as the PK if that would help, since that combo will always be unique. 

Anyway, after examaning my code, I realized I had a logic error.  When this update would execute it would set existing values in my columns to "" if I did not give it a value--I can't lose data on an update.  So I changed things around a bit to load my XML into a dataset.  Then query my itemDefinition table where "name" equals the current row's name value.  I could then just update the columns that need to be updated.   I can't be hard code my update command and be selective on what columns get updated because the XML files may always have slightly different schemas and the columns that match the DB schema may always be different.  So my update command needs to built dynamically.

So my new code works on the first pass, but on subsequent passes, I get a System.Data.DBConcurrencyException.

My new code is as follows:

            DataSet ds=new DataSet();
            ds.ReadXml(filename);
            DBSingletonConnectionManager cm = DBSingletonConnectionManager.GetInstance();
            if (cm.SqlConnection.State.ToString() == "Closed")
                cm.SqlConnection.Open();

           DataTable xmlDT= ds.Tables[0];


           try
           {
              foreach (DataRow row in xmlDT.Rows)
               {
                   SQLiteDataAdapter da = new SQLiteDataAdapter();
                   SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da);
 
                   DataSet dbDS = new DataSet();
                  string qry = "select * from itemdefinition where name='" + row["name"].ToString().Trim() + "'";
                   da.SelectCommand = new SQLiteCommand(qry, DBSingletonConnectionManager.GetInstance().SqlConnection);
                   da.Fill(dbDS);
                   DataTable dbDT = dbDS.Tables[0];
                   foreach (DataColumn column in dbDT.Columns)
                   {
                       try
                       {
                           dbDT.Rows[0][column.ColumnName] = row[column.ColumnName].ToString().Trim();
                        }
                       catch (Exception)
                       {
                       }
                      
                   }
                   using (DbTransaction trans = DBSingletonConnectionManager.GetInstance().SqlConnection.BeginTransaction())
                  {
                       try
                       {

                           da.Update(dbDS);
                           da.Dispose();
                           cb.Dispose();
                           dbDT.Dispose();
                          
                           trans.Commit();
                       }
                       catch (SQLiteException)
                       {
                           trans.Rollback();
                           trans.Dispose();
                       }


                  }

               }
           }
           catch (Exception e)
           {
               Console.WriteLine("Error: " + e);
           }

Not sure what is causing the error...I know there is a row that is being updated on each pass as I can see the changes happening in the debugger.

Thanks!
Wayne

Goodness ... don't create a new commandbuilder and data adapter every time.  Just create them outside the loop in a using clause and re-use them inside the loop.  As long as you keep changing the Select command, those objects should take care of the rest.

Even better, make that select clause a parameterized query outside the loop, and just keep changing the parameter value inside the loop rather than rebuild a string every time.

If name can have multiple entries, and the XML doesn't have the AssetID, then what are you going to be expecting to update when two items have the same name in the table?

As for a concurrency violation, I'd need to see the entire stack trace.

Robert

 

Hi Robert...

Good point about the multiple rows in the same table.  Like I said, though, it was for a rare occasion, but thinking about it now, that rare occasion isn't something I really need to handle so I will add UNIQUE to the name definition. 

Based on your suggestions I've changed my code to:

           DataSet ds=new DataSet();
            ds.ReadXml(filename);
            DBSingletonConnectionManager cm = DBSingletonConnectionManager.GetInstance();
            if (cm.SqlConnection.State.ToString() == "Closed")
                cm.SqlConnection.Open();

           DataTable xmlDT= ds.Tables[0];
           try
           {
              SQLiteCommand cmd = new SQLiteCommand(DBSingletonConnectionManager.GetInstance().SqlConnection);
               cmd.CommandText = "select * from itemdefinition where name=@name";
               cmd.Parameters.Add(new SQLiteParameter("@name"));
               using (SQLiteDataAdapter da = new SQLiteDataAdapter())
               {
                   using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da))
                   {
                       foreach (DataRow row in xmlDT.Rows)
                       {
                           DataSet dbDS = new DataSet();
                           cmd.Parameters["@name"].Value=row["name"].ToString().Trim();
                           da.SelectCommand = cmd;
                           da.Fill(dbDS);
                           da.AcceptChangesDuringUpdate = true;
                           DataTable dbDT = dbDS.Tables[0];
                           foreach (DataColumn column in dbDT.Columns)
                           {
                               try
                               {
                                   dbDT.Rows[0][column.ColumnName] = row[column.ColumnName].ToString().Trim();
                               }
                               catch (Exception)
                               {
                               }

                           }

                           using (DbTransaction trans = DBSingletonConnectionManager.GetInstance().SqlConnection.BeginTransaction())
                           {
                               try
                               {

                                   da.Update(dbDT);
                                   da.Dispose();
                                   cb.Dispose();
                                   dbDT.Dispose();

                                   trans.Commit();
                               }
                               catch (SQLiteException)
                               {
                                   trans.Rollback();
                                   trans.Dispose();
                               }
                               catch (System.Data.DBConcurrencyException)
                               {
                               }


                           }


                       }
                   }
               }
           }
           catch (Exception e)
           {
               Console.WriteLine("Error: " + e);
           }



However I still get the DBConcurrencyError.  Stack trace is:

   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at Database.DBImportUtil.Import(String filename) in C:\Users\wayne\Documents\Visual Studio 2005\Projects\PC\Database\DBImportUtil.cs:line 144

BTW, thanks so much for all your help!
Wayne

Ooops...those dispose() commands should not be there...doesn't really matter at the moment since they're never getting called :)

This is starting to really drive me crazy!  I've tried all kinds of things and can't get past that stupid error.  Any idea what is causing it?  I know that the commandbuilder really isn't supposed to be used for production, but like I said, my XML files will almost always vary and I need the update command to be dynamically generated.

Wayne

Can you send me a test app, either a debug build so I can step through it, or a sample source code project so I can run it myself?  Make sure you include everything I'll need to run it properly.

robert at blackcastlesoft dot com