ExecuteScalar<T>

-

 I came across this while working on a existing code from an ISV.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 public class SomeDBWrapper : IDisposable
 {


     public string ExecuteExecuteScalar_String()
     {
         object value = this.ExecuteExecuteScalar();
         if (value == DBNull.Value)
            return "";
         else
         return (string)value;
     }
     public int ExecuteExecuteScalar_Int()
     {
        object value = this.ExecuteExecuteScalar();
        if (value == DBNull.Value)
           return 0;
        else
        {
           return Convert.ToInt32(value);               
        }
     }
     public Int64 ExecuteExecuteScalar_Int64()
     {
          object value = this.ExecuteExecuteScalar();
          if (value == DBNull.Value)
            return 0;
          else
             return (Int64)value;
     }
     public Double ExecuteExecuteScalar_Double()
     {
      object value = this.ExecuteExecuteScalar();
      if (value == DBNull.Value)
         return 0.0;
      else
         return (double)value;
      

     }
}

It's just hard to ignore the naming convention used as well as the hard coded _Bool so I decided to create my own implementation, instead of making it statically typed method call, using Generics I was able to create dynamically typed method call for ExecuteScalar.

ExecuteScalar<T> where T is a type of primitive object and of course a string.

Here's my implementation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 public static T ExecuteScalar<T>(this database that, SqlTransaction sqlTrans = null, bool isCPM = false) where T : new()
 {
     T result;
     if (typeof(T).IsPrimitive || typeof(string) == typeof(T))
     {
         object retVal = null;
         using (that)
         {
             that.setConnection(isCPM);
             that.dbCmd.Transaction = sqlTrans;
             if (that.dbCmd.Connection.State != ConnectionState.Open) that.dbCmd.Connection.Open();
                 retVal = that.dbCmd.ExecuteScalar();
                 result = (retVal != DBNull.Value) ? (T)Convert.ChangeType(retVal, typeof(T)) : (typeof(T) == typeof(string)) ? (T)(object)string.Empty : default(T);
         }
      }
      else
      {
          throw new InvalidCastException("Error cannot convert result to a none string or primitive type.");
      }

      return result;
  }

Usage:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
internal object RemoveSomething(List<ExpandoObject> list, int? id, out CustomResult ajaxresult)
{

     bool result = true;
     ajaxresult = new CustomResult();
     try
     {
         list.ForEach(x =>
         {
            ((dynamic)x).siteId = id;
               db.setCommand("proc_Delete_Something", CommandType.StoredProcedure, null);
               db.AssignParameters((ExpandoObject)x, db.dbConn2);
               result = db.ExecuteScalar<bool>(null, true);
            });

      }
      catch (Exception e)
      {
          ajaxresult.errorMessage = e.Message;
          ajaxresult.success = false;
      }
       return result;
}

Hope this help, happy coding.



Comments

Post a Comment

Popular posts from this blog

Serializing JSON string to ExpandoObject

-
In some case you want to create and Object from JSON, a great way of doing this to deserialize this JSON to an ExpandoObject. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 /// <summary> /// Convert Json String to an Expando Object /// </summary> /// <param name="that">Json string to convert</param> /// <returns>Expando Object</returns> public static ExpandoObject ToExpando ( this string that) { ExpandoObject result; try { result = JsonConvert.DeserializeObject<ExpandoObject>(that, new ExpandoObjectConverter()); } catch (Exception e) { throw e; } return result; } Here we use JsonConvert.DeserializeObject, you need to add reference to NewtonSoft.Json see below. 1 2 3 using Newtonsoft.Json ; using Newtonsoft.Json.Converters ; using Newtonsoft.Json.Linq ;
Read more

XML to ExpandoObject using Recursion in C#

-
Here's how to convert XML to an ExpandoObject using recursion. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 public static dynamic ToExpando ( this XDocument document) { return CreateExpando (document.Root); } private static dynamic CreateExpando (XElement element) { var result = new ExpandoObject() as IDictionary< string , object >; if (element.Elements().Any(x => x.HasElements)) { var list = new List<ExpandoObject>(); result.Add(element.Name.ToString(), list); foreach ( var childElement in element.Elements()) { list.Add(CreateExpando(childElement)); } } else { foreach ( var leafElement in element.Elements()) { result.Add(leafElement.Name.ToString(), leafElement.Value); } } return result; } Happy Coding!
Read more

Automatically Discover and Assign Parameter with Values to a Stored Procedure Call in C#

-
I'm sure most programmers have run across a situation wherein they have to pass  a lot of parameters to a stored procedure call in C#. Instead of manually typing each parameter in and assigning a value, why not develop a way to automatically discover the parameter, add it to the parameter list of the command object and assign the corresponding value from an entity or model. Here's my implementation using extension method. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 /// <summary> /// Automatically assigns parameters to Command.Parameter Collection from List of parameter names /// </summary> /// <param name="that">Command object being extended</param> /// <param name="model">Expando model</param> /// <param name="conn">sQLConnection used to g...
Read more