•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the C# section within the Software Development category of DaniWeb, a massive community of 402,511 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,670 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our C# advertiser: Programming Forums
Views: 1049 | Replies: 11
 |
•
•
Join Date: Aug 2007
Location: Gunnison, Colorado
Posts: 57
Reputation: 
Rep Power: 2
Solved Threads: 1
Junior Poster in Training
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.
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.
•
•
Join Date: Aug 2006
Location: Egypt
Posts: 760
Reputation:
Rep Power: 4
Solved Threads: 58
Master Poster
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.
You can add to your TableAdapter an update command (customized) and it also update your date immediately.
B.Sc Computer Science, Helwan University
Microsoft Student Partner
Personal blog http://ramymahrous.blogspot.com/
Arabic technical blog http://fci-h-ar.blogspot.com/
English technical blog http://fci-h.blogspot.com/
Microsoft Student Partner
Personal blog http://ramymahrous.blogspot.com/
Arabic technical blog http://fci-h-ar.blogspot.com/
English technical blog http://fci-h.blogspot.com/
•
•
Join Date: Aug 2007
Location: Gunnison, Colorado
Posts: 57
Reputation:
Rep Power: 2
Solved Threads: 1
Junior Poster in Training
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.
•
•
Join Date: Aug 2006
Location: Egypt
Posts: 760
Reputation:
Rep Power: 4
Solved Threads: 58
Master Poster
what??
B.Sc Computer Science, Helwan University
Microsoft Student Partner
Personal blog http://ramymahrous.blogspot.com/
Arabic technical blog http://fci-h-ar.blogspot.com/
English technical blog http://fci-h.blogspot.com/
Microsoft Student Partner
Personal blog http://ramymahrous.blogspot.com/
Arabic technical blog http://fci-h-ar.blogspot.com/
English technical blog http://fci-h.blogspot.com/
•
•
Join Date: Aug 2007
Location: Gunnison, Colorado
Posts: 57
Reputation:
Rep Power: 2
Solved Threads: 1
Junior Poster in Training
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.
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.
•
•
Join Date: Nov 2006
Location: Bonners Ferry, ID
Posts: 279
Reputation:
Rep Power: 2
Solved Threads: 38
Posting Whiz in Training
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.
•
•
Join Date: Oct 2007
Posts: 92
Reputation:
Rep Power: 1
Solved Threads: 8
Junior Poster in Training
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
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
•
•
Join Date: Oct 2007
Posts: 92
Reputation:
Rep Power: 1
Solved Threads: 8
Junior Poster in Training
I hope that this can help you achieve your goal
•
•
Join Date: Aug 2007
Location: Gunnison, Colorado
Posts: 57
Reputation:
Rep Power: 2
Solved Threads: 1
Junior Poster in Training
•
•
•
•
Originally Posted by 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.
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);
}
} •
•
Join Date: Nov 2006
Location: Bonners Ferry, ID
Posts: 279
Reputation:
Rep Power: 2
Solved Threads: 38
Posting Whiz in Training
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.
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();
}
}
} |
•
•
•
•
•
•
•
•
DaniWeb C# Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Other Threads in the C# Forum
- Previous Thread: How to add running application to window xp taskbar notification area ?
- Next Thread: Array homework
