How do I use a dataadapter to update just one row of a DQL table?

Question

0.00/5 (No votes)

See more:

I’m trying to update one row of a SQL database from a DataTable.
In Variables.cs I have

public static DataSet PeopleDS = new DataSet();
        public static DataTable PeopleTable = PeopleDS.Tables.Add("PeopleTable");
        public static SqlDataAdapter PeopleDA;

In the main body I have:-

Variables.PeopleDA = new SqlDataAdapter();
Variables.PeopleDA.SelectCommand = new SqlCommand ("SELECT * FROM People" ,con0);
Variables.PeopleDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
Variables.PeopleDA.Fill(Variables.PeopleDS, "People");
Variables.PeopleTable = Variables.PeopleDS.Tables["People"];

After a selected person is found that row of the table is read into a DataRow variable:

   SelectString = "PersonID = " + WBID.ToString();
foundrows = Variables.PeopleTable.Select(SelectString);
   ThisRow = foundrows[0];

And values passed to a form:

textBox1.Text = ThisRow["FullName"].ToString();
            textBox3.Text = ThisRow["PersonalAddress"].ToString();
            textBox4.Text = ThisRow["EmailAddress"].ToString();
            textBox5.Text = ThisRow["EmailAddress2"].ToString();
            textBox6.Text = ThisRow["Details"].ToString();
            textBox7.Text = ThisRow["EmployeeNo"].ToString();
etc etc.

This form can then be edited and any changed values are passed back to a DataRow:

             ThisRow = Variables.PeopleTable.Select("PersonID=" + textBox17.Text).FirstOrDefault();
                    ThisRow["Title"] = comboBox2.SelectedItem;
                    ThisRow["CompanyID"] = Convert.ToInt16(comboBox3a.SelectedValue.ToString());
Etc etc

I’ve checked and so far so good, the DataTable has the changed values.
I can’t see how to run a SQL update for just this record – to find the correct row in the SQL table and simply copy the DataRow back. Having to write a SQL UPDATE statement listing every field would make DB changes require code changes.

What I have tried:

Started writing the complete UPDATE statement before realising its problems
Tried Variables.PeopleDA.Update(Variables.PeopleDS) -this gives System.InvalidOperationException

Posted 15-Oct-21 17:02pm

ormonds

Updated 15-Oct-21 17:38pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2021-10-15T17:38:00

As you wrote, one way is to write the SQL statements which means that you need to specify the fields in the statement. While this means a code change when the schema in the database changes, you may need to change code also for other reasons when a new column is added or an existing changed. For example, if the new column is included in some validations, participating in different kinds of business logic etc.

Having that said, you can also utilize a command builder to build the statements for you. This requires that sufficient metadata is present in the database, for example key information. For more information, have a look at SqlCommandBuilder Class (System.Data.SqlClient) | Microsoft Docs[^]

The capabilities of a data adapter (and command builder) are quite limited so if you need more complex logic in the data access layer, you may want to have a look at Entity Framework as an option. See Entity Framework Tutorial[^]