Socal Media Productions

Say you have a variable x and you need to pass it as a parameter in a SQL query and it has to be an INT data type. Just convert the variable to an INT using this syntax:

Convert.ToInt32(x.Value);

 

over and out

For handeling NULLS in a typed DataSet there are several solutions which are all combersome if the datatype is anything other than a string. 

In Visual Studio once you define the typed dataset you can adjust the properties of the column values by clicking on the column in the .xsd GUI.  This allows you to set the datatype and how to handle NullValue.  If the datatype is System.String then you can change the NullValue to empty(""), null(DBNull) or throw exception.  The exception displays an ugly error message in the browser and provides little information on how to correct the issue. (The value for column '*' in table '*' is DBNull) or (System.InvalidCastException: Specified cast is not valid.)

If your datatype is INT, Decimal, etc.. you have to use error handeling in the script to workaround this issue.  Ideally you would expect MS VS2005 to allow you to set the null value to (0), (0m), etc... but that bug is still not fixed. 

Some blogs suggest to change the .cs file to set the value using an if statement.  That will work but it will also get overwritten wehn you uspdate the file with another dataset. 

http://forums.microsoft.com/MSDN/ShowPost.aspx?
PostID=174108&SiteID=1

Fortunately when the Typed Dataset was created it generated a method to return a boolean value if the column data was Null.  So here is how you use it.

 

 define your table adapter:

xTableAdapter ds = new xtableAdapter;

Fill a datatable with the adapters resulting data:

databasename.xDataTable dt = ds.GetData();

Run throught the data and grab the values you need and change the Nulls into values you want.

foreach(databasename.xRow dr in dt)

{  decimal y;

//error check the column value first

    if (dr.Iscolumn1Null())

       { y = 0m;} else { y = dr.column1;}

}

 

That's it!  What sucks is that you have to use error checking in your script everytime you want to grab a column value in a typed dataset.  It should be handeled in the .xsd file.

 

 

When working with datasets and datatables we will run into issues where the resulting column values are null in the database.  These issues can cause your code to fail when you are using the values to make calculations or validations with the data.  The easiest way to handle this is to zero the null value using a custom method call.  The logic will also work with string values and here is how it works:

 object ZeroNull(object i, object defaultvalue)
    {
        if (i.Equals(DBNull.Value))
        {
            return defaultvalue;
        }
        else
        {
            return i;
        }
    }

add data to the dataset (see other blog for instructions)

DataSet ds = new DataSet
adapter.Fill(ds);

choose a row in the dataset to work with.

DataRow dr = ds.Rows[0];

Insert the column value into our new method to zero the null value if it is null otherwise it will return its actual value and cast it as a decimal.

(decimal)x = (decimal)ZeroNull(dr["columnName"], 0m);

 Good Luck