<< Environment Variable resolution | Home | BizTalk Role Links explained >>

Execute SQL Query from within a C# function

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;

posted @ Thursday, June 19, 2008 10:22 AM

Print

Comments on this entry:

# re: Execute SQL Query from within a C# function

Left by Tim Rayburn at 4/18/2008 8:00 AM
Gravatar

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.

 re: Execute SQL Query from within a C# function

Left by oballyci at 7/24/2009 4:21 AM

Merci beaucoup!!!!!!
Ca m'a vraiment aidé.

# re: Execute SQL Query from within a C# function

Left by software development company at 8/14/2009 3:39 AM
Gravatar

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

# re: Execute SQL Query from within a C# function

Left by Eric Stott at 8/14/2009 9:50 AM
Gravatar

Thanks let us know if there are other things that should be written.

 re: Execute SQL Query from within a C# function

Left by Sam at 8/17/2009 7:44 PM

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?)

# re: Execute SQL Query from within a C# function

Left by Eric Stott at 8/17/2009 9:23 PM
Gravatar

Yes, refer to SQLConnection

Your comment:



 (will not be displayed)


 
 
 
Please add 6 and 4 and type the answer here:
 

Live Comment Preview:

 
«March»
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910