Proper ASP.NET Database Calls

by fwhagen Mon, 20 August 2007

Last week, I posted an entry about a simple call to an MS-SQL database with a warning that it should only ever be used in a quick and dirty scenario.  By that I mean when it doesn't make sense to integrate or create a Data Access Layer or class to do the heavy lifting for you.

The advantages of creating classes to do the data calls behind the scenes are many, not the least of which is easier development in the long run.  Take a look at the following code:

    Query qEmplQuery = new Query(); 
    qEmplQuery.ConnectionSettings = ConfigurationManager.ConnectionStrings["EmployeeDB"]; 
    qEmplQuery.StoredProcedure = "DW.PKG_HRPORTAL.PRC_EmployeeInfo"; 
    qEmplQuery.AddParameter("P_EmployeeNumber", szEmplNum); 
    qEmplQuery.MinutesCached = 12 * 60; 
    DataSet dsEmplQuery = qEmplQuery.RetrieveDataSet("P_EmployeeInfo");

Much easier than even the "simple" call I posted earlier, isn't it.  The beauty of this is that it is also an Oracle call (database agnostic:  the same call for Oracle or MS-SQL makes for easier and standardized development) and is cached for 12 hours.  We also have a coding standard that only parameterized Stored Procedures are used for any kind of data access. 

But the most important advantage is that the Query class contains all of the complex coding needed to make this as efficient and "correct" as you know how and never have to think about it again.  This is basic abstraction and Object Oriented Programming fundamentals.  I have used Enterprise Library factories for my calls in Query because of the desire for its good caching and efficient data and network layer calls, but you can use whatever you want. 

In my next series of posts, I will present details of the Query class in order to help build your own.

Keywords: , ,

Filed Under: .NET | DataBase | Programming

Simple ASP.NET Database Query

by fwhagen Wed, 08 August 2007

Below is the most simple database query for C# to populate a GridView object:

int nItemID = 1; try { using (SqlConnection oSQLConn =
new SqlConnection(ConfigurationManager.ConnectionStrings["UserInfo"].ConnectionString)) { oSQLConn.Open(); using (SqlCommand oSQLCmd = new SqlCommand()) { oSQLCmd.Connection = oSQLConn; oSQLCmd.CommandText = "SELECT DisplayName, EMail FROM Users WHERE UserID = @PARAM_ItemID"; oSQLCmd.Parameters.AddWithValue("@PARAM_ItemID", nItemID); SqlDataReader oSQLReader = oSQLCmd.ExecuteReader(); GridView1.DataSource = oSQLReader; GridView1.DataBind(); } } } catch (Exception ex) { Label_ErrorMsg.Text = ex.Message; }

There are actually many reasons to never use this, including performance, caching, safe SQL, code reuse, standards, proper exception handling, etc, etc.  But sometimes you need a quick and dirty, non-production, piece of code for testing and/or development.  This is it.

Keywords: , ,

Filed Under: .NET | Programming | SQL

Oracle.NET connector issues

by fwhagen Wed, 18 July 2007
Interesting problem:  I always use library functions in .NET apps to maximize code reuse, especially for Data Access calls.  So when I ran across the following error from Oracle when connecting to a server running 10.2, I was stymied.  Especially since it worked fine with a 10.1 server:
ORA-12571: TNS:packet writer failure

Looking this one up brings a whole lot of results, none of them helpful in this situation.  To make it worse, VS2005 ran the application just fine. 

The solution?  The server had the 8.1 connector installed, which for some reason connects to 10.1 just fine.  10.2 was different enough to through the error above.  We got IS to push the 10.2 client on the server, and everything is working.

Keywords: ,

Filed Under: .NET | Oracle

VSS Source Code Headers: Automated Comments

by fwhagen Fri, 20 April 2007
One best practice I insist on is codefile headers, especially since VSS makes this stupid easy.  Below is the format I currently use for C#.NET:


#region Version Header
/// <remarks>
/// ----------------------------------------------------------------------------
///   Application: XXXXXXXXXXXXX
///     $Workfile: Default.aspx.cs $
///   Description: Start page for XXXXXXXXXXX.
///        Author: Frank Hagen
///       Created: 11/22/2006
///     Copyright: © 2007 XXXXXXXXXXXXXXX.
/// ----------------------------------------------------------------------------
///      $Modtime:  $
///     $Revision:  $
///   Mod $Author: XXXXXXXXXX $
///  VSS $Archive: /XXXXX/XXXXX/XXXXX/Default.aspx.cs $
/// ----------------------------------------------------------------------------
/// </remarks>

Keywords: ,

Filed Under: .NET | Programming

Lazy Coding or Careful Coding?

by fwhagen Fri, 09 March 2007

Some background:  A few months ago, I decided to put a little AJAX in an app I inherited.  The problem was a huge form that used post-back to fill some dropdown boxes based on values in other elements.  I coded the AJAX all from scratch, or near scratch anyway, mainly because ATLAS was still in early beta and this is a 1.1 app.  Anyway, everything worked well enough, or so I thought. 

This is where good end-user testing is so important.  You see, since I inherited this beast, I don't know how everything is supposed to work.  Some of the nuances are not always apparent to me and I might miss them.  For example, when creating a new form, a set a parameters will autofill the main form.  When I inserted my AJAX streamlining and tested, everything worked great from my perspective, a new form was created with some of the values prepopulated.   What I didn't realize was that the dropdown list was not being read correctly by the codebehind, because from it's perspective, it was empty the last time it saw it and was not reading the form post value.  I don't know yet whether this is my fault or the friendly designer who wrote this screaming... thing, but the end result is that not all of the values were filled that were supposed to.

So here's where my post title comes in:  When I put the AJAX calls in, I did not change the code-behind.  I simply added my JavaScript and disabled the elements' autopostback attributes.  When developing systems I don't understand, I make as little impact as possible and comment like hell, so someone can come in behind me and fix what I broke.  I also CYA in comments for who requested what changes and why.  Fixing broken code reminds me constantly about the importance of good commenting, despite that I don't do very good job of that in my original code.  Anyway, the implementation of some business logic features and these got rolled up, tested of course, and deployed late yesterday.

Well, this morning I get a frantic call and a HD Ticket:  Yep, the form is not populating all of the values.  It needs to be fixed immediately!  And behold!  It only took 15 minutes to find the cause and simply turn on the autopostback and disable the JavaScript.  All in the aspx file!  If I had removed or commented the methods in the code-behind, I would have had to recompile everything and risk version conflict; I had started on the next set of changes, of course.

So there's a lesson here, I think.  Although it is not the most efficient coding technique in the world, there is ample reason to not remove a method that you think is unused, especially if you do not understand the total impact of a change.  Let it sit there, all it is hurting is a tiny amount of overhead and some compile time.  This time it really saved my bacon!  Next time it will probably bite me.

Disagree?  Send me your exeriences in comments!

Keywords: , , ,

Filed Under: .NET | AJAX | Programming | Work

I Believe - ADO.NET Edition

by fwhagen Fri, 02 March 2007
I Believe...  that all database connections should be opened immediately before using them and closed immediately afterwards.
I Believe...  that if you are going to create a data layer, and you should, and you also create a data access layer, and you should, that the method be put in the lowest level and handled as abstractly as possible.  Yes the connection.close should be called before returning results.
I Believe...  that if you open the connection to the database in a method and pass that connection as a variable to another object, you should be shot.
I Believe...  that if you open the connection to the database in a method and pass it as a variable to another object, and forget to close said connection, you should be stabbed.
I Believe...  that if you open the connection to the database in a method and pass it as a variable to another object, and store the open connection in an ASP.NET session variable, you should be stabbed in the eye.  Twice.  With a spoon.
I Believe...  that if you store the results of a large query in ASP.NET session so you can page it back to the user, they haven't devised a painful enough punishment for you yet.
With special thanks to Blue Collar TV for the "I Believe..." concept.

Keywords: , ,

Filed Under: .NET | Programming | Rant

AJAX and NTLM Authentication Issues

by fwhagen Tue, 06 February 2007

Because the AJAX.NET hadn't been finalized yet, and because the app I was trying to "AJAX up" was a 1.1 app, I chose to roll my own AJAX backend.  It's extremely simple and lightweight, and really wasn't difficult to do.  The only problem that I had was in an Intranet setting with Windows Authentication security throughout.  The first time the client would initiate an AJAX call, the browser would throw a login credential request up.  I tried many different security settings to defeat that but was not successful.  Until I came across an article that mentioned it in passing.  Here's my altered code block:

        req.onreadystatechange = processReqChange;'POST', szURL, true);
        req.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
        req.setRequestHeader("Content-length", data.length);
        //req.setRequestHeader("Connection", "close");  /// Fixes AJAX Authentication Issue!!!

Note, line 5 is commentted out.  I don't know why this works, but it does and seemingly without any impact on either the client or the server.  All sessions are closed successfully and there is no data leakage that I can see.  So until I upgrade the app to 2.0 and use the official framework, I'm going with it.

If you'd like my AJAX backend .js file, it is only 60 lines and meets simple AJAX needs.  Just drop me a comment or email here with your address and I will get back to you.

Keywords: ,

Filed Under: .NET | AJAX

Trace Info from Business and Data Layer Classes

by fwhagen Tue, 23 January 2007

If you have tried to write trace info from an ASP.NET page, you know how useful this can be for debugging.  If you have tried to write to trace from another tier, you might have found it too difficult.  I did; until now:

To write to the current context trace (hint, hint), simply use the following call:

System.Web.HttpContext.Current.Trace.Warn(szCategory, szTraceMsg);

Yep, that's it.  So simple, yet so hard to find.  Now you (and I) know.


Filed Under: .NET

ASP.NET and NT Group Membership

by fwhagen Thu, 04 January 2007

This was MUCH easier to accomplish than ADSI info gathering.  Code is below:

    public bool IsMemberOf(string szGroupName)
        System.Security.Principal.WindowsPrincipal user =
        bool bVal = user.IsInRole(szGroupName);
        return (bVal);


Filed Under: .NET

Collecting User Info from the Domain

by fwhagen Fri, 22 December 2006

In Intranet applications, it is often critical to know who your user is.  NTLM and .NET give you the ability to get the login name, which is a great identifier, but tells you nothing about them.  You get this by using:


though typically that can be shortened to simply User.Identity.Name.  However, with LDAP calls you can get more information, but you need to know the LDAP address of the domain controller for AD.  One tool I found that helps is ldp.exe which is available with the Windows Support Tools from Microsoft (free!).  Just connect to the AD controller and it gives you the LDAP address you can use in your System.DirectoryServices calls.

The guts of it are this: 
Make a connection to the domain controller using an LDAP address:

        szADPath = String .Format( "LDAP://CN=Users,DC={0},DC=com" , szDomain);

        DirectoryEntry entry = new DirectoryEntry (szADPath);

Create a DirectorySearcher with filters and find the user you want:

        string_szUID = HttpContext.Current.User.Identity.Name;


        DirectorySearcher search = new DirectorySearcher(entry);

        search.Filter = String.Format("(SAMAccountName={0})", _szUID.Substring(_szUID.IndexOf("\\") + 1));

        search.PropertiesToLoad.Add("displayName");    // Full Name (Frank Hagen)

        search.PropertiesToLoad.Add("employeeid");     // EmplID   (123456)

        search.PropertiesToLoad.Add("givenname");      // First Name (Frank)

        search.PropertiesToLoad.Add("sn");             // Last Name  (Hagen)

        SearchResult result = search.FindOne();        // Execute filtered search

Then iterate through all of the properties returned:

        foreach(string key in result.Properties.PropertyNames)

That's really all there is to it. 

Of course the data available is dependant on the quality of data input by the Network Support group.  If they don't put anything useful in, your still stuck with nothing.  We are fortunate here and are taking the employee id and querying against other sources for additional data.

I am building a class for internal projects to use this.  When I have cleaned it up and optimized it properly, I will post it.  It was hard to find good resources online for this, surprisingly, although there were many 3rd party paid products available.  Maybe I should package it up and sell it too....


Filed Under: .NET