public string InsertDefinition(string fileName, Object obj) { string query = "INSERT INTO " + library + "." + fileName + " "; string fields = "( "; string values = " VALUES ( "; Type firstType = obj.GetType(); foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { // populating firld name list DBFieldAttribute dbField = null; PrimaryKeyAttribute primary = null; object[] attrs = propertyInfo.GetCustomAttributes(true); foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; primary = (attr as PrimaryKeyAttribute) != null ? attr as PrimaryKeyAttribute : primary; } string value = (propertyInfo.PropertyType == typeof(decimal)) ? propertyInfo.GetValue(obj, null).ToString() : "'" + propertyInfo.GetValue(obj, null) + "'"; if (dbField != null) { fields = fields == "( " ? (fields + dbField.FieldName) : (fields + "," + dbField.FieldName); values = values == " VALUES ( " ? values + value : values + " , " + value; } } query = "INSERT INTO " + library + "." + fileName + " " + fields + " ) " + values + " )"; int success = -1; try { dbConnection.cmd.CommandText = query; dbConnection.cmd.CommandType = System.Data.CommandType.Text; success = dbConnection.cmd.ExecuteNonQuery(); } catch (Exception ex) { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.RollBack(); } return(ex.Message + " - Query :" + query); } finally { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.Commit(); } } utils.Logger(query); return((success == 1) ? "1" : "Error - Query:" + query); }
public string Delete(string fileName, Object obj) { string delete = "DELETE FROM " + library + "." + fileName + " WHERE "; string whereCondition = ""; string query = ""; Type firstType = obj.GetType(); foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { object[] attrs = propertyInfo.GetCustomAttributes(true); DBFieldAttribute dbField = null; PrimaryKeyAttribute pkField = null; foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; pkField = (attr as PrimaryKeyAttribute) != null ? attr as PrimaryKeyAttribute : pkField; } if (dbField != null && pkField != null) { string value = propertyInfo.PropertyType == typeof(string) ? "'" + propertyInfo.GetValue(obj, null).ToString() + "'" : propertyInfo.GetValue(obj, null).ToString(); string condition = dbField.FieldName + "=" + value; condition = whereCondition == "" ? " " + condition : " AND " + condition; whereCondition += condition; } } query = delete + whereCondition; int success = -1; try { dbConnection.cmd.CommandText = query; dbConnection.cmd.CommandType = System.Data.CommandType.Text; success = dbConnection.cmd.ExecuteNonQuery(); } catch (Exception ex) { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.RollBack(); } return(ex.Message + " - Query :" + query); } finally { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.Commit(); } } utils.Logger(query); return((success >= 1) ? "1" : "Error - Query :" + query); }
/// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="rdr"></param> /// <returns></returns> public object GetItemFromReader(OdbcDataReader rdr, object item) { Type type = item.GetType(); PropertyInfo[] properties = type.GetProperties(); foreach (PropertyInfo property in properties) { Type type1 = property.ReflectedType; // for each property declared in the type provided check if the property is // decorated with the DBField attribute if (Attribute.IsDefined(property, typeof(DBFieldAttribute))) { DBFieldAttribute attrib = (DBFieldAttribute)Attribute.GetCustomAttribute(property, typeof(DBFieldAttribute)); try { if (Convert.IsDBNull(rdr[attrib.FieldName])) // data in database is null, so do not set the value of the property { continue; } } catch (Exception) { continue; } if (property.PropertyType == rdr[attrib.FieldName].GetType()) // if the property and database field are the same { property.SetValue(item, rdr[attrib.FieldName], null); // set the value of property } else { try { property.SetValue(item, Convert.ChangeType(rdr[attrib.FieldName], property.PropertyType), null); } catch (Exception) { property.SetValue(item, rdr[attrib.FieldName].ToString().Trim(), null); } // change the type of the data in table to that of property and set the value of the property // for SQL attrib.FieldName canbe modified as ( "@" +attrib.FieldName) } } } return(item); }
private void Init(IDataReader reader) { if (_Initialized) { return; } // Get Reader Column names ordered by ordinal string[] _readerFieldNames = DataUtils.GetReaderColumns(reader); // Create property and Attribute dictionaries var _attributes = new Dictionary <string, DBFieldAttribute>(); var _properties = new Dictionary <string, PropertyInfo>(); foreach (PropertyInfo property in typeof(TRESULTOBJECT).GetProperties()) { DBFieldAttribute _attribute = property.GetCustomAttribute <DBFieldAttribute>(false); if (_attribute != null) { string _key = _attribute.Field.ToLower(); _attributes.Add(_key, _attribute); _properties.Add(_key, property); } } // Validate fieldsnames foreach (string column in _readerFieldNames) { if (!_properties.ContainsKey(column)) { string message = string.Format("Column '{0}' does not exist in the result DataSet.", column); throw new DataException(message); } } // Convert Property / Attribute Dictionaries to Ordinal Array. _Attributes = new DBFieldAttribute[_readerFieldNames.Length]; _Properties = new PropertyInfo[_readerFieldNames.Length]; for (int index = 0; index < _readerFieldNames.Length; index++) { string _name = _readerFieldNames[index]; _Attributes[index] = _attributes[_name]; _Properties[index] = _properties[_name]; } _Initialized = true; }
public List <T> GetAll <T>() where T : class { string fileName = GetAttribute(typeof(T)); string url = serviceUrl + "SelectAll?fileName=" + fileName; List <T> tempList = new List <T>(); List <T> result = new List <T>(); try { request = WebRequest.Create(url); request.Headers.Add("SecretKey:" + secretKey); ws = request.GetResponse(); response = (Response)jsonSerializer.ReadObject(ws.GetResponseStream()); tempList = javaScriptSerializer.Deserialize <List <T> >(response.Data); } catch (Exception e) { BusinessLogics.LogError("File Name - " + fileName + " " + e.Message); } foreach (T obj in tempList) { Type firstType = obj.GetType(); foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { // populating firld name list DBFieldAttribute dbField = null; object[] attrs = propertyInfo.GetCustomAttributes(true); foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; } if (dbField != null && (propertyInfo.PropertyType == typeof(string)) && propertyInfo.GetValue(obj, null) != null) { propertyInfo.SetValue(obj, propertyInfo.GetValue(obj, null).ToString().Trim(), null); } } result.Add(obj); } return(result); }
private static bool InsertOrUpdateObject <T>(T EntityInstance, string ProcedureName, string PrimaryKey, bool IsInserting) where T : class { bool bSuccess = false; try { using (IDbConnection oConnection = new SqlConnection(ConfigManager.ConnectionString)) { Type oCustomType = typeof(T); object objPropertyValue = null; PropertyInfo[] arrProperties = oCustomType.GetProperties(); PropertyInfo oPrimaryKeyInfo = null; var spPARAM = new DynamicParameters(); foreach (PropertyInfo oInfo in arrProperties) { if (Attribute.IsDefined(oInfo, typeof(DBFieldAttribute))) { DBFieldAttribute oAttribute = (DBFieldAttribute)Attribute.GetCustomAttribute(oInfo, typeof(DBFieldAttribute)); if (oAttribute.IsDatabaseField) { if (!(IsInserting && (oAttribute.IsPrimaryKey || oInfo.Name == PrimaryKey))) { objPropertyValue = oInfo.GetValue(EntityInstance, null); //if (objPropertyValue == null || DateTime.MinValue.Equals(objPropertyValue)) // spPARAM.Add("@" + oAttribute.ColumnName, DBNull.Value); //else // spPARAM.Add("@" + oAttribute.ColumnName, objPropertyValue); if (objPropertyValue != null && !DateTime.MinValue.Equals(objPropertyValue)) { spPARAM.Add("@" + oAttribute.ColumnName, objPropertyValue); } } else { oPrimaryKeyInfo = oInfo; } } } } if (oConnection.State == ConnectionState.Closed) { oConnection.Open(); } var result = oConnection.Execute(ProcedureName, spPARAM, commandType: CommandType.StoredProcedure); oConnection.Close(); } bSuccess = true; } catch (Exception ex) { LogManager.LogManagerStatic().LogError(ex.Message); } return(bSuccess); }
private static bool DeleteObject <T>(string ProcedureName, string PrimaryKey, int ID, SqlParameter[] Parameters = null) where T : class { bool bSuccess = false; try { using (IDbConnection oConnection = new SqlConnection(ConfigManager.ConnectionString)) { var spPARAM = new DynamicParameters(); Type oCustomType = typeof(T); if (Parameters != null) { foreach (SqlParameter oParameter in Parameters) { spPARAM.Add("@" + oParameter.ParameterName, oParameter.Value); } } else if (!string.IsNullOrEmpty(PrimaryKey)) { spPARAM.Add("@" + PrimaryKey, ID); } else { foreach (PropertyInfo oInfo in oCustomType.GetProperties()) { if (Attribute.IsDefined(oInfo, typeof(DBFieldAttribute))) { DBFieldAttribute oAttribute = (DBFieldAttribute)Attribute.GetCustomAttribute(oInfo, typeof(DBFieldAttribute)); if (oAttribute.IsPrimaryKey) { spPARAM.Add("@" + oInfo.Name, ID); break; } } } } if (oConnection.State == ConnectionState.Closed) { oConnection.Open(); } var result = oConnection.Execute(ProcedureName, spPARAM, commandType: CommandType.StoredProcedure); oConnection.Close(); bSuccess = true; } } catch (Exception ex) { LogManager.LogManagerStatic().LogError(ex.Message); } return(bSuccess); }
public string Insert(string fileName, Object obj) { string query = "INSERT INTO " + library + "." + fileName + " "; string fields = "( "; string values = " VALUES ( "; string primaryKeyId = "ID"; Type firstType = obj.GetType(); decimal pkId = 0; foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { // populating firld name list DBFieldAttribute dbField = null; PrimaryKeyAttribute primary = null; object[] attrs = propertyInfo.GetCustomAttributes(true); foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; primary = (attr as PrimaryKeyAttribute) != null ? attr as PrimaryKeyAttribute : primary; } decimal recCount; try { dbConnection.cmd.CommandText = "SELECT COUNT(*) FROM " + library + "." + fileName; recCount = decimal.Parse(dbConnection.cmd.ExecuteScalar().ToString()); } catch (Exception ex) { recCount = 0; } if (primary != null && dbField != null & recCount > 0) { try { dbConnection.cmd.CommandText = "SELECT MAX(cast(" + dbField.FieldName + " as int)) FROM " + library + "." + fileName; pkId = decimal.Parse(dbConnection.cmd.ExecuteScalar().ToString()); pkId++; } catch (Exception ex) { pkId = 0; } } string value = (propertyInfo.PropertyType == typeof(decimal)) ? propertyInfo.GetValue(obj, null).ToString() : "'" + propertyInfo.GetValue(obj, null) + "'"; if (dbField != null & primary == null && value != "0" && value != "" && value != "''") { fields = fields == "( " ? (fields + dbField.FieldName) : (fields + "," + dbField.FieldName); values = values == " VALUES ( " ? values + value : values + " , " + value; } else if (dbField != null & primary != null) { primaryKeyId = dbField.FieldName; fields = fields == "( " ? (fields + dbField.FieldName) : (fields + "," + dbField.FieldName); string primaryKey = (propertyInfo.PropertyType == typeof(decimal)) ? pkId.ToString() : "'" + pkId.ToString() + "'"; values = values == " VALUES ( " ? values + primaryKey : values + " , " + primaryKey; } } query = "INSERT INTO [" + library + "].[dbo].[" + fileName + "] " + fields + " ) " + values + " )"; int success = -1; try { dbConnection.cmd.CommandText = query; dbConnection.cmd.CommandType = System.Data.CommandType.Text; success = dbConnection.cmd.ExecuteNonQuery(); } catch (Exception ex) { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.RollBack(); } return(ex.Message + " - Query :" + query); } finally { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.Commit(); } } utils.Logger(query); return((success == 1) ? pkId.ToString() : "-1"); }
public object SelectLast(string fileName, Object obj) { string select = "SELECT * FROM " + library + "." + fileName + " WHERE "; string castPk = ""; string whereCondition = ""; string query = ""; Type firstType = obj.GetType(); object result = new object(); DataAccessObject dataAccessObject = new DataAccessObject(); foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { object[] attrs = propertyInfo.GetCustomAttributes(true); DBFieldAttribute dbField = null; PrimaryKeyAttribute pkField = null; foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; pkField = (attr as PrimaryKeyAttribute) != null ? attr as PrimaryKeyAttribute: pkField; } if (dbField != null && (propertyInfo.GetValue(obj, null).ToString() != "" && propertyInfo.GetValue(obj, null).ToString() != "0")) { string value = propertyInfo.PropertyType == typeof(string) ? "'" + propertyInfo.GetValue(obj, null).ToString() + "'" : propertyInfo.GetValue(obj, null).ToString(); string condition = dbField.FieldName + "=" + value; condition = whereCondition == "" ? " " + condition : " AND " + condition; whereCondition += condition; } if (pkField != null) { castPk = " CAST(" + dbField.FieldName + " AS INT) "; } } whereCondition = whereCondition != "" ? " WHERE " + whereCondition : whereCondition; query = " ( SELECT MAX(" + castPk + ") FROM " + library + "." + fileName + " " + whereCondition + ") "; query = "SELECT * FROM " + library + "." + fileName + " WHERE " + castPk + "=" + query; dbConnection.cmd.CommandText = query; dbConnection.cmd.CommandType = System.Data.CommandType.Text; try { using (dbConnection.dr = dbConnection.cmd.ExecuteReader()) { result = dataAccessObject.GetSingleOject(dbConnection.dr, obj); } } catch (Exception ex) { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.RollBack(); } return(result); } finally { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.Commit(); } } return(result); }
public List <object> Select(string fileName, Object obj) { string select = "SELECT * FROM \"" + library + "\".\"" + fileName + '"'; string whereCondition = ""; string query = ""; Type firstType = obj.GetType(); List <object> list = new List <object>(); DataAccessObject dataAccessObject = new DataAccessObject(); foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { object[] attrs = propertyInfo.GetCustomAttributes(true); DBFieldAttribute dbField = null; PrimaryKeyAttribute pkField = null; foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; } if (dbField != null && (propertyInfo.GetValue(obj, null).ToString() != "" && propertyInfo.GetValue(obj, null).ToString() != "0")) { string value = propertyInfo.PropertyType == typeof(string) ? "'" + propertyInfo.GetValue(obj, null).ToString() + "'" : propertyInfo.GetValue(obj, null).ToString(); string condition = '"' + dbField.FieldName + "\"=" + value + ""; condition = whereCondition == "" ? " " + condition : " AND " + condition; whereCondition += condition; } else if (dbField != null && (propertyInfo.GetValue(obj, null).ToString() != "" && pkField != null)) { string value = propertyInfo.PropertyType == typeof(string) ? "'" + propertyInfo.GetValue(obj, null).ToString() + "'" : propertyInfo.GetValue(obj, null).ToString(); string condition = dbField.FieldName + "=" + value; condition = whereCondition == "" ? " " + condition : " AND " + condition; whereCondition += condition; } } whereCondition = whereCondition != "" ? " WHERE " + whereCondition : whereCondition; query = select + whereCondition; dbConnection.cmd.CommandText = query; dbConnection.cmd.CommandType = System.Data.CommandType.Text; try { using (dbConnection.dr = dbConnection.cmd.ExecuteReader()) { list = dataAccessObject.ReadCollection(dbConnection.dr, obj); } } catch (Exception ex) { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.RollBack(); } return(new List <object>()); } finally { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.Commit(); } } return(list); }
public string UpdateAllFields(string fileName, Object obj) { string update = "UPDATE " + library + "." + fileName; string setValues = ""; string whereCondition = ""; string query = ""; Type firstType = obj.GetType(); foreach (PropertyInfo propertyInfo in firstType.GetProperties()) { // populating firld name list DBFieldAttribute dbField = null; PrimaryKeyAttribute primaryKey = null; string value = (propertyInfo.PropertyType == typeof(decimal)) ? propertyInfo.GetValue(obj, null).ToString() : "'" + propertyInfo.GetValue(obj, null) + "'"; // Go through each attribute of the property object[] attrs = propertyInfo.GetCustomAttributes(true); foreach (object attr in attrs) { dbField = (attr as DBFieldAttribute) != null ? attr as DBFieldAttribute : dbField; primaryKey = (attr as PrimaryKeyAttribute) != null ? attr as PrimaryKeyAttribute : primaryKey; } if (primaryKey != null && primaryKey.IsPrimary) { whereCondition = dbField.FieldName + "=" + value; continue; } if (dbField != null) { string updateValue = dbField.FieldName + "=" + value; updateValue = setValues == "" ? updateValue : " , " + updateValue; setValues += updateValue; } } setValues = setValues != "" ? " SET " + setValues : setValues; whereCondition = whereCondition != "" ? " WHERE " + whereCondition : whereCondition; query = update + setValues + whereCondition; dbConnection.cmd.CommandText = query; dbConnection.cmd.CommandType = System.Data.CommandType.Text; int success = -1; try { success = dbConnection.cmd.ExecuteNonQuery(); } catch (Exception ex) { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.RollBack(); } return(ex.Message + " - Query :" + query); } finally { if (dbConnection.con.State == System.Data.ConnectionState.Open) { dbConnection.Commit(); } } utils.Logger(query); return("1"); }