Sending update commands directly

[fishsqzr]

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.

[Ramy Mahrous]

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.

[fishsqzr]

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.

[Ramy Mahrous]

what??

[fishsqzr]

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.

[JerryShaw]

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.

[Jugortha]

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

[Jugortha]

I hope that this can help you achieve your goal

[fishsqzr]

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);
            }
        }

[JerryShaw]

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();
                }
            }
        }

[fishsqzr]

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.

[Jugortha]

OK, even the transactions are very useful espacially in such cases