none
EF Dynamic type - The SqlParameter is already contained by another SqlParameterCollection RRS feed

  • Question

  • I have a stored procedure which gives a dynamic result set

    (Eg1. storedProcedureNameXX 4 - sql server result  may be 5 columns)
    (Eg2. storedProcedureNameXX 1 - sql server result  may be 3 columns)

    Lets assume i have added the dynamic columns in the Type Builder: TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType")
    //Todo:get the dynamic column names
    TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType")<br>
    //Todo:get the dynamic column names
    CreateAutoImplementedProperty(builder, "column1", typeof(string));
    CreateAutoImplementedProperty(builder, "column2", typeof(string));
    CreateAutoImplementedProperty(builder, "column3", typeof(string));

    Type resultType = builder.CreateType();
       var parameters = new List<SqlParameter>();
       parameters.Add(new SqlParameter("parm1", 1));
    var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.ToArray());
    Error in P1 variable:
    "The SqlParameter is already contained by another SqlParameterCollection" Unable to place ToList()
    //Added methods used
    private static TypeBuilder createTypeBuilder( string assemblyName, string moduleName, string typeName) 

    TypeBuilder typeBuilder = AppDomain.CurrentDomain
    .DefineDynamicAssembly(new AssemblyName(assemblyName), AssemblyBuilderAccess.Run)
    .DefineDynamicModule(moduleName)
    .DefineType(typeName, TypeAttributes.Public); 
    typeBuilder.DefineDefaultConstructor(MethodAttributes.Public); 
    return typeBuilder; 

    private static void createAutoImplementedProperty( TypeBuilder builder, string propertyName, Type propertyType) { const string PrivateFieldPrefix = "m_"; const string GetterPrefix = "get_"; const string SetterPrefix = "set_";

     // Generate the field.
            FieldBuilder fieldBuilder = builder.DefineField(
                string.Concat(PrivateFieldPrefix, propertyName),
                              propertyType, FieldAttributes.Private);

            // Generate the property
            PropertyBuilder propertyBuilder = builder.DefineProperty(
                propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

            // Property getter and setter attributes.
            MethodAttributes propertyMethodAttributes =
                MethodAttributes.Public | MethodAttributes.SpecialName |
                MethodAttributes.HideBySig;

            // Define the getter method.
            MethodBuilder getterMethod = builder.DefineMethod(
                string.Concat(GetterPrefix, propertyName),
                propertyMethodAttributes, propertyType, Type.EmptyTypes);

            // Emit the IL code.
            // ldarg.0
            // ldfld,_field
            // ret
            ILGenerator getterILCode = getterMethod.GetILGenerator();
            getterILCode.Emit(OpCodes.Ldarg_0);
            getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
            getterILCode.Emit(OpCodes.Ret);

            // Define the setter method.
            MethodBuilder setterMethod = builder.DefineMethod(
                string.Concat(SetterPrefix, propertyName),
                propertyMethodAttributes, null, new Type[] { propertyType });

            // Emit the IL code.
            // ldarg.0
            // ldarg.1
            // stfld,_field
            // ret
            ILGenerator setterILCode = setterMethod.GetILGenerator();
            setterILCode.Emit(OpCodes.Ldarg_0);
            setterILCode.Emit(OpCodes.Ldarg_1);
            setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
            setterILCode.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterMethod);
            propertyBuilder.SetSetMethod(setterMethod);
        } 
     // Generate the field.
            FieldBuilder fieldBuilder = builder.DefineField(
                string.Concat(PrivateFieldPrefix, propertyName),
                              propertyType, FieldAttributes.Private);

            // Generate the property
            PropertyBuilder propertyBuilder = builder.DefineProperty(
                propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

            // Property getter and setter attributes.
            MethodAttributes propertyMethodAttributes =
                MethodAttributes.Public | MethodAttributes.SpecialName |
                MethodAttributes.HideBySig;

            // Define the getter method.
            MethodBuilder getterMethod = builder.DefineMethod(
                string.Concat(GetterPrefix, propertyName),
                propertyMethodAttributes, propertyType, Type.EmptyTypes);

            // Emit the IL code.
            // ldarg.0
            // ldfld,_field
            // ret
            ILGenerator getterILCode = getterMethod.GetILGenerator();
            getterILCode.Emit(OpCodes.Ldarg_0);
            getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
            getterILCode.Emit(OpCodes.Ret);

            // Define the setter method.
            MethodBuilder setterMethod = builder.DefineMethod(
                string.Concat(SetterPrefix, propertyName),
                propertyMethodAttributes, null, new Type[] { propertyType });

            // Emit the IL code.
            // ldarg.0
            // ldarg.1
            // stfld,_field
            // ret
            ILGenerator setterILCode = setterMethod.GetILGenerator();
            setterILCode.Emit(OpCodes.Ldarg_0);
            setterILCode.Emit(OpCodes.Ldarg_1);
            setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
            setterILCode.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterMethod);
            propertyBuilder.SetSetMethod(setterMethod);
        }    


    Thursday, November 8, 2018 3:12 AM

All replies

  • I have a stored procedure which gives a dynamic result set

    (Eg1. storedProcedureNameXX 4 - sql server result  may be 5 columns)
    (Eg2. storedProcedureNameXX 1 - sql server result  may be 3 columns)

    Lets assume i have added the dynamic columns in the Type Builder: TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType")
    //Todo:get the dynamic column names
    TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType")<br>
    //Todo:get the dynamic column names
    CreateAutoImplementedProperty(builder, "column1", typeof(string));
    CreateAutoImplementedProperty(builder, "column2", typeof(string));
    CreateAutoImplementedProperty(builder, "column3", typeof(string));

    Type resultType = builder.CreateType();
       var parameters = new List<SqlParameter>();
       parameters.Add(new SqlParameter("parm1", 1));

    var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.ToArray());

    Error in P1 variable:
    "The SqlParameter is already contained by another SqlParameterCollection" Unable to place ToList()


    //Added methods used
    private static TypeBuilder createTypeBuilder( string assemblyName, string moduleName, string typeName) 

    TypeBuilder typeBuilder = AppDomain.CurrentDomain
    .DefineDynamicAssembly(new AssemblyName(assemblyName), AssemblyBuilderAccess.Run)
    .DefineDynamicModule(moduleName)
    .DefineType(typeName, TypeAttributes.Public); 
    typeBuilder.DefineDefaultConstructor(MethodAttributes.Public); 
    return typeBuilder; 

    private static void createAutoImplementedProperty( TypeBuilder builder, string propertyName, Type propertyType) { const string PrivateFieldPrefix = "m_"; const string GetterPrefix = "get_"; const string SetterPrefix = "set_";

     // Generate the field.
            FieldBuilder fieldBuilder = builder.DefineField(
                string.Concat(PrivateFieldPrefix, propertyName),
                              propertyType, FieldAttributes.Private);

            // Generate the property
            PropertyBuilder propertyBuilder = builder.DefineProperty(
                propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

            // Property getter and setter attributes.
            MethodAttributes propertyMethodAttributes =
                MethodAttributes.Public | MethodAttributes.SpecialName |
                MethodAttributes.HideBySig;

            // Define the getter method.
            MethodBuilder getterMethod = builder.DefineMethod(
                string.Concat(GetterPrefix, propertyName),
                propertyMethodAttributes, propertyType, Type.EmptyTypes);

            // Emit the IL code.
            // ldarg.0
            // ldfld,_field
            // ret
            ILGenerator getterILCode = getterMethod.GetILGenerator();
            getterILCode.Emit(OpCodes.Ldarg_0);
            getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
            getterILCode.Emit(OpCodes.Ret);

            // Define the setter method.
            MethodBuilder setterMethod = builder.DefineMethod(
                string.Concat(SetterPrefix, propertyName),
                propertyMethodAttributes, null, new Type[] { propertyType });

            // Emit the IL code.
            // ldarg.0
            // ldarg.1
            // stfld,_field
            // ret
            ILGenerator setterILCode = setterMethod.GetILGenerator();
            setterILCode.Emit(OpCodes.Ldarg_0);
            setterILCode.Emit(OpCodes.Ldarg_1);
            setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
            setterILCode.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterMethod);
            propertyBuilder.SetSetMethod(setterMethod);
        }    

     // Generate the field.
            FieldBuilder fieldBuilder = builder.DefineField(
                string.Concat(PrivateFieldPrefix, propertyName),
                              propertyType, FieldAttributes.Private);

            // Generate the property
            PropertyBuilder propertyBuilder = builder.DefineProperty(
                propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

            // Property getter and setter attributes.
            MethodAttributes propertyMethodAttributes =
                MethodAttributes.Public | MethodAttributes.SpecialName |
                MethodAttributes.HideBySig;

            // Define the getter method.
            MethodBuilder getterMethod = builder.DefineMethod(
                string.Concat(GetterPrefix, propertyName),
                propertyMethodAttributes, propertyType, Type.EmptyTypes);

            // Emit the IL code.
            // ldarg.0
            // ldfld,_field
            // ret
            ILGenerator getterILCode = getterMethod.GetILGenerator();
            getterILCode.Emit(OpCodes.Ldarg_0);
            getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
            getterILCode.Emit(OpCodes.Ret);

            // Define the setter method.
            MethodBuilder setterMethod = builder.DefineMethod(
                string.Concat(SetterPrefix, propertyName),
                propertyMethodAttributes, null, new Type[] { propertyType });

            // Emit the IL code.
            // ldarg.0
            // ldarg.1
            // stfld,_field
            // ret
            ILGenerator setterILCode = setterMethod.GetILGenerator();
            setterILCode.Emit(OpCodes.Ldarg_0);
            setterILCode.Emit(OpCodes.Ldarg_1);
            setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
            setterILCode.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterMethod);
            propertyBuilder.SetSetMethod(setterMethod);
        }    

    Already tried solutions:

    1. var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    2.  var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.Select(=> ((ICloneable)x).Clone()).ToArray()).ToList();


    note: resulttype is a dynamic class which will be created at run time.

    In the above tried solutions the ".ToList()" does not appear in IntelliSense

    Wednesday, November 7, 2018 3:53 PM
  • if  you are using dynamic, then you should be using dynamic.

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/types/using-type-dynamic

    If ToList() can't be used is becuase a List<T> can't be generated from the result.

     
    Wednesday, November 7, 2018 5:00 PM
  • Hello,

    I think that the issue is that the SqlQuery() method find that the "@param1" is already defined,  which is explicit but not so clear in the error message:

    Type resultType = builder.CreateType();
       var parameters = new List<SqlParameter>(); 
       parameters.Add(new SqlParameter("parm1", 1)); // <-- here the name of the parameter is incorrect, it should be "@param1"
    
    // var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.ToArray());
    
       var p1 = entity.Database.SqlQuery(resultType,"storedProcedureNameXX @parm1", new SqlParameter("@parm1", 1)); 
    /* Error in P1 variable:
    "The SqlParameter is already contained by another SqlParameterCollection" 
    I think that the reason behind this execption is that there is already a parameter declared @param1 and you try to add a new one parm1 which is not in the sql query statment*/ 

    anyway, I'll try to reproduce the issue on my workstation to investigate directly at runtime.

    Got it! we should reverse the responsibilities and extract a method from our code that will call the SqlQuery<> generic method, and then using reflection we'll call our newly created method using MakeGenericMethod to specify the type and then Invoke it:

    public List<TType> ExecuteQuery<TType>(string sqlString, SqlParameter[] parameters)
    {
        List<TType> resultToReturn;
        using (entity = new AppDataContext())
        {
            resultToReturn = entity.Database.SqlQuery<TType>(sqlString, parameters).ToList();
        }
        return resultToReturn;
    }

    and to call it we reflect on our class to call the method:

    public void execute_testProcedure()
    {
        TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType");
        //Todo:get the dynamic column names
        CreateAutoImplementedProperty(builder, "Column1", typeof(string));
        CreateAutoImplementedProperty(builder, "Column2", typeof(string));
        CreateAutoImplementedProperty(builder, "Column3", typeof(string));
    
        Type resultType = builder.CreateType();
    
        var parameters = new List<SqlParameter>({new SqlParameter("parm1", "4"});
        parameters.Add());
    
        var executeQueryMethod = this.GetType().GetMethod("ExecuteQuery").MakeGenericMethod(resultType);
    
        var result = executeQueryMethod.Invoke(this, new object[] { "testProcedure @parm1", parameters.ToArray() });
    }


    Best Regards,

    Good Coding;





    Wednesday, November 7, 2018 9:41 PM
  • Hi DA924x,

    I did try to place in the dynamic

    var p1 = entity.Database.SqlQuery<dynamic>("exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    I got a list of objects, I wont be able iterate them to get the values since the type is not known.
    Is there anyway to Cast the List of objects to a specific type since. Presently i have created the "returnType" is a variable which at run time has the type.
    Thursday, November 8, 2018 12:31 AM
  • for the generic list type, we can simply call the method MakeGeneric(typeof(List<>)) :

    Type resultType = builder.CreateType();
    Type listType = typeof(List<>).MakeGenericType(resultType);
    var result = entity.Database.SqlQuery(listType, "testProcedure @parm1", parameters.ToArray()).ToListAsync().Result;

    Thursday, November 8, 2018 1:05 AM
  • Hi Cherkaoui.Mouad,

    I tried the 

    Type resultType = builder.CreateType();
    Type listType = typeof(List<>).MakeGenericType(resultType);
    var result = entity.Database.SqlQuery(listType, "testProcedure @parm1", parameters.ToArray()).ToListAsync().Result;

    The ".ToListAsync()" is not appearing in IntelliSense, so the same error is appearing "The SqlParameter is already contained by another SqlParameterCollection"
     
    Thursday, November 8, 2018 1:13 AM
  • Hi DreamCatcher,
    what about ToList() method!
    I think I have a different EF version, or I miss a namespace, I can't find the ToList() method on intellisense,  which version are you using?


    and here is another code that works for me, I used reflection to get method to use:
    TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType");
    //Todo:get the dynamic column names
    CreateAutoImplementedProperty(builder, "Column1", typeof(string));
    CreateAutoImplementedProperty(builder, "Column2", typeof(string));
    CreateAutoImplementedProperty(builder, "Column3", typeof(string));
    
    Type resultType = builder.CreateType();
    Type listType = typeof(List<>).MakeGenericType(resultType);
    var parameters = new List<SqlParameter>();
    parameters.Add(new SqlParameter("@parm1", "7"));
    using (entity = new AppDataContext())
    {
        var genericQueryMethod = entity.Database.GetType().GetMethod("SqlQuery", new Type[] { typeof(string), typeof(SqlParameter[]) }).MakeGenericMethod(resultType);
        var result = genericQueryMethod.Invoke(entity.Database, new object[] { "testProcedure @parm1", parameters.ToArray() });
        var toListAsyncMethod = result.GetType().GetMethod("ToListAsync", new Type[] { });
        var resultList = toListAsyncMethod.Invoke(result, null);
        // var resultAsync = entity.Database.SqlQuery(listType, "testProcedure @parm1", parameters.ToArray()).ToListAsync();
        // var result = entity.Database.SqlQuery(listType, "testProcedure @parm1", parameters.ToArray()).ToList();
    
    }
    but I still can't get the ToList method, in my case the only situation where I found it is when using "SqlQuery<TType>" variety of the method, and when I'm using reflection all I can get is the ToListAsync method.

    Hope it Helps;
    Thursday, November 8, 2018 1:22 AM
  • Hi Da924x,
    I tried converting the List of objects  to the return type by using the following code.

    Type resultType = builder.CreateType(); // dynamic created class
    var p1 = entity.Database.SqlQuery<dynamic>("exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    var t = ConvertDynamicType(p1, resultType);

    // Method Used:
      public static dynamic ConvertDynamicType(dynamic source, Type dest)
      {
          return Convert.ChangeType(source, dest);
       }

    Error:
    Object must implement IConvertible

    Is there any way to iterate and get the data?

    Thursday, November 8, 2018 1:25 AM
  • Hi Cherkaoui.Mouad,

    I am using EF5.0
    Thursday, November 8, 2018 1:41 AM
  • Hi Cherkaoui.Mouad,

    var genericQueryMethod = entity.Database.GetType().GetMethod("SqlQuery", new Type[] { typeof(string), typeof(SqlParameter[]) }).MakeGenericMethod(resultType);
    var result = genericQueryMethod.Invoke(entity.Database, new object[] { "testProcedure @parm1", parameters.ToArray() });
    var toListAsyncMethod = result.GetType().GetMethod("ToListAsync", new Type[] { });
    var resultList = toListAsyncMethod.Invoke(result, null);


    Error Message @ variable result  = "The SqlParameter is already contained by another SqlParameterCollection."
    Thursday, November 8, 2018 1:58 AM
  • Hi DreamerCatcher2018,

    Thank you for posting here.

    Since your question is more related to EF, I will move it to ADO.NET Entity Framework and LINQ to Entities forum for suitable support.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework&filter=alltypes&sort=lastpostdesc

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 8, 2018 2:45 AM
  • Already tried solutions:

    1. var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    2.  var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.Select(=> ((ICloneable)x).Clone()).ToArray()).ToList();

    3. var p1 = entity.Database.SqlQuery<dynamic>( "exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    note: resulttype is a dynamic class which will be created at run time.<br>* In the above tried solutions (1 and 2) the ".ToList()" does not appear in IntelliSense

    * Solution 3 : I was able to get a list of Objects, but unable to Convert to strong type. Is there any way to Iterate ?
           // Method Used in Solution 3:
                   public static dynamic ConvertDynamicType(dynamic source, Type dest){  return Convert.ChangeType(source, dest); }
                      Error:   Object must implement IConvertible

    Thursday, November 8, 2018 3:12 AM
  • Already tried solutions:

    1. var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    2.  var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.Select(=> ((ICloneable)x).Clone()).ToArray()).ToList();

    3. var p1 = entity.Database.SqlQuery<dynamic>( "exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

    note: resulttype is a dynamic class which will be created at run time.<br>* In the above tried solutions (1 and 2) the ".ToList()" does not appear in IntelliSense

    * Solution 3 : I was able to get a list of Objects, but unable to Convert to strong type. Is there any way to Iterate ?
           // Method Used in Solution 3:
                   public static dynamic ConvertDynamicType(dynamic source, Type dest){  return Convert.ChangeType(source, dest); }
                      Error:   Object must implement IConvertible

    Thursday, November 8, 2018 3:13 AM
  • then this time you should look in this direction:

    Type resultType = builder.CreateType();
       var parameters = new List<SqlParameter>(); 
       parameters.Add(new SqlParameter("parm1", 1)); // <-- here the name of the parameter is incorrect, it should be "@param1"
    
    // var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.ToArray());
    
       var p1 = entity.Database.SqlQuery(resultType,"storedProcedureNameXX @parm1", new SqlParameter("@parm1", 1)); 
    /* Error in P1 variable:
    "The SqlParameter is already contained by another SqlParameterCollection" 
    I think that the reason behind this execption is that there is already a parameter declared @param1 and you try to add a new one parm1 which is not in the sql query statment*/ 

    Got it! we should reverse the responsibilities and extract a method from our code that will call the SqlQuery<> generic method, and then using reflection we'll call our newly created method using MakeGenericMethod to specify the type and then Invoke it:

    public List<TType> ExecuteQuery<TType>(string sqlString, SqlParameter[] parameters)
    {
        List<TType> resultToReturn;
        using (entity = new AppDataContext())
        {
            resultToReturn = entity.Database.SqlQuery<TType>(sqlString, parameters).ToList();
        }
        return resultToReturn;
    }

    and to call it we reflect on our class to call the method:

    public void execute_testProcedure()
    {
        TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType");
        //Todo:get the dynamic column names
        CreateAutoImplementedProperty(builder, "Column1", typeof(string));
        CreateAutoImplementedProperty(builder, "Column2", typeof(string));
        CreateAutoImplementedProperty(builder, "Column3", typeof(string));
    
        Type resultType = builder.CreateType();
    
        var parameters = new List<SqlParameter>({new SqlParameter("parm1", "4"});
        parameters.Add());
    
        var executeQueryMethod = this.GetType().GetMethod("ExecuteQuery").MakeGenericMethod(resultType);
    
        var result = executeQueryMethod.Invoke(this, new object[] { "testProcedure @parm1", parameters.ToArray() });
    }

    Best Regards,

    Mouad.




    Thursday, November 8, 2018 4:48 AM
  • You can use late binding on a dynamic object, as long as  you know the name of the property in the object.

    dynamic dynamlist = GetDynamicList();
    
    foreach (var obj in dynamlist )
    {
       string firstname = obj.firstname;
    }

    The name of the property is case sensitive. So you have to use the debugger's Quickwatch to look at the objects in the list in order to get the exact name of the object's property based on case. 


    • Edited by DA924x Thursday, November 8, 2018 6:05 AM
    Thursday, November 8, 2018 6:02 AM
  • Hi Mouad,

    Will try this out ...thank you. hope it works.

    Thursday, November 8, 2018 6:25 AM
  • Hi DA924x,

    foreach (var obj in dynamlist )
    {
       string firstname = obj.firstname;
    }

    Is there any other way to get all the feild names without explicitly giving the feildname

    Thursday, November 8, 2018 6:26 AM
  • Hi  Cherkaoui.Mouad,

    I am getting this error ?

    Thursday, November 8, 2018 6:50 AM
  • Hi DA924x,

    I am able to see the values (field names with values) in the List. how to iterate and get the values.

    for my requirement, I can't place the field name ...
    Is there any way to iterate

    Thursday, November 8, 2018 7:16 AM
  • Hi DA924x,

    I am able to see the values (field names with values) in the List. how to iterate and get the values.

    for my requirement, I can't place the field name ...
    Is there any way to iterate

    No, not that I know about, other than what is being shown in the C# forum, which is way too much code to so something so simple. 

    Object has properties, and they are not fields.

    The only other way you could do this simply is to simply have a custom type, a concrete object,  that matches the properties of the dynamic object is the dynamic list and cast the dynamic object to the concrete object.

    A known type DtoProject is being returned from a Web service call in a Json format, a Json array, that is parsed into a dynamic list of objects that are cast to a DtoProject and loaded into a collection. 

    public List<DtoProject> GetProjsByUserIdApi(string userid)
            {
                var dtoprojects = new List<DtoProject>();
    
                using (var client = new HttpClient())
                {
                    var uri = new Uri("http://progmgmntcore2api.com/api/project/GetProjsByUserId?userid=" + userid);
    
                    var response = client.GetAsync(uri).Result;
    
                    if (!response.IsSuccessStatusCode)
                        throw new Exception(response.ToString());
    
                    var responseContent = response.Content;
                    var responseString = responseContent.ReadAsStringAsync().Result;
    
                    dynamic projects = JArray.Parse(responseString) as JArray;
    
                    foreach (var obj in projects)
                    {
                        DtoProject dto = obj.ToObject<DtoProject>();
    
                        dtoprojects.Add(dto);
                    }
                }
    
                return dtoprojects;
            }

    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }




    • Edited by DA924x Thursday, November 8, 2018 7:52 AM
    Thursday, November 8, 2018 7:49 AM
  • Hi DA924x,

    i don't want to write to separate object nor create class, since i need to values directly to write to excel

    Thursday, November 8, 2018 7:57 AM
  • Hi  Cherkaoui.Mouad,

    I am getting this error ?


    did you take a look at the parameters declaration, one of them is not in its good type, maybe you passing a string as parameter value while it should be an integer:


    Hope It Works :)

    Thursday, November 8, 2018 10:10 AM
  • Hi DA924x,

    i don't want to write to separate object nor create class, since i need to values directly to write to excel

    You don't make a whole lot of sense here. Your options are  limited as to what you can do with a dynamic object. 

    You can take the collection of concrete objects and use one of the many .NET Excel tools that can be installed with Nuget and work with Excel using Linq to query the collection and load an Excel spreadsheet. 

    Thursday, November 8, 2018 12:11 PM
  • Hi DreamerCatcher2018,

    there is a problem with the SqlParameter you pass which is not the same type as the stored procedure parameter declaration, then investigate in this direction, for the reflection part and other suggestions you can isolate them to test their functionality, and test the call to the stored procedure just using Ado.Net SqlCommand, this will helps you to find what caused the exception.

    Good Coding;

    Thursday, November 8, 2018 1:01 PM
  • Hi DreamerCatcher2018,

    According to your description and requirement, I would suggest that you could store the records into a dictionary, like this:

    public List<Dictionary<string, object>> Read(DbDataReader reader)
    {
     List<Dictionary<string, object>> expandolist = new List<Dictionary<string, object>>();
     foreach (var item in reader)
     {
       IDictionary<string, object> expando = new ExpandoObject();
       foreach (PropertyDescriptor propertyDescriptor in TypeDescriptor.GetProperties(item))
       {
       	 var obj = propertyDescriptor.GetValue(item);
    	 expando.Add(propertyDescriptor.Name, obj);
       }
       expandolist.Add(new Dictionary<string, object>(expando));
     }
    return expandolist;
    }

    #Usage:

    using (var ctx = new YourDbContext())
      using (var cmd = ctx.Database.Connection.CreateCommand())
      {
    	ctx.Database.Connection.Open();
    	cmd.CommandText = "EXEC DynamicPivot";
    	using (var reader = cmd.ExecuteReader())
    	{
    	  var model =  Read(reader).ToList();
    	  return View(model);
    	}
      }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 9, 2018 1:49 AM
    Moderator