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);
}
private object FillEntityFromReader(SqlDataReader dr, object objEntity)
{
object objResult = null;
try
{
if (dr.HasRows)
{
while (dr.Read())
{
foreach (var prop in objEntity.GetType().GetProperties())
{
TTAttributs attr = HelperMethod.GetTTAttributes(objEntity, prop.Name);
if (attr.isTableField)
{
int ordinal = dr.GetOrdinal(attr.FieldName);
object objValue = dr.GetValue(ordinal);
if (objValue != System.DBNull.Value)
{
prop.SetValue(objEntity, objValue, null);
}
}
}
}
objResult = objEntity;
}
}
catch (Exception)
{
throw;
}
return(objResult);
}
public static Boolean AddParameter(SqlCommand parCMD, object obj, string parPropertyName)
{
Boolean blnResult = false;
//TTAttributs MyAttributs = (TTAttributs)obj.GetType().GetProperty(parPropertyName).GetCustomAttribute(typeof(TTAttributs), false);
TTAttributs MyAttributs = HelperMethod.GetTTAttributes(obj, parPropertyName);
object objValue = HelperMethod.GetPropValue(obj, parPropertyName);
if (MyAttributs.ParamaterDataType == SqlDbType.NVarChar)
{
int parFieldWidth = 50;
if (MyAttributs.isMemoField == false)
{
parFieldWidth = ((MaxLengthAttribute)(obj.GetType().GetProperty(parPropertyName).GetCustomAttributes(typeof(MaxLengthAttribute), true)[0])).Length;
parCMD.Parameters.Add("@" + MyAttributs.FieldName, MyAttributs.ParamaterDataType, parFieldWidth).Value = objValue == null ? string.Empty : objValue;
}
else
{
parFieldWidth = objValue == null ? 0 : objValue.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 object GetEnityByPrimaryKey(object objEntity)
{
object objResult = null;
try
{
GetConnection();
string strPrimaryKey = null;
if (sqlCMD.Connection.State == ConnectionState.Open)
{
if (sqlCMD.Connection.State == ConnectionState.Closed)
{
MySqlConnection.GetConnection.OpenConnection(sqlCon);
}
CommonMSSQL.ClearParameter(sqlCMD);
#region Get Primarykey Information from object
PropertyInfo[] properties = objEntity.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
var attribute = Attribute.GetCustomAttribute(property, typeof(KeyAttribute)) as KeyAttribute;
if (attribute != null) // This property has a KeyAttribute
{
// Do something, to read from the property:
//object val = property.GetValue(objPrimaryKey);
strPrimaryKey = ((TTAttributs)objEntity.GetType().GetProperty(property.Name).GetCustomAttribute(typeof(TTAttributs), false)).FieldName;
break;
}
}
#endregion
CommonMSSQL.AddParameter(sqlCMD, objEntity, strPrimaryKey);
sqlCMD.CommandType = CommandType.Text;
sqlCMD.CommandText = CommonMSSQL.PrepairSelectPrimaryKey(HelperMethod.GetTableName(objEntity).ToString(), objEntity);
using (SqlDataReader dr = sqlCMD.ExecuteReader())
{
objResult = FillEntityFromReader(dr, objEntity);
dr.Close();
}
}
}
catch (Exception)
{
objResult = null;
throw;
}
finally
{
MySqlConnection.GetConnection.CloseConnection(sqlCon);
}
return(objResult);
}
private long PagerRecordCount(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity)
{
long lngResult = 0;
StringBuilder sbCount = new StringBuilder();
SqlCommand sqlCMDCount = new SqlCommand();
sqlCMDCount.Connection = MySqlConnection.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 static String PrepairInsertQueryByEntity(object Entity, string parTableName)
{
string strQueryResult = "";
string strFields = "";;
string strValues = "";
List <string> fieldlist = new List <string>();
List <object> valuelist = new List <object>();
List <string> lstParam = new List <string>();
PropertyInfo[] properties = Entity.GetType().GetProperties();
for (int i = 0; i < properties.Count(); i++)
{
var el = properties.ElementAt(i);
if (el != null)
{
TTAttributs tt = HelperMethod.GetTTAttributes(Entity, el.Name);
if (tt.isTableField == true)
{
fieldlist.Add(tt.FieldName);
object objValue = HelperMethod.GetPropValue(Entity, tt.FieldName);
if (tt.ParamaterDataType == SqlDbType.NVarChar || tt.ParamaterDataType == SqlDbType.UniqueIdentifier || tt.ParamaterDataType == SqlDbType.DateTime || tt.ParamaterDataType == SqlDbType.Date)
{
valuelist.Add("'" + objValue + "'");
}
else if (tt.ParamaterDataType == SqlDbType.Bit)
{
valuelist.Add((bool)objValue ? 1 : 0);
}
else
{
valuelist.Add(objValue);
}
}
}
}
strFields = string.Join(",", fieldlist);
strValues = string.Join(",", valuelist);
strFields = "Insert Into " + parTableName + " ( " + strFields + " ) Values";
strValues = "(" + strValues + ")";
strQueryResult = strFields + strValues;
return(strQueryResult);
}
public static String PrepairSelectPrimaryKey(string parTableName, object objEntity)
{
string strQueryResult = "";
string strFields = "";;
string strParameter = "";
string strPrimaryKey = "";
#region Get Primarykey Information from object
PropertyInfo[] properties = objEntity.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
var attribute = Attribute.GetCustomAttribute(property, typeof(KeyAttribute)) as KeyAttribute;
if (attribute != null) // This property has a KeyAttribute
{
// Do something, to read from the property:
//object val = property.GetValue(objPrimaryKey);
strPrimaryKey = ((TTAttributs)objEntity.GetType().GetProperty(property.Name).GetCustomAttribute(typeof(TTAttributs), false)).FieldName;
break;
}
}
#endregion
properties = objEntity.GetType().GetProperties();
List <string> fieldlist = new List <string>();
for (int i = 0; i < properties.Count(); i++)
{
var el = properties.ElementAt(i);
if (el != null)
{
TTAttributs tt = HelperMethod.GetTTAttributes(objEntity, el.Name);
if (tt.isTableField == true)
{
fieldlist.Add(tt.FieldName);
}
}
}
strFields = string.Join(",", fieldlist);
strParameter = strPrimaryKey + "= @" + strPrimaryKey;
strFields = "Select " + strFields + " From " + parTableName;
strParameter = " Where " + strParameter;
strQueryResult = strFields + strParameter;
return(strQueryResult);
}
public static String PrepairSelectPerameters(Dictionary <string, Enumration.Operators> parFields, string parTableName, object objEntity)
{
string strQueryResult = "";
string strFields = "";
string strParameter = "";
List <string> fieldlist = new List <string>();
List <string> lstParam = new List <string>();
PropertyInfo[] properties = objEntity.GetType().GetProperties();
for (int i = 0; i < properties.Count(); i++)
{
var el = properties.ElementAt(i);
if (el != null)
{
TTAttributs tt = HelperMethod.GetTTAttributes(objEntity, el.Name);
if (tt.isTableField == true)
{
fieldlist.Add(tt.FieldName);
}
}
}
if (parFields.Count > 0)
{
for (int i = 0; i < parFields.Count; i++)
{
var el = parFields.ElementAt(i);
lstParam.Add(el.Value.ToString() + " " + el.Key + "=@" + el.Key);
}
}
strFields = string.Join(",", fieldlist);
strParameter = string.Join(" ", lstParam);
strFields = "Select " + strFields + " From " + parTableName + " ";
strQueryResult = strFields + strParameter;
return(strQueryResult);
}
public static String PrepairPartialDeleteQuery(Dictionary <string, bool> parFields, string parTableName, object objEntity)
{
string strQueryResult = "";
string strFields = "";;
string strParameter = "";
Dictionary <string, bool> parm = parFields.Where(x => x.Value == true).ToDictionary(x => x.Key, x => x.Value);
if (parm.Count > 1)
{
for (int i = 0; i < parm.Count; i++)
{
var element = parm.ElementAt(i);
TTAttributs MyAttributs = HelperMethod.GetTTAttributes(objEntity, element.Key);
if (element.Value == true)
{
if (i != parm.Count - 1)
{
strParameter += MyAttributs.FieldName + "= @" + MyAttributs.FieldName + " AND ";
}
else
{
strParameter += MyAttributs.FieldName + "= @" + MyAttributs.FieldName;
}
}
}
}
else
{
var element = parm.ElementAt(0);
TTAttributs MyAttributs = HelperMethod.GetTTAttributes(objEntity, element.Key);
strParameter = MyAttributs.FieldName + "= @" + MyAttributs.FieldName;
}
strFields = "Delete " + parTableName;
strParameter = " Where " + strParameter;
strQueryResult = strFields + strParameter;
return(strQueryResult);
}
public static List <TEntity> CopyDataReaderToEntity <TEntity>(IDataReader dataReader) where TEntity : class
{
List <TEntity> entities = new List <TEntity>();
PropertyInfo[] properties = typeof(TEntity).GetProperties();
while (dataReader.Read())
{
TEntity tempEntity = Activator.CreateInstance <TEntity>();
foreach (PropertyInfo property in properties)
{
try
{
TTAttributs MyAttributs = HelperMethod.GetTTAttributes(tempEntity, property.Name);
if (MyAttributs.isSelectField)
{
SetValue <TEntity>(property, tempEntity, dataReader[property.Name]);
}
}
catch (Exception ex) { }
}
entities.Add(tempEntity);
}
return(entities);
}
public SqlDataReader ExecuteReaderQuery(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity)
{
SqlDataReader drResult = null;
try
{
GetConnection();
MySqlConnection.GetConnection.OpenConnection(sqlCon);
sqlCMD.Parameters.Clear();
if (objEntity != null)
{
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();
TTPagination.RecordCount = PagerRecordCount(SQLDictionery, parDictionery, objEntity);
drResult = dr;
}
catch (Exception)
{
throw;
}
finally
{
TTPagination.isPageing = false;
}
return(drResult);
}
public SqlDataReader ExecuteReaderQuery(TTDictionaryQuery SQLDictionery, object objEntity)
{
SqlDataReader drResult = null;
try
{
GetConnection();
if (!MySqlConnection.GetConnection.isConnectionOpen)
{
sqlCon.Open();
}
sqlCMD.Parameters.Clear();
StringBuilder SQLQuery = new StringBuilder();
SQLQuery.Append(SQLDictionery.SelectPart).AppendLine();
SQLQuery.Append(SQLDictionery.TablePart).AppendLine();
if (SQLDictionery.GroupPart.Trim() != "")
{
SQLQuery.Append(SQLDictionery.GroupPart).AppendLine();
}
if (SQLDictionery.HavingPart.Trim() != "")
{
SQLQuery.Append(SQLDictionery.HavingPart).AppendLine();
}
if (TTPagination.isPageing)
{
SQLQuery.Append(string.Format("{0} {1}", SQLDictionery.OrderPart, HelperMethod.GetPageingString(SQLDictionery.OrderPart, objEntity))).AppendLine();
}
else
{
SQLQuery.Append(SQLDictionery.OrderPart).AppendLine();
}
sqlCMD.CommandType = CommandType.Text;
sqlCMD.CommandText = SQLQuery.ToString();
SqlDataReader dr = sqlCMD.ExecuteReader();
if (dr.HasRows)
{
drResult = dr;
TTPagination.RecordCount = PagerRecordCount(SQLDictionery, null, objEntity);
}
}
catch (Exception)
{
throw;
}
finally
{
MySqlConnection.GetConnection.CloseConnection(sqlCon);
}
return(drResult);
}
public object GetEnityByPerameters(object objEntity, Dictionary <string, Enumration.Operators> parFields)
{
object objResult = null;
try
{
GetConnection();
if (sqlCMD.Connection.State == ConnectionState.Open)
{
if (sqlCMD.Connection.State == ConnectionState.Closed)
{
MySqlConnection.GetConnection.OpenConnection(sqlCon);
}
CommonMSSQL.ClearParameter(sqlCMD);
for (int i = 0; i < parFields.Count; i++)
{
var el = parFields.ElementAt(i);
CommonMSSQL.AddParameter(sqlCMD, objEntity, el.Key);
}
sqlCMD.CommandType = CommandType.Text;
sqlCMD.CommandText = CommonMSSQL.PrepairSelectPerameters(parFields, HelperMethod.GetTableName(objEntity), objEntity);
using (SqlDataReader dr = sqlCMD.ExecuteReader())
{
objResult = FillEntityFromReader(dr, objEntity);
dr.Close();
}
}
}
catch (Exception)
{
objResult = null;
throw;
}
finally
{
MySqlConnection.GetConnection.CloseConnection(sqlCon);
}
return(objResult);
}
