Aussieveedubbers - Powered by GaiaBB

C# ? not VW

amazeer - November 12th, 2009 at 10:41 PM

I really think this place should have an IT forum to put all these What Laptop? What printer? and all my programming geek questions in.

I'm experimenting with C# at the moment, hooking up to a sql server database with stored procedures.

lets just say im selecting first name middle name surname from a database. Not everyone has a middle name so it is null.

When my c# program tries to assign the null middle name to a string it falls over, cant convert DBNull to String or something like that.

I could also get around it (not sure exactly how in code) in C# by saying if result != DBNull middle name = (string)result... but thats really yuk too. Or try catch.. but not technically correct, and again, lots of unnecessary characters.

I can get around it by selecting coalesce (firstname ''), coale... blah blah. But I dont like that, even though its the best option I can see.

This is all well n good, but what happens when you're joining your customer tables, with adress tables and whatever else. Surely you dont have all these if statements. Is coalesce the only way?

vw2b - November 12th, 2009 at 10:56 PM

Why are you assigning a 'NULL" from c# program ? .

It the SQL DB Table value for NamdMiddle is varchar(20 whatever then just pass it an empty string not a null. A null is not an empty string it is a null which isn't empty or full. You can not compare a null like it is a variable you need to use IS NULL and IS NOT NULL

If you pass it an empty ie "" if no middle added it will not be an issue.

If your sp has any issues with nulls then I would re-write it. Post your DB creates for your table schema and your sp and I can help you out. Other than that get rid of != null to start with

Matt Ryan - November 12th, 2009 at 11:43 PM

Bloody Geeks,

Have a look around here:

http://needfulthingzs.org/

It's a site with a similar feel to our own AussieVeeDubbers, although a bit more international, it has everything from newbie to expert. And lots of "safe" warez too.

Register and post your question there and you will get an answer quickly, due to the international nature of the place. Plus it's a really friendly community.

Regards,

Matt.

donn - November 13th, 2009 at 05:00 AM

Eh?

eraser - November 13th, 2009 at 06:32 AM

just do an

if(row["columnname"] != System.DBNull.Value)
{
Do whatever ehre.
}

Also a Microsoft SQL database?

Sides - November 13th, 2009 at 06:52 AM

I kind of agree with eraser, but normally considered good practice to do that type of stuff in a helper or wrapper class... like maybe create a ConvertHelper that has a string ConvertToString(object value) method in it which does the != DBNull check before doing the convert inside the method. If you have to write the if statement more than twice then yeah - definitely create a helper/wrapper class.

Gotta ask though... what's the scenario that you're trying to convert the middle name to string ??? Actually, your exception sounds like you're trying to cast rather than convert.... bit surprised at you having to convert or cast from a DataSet/DataTable like that...

Joins etc. are all data operations, and the big rule to leave as much of that to SQL as you can... as in you write a StoredProc or a View that gives you what you want, and in C# you just set up your SqlDataAdapter/SqlCommand with the T-SQL to call it.

Scarab - November 13th, 2009 at 07:39 AM

.....its all GEEK to me!!

amazeer - November 13th, 2009 at 08:05 AM

Quote:

Originally posted by Sides
I kind of agree with eraser, but normally considered good practice to do that type of stuff in a helper or wrapper class... like maybe create a ConvertHelper that has a string ConvertToString(object value) method in it which does the != DBNull check before doing the convert inside the method. If you have to write the if statement more than twice then yeah - definitely create a helper/wrapper class.

Gotta ask though... what's the scenario that you're trying to convert the middle name to string ??? Actually, your exception sounds like you're trying to cast rather than convert.... bit surprised at you having to convert or cast from a DataSet/DataTable like that...

Joins etc. are all data operations, and the big rule to leave as much of that to SQL as you can... as in you write a StoredProc or a View that gives you what you want, and in C# you just set up your SqlDataAdapter/SqlCommand with the T-SQL to call it.

middle name was just an example.because I thought it would be easier to explain.

The actual task is creating an invoice. Selects from a a customer table and an address table. The address table has a flag for billing and delivery address types. A customer can have 2 addresses, a billing address and a delivery address. If the billing and delivery go to the same ponit there is no delivery address. An address has multiple lines. addr1, addr2, addr3, suburb etc. Most of the time addr2 & 3 are empty. Half of the time all of the delivery address fields are empty. The select statement is a stored procedure... customer inner joined to address for billing, left outer joined for delivery address. There isnt a problem with the selects.

When the data comes back there is a long list of assigning those data fields to the fields that appear on the invoice. Invariably, some of these fields are null. Its very ugly haveing an if statement around each of them, hence why I am using the coalesce(addr2, '') etc for each field in the stored proc. Was just wondering if thats the nicest way really. I know and empty string is not a null string, but I cant assign a null database object to a string. Thats not of real concern to me though as in the application instead of checking for null I check for empty string. I'm able to print direct to a pdf file using iTextSharp library, its all working well, just that my OCD is worried about the aesthetics of the code more so than its functionality :P

And I could join a geek forum and have done in the past, but often they explain things at an uber geek level which doesnt help at all. And I love you guys!

Sides - November 13th, 2009 at 08:38 AM

Quote:

Originally posted by Scarab
.....its all GEEK to me!!

Yeah, true - but have to do SOMETHING to pay for the Volksy obsession...

Anyway, the explanation helps Chris cos yeah - I just couldn't see a case where you'd need to cast/convert like that.

I don't know iTextSharp at all, but most packages/libraries for doing reporting or print layout you can bind the fields/controls directly to the data itself, and just let the binding Format and Parse methods (all down in the System.Data etc. dll's) deal with all the converts that are necessary. Kinda curious now about iTextSharp, so might have a bit of a look-see into that also.

Short answer tho - I wouldn't use coalesce but would instead have the if statements, included in a helper or wrapper class. By using coalesce you're sort of blurring the line between data and formatting, which although works fine it can trip you up later because you're effectively losing information. Best example I can think of is for the times when the difference between String.Empty, null and DBNull are important... something like where DBNull and null mean "use the default value", and String.Empty means that a human is indicating "use a value of 0 or empty"...

Oh and hey - it ain't OCD to care about aesthetics of code !!! Nothing worse than having to come back years down the track and deal with someone's ugly, twisted code that's full of backwards or just plain martian logic !!! People who commit such crimes deserve a good fonging....