Thomas Coleman said:
I have been playing around with 2.0 and I'm trying clarify a few things
about
generics and data access. I was hoping to be able to do something like
this with
nullable types:
int? foo = null;
DataRow dr = ...;
dr["Column1"] = foo;
However, this does not work if the DataType value is set to int. In that
scenario, the system throws an invalid cast exception claiming that <>
cannot be
converted to Int32. I have already read about all the reasons about why
that doesn't work.
What I need is simple: In *one line of code*, I need the ability to set a
variable that I can use to type a business object property such that it
can represent a primitive and a null in a value type and not be tied to
SQL Server.
As I think about it, I could write a series of functions like ToInt32?,
ToInt16? that would cast a DBNull.Value to a null reference or the value
in question. Of course, I suppose I'll also need a series of functions
that do the reverse like ToDbInt32, ToDbInt16 etc. So I might have
something like:
int? foo = StaticClass.ToInt32?(dr["ColName"]);
or
commandObj.Parameters[0].Value = StaticClass.ToDbInt32?(foo);
If that's in the framework, please tell me where. If that is not in the
framework, why? Why do I have to write a series of functions for a
fundamental operation in all database driven systems?
It's not yet in the framework. ADO.NET just doesn't leverage nullable value
types in this release.
For the DataReader, we would need a new interface, say INullableDataReader
to return the nullable types.
Here's an implementation of INullableDataReader a utility class for wrapping
an IDataReader in a INullableDataReader, which should be helpful. Also it
illustrates some of the issues in integrating nullable types and ADO.NET.
Nullable types really would require a seperate ADO.NET library.
Using nullable types in a Data Access Layer is a great idea, but we will
have to build up the infrastructure to do it seperately from ADO.NET, for
now.
David
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
public class Program
{
struct T
{
public class ColumnMap
{
public static readonly int id = 0;
public static readonly int s = 1;
public static readonly int i = 2;
public static readonly int d = 3;
}
public int id;
public string s;
public int? i;
public decimal? d;
public override string ToString()
{
return string.Format("id={0} s={1} i={2} d={3}", id, s, i, d);
}
}
static void Main(string[] args)
{
string constr = "Data Source=(local);Integrated Security=SSPI";
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
string batch = @"
create table #t(id int identity primary key, s varchar(12), i int, d
decimal(13,2))
insert into #t(s,i,d) values (null,2,null)
insert into #t(s,i,d) values ('a',null,6.5)
";
new SqlCommand(batch,con).ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("select * from #t", con);
using (INullableDataReader r = new
NullableDataReaderWrapper(cmd.ExecuteReader()))
while (r.Read())
{
T t = new T();
t.id = (int)r.GetInt32(T.ColumnMap.id);
t.s = r.GetString(T.ColumnMap.s);
t.i = r.GetInt32(T.ColumnMap.i);
t.d = r.GetDecimal(T.ColumnMap.d);
Console.WriteLine(t);
}
}
}
interface INullableDataReader : IDisposable
{
void Close();
int Depth
{
get;
}
int FieldCount
{
get;
}
bool? GetBoolean(int i);
byte? GetByte(int i);
long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset,
int length);
char? GetChar(int i);
long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset,
int length);
System.Data.IDataReader GetData(int i);
string GetDataTypeName(int i);
DateTime? GetDateTime(int i);
decimal? GetDecimal(int i);
double? GetDouble(int i);
Type GetFieldType(int i);
float? GetFloat(int i);
Guid? GetGuid(int i);
short? GetInt16(int i);
int? GetInt32(int i);
long? GetInt64(int i);
string GetName(int i);
int GetOrdinal(string name);
System.Data.DataTable GetSchemaTable();
string GetString(int i);
object GetValue(int i);
int GetValues(object[] values);
bool IsClosed
{
get;
}
bool IsDBNull(int i);
bool NextResult();
bool Read();
int RecordsAffected
{
get;
}
object this[int i]
{
get;
}
object this[string name]
{
get;
}
}
public class NullableDataReaderWrapper : INullableDataReader
{
private IDataReader r;
public NullableDataReaderWrapper(IDataReader r)
{
this.r = r;
}
public IDataReader WrappedReader
{
get
{
return r;
}
}
public void Close()
{
r.Close();
}
public int Depth
{
get
{
return r.Depth;
}
}
public DataTable GetSchemaTable()
{
return r.GetSchemaTable();
}
public bool IsClosed
{
get
{
return r.IsClosed;
}
}
public bool NextResult()
{
return r.NextResult();
}
public bool Read()
{
return r.Read();
}
public int RecordsAffected
{
get
{
return r.RecordsAffected;
}
}
public void Dispose()
{
r.Dispose();
}
public int FieldCount
{
get
{
return r.FieldCount;
}
}
public bool? GetBoolean(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetBoolean(i);
}
public byte? GetByte(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetByte(i);
}
public long GetBytes(int i, long fieldOffset, byte[] buffer, int
bufferoffset, int length)
{
return r.GetBytes(i, fieldOffset, buffer, bufferoffset, length);
}
public char? GetChar(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetChar(i);
}
public long GetChars(int i, long fieldoffset, char[] buffer, int
bufferoffset, int length)
{
return r.GetChars(i, fieldoffset, buffer, bufferoffset, length);
}
public IDataReader GetData(int i)
{
return r.GetData(i);
}
public string GetDataTypeName(int i)
{
return r.GetDataTypeName(i);
}
public DateTime? GetDateTime(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetDateTime(i);
}
public decimal? GetDecimal(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetDecimal(i);
}
public double? GetDouble(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetDouble(i);
}
public Type GetFieldType(int i)
{
Type t = r.GetFieldType(i);
switch (Type.GetTypeCode(t))
{
case TypeCode.Boolean:
return typeof(bool?);
case TypeCode.Byte:
return typeof(byte?);
case TypeCode.Char:
return typeof(char?);
case TypeCode.DateTime:
return typeof(DateTime?);
case TypeCode.Decimal:
return typeof(Decimal?);
case TypeCode.Double:
return typeof(Decimal?);
case TypeCode.Int16:
return typeof(Int16?);
case TypeCode.Int32:
return typeof(Int32?);
case TypeCode.Int64:
return typeof(Int64?);
case TypeCode.SByte:
return typeof(SByte?);
case TypeCode.Single:
return typeof(Single?);
case TypeCode.UInt16 :
return typeof(UInt16?);
case TypeCode.UInt32 :
return typeof(UInt32);
case TypeCode.UInt64:
return typeof(UInt64);
default:
return t;
}
}
public float? GetFloat(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetFloat(i);
}
public Guid? GetGuid(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetGuid(i);
}
public short? GetInt16(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetInt16(i);
}
public int? GetInt32(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetInt32(i);
}
public long? GetInt64(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetInt64(i);
}
public string GetName(int i)
{
return r.GetName(i);
}
public int GetOrdinal(string name)
{
return r.GetOrdinal(name);
}
public string GetString(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetString(i);
}
public object GetValue(int i)
{
if (r.IsDBNull(i))
return null;
return r.GetValue(i);
}
public int GetValues(object[] values)
{
int rv = r.GetValues(values);
for (int i=0;i<values.Length;i++)
{
object o = values
;
if (o == DBNull.Value)
{
values = null;
}
}
return rv;
}
public bool IsDBNull(int i)
{
return r.IsDBNull(i);
}
public object this[string name]
{
get
{
object o = r[name];
if (o == DBNull.Value)
{
o = null;
}
return o;
}
}
public object this[int i]
{
get
{
object o = r;
if (o == DBNull.Value)
{
o = null;
}
return o;
}
}
}
}
David
