<< Get the Original File Name from either the HIPAA or BaseEDI adapters | Home | Find the processes locking a .dll >>

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, April 17, 2008 4:51 PM

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.

Your comment:



 (will not be displayed)


 
 
 
Please add 5 and 1 and type the answer here:
 

Live Comment Preview: