Question SqlCommandBuilder / SqlDataAdapter Combination Question

[fristomer]

I am struggling to get the Database to update after inserting a new row into the table. My question is, what exactly do the two items in the title do? Which do I need to work with to update the database?
(I know this excerpt is very limiting. If anyone wants the full section of code for testing purposes, please let me know.)

public class Connector {
    private SqlConnection Con { get; } = new SqlConnection("Connection String");
    private SqlDataAdapters[] Adapters;
    private DataSet Data;
    public Connector() {
        // Build SqlDataAdapters and Data
        // Note: All SqlDataAdapters are built with the "SELECT" command.
        // Does this matter? Do I need to modify them in the Update call?
    }
    public void UpdateDatabase(int tableId = 0) {
        // Note: This code works great when adding rows to tables in
        // another section of the code, but fails during the section in question.
        SqlCommandBuilder cb = new SqlCommandBuilder(Adapters[tableId]);
        cb.DataAdapter.Update(Data.Tables[tableId]);
    }
}

public partial class wdoCompany : Form {
    Connector Companies { get; set; }
    private int[] ComboArray {get; } = new int[] { 17, 18, 19, 20 }
    private void btnSave_Click(object sender, EventArgs e) {
        if (RequirementsMet()) {
            DataRow row = Companies.Data.Tables[0].NewRow();
            row["Company"] = txtCompanyName.Text;
            row["Website"] = txtWebsite.Text;
            row["CompanyType"] = ComboArray[cboCompanyType.SelectedIndex];
            
            Companies.UpdateDatabase();
        }
    }
}

 

[jmcilhinney]

You need to specify what the error message is. That will likely tell you/us what the actual problem is. One potential problem is that your table doesn't have a primary key, or that it does but your query doesn't include it. In that case, the command builder cannot generate an UpdateCommand or a DeleteCommand, because it cannot uniquely identify a record to update or delete. It should still be able to generate an InsertCommand though. Even if your query includes the PK, you should set the MissingSWchemaAction property of the data adapter to AddWithKey - the default is Add - if it is generating the columns in your DataTable. That's just a guess though, because you haven't provided us with the diagnostic information that the system provided to you.

It would also help if you provided a minimal, reproducible example, which means that you provide all the code required the reproduce your issue and none that isn't. It doesn't have to be code from your actual project or even closely resemble it, e.g. you don't need any arrays to demonstrate your issue. If you have to create a separate test project to isolate the problem functionality then that's what you do. You probably already should have done that for yourself anyway. isolating problem functionality by removing noise is an important part of debugging many issues.

 

[fristomer]

There is no error. It is simply stepping through the Update command without actually updating the database. There is a PK, though it is automatically generated with the "IDENTITY(1,1)" feature of the Database. Therefore, it should not be needed (to the best of my knowledge). While debugging, I have encountered the adapter, stepped through it, and it still says "SELECT * FROM Companies." My source question is: Is that the root of my problem?

Thank you for the advice on debugging. I have tried recreating the project using a few minor modifications, but it is the same step that is bugging out. I will create a testing sample and attach it later today. How should I do that? As a VS Project or C# file(s)? Still new to the Forum and asking for help...

*** removed unnecessary quote ***

 

[fristomer]

Here is the test file. Let me know if anything else could be useful...

 

[jmcilhinney]

If there's no exception thrown then the code is working, so it's your expectations that are wrong. The next step is to see what value is returned by the Update call. That value is the number of records affected in the database. If that number is not zero then changes are being saved and, if you're not seeing them, you're either looking in the wrong place or in the right place at the wrong time. This is far more common than you might think. If that number is zero then there are no changes in the DataTable to save in the first place, so either you've not added any changes or you've already accepted them. Are you calling AcceptChanges anywhere?

As for the identity column, that's irrelevant for inserting new records. The requirement for a PK is, as I said, for generating an UpdateCommand and a DeleteCommand. If you're not trying to update or delete existing records then those commands don't matter, whether they can be generated or not.

 

[fristomer]

*** removed unnecessary quote ***

I have tried the AcceptChanges command, to the same result. What I don't understand is, the UpdateDatabase method is
working elsewhere in the code. For example, if the Database does not yet exist, I have a method creating it. The UpdateDatabase method is called from within this "CreateDatabase" method and it works as expected. However, when I add rows to the table through another "Connector" object, it is not working as expected.

Thank you so much for your assistance with this, by the way. I sincerely appreciate your attention.

 

[Skydiver]

Side note. This is a traditional style forum where post are in sequential linear order. There is no real need to quote the post above your reply, unless there is a specific thing you want to address or highlight. In that case, quote and trim down to the specific thing..

 

[fristomer]

Side note noted. Still learning... lol. Sorry for the inconvenience.

 

[Skydiver]

However, when I add rows to the table through another "Connector" object, it is not working as expected.

Perhaps this is the issue. Object instances are separate from each other. One instance of a Connector object will be completely separate from another instance, unless you bend over backwards to make the two instances talk to each other, or have those two instances just be proxies for a singleton object.

 

[fristomer]

I understand what you're stating about the different objects of Connector class... but they both target the same database/table. While the first instance is destroyed (out of scope) after creating the Database, the second instance is "INTENDED" to be just a segment of the database--the one table. I know that the intent is the problem, which is why it's emphasized.

It is also important to note that I also tried the Connector class as a static class. With no instance required, a few minor details were different... but I was able to get the EXACT same result. This is why I'm wondering if the SqlDataAdapter is the problem. Do I need to change the statement with every use? Perhaps I can have an overloaded method with SQL statement parameters for a new adapter every time the Update command is called? I will try this for now, but I would like to know your guys' thoughts on efficiency/accuracy/overall effectiveness of this method?

 

[Skydiver]

Just my opinion, but I feel that a lot of people have moved away from using DataSet (both strongly typed and otherwise), DataTable, SqlAdapter, and SqlCommandBuilder for any new code. They would tend to go with using Entity Framework or some other ORM, or gone lightweight by talking to the database directly, or somewhere in between with Dapper and/or AutoMapper.

 

[fristomer]

If there's no exception thrown then the code is working, so it's your expectations that are wrong.

As expected, you were right. I figured it out. I was skipping a step: DataTable.Rows.Add(newRow). Sometimes the obvious is the hardest thing to see. Thanks for helping me out!

 

[jmcilhinney]

Sometimes the obvious is the hardest thing to see.

Which is why you need to be systematic in your testing and debugging. The correct way to approach this, which you aren't necessarily to know starting out, was to first check the result of Update and, if it was zero, to then examine the contents of the DataTable before calling it. You'd then see that the expected rows were not present so you would go back to where you expected them to be added. If you couldn't see the fault with the code right away, you'd then step through it with the debugger. You'd see that no row was actually added to the table so you'd either know that you forgot to call Add or you'd be able to tell us exactly what was and wasn't happening and we'd be able to tell you straight out.

You should have known that Update returned the number of rows affected because, when you were calling it and it wasn't behaving as you expected, you should have read the documentation for that method, if not before even. You can click a type or member name in the code editor and press F1 to go straight to the documentation for that type or member, so there's really no excuse to not to do that reading. You'll learn a lot from doing that, and that includes information that you won't use right now but will already know when you need it in the future. ALWAYS read the relevant documentation. Use context-sensitive Help (F1) often and also favourite/bookmark the home page in your browser. Here is the Australian page that I use. You can just change the culture in the URL to find your own page and then use it to search for specific things.