public static Boolean AddParameter(SqlCommand parCMD, TTDictionary lstParamters, object objEntity)
{
Boolean blnResult = false;
//TTAttributs MyAttributs = (TTAttributs)obj.GetType().GetProperty(parPropertyName).GetCustomAttribute(typeof(TTAttributs), false);
for (int i = 0; i < lstParamters.Count(); i++)
{
var el = lstParamters.ElementAt(i);
TTAttributs MyAttributs = HelperMethod.GetTTAttributes(objEntity, el.Key);
object objValue = el.Value.Value1;
if (MyAttributs.ParamaterDataType == SqlDbType.NVarChar)
{
int parFieldWidth = ((MaxLengthAttribute)(objEntity.GetType().GetProperty(el.Key).GetCustomAttributes(typeof(MaxLengthAttribute), true)[0])).Length;
if (MyAttributs.isMemoField == false)
{
parCMD.Parameters.Add("@" + MyAttributs.FieldName, MyAttributs.ParamaterDataType, parFieldWidth).Value = objValue == null ? string.Empty : objValue;
}
else
{
parFieldWidth = objEntity.ToString().Length;
parCMD.Parameters.Add("@" + MyAttributs.FieldName, MyAttributs.ParamaterDataType, parFieldWidth).Value = objValue == null ? string.Empty : objValue;
}
}
else
{
parCMD.Parameters.Add("@" + MyAttributs.FieldName, MyAttributs.ParamaterDataType).Value = objValue;
}
}
return(blnResult);
}
public static StringBuilder GetParameter(TTDictionary FieldCollection, StringBuilder sb)
{
StringBuilder sbResult = new StringBuilder();
for (int i = 0; i < FieldCollection.Count(); i++)
{
var el = FieldCollection.ElementAt(i);
sbResult.Append(" " + el.Value.Value2 + " " + el.Key + " " + el.Value.value3 + " @" + el.Key);
}
return(sbResult);
}
private long PagerRecordCount(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity)
{
long lngResult = 0;
StringBuilder sbCount = new StringBuilder();
SqlCommand sqlCMDCount = new SqlCommand();
sqlCMDCount.Connection = Connection.GetConnection.GetDBConnection();
sbCount.Append("select total_count = COUNT(*) ").AppendLine();
sbCount.Append(SQLDictionery.TablePart).AppendLine();
if (parDictionery != null)
{
if (parDictionery.Count > 0)
{
sqlCMDCount.Parameters.Clear();
CommonMSSQL.AddParameter(sqlCMDCount, parDictionery, objEntity);
sbCount.Append(HelperMethod.GetParameter(parDictionery, sbCount)).AppendLine();
if (SQLDictionery.ParameterPart != null)
{
sbCount.Append(SQLDictionery.ParameterPart.Replace("WHERE", "AND"));
}
}
else
{
sbCount.Append(SQLDictionery.ParameterPart);
}
}
else
{
sbCount.Append(SQLDictionery.ParameterPart);
}
if (SQLDictionery.GroupPart != null)
{
sbCount.Append(SQLDictionery.GroupPart).AppendLine();
}
if (SQLDictionery.HavingPart != null)
{
sbCount.Append(SQLDictionery.HavingPart).AppendLine();
}
sqlCMDCount.CommandText = sbCount.ToString();
object objCount = sqlCMDCount.ExecuteScalar();
if (objCount != null)
{
lngResult = objCount == DBNull.Value ? 0 : Convert.ToInt64(objCount);
TTPagination.PageCount = (long)Math.Ceiling((double)lngResult / TTPagination.PageSize);
}
sqlCMDCount.Connection.Close();
sqlCMDCount.Dispose();
return(lngResult);
}
public DataTable GetDatasetByCommand(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity)
{
try
{
GetConnection();
Connection.GetConnection.OpenConnection(sqlCon);
sqlCMD.Parameters.Clear();
CommonMSSQL.AddParameter(sqlCMD, parDictionery, objEntity);
StringBuilder SQLQuery = new StringBuilder();
SQLQuery.Append(SQLDictionery.SelectPart).AppendLine();
SQLQuery.Append(SQLDictionery.TablePart).AppendLine();
if (parDictionery.Count > 0)
{
SQLQuery.Append(HelperMethod.GetParameter(parDictionery, SQLQuery)).AppendLine();
}
if (!string.IsNullOrEmpty(SQLDictionery.ParameterPart))
{
if (SQLDictionery.ParameterPart.Trim() != "")
{
SQLQuery.Append(SQLDictionery.ParameterPart).AppendLine();
}
}
if (SQLDictionery.GroupPart != null)
{
SQLQuery.Append(SQLDictionery.GroupPart).AppendLine();
}
if (SQLDictionery.HavingPart != null)
{
SQLQuery.Append(SQLDictionery.HavingPart).AppendLine();
}
if (TTPagination.isPageing)
{
if (TTPagination.PageSize != -1)
{
SQLQuery.Append(string.Format("{0} {1}", SQLDictionery.OrderPart, HelperMethod.GetPageingString(SQLDictionery.OrderPart, objEntity))).AppendLine();
}
}
else
{
if (SQLDictionery.OrderPart != null)
{
SQLQuery.Append(SQLDictionery.OrderPart).AppendLine();
}
}
sqlCMD.CommandType = CommandType.Text;
sqlCMD.CommandText = SQLQuery.ToString();
sqlCMD.CommandTimeout = 180;
//objSqlCommand.CommandText = Command;
//objSqlCommand.CommandTimeout = 60;
//objSqlCommand.CommandType = CommandType.StoredProcedure;
//mobj_SqlConnection.Open();
SqlDataAdapter adpt = new SqlDataAdapter(sqlCMD);
DataTable dt = new DataTable();
adpt.Fill(dt);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.GetConnection.CloseConnection(sqlCon);
}
}
public SqlDataReader ExecuteReaderQuery(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity)
{
SqlDataReader drResult = null;
try
{
GetConnection();
Connection.GetConnection.OpenConnection(sqlCon);
sqlCMD.Parameters.Clear();
CommonMSSQL.AddParameter(sqlCMD, parDictionery, objEntity);
StringBuilder SQLQuery = new StringBuilder();
SQLQuery.Append(SQLDictionery.SelectPart).AppendLine();
SQLQuery.Append(SQLDictionery.TablePart).AppendLine();
if (parDictionery.Count > 0)
{
SQLQuery.Append(HelperMethod.GetParameter(parDictionery, SQLQuery)).AppendLine();
}
if (!string.IsNullOrEmpty(SQLDictionery.ParameterPart))
{
if (SQLDictionery.ParameterPart.Trim() != "")
{
SQLQuery.Append(SQLDictionery.ParameterPart).AppendLine();
}
}
if (SQLDictionery.GroupPart != null)
{
SQLQuery.Append(SQLDictionery.GroupPart).AppendLine();
}
if (SQLDictionery.HavingPart != null)
{
SQLQuery.Append(SQLDictionery.HavingPart).AppendLine();
}
if (TTPagination.isPageing)
{
if (TTPagination.PageSize != -1)
{
SQLQuery.Append(string.Format("{0} {1}", SQLDictionery.OrderPart, HelperMethod.GetPageingString(SQLDictionery.OrderPart, objEntity))).AppendLine();
}
}
else
{
if (SQLDictionery.OrderPart != null)
{
SQLQuery.Append(SQLDictionery.OrderPart).AppendLine();
}
}
sqlCMD.CommandType = CommandType.Text;
sqlCMD.CommandText = SQLQuery.ToString();
SqlDataReader dr = sqlCMD.ExecuteReader();
if (TTPagination.isPageing)
{
TTPagination.RecordCount = PagerRecordCount(SQLDictionery, parDictionery, objEntity);
}
drResult = dr;
}
catch (Exception er)
{
throw;
}
finally
{
TTPagination.isPageing = false;
}
return(drResult);
}