Saturday, September 26, 2009

DataGeneral: Lightweight ADO.NET wrapper replacing Enterprise Library Data Access Application Block

 

The DataGeneral class provides access to the most often used features of ADO.NET in a simple-to-use class boosting developer productivity.

DataGeneral is lightweight for maximum performance. It employs Microsoft best practices. DataGeneral provides the best features of Enterprise Libraries Data Application block. In my experience, I like the Enterprise libraries Application Blocks to learn what features of the .NET framework are available , and best practice coding techniques (hence the name patterns and practices). But I don't like the weight of the enterprise libraries, and the random errors I get when versions are out of sync across environments. That is why I wrote DataGeneral which is just a single wrapper class. With DataGeneral you will have less lines of code to debug. DataGeneral is specific to Microsoft Sql Server.

For example, one of the features of the Data Access Block is stated as: "By changing the settings in the configuration file, developers can use their applications with different database configurations without recompiling their code." This makes me laugh: What application can have its most major component ripped out and replaced without recompiling the code? I would state that this feature is useless as are many of the other features in the Data Access block. However Enterprise library has many good features like saving you lines of code when building a SqlCommands with SqlParameters. That's why I wrote DataGeneral: All the good parts without all the useless parts.

DataGeneral has been used in several enterprise applications currently in production. On each project it has been further refined and perfected.

 

Example 1) DataGeneral Source Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
public class DataGeneral
{

private static SqlConnection createConnection()
{
string sqlCn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection cn = new SqlConnection(sqlCn);
cn.Open();
return cn;
}

/// <summary>
/// Wraps the SqlCommand.ExecuteReader() method.
/// </summary>
/// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
/// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
/// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement.</param>
/// <returns>Returns a SqlDataReader which MUST be wrapped in a using statement so that its SqlConnecion is closed as soon as the SqlDataReader is disposed.</returns>
public static SqlDataReader ExecuteReader(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
{
//IMPORTANT: make sure you wrap the returned SqlDataReader in a using statement so that it is closed. (You do not need to close the SqlConnection object.)
SqlConnection cn = createConnection();

SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);

if (isStoredProcedure)
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
cmd.Parameters.AddRange(parameters.ToArray());
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

/// <summary>
/// Wraps the SqlCommand.ExecuteScalar() method.
/// </summary>
/// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
/// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
/// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement.</param>
/// <returns>Returns the first value of the first row of the Sql Statement.</returns>
public static object ExecuteScalar(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
{
object scalarValue;
using (SqlConnection cn = createConnection())
{
SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
if (isStoredProcedure)
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
cmd.Parameters.AddRange(parameters.ToArray());
scalarValue = cmd.ExecuteScalar();
}
return scalarValue;
}

/// <summary>
/// Wraps the SqlCommand.ExecuteNonQuery() method.
/// </summary>
/// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
/// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
/// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement</param>
/// <returns>Returns the number of rows affected by ExecuteNonQuery().</returns>
public static int ExecuteNonQuery(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
{
int rowsAffected;
using (SqlConnection cn = createConnection())
{
SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
if (parameters != null)
cmd.Parameters.AddRange(parameters.ToArray());
if (isStoredProcedure)
cmd.CommandType = CommandType.StoredProcedure;

rowsAffected = cmd.ExecuteNonQuery();
}
return rowsAffected;
}

public class ParamBuilder
{
private readonly List<SqlParameter> _parameters = new List<SqlParameter>();
public List<SqlParameter> Parameters
{
get
{
return _parameters;
}
}

public void AddParam(SqlDbType sqlDbType, string paramName, object paramVal)
{
SqlParameter p = new SqlParameter(paramName, sqlDbType);
p.Value = paramVal ?? DBNull.Value;
_parameters.Add(p);
}

public SqlParameter AddOutputParam(SqlDbType sqlDbType, string paramName)
{
SqlParameter p = new SqlParameter(paramName, sqlDbType);
p.Direction = ParameterDirection.Output;
_parameters.Add(p);
return p;
}
}


}
}


Example 2) Sample Usage of ExecuteReader()



Note you should wrap the ExecuteReader() in a using statement.



public static Address GetAddressByID(int addressID)
{
Address address = null;

DataGeneral.ParamBuilder paramBuilder = new DataGeneral.ParamBuilder();
paramBuilder.AddParam(SqlDbType.Int, "@AddressID", addressID);

using (SqlDataReader sqlDataReader = DataGeneral.ExecuteReader("Location_Address_GetByID", paramBuilder.Parameters, true))
{
if (sqlDataReader.Read())
{
address = PopulateAddressFromReader(sqlDataReader);
}
}

return address;
}

private static Address PopulateAddressFromReader(SqlDataReader sqlDataReader)
{
Address address = new Address();

address.AddressID = (int)sqlDataReader["AddressID"];
address.AddressLine1 = sqlDataReader["AddressLine1"] as string;
address.AddressLine2 = sqlDataReader["AddressLine2"] as string;
address.AddressLine3 = sqlDataReader["AddressLine3"] as string;
address.City = sqlDataReader["City"] as string;
address.Zipcode = sqlDataReader["ZipCode"] as string;
address.CountryRefID = (int)sqlDataReader["CountryRefID"];
address.CityLatitude = sqlDataReader["CityLatitude"] as float?;
address.CityLongitude = sqlDataReader["CityLongitude"] as float?;
address.ZipcodeLatitude = sqlDataReader["ZipcodeLatitude"] as float?;
address.ZipcodeLongitude = sqlDataReader["ZipcodeLongitude"] as float?;

return address;
}


Example 3) Sample usage of ExecuteNonQuery()



Note, that both inline sql and stored procedures are supported. This example also demonstrates output parameters. For example, if you are inserting in to a table with an identity column and want to return the newly created identity value.



This example also demonstrates wrapping a call with TransactionScope() which is another great feature of the .NET framework.



public static void AddAddress(Address address)
{
DataGeneral.ParamBuilder paramBuilder = new DataGeneral.ParamBuilder();
SqlParameter paramNewAddressId = paramBuilder.AddOutputParam(SqlDbType.Int, "@NewAddressID");
paramBuilder.AddParam(SqlDbType.VarChar, "@AddressLine1", address.AddressLine1);
paramBuilder.AddParam(SqlDbType.VarChar, "@AddressLine2", address.AddressLine2);
paramBuilder.AddParam(SqlDbType.VarChar, "@AddressLine3", address.AddressLine3);
paramBuilder.AddParam(SqlDbType.VarChar, "@City", address.City);
paramBuilder.AddParam(SqlDbType.VarChar, "@StateProvinceRefID", address.StateProvinceRefID);
paramBuilder.AddParam(SqlDbType.VarChar, "@ZipCode", address.Zipcode);
paramBuilder.AddParam(SqlDbType.VarChar, "@CountryRefID", address.CountryRefID);
paramBuilder.AddParam(SqlDbType.Float, "@CityLatitude", address.CityLatitude);
paramBuilder.AddParam(SqlDbType.Float, "@CityLongitude", address.CityLongitude);
paramBuilder.AddParam(SqlDbType.Float, "@ZipcodeLatitude", address.ZipcodeLatitude);
paramBuilder.AddParam(SqlDbType.Float, "@ZipcodeLongitude", address.ZipcodeLongitude);

const string sql =
@" INSERT Address
( AddressLine1, AddressLine2, AddressLine3, City, StateProvinceRefID, ZipCode, CountryRefID,
CityLatitude, CityLongitude, ZipcodeLatitude, ZipcodeLongitude, AddressPhoneID,AddressFaxPhoneID,
CreatedDate, CreatedBy, UpdatedDate, UpdatedBy)

VALUES
(@AddressLine1,@AddressLine2,@AddressLine3,@City,@StateProvinceRefID,@ZipCode,@CountryRefID,
@CityLatitude,@CityLongitude,@ZipcodeLatitude,@ZipcodeLongitude)

SET @NewAddressID=SCOPE_IDENTITY();";

using (TransactionScope transactionScope = new TransactionScope())
{
DataGeneral.ExecuteNonQuery(sql, paramBuilder.Parameters, false);
transactionScope.Complete();
}
address.AddressID = (int) paramNewAddressId.Value;
}

2 comments:

Innovapath said...

Excellent Post. Also visit http://www.msnetframework.com/

otr214426 said...

INTERNATIONAL CONCEPT OF WORK FROM HOME
Work from home theory is fast gaining popularity because of the freedom and flexibility that comes with it. Since one is not bound by fixed working hours, they can schedule their work at the time when they feel most productive and convenient to them. Women & Men benefit a lot from this concept of work since they can balance their home and work perfectly. People mostly find that in this situation, their productivity is higher and stress levels lower. Those who like isolation and a tranquil work environment also tend to prefer this way of working. Today, with the kind of communication networks available, millions of people worldwide are considering this option.

Women & Men who want to be independent but cannot afford to leave their responsibilities at home aside will benefit a lot from this concept of work. It makes it easier to maintain a healthy balance between home and work. The family doesn't get neglected and you can get your work done too. You can thus effectively juggle home responsibilities with your career. Working from home is definitely a viable option but it also needs a lot of hard work and discipline. You have to make a time schedule for yourself and stick to it. There will be a time frame of course for any job you take up and you have to fulfill that project within that time frame.

There are many things that can be done working from home. A few of them is listed below that will give you a general idea about the benefits of this concept.

Baby-sitting
This is the most common and highly preferred job that Women & Men like doing. Since in today's competitive world both the parents have to work they need a secure place to leave behind their children who will take care of them and parents can also relax without being worried all the time. In this job you don't require any degree or qualifications. You only have to know how to take care of children. Parents are happy to pay handsome salary and you can also earn a lot without putting too much of an effort.

Nursery
For those who have a garden or an open space at your disposal and are also interested in gardening can go for this method of earning money. If given proper time and efforts nursery business can flourish very well and you will earn handsomely. But just as all jobs establishing it will be a bit difficult but the end results are outstanding.

Freelance
Freelance can be in different wings. Either you can be a freelance reporter or a freelance photographer. You can also do designing or be in the advertising field doing project on your own. Being independent and working independently will depend on your field of work and the availability of its worth in the market. If you like doing jewellery designing you can do that at home totally independently. You can also work on freelancing as a marketing executive working from home. Wanna know more, email us on workfromhome.otr214426@gmail.com and we will send you information on how you can actually work as a marketing freelancer.


Internet related work
This is a very vast field and here sky is the limit. All you need is a computer and Internet facility. Whatever field you are into work at home is perfect match in the software field. You can match your time according to your convenience and complete whatever projects you get. To learn more about how to work from home, contact us today on workfromhome.otr214426@gmail.comand our team will get you started on some excellent work from home projects.


Diet food
Since now a days Women & Men are more conscious of the food that they eat hence they prefer to have homemade low cal food and if you can start supplying low cal food to various offices then it will be a very good source of income and not too much of efforts. You can hire a few ladies who will help you out and this can be a good business.

Thus think over this concept and go ahead.