Solved

Update query with certain parameters

Posted on 2013-02-06
5
291 Views
Last Modified: 2013-02-06
Hello,

I hope I can explain this without making myself sound an idiot.

I'm getting to grips with creating SQL adapters.  I can create an update command with parameters and pass those values in for the update with no problem.

The disadvantage of this as far as I can see is that you have to create a new update command every time you want to change a record to reflect the changes that you are making at that point.  My question is if it is possible to create a "global" update command for the table ( with all the columns in the table as parameters ) which will use the existing values for each column as the parameters values unless you provide new values for one or more of the parameters.

Does that make sense?
0
Comment
Question by:Petermcg001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38859433
you can do batch operations using datatable which means you modify your datatable instance and use the adapter to update your DB table:
Performing Batch Operations Using DataAdapters
you may use stored procedure if u need to update large number of rows.
0
 
LVL 1

Author Comment

by:Petermcg001
ID: 38859588
Hello, thanks for looking at this.

It's not really what I was trying to get at.

Let's say I have a two column table called tblContacts with columns FirstName and LastName.

If I want to update just the first name my update command will be "UPDATE tblContacts SET FirstName  = @FirstNameParam WHERE....." and I then have to pass the first name value in.  If in a different form I want change the LastName I have to create another update command with "UPDATE tblContacts SET LastName = @LastNameParam WHERE....." and then provide the last name as a value for the parameter.

What I would like to achieve is to create one update command of "UPDATE tblContacts SET FirstName = @FirstNameParam, LastName = @LastNameParam WHERE....." which can be used anywhere in my application and where I can choose to provide a new value for any of the parameters but to take the existing value of a column for the parameter value for those columns which I don't want to change at that point.

Thanks
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 38859786
create an update command builder which uses named parameters and can be invoke all the updates at once.

here full class:
   public class UpdateCommandBuilder
    {
        Dictionary<string, object> _updateParams = new Dictionary<string, object>();
        Dictionary<string, object> _whereParams = new Dictionary<string, object>();
        string _constring;
        string _table;

        public UpdateCommandBuilder(string connectionstring, string table)
        {
            _constring = connectionstring;
            _table = table;
        }

        public void AddWhere(string name, object value)
        {
            _whereParams.Add(name, value);
        }

        public void AddParam(string name, object value)
        {
            _updateParams.Add(name, value);
        }

        public int SubmitChanges()
        {
            string commandText = BuildCommandText();

            using (SqlConnection connection = new SqlConnection(_constring))
            {
                SqlCommand command = new SqlCommand(commandText.ToString(), connection);

                command.Parameters.AddRange(_whereParams.Select(n =>
                    new SqlParameter
                    {
                        ParameterName = "@" + n.Key,
                        Value = n.Value
                    }).ToArray());

                command.Parameters.AddRange(_updateParams.Select(n =>
                    new SqlParameter
                    {
                        ParameterName = "@" + n.Key,
                        Value = n.Value
                    }).ToArray());

                try
                {
                    connection.Open();
                    return command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);

                }

                return 0;
            }
        }

        private string BuildCommandText()
        {
            var sb = new StringBuilder(string.Format("UPDATE {0} SET ", _table));
            foreach (var key in _updateParams.Keys)
            {
                sb.Append(string.Format("{0} = @{0},", key));
            }

            sb.Remove(sb.Length - 1, 1);

            if (_whereParams.Count > 0)
            {
                sb.Append(" WHERE ");
                foreach (var key in _whereParams.Keys)
                {
                    sb.Append(string.Format("{0} = @{0} AND ", key));
                }
            }

            sb.Remove(sb.Length - 3, 3);
            return sb.ToString();
        }
    }

Open in new window


example usage:
UpdateCommandBuilder cmdbuilder = new UpdateCommandBuilder("your-connection-string", "yout-table");
            cmdbuilder.AddParam("FirstName", "Joe");
            cmdbuilder.AddParam("LastName", "Brown");
            cmdbuilder.AddParam("Age", 37);
            cmdbuilder.AddWhere("ID", 1);
            cmdbuilder.AddWhere("IsActive", true);
            cmdbuilder.SubmitChanges();

Open in new window


the result sql will be:
UPDATE Employee SET FirstName = @FirstName,LastName = @LastName,Age = @Age WHERE ID = @ID AND IsActive = @IsActive 

Open in new window

0
 
LVL 25

Expert Comment

by:jogos
ID: 38859905
When you use "SET FirstName = @FirstNameParam, LastName = @LastNameParam" then you have to provide both @FirstNameParam and @LastNameParam.  
If you want to reuse that code then you must provide also the value of the column that is not changed.  This means that you only can do this if you know that value in your programm.  
There can be ugly workarounds for 'NOT NULL'-fields providing a DbNULL-value and then using the isnull in your update. If you pass the null-value then it is set to itself.

.... but again ugly
UPDATE tblContacts SET FirstName  = isnull(@FirstNameParam,FirstName)
,LastName = isnull(@LastNameParam, LastName)
 

Open in new window

0
 
LVL 1

Author Closing Comment

by:Petermcg001
ID: 38860069
Thanks!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question