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, 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 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 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, MyEnumration.Operation> 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(); 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.CommandTimeout = 0; 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, MyEnumration.Operation> 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); }