SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Saturday, March 24, 2007
C# Code Snippet: Read Data from Stored Procedure
I love clear, concise, easy to read code. Here’s a fairly minimalist .NET 2.0 code snippet to read data using a stored procedure that is hopefully all those things. It makes use of the using statement which guarantees that resources held by types that implement IDisposable will be released even if an exception is thrown (using is syntactic sugar for an enclosing try/catch/finally loop):
// Execute a stored proc to read data
using (SqlConnection conn = new SqlConnection(this.connectionString))
using (SqlCommand cmd = conn.CreateCommand())
cmd.CommandText = "ProcName";
cmd.CommandType = CommandType.StoredProcedure;
// Add any input Params...
// Assuming Stored Proc returns a set of records...
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
// do something with each rdr row ...
// OR alternatively,
// if your proc returns a single valued result (e.g. an image)
// (i.e. query of the form "SELECT imgColumnName FROM Table WHERE ID= ?"
// byte img = (byte)cmd.ExecuteScalar();
MSN, Email: mitch døt wheat at gmail.com