Solved

Using SqlConnection SqlCommand and SqlDataReader

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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