How to perform soft delete and undelete in SQL client(C# windows form app)

Question

I am trying to soft delete a row in the table. Once I update it, if I want to undelete my row, I should be able to do it. Also, I like to purge delete, permanent delete. I am using SQL client using C# Windows Forms (NO ENTITY).

The soft delete code below:

conn1.Open();  // conn1 which is my object and sql command open

SqlCommand cmd = conn1.CreateCommand();   // sql command to create new object
cmd.CommandType = CommandType.Text;  // setting up text SQL command

cmd.CommandText = "IsDeleted  = 0 from [Table] where ADD_UID= '" + textBox1.Text + "'";  //Select which Table and Row Based on Deleting

cmd.ExecuteNonQuery();
conn1.Close();
textBox1.Clear();

I am getting this error:

However I am able to delete a row without any error using delete command instead of saying isdelete. Is there any other way to soft delete and physical delete in SQL client without using ENTITY.

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Apr 18 '18 at 02:50
Please share your `CREATE TABLE` for the table, so we can see your existing column names and types. — mjwills, Apr 19 '18 at 01:58

score 2 · Answer 1 · answered Apr 18 '18 at 03:15

2

The way you are executing your SQL statement opens you up to SQL injection attacks because people could include text in your TextBox that interrupts your query and does any number of malicious things to your database. If you parameterize your query it can help avoid this. Here is a sample:

        var conn1 = new SqlConnection();
        conn1.Open(); 
        SqlCommand cmd = conn1.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Update [TABLE] set IsDeleted = 0 where ADD_UID= @TextBoxVal";
        SqlParameter param = new SqlParameter();
        param.ParameterName = "TextBoxVal";
        param.Value = textBox1.Text;
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
        conn1.Close();
        textBox1.Clear();

answered Apr 18 '18 at 03:15

daniel_sweetser

381
1
8

Consider using `using` for `conn1` and `cmd` to avoid the need for the `Close` calls. – mjwills Apr 18 '18 at 03:55
Here is a good summary of common SQL injection attacks to give you a general idea what can happen: https://www.w3schools.com/sql/sql_injection.asp – daniel_sweetser Apr 19 '18 at 01:07
I think you guys didn't understand my point. It's not about injection, I am asking how to perform soft delete and undelete, which is not working – r2k Apr 19 '18 at 01:42
We do understand your point. You have **two** issues. One is the problem you think you have - which is soft deletes. You also have another, **much worse**, problem - which is SQL Injection. Have a read of https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . – mjwills Apr 19 '18 at 01:57
@mjwills Thank you so much. After reading about sql injection, now I know what you guys are talking about. – r2k Apr 19 '18 at 12:56

How to perform soft delete and undelete in SQL client(C# windows form app)

1 Answers1