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.

  • oballyci

    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.

  • Sam

    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

  • Tomas

    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?

  • Anonymous

    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.

  • grzesiekl

    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.

  • muzikhera

    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