Recent twitter entries...

Doing MVC type stuff with Classic ASP

Posted by Brad | Posted in Ramblings | Posted on 09-08-2011

Way back in 2006 I created one of the many affiliate marketing sites that have been giving a handy modest return (not easy when your a one man band, with a full time job, and enjoy spending time with your family!). The site was Bingo Tonight and I wanted to learn from the headaches of a previous site Financeland on how to publish database driven content in a url re-write friendly manner from a handful of templates (views) so I could put new offers, reviews up and have them easily crawled with keywords in the URL for SEO juice.

So instead of the httphandlers and component installed on IIS to handle the financeland article redirects the simple use of a custom 404 page using server.execute served my purposes, detecting from the first part of the URL after the domain what the type of request was (controller), then using backslash separated parameters “folders” to select the records (parameters).

So as I’m here in 2011 going through the MVC 3 music store tutorial application… it’s not entirely that unfamiliar to grasp the concept of turning URLs into dynamic pages through a limited set of classes.

Edit: ahhhh most of the way through the music store tutorial now and I’m seeing the power of MVC scaffolded views… yep.. that’s a very nice timesaver indeed!, the razor engine is very nice and clean to code with by the look of it.

Continue Reading

Nice C# class for ADO.net databinding

Posted by Brad | Posted in C# | Posted on 02-08-2011

Nice class with a couple of methods to handle execution of a stored procedure with an array of parameters or a regular query and return a dataset object, all you need do is pass in the connection string when creating the connection.

using System;
using System.Data.SqlClient;
using System.Data;
 
namespace Connection
{
  public class Connect
  {
    private SqlConnection sqlConn;
 
    // Constructor
    public Connect(string ConnectionString)
    {
      sqlConn = new SqlConnection(ConnectionString);
    }
 
    // Open the connection
    public void OpenCon()
    {
      sqlConn.Open();
    }
 
    // Close the connection
    public void CloseCon()
    {
      sqlConn.Close();
    }
 
    public DataSet ExecuteProc(string spName, SqlParameter[] spParams)
    {
      try
      {
        SqlCommand cmd = new SqlCommand(spName, sqlConn);
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        if (spParams != null)
        {
          cmd.Parameters.AddRange(spParams);
        }
        DataSet ds = new DataSet();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          OpenCon();
          da.Fill(ds);
          cmd.Dispose();
          da.Dispose();
        }
        return ds;
      }
      catch (Exception ex)
      {
        throw new Exception("Error while executing the stored proc : " + ex.Message);
      }
      finally
      {
        CloseCon();
      }
    }
 
    public DataSet ExecuteQuery(string queryString)
    {
      try
      {
        SqlCommand cmd = new SqlCommand(queryString, sqlConn);
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.Text;
 
        DataSet ds = new DataSet();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          OpenCon();
          da.Fill(ds);
          cmd.Dispose();
          da.Dispose();
        }
        return ds;
      }
      catch (Exception ex)
      {
        throw new Exception("Error while executing the query : " + ex.Message);
      }
      finally
      {
        CloseCon();
      }
    }
 
  }
}

 

Usage in your code behind, place these using directives

using Connection;
using System.Data.SqlClient;

Call a stored procedure in an event within the page such as follows

Connect Conn = new Connect(WebConfigurationManager.ConnectionStrings["NAMEOFCONNECTIONSTRING"].ConnectionString);
      Conn.ExecuteProc("AddRecord", new SqlParameter[] {
      new SqlParameter("@Name", txtName.Text),
      new SqlParameter("@Address", txtAddress.Text),
      new SqlParameter("@Telephone", txtTelephone.Text),
      new SqlParameter("@Comments", txtComments.Text),
      new SqlParameter("@CodeField", ddlCodeField.SelectedValue),
    });

Continue Reading

Binding a GridView in C# with a parameterized command

Posted by Brad | Posted in C# | Posted on 14-07-2010

With two controls on your asp.net page a DropDownList (ID ddlDropDownList) and a GridView (ID gvGridView), to retrieve the SelectedValue from the DropDownList and use that in the where clause of the query used to bind data to your GridView the following will help.

Required using statements

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

ADO.NET block of code to base your bind on using an SQLDataAdapter so you can still have paging on the GridView.

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTRINGNAME"].ConnectionString))
    {
      using (SqlCommand cmdGrid = new SqlCommand("SELECT * FROM TABLE WHERE FIELD = @PARAMETER", con))
      {
      cmdGrid.Parameters.AddWithValue("@PARAMETER", ddlDropDownList.SelectedValue);
      con.Open();
 
      using (SqlDataAdapter da = new SqlDataAdapter(cmdGrid))
      {
        DataTable dt = new DataTable();
        da.Fill(dt);           
        gvGridView.DataSource = dt;
        gvGridView.DataBind();
      }
      }
    }

The structure of the code is with using statments to ensure the garbage collection is carried out efficiently.

Continue Reading