Update sql record in c# wpf

Question

I have a User class that gets and sets data about users.

private string userId;  
        public string UserId
        {
            get { return userId; }
            set {
                userId = value;
                OnPropertyChanged(new PropertyChangedEventArgs("UserId"));
            }
        }

        private string employeeNumber;
        public string EmployeeNumber
        {
            get { return employeeNumber; }
            set {
                employeeNumber = value;
                OnPropertyChanged(new PropertyChangedEventArgs("EmployeeNumber"));
            }
        }
         ...

The data is retrieved from a select all query into an observablecollection and then displayed in a simple wpf window that has a combobox to list all users by userid and textboxes that displays the rest of the user data. All this works great. However, the edit/save has me over a barrel.

After a text box is edited and the save button is clicked the following method is executed

private void btnSave_Click(object sender, RoutedEventArgs e)
    {
        User user = (User)grdUserManagement.DataContext;
        try
        {
            UserDB.UpdateUser(user);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

Obviously, the user passed to the Update statement is the new edited user.

 public static void UpdateUser(User user)
    {
        SqlConnection connection = BusinessDB.GetConnection();
        string updateStatement =
            "UPDATE Employees SET userid = '" + user.UserId.ToString() + "', " +
            "password = '" + user.Password.ToString() + "', " +
            "empno = '" + user.EmployeeNumber.ToString() + "', " +
            "firstname = '" + user.FirstName.ToString() + "', " +
            "lastname = '" + user.LastName.ToString() + "' " +
            "WHERE (userid = '" + user.UserId + "' " +
            "OR userid IS NULL AND '" +user.UserId.ToString() + "' IS NULL) " +
            "AND (password = '" + user.Password.ToString() + "' " +
            "OR password IS NULL AND '" + user.Password.ToString() + "' IS NULL) " +
            "AND empno = '" + user.EmployeeNumber.ToString() + "' " +
            "AND firstname = '" + user.FirstName.ToString() + "' " +
            "AND lastname = '" + user.LastName.ToString() + "'";

             SqlCommand updateCommand = new SqlCommand(updateStatement, connection);

              try
        {
            connection.Open();
            updateCommand.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            connection.Close();
        }

    }

...nothing is saved because there is nothing in the table that matches the new 'edited' data.

How do I write this to check for concurrency and update the record?

Regarding the above issue, what is the best method to ensure concurrency? — BDFarm
– BDFarm, Commented May 8, 2014 at 13:58

SHEKHAR SHETE · Accepted Answer · 2014-05-08 04:01:01Z

3

You can update a table data using the PrimaryKey in where clause itself and never try to update the PrimaryKey in DB.. Please use the Parameters to prevent from SQLInjections.

Use of Paramters to prevent SQLInjection

Using Block: using statement allows the programmer to specify when objects that use resources should release them. The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object's resources.

using (SqlConnection conn =
            new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd =
                new SqlCommand("UPDATE Employees SET firstname=@firstname, lastname=@lastname" +
                    " WHERE Id=@Id", conn))
            {
                cmd.Parameters.AddWithValue("@Id",user.UserId );
                cmd.Parameters.AddWithValue("@firstname",user.FirstName);
                cmd.Parameters.AddWithValue("@lastname",user.LastName);
                //add whatever parameters you required to update here
                int rows = cmd.ExecuteNonQuery();
                conn.Close();
            }
    }

Hope this helps you!

edited May 8, 2014 at 4:01

answered May 8, 2014 at 3:55

SHEKHAR SHETE

6,08415 gold badges89 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BDFarm Over a year ago

Thanks to both Shekhar Shete and Pushpraj Ruhal both answers were very helpful.

pushpraj · Accepted Answer · 2014-05-08 03:17:49Z

1

You may not practically update the key field while keeping it in the where clause at the same time. Secondly where clause looks so complicated for a simple update.

So the solution is to have a dedicated primary key, hidden from the view and use the same in the where clause to update the rest of the fields, do not use all of the fields in where clause. A GUID kind of filed works best here, provides uniqueness. A simple running number can also work for you.

See sample below, I am referring ID as a primary key in your table eg.

string updateStatement =
        "UPDATE Employees SET userid = '" + user.UserId + "', " +
        "password = '" + user.Password + "', " +
        "empno = '" + user.EmployeeNumber + "', " +
        "firstname = '" + user.FirstName + "', " +
        "lastname = '" + user.LastName + "' " +
        "WHERE ID = '" + user.ID + "'"

edited May 8, 2014 at 3:17

answered May 8, 2014 at 2:57

pushpraj

13.7k3 gold badges38 silver badges52 bronze badges

1 Comment

vesan Over a year ago

+1, however it might be mentioned that the query should use parameters to avoid SQL injection.

Collectives™ on Stack Overflow

Update sql record in c# wpf

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related