none
Can't get SQL Data Adapter Insert, Delte and Update commands to fill RRS feed

  • Question

  • I asked this under C# general, but this seems a more appropriate place.

    I've now tried this in both SQLCe and SQLite, but I can't get my DataAdapter's Update, Delete and Inset commands to populate correctly in the below code. As far as I can tell I've done it correctly, and my Select Query works correctly as it returns any data i manually enter into the database later on.

    DataTable CharacterData = new DataTable();
                        DataTable CharacterLevelData = new DataTable();
                        StringBuilder CreateTableQuery = new StringBuilder();
                        StringBuilder CreateLevelQuery = new StringBuilder();
                        CreateTableQuery.Append("CREATE TABLE " + newCharName.Text + "(");
                        CreateLevelQuery.Append("CREATE TABLE " + newCharName.Text + "Level (Level int PRIMARY KEY");
                        CharacterLevelData.Columns.Add("Level", typeof(int));
                        for (int i = 0; i < StatPages.Count; i++)
                        {
                            CharacterLevelData.Columns.Add(StatPages[i], typeof(int));
                            CreateLevelQuery.Append(", [" + StatPages[i] + "] int");
    
                        }
                        object[] rowdefaults = new object[StatPages.Count + 1];
                        for (int i = 1; i < rowdefaults.Length; i++)
                            rowdefaults[i] = 0;
    
                        for (int i = 0; i < 100; i++)
                        {
                            rowdefaults[0] = i + 1; 
                            CharacterLevelData.Rows.Add(rowdefaults);
                        }
    
                        for (int i = 0; i < SkillPages.Count; i++)
                        {
                            CharacterLevelData.Columns.Add(SkillPages[i]);
                            CreateLevelQuery.Append(", [" + SkillPages[i] + "] nvarchar(512)");
                        }
                        CreateLevelQuery.Append(")");
                        try
                        {
                            try
                            {
    
                                SQLConn.Open();
                            }
                            catch
                            {
                            }
                            SQLiteCommand command = new SQLiteCommand();
                            command.CommandText = CreateLevelQuery.ToString();
                            command.Connection = SQLConn;
                            try
                            {
                                command.ExecuteNonQuery();
                                //command.CommandText = "CREATE UNIQUE INDEX " + newCharName.Text + "_LEVEL_INDEX ON " + newCharName.Text + "Level (Level)";
                                //command.CommandText = "ALTER TABLE " + newCharName.Text + "Level ADD PRIMARY KEY (Level)";
                                //command.ExecuteNonQuery();
                            }catch
                            {}
                            SQLiteDataAdapter liteAdapter = new SQLiteDataAdapter("SELECT * FROM " + newCharName.Text + "Level", SQLConn);
                            //SqlCeDataAdapter adapater = new SqlCeDataAdapter("Select * From " + newCharName.Text + "Level", SQLConn);
                            DataSet ds = new DataSet();
                            //SqlCeCommandBuilder cBuild = new SqlCeCommandBuilder(adapater);
                            SQLiteCommandBuilder cBuild = new SQLiteCommandBuilder(liteAdapter);
                            cBuild.ConflictOption = ConflictOption.OverwriteChanges;
                            cBuild.QuotePrefix = "[";
                            cBuild.QuoteSuffix = "]";
                            cBuild.RefreshSchema();
    
                            CharacterLevelData.TableName = newCharName.Text + "Level";
                            ds.Tables.Add(CharacterLevelData);
                            
                            liteAdapter.InsertCommand.CommandText = cBuild.GetInsertCommand(true).CommandText;
                            liteAdapter.Update(CharacterLevelData);
                        }
                        catch
                        {
                        }
    

     

    The database is definitely being created as I can see it on the database front end, but the Update, Delete and Insert commands that are attached to the dataadapter never fill out. Is there something else I need to do? If nothing else, how would i manuaully build the commands? Particularly the update one. I know how to create an update command if I had a set of values, but my values are in the table, and I'm unsure how to reference them in the update command the database adapter would need.

    Thanks in advance
    • Edited by Gary Oak Wednesday, March 31, 2010 3:37 PM
    Tuesday, March 30, 2010 2:29 PM

Answers

All replies

  • I've now tried this in both SQLCe and SQLite, but I can't get it to populate my DataAdapter Update, Delete and Inset commands to populate correctly. As far as I can tell I've done it correctly, and my Select Query works correctly as it returns any data i manually enter into the database later on.

     

    DataTable CharacterData = new DataTable();
                        DataTable CharacterLevelData = new DataTable();
                        StringBuilder CreateTableQuery = new StringBuilder();
                        StringBuilder CreateLevelQuery = new StringBuilder();
                        CreateTableQuery.Append("CREATE TABLE " + newCharName.Text + "(");
                        CreateLevelQuery.Append("CREATE TABLE " + newCharName.Text + "Level (Level int PRIMARY KEY");
                        CharacterLevelData.Columns.Add("Level", typeof(int));
                        for (int i = 0; i < StatPages.Count; i++)
                        {
                            CharacterLevelData.Columns.Add(StatPages[i], typeof(int));
                            CreateLevelQuery.Append(", [" + StatPages[i] + "] int");
    
                        }
                        object[] rowdefaults = new object[StatPages.Count + 1];
                        for (int i = 1; i < rowdefaults.Length; i++)
                            rowdefaults[i] = 0;
    
                        for (int i = 0; i < 100; i++)
                        {
                            rowdefaults[0] = i + 1; 
                            CharacterLevelData.Rows.Add(rowdefaults);
                        }
    
                        for (int i = 0; i < SkillPages.Count; i++)
                        {
                            CharacterLevelData.Columns.Add(SkillPages[i]);
                            CreateLevelQuery.Append(", [" + SkillPages[i] + "] nvarchar(512)");
                        }
                        CreateLevelQuery.Append(")");
                        try
                        {
                            try
                            {
    
                                SQLConn.Open();
                            }
                            catch
                            {
                            }
                            SQLiteCommand command = new SQLiteCommand();
                            command.CommandText = CreateLevelQuery.ToString();
                            command.Connection = SQLConn;
                            try
                            {
                                command.ExecuteNonQuery();
                                //command.CommandText = "CREATE UNIQUE INDEX " + newCharName.Text + "_LEVEL_INDEX ON " + newCharName.Text + "Level (Level)";
                                //command.CommandText = "ALTER TABLE " + newCharName.Text + "Level ADD PRIMARY KEY (Level)";
                                //command.ExecuteNonQuery();
                            }catch
                            {}
                            SQLiteDataAdapter liteAdapter = new SQLiteDataAdapter("SELECT * FROM " + newCharName.Text + "Level", SQLConn);
                            //SqlCeDataAdapter adapater = new SqlCeDataAdapter("Select * From " + newCharName.Text + "Level", SQLConn);
                            DataSet ds = new DataSet();
                            //SqlCeCommandBuilder cBuild = new SqlCeCommandBuilder(adapater);
                            SQLiteCommandBuilder cBuild = new SQLiteCommandBuilder(liteAdapter);
                            cBuild.ConflictOption = ConflictOption.OverwriteChanges;
                            cBuild.QuotePrefix = "[";
                            cBuild.QuoteSuffix = "]";
                            cBuild.RefreshSchema();
    
                            CharacterLevelData.TableName = newCharName.Text + "Level";
                            ds.Tables.Add(CharacterLevelData);
                            
                            liteAdapter.InsertCommand.CommandText = cBuild.GetInsertCommand(true).CommandText;
                            liteAdapter.Update(CharacterLevelData);
                        }
                        catch
                        {
                        }

     

    The database is definitely being created as I can see it on the database front end, but the Update, Delete and Insert commands that are attached to the dataadapter never fill out. Is there something else I need to do? If nothing else, how would i manuaully build the commands? Particularly the update one. I know how to create an update command if I had a set of values, but my values are in the table, and I'm unsure how to reference them in the update command the database adapter would need.

    Thanks in advance

    Tuesday, March 30, 2010 2:21 AM
  • Hi Gary,

    Does the new table has one primary key ?

    The SelectCommand must return at least one primary key or unique column. If none are present, the commands are not generated when using a commandbuilder to create the Update, Delete and Inset commands. Please have a check.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, March 31, 2010 9:07 AM
    Moderator
  • Hi Alex, I thought I was creating a priamry key, but on closer inspection it appears I wasn't. I modified my code (you can see the changes above), and my first part of my table creation command is now

     CreateLevelQuery.Append("CREATE TABLE " + newCharName.Text + "Level (Level int PRIMARY KEY");
    

    I ran through the code again, and the table creation command executes successfully. I checked the database, and the Level field is showing up as the primary key. I removed all potential spaces from column names to be on the safe side, but the problem is still there.

    Any ideas?
    Wednesday, March 31, 2010 11:59 AM
  • I see that you have created a UNIQUE INDEX, but I believe that's not the same thing as a PrimaryKey. In order to use the .Update() method of the DataAdapter, you need to have a PrimaryKey defined in your database. Now, I'm not familiar with the differences between full SQL Server and SQLite, so I'm not 100% sure that this is your problem, but it's something to look at.

    You might want to take a quick look at my blog 3-part series on DataAccess. The first part mentions the .Update() method and CommandBuilder and alternatives methodologies:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Gary Oak Tuesday, April 6, 2010 1:13 PM
    Wednesday, March 31, 2010 2:47 PM
  • Hi,

    Thanks for the help. I wasn't creating a primary key, so I went back and modified my code so that it creates a primary key on the Level coloumn. I edited my original post to show the changes to the code. I then ran the program again, but the Update/Insert/Delete fields still won't fill out. I verfied the table is successfully created, and that Level is marked as the Primary index.

      I'd really like to get the update command working. Once the initial set of rows is made, I won't be adding/deleting new rows, but I will be modifying the value of the rows often.
    Wednesday, March 31, 2010 3:40 PM
  • Gary,

    The CommandBuilder classes can generate updating logic at run-time, but using your own updating logic will give you much better performance since the queries that CommandBuilders execute to retrieve schema information are rather costly.  It looks like you understand the table schema you're working with and how you want to perform updates (PK-only in the where clause since you're manually setting ConflictOption to OverwriteChanges).  The SQL for INSERT, UPDATE and DELETE queries are isn't much more complex than the CREATE TABLE queries you're already executing.  With that said, if you do wish to stick with CommandBuilders, there's no need to manually set the DataAdapter's InsertCommand, UpdateCommand or DeleteCommand properties.  Once you've linked the CommandBuilder and DataAdapter, the CommandBuilder listens for DataAdapter events and handles them appropriately.

     

    I do see where you're creating the CharacterLevelData DataTable and later calling DataAdapter.Update, but I don't see any code that creates any inserted, modified or deleted rows in the DataTable.  Is there an actual exception being thrown?  If so, what are the contents of the exception?  Based on the code you posted, it looks like the DataTable contains no changes so there's no attempt on the part of the DataAdapter to submit any updates.  If you could post a small sample that reproduces your problem using an existing table to simplify the code, someone might be able provide an explanation and offer potential solutions.

     

    I hope this information proves helpful.


    David Sceppa
    Wednesday, March 31, 2010 5:43 PM
    Moderator
  • David -- Gary is already adding Rows to his DataTable ... I missed it the first time I looked at his code too.

    Gary -- I agree with David's assessment of the CommandBuilder. Personally, I never use it (if you read my blog posts, you already know that).

    Next question ... have you've verified that nothing is getting added to your database table? Or were you just looking at the Insert command that the CommandBuilder generated and not seeing anything of use there? Because you won't see what you expect to see there, as it all happens automagically when the .Update() method runs.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, March 31, 2010 7:36 PM
  • Hi Bonnie,

    I can verify that nothing is getting added to my database because the Insert, Update and Delete commands are all null. The select command is filled correctly though. Because of this, the exception being thrown if of course that the Insert Command doesn't exist.

    David, as bonnie said I am adding data. I add 100 rows into the datatable. The first Column is Level and has the values 1...100. The next set of columns are set to 0. I then add a few additional coloumns which are empty strings.

     I'll try manually building the commands, I was just hoping to be able to use the Adapater's update command as theres less chance for slip ups, and adding those commands is going to be tedious. I'll also try targetting a few different database types to see if this problems still shows up.

    Thursday, April 1, 2010 1:53 PM
  • Gary,

    I wonder if the problem is with SQLCe and SQLite version of the CommandBuilder? I would think it odd if they both are having the same problem though. I know it works in regular SQL, but that's no help to you if you need to use the "light" versions.

    Adding the commands manually shouldn't be too tedious ... a lot of it can be done in a loop through your Rows collection.

    If you feel like it, you could zip up your solution and email it to me (the SQLCe version, I don't have SQLite) and I can take a quick look at it when I get some free time later today. My email is bonnieb at profitware-online dot com.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, April 1, 2010 3:09 PM
  • Gary,

    The simplest way to get to the root of the problem is to create and post a simple repro.  Based on the information you've provided so far, it sounds like you should see the same behavior with a very simple table (PK and one other column) and adding one or two rows.  Here's the code I used to make the basic scenario work with SQL Server Compact:

     

    string databaseFile = "Test.sdf";
    
    if (System.IO.File.Exists(databaseFile))
        System.IO.File.Delete(databaseFile);
    
    //Create the database
    string connectionString = string.Format("Data Source='{0}'", databaseFile);
    SqlCeEngine engine = new SqlCeEngine(connectionString);
    engine.CreateDatabase();
    
    //Connect
    SqlCeConnection connection = new SqlCeConnection(connectionString);
    connection.Open();
    SqlCeCommand command = connection.CreateCommand();
    
    //Create the table
    command.CommandText = "CREATE TABLE CantGetAdapterToInsert " +
                          " (ID int PRIMARY KEY, OtherField nvarchar(255))";
    command.ExecuteNonQuery();
    
    //Create the DataAdapter, construct the DataTable
    command.CommandText = "SELECT ID, OtherField FROM CantGetAdapterToInsert";
    SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);
    DataTable table = new DataTable();
    dataAdapter.Fill(table);
    
    //Add a row
    table.Rows.Add(1, "First Row");
    
    //Create updating logic via a CommandBuilder
    SqlCeCommandBuilder commandBuilder = new SqlCeCommandBuilder(dataAdapter);
    
    //Make sure there are pending inserts
    Debug.Assert(table.GetChanges(DataRowState.Added).Rows.Count > 0,
        "Something's wrong.  There are no DataRows marked as Added.");
    
    //Submit the pending changes
    dataAdapter.Update(table);
    
    //Check to see how many rows exist in table after update
    command.CommandText = "SELECT COUNT(ID) FROM CantGetAdapterToInsert";
    int rowsInTable = (int)command.ExecuteScalar();
    if (rowsInTable == 1)
        Console.WriteLine("Successfully submitted one row to database");
    else
        Console.WriteLine("Something's wrong.  {0} row(s) in database table", rowsInTable);

     

     

    I hope this information proves helpful.


    David Sceppa
    Thursday, April 1, 2010 10:09 PM
    Moderator
  • Thanks guys, I was able to get it working.
    Tuesday, April 6, 2010 1:12 PM
  • How did you solve the problem, Gary? Maybe just a quick explanation for the lurkers ... =0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, April 6, 2010 3:20 PM