none
Execute scalar in a dataAccess tier RRS feed

  • Question

  • I add the following in my data access component.  Now i am using it more, i need diff return types, i was thinking of ExecuteScalar_Int, ExecuteScalar_Dec, ExecuteScalar_Date but I think it could be better, like maybe have the same code and use an out param of each, the only thing is the date time cant parse null.  Any ideas or suggestions on design appreciated

     public static void ExecuteScalar(string sql, out DateTime val)
            {
                string returnValue = ExecuteScalar(sql);
                if (string.IsNullOrEmpty(returnValue) )
                    what todo?

                else
                {
                    val= DateTime.Parse( returnValue);
                }
            }
            public static void ExecuteScalar(string sql, out decimal val)
            {
                string returnValue = ExecuteScalar(sql);
                val = Convert.ToDecimal(string.IsNullOrEmpty(returnValue) ? null : returnValue);
            }
            public static void ExecuteScalar(string sql, out int val)
            {
                string returnValue = ExecuteScalar(sql);
                val= Convert.ToInt32(string.IsNullOrEmpty(returnValue) ? null: returnValue);
            }
            public static string ExecuteScalar(string sql)
            {
                // Open the connection
                using (SqlConnection cnn = new SqlConnection(ConnectionString))
                {
                    cnn.Open();

                    // Define the command
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = cnn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql;

                        string returnValue = cmd.ExecuteScalar() as string;

                        return string.IsNullOrEmpty(returnValue) ? string.Empty : returnValue;
                    }
                }
            }

     

     


    McC
    Friday, May 7, 2010 7:07 PM

Answers

  • Try this:

    public static void ExecuteScalar<T>(string sql, out T val)
    {
     string returnValue = ExecuteScalar(sql);
     if (string.IsNullOrEmpty(returnValue))
     return null;
     
     if (T is DateTime)
     return DateTime.Parse( returnValue);
     
     if (T is decimal)
     return Convert.ToDecimal(returnValue);
     
     if (T is int)
     return Convert.ToInt32(returnValue);
     
     // etc.etc.
    }

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 8, 2010 6:22 PM
  • Actually, I don't think you can do this with generics. I think you need to go back to your original code and the answer I was originally going to give, but didn't for some stupid reason.

    Your original question was what to do about the DateTime type if the returnValue was null. You have two choices:

    1) Use a default, such as DateTime.MinValue:

    public static void ExecuteScalar(string sql, out DateTime val)
    {
      string returnValue = ExecuteScalar(sql);
      if (string.IsNullOrEmpty(returnValue) )
        val = DateTime.MinValue;
      else
        val= DateTime.Parse( returnValue);
    }

     2) Use a nullable DateTime:

    public static void ExecuteScalar(string sql, out DateTime? val)
    {
      string returnValue = ExecuteScalar(sql);
      if (string.IsNullOrEmpty(returnValue) )
        val = null;
      else
        val= DateTime.Parse( returnValue);
    }

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 8, 2010 8:31 PM

All replies

  • Hii John,

    Why not you try using the Generic ?

    public static void ExecuteScalar(string sql, out <T> val)
    {
        <T> returnValue = ExecuteScalar(sql);
        val = (<T>)(string.IsNullOrEmpty(returnValue) ? null: returnValue);
    }
    


    This will reduce writing more functions.

    Thanks in Advance.


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Saturday, May 8, 2010 12:47 PM
  • That didnt work at all, i did some looking and changed to :
    public static void ExecuteScalar <T> (string sql, out T val)
    {
    	string returnValue = ExecuteScalar(sql);
        val = (T)(string.IsNullOrEmpty(returnValue) ? null: returnValue);
    }

    the code is good to the point of val = (T) where i get the err cant convert type 'string' to T

    Saturday, May 8, 2010 1:23 PM
  • You still need to have the conversions for each type that T might be, as in your previous code (you know, if T is int, Convert.ToInt32() ... etc.)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 8, 2010 4:41 PM
  • How do i do that i havent done generics yet :(
    McC
    Saturday, May 8, 2010 5:12 PM
  • Try this:

    public static void ExecuteScalar<T>(string sql, out T val)
    {
     string returnValue = ExecuteScalar(sql);
     if (string.IsNullOrEmpty(returnValue))
     return null;
     
     if (T is DateTime)
     return DateTime.Parse( returnValue);
     
     if (T is decimal)
     return Convert.ToDecimal(returnValue);
     
     if (T is int)
     return Convert.ToInt32(returnValue);
     
     // etc.etc.
    }

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 8, 2010 6:22 PM
  • Nevermind, I screwed up that example code ... I'm working on something that will actually compile. ;0) .... I'll be back ....
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 8, 2010 6:59 PM
  • Actually, I don't think you can do this with generics. I think you need to go back to your original code and the answer I was originally going to give, but didn't for some stupid reason.

    Your original question was what to do about the DateTime type if the returnValue was null. You have two choices:

    1) Use a default, such as DateTime.MinValue:

    public static void ExecuteScalar(string sql, out DateTime val)
    {
      string returnValue = ExecuteScalar(sql);
      if (string.IsNullOrEmpty(returnValue) )
        val = DateTime.MinValue;
      else
        val= DateTime.Parse( returnValue);
    }

     2) Use a nullable DateTime:

    public static void ExecuteScalar(string sql, out DateTime? val)
    {
      string returnValue = ExecuteScalar(sql);
      if (string.IsNullOrEmpty(returnValue) )
        val = null;
      else
        val= DateTime.Parse( returnValue);
    }

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 8, 2010 8:31 PM
  • thank you so much bonnie!  I have to go into work sunday and finish this thing up and send to india imagine that LOL
    McC
    Saturday, May 8, 2010 8:59 PM
  • You're quite welcome. Glad to help. =0)

    Ah, partners in India ... that's always fun. ;0)

    I work at home (my hubby and I are in the process of starting a new company), so I frequently work on the weekend. But then, if I feel like it, I don't have to work on Tuesday. ;0)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, May 9, 2010 12:04 AM