Welcome to Pegasus Mail & Mercury Sign in | Join | Help

Peter Strömblad

Peter lives in Sweden and is an independent contractor with hosting services.
C# Null, DBNull and Entities

I've been working like mad to get my 3-tier construction to work. As I'm not all that great in C# yet, I delightedly discovered that C# supports nullable types. Now finally I can match my database constructions with a proper entity in code, and as well build the business layer so that it deals with the values as it should.

Some of you know that a relational database is perfect when it comes to relating a drop down with a key value, then fetching the meaning of the key value from the related table. But if the drop down hasn't been selected, you should not store an arbitrary value - you store NULL = unknown.

Now I came across a tool with source from Rick Strahls weblog, called DataRowGenerator. I've managed to alter this so that it now suits my needs by creating one entity file for each datatable. Below is a sample of the code that the program now generates automatically for all my 54 tables.

Here are some reference links that you may find useful.

http://blogs.msdn.com/thottams/archive/2008/06/30/dbnull-and-nullable-types.aspx
http://sab39.netreach.com/Blog/Blog/12/vobId__172/pm__18/
http://www.codeproject.com/KB/architecture/three_tier_architecture.aspx
http://west-wind.com/weblog/posts/147.aspx

 

/* ArtikelGrp.cs (c) Petena AB 1994-2008, http://www.petena.se, http://www.praktit.se


DEVELOPMENT HISTORY: ******************************************************************************
v0.01
  20080825 PiS -> Start

BUGS & NOTES: *********** ( !=Warning, +=Additions to be made, -=Comment ) ************************
  - 
 */

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace PraktIT.CRM.Data {

	[Serializable]
	public partial class ArtikelGrp {
				
    #region Private Members
		  private Int32? _ID = null;   //  
		  private Guid? _UID = null;   //  
		  private Int32? _ArtikelGrp_ID_Parent = null;   //  
		  private Int32? _Nr = null;   //  
		  private String _Kod = null;   //  
		  private String _Namn = null;   //  
		  private String _Titel = null;   //  
		  private Boolean? _Aktiv = null;   //  
		  private Guid? _Firma_UID_Owner = null;   //  

    #endregion

    #region Public Properties
        public Int32? ID
        {   get { return _ID; }
            set { _ID = value; }  }

        public Guid? UID
        {   get { return _UID; }
            set { _UID = value; }  }

        public Int32? ArtikelGrp_ID_Parent
        {   get { return _ArtikelGrp_ID_Parent; }
            set { _ArtikelGrp_ID_Parent = value; }   }

        public Int32? Nr
        {   get { return _Nr; }
            set { _Nr = value; }  }

        public String Kod
        {   get { return _Kod; }
            set { _Kod = value; }  }

        public String Namn
        {   get { return _Namn; }
            set { _Namn = value; }    }

        public String Titel
        {   get { return _Titel; }
            set { _Titel = value; }   }

        public Boolean? Aktiv
        {   get { return _Aktiv; }
            set { _Aktiv = value; }  }

        public Guid? Firma_UID_Owner
        {   get { return _Firma_UID_Owner; }
            set { _Firma_UID_Owner = value; }  }


    #endregion

    #region Populate
      public static ArtikelGrp Populate(IDataReader dr) 
      {
        ArtikelGrp _ArtikelGrp = new ArtikelGrp();
        Populate(dr, ref _ArtikelGrp);
        return _ArtikelGrp;
      }

      public static void Populate(IDataReader dr, ref ArtikelGrp _ArtikelGrp)
      {
        _ArtikelGrp.ID = dr["ID"] as Int32?;
        _ArtikelGrp.UID = dr["UID"] as Guid?;
        _ArtikelGrp.ArtikelGrp_ID_Parent = dr["ArtikelGrp_ID_Parent"] as Int32?;
        _ArtikelGrp.Nr = dr["Nr"] as Int32?;
        _ArtikelGrp.Kod = dr["Kod"] as String;
        _ArtikelGrp.Namn = dr["Namn"] as String;
        _ArtikelGrp.Titel = dr["Titel"] as String;
        _ArtikelGrp.Aktiv = dr["Aktiv"] as Boolean?;
        _ArtikelGrp.Firma_UID_Owner = dr["Firma_UID_Owner"] as Guid?;
        }
    #endregion

	}

	public class ArtikelGrpDataProvider : DataProvider {

  #region Get using Select
    public ArtikelGrp GetArtikelGrp(Int32 ArtikelGrp_ID)
    {
      SqlConnection conn = new SqlConnection(ConnectionString);
      string _SqlCmdTxt = "Select * FROM ArtikelGrp WHERE ID = @pk";
      SqlCommand cmd = new SqlCommand(_SqlCmdTxt, conn);
      cmd.Parameters.AddWithValue("@pk", ArtikelGrp_ID);
      conn.Open();
      SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      if (!dr.Read())
      {
        dr.Close();
        conn.Close();
        throw new Exception("ArtikelGrp not found!\r\nID = " + ArtikelGrp_ID);
      }
      else
      {
        ArtikelGrp _ArtikelGrp = ArtikelGrp.Populate(dr);
        dr.Close();
        conn.Close();
        return _ArtikelGrp;
      }
    }
  #endregion

  #region Get using Stored Procedure
    public ArtikelGrp GetArtikelGrp(Int32 ArtikelGrp_ID, Guid Person_UID)
    {
      SqlConnection conn = new SqlConnection(ConnectionString);
      SqlCommand cmd = new SqlCommand("sp_ArtikelGrp_Get", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@ID", ArtikelGrp_ID);
      cmd.Parameters.AddWithValue("@Person_UID", Person_UID);
      conn.Open();
      SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      if (!dr.Read())
      {
        dr.Close();
        conn.Close();
        throw new Exception("ArtikelGrp not found!\r\nID = " + ArtikelGrp_ID + "\r\nPerson_UID = " + Person_UID);
      }
      else
      {
        ArtikelGrp _ArtikelGrp = ArtikelGrp.Populate(dr);
        dr.Close();
        conn.Close();
        return _ArtikelGrp;
      }
    }
  #endregion
	}

}
 
Posted: Monday, August 25, 2008 10:59 AM by Peter Strömblad

Comments

richardalgor said:

You can check C# dbnull value using System.DBNull.Value

http://net-informations.com/csprj/ado.net/cs-dbnull.htm

richie.

# March 23, 2012 7:38 AM
Anonymous comments are disabled