I have a highly specialized update operation which I must occasionally run against a table. I would like to just send the SQL directly to the database as an UPDATE command. I have a data adapter for the table which handles normal read/write operations but this is a special case.

Can I get away with creating a secondary data adapter and supply it with an UpdateCommand (with the appropriate CommandText) then call the Update method of that adapter? I don't want to mess with the parameter list of the UpdateCommand of the primary adapter if I don't need to. It would be a lot easier to just generate the SQL directly.

You can use connected mode to send your data once it's updated.
You can add to your TableAdapter an update command (customized) and it also update your date immediately.

Yeah, I knowI can do it the 'correct' way. That sounds like an unnecessary complication. I want to avoid as much M$ Mickey Mouse as possible. Operations like this are a lot easier in other environments.

For the work I am doing, direct SQL is preferrable to ANYTHING Microsoft has to offer. It is simple and to the point and most things can be done with a single command, which is easily constructed given the toolset I use. I'm just trying to find the best way to get that done without going through a bunch of intermediate layers. While disconnected datasets certainly have their uses, they can also be a royal pain for some kinds of operations. For the case at hand, sending a SQL command directly to the database is far more efficient and easier to program than loading, modifying and updating using adapters and a DataSet.

It turns out it doesn't matter. I have discovered that Command objects can indeed be used to send commands directly back to the database using adapter command objects.

I prefer using the SqlCommand as well. One approach is to create a method (even perhaps a static method) that you can pass a T-SQL statement, and it does all the work, or you can pass a stored procedure name and a array of parameters for the proc to use. Comes in real handy.

If I well understand your question you should use a transaction to update your database directily I give you a trick might be gives you some help:

SqlConnection oConnection = new SqlConnection();
            SqlTransaction oTransaction;
            oConnection.ConnectionString = ""; // Put your connection string
            oConnection.Open();
            try
            {
                oTransaction = oConnection.BeginTransaction();
                try
                {
                    // TO DO put the update code here use a command object to do that
                    oTransaction.Commit();

                }
                catch (Exception caught1)
                {
                    MessageBox.Show("Error source: " + caught1.Source.ToString() + "Transaction can't be processed");
                    oTransaction.Rollback();
                }

            }
            catch (Exception caught2)
            {
                MessageBox.Show("Error source: " + caught2.Source.ToString());
            }
            finally
            {
                oConnection.Close();
            }

using transaction you have 2 advantages make more than one instruction to a data base at the same time

and if there is an error all actions are rejected as if you didn't make any command excution

Edited 3 Years Ago by happygeek: fixed formatting

I prefer using the SqlCommand as well. One approach is to create a method (even perhaps a static method) that you can pass a T-SQL statement, and it does all the work, or you can pass a stored procedure name and a array of parameters for the proc to use. Comes in real handy.

Actually, using the command ExecuteNonQuery method uses a generic connection and works fine. I try to avoid parameters whenever possible because the are too specific. Here is a generic method (put into a static class) which seems to work. Note that the table involved must be cleared and filled again before the changes are avaliable in the DataSet. This might seem a bit complicated, but the update I need in this case reflects a very complex business rule which would take a number of additional steps if I had to update the data in memory and send it back out using a data adapter. The SQL is actually a much simpler way to go. And now that I have the generic method, it will probably prove useful elsewhere. Here's the method...

public static void ExecuteNonQuery(DbConnection cnn, string sql) {
            DbCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            try {
                cnn.Open();
                cmd.ExecuteNonQuery();
                cnn.Close();
            }
            catch (Exception ex) {
                MessageBox.Show("Update failure in Global.ExecuteNonQuery" + Environment.NewLine
                  + ex.Message);
            }
        }

Great Minds think alike :)
I too have the same exact method in my Global static class. Although I have a "finally" in mine to handle closing the connection just in case of an error. I instantiate the connection from a Global connection string. I have inlcuded my Stored Procedure handling method for your ammusement.

By using the "params" statement, I can pass any number of SqlParameters that a proc will need. Our company (DBA) insists on no embedded SQL in our projects, But I have both methods anyway, and use my Global class in multiple apps.

public static void ExecuteNonQueryStoredProcedure(string ProcedureName, params SqlParameter[] values)
        {
            SqlConnection conn = new SqlConnection(Global.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (values != null && values.Length > 0)
                    cmd.Parameters.AddRange(values);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show("Update failure in Global.ExecuteNonQueryStoredProcedure\r\n" + e.Message);
                throw e;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

The finally clause is a good idea that I will adopt. In this particular case I could use a stored procedure. I use a search system which is totally dynamic and could not be done with parameters as there would be thousands of permutations. I use a parsing system to construct the queries on the fly. Since the application is a database for fisheries work, it isn't likely to be the target of mishchief anyway (we hope). Thanks for the tips.

This article has been dead for over six months. Start a new discussion instead.