Ryan Smith

So I recently started looking into using Commercial controls for my ASP.NET application (I won't name any names yet in case I'm way off base and crazy, I don't want to make them look bad, when it's really me that's at fault).  They look great and function well but my little input page of 25k (pre canned controls) is now 130k!  I've gone through and disabled viewstate where appropriate and have followed the vendors suggestions regarding making them more lightweight...but it's disconcerting to see that file size discrepancy.

The obvious take away from this is that these controls are providing a better user experience and the cost of size.  Now the system this is going to be deployed to is rather burly and has plenty of bandwidth, be even with that the page certainly cannot load as fast as it did before.  My question is to you Good Citizens...what is your take on this...does the added functionality warrant a bigger page?  Am I too concerned about the HTML size and will HttpCompression save the day and make the whole thing moot?

Looks like you can't be logged into the forums and weblogs at the same time.  I say this because I found out the hard way...when I tried to look something up in the forums while I was writing a post...lost the last part of my post.  No biggie but I thought I'd put it out there as a tip of sorts.

I don't know about you but when I'm coding with I frequently find myself doing the same things over and over.  One of those things that used to drive me crazy with .Net was retrieving a table from the database and doing something with it.  I come from the classic asp world where it 'seemed' much more straight forward than it is in .Net...seems like the code count is higher...anyway it bugged me.  I heard about Enterprise Library's Data Access Application Block and decided to check it out. Better...code count got cut by a couple lines, but still not idiot proof (a design consideration, since I'll be it's only user). So I scoured the web, learned a lot from great programmers like Jean Paul Boodhoo (who's DNRTv series about Patterns inspired the following methods...inspired is a weak word...I blatantly pinched most of this from him but did tweak it a little (especially the SqlParameters part)).  Now when I want a DataTable I can call it as easily as this:

If I have a need to use tSQL I can do this:

DataTable MyDataTable = gateway.ExecuteSqlStatementDataTable("select * from Product_Categories");

Or if I have a need to use a stored procedure I can do this: 

DataTable MyDataTable = gateway.ExecuteSqlSP("dbo.usp_GetProductCategories");

That's it...one line blammo.  If I have a stored procedure with parameters it gets a little more codey but still pretty easy I just have to create an array of sql parameters:

SqlParameter[] sqp = new SqlParameter[2]; // the number here represents the number of parameters.
sqp[0] = new SqlParameter("@param1name",SqlDbType.Int);
sqp[0].Value = 35;
sqp[1] = new SqlParameter("@param2name",SqlDbType.VarChar);
sqp[1].Value = "some text";
DataTable MyDataTable = gateway.ExecuteSqlSP(sqp, "dbo.usp_SomeStoredProcedure");

 This is accomplished with the use of the Enterprise Library DAAB and the gateway pattern. Here's how to get this up and running:

<configuration>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<dataConfiguration defaultDatabase="Connection String" />

<add name="Connection String" connectionString="Data Source=myserver;Initial Catalog=mydatabase;Persist Security Info=True;User ID=dbusername;Password=xxxxxx"

</configuration>

Second: Make sure your project has the following references (for the DAAB part of this solution):
Microsoft.Practices.EnterpriseLibrary.Common
Microsoft.Practices.EnterpriseLibrary.Data

Third: Make your gateway class.  Here's mine in it's entirety:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace ApplicationLibrary
{
    public class DbGateway

    {

        public DataTable ExecuteSqlSP(string spname)
        {
          
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetStoredProcCommand(spname))
            {
                using (DataSet myds = db.ExecuteDataSet(dbCommand))
                {
                    return myds.Tables[0].Copy();
                }
            }
           

        }

        public DataTable ExecuteSqlSP(SqlParameter[] parms, string spname)
        {
          
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetStoredProcCommand(spname))
            {
                foreach (SqlParameter sqp in parms)
                {
                    dbCommand.Parameters.Add(sqp);
                }

                using (DataSet myds = db.ExecuteDataSet(dbCommand))
                {
                 return myds.Tables[0].Copy();
                }

            }
           
           

        }

        public DataTable ExecuteSqlStatementDataTable(string sqlstatementstring)
        {
          
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(sqlstatementstring))
            {
             using (DataSet myds = db.ExecuteDataSet(dbCommand))
                {
                 return  myds.Tables[0].Copy();
                }
            }
                     
           

        }
    }
}

Fourth: Implement and use:

What I like to do is make a private insance in the class I'm using so right after my class declaration I'd do:

private DbGateway gateway = new DbGateway();

Now in the methods of that class I can use gateway over and over like you saw in the examples at the top.

Where to go from here:
One of the first things I can see people wanting to do is return the DataSet and not just the table (in the event there are many tables in the set).  I think you can see where that'd be pretty easy.

I hope you found this article helpful.  If you have any suggestions on changes or tweaks please feel free to leave a comment.

Special Thanks:
Jean Paul Boodhoo, Svante (from the asp.net forums) and Dot Net Rocks and DnrTV. (If you don't listen to either of those casts you are missing out my friend).

Hi everyone!

I attened the So. Cal. Code Camp this weekend...well one day any way.  The weather and life kept me away on Sunday.  However Saturday was great, I attended 3 sessions. The first was a two parter .Net Object Modeling and was presented by Eric Kaufman.  Eric did a fantastic job of keeping it light and fun.  The presentation had an added bonus of a peek at integrating NHibernate.  Very eye opening to see a large project using NHibernate.

The third session unfortunately was a little lack luster and didn't keep me in my seat.  But that's ok the point of the Code Camp is to get you exposed to different methodolgies and modes of thinking.

 All in all it's very worth while to attend and I highly recommend that you attend one near you when the opportunity presents itself.

Cheers!
Ryan

I'll be moving all (what little there is ATM) the content from www.file-new.com to here and revamping it a little.  File-new won't be going away, I'll probable still use it to host images and screencasts.

 I'll be re-writing my DAAB tutorials too, had some requests to make them more beginnery.  So that will be coming shortly.  Hopefully before the weekend as I'll be spending most of it at the Southern California Code Camp www.socalcodecamp.com

Cheers!

Ryan