Solved

Using SqlConnection SqlCommand and SqlDataReader

Posted on 2011-03-07
4
862 Views
Last Modified: 2012-05-11
Hi
Can anyone show me how to convert the code attached to use SqlConnection SqlCommand and SqlDataReader and also include the try catch blocks

Thanks
George
public List<GetCurrencyDataFieldsFromDB> GetCurrencyDatabaseFields()
        {
            string spName = "**********";
            SqlConnection cn = new SqlConnection(dbConn);
            SqlCommand cmd = new SqlCommand(spName, cn);
            cmd.CommandType = CommandType.StoredProcedure;

            List<GetCurrencyDataFieldsFromDB> CurrencyData = new List<GetCurrencyDataFieldsFromDB>();
            try
            {
                cn.Open();
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
                while (rdr.Read())
                {
                    GetCurrencyDataFieldsFromDB GetData = new GetCurrencyDataFieldsFromDB(
                        (string)rdr["CurrencyID"],
                        (string)rdr["Country"]);
                    CurrencyData.Add(GetData);
                }
                rdr.Close();
                return CurrencyData;
            }
            catch (SqlException)
            {
                throw new ApplicationException("Error");
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cmd.Dispose();
                    cn.Close();
                    cn.Dispose();
                }
            }
        }

Open in new window

0
Comment
Question by:WebsiteDesign
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35063217
Hi,

What exactly do you want to convert? since the code you post is using SqlConnection

SqlConnection cn = new SqlConnection(dbConn);

using SqlCommand

SqlCommand cmd = new SqlCommand(spName, cn);

and using SqlDataReader

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);

and also include the try catch blocks

           try
            {
                  .....
                }
                rdr.Close();
                return CurrencyData;
            }
            catch (SqlException)
            {
                throw new ApplicationException("Error");
            }
            finally
            {
                         ....
              }
0
 

Author Comment

by:WebsiteDesign
ID: 35063236
Hi
Need it to start using using
 (SqlConnection cn = new SqlConnection(dbConn))
            {
                using (SqlCommand cmd = new SqlCommand(spName, cn))
                {
using (SqlDataReader rdr = cmd.ExecuteReader())
0
 
LVL 11

Expert Comment

by:lenordiste
ID: 35063263
look at this ressource from MS it shows exactly how to do a using properly with SQLConenction, SqlCommand and SQlDataReader:
http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlcommand(VS.80).aspx
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35063293
Ok, try this
public List<GetCurrencyDataFieldsFromDB> GetCurrencyDatabaseFields()
    {
        string spName = "**********";
        using (SqlConnection cn = new SqlConnection(dbConn))
        {

            using (SqlCommand cmd = new SqlCommand(spName, cn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                List<GetCurrencyDataFieldsFromDB> CurrencyData = new List<GetCurrencyDataFieldsFromDB>();
                try
                {
                    cn.Open();
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult))
                    {
                        while (rdr.Read())
                        {
                            GetCurrencyDataFieldsFromDB GetData = new GetCurrencyDataFieldsFromDB(
                                (string)rdr["CurrencyID"],
                                (string)rdr["Country"]);
                            CurrencyData.Add(GetData);
                        }
                        rdr.Close();
                    }
                    return CurrencyData;
                }
                catch (SqlException)
                {
                    throw new ApplicationException("Error");
                }
                finally
                {
                    if (cn.State == ConnectionState.Open)
                    {
                        //cmd.Dispose(); not needed because using statement will call it 
                        cn.Close();
                        //cn.Dispose(); not needed because using statement will call it 
                    }
                }
            }
        }
    }

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question