• 0

C# sqlDataReader Help


Question

I am having some major issues with this piece of code... and I am sure there has to be a better way of going about checking for a null value and doing an else command, but thats not even the issue every time I run this and there happens to be a string in the value of lets say Companyname in the database i get a incorrect string format error which makes some sence seeing that I am parsing a string to do a boolean on an int and then it has not clue what to do with the string. Now this of course works if the value is an Int... So I am lost on how to fix this issue any help would be great.

public Company[] GetCompany()
        {
            con = "Data Source=MENTALIS;Initial Catalog=LiveData;Integrated Security=True";
            SqlDataReader d = SqlHelper.ExecuteReader(con,CommandType.StoredProcedure,"Company_Sel");
            ArrayList Alpha = new ArrayList();
            while(d.Read())
            {
                Company al = new Company();
                al.CompanyID = d.IsDBNull(Int32.Parse(d["CompanyID"].ToString())) ? "" : d["CompanyID"].ToString();
                al.Companyname = d.IsDBNull(Int32.Parse(["Companyname"].ToString())) ? " " : d["Companyname"].ToString();
                al.Address = d.IsDBNull(Int32.Parse(d["Address"].ToString())) ? "" : d["Address"].ToString();
                //al.Address2 = d.IsDBNull(Int32.Parse(d["Address2"].ToString())) ? "" : d["Address2"].ToString();
                Alpha.Add(al);

            }
            return (Company[])Alpha.ToArray(typeof(Company));
        }

Link to comment
Share on other sites

14 answers to this question

Recommended Posts

  • 0

This is going to be a little off topic, however it will be something of value to think about.

I ASSUME you are using an Identity for the company int/ID. This is quite standard, however you may want to use a GUID (run; NEWID() from Query Analizer) as if you are running muliple databases and if they ever need to sync up there will be multiple companies with the same id. NewID solves this problem.

let us know EXACTLY which line(s) you are having the issue with. It SEEMS as if this line has an issue as the company name is not an Int32 (sorry, bv/vb.net dev... I may be missing something)

>>>.al.Companyname = d.IsDBNull(Int32.Parse(["Companyname"].ToString())) ? " " : d["Companyname"].ToString();

Link to comment
Share on other sites

  • 0
I am having some major issues with this piece of code... and I am sure there has to be a better way of going about checking for a null value and doing an else command, but thats not even the issue every time I run this and there happens to be a string in the value of lets say Companyname in the database i get a incorrect string format error which makes some sence seeing that I am parsing a string to do a boolean on an int and then it has not clue what to do with the string. Now this of course works if the value is an Int... So I am lost on how to fix this issue any help would be great.

public Company[] GetCompany()
        {
            con = "Data Source=MENTALIS;Initial Catalog=LiveData;Integrated Security=True";
            SqlDataReader d = SqlHelper.ExecuteReader(con,CommandType.StoredProcedure,"Company_Sel");
            ArrayList Alpha = new ArrayList();
            while(d.Read())
            {
                Company al = new Company();
                al.CompanyID = d.IsDBNull(Int32.Parse(d["CompanyID"].ToString())) ? "" : d["CompanyID"].ToString();
                al.Companyname = d.IsDBNull(Int32.Parse(["Companyname"].ToString())) ? " " : d["Companyname"].ToString();
                al.Address = d.IsDBNull(Int32.Parse(d["Address"].ToString())) ? "" : d["Address"].ToString();
                //al.Address2 = d.IsDBNull(Int32.Parse(d["Address2"].ToString())) ? "" : d["Address2"].ToString();
                Alpha.Add(al);

            }
            return (Company[])Alpha.ToArray(typeof(Company));
        }

585177583[/snapback]

You are parsing incorrectly. You don't need to parse to an Int32 in order to determine DBNull.

al.CompanyID = d.IsDBNull( d["CompanyID"] ) ? "" : d["CompanyID"].ToString();

Link to comment
Share on other sites

  • 0
You are parsing incorrectly. You don't need to parse to an Int32 in order to determine DBNull.

al.CompanyID = d.IsDBNull( d["CompanyID"] ) ? "" : d["CompanyID"].ToString();

585177744[/snapback]

actually that does not work... it throws an errorsaying cannot convert an object to an int. now if the object is not a string and I have the d.IsDBNull(int.Parse(d["ComanyID"])).... That does work because its an int Now I have issue with d.IsDBNull(int.parse(d["Companyname"]))... because Companyname is a string and it gives me an string input formatting error. So anything where its a string it gives me that error.... Anything with an Int goes fine.

Edited by wacko412
Link to comment
Share on other sites

  • 0

Ah... well, having double checked the method signature, the int it expects is the column ordinal, not the value.

I'm used to using the IsDBNull method of Convert, which expects an object argument.

Link to comment
Share on other sites

  • 0
Ah... well, having double checked the method signature, the int it expects is the column ordinal, not the value.

I'm used to using the IsDBNull method of Convert, which expects an object argument.

585177925[/snapback]

I mean I could do this in VB.net which is not hard just a simple al.Companyname = IIF(IsDBNull(d("Companyname")," ", d("Companyname")) and that compiles and works sadly that damn IIF command lacks in C# and the IsDBNull does not convert :( So i have no clue what todo

Link to comment
Share on other sites

  • 0
I mean I could do this in VB.net which is not hard just a simple al.Companyname = IIF(IsDBNull(d("Companyname")," ", d("Companyname")) and that compiles and works sadly that damn IIF command lacks in C# and the IsDBNull does not convert :( So i have no clue what todo

585177950[/snapback]

Use the Convert object's IsDBNull method instead of the datareader. Then you can use my method I posted earlier.

al.CompanyID = Convert.IsDBNull( d["CompanyID"] ) ? 0 : int.Parse(d["CompanyID"].ToString());
al.CompanyName = Convert.IsDBNull( d["CompanyName"] ) ? "" : d["CompanyName"].ToString();

Link to comment
Share on other sites

  • 0
Weenur youget the golden cookie... your awsome thanks for your help... YAYAYAYAYAYAYAYAYAY thank YOU !!!!!!!!!!!!!!!!!!!!!!!!!!

585178243[/snapback]

No problem, brother. :D

I just looked at what I typed. It isn't necessary to parse the int, or any type for that matter, from the column. You can just cast it. It saves the overhead of calling ToString() and Parse(), though I can't say it's anymore efficient due to unboxing. I'd have to look at the IL to figure that out.

Link to comment
Share on other sites

  • 0
it is a bit icky but it works for what I need done. I wish there was a cleaner way of going about it but oh well. thanks again

585178496[/snapback]

You may want to encapsulate that DBNull test into a class.

For instance, we've created a class called SafeValue that exposes methods for each type we commonly use, takes an object as an argument, and returns a strong type.

Your code would look similar to a.CompanyID = SafeValue.Int32(d["CompanyID"]);.

Hell, here you go:

	public class SafeValue
	{
  public SafeValue()
  {
  }

  public static bool Bool(object val)
  {
  	if( val == null || val == DBNull.Value )
  	{
    return false;
  	}
  	else
  	{
    return (bool)val;
  	}
  }

  public static string String(object val)
  {
  	if( val == null || val == DBNull.Value )
  	{
    return "";
  	}
  	else
  	{
    return val.ToString().Trim();
  	}
  }

  public static int Int32(object val)
  {
  	if( val == null || val == DBNull.Value || val.ToString() == "" )
  	{
    return -1;
  	}
  	else
  	{
    if( val is System.String )
    {
    	return int.Parse(val.ToString());
    }

    return (int)val;
  	}
  }

  public static long Long(object val)
  {
  	if( val == null || val == DBNull.Value || val.ToString() == "" )
  	{
    return -1;
  	}
  	else
  	{
    if( val is System.String )
    {
    	return long.Parse(val.ToString());
    }

    return (long)val;
  	}
  }

  public static Enum GetEnum(object enumName, int val)
  {
  	return (System.Enum)System.Enum.ToObject(enumName.GetType(), val);
  }

  public static Enum GetEnum(Type enumType, string val)
  {
  	return (Enum)Enum.Parse(enumType, val, true);
  }

  public static string Ellipses(string input, int length)
  {
  	if( input.Length <= length )
  	{
    return input;
  	}

  	input = input.Substring(0, length).TrimEnd();
  	if( !input.EndsWith("?") && !input.EndsWith("!") )
  	{
    return input + "...";
  	}

  	return input;
  }

  #region Html handling

  public static string MakeSafeHtml(string input)
  {
  	input = System.Web.HttpContext.Current.Server.HtmlEncode(input);
  	input = input.Replace(Environment.NewLine, "<br>");

  	return input;
  }

  public static string ConvertHtmlBreaks(string input)
  {
  	return input.Replace("<br>", Environment.NewLine);
  }

  public static string ConvertLineBreaks(string input)
  {
  	return input.Replace(Environment.NewLine, "<br>");
  }

  #endregion

  #region date handling

  public static DateTime SqlDateMaxValue
  {
  	get
  	{
    return new DateTime(9999, 1, 1);
  	}
  }

  public static DateTime SqlDateMinValue
  {
  	get
  	{
    return new DateTime(1753, 1, 1);
  	}
  }

  public static DateTime Date(object val)
  {
  	if( val == null || val is DBNull )
  	{
    return SqlDateMinValue; // use a safe SQL date
  	}
  	else
  	{
    return (DateTime)val;
  	}
  }

  public static string GetGregorianMonthName(int monthIndex)
  {
  	string month = "";
  	switch( monthIndex )
  	{
    case 1:
    	month = "January";
    	break;
    case 2:
    	month = "February";
    	break;
    case 3:
    	month = "March";
    	break;
    case 4:
    	month = "April";
    	break;
    case 5:
    	month = "May";
    	break;
    case 6:
    	month = "June";
    	break;
    case 7:
    	month = "July";
    	break;
    case 8:
    	month = "August";
    	break;
    case 9:
    	month = "September";
    	break;
    case 10:
    	month = "October";
    	break;
    case 11:
    	month = "November";
    	break;
    case 12:
    	month = "December";
    	break;
  	}

  	return month;
  }

  #endregion

  #region path handling

  public static string PathToUrl(string path)
  {
  	return path.Replace(System.IO.Path.DirectorySeparatorChar.ToString(), "/");
  }

  #endregion
	}

Link to comment
Share on other sites

  • 0

I thought about using the if statements but just was not committed to using them at this point.... but your stuff kicks ass :) thanks again

Edited by wacko412
Link to comment
Share on other sites

  • 0

Hi guys,

I just wanted to point some stuff out for you or other people that are going to read this thread.

You guys make a lot of boxing and unboxing which is never great for performance.

1- Consider using String.Empty instead of ""

2- You would rather make a mapping class(read struct) that would convert the field-name to it's ordinal number by using the reader.GetOrdinal("FieldName") method, and go back to using reader.IsDBNull(index)? String.Empty : reader.GetString(index);

This will be more efficient, provide type safety and still be clean code to read.

3- Also consider modifying your proc to return the proper type instead of having to parse a string that contains an int, that is if the underlying table contains integers in the first place. Will allow you to use reader.GetInt32(index) and avoid boxing/unboxing.

David

Link to comment
Share on other sites

  • 0

An easier way of handling dbnulls, but not the easiest, im all for reflection on this sort of thing... if you cache the data on orm query, then the initial run will be slow (if errors exist) but future runs would be extremely quick due to a structual cache of each objects exclusions, but thats a wee bit more code. Anyway...

// Framework 2 & greater

class MyObject

{

public int? CompanyId {get;set;}

public string CompanyName{get;set;}

}

while (reader.Read())

{

myObject.CompanyId = (reader["CompanyId"] == DBNull.Value) ? (int?) null: (int?) reader["CompanyId"];

myObject.CompanyName = (reader["CompanyName"] == DBNull.Value) ? (string) null: (string) reader["CompanyId"];

}

//Framework 1

public static int InvalidNumericValue

{

get

{

return int.Parse(System.Configuration.ConfigurationManager.AppSettings["InvalidNumericValue"]);

}

}

class MyObject

{

private int companyId;

private string companyName;

public int CompanyId

{

get

{

return this.companyId;

}

set

{

this.companyId = value;

}

}

` public int CompanyName

{

get

{

return this.companyName;

}

set

{

this.companyName = value;

}

}

}

while (reader.Read())

{

myObject.CompanyId = GetValidValue(reader["CompanyId"], typeof(int));

myObject.CompanyName = (reader["CompanyName"] == DBNull.Value) ? (string) null: (string) reader["CompanyId"];

}

private object GetValidValue(object value, Type type)

{

if (typeof(int) == type)

{

if (value == DBNull.Value)

{

return InvalidNumericValue;

}

else

{

return (int) value;

}

}

else if (typeof(string) == type)

{

if (value == DBNull.Value)

{

return string.Empry;

}

else

{

return (string) value;

}

}

}

Obviously this does get abit tedious to do initially, but it deffinitely shortens the work load in the long run.

ONe of the big +'s for .Net2 was in my view nullable values, as I do alot of database work along with c#.

Edited by Jonathans
Link to comment
Share on other sites

  • 0

Little cleaner

		 while(d.Read())
		   {
			   Company al = new Company();
			   al.CompanyID = d["CompanyID"] == DBNull.Value ? "" : d["CompanyID"].ToString();
			   al.Companyname = d["Companyname"] == DBNull.Value ? " " : d["Companyname"].ToString();
			   al.Address = d["Address"] == DBNull.Value ? "" : d["Address"].ToString();
			   //al.Address2 = d["Address2"] == DBNull.Value ? "" : d["Address2"].ToString();
			   Alpha.Add(al);
		   }

Also, I would recommend using a generic List rather than an ArrayList

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.