예제 #1
0
        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 && (propertyInfo.GetValue(obj, null).ToString() != "" && propertyInfo.GetValue(obj, null).ToString() != "0" && 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;
                }
                else if (dbField != null && (propertyInfo.GetValue(obj, null).ToString() != "" && propertyInfo.GetValue(obj, null).ToString() != "0" && 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;
                }
            }
            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();
                }
            }

            return((success == 1) ? "Ok - Query :" + query : "Error - Query :" + query);
        }
예제 #2
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rdr"></param>
        /// <returns></returns>
        public T GetItemFromReader <T>(OdbcDataReader rdr) where T : class
        {
            Type type = typeof(T);
            T    item = Activator.CreateInstance <T>();

            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);
        }
예제 #3
0
        public string Insert(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;
                }

                decimal recCount = 0;
                decimal pkId     = 0;
                try
                {
                    dbConnection.cmd.CommandText = "Select COUNT(*) from " + library + "." + fileName;
                    recCount = decimal.Parse(dbConnection.cmd.ExecuteScalar().ToString());
                }
                catch (Exception ex)
                {
                }

                if (primary != null && dbField != null & recCount > 0)
                {
                    try
                    {
                        dbConnection.cmd.CommandText = "select max(" + dbField.FieldName + ")+1 from " + library + "." + fileName;
                        pkId = decimal.Parse(dbConnection.cmd.ExecuteScalar().ToString());
                    }
                    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)
                {
                    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 + "." + 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();
                }
            }

            return((success == 1) ? "Ok  - Query:" + query : "Error - Query:" + query);
        }
예제 #4
0
        public List <T> Select <T>(string fileName, Object obj) where T : class
        {
            string           select           = "SELECT * FROM " + library + "." + fileName;
            string           whereCondition   = "";
            string           query            = "";
            Type             firstType        = obj.GetType();
            List <T>         list             = new List <T>();
            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 <T>(dbConnection.dr);
                }
            }
            catch (Exception ex)
            {
                if (dbConnection.con.State == System.Data.ConnectionState.Open)
                {
                    dbConnection.RollBack();
                }
                return(new List <T>());
            }
            finally
            {
                if (dbConnection.con.State == System.Data.ConnectionState.Open)
                {
                    dbConnection.Commit();
                }
            }

            return(list);
        }
예제 #5
0
        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();
                }
            }

            return("Ok - Query :" + query);
        }