• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 888
  • Last Modified:

Using SqlConnection SqlCommand and SqlDataReader

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
WebsiteDesign
Asked:
WebsiteDesign
  • 2
1 Solution
 
gamarrojgqCommented:
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
 
WebsiteDesignAuthor Commented:
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
 
lenordisteCommented:
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
 
gamarrojgqCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now