.NET validate if a column exists in an IDataRecord C #

Question

.NET validate if a column exists in an IDataRecord C #

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have a constructor that I feed through a query to BBDD through a method that, when called from different points of the application, will cause the result obtained from SELECT no have the same columns in all cases.

To give you an idea, this is the method that returns the object I want to create:

private static SMOrder CreateSMOrderObjectFromDataRecord(IDataRecord record)
{
        return new SMOrder(){
            Society =       (int)record["SOCIETY_ID"],    //no siempre llega
            Division =      (int)record["DIVISION_ID"],   //no siempre llega
            Order =         (string)record["ORDER"],   
            Lang =          (string)record["LANG_ID"],
            Description =   (string)record["DESCRIPTION"] //no siempre llega
        };
    }

With the following code I can check if the value that arrives from BBDD is empty or not before assigning it to my variable, but it only seems to work with strings :

string description = !String.IsNullOrEmpty((string)record["DESCRIPTION"].ToString()) ? (string)record["DESCRIPTION"] : null

I need to do the same for a whole value int and thus take advantage of the call to the same method.

I have tried with the following possibilities:

Society = (int)record["SOCIETY_ID"] == null  ? 0 : (int)record["SOCIETY_ID"],
Society = (int)record["SOCIETY_ID"] ? 0 : (int)record["SOCIETY_ID"],
Society = !String.IsNullOrEmpty(record["SOCIETY_ID"].ToString()) ? (int)record["SOCIETY_ID"] : 0,

But I always get false since never an integer = null .

How can I solve this without having to modify the SELECT ?

EDIT

The problem that I face is that record["SOCIETY_ID"] (or other columns) does not exist within record and when doing the current check, or the ToString() peta and gives the following error:
< strong> Exception details : System.IndexOutOfRangeException: SOCIETY_ID.

I've tried the examples of this question and their answers but still giving the same error.

c# .net

asked by AXL 26.04.2017 в 16:20

source

2 answers

1

With the current design you have two options:

Try to get the position or value of the field by name (using GetOrdinal or GetInt32 ) and handle the exception IndexOutOfRangeException .

Before reading the data, obtain the names of all the fields contained in the IDataRecord using GetName in a cycle that goes from 0 to FieldCount and verify if the field in question exists.

However, I think you should change the design so that in all cases you always have the same fields and if you do not have value that is returned DBNull , since the options mentioned above have an impact on performance .

answered by 26.04.2017 в 17:05

Take Json data Classes in arduino problem with the loop

score 2 · Accepted Answer

The database records columns are not null ever, if not DBNull . To solve your problem, try this code:

Society = record["SOCIETY_ID"] == DBNull.Value  ? 0 : (int)record["SOCIETY_ID"]

EDITING

Ok, I had misunderstood you. To check if a column exists in a IDataRecord you must obtain a list of columns (in a dictionary for example), such as using this extension method:

public static Dictionary<string, int> GetAllNames(this IDataRecord record) {
    var result = new Dictionary<string, int>();
    for (int i = 0; i < record.FieldCount; i++) 
    {
        result.Add(record.GetName(i), i);
    }
    return result;
}

The way to use it would be like this:

if (record.GetAllNames().ContainsKey("SOCIETY_ID"))
{
    Society = (int)record["SOCIETY_ID"];
}
else
{
    Society = 0;
}

Edition 2

Another option on a single line using Enumerable.Range and Any taken from this question :

bool existe = Enumerable.Range(0, record.FieldCount)
                        .Any(x => record.GetName(x) == "SOCIETY_ID")