Problem with NULL values and FK constraints

[lbolognini]

Hi all,

I'm building a data mapper class where each property of the class maps
a column in the database (guess this is formally called by Fowler in
PoEAA a Data Mapper or an Active Record pattern).

Ok the problem is I have several columns in the User table which have
Foreign Key constraints and allow NULL values to be stored.

I tried several approaches for passing null values to the db but none
of them seems to work. Columns declared as bigint in SQL Server 2005
got passed a 0 instead of NULL even if I use the following C# code to
prevent it:

1st approach (this is how LLBGEN generates the code)
CreateCommand.Parameters.Add(new SqlParameter("@LangID",
SqlDbType.BigInt, 8, ParameterDirection.Input, true, 19, 0, "",
DataRowVersion.Proposed, LangID));

in this case the "true" which is the fith parameter in this overriden
constructor of the parameter still gets the property initialized at 0
even if I don't set it in my code and even if I execute the code below
in the second approach.

2nd approach
foreach (SqlParameter Parameter in CreateCommand.Parameters)
{
if (Parameter.Value == null)
{ Parameter.Value = DBNull.Value;}
}

2nd-bis approach
foreach (SqlParameter Parameter in CreateCommand.Parameters)
{
if ((Parameter.Value == null) | (Parameter.Value == 0))
{Parameter.Value = DBNull.Value;}
}

this returns the error Operator '==' cannot be applied to operands of
type 'object' and 'int'

3rd approach
Nullable Types declaring long? but gives an error and found out that
ADO.NET doesn't support Nullable types
(http://unboxedsolutions.com/sean/archive/2005/09/10/727.aspx). Not
clear though since using SqlClient ADO.NET should be bypassed.

So any clues of how to handle such problem? I'm pretty sure I'm
overlooking something since it must be a problem faced by everybody
long time ago.

BTW I'm using SqlServer 2005, ADO.NET and C#2 with the .NET framework v
2 obviously.

Thanks in advance,
Lorenzo

 

[lbolognini]

1st approach (this is how LLBGEN generates the code)
CreateCommand.Parameters.Add(new SqlParameter("@LangID",
SqlDbType.BigInt, 8, ParameterDirection.Input, true, 19, 0, "",
DataRowVersion.Proposed, LangID));

in this case the "true" which is the fith parameter in this overriden
constructor of the parameter still gets the property initialized at 0
even if I don't set it in my code and even if I execute the code below
in the second approach.

Sorry guys,

I failed to mention that the "true" parameter means Is Nullable.

Lorenzo

 

[David Browne]

Hi all,

I'm building a data mapper class where each property of the class maps
a column in the database (guess this is formally called by Fowler in
PoEAA a Data Mapper or an Active Record pattern).

Ok the problem is I have several columns in the User table which have
Foreign Key constraints and allow NULL values to be stored.

I tried several approaches for passing null values to the db but none
of them seems to work. Columns declared as bigint in SQL Server 2005
got passed a 0 instead of NULL even if I use the following C# code to
prevent it:

1st approach (this is how LLBGEN generates the code)
CreateCommand.Parameters.Add(new SqlParameter("@LangID",
SqlDbType.BigInt, 8, ParameterDirection.Input, true, 19, 0, "",
DataRowVersion.Proposed, LangID));

in this case the "true" which is the fith parameter in this overriden
constructor of the parameter still gets the property initialized at 0
even if I don't set it in my code and even if I execute the code below
in the second approach.

2nd approach
foreach (SqlParameter Parameter in CreateCommand.Parameters)
{
if (Parameter.Value == null)
{ Parameter.Value = DBNull.Value;}
}

2nd-bis approach
foreach (SqlParameter Parameter in CreateCommand.Parameters)
{
if ((Parameter.Value == null) | (Parameter.Value == 0))
{Parameter.Value = DBNull.Value;}
}

this returns the error Operator '==' cannot be applied to operands of
type 'object' and 'int'

3rd approach
Nullable Types declaring long? but gives an error and found out that
ADO.NET doesn't support Nullable types
(http://unboxedsolutions.com/sean/archive/2005/09/10/727.aspx). Not
clear though since using SqlClient ADO.NET should be bypassed.

So any clues of how to handle such problem? I'm pretty sure I'm
overlooking something since it must be a problem faced by everybody
long time ago.

BTW I'm using SqlServer 2005, ADO.NET and C#2 with the .NET framework v
2 obviously.

Nullable types are, indeed, the way to go here. Otherwise you must maintain
seperate null flags for each of your nullable columns.

ADO.NET does not support nullable types very well, but they are perfect for
this kind of ORM. You may just be on the hook for translating from int? to
SqlInteger, etc.

But there is additional good the example you cite is out of date. The
boxing behavior for nullable types has changed. Nullable types are now
boxed as their underlying value type, or null.
http://msdn2.microsoft.com/en-us/library/ms134802


public class Program
{
static void Main(string[] args)
{
int? i = null;
int? j = 5;
DumpType(i);
DumpType(j);
}

static void DumpType(object o)
{
if (o == null)
Console.WriteLine("null");
else
Console.WriteLine(o.GetType());
}
}


prints

null
System.Int32

Which means that when you pass an int? to the SqlParameter constructor it
goes in as either null or a regular int. Not as a boxed int?, for which
there is no built-in mapping.

David

 

[lbolognini]

You may just be on the hook for translating from int? to
SqlInteger, etc.

Hi David,

thanks for the answer. Nullable types do work but as you say I should
perform the cast since I get this returned from
Command.ExecuteNonQuery():

Failed to convert parameter value from a Nullable`1 to a Int64.

I fail to see where I should perform the cast. Here's my code:

private long? langid;

public long? LangID
{
get { return langid; }
set { langid = value; }
}

also chainging the above code to use native SqlServer types like
SqlInt64 doesn't work either and gives the same error. But the
overriden constructor I use only accepts the enum SqlDbType as u see
from the code below:

CreateCommand.Parameters.Add(new SqlParameter("@LangID",
SqlDbType.BigInt, 8, ParameterDirection.Input, true, 19, 0, "",
DataRowVersion.Proposed, LangID));

Any clues?

Thanks,
Lorenzo

 

[lbolognini]

more details on my previous post:
Exception Details: System.InvalidCastException: Object must implement
IConvertible.

Lorenzo

 

[David Browne]

Hi David,

thanks for the answer. Nullable types do work but as you say I should
perform the cast since I get this returned from
Command.ExecuteNonQuery():

Failed to convert parameter value from a Nullable`1 to a Int64.

I fail to see where I should perform the cast. Here's my code:

private long? langid;

public long? LangID
{
get { return langid; }
set { langid = value; }
}

also chainging the above code to use native SqlServer types like
SqlInt64 doesn't work either and gives the same error. But the
overriden constructor I use only accepts the enum SqlDbType as u see
from the code below:

CreateCommand.Parameters.Add(new SqlParameter("@LangID",
SqlDbType.BigInt, 8, ParameterDirection.Input, true, 19, 0, "",
DataRowVersion.Proposed, LangID));

I think you are using VS2005 Beta 2. In September CTP and in RTM the
parameter value will be passed into the SqlParameter constructor as an Int64
or null instead of Nullable<Int64>.

SqlParameter doesn't like null, so you need to replace null with
DbNull.Value, but you can do this to every command just before you exeute
it. Like this:

public class Program
{
static void Main(string[] args)
{

using (SqlConnection con = new SqlConnection("Data
Source=(local);Initial Catalog=test;Integrated Security=true"))
{
con.Open();
new SqlCommand("create table #t(i bigint)", con).ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("insert into #t(i) values (@i)", con);
SqlParameter pI = cmd.Parameters.Add(new SqlParameter("@i",
SqlDbType.BigInt));


Int64? i = 4;
pI.Value = i;


PrepareCommand(cmd);
Console.WriteLine(cmd.ExecuteNonQuery());

Int64? j = null;

pI.Value = j;

PrepareCommand(cmd);
Console.WriteLine(cmd.ExecuteNonQuery());

}

}
static void PrepareCommand(SqlCommand cmd)
{
//map null and "" to DbNull.Value
//you want to do this anyway since storing both "" and null is an
abomination
foreach (SqlParameter p in cmd.Parameters)
{
if (p.Value == null)
p.Value = DBNull.Value;
else if (p.Value is string && ((string)p.Value) == "")
p.Value = DBNull.Value;
}

}

}

David

David

 

[lbolognini]

Hi David,

thanks very much for all your help, we tried every possible way. BTW
had MS comment on this and as they said there's no solution: there are
only workarounds.

Another odd things (not sure in which version of the code we tried but
i guess this very same one) is that we couldn't get this to work by
testing parameters values inside a foreach.

This worked:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.SqlTypes;


public partial class bigIntNullTest : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
PersonAge x = new PersonAge();
x.Create();

}



public class PersonAge
{
private static string ConnString =
ConfigurationManager.AppSettings["DSN"];
private static SqlConnection DbConn = new
SqlConnection(ConnString);

private long? age;
public long? Age
{
get { return age; }
set { age = value; }
}

public PersonAge()
{ }


public void Create()
{
string sqlStatment = "INSERT INTO tblTest (PersonAge)
VALUES (@PersonAge)";
SqlCommand sqlCmd = new SqlCommand(sqlStatment, DbConn);


if (Age == null)
sqlCmd.Parameters.AddWithValue("@PersonAge", DBNull.Value);
else sqlCmd.Parameters.AddWithValue("@PersonAge",
Age.Value);


try
{
DbConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (DbConn.State != ConnectionState.Closed)
{
DbConn.Close();
}
throw ex;
}
finally
{
DbConn.Close();
}

}


}


}

 

[lbolognini]

Hi David,

please ignore my prev post the code doesnt work if you set Age to a NON
null value like, say, 100.

You could try with the following code both testing if the
property.HasValue and if the property.Value = null it still doesnt work
but its a start.

Take care,
Lorenzo

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class test1 : System.Web.UI.Page
{
public class TestMe
{
private long? age;

public long? Age
{
get { return age; }
set { age = value; }
}

public TestMe()
{

}
}

protected void Page_Load(object sender, EventArgs e)
{

string ConnectionString =
ConfigurationManager.AppSettings["DSN"];
SqlConnection DbConnection = new
SqlConnection(ConnectionString);

TestMe pippo = new TestMe();
pippo.Age = 100;

string CreateStatement = "INSERT INTO tblTest(PersonAge)
VALUES(@PersonAge)";

SqlCommand CreateCommand = new SqlCommand(CreateStatement,
DbConnection);


if (pippo.Age.HasValue)
//change this to pippo.Age == null to test... then you
would need to
//reverse the testing if/else logic

{
CreateCommand.Parameters.AddWithValue("@PersonAge",
pippo.Age.Value);
}
else
{

CreateCommand.Parameters.AddWithValue("@PersonAge",
DBNull.Value);
}


if (DbConnection.State == ConnectionState.Open) {
DbConnection.Close(); }

DbConnection.Open();
CreateCommand.ExecuteNonQuery();
DbConnection.Close();

Response.Write("Hello!");

}
}

 

[lbolognini]

**********************************
SOLUTION
**********************************

Hi David,

finally this story arrives to an end. Got the above code working. The
following definitely works. Yesterday I was tricked by a bug in VS 2005
which didn't show me any database updates so I thought nothing was
being committed. Anyway there's the code hope this problem won't make
anybody anymore waste the time I did.

Thanks a lot!
Lorenzo

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class test1 : System.Web.UI.Page
{
public class Person
{
private long? age;

public long? Age
{
get { return age; }
set { age = value; }
}

public Person()
{

}
}

protected void Page_Load(object sender, EventArgs e)
{
string ConnectionString =
ConfigurationManager.AppSettings["DSN"];
SqlConnection DbConnection = new
SqlConnection(ConnectionString);

Person joe = new TestMe();
joe.Age = 100; //assign null to test for null values

string CreateStatement = "INSERT INTO tblTest(PersonAge)
VALUES(@PersonAge);";
SqlCommand CreateCommand = new SqlCommand(CreateStatement,
DbConnection);

//either testing for HasValue or != null works

/*
if (joe.HasValue)
{ CreateCommand.Parameters.AddWithValue("@PersonAge",
joe.Age.Value); }
else { CreateCommand.Parameters.AddWithValue("@PersonAge",
DBNull.Value); }
*/

if (joe.Age != null)
{ CreateCommand.Parameters.AddWithValue("@PersonAge",
joe.Age.Value); }
else { CreateCommand.Parameters.AddWithValue("@PersonAge",
DBNull.Value); }

if (DbConnection.State == ConnectionState.Open) {
DbConnection.Close(); }

DbConnection.Open();
CreateCommand.ExecuteNonQuery();
DbConnection.Close();

Response.Write("Hello!");
}
}

 

[lbolognini]

(e-mail address removed) wrote:

typo here:
Person joe = new TestMe();

substitute with
Person joe = new Person();

Lorenzo