CODEWITHSUNDEEP
c#ormmappingdapper
napi15
napi15

Reputation: 2402

Why Query returns some null values to my list Object using dapper

I have this functional query where I only used fictive tables names for security concerns :

SELECT 
h.CENID,
 h.BMHFMC, 
 h.BMHDONEMIDASSTEP1, 
 h.BMHDONEMIDASSTEP2,
 h.LMIID, 
 h.BMHHOLD,
 h.BMHBATCHMIDAS,
 h.BMHFMCVALUEDATE AS HeaderValueDate, 
 h.SUNID, 
 h.BRAID,
 d.BMHID, 
 d.BMDRUBRIQUE,
 d.BMDCLIENT,
 d.BMDSEQUENCE,
 d.BMDDATE, 
 d.BMDDEVISE, 
 d.BMDMONTANT,
 d.BMDTYPE, 
 d.BMDNOTE, 
 d.BMDENTRYNBRE,
 v.DEVDECIMAL ,
 NVL(t.TYPVERIFCOMPTEMIDAS, 0) AS TYPVERIFCOMPTEMIDAS 
 FROM dbo.TableOne h 
 INNER JOIN dbo.Tabletwoo d
 ON h.BMHID = d.BMHID 
 INNER JOIN dbo.tableThree v
 ON d.BMDDEVISE = v.DEVID 
 LEFT JOIN dbo.TableFour t 
 ON  t.TYPID=h.BMHFMC  
 WHERE d.BMDMONTANT != 0 
 AND h.BMHDONEMIDASSTEP1 = 0
 AND h.BMHDONEMIDASSTEP2 = 0 
 AND h.LMIID = 0 
 AND h.BMHHOLD = 0

And I made a class in order to bind every fields 

 public class Batch :BaseRepository ,IList<Batch>
    {
        public Batch()
        {
        }

        private string cendid;
        private string bmhfmc;
        private double bmhdonemidasstep1;
        private double bmhdonemidasstep2;
        private double lmiid;
        private double bmhhold;
        private double bmhbatchmidas;
        private DateTime headervaluedateordinal;
        private double sunid; // 
        private string bradid; //
        private double bmhid;
        private string bmdrubirique; //
        private string bmdclient;
        private string bmdsequence;
        private DateTime bmddate;
        private string bmddevise;
        private double bmdmontant;
        private string bmdtype;
        private string bmdnote;
        private string bmdentrynbre; //
        private double devdecimalordinal;
        private double typverifcomptemidasordinal;

        public Batch(string cendid, string bmhfmc, double bmhdonemidasstep1, double bmhdonemidasstep2, double lmiid, double bmhhold, double bmhbatchmidas, DateTime headervaluedateordinal, double sunid, string bradid, double bmhid, string bmdrubirique, string bmdclient, string bmdsequence, DateTime bmddate, string bmddevise, double bmdmontant, string bmdtype, string bmdnote, string bmdentrynbre, double devdecimalordinal, double typverifcomptemidasordinal)
        {
            this.cendid = cendid;
            this.bmhfmc = bmhfmc;
            this.bmhdonemidasstep1 = bmhdonemidasstep1;
            this.bmhdonemidasstep2 = bmhdonemidasstep2;
            this.lmiid = lmiid;
            this.bmhhold = bmhhold;
            this.bmhbatchmidas = bmhbatchmidas;
            this.headervaluedateordinal = headervaluedateordinal;
            this.sunid = sunid;
            this.bradid = bradid;
            this.bmhid = bmhid;
            this.bmdrubirique = bmdrubirique;
            this.bmdclient = bmdclient;
            this.bmdsequence = bmdsequence;
            this.bmddate = bmddate;
            this.bmddevise = bmddevise;
            this.bmdmontant = bmdmontant;
            this.bmdtype = bmdtype;
            this.bmdnote = bmdnote;
            this.bmdentrynbre = bmdentrynbre;
            this.devdecimalordinal = devdecimalordinal;
            this.typverifcomptemidasordinal = typverifcomptemidasordinal;
        }

        public string Cendid
        {
            get { return cendid; }
            set { cendid = value; }
        }

        public string Bmhfmc
        {
            get { return bmhfmc; }
            set { bmhfmc = value; }
        }

        public double Bmhdonemidasstep1
        {
            get { return bmhdonemidasstep1; }
            set { bmhdonemidasstep1 = value; }
        }

        public double Bmhdonemidasstep2
        {
            get { return bmhdonemidasstep2; }
            set { bmhdonemidasstep2 = value; }
        }

        public double Lmiid
        {
            get { return lmiid; }
            set { lmiid = value; }
        }

        public double Bmhhold
        {
            get { return bmhhold; }
            set { bmhhold = value; }
        }

        public double Bmhbatchmidas
        {
            get { return bmhbatchmidas; }
            set { bmhbatchmidas = value; }
        }

        public DateTime Headervaluedateordinal
        {
            get { return headervaluedateordinal; }
            set { headervaluedateordinal = value; }
        }

        public double Sunid
        {
            get { return sunid; }
            set { sunid = value; }
        }

        public string Bradid
        {
            get { return bradid; }
            set { bradid = value; }
        }

        public double Bmhid
        {
            get { return bmhid; }
            set { bmhid = value; }
        }

        public string Bmdrubirique
        {
            get { return bmdrubirique; }
            set { bmdrubirique = value; }
        }

        public string Bmdclient
        {
            get { return bmdclient; }
            set { bmdclient = value; }
        }

        public string Bmdsequence
        {
            get { return bmdsequence; }
            set { bmdsequence = value; }
        }

        public DateTime Bmddate
        {
            get { return bmddate; }
            set { bmddate = value; }
        }

        public string Bmddevise
        {
            get { return bmddevise; }
            set { bmddevise = value; }
        }

        public double Bmdmontant
        {
            get { return bmdmontant; }
            set { bmdmontant = value; }
        }

        public string Bmdtype
        {
            get { return bmdtype; }
            set { bmdtype = value; }
        }

        public string Bmdnote
        {
            get { return bmdnote; }
            set { bmdnote = value; }
        }

        public string Bmdentrynbre
        {
            get { return bmdentrynbre; }
            set { bmdentrynbre = value; }
        }

        public double Devdecimalordinal
        {
            get { return devdecimalordinal; }
            set { devdecimalordinal = value; }
        }

        public double Typverifcomptemidasordinal
        {
            get { return typverifcomptemidasordinal; }
            set { typverifcomptemidasordinal = value; }
        }

Now when I execute the query into a list using dapper 

Connection conn = new Connection();
 OracleConnection connection = conn.GetDBConnection();
  myList= connection.Query<Batch>(querySql).ToList();

Now,while debugging, all fields returns the expected values .But, I noticed those fields below are null in myList not empty but really null , but the problem is they aren't null in the database 

Bmdrubirique , Sunid, Bmdentrynbre, Bradid ,Cenid

In oracle database those fields are like the following : 

CENID is VARCHAR2(3 BYTE)` 
Bmhid is VARCHAR2(3 BYTE) 
Sunid is NUMBER(38,0)
Bradid is VARCHAR2(3 BYTE)

I don't get it , where did it go wrong? why other fields are properly loaded while those returns null value ?

Upvotes: 1

Views: 2020

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063714

My default assumption would be that there is a typo in the real code and the constructor is assigning a value from a field to itself. However, frankly: since you have a public Batch() {} constructor, I'm not sure what the benefit of the second one is - it just adds risk of errors. Likewise with the fields and manual properties.

So if this as me, where you currently have (simplified to two properties):

public class Batch
{
    private string cendid;
    private string bmhfmc;
    public Batch() {}
    public Batch(string cendid, string bmhfmc)
    {
        this.cendid = cendid;
        this.bmhfmc = bmhfmc;
    }

    public string Cendid
    {
        get { return cendid; }
        set { cendid = value; }
    }

    public string Bmhfmc
    {
        get { return bmhfmc; }
        set { bmhfmc = value; }
    }
}

I would have literally just:

public class Batch
{
    public string Cendid {get;set;}
    public string Bmhfmc {get;set;}
}

All of the rest of the code is just opportunities to make coding errors.

Now: the reason that Cendid is null is because: the column is CENID - only one d. This means that dapper isn't even using your custom constructor, because it isn't a perfect match between the constructor and the columns. Ditto the other fields like BRAID vs BRADID.

So the next thing to do is to fix the typos.

Upvotes: 1

Related Questions