Posted on by admin

Pengdows.CRUD

This very helpful wrapper over ADO.NET has been released to NuGet free of charge and will soon be released as open source. It is now and will forever be free to use.

At the moment, I have only included the .NET 4.0 binary for the moment, this will be remedied soon. For the moment, create a .NET 4 application.

Here is some example code with comments on basic functionality, just to jump start you.

//create a context to the database, this will allow us
// to create objects of the correct type from the factory
// as well as find out about things like quote prefix and
// suffixes. You may either choose a connectionstring and
// provider name, or simply pass they "name" of a
// connectionString entry in the config file.
var context = new DatabaseContext("dsn");

//create a container for the SQL and parameters etc.
 var sc = context.CreateSQLContainer();

//write any sql, I am making sure to create it using
//the providers quotes. The SQLText property is a
//StringBuilder, allowing for complext string manipulation
sc.SQLText.AppendFormat(@"SELECT {0}CategoryID{1}
  ,{0}CategoryName{1}
  ,{0}Description{1}
  ,{0}Picture{1}
 FROM {0}Categories{1}
 WHERE {0}CategoryID{1}=", context.QuotePrefix, context.QuoteSuffix);

//create a parameter, automattically generating a name
//and attaching it to sqlcontainer
var p = sc.AddWithValue(DbType.Int32, 7);

//append the name of the parameter to the SQL string
//if the provider only supports positional parameters
// that will be used. However, if named parameters are
// supported, the proper prefixing will be used with the
//name. For example, @parameter for SQL Server, and
// arameterName for Oracle.
sc.SQLText.AppendFormat(context.SQLParameterName(p));

//write the resulting SQL for examination by the programmer
Debug.WriteLine(sc.SQLText);

// get a datatable
var dt = sc.ExecuteDataTable();

// get the first row of the datatable
var row = dt.GetFirstRow();

//loop through and output all the data to the screen.
foreach (var itm in dt.Columns.OfType())
{
     Console.WriteLine("{0}: {1}", itm.ColumnName, row[itm]);
}

So this is easy, but why would you want to use this? What does it provide over plain ADO.NET, or EnterpriseBlocks?

Here are some of the benefits.

  • Self-contained blocks for execution.
    • SQLContainers – know which database to execute against
      • Carry the SQL and parameters in 1 encapsulated object
      • Adds “ExecuteDataSet” and “ExecuteDataTable” functions, making getting disconnected DataSet and DataTable objects easy. Also, exposing the DataTable, eliminates the overhead of always getting a DataSet, when only a single table is needed.
      • Changes the default on DbDataReaders to automatically close the connection upon closing of the object, based the ConnectionMode.
    • DatabaseContext – Encapsulates much of the programming people skip
      • Using a factory to create the connections
      • Interrogates the provider to determine
        • If there is support for named parameters
        • What the quoting characters are, defaulting to SQL-92 standard double-quotes ( ” ).
        • If there is support for stored procedures.
        • What is the named parameter indicator (such as an @ for SQL Server or : for Oracle).
        • Validates connection string
        • Will automatically read from the “ConnectionStrings” area of the app.config or web.config
        • Allows you to specify connection mode
          • Standard – uses connection pooling, asking for a new connection each time a statement is executed, unless a transaction is being used.
          • SingleConnection – funnels everything through a single connection, useful for databases that only allow a single connection.
          • SqlCe – keeps a single connection open all the time, using it for all write access, while allowing many read-only connections. This prevents the database being unloaded and keeping within the rule of only having a single write connection open.
          • SqlExpressUserMode – The same as “Standard”, however it keeps 1 connection open to prevent unloading of the database. This is useful for the new localDb feature in SQL Express.
        • Sets up SQL Server connections with the proper options to support Indexed Views.
        • Homogenizes connection strings using the DbConnectionStringBuilder class.

Leave a Reply