Archive for April, 2007

DbProviderFactory GetConnection

With the new ADO.NET 2.0 DbProviderFactory and DbProviderFactories classes, you can change the database provider at runtime.  The DbProviderFactories class is use to create a factory object, which in turn is use to create the appropriate provider objects (connection, command, dataadapter, etc).  Here’s an example that returns a DbConnection object based on the name of the connection string in the Web.config file.

/// <summary>
/// Create generic database connection object using new DbProviderFactories class.
/// </summary>
/// <param name="name">Name of the connection string in the Web.Config file</param>
/// <returns></returns>
public static DbConnection GetConnection( string name )
{
    ConnectionStringSettings settings = WebConfigurationManager.ConnectionStrings[name];
    DbProviderFactory factory = DbProviderFactories.GetFactory( settings.ProviderName );
    DbConnection conn = factory.CreateConnection();

    conn.ConnectionString = settings.ConnectionString;

    return conn;
}

This entry was posted on Monday, April 23rd, 2007 at %I:%M %p and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Random Rows From a Database Table

By using the NEWID() function in the ORDER BY clause and setting a TOP xx value, a random number of records can be returned from a database table.  There are similar approaches for each of the major databases (Oracle, Sybase, DB2, etc).  The example below is T-SQL.

SELECT TOP 10 lastname, firstname, phone
FROM dbo.customers
ORDER BY NEWID()


In MS Access you can achieve the same results using the RND() function in the ORDER BY clause with a SEED (an AutoNumber or DateTime column seems to work just fine). The RND() without a parameter returns a value between 0 and 1, but doesn’t regenerate for each row in the query and you end up with the same value for each row. The example below uses an AutoNumber column as the seed.

SELECT TOP 10 lastname, firstname, phone
FROM dbo.customers
ORDER BY RND([key]);

 

This entry was posted on Monday, April 23rd, 2007 at %I:%M %p and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.