<geek>Let me first say that I really, really like working with .nizzle. The problem is that so many of the web apps that i work on are really similar, so a lot of the work gets boring; seems like I'm always doing the same steps for an admin site: 1. Create database table for new article/category/user/whatever. 2. Create all associated stored procedures to add/update/delete/otherwise modify new db table. 3. Create a strongly typed C# object to hold all the methods and properties of said object. 4. Create front-end .aspx pages to allow admin user to make use of steps 1-3. 5. Lather/rinse/repeat.

The other day I found a neat little chunk of code from M$FT to help me out with step 3, the Data Access Application Block, which is really just a bunch of handy shortcuts for some of the more repetitive coding that I do.

"Well, geez", you're saying, "don't just tell me about it, show my why it's better!" Hey sure.

For a really simple grab of all records and stuffing them in a database, we go from this:

public DataSet GetAllArticles()
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
            SqlCommand myCommand = new SqlCommand("GetAllNewsArticles", myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;

            // Adapter and DataSet
            SqlDataAdapter oAdapter= new SqlDataAdapter();
            oAdapter.SelectCommand=myCommand;
            DataSet oDataSet = new DataSet();

            try
            {
                myConnection.Open();
                oAdapter.Fill(oDataSet,"Articles");
                return oDataSet;
            }

            catch(Exception oException)
            {
                throw oException;
            }
            finally
            {
                // Close the Connection
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }
        } // end GetAllArticles()

to this:

public DataSet GetTopLevelProductCategories()
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
            DataSet oDataSet;
            oDataSet = SqlHelper.ExecuteDataset(myConnection,CommandType.StoredProcedure, "GetTopLevelProductCategories");
            return oDataSet;
            if (myConnection.State == ConnectionState.Open) myConnection.Close();
        } // end GetTopLevelProductCategories()

Notice the "ExecuteDataset" function, which is an overloaded function which lets you pass parameters, transaction info, or not.

I like it even better when you're passing parameters to an insert or update stored procedure. Old style:

public bool UpdateCategory(Category myCategory)
        {
            SqlConnection myConnection = null;
            SqlCommand    myCommand    = null;
            bool          bUpdated     = true;

myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
myCommand = new SqlCommand("UpdateCategory", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterCategoryId = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
            parameterCategoryId.Value = myCategory.GetCategoryId();
            myCommand.Parameters.Add(parameterCategoryId);

            SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.VarChar, 255);
            parameterName.Value = myCategory.GetName();
            myCommand.Parameters.Add(parameterName);

            SqlParameter parameterImageUrl = new SqlParameter("@ImageUrl", SqlDbType.VarChar, 255);
            parameterImageUrl.Value = myCategory.GetImageUrl();
            myCommand.Parameters.Add(parameterImageUrl);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
                bUpdated = false;
            }
            finally
            {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }

return bUpdated;

} // end UpdateCategory()

New hotness:

public void UpdateProductCategory(ProductCategory myCategory)
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);

            SqlParameter[] arParams = new SqlParameter[4];
            arParams[0] = new SqlParameter("@CategoryID", myCategory.GetCategoryId());
            arParams[1] = new SqlParameter("@CategoryName", myCategory.GetCategoryName());
            arParams[2] = new SqlParameter("@DetailedDescription", myCategory.GetDetailedDescription());
            arParams[3] = new SqlParameter("@DefaultProduct",myCategory.GetCodeNumber());
            SqlHelper.ExecuteNonQuery(myConnection, CommandType.StoredProcedure, "UpdateProductCategory", arParams);
            if (myConnection.State == ConnectionState.Open) myConnection.Close();
        } // end UpdateProductCategory()

No more setting datatypes, and taking 3 lines to add one stored procedure parameter. Less is more. </geek>