inserting null values in datetime column and integer column

Question

I am using C# and developing a winform application. I have a project class which has the project attributes.

the constructor of the project class is as follows:

newProject = new Project(GCD_ID.IsNull() ? (int?)null : Convert.ToInt32(GCD_ID), txt_Proj_Desc.Text, txt_Prop_Name.Text, ST.ID.ToString().IsNull() ? null: ST.ID.ToString(), cmbCentre.Text, 
                                     SEC.ID.ToString().IsNull() ? null : SEC.ID.ToString(), cmbZone.Text,
                                     FD.ID.ToString().IsNull() ? null : FD.ID.ToString(), DT.ID.ToString().IsNull() ? null : DT.ID.ToString(), OP.ID.ToString().IsNull() ? null : OP.ID.ToString(), T.ID.ToString().IsNull() ? null : T.ID.ToString(),
                                     CKV.ID.ToString().IsNull() ? null : CKV.ID.ToString(), STAT.ID.ToString().IsNull() ? null : STAT.ID.ToString(), MW.IsNull() ? (Double?)null : Convert.ToDouble(MW),
                                     txt_Subject.Text, Ip_Num.IsNull() ? (int?)null : Convert.ToInt32(Ip_Num), H1N_ID.IsNull() ? (int?)null : Convert.ToInt32(H1N_ID), 
                                     NOMS_Slip_Num.IsNull() ? (int?)null : Convert.ToInt32(NOMS_Slip_Num), NMS_Updated.IsNull() ? (DateTime?)null : Convert.ToDateTime(NMS_Updated),
                                     Received_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Received_Date), Actual_IS_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Actual_IS_Date),
                                     Scheduled_IS_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Scheduled_IS_Date), UpSt.ID.ToString().IsNull() ? null : UpSt.ID.ToString(),
                                     UpFd.ID.ToString().IsNull() ? null : UpFd.ID.ToString(), txtHVCircuit.Text, cmbbxSIA.Text);

My problem is that i cannot insert values into the database when the datetime variables and the integer variables are null. all this data are assigned to the variable from textboxes on the form..

bELOW is the database function which takes in all the variables and insert them into the database.

public static void createNewProject(int? GCD_ID, string Project_Desc, string Proponent_Name, int Station_ID, string OpCentre, int Sector_ID, string PLZone, int Feeder, int DxTx_ID, int OpControl_ID, int Type_ID, int ConnKV_ID, int Status_ID, double? MW, string Subject, int? Ip_Num, int? H1N_ID, int? NOMS_Slip_Num, DateTime? NMS_Updated, DateTime? Received_Date, DateTime? Actual_IS_Date, DateTime? Scheduled_IS_Date, int UP_Station_ID, int UP_Feeder_ID, string @HV_Circuit, string SIA_Required)
    {
        SqlConnection conn = null;
        try
        {
            //Takes in all the employee details to be added to the database.
            conn = new SqlConnection(databaseConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("createNewProject", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("GCD_ID", GCD_ID));
            cmd.Parameters.Add(new SqlParameter("Project_Desc", MiscFunctions.Capitalize(Project_Desc)));
            cmd.Parameters.Add(new SqlParameter("Proponent_Name", MiscFunctions.Capitalize(Proponent_Name)));
            cmd.Parameters.Add(new SqlParameter("Station_ID", Station_ID));
            cmd.Parameters.Add(new SqlParameter("OpCentre", OpCentre));
            cmd.Parameters.Add(new SqlParameter("Sector_ID", Sector_ID));
            cmd.Parameters.Add(new SqlParameter("PLZone", PLZone));
            cmd.Parameters.Add(new SqlParameter("Feeder", Feeder));
            cmd.Parameters.Add(new SqlParameter("DxTx_ID", DxTx_ID));
            cmd.Parameters.Add(new SqlParameter("OpControl_ID", OpControl_ID));
            cmd.Parameters.Add(new SqlParameter("Type_ID", Type_ID));
            cmd.Parameters.Add(new SqlParameter("ConnKV_ID", ConnKV_ID));
            cmd.Parameters.Add(new SqlParameter("Status_ID", Status_ID));
            cmd.Parameters.Add(new SqlParameter("MW", MW));
            cmd.Parameters.Add(new SqlParameter("Subject", Subject));
            cmd.Parameters.Add(new SqlParameter("Ip_Num", Ip_Num));
            cmd.Parameters.Add(new SqlParameter("H1N_ID", H1N_ID));
            cmd.Parameters.Add(new SqlParameter("NOMS_Slip_Num", NOMS_Slip_Num));
            cmd.Parameters.Add(new SqlParameter("NMS_Updated", NMS_Updated));
            cmd.Parameters.Add(new SqlParameter("Received_Date", Received_Date));
            cmd.Parameters.Add(new SqlParameter("Actual_IS_Date", Actual_IS_Date));
            cmd.Parameters.Add(new SqlParameter("Scheduled_IS_Date", Scheduled_IS_Date));
            cmd.Parameters.Add(new SqlParameter("UP_Station_ID", UP_Station_ID));
            cmd.Parameters.Add(new SqlParameter("UP_Feeder_ID", UP_Feeder_ID));
            cmd.Parameters.Add(new SqlParameter("HV_Circuit", HV_Circuit));
            cmd.Parameters.Add(new SqlParameter("SIA_Required", SIA_Required));
            cmd.ExecuteNonQuery();
        }
        catch (Exception e) //returns if error incurred.
        {
            MessageBox.Show("Error occured in createNewProject" + Environment.NewLine + e.ToString());
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

My question is, how do i insert values into the database. please please help

Answer 1

You need to check if the value is null, and if so then insert System.DBNull.Value instead of the value.

<EDIT> In addition to that, I encourage you to create a structure (struct) to hold all of that data inside of a single data type instead of having a function signature with a bajillion parameters. </EDIT>

Answer 2

Couldn't you just check for null before inserting into the database, and use some sensible default value instead??

public static void createNewProject(int? GCD_ID........)
{
    if(!GCD_ID.HasValue)
    {
        GCD_ID = 0;
    }
    .........

    if(!Received_Date.HasValue)
    { 
        Received_Date = DateTime.Today;
    }
    .......
 }

PS: have you ever considered putting all those individual values into a data transfer object (e.g. NewProjectDTO, and just passing in a single instance of that class?

public class NewProjectDTO
{
    int GCD_ID { get; set; }
    string Project_Desc { get; set; } 
    string Proponent_Name { get; set; } 
     ....... (and so on)
} 

public static void createNewProject(NewProjectDTO newProjectValues)
{
 .....
}

As soon as you have more than 3, 4 parameters, you should really consider putting those into a transfer DTO of some sort!

Also, if you had such an object, you might even add some logic to it to e.g. replace NULL values with sensible defaults before saving it....

Answer 3

Are you getting an error saying that the parameter is missing? If so, for each of the paramaters that have a null value, use DBNull.Value instead.

So do this, or some variation of it:

if(H1N_ID != null)
{
   cmd.Parameters.Add(new SqlParameter("H1N_ID",HIN_ID);
}
else
{
   cmd.Parameters.Add(new SqlParameter("H1N_ID",DBNull.Value);
}

Also, anytime you are reading those fields out of the database into your objects, you should check for the DBNull.Value value and convert that to null.

Answer 4

Are you sure your database allows nulls in the columns you are inserting? If not then you need to change DB to support null values or insert default values for each type.