how to make sql query more dynamic in aspnet?

Question

hello everyone.
so I have a webpage and a gridview connected to a database table. my update queries of the columns are as followed:

` if (oldName != NAME && oldCreated == DATE)  
    {  
        GeneralDbExecuterService.executeSqlNonQuery(string.Format("UPDATE EXCEPTIONAL_USE_POLICY_PARAM SET NAME  = '{0}' WHERE ID = '{1}' ", NAME, ID));  
    }  
    //if date was changed alone  
    if (oldCreated != DATE && oldName == NAME)  
    {  
        GeneralDbExecuterService.executeSqlNonQuery(string.Format("UPDATE EXCEPTIONAL_USE_POLICY_PARAM SET CREATED_DATE = to_date('{0}', 'dd/MM/yyyy') WHERE ID = '{1}' ", DATE, ID));  
    }  
    //if both values were changed  
    if (oldName != NAME && oldCreated != DATE)  
    {  
        GeneralDbExecuterService.executeSqlNonQuery(string.Format("UPDATE EXCEPTIONAL_USE_POLICY_PARAM SET NAME  = '{0}', CREATED_DATE = to_date('{2}', 'dd/MM/yyyy') WHERE ID = '{1}' ", NAME, ID, DATE));  
    }`  

my question is, how can I make it more modular? for example if 2 more columns are added its going to raise my IFs a lot. what is the best way to achieve that kind of dynamic approach? and is that even possible?. thanks

Answer 1

Hi @gal mor ,
I think you can try the following way of writing, it should reduce the workload a little, if there are new columns you only need to add two places.

  if (OldName != NAME || oldCreated != DATE || new columns )  
            {   
                if(OldName == NAME)  
                {                     
                    SqlCommand cmd = new SqlCommand("Update Test set Date='" + DATE + "' where ID=" + Convert.ToInt32(id.Text), con);  
                    c m d.ExecuteNonQuery();  
                }  
                if(oldCreated == DATE)  
                {  
                    SqlCommand cmd = new SqlCommand("Update Test set Name='" + NAME + "' where ID=" + Convert.ToInt32(id.Text), con);  
                    c m d.ExecuteNonQuery();  
                }  
                if (new columns )  
                {  
  
                }                                
            }  
            else  
            {  
                SqlCommand cmd = new SqlCommand("Update Test set Name='" + NAME + "',Date='" + DATE + "' where ID=" + Convert.ToInt32(id.Text), con);  
                c m d.ExecuteNonQuery();  
            }  

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Entity Framework and/or standard data access design patterns have solved this problem long long ago.

A common approach is using the user's validated input to populate an object called a view model. The view model is passed to a business layer which might process the view model according to business rules. The business layer passes the view model to the data access layer. The data access layer uses the view model to populate an entity (representation of a table record). Next, DML logic like a T-SQL UPDATE is executed which updates all the user columns. Creating a dynamic T-SQL UPDATE for the changed columns is overly complex. However, it is possible to accomplish.

If the updated columns are needed for history purposes or logging, this can be handled in the data access layer or database (stored procedure). A simple pattern using an object approach is below. The MyEntity object is populated by querying the database table. At this point, the view model and an entity are populated. The view model contains the user's inputs (updates) and the entity has the current state of the database record before the update. Note: typically an unique Id is used to look up the record which I did not include.

    public class MyEntity  
    {  
        private string _name;  
  
        public string Name  
        {  
            get  
            {  
                return _name;  
            }  
  
            set  
            {  
                if (_name != value)  
                {  
                    //Do something like fire an event,   
                    //or log the change.   
                }  
  
                _name = value;  
            }  
        }  
    }  

The same type of logic can be written in T-SQL.

Now, if you really really want to create custom T-SQL then the pattern above can be used to determine which columns changed. The entity is used as the basis for generating the T-SQL.

However, I recommend learning the common approaches like Entity Framework and layered designs. Design patterns like the repository or unit of work are also helpful.