Solved

Using SqlConnection SqlCommand and SqlDataReader

Posted on 2011-03-07
4
847 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
  • 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now