예제 #1
0
        /// <summary>
        /// Inserts the row.
        /// </summary>
        /// <param name="insertObj">The insert object.</param>
        /// <returns></returns>
        public int InsertRow(object obj)
        {
            int          result       = 0;
            InsertObject insertObject = null;
            string       sqlInsert    = null;

            try
            {
                insertObject = InsertObject.CreateInstance(obj);
                sqlInsert    = string.Format(StringFormat.SqlInsert, insertObject.Table, insertObject.Columns, insertObject.Values);

                result = SQLiteQuery.ExecuteNonQuery(sqlInsert, this.Connection);

                if (result > 0)
                {
                    this.HandleByteArrayList(insertObject.Table, insertObject.ByteArrayColumns, insertObject.ByteArrayValues, insertObject.KeyString);
                }
            }
            catch (Exception exception)
            {
                APILog.Error(this, "InsertRow", exception);
            }
            finally
            {
                insertObject = null;
                sqlInsert    = null;
            }

            return(result);
        }
예제 #2
0
        /// <summary>
        /// Gets the rows total.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereClause">The where clause.</param>
        /// <returns></returns>
        public int GetRowsTotal <T>(string whereClause = "") where T : new()
        {
            int returnValue         = 0;
            SQLiteDataReader reader = null;

            try
            {
                var objectType = typeof(T);
                var properties = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                string sqlQuery = SQLiteQuery.CountTotal(objectType.Name, whereClause);
                reader = SQLiteQuery.ExecuteReader(sqlQuery, this.Connection);

                while (reader.Read())
                {
                    returnValue = Convert.ToInt32(reader[Constant.TotalCount]);
                }
            }
            catch (Exception exception)
            {
                APILog.Error(this, "GetRowsTotal", exception);
            }
            finally
            {
                reader.Close();
                reader = null;
            }

            return(returnValue);
        }
예제 #3
0
        /// <summary>
        /// Gets the column maximum value.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns></returns>
        public object GetColumnMaxValue <T>(string columnName)
        {
            object           returnValue = null;
            SQLiteDataReader reader      = null;

            try
            {
                Type   type         = typeof(T);
                var    propertyList = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
                string sqlQuery     = SQLiteQuery.MaxFromTable(type.Name, columnName);
                reader = SQLiteQuery.ExecuteReader(sqlQuery, this.Connection);

                while (reader.Read())
                {
                    returnValue = reader[Constant.MaxValue].ToString();
                }

                var property = propertyList.FirstOrDefault(x => x.Name == columnName);

                if (property != null)
                {
                    if (string.IsNullOrWhiteSpace(returnValue.ToString()))
                    {
                        if (property.PropertyType.IsNumber())
                        {
                            returnValue = 0;
                        }
                        else if (property.PropertyType.IsDateTime())
                        {
                            returnValue = DateTime.MinValue;
                        }
                    }

                    returnValue = Convert.ChangeType(returnValue, property.PropertyType);
                }
            }
            catch (Exception exception)
            {
                APILog.Error(this, "GetColumnMaxValue", exception);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader = null;
                }
            }

            return(returnValue);
        }
예제 #4
0
        /// <summary>
        /// Selects all rows.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereClause">The where clause.</param>
        /// <returns></returns>
        public List <T> SelectAllRows <T>(string whereClause = "") where T : new()
        {
            var objectList          = new List <T>();
            SQLiteDataReader reader = null;

            try
            {
                var objectType = typeof(T);
                var properties = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                string sqlQuery = SQLiteQuery.SelectAllFrom(objectType.Name, whereClause);
                reader = SQLiteQuery.ExecuteReader(sqlQuery, this.Connection);

                while (reader.Read())
                {
                    var newObject = new T();

                    foreach (var property in properties)
                    {
                        if (property.IsDataMember())
                        {
                            if (reader[property.Name].HaveContent() || property.PropertyType.IsString())
                            {
                                Type type = property.PropertyType;
                                TypeExtension.GetDataType(type, out type);

                                object  propertyValue = reader[property.Name];
                                dynamic changedObject = Convert.ChangeType(propertyValue, type);
                                property.SetValue(newObject, changedObject, null);
                            }
                        }
                    }

                    objectList.Add(newObject);
                }
            }
            catch (Exception exception)
            {
                APILog.Error(this, "SelectAllRows", exception);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader = null;
                }
            }

            return(objectList);
        }
예제 #5
0
        /// <summary>
        /// Closes the database connection.
        /// </summary>
        /// <returns></returns>
        public bool CloseDbConnection()
        {
            bool result = true;

            try
            {
                if (this.Connection.State != ConnectionState.Closed)
                {
                    this.Connection.Close();
                }
            }
            catch (Exception exception)
            {
                APILog.Error(this, "CloseDbConnection", exception);
                result = false;
            }
            finally
            {
                this.Connection.Dispose();
                this.Connection = null;
            }

            return(result);
        }
예제 #6
0
        /// <summary>
        /// Creates the table.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public void CreateTable <T>()
        {
            try
            {
                int           aux           = 1;
                List <string> keyColumnList = new List <string>();

                var objectType   = typeof(T);
                var propertyList = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                StringBuilder sbCreateTable = new StringBuilder();
                sbCreateTable.Append("CREATE TABLE IF NOT EXISTS ");
                sbCreateTable.Append(objectType.Name);
                sbCreateTable.Append("(");

                foreach (var property in propertyList)
                {
                    if (property.IsDataMember())
                    {
                        if (property.IsKey())
                        {
                            keyColumnList.Add(property.Name);
                        }

                        sbCreateTable.Append(property.Name);
                        sbCreateTable.Append(" ");
                        sbCreateTable.Append(property.PropertyType.ToSQLiteDataType());

                        if (property.PropertyType.IsNull())
                        {
                            sbCreateTable.Append(" NULL");
                        }
                        else
                        {
                            sbCreateTable.Append(" NOT NULL");
                        }

                        sbCreateTable.Append(",");
                    }
                }

                if (keyColumnList.Count > 0)
                {
                    sbCreateTable.Append("PRIMARY KEY (");

                    foreach (string key in keyColumnList)
                    {
                        sbCreateTable.Append(key);

                        if (aux < keyColumnList.Count)
                        {
                            sbCreateTable.Append(",");
                        }

                        aux++;
                    }

                    sbCreateTable.Append("));");
                }
                else
                {
                    sbCreateTable.Remove((sbCreateTable.Length - 1), 1);
                    sbCreateTable.Append(");");
                }

                SQLiteQuery.ExecuteNonQuery(sbCreateTable.ToString(), this.Connection);
            }
            catch (Exception exception)
            {
                APILog.Error(this, "CreateTable", exception);
            }
        }