Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using SqlConnection SqlCommand and SqlDataReader

Posted on 2011-03-07
4
Medium Priority
?
869 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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