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");
}