Kathleen Dollard said:
Bill,
There is a different reason to avoid SELECT * in SQL Server. To be
honest I haven't followed up on this, but as I heard it from Kinberly
Tripp, I assumed it was correct SQL Server lore... The Server can't
optimize statements based on wildcards, but need an explicit file
list to work their magic.
Understood; I never use Select * myself for that exact reason but that's
what the question you responded to used.
I use code gen for this kind of stuff, so the maintenance is not
excessive (both the stored proc asn the enum from the same list based
on the table columns).
That makes sense and would be the way to go. It's just that I've seen a
couple of people suggest using an Enum instead of the GetOrdinal method and
was thinking that if you are maintaining an app that uses SQL strings
without that sort of code generation available then you need to synchronize
the enum with the select. I toyed with the idea and it seemed like I could
build the field list pretty easily and that way I'd never have to worry
about keeping the two matching.
Building the stored proc from the enum is
conceivable, but difficult. If you're using dynmaic SQL (do you want
that speech now?)
<g> no speech needed but I have a number of cases where the same app has to
run against SQL, Oracle, Access and possibly other backend databases.
Building SQL strings often makes that easier than dealing with different SP
capabilities.
then iterating through the enum is not a cheap operation.
Yes, but it'd only have to be done once at the start of the application and
if it saves one instance of missing updating things properly or relieves a
maintenance programmer from having to know about the requirement the
tradeoff might be worth it.
I guess if you're leaning that way you might also consider
creating a series of variables, one for the position of each column.
You could then use GetOrdinal to determine the position once and
iterate through your records.
That's what the enum gives me isn't it?
I hate strings becuase the compiler can't help you. It's "magic" as
Ibrahim pointed out, and its resolved at runtime. If you're doing it
by hand, you'd still need a fairly dynamic database to be making many
changes.
True, but all it takes is one missed change to potentially cause a lot of
trouble.
Every way you can leverage the compiler to insure you get
compiler errors (cheap to fix) rather than logic errors (expensive to
fix) is a good thing.
No argument there. All of this is just a 'gedanken experiment' on my part.
I'm not sure I'd ever use the technique but am curious what the drawbacks
might be so this has been very useful.
If you like strings, you can create constants to represent them,
which at least eases some of the maintenance burderns. Unfortunately
it turns into a lot of string constants in even medium sized
databases.
Again, I think the Enum dynamically translated to a list of fields would
work...
private enum dbfields {
field1,
field2,
field3,
field4
}
private string GetFieldList() {
dbfields dbf=dbfields.field1;
string s="";
foreach (string f in Enum.GetNames(dbf.GetType()))
s=s + "[" + f + "],";
return s.Substring(0,s.Length-1);
}
string s="Select " + GetFieldList() + " From mytable";
Kathleen
If you're a beginner (you're sig) -
C# newbie, I have used other languages quite a bit over the years. Always
looking to learn new/better techniques though.
if you're not working in ASP.NET,
Mostly not, at least not yet. Gotta get more familiar with C# and the
framework before adding the complexities of ASP.Net on top of that.
and half the time when you are, the extra code to manage the
DataReader (instead of the DataSet) is hard to justify. Don't
overlook the
That got cut off but I generally do use the DataSet or a DataTable rather
than a DataReader. I still need to run a query to get the data somehow
though! <g>
