An helper class to access databases with ado.

A while ago I read an exceptional post by Ayende, I found it very interesting, and I decided to use his technique with some projects, but I choose to do a little modification to make the code works with every type of database, not only with SqlServer. Here is my version. All real code is made by ayende, I simply do some changes to make it database agnostic.

public static void Execute(VoidFunc<DbCommand, DbProviderFactory> functionToExecute) {
   
DbProviderFactory factory = DbProviderFactories.GetFactory(
      MainConnectionString.ProviderName);
   
using (DbConnection connection = factory.CreateConnection()) {
      connection.ConnectionString = MainConnectionString.ConnectionString;
      connection.Open();
      
DbTransaction tx = connection.BeginTransaction();
      
try {
         
using (DbCommand command = factory.CreateCommand()) {
            command.Connection = connection;
            command.Transaction = tx;
            functionToExecute(command, factory);
         }
         tx.Commit();
      }
      
catch {
         tx.Rollback();
         
throw;
      }
      
finally {
         tx.Dispose();
      }
   }
}

The modification from the original ayende’s code is the following: instead to directly create a SqlConnection I use DbProviderFactories to create a DbProviderFactory, with this object I can create connection and parameter based on the provider name specified in the app config. The connectionstring is taken from a property MainConnectionString that simply use ConfigurationManager to get the connection from the app.config, this property returns a ConnectionStringSettings object that not only stores the effective connection string, but contains also some other properties, such as ProviderName, used to specify the provider. The rest of the code is the same of exceptional Ayende’s example. The delegate I use to execute the query accepts both a DbCommand and a DbProviderFactory that must be used to create parameter for the command. I also inserted another helper function that adds a parameter to the command choosing the right name at runtime based on the provider type (this is necessary because for Oracle parameter names are to be prefixed with :, sql server use @, etc…).

private static Dictionary<Type, String> mParametersFormat = new Dictionary<Type, String>();
 
private static String GetParameterFormat(DbCommand command) {
   
if (!mParametersFormat.ContainsKey(command.GetType())) {
      mParametersFormat.Add(
         command.GetType(),
         command.Connection.GetSchema(
“DataSourceInformation”)
            .Rows[0][
“ParameterMarkerFormat”].ToString());
   }
   
return mParametersFormat[command.GetType()];
}

public
 static void AddParameterToCommand(
   
DbCommand command,
   
DbProviderFactory factory,
   System.Data.
DbType type,
   
String name,
   
object value) {
 
   
DbParameter param = factory.CreateParameter();
   param.DbType = type;
   param.ParameterName = 
String.Format(GetParameterFormat(command), name);
   param.Value = value;
   command.Parameters.Add(param);
}

As you can see I store the format of the parameter in a dictionary indexed by command type. For each different type of commands (SqlCommand, OleDbCommand) I store the ParameterMarkerFormat string that is retrieved with DbConnection.GetSchema() function. This caching could be avoided if we are sure to use the same db in every part of the application (an assumption that is easily true since it is very strange to create an application that stores some data in a sql server and some other data in a different engine, for example Oracle). The ParameterMarkerFormat is a sting that can be used with String.Format to create the right parameter name for the current provider, for example with sql server is “{0}”, while for an access database is “?”, because access only supports positional parameters. Here is an example that use the class to find all customers living in London from the NorthWind database.

Int32 CustomerCount = Nablasoft.Helpers.DataAccess.Execute<Int32>(
   
delegate(DbCommand command, DbProviderFactory factory) {
      command.CommandType = System.Data.
CommandType.Text;
      command.CommandText = 
“SELECT COUNT(*) FROM Customers WHERE City = @city”;
      Nablasoft.Helpers.
DataAccess.AddParameterToCommand(
         command, factory, System.Data.
DbType.String, “city”, “London”); 
      
return (Int32)command.ExecuteScalar();
   });

The connection in the app.config is the following. As you can see the query text use a parameter name prefixed

<connectionStrings>
   <add 
         name=MainDatabase 
         connectionString=Database=Northwind;Server=localhost\SQL2000;User Id=sa;Password=ottaedro_2006@ 
         providerName=System.Data.SqlClient/>
 </connectionStrings>

Now the helper is database independent. I want to thank Ayende for it’s great example, his code is really amazing.

Alk.

Improving performances in sql server when join large varchar columns

I have a database with a table that contains a column called pageData of type nvarchar(3000), my problem is that periodically I need to check another db that have a similar table and I need to make a join between the two tables on pageData column. The problem is that a column of nvarchar(3000) cannot be indexed, so the join is too slow.

A possible solution is to include a extended stored procedure to compute the Md5 of a string, apossible approach can be found here. Before joining the two table I compute the md5 value of column pageData in another column that now is indexed because is a char(32) type, and then make the join. The result is much faster than the older join.

Alk.

Today is not a good day :(

Today is not a good day for a couple of reasons, but now my visual studio instance begins to behave in a strange manner, when I digit ALT+126 to make tilde character in a code windows, a strange chars appears instead of tilde, and the font of the visual studio changes, here how it looks like on my monitor

In every other window ALT+126 works as expected (~), mah……………

Alk.

Change style of user controls with css

Thanks to Guardian for the suggestion on this tip, I’m not really a stylesheet guru :D.
The problem is this, I have an ASP.NET user control and I want to show it with two different styles in the same page. I looked into some asp.net forums and I found some examples of peoples that changes SkinId at runtime or did some other tricks, but a much better solution is to use css. In code Example attached to this post I show how to format single instances of user control with the only support of css. First of all This is the code of my user control, a simple label with a textbox.

<asp:Label ID=”Label1″ runat=”server” Text=”Description” CssClass=”labelDefault” />
<
asp:TextBox ID=”TextBox1″ runat=”server” CssClass=”textboxDefault” />

As you can see I simply use a style labelDefault for the label and a textboxDefault for the textbox. For my site the default aspect is red so I use this base css.

.labelDefault 
{
    color: #FF0000;
    font-weight:lighter;
}

.textboxDefault 
{
    border-color:#FF0000;
    border-width: 2;
}

Nothing special :D. Now my objective is to create a page with an instance of this user control with default red style and another instance with a different style that will show everything in blue color. The code of the aspx page is the following.

<div>
<
uc1:MyControl ID=”MyControl1″ runat=”server”></uc1:MyControl>
</
div>
<
div class=”bluestyle”>
<
uc1:MyControl ID=”MyControl2″ runat=”server”></uc1:MyControl>
</
div>

As you can see I simply enclose the second instance of the user control in a div with a style called bluestyle. Now here is the magic part, the css styles to format only the second instance of user control:

.bluestyle .labelDefault 
{
    color: #0000FF;
    font-weight:lighter;
}

.bluestyle .textboxDefault 
{
    border-color:#0000FF;
    border-width: 2;
}

It works as following, let’s consider the first style called .bluestyle .labeldefault, since it has a double name it
will be applied to every object that satisfies these two conditions: has the labelDefault style applied and is enclosed in an object with style bluestyle. With this technique you can change the style of a user control simply enclosing in a div with different css style, this result is achieved only with the use of css styles, no SkinId, no procedural code and no strange tricks :D.

Alk.

 
Â

 Â