C# and ASP.Net - Searching for a control recursively

At some point during my programming career I found I needed to search for a control on a page but had no way to know who the control's parent would be.  So after a little thinking I thought to myself "what if I could just search any parent and it's children recursively?".

This would give me the flexibility to search a specific parent control like a panel or an entire master page if needed.  So by adding the following methods to a "Search" class (or whatever you want to name it) you too can have this functionality:

    /// <summary>
    /// Searches through all child controls looking for the control name specified
    /// </summary>
    /// <param name="parentControl">
    /// Control - the control to search
    /// </param>
    /// <param name="controlName">
    /// string - control name to find
    /// </param>
    /// <param name="control">
    /// Control - control to bind to the found control
    /// </param>
    public static void FindControl(Control parentControl, string controlName, ref Control control)
    {
        if (control.ID == null)
        {
            SearchControl(parentControl, controlName, ref control);
        }


        if (parentControl.HasControls())
        {
            foreach (Control item in parentControl.Controls)
            {
                FindControl(item, controlName, ref control);
            }
        }
    }


    /// <summary>
    /// Helper method for FindControl
    /// </summary>
    private static void SearchControl(Control parentControl, string controlName, ref Control control)
    {
        if (parentControl.FindControl(controlName) != null)
        {
            control = parentControl.FindControl(controlName);
        }
    }


Pretty simple yeah?

So now you just have to call FindControl and pass in the parent control to search.  It will then search all of it's children parent controls looking for the control by name.  Once it finds the control its looking for it will assign a reference to the ref Control control parameter.  If it doesn't find it then the control you passed in will still be null or set to whatever it is you set it to before passing it in.

Happy control searching!

-Matt

C# and ASP.Net - Reusable data layer

Creating a data layer can be a fairly complex issue.  There are many options to choose from, and keeping business code removed from your data layer can be bothersome.

The example code below will describe how to create an abstract database call using MS SQL Server.  This technique will demonstrate how to keep the data layer far far away from the business code.  Enjoy =)

First thing we need to do is add a connection string to the database we want access to using the web.config file.  In your web.config file create a "connectionstring" element like this:

<configuration>
    <configSections>
    ........
    <connectionStrings>
      <add name="SiteConnectionString" connectionString="Server=..;Database=..;Uid=..;Password=..;"/>
    </connectionStrings>
.......

Now that we have a connection string ready to go we can build the abstract class to take advantage of it like this:


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


public abstract class DatabaseCall : IDisposable
{
    //Create a connection
    private SqlConnection sqlConnection = new SqlConnection();


    //Create a command
    private SqlCommand sqlCommand = new SqlCommand();



    //Create a transaction
    private SqlTransaction sqlTransaction; 

.......

We need this class to disposed of itself correctly so the IDisposable interface has been added to handle that.  So let's add some clean up code to the class:


    public void Dispose()
    {
        if (sqlConnection != null)
        {
            sqlConnection.Close();
            sqlConnection.Dispose();
        }
        if (sqlCommand != null)
        {
            sqlCommand.Dispose();
        }
        if (sqlTransaction != null)
        {
            sqlTransaction.Dispose();
        }
    }




Now that we can clean up our mess after we instantiate the class, let's create our constructor for the class:


    public DatabaseCall(bool hasTransaction, string connectionString)
    {
        this.SetConnection = connectionString;
        sqlCommand.Connection = sqlConnection;
        sqlConnection.Open();
        sqlCommand.CommandTimeout = 30;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        if (hasTransaction)
        {
            sqlTransaction = sqlConnection.BeginTransaction();
            sqlCommand.Transaction = sqlTransaction;
        }
    }


The constructor is simple enough.  You tell it if the database calls from the derived class will be using transactions, and which connection string to use (from the web.config).

Now that we have all that taken care of we can create a simple database call method like so:


    public int ExecuteNonQuery(string procedureName, Hashtable hashtable)
    {
        try
        {
            //Clear parameters and set stored procedure to execute
            sqlCommand.Parameters.Clear();
            sqlCommand.CommandText = procedureName;


            AssignParameters(ref sqlCommand, hashtable);


            return sqlCommand.ExecuteNonQuery();
        }
        catch (SqlException sqlex)
        {
            throw sqlex;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


The ExecuteNonQuery method takes a stored procedure name and a hashtable of parameter values and sends that information to the database after a bit of manipulation from the AssignParameters method does it's magic.  Of course if there are any exceptions they are thrown up the stack to be captured by a process of your choosing.

Now that we can call a stored procedure we need to know how the parameters are pulled out of the hashtable.  This brings us to the AssignParameters method:


    private void AssignParameters(ref SqlCommand sqlCommand, Hashtable hashtable)
    {
        SqlCommandBuilder.DeriveParameters(sqlCommand);


        //Check for a parameter match and set the value
        foreach (SqlParameter parameter in sqlCommand.Parameters)
        {
            if (hashtable.ContainsKey(parameter.ParameterName))
            {
                parameter.Value = hashtable[parameter.ParameterName];
            }
        }
    }



The AssignParameters method uses the SqlCommandBuilder.DeriveParameters() method to pull out parameter information from the database for the stored procedure stored in the SqlCommand object.  Once that information is returned from the database we can go through the hashtable and find the stored procedures by name and create a parameter collection.  After all that information is gathered you can then call the ExecuteNonQuery() method in ADO.NET which executes the stored procedure and returns the number of rows affected by the call.

So now let's derive this puppy and see how it works in a real application.  We will need to build a derived class for each connection string in our web.config file (in this case just one).  So let's create the derived class now:

public class RunDabaseCall : DatabaseCall
{

    public RunDabaseCall ()
        : base(false, ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString)
    { }
}

Whew!  What a monster class!  Now to run a database call from your application simply do this:

int Main(args[])
{

        Hashtable hash = new Hashtable();
        RunDabaseCall runDatabaseCall = new RunDabaseCall();


       hash["@parameter1"] = "some data";
       runDatabaseCall.ExecuteNonQuery("stored procedure name", hash);
      
      runDatabaseCall.Dispose();

}


You want to run this in a transaction!?  Yer crazy, but I like you... Here's all you have to do.  Add this bit to the DatabaseCall class:


    public void CommittTransaction()
    {
        try
        {
            if (sqlTransaction != null)
            {
                sqlTransaction.Commit();
            }
        }
        catch (SqlException sqlex)
        {
            throw sqlex;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

AND:


    public void RollBackTransaction()
    {
        try
        {
            sqlTransaction.Rollback();
        }
        catch (SqlException sqlex)
        {
            throw sqlex;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }



Now change the derived class RunDatabaseCall like this:

public class RunDabaseCall : DatabaseCall

{

    public RunDabaseCall ()

        : base(true, ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString)

    { }

}

Now when you run your database calls in your application you will be running them in a database transaction:

int Main(args[])

{

        Hashtable hash = new Hashtable();

        RunDabaseCall runDatabaseCall = new RunDabaseCall();

       hash["@parameter1"] = "some data";            <------------------ CASE SenSItiVE

       runDatabaseCall.ExecuteNonQuery("", hash);

      runDatabaseCall.CommitTransaction();

      runDatabaseCall.Dispose();

}

If there are any errors or any reasons to roll back your transaction like it never happened just call the RollBackTransaction() method.

Thats it!  Happy database calling.

-Matt