none
Do parameter objects being passed need to be cast? RRS feed

  • Question

  • I have an example I was curious about: This method is going to handle a large amount of inserts. In the while loop, it is not necessary to cast these objects to their respective DataTypes is it? The reason I ask, is that some may be null. How is this usually handled? Am I on the right path?

     

    Code Snippet

    private static void ContactAddressT(IDataReader dr)

    {

        Console.WriteLine("CONTACT_ADDRESS_T table being inserted....");

        using (SqlCommand cmd = new SqlCommand(insert.ContactAddressT, TfdCn))

        {

            //CONTRCODE parameter

            SqlParameter CONTRCODE = new SqlParameter("@CONTRCODE", SqlDbType.VarChar, 20);

            cmd.Parameters.Add(CONTRCODE);

     

            //CONTPERS parameter

            SqlParameter CONTPERS = new SqlParameter("@CONTPERS", SqlDbType.VarChar, 100);

            CONTPERS.IsNullable = true;

            cmd.Parameters.Add(CONTPERS);

     

            //CAGECODE parameter

            SqlParameter CAGECODE = new SqlParameter("@CAGECODE", SqlDbType.VarChar, 20);

            CAGECODE.IsNullable = true;

            cmd.Parameters.Add(CAGECODE);

     

            //CONTRNAME parameter

            SqlParameter CONTRNAME = new SqlParameter("@CONTRNAME", SqlDbType.VarChar, 100);

            CONTRNAME.IsNullable = true;

            cmd.Parameters.Add(CONTRNAME);

     

            //SUPPLRATE parameter

            SqlParameter SUPPLRATE = new SqlParameter("@SUPPLRATE", SqlDbType.Int, 3);

            SUPPLRATE.IsNullable = true;

            cmd.Parameters.Add(SUPPLRATE);

     

            //TELNO parameter

            SqlParameter TELNO = new SqlParameter("@TELNO", SqlDbType.VarChar, 20);

            TELNO.IsNullable = true;

            cmd.Parameters.Add(TELNO);

     

            //EMAIL parameter

            SqlParameter EMAIL = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);

            EMAIL.IsNullable = true;

            cmd.Parameters.Add(EMAIL);

     

            //FAXNO parameter

            SqlParameter FAXNO = new SqlParameter("@FAXNO", SqlDbType.VarChar, 20);

            FAXNO.IsNullable = true;

            cmd.Parameters.Add(FAXNO);

     

            //ADDR1 parameter

            SqlParameter ADDR1 = new SqlParameter("@ADDR1", SqlDbType.VarChar, 100);

            ADDR1.IsNullable = true;

            cmd.Parameters.Add(ADDR1);

     

            //ADDR2 parameter

            SqlParameter ADDR2 = new SqlParameter("@ADDR2", SqlDbType.VarChar, 100);

            ADDR2.IsNullable = true;

            cmd.Parameters.Add(ADDR2);

     

            //ADDR3 parameter

            SqlParameter ADDR3 = new SqlParameter("@ADDR3", SqlDbType.VarChar, 100);

            ADDR3.IsNullable = true;

            cmd.Parameters.Add(ADDR3);

     

            //ADDR4 parameter

            SqlParameter ADDR4 = new SqlParameter("@ADDR4", SqlDbType.VarChar, 100);

            ADDR4.IsNullable = true;

     

            cmd.Parameters.Add(ADDR4);

            TfdCn.Open();

            cmd.Prepare();

            while (dr.Read())

            {

                CONTRCODE.Value = dr[0];

      CONTPERS.Value = dr[1];

      CAGECODE.Value = dr[2];

      CONTRNAME.Value = dr[3];

      SUPPLRATE.Value = dr[4];

      TELNO.Value = dr[5];

      EMAIL.Value = dr[6];

      FAXNO.Value = dr[7];

      ADDR1.Value = dr[8];

      ADDR2.Value = dr[9];

      ADDR3.Value = dr[10];

      ADDR4.Value = dr[11];

      cmd.ExecuteNonQuery();

            }

        }

        dr.Close();

        Console.WriteLine("CONTACT_ADDRESS_T insert complete\r\n");

    }

     

     

    Wednesday, August 27, 2008 11:40 PM

Answers

  • As I know you must provide DBNull, and null from C# will NOT be converted into database NULL value. All other values (except NULL) will be converted to appropriate type by provider, assuming that they can be converted based on content
    Thursday, August 28, 2008 6:27 PM
    Moderator

All replies

  • As long as you defined proper data types for your parameters, you should be OK, but might get some exceptions from database about failed conversion. Provider/database will do actual conversion for you. But it might affect performance of your application. What exactly are you asking about?  If you are asking about NULL values, you could pass them to parameters using DbNull.Value. That would be proper way to handle NULLs.

     

    Thursday, August 28, 2008 10:01 AM
    Moderator
  • I was actually asking about both. Since the data is being pushed in from a DataReader, I was curious if I had to cast it from the "object" to its respective type. Also, do I need to check for nulls, and if it is null do I cast it to DBNull?

    Thursday, August 28, 2008 1:15 PM
  • I guess I mean do I need to do something like this in the while loop.

     

    CONTPERS.Value = (dr[1] == null ? DBNull.Value : dr[1]);

    Thursday, August 28, 2008 4:15 PM
  • Anyone?

    Thursday, August 28, 2008 4:50 PM
  • As I know you must provide DBNull, and null from C# will NOT be converted into database NULL value. All other values (except NULL) will be converted to appropriate type by provider, assuming that they can be converted based on content
    Thursday, August 28, 2008 6:27 PM
    Moderator
  • Thanks VMazur,

    I haven't tested it yet, but I am pretty sure you are correct. Thank you!

    Thursday, August 28, 2008 6:48 PM