Cool .NET Tips and Tricks #10
By Dr. Dexter Dotnetsky
Printer - Friendly Version
Dr. Dotnetsky

Howdy, Dr. Dotnetsky here again! You know, I do get email from readers, although I'm nowhere near as popular as some of the real gurus around here. But some of them are good for a few chuckles, especially the people who take offense to my occasional irreverent political correctisms. Here's one (name withheld):

To: youbetcha@mindless.com
Cc:
Subject: stick to coding
Date: Tue, 12 Aug 2003 10:58:44 -0700 (PDT)

political views don't belong in an ASP.NET article, particularly
views that suck. spend-now-tax-more-later conservatives will be the
death of us all

Jeesh! Hypocrisy comes cloaked in many costumes, doesn't it -- especially the kind that comes after the word "particularly!" I guess they must have been referring to my proposed solution to that "California problem"! And who ever said my rants were "ASP.NET Articles"? This one gets 3 Chuckles:



Stored Procedure Shortcuts in .NET

I've seen a number of instances where developers use "shortcuts" by calling stored procedures using "EXEC" along with the name of the sproc followed by parameter values as the CommandText. You can get away with this because .NET data providers can call stored procedures on the database server by executing the procedure as any other SQL query.

However, executing the stored procedure as a SQL query results in the database server parsing the statement, validating the argument types, and converting the arguments into the correct data types, which is an inefficient way to accomplish the task.

When calling stored procedures, always add parameter values to the parameter collection instead of using literal arguments in a string. The SqlClient classes are optimized to talk to SQL Server in a strongly - typed highly - efficient TDS datastream. But, you have to give them the tools they need to do so, and that means a strongly typed Parameters collection, even if it's only **one** parameter.

To correctly use stored procedures, set the CommandText property of the command object to the name of the stored procedure and then set the CommandType property of the command to StoredProcedure. Do not physically code the literal arguments into the CommandText.

For example, do not use literal arguments such as:

{call foo (3567, 'John', 987.32)}
or "exec sp_doSomething 'John', 123.41"

You can add your parameters to the Command instance with any of the overloads of the SqlParameter class. An example might be:

cmd.Parameters.Add(new SqlParameter("@name", intValue))

Parameters can also be derived from a stored procedure using the CommandBuilder class. Both the SqlCommandBuilder and OleDbCommandBuilder classes provide a static method, DeriveParameters, which will automatically populate the Parameters collection of a Command object with parameter information from a stored procedure. This is precisely how the MS Application Blocks "SqlHelper" class allows you the simplicity of passing paremeter values as a simple object array.

Deriving parameter information does require an added trip to the data source for the information. If parameter information is known at design-time, you can improve the performance of your application by setting the parameters explicitly, as shown above.

Dr. Dotnetsky wouldn't play you wrong, .NETers.

Add-In / Utility Contest

By the way, while I'm on this subject, let's get some action going. Let's have a little contest for the reader who can identify the best Visual Studio.Net add-in or utility for coding or calling stored procedures. This doesn't mean you have to write one yourself - just provide a link to something you've found that you like. The only proviso is that the code is freely available and is freeware, not "for purchase" or "trialware". Post your entry with a short description and a link to the product at our forums under the "Article Discussions" topic. We'll test and review all the entries and the selected winner will receive a free copy of our Eggheadcafe.com e-book (That's the 21MB CHM containing all our eggheadcafe.com resources, articles and code, with full-text search, plus our $30 value US Zipcode database.) I hope readers come up with some interesting stuff, cause I have a real aversion to 1) writing generic stored procedures every time I create a table and 2) writing all the code to call them. So, if you know of something, take a minute to pop us an entry with a link at the Article Discussions section!

Default ASPNET Account prevents ASP.NET from working on domain controller

We get lots of questions about this, where people can't get ASP.NET to do certain things (or on a domain controller, it doesn't work at all). The main reason for all these related issues is that the default ASPNET account, wich is what the ASPNET worker process runs under, and which is defined in machine.config by default as "machine", is a very weak account without many privileges. It's also a local, not a domain account, hence the difficulty with ASP.NET going "BOINK!" on domain controllers. Its easy to fix this by changing the userName attribute value in the <processModel section from "machine" to "system", but Microsoft doesn't recommend this, for security reasons. Here is the official stance on the issue:

Create a user account on the computer named ASPUSER, and then add this account to the Users group.

NOTE: You can also use the ASPNET account that the .NET Framework created if you change the password on this account. You must know the password on this account because you add the password to the <processModel> section later in these steps.

Grant the ASPUSER or the ASPNET account the Log on as a batch job user right. Make sure that this change appears in the Local Security Policy settings.

NOTE: To grant the Log on as a batch job user right on this account, you may have to grant this user right in each of the following security policies (From the Control Panel/Administrative Tools):


Domain Controller Security Policy
Domain Security Policy
Local Security Policy

NOTE: You may have to reboot the server for these changes to take effect.
Make sure that the ASPUSER or the ASPNET account has permission to access all of the necessary directories and files to start the Aspnet_wp.exe process and to serve the ASP.NET pages.For additional information about what permissions you must grant to this account, click the article number below to view the article in the Microsoft Knowledge Base:
317012 INFO: Process and Request Identity in ASP.NET

Open the Machine.config file. The path to the file is: %Systemroot%\Microsoft.NET\Framework\v1.0.3705\CONFIG.
In the <processModel> section of the Machine.config file, change the userName and the password attributes to the name and the password of the account that you created in step 1. For example: userName="DomainName\ASPUSER" password="ASPUSERpassword"
Save the changes to the Machine.config file.

Complaint Department:

And now a formal complaint to Microsoft. Don't you just love it when you search the KB and find that there's a supported fix for your problem, but you have to jump through hoops to get it? I'm talking about the following scenario:

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next .NET Framework 1.1 that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix.

-- Duh! If you guys acknowledge there'a a problem, and you've created a fix for it, why the HELL don't you just include a link to the file so developers can download it! I mean, after all, you guys are giving away the .NET Framework, so if you have a fix for people to use, what the heck are we accomplishing here?

Well, that's it for me. See ya next time!

Dr. Dexter Dotnetsky is the alter-ego of the Eggheadcafe.com forums, where he often pitches in to help answer particularly difficult questions and make snide comments. Dr. Dotnetsky holds no certifications, and does not have a resume. Always the consummate gentleman, Dr. Dotnetsky can be reached at youbetcha@mindless.com.  Dr. Dotnetsky's motto: "If we were all meant to get along, there would be no people who wait for all the groceries to be rung up before starting to look for their damn checkbook."