Saturday, September 30, 2006

Enterprise Application Data Block Version 2.0

Since I really missed the convenience of version 1.0 of the enterprise application data block and wanted to reduce redundant code while making calls to the database I came up with a helper class much like SqlHelper class in the data block one.

Usage :


DBHelper dhp = new DBHelper();
DBParameter[] para = { new DBParameter("param_name", DbType.String, param_Value) };

return dhp.Query(@"Sql_query", para);


You will need to add the following classes to your project

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

///
/// Summary description for DBHelper
///

public class DBHelper
{

Database burstdb;


public DBHelper()
{
//
// TODO: Add constructor logic here
//

burstdb = DatabaseFactory.CreateDatabase();
}

public DataTable Query(string sqlText, DBParameter[] param)
{
DbCommand command = burstdb.GetSqlStringCommand(sqlText);
for (int i = 0; i < param.Length; i++)
{
burstdb.AddInParameter(command, param[i].Name,param[i].Type,param[i].DBValue);
}
burstdb.ExecuteDataSet(command);
DataSet ds = new DataSet();
burstdb.LoadDataSet(command, ds, "ds");
return ds.Tables[0];
}

public DataTable QuerySP(string spName, DBParameter[] param)
{
DbCommand command = burstdb.GetStoredProcCommand(spName);
for (int i = 0; i < param.Length; i++)
{
burstdb.AddInParameter(command, param[i].Name, param[i].Type, param[i].DBValue);
}
burstdb.ExecuteDataSet(command);
DataSet ds = new DataSet();
burstdb.LoadDataSet(command, ds, "ds");
return ds.Tables[0];
}

public void nonQuery(string sqlText, DBParameter[] param)
{
DbCommand command = burstdb.GetSqlStringCommand(sqlText);
for (int i = 0; i < param.Length; i++)
{
burstdb.AddInParameter(command, param[i].Name, param[i].Type, param[i].DBValue);
}
burstdb.ExecuteNonQuery(command);
}

public void nonQuerySP(string spName, DBParameter[] param)
{
DbCommand command = burstdb.GetStoredProcCommand(spName);
for (int i = 0; i < param.Length; i++)
{
burstdb.AddInParameter(command, param[i].Name, param[i].Type, param[i].DBValue);
}
burstdb.ExecuteNonQuery(command);
}


}




using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///
/// Summary description for DBParameter
///

public class DBParameter
{
string _name;
DbType _dbtype;
object _value;

public string Name
{
get { return _name; }
set { _name = value; }
}

public DbType Type
{
get { return _dbtype;}
set {_dbtype = value;}
}

public object DBValue
{
get { return _value; }
set { _value = value; }
}

public DBParameter(string name, DbType type,object value)
{
Name = name;
Type = type;
DBValue = value;
}


}

2 comments:

Syed said...

How can I assign the connection string in Enterprise Application Data Block Version 2.0?.

codemister said...

Not sure if you want to assign the connection string programmatically or manually in the web config

To do so manually add a connection string in your web.config and use the connection name E.g LocalSqlServer as the DBname in your DAAB code

see here on how to create a connection string in your web.config

http://weblogs.asp.net/owscott/archive/2005/08/26/using-connection-strings-from-web.config-in-asp.net-v2.0.aspx