internal void Build(IBaseBO bo, IQueryFormat qformat, IQueryAdds adds, QueryTypes query_type, ConnectionTypes conn_type)
{
try
{
string queryText = string.Empty;
queryText = qformat.Format;
queryText = queryText ?? string.Empty;
string table_name = string.Empty;
string columns = string.Empty;
string vals = string.Empty;
table_name = GetTableName(bo, adds);
columns = GetColumns(bo, adds, query_type, conn_type);
vals = GetVals(bo, adds, query_type, conn_type);
queryText = queryText.Replace("#TABLE_NAME#", table_name);
queryText = queryText.Replace("#COLUMNS#", columns);
queryText = queryText.Replace("#VALS#", vals);
query.Text = queryText;
List <DbParameter> parameters = GetParameters(bo, adds, query_type);
query.Parameters = parameters;
}
catch (Exception e)
{
throw;
}
}
internal static string GetColumns(IBaseBO bo, IQueryAdds adds, QueryTypes query_type, ConnectionTypes conn_type)
{
string cols = string.Empty;
try
{
List <string> colList = bo.GetColumnChangeList();
if (query_type == QueryTypes.Insert ||
query_type == QueryTypes.InsertAndGetId ||
query_type == QueryTypes.Update)
{
string id_col = bo.GetIdColumn();
colList.Remove(id_col);
}
if (query_type == QueryTypes.Insert || query_type == QueryTypes.InsertAndGetId ||
query_type == QueryTypes.InsertAnyChange || query_type == QueryTypes.SelectChangeColumns)
{
foreach (var col in colList)
{
cols = string.Concat(cols, ", ", col);
}
cols = cols.TrimStart(',').TrimStart();
// RETURN
return(cols);
}
if (query_type == QueryTypes.Update)
{
if (conn_type == ConnectionTypes.Odbc)
// || conn_type == ConnectionTypes.External)
{
foreach (var col in colList)
{
cols = string.Concat(cols, ", ", col, "=", adds.ParameterPrefix);
}
}
else
{
foreach (var col in colList)
{
cols = string.Concat(cols, ", ", col, "=", adds.ParameterPrefix, col);
}
}
cols = cols.TrimStart(',').TrimStart();
// RETURN
return(cols);
}
}
catch (Exception e)
{
throw;
}
return(cols);
}
public QueryBuilder(ConnectionTypes ConnType, QueryTypes queryType,
IBaseBO queryObject)
{
_baseBO = queryObject;
_queryType = queryType;
_ConnType = ConnType;
_queryString = GetQueryString();
_parameters = GetParameters();
}
/// <summary>
/// Create a QueryBuilder with given object and querytype and connetiontypes.
/// </summary>
/// <param name="ConnType">Connetion Types of Query Builder.</param>
/// <param name="queryType">Query Types of Query Builder.</param>
/// <param name="queryObject">An object inherits IBaseBO interface.</param>
public QueryBuilder(ConnectionTypes ConnType, QueryTypes queryType,
IBaseBO queryObject)
{
_baseBO = queryObject;
_queryType = queryType;
_ConnType = ConnType;
isNotEmptyChangeList = _baseBO.GetColumnChangeList().Count > 0;
_queryString = GetQueryString();
_parameters = GetParameters();
}
public static IQuery BuildInsertAndGetId(IBaseBO bo, ConnectionTypes conn_type)
{
IQuery q = null;
try
{
q = Build(bo, conn_type, QueryTypes.InsertAndGetId);
}
catch (Exception)
{
throw;
}
return(q);
}
public static IQuery BuildDelete(IBaseBO bo, ConnectionTypes conn_type)
{
IQuery q = null;
try
{
q = Build(bo, conn_type, QueryTypes.Delete);
}
catch (Exception)
{
throw;
}
return(q);
}
public int Delete(IBaseBO baseBO)
{
try
{
QueryBuilder queryBuilder =
CreateQueryBuilder(QueryTypes.Delete, baseBO);
return(ExecuteQuery(queryBuilder.QueryString,
queryBuilder.QueryParameters));
}
catch (Exception exc)
{
throw exc;
}
}
public int InsertAndGetId(IBaseBO baseBO)
{
try
{
QueryBuilder queryBuilder =
CreateQueryBuilder(QueryTypes.InsertAndGetId, baseBO);
return(Convert.ToInt32(ExecuteScalarQuery(queryBuilder.QueryString,
queryBuilder.QueryParameters)));
}
catch (Exception exc)
{
throw exc;
}
}
/// <summary>
/// Gets exceution result of Delete Operation.
/// </summary>
/// <param name="bo">BaseBO object.</param>
/// <returns>returns int</returns>
public virtual int Delete(IBaseBO bo)
{
int result = -100;
try
{
IQuery q = QueryBuilder.BuildDelete(bo, conn_type);
result = this.Execute(q.Text, CommandType.Text, q.Parameters);
}
catch (Exception)
{
throw;
}
return(result);
}
/// <summary>
/// Gets exceution result of Update Operation.
/// </summary>
/// <param name="bo">BaseBO object.</param>
/// <returns>returns int</returns>
public virtual int Update(IBaseBO bo)
{
int result = -100;
try
{
IQuery q = QueryBuilder.BuildUpdate(bo, this.ConnectionType);
result = this.Execute(q.Text, CommandType.Text, q.Parameters.GetParameters());
}
catch (Exception e)
{
throw;
}
return(result);
}
public static IQuery Build(IBaseBO bo, ConnectionTypes conn_type, QueryTypes query_type)
{
IQuery q = null;
try
{
IQueryFormat qformat = new QueryFormat(query_type);
IQueryAdds adds = new QueryAdds(conn_type);
q = Build(bo, qformat, adds, query_type, conn_type);
}
catch (Exception)
{
throw;
}
return(q);
}
/// <summary>
/// Gets Columns which baseBo object changelist contains.
/// Example: Column Change List Of BaseBO : Col1, Col2, Col3.
/// Query = Select * From TableOfBaseBO Where Col1=Col1Value And Col2=Col2Value And Col3=Col3Value;
/// </summary>
/// <param name="bo">IBaseBO object.</param>
/// <returns>Returns a System.Data.DataTable</returns>
public DataTable GetWhereChangeColumnList(IBaseBO bo)
{
DataTable dt = null;
try
{
IQuery q = QueryBuilder.Build(bo, conn_type, QueryTypes.SelectWhereChangeColumns);
DataSet ds = null;
ds = this.GetResultSet(q.Text, CommandType.Text, q.Parameters);
dt = ds.Tables[0];
}
catch (Exception)
{
throw;
}
return(dt);
}
/// <summary>
/// Gets One Record of BaseBO object if any object has BaseBO Id.
/// </summary>
/// <param name="bo">IBaseBO object.</param>
/// <returns>Returns a System.Data.DataTable</returns>
public DataTable GetById(IBaseBO bo)
{
DataTable dt = null;
try
{
IQuery q = QueryBuilder.Build(bo, this._Conn.ConnectionType, QueryTypes.SelectWhereId);
DataSet ds = this.GetResultSet(q.Text, CommandType.Text, q.Parameters);
dt = ds.Tables[0];
}
catch (Exception e)
{
throw;
}
return(dt);
}
internal string GetTableName(IBaseBO bo, IQueryAdds adds)
{
string tbl_name = string.Empty;
try
{
tbl_name = bo.GetTableName();
if (string.IsNullOrWhiteSpace(tbl_name))
{
throw new ArgumentException("Table Name can not be empty or white space");
}
tbl_name = string.Format("{0}{1}{2}", adds.Prefix, tbl_name, adds.Suffix);
}
catch (Exception e)
{
throw;
}
return(tbl_name);
}
internal static IQuery Build(IBaseBO bo, IQueryFormat qformat, IQueryAdds adds, QueryTypes query_type, ConnectionTypes conn_type)
{
IQuery q = null;
try
{
q = new Query();
string query = string.Empty;
query = qformat.Format;
query = query ?? string.Empty;
string table_name = "";
string columns = "";
string vals = "";
Hashmap h = null;
table_name = GetTableName(bo, adds);
columns = GetColumns(bo, adds, query_type, conn_type);
vals = GetVals(bo, adds, query_type, conn_type);
h = GetParameters(bo, adds, query_type);
query = query.Replace("#TABLE_NAME#", table_name);
query = query.Replace("#COLUMNS#", columns);
query = query.Replace("#VALS#", vals);
q.Text = query;
q.Parameters = h;
}
catch (Exception)
{
throw;
}
return(q);
}
/// <summary>
/// Create A QueryBuilder object with ConnectionType Of Connection and given querytype and AbstractBaseBo object.
/// </summary>
/// <param name="queryType">QueryType Of QueryBuilder</param>
/// <param name="tableObject">Table class object.</param>
/// <returns>Returns A QueryBuilder object with ConnectionType Of Connection and given querytype and AbstractBaseBo object.</returns>
public QueryBuilder CreateQueryBuilder(QueryTypes queryType, IBaseBO tableObject)
{
return(new QueryBuilder(_ConType, queryType, tableObject));
}
/// <summary>
/// Create a QueryBuilder with given object and querytype and SqlServer ConnetionTypes.
/// </summary>
/// <param name="queryType">Query Types of Query Builder.</param>
/// <param name="queryObject">An object inherits IBaseBO interface.</param>
public QueryBuilder(QueryTypes queryType, IBaseBO queryObject)
: this(ConnectionTypes.SqlServer, queryType, queryObject)
{
}
/// <summary>
/// Create a QueryBuilder with given object and SqlServer ConnetionTypes and Select QueryTypes.
/// </summary>
/// <param name="queryObject">An object inherits IBaseBO interface.</param>
public QueryBuilder(IBaseBO queryObject)
: this(ConnectionTypes.SqlServer, QueryTypes.Select, queryObject)
{
}
public BaseDL(IBaseBO basebo)
{
_basebo = basebo;
}
internal List <DbParameter> GetParameters(IBaseBO bo, IQueryAdds adds, QueryTypes query_type)
{
List <DbParameter> parameters = null;
DbParameter prm;
try
{
if (query_type != QueryTypes.Select ||
query_type != QueryTypes.SelectChangeColumns)
{
List <string> colList = bo.GetColumnChangeList();
string id_col;
PropertyInfo propInfo;
Type t = bo.GetType();
id_col = bo.GetIdColumn();
object val;
// IF BLOCK 1
if (query_type == QueryTypes.Delete || query_type == QueryTypes.SelectWhereId)
{
if (string.IsNullOrWhiteSpace(id_col))
{
throw new Exception("Id Column can not be empty or whit space.");
}
propInfo = t.GetProperty(id_col);
if (propInfo == null)
{
throw new Exception("Id Column Property has been specified as wrong. Please define right.");
}
val = propInfo.GetValue(bo, null);
if (val == null)
{
throw new Exception(string.Format("Id Column({0}) Value must be specified.", id_col));
}
parameters = new List <DbParameter>();
prm = this.CreateParameter();
prm.ParameterName = string.Format("{0}{1}", adds.ParameterPrefix, id_col);
prm.Value = val;
parameters.Add(prm);
return(parameters);
}
// IF BLOCK 2
if (query_type == QueryTypes.Insert || query_type == QueryTypes.InsertAndGetId)
{
colList.Remove(id_col);
}
parameters = new List <DbParameter>();
foreach (var col in colList)
{
propInfo = null;
propInfo = t.GetProperty(col);
if (propInfo == null)
{
throw new Exception(
string.Format("{0} Column Property has been specified as wrong. Please define right.", col));
}
val = propInfo.GetValue(bo, null);
prm = this.CreateParameter();
prm.ParameterName = string.Format("{0}{1}", adds.ParameterPrefix, col);
prm.Value = val;
parameters.Add(prm);
}
//RETURN
return(parameters);
}
}
catch (Exception e)
{
throw;
}
//RETURN
return(parameters);
}
public SqlQueryBuilder(IBaseBO businessObj, QueryTypes queryType, ConnectionTypes connType)
{
bo = businessObj;
query_type = queryType;
conn_type = connType;
}
internal string GetVals(IBaseBO bo, IQueryAdds adds, QueryTypes query_type, ConnectionTypes conn_type)
{
string vals = string.Empty;
try
{
List <string> colList = bo.GetColumnChangeList();
string id_col = string.Empty;
//IF BLOCK 1
if (query_type == QueryTypes.Insert || query_type == QueryTypes.InsertAndGetId ||
query_type == QueryTypes.InsertAnyChange)
{
if (query_type != QueryTypes.InsertAnyChange)
{
id_col = bo.GetIdColumn();
colList.Remove(id_col);
}
if (conn_type == ConnectionTypes.Odbc)
// || conn_type == ConnectionTypes.External)
{
foreach (var col in colList)
{
vals = string.Concat(vals, ", ", adds.ParameterPrefix);
}
}
else
{
foreach (var col in colList)
{
vals = string.Concat(vals, ", ", adds.ParameterPrefix, col);
}
}
vals = vals.TrimStart(',').TrimStart();
//RETURN
return(vals);
}
//IF BLOCK 2
if (query_type == QueryTypes.Delete || query_type == QueryTypes.SelectWhereId ||
query_type == QueryTypes.Update)
{
id_col = bo.GetIdColumn();
if (conn_type == ConnectionTypes.Odbc)
// || conn_type == ConnectionTypes.External)
{
vals = string.Format("{0}={1}", id_col, adds.ParameterPrefix);
}
else
{
vals = string.Format("{0}={1}{0}", id_col, adds.ParameterPrefix);
}
//RETURN
return(vals);
}
//IF BLOCK 3
if (query_type == QueryTypes.SelectWhereChangeColumns)
{
if (conn_type == ConnectionTypes.Odbc)
// || conn_type == ConnectionTypes.External)
{
foreach (var col in colList)
{
vals = string.Concat(vals, "AND ", col, "=", adds.ParameterPrefix);
}
}
else
{
foreach (var col in colList)
{
vals = string.Concat(vals, "AND ", col, "=", adds.ParameterPrefix, col);
}
}
if (vals.Length > 4)
{
vals = vals.Substring(4, vals.Length - 4);
}
//RETURN
return(vals);
}
}
catch (Exception e)
{
throw;
}
//RETURN
return(vals);
}
internal static Hashmap GetParameters(IBaseBO bo, IQueryAdds adds, QueryTypes query_type)
{
Hashmap h = null;
try
{
if (query_type != QueryTypes.Select || query_type != QueryTypes.SelectChangeColumns)
{
h = new Hashmap();
List <string> colList = bo.GetColumnChangeList();
string id_col;
PropertyInfo propInfo;
Type t = bo.GetType();
id_col = bo.GetIdColumn();
object val;
// IF BLOCK 1
if (query_type == QueryTypes.Delete || query_type == QueryTypes.SelectWhereId)
{
if (string.IsNullOrWhiteSpace(id_col))
{
throw new Exception("Id Column can not be empty or whit space.");
}
propInfo = t.GetProperty(id_col);
if (propInfo == null)
{
throw new Exception("Id Column Property has been specified as wrong. Please define right.");
}
val = propInfo.GetValue(bo, null);
if (val == null)
{
throw new Exception(string.Format("Id Column({0}) Value must be specified.", id_col));
}
h.Set(string.Format("{0}{1}", adds.ParameterPrefix, id_col), val);
//RETURN
return(h);
}
// IF BLOCK 2
if (query_type == QueryTypes.Insert || query_type == QueryTypes.InsertAndGetId)
{
colList.Remove(id_col);
}
foreach (var col in colList)
{
propInfo = null;
propInfo = t.GetProperty(col);
if (propInfo == null)
{
throw new Exception(string.Format("{0} Column Property has been specified as wrong. Please define right.", col));
}
val = propInfo.GetValue(bo, null);
h.Set(string.Format("{0}{1}", adds.ParameterPrefix, col), val);
}
//RETURN
return(h);
}
}
catch (Exception)
{
throw;
}
//RETURN
return(h);
}
