Is there any way to use SqlTypes with a SqlDataAdapter?

Question

I'm trying to write some simple C# code that will select some data from a SQL Server instance, modify the data, and update the changes back to the database. But I'm having a hard time figuring out exactly how to do this. Here is a simplified version of my code:

using (SqlConnection conn = new SqlConnection("Server=(local);Integrated Security=true;Database=master"))
{
    SqlDataAdapter da = new SqlDataAdapter("SELECT ID, Field FROM test", conn);
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    DataSet ds = new DataSet();
    da.Fill(ds, "test");

    foreach (DataRow dr in ds.Tables["test"].Rows)
    {
        dr["Field"] = someFunction((SqlString)dr["Field"]);
    }

    da.Update(ds, "test");
}

But this code is giving me the following error:

System.InvalidCastException: specified cast is not valid.

So I change the code to use string rather than SqlString:

dr["Field"] = someFunction((string)dr["Field"]);

But then I get this error if there are any nulls in my data:

System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.

I thought the whole point of the SqlTypes namespace was to provide types that can handle database nulls. If that is the case, why can't I use them with a SqlDataAdapter? Hopefully I'm missing something obvious.

Answer 1

I could be very wrong here (just a disclaimer), but I think you need to make a new SqlString from your db value, like so: I think that will work for your null case, I haven't tested it though.

    SqlString str = (dr["Field"] == null) ? SqlString.Null : new SqlString((string)dr["Field"]);
someFunction(str);

Answer 2

Try

dr["Field"] = someFunction(dr["Field"] is DbNull ? null : (string)dr["Field"]);