Jun 192008
I don’t know how many times I’ve had to execute a stored procedure or a SQL statement from within a BizTalk map or within a C# class, so I’m putting this entry as a reminder to myself of how to do it.
// 1. Create your SQL Connection SqlConnection conn = null; // 2. Create and open a connection object conn = new SqlConnection("Connection String Goes Here"); // 3. Open the Connection conn.Open(); // 4. Create the SQL Command and assign it it a string string strSQLCommand = "SELECT * FROM TABLE"; // 5. Execute the SQL Command SqlCommand command = new SqlCommand(strSQLCommand, conn); // 6. Use ExecuteScalar() to return the first result string returnvalue = (string)command.ExecuteScalar(); // 7. Close the Connection conn.Close(); // 8. Return the Value return returnvalue;
I’d suggest a refactoring of this code, Eric. This works, but if an Exception is generated can leave a hanging SQL Connection. This works slightly better:
using (SqlConnection conn = new SqlConnection(“Connection String Goes Here”))
{
conn.Open();
using (SqlCommand comm = new SqlCommand(“SELECT * FROM TABLE”, conn))
{
return command.ExecuteScalar() as string;
}
}
The using statements ensure that the IDisposable interface of the components SqlCommand and SqlConnection are properly called even if there is an exception. This also allows you to put the Return command where you’d expect it, when you get the value to return.
Merci beaucoup!!!!!!
Ca m’a vraiment aidé.
That was inspiring,
I usually miss some steps to , but I do’t have to blog it to remember it I cone to your blog and I use it:D
Keep up the good work
Thanks let us know if there are other things that should be written.
Do I need to use any System library for this to work?
I got error “The type or namespace name ‘SQLConnection’ could not be found (are you missing a using directive or an assemply reference?)
Yes, refer to SQLConnection
Hey, what i mus put to using (SqlConnection conn = new SqlConnection(“Connection String Goes Here”))???
I put here this: “server=192.189.40.2;database=test;uid=root;pwd=****”
but this is wrong. Where is error?
Wow, I was looking all over for a tutorial on how to do this, and finally found this! Thanks a lot!
Also, it would be nice to know how to retrieve values from specific rows rather than just the first row, or all values for all rows.
there is a mistake :
Tim Rayburn wrote:
return command.ExecuteScalar() as string;
should be
return comm.ExecuteScalar() as string;
Hi webmaster, commenters and everybody else !!! The blog was absolutely fantastic! Lots of great information and inspiration, both of which we all need!b Keep ‘em coming… you all do such a great job at such Concepts… can’t tell you how much I, for one appreciate all you do!
nice code.Thanks for sharing this cool post.
Thank you so much for this lovely piece of code.
try to include
using System.Data.SqlClient;
Hope it works for you.
to Tim Rayburn:
SqlCommand does’t implement IDisposable interface, so you don’t have to use ‘using’ statement with it. Read:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx
Interesting, very helpfull thankyou!
good