It is a variable but it is used like a type C# Compiler error

Question

I have the following code :

public void InsertDataInBulk(DataTable dataTable, string functionName , ILogger logger) 
        {
            try
            {
                var connection = _dbConnect.OpenConnection(logger);
                DbCommand command = connection.CreateCommand();
                command.CommandText = functionName;
                command.CommandType = CommandType.StoredProcedure;
                if (dataTable != null)
                {
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = column.ColumnName;
                        Type type = column.DataType;
                        parameter.Value = dataTable.AsEnumerable().Select(r => r.Field<string>(column.ColumnName)).ToArray();
                        command.Parameters.Add(parameter);
                    }
                }
                _dbConnect.CloseConnection(connection, logger);
            }
            catch (Exception ex)
            {
                logger.LogError(ex, ex.Message);
                throw;
            }
        }

I have one issue in this code and that is : I want to make the following piece of code as generic code:

Type type = column.DataType;
parameter.Value = dataTable.AsEnumerable().Select(r => r.Field<string>(column.ColumnName)).ToArray();

I just want to pass the DataType that I have found in the variable type in the next line as

r => r.Field<type> just to make my code generic as the datatable that I am getting in this function consists of columns of many different data types.

But it gives me compiler error that type is a variable but used like a Type .

How can I solve this ?

Please help.

Thank you

Does this answer your question? [Pass An Instantiated System.Type as a Type Parameter for a Generic Class](https://stackoverflow.com/questions/266115/pass-an-instantiated-system-type-as-a-type-parameter-for-a-generic-class) — GSerg, Jun 28 '21 at 13:44
In case of an error you will never close that connection. Instead of your `DbConnect` class simple use an `using`-statement. — Tim Schmelter, Jun 28 '21 at 14:01
Then you didn't try this code properly @SimranKaur because it is the only way to do it. Please try again. — GSerg, Jun 28 '21 at 14:03
I don't think setting an array to a parameter value will ever work the way you want. — Joel Coehoorn, Jun 28 '21 at 14:13
Yes, i have overlooked what you are trying to do there, this doesn't work, so i have deleted my answer. Why you want to bulk insert all row's fields separately? Instead you can use [`SqlBulkCopy.WriteToServer`](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.writetoserver?redirectedfrom=MSDN&view=dotnet-plat-ext-5.0#System_Data_SqlClient_SqlBulkCopy_WriteToServer_System_Data_DataTable_) to bulk insert the whole table. — Tim Schmelter, Jun 28 '21 at 15:21
@TimSchmelter SqlBulkCopy works for Sql Server Database but I am using postgreSql currently. Also, I have written my code in order to cater 4 databases (SQL, MySql, PostgreSql, Oracle). Is there any Generic Keyword for bulk copy which will be able to cater these databases. ? — Simran Kaur, Jun 28 '21 at 15:43
@SimranKaur: Every vendor has different ways to do what you want. But "bulk copy" or "bulk import" is the right term if you want to find it — Tim Schmelter, Jun 28 '21 at 15:44

score 0 · Answer 1 · answered Jun 28 '21 at 16:54

First of all I must apologize about my English.

You need to use Reflection to do what you want. Something like this:

.Select(r => {
var methodInfo = r.GetType().GetMethod("Field", 1, new Type[] { typeof(string) });
var genericMethod = methodInfo.MakeGenericMethod(type);
return genericMethod.Invoke(r, new object[] {column.ColumnName}) })

I couldn't test this code, so please let me know any issue you have with the code.

This is the explanation of what that does this code do:

var methodInfo = r.GetType().GetMethod("Field", 1, new Type[] { typeof(string) });

It returns an Object which represent the generic method to invoke. It searches a method whose name is "Field", it has 1 generic parameter, and it has 1 argument of type string. Please validate that with method definitions and adjust accordingly.

var genericMethod = methodInfo.MakeGenericMethod(type);

This line convert Field<T> into a specific instance of Field<type> where type is the type you want.

return genericMethod.Invoke(r, new object[] {column.ColumnName})

This last line invokes the method for your object "r" using the column.ColumnName as argument.

I tried the above code that you explained. It says `Npgsql 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead` error. So, instead of using AsEnumerable(), I tried `var rowArray = dataTable.Rows.OfType().Select(k => k[column.ColumnName].ToString()).ToArray();` but it is still giving me the same error that `Npgsql 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead`.. — Simran Kaur, Jun 29 '21 at 05:35

It is a variable but it is used like a type C# Compiler error

1 Answers1