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;
}
}
