예제 #1
0
 /// <summary>
 /// Check if a table exists in the database.
 /// </summary>
 /// <param name="tableName">The name of the table.</param>
 /// <returns>True if exists.</returns>
 public bool TableExists(string tableName)
 {
     if (String.IsNullOrEmpty(tableName))
     {
         throw new ArgumentNullException(nameof(tableName));
     }
     return(ListTables().Contains(PostgresqlHelper.ExtractTableName(tableName)));
 }
예제 #2
0
 /// <summary>
 /// Sanitize an input string.
 /// </summary>
 /// <param name="s">The value to sanitize.</param>
 /// <returns>A sanitized string.</returns>
 public string SanitizeString(string s)
 {
     if (String.IsNullOrEmpty(s))
     {
         return(s);
     }
     return(PostgresqlHelper.SanitizeString(s));
 }
예제 #3
0
 /// <summary>
 /// Empties a table completely.
 /// </summary>
 /// <param name="tableName">The table you wish to TRUNCATE.</param>
 public void Truncate(string tableName)
 {
     if (String.IsNullOrEmpty(tableName))
     {
         throw new ArgumentNullException(nameof(tableName));
     }
     Query(PostgresqlHelper.TruncateQuery(tableName));
 }
예제 #4
0
 /// <summary>
 /// Execute a SELECT query.
 /// </summary>
 /// <param name="tableName">The table from which you wish to SELECT.</param>
 /// <param name="indexStart">The starting index for retrieval; used for pagination in conjunction with maxResults and orderByClause.  orderByClause example: ORDER BY created DESC.</param>
 /// <param name="maxResults">The maximum number of results to retrieve.</param>
 /// <param name="returnFields">The fields you wish to have returned.  Null returns all.</param>
 /// <param name="filter">The expression containing the SELECT filter (i.e. WHERE clause data).</param>
 /// <param name="orderByClause">Specify an ORDER BY clause if desired.</param>
 /// <returns>A DataTable containing the results.</returns>
 public DataTable Select(string tableName, int?indexStart, int?maxResults, List <string> returnFields, Expression filter, string orderByClause)
 {
     if (String.IsNullOrEmpty(tableName))
     {
         throw new ArgumentNullException(nameof(tableName));
     }
     return(Query(PostgresqlHelper.SelectQuery(tableName, indexStart, maxResults, returnFields, filter, orderByClause)));
 }
예제 #5
0
 /// <summary>
 /// Create an instance of the database client.
 /// </summary>
 /// <param name="settings">Database settings.</param>
 public DatabaseClient(DatabaseSettings settings)
 {
     _Settings = settings ?? throw new ArgumentNullException(nameof(settings));
     if (_Settings.Type != DbTypes.Postgresql)
     {
         throw new ArgumentException("Database settings must be of type 'Postgresql'.");
     }
     _ConnectionString = PostgresqlHelper.ConnectionString(_Settings);
 }
예제 #6
0
 /// <summary>
 /// Execute a DELETE query.
 /// </summary>
 /// <param name="tableName">The table in which you wish to DELETE.</param>
 /// <param name="filter">The expression containing the DELETE filter (i.e. WHERE clause data).</param>
 public void Delete(string tableName, Expression filter)
 {
     if (String.IsNullOrEmpty(tableName))
     {
         throw new ArgumentNullException(nameof(tableName));
     }
     if (filter == null)
     {
         throw new ArgumentNullException(nameof(filter));
     }
     Query(PostgresqlHelper.DeleteQuery(tableName, filter));
 }
예제 #7
0
 /// <summary>
 /// Create a table with a specified name.
 /// </summary>
 /// <param name="tableName">The name of the table.</param>
 /// <param name="columns">Columns.</param>
 public void CreateTable(string tableName, List <Column> columns)
 {
     if (String.IsNullOrEmpty(tableName))
     {
         throw new ArgumentNullException(nameof(tableName));
     }
     if (columns == null || columns.Count < 1)
     {
         throw new ArgumentNullException(nameof(columns));
     }
     Query(PostgresqlHelper.CreateTableQuery(tableName, columns));
 }
예제 #8
0
        /// <summary>
        /// Determine if records exist by filter.
        /// </summary>
        /// <param name="tableName">The name of the table.</param>
        /// <param name="filter">Expression.</param>
        /// <returns>True if records exist.</returns>
        public bool Exists(string tableName, Expression filter)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            DataTable result = Query(PostgresqlHelper.ExistsQuery(tableName, filter));

            if (result != null && result.Rows.Count > 0)
            {
                return(true);
            }
            return(false);
        }
예제 #9
0
        /// <summary>
        /// List all tables in the database.
        /// </summary>
        /// <returns>List of strings, each being a table name.</returns>
        public List <string> ListTables()
        {
            List <string> tableNames = new List <string>();
            DataTable     result     = Query(PostgresqlHelper.LoadTableNamesQuery());

            if (result != null && result.Rows.Count > 0)
            {
                foreach (DataRow curr in result.Rows)
                {
                    tableNames.Add(curr["tablename"].ToString());
                }
            }

            return(tableNames);
        }
예제 #10
0
        /// <summary>
        /// Determine the number of records that exist by filter.
        /// </summary>
        /// <param name="tableName">The name of the table.</param>
        /// <param name="filter">Expression.</param>
        /// <returns>The number of records.</returns>
        public long Count(string tableName, Expression filter)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            DataTable result = Query(PostgresqlHelper.CountQuery(tableName, _CountColumnName, filter));

            if (result != null &&
                result.Rows.Count > 0 &&
                result.Rows[0].Table.Columns.Contains(_CountColumnName) &&
                result.Rows[0][_CountColumnName] != null &&
                result.Rows[0][_CountColumnName] != DBNull.Value)
            {
                return(Convert.ToInt64(result.Rows[0][_CountColumnName]));
            }
            return(0);
        }
예제 #11
0
        /// <summary>
        /// Determine the sum of a column for records that match the supplied filter.
        /// </summary>
        /// <param name="tableName">The name of the table.</param>
        /// <param name="fieldName">The name of the field.</param>
        /// <param name="filter">Expression.</param>
        /// <returns>The sum of the specified column from the matching rows.</returns>
        public decimal Sum(string tableName, string fieldName, Expression filter)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            if (String.IsNullOrEmpty(fieldName))
            {
                throw new ArgumentNullException(nameof(fieldName));
            }
            DataTable result = Query(PostgresqlHelper.SumQuery(tableName, fieldName, _SumColumnName, filter));

            if (result != null &&
                result.Rows.Count > 0 &&
                result.Rows[0].Table.Columns.Contains(_SumColumnName) &&
                result.Rows[0][_SumColumnName] != null &&
                result.Rows[0][_SumColumnName] != DBNull.Value)
            {
                return(Convert.ToDecimal(result.Rows[0][_SumColumnName]));
            }
            return(0m);
        }
예제 #12
0
        /// <summary>
        /// Create an instance of the database client.
        /// </summary>
        /// <param name="serverIp">The IP address or hostname of the database server.</param>
        /// <param name="serverPort">The TCP port of the database server.</param>
        /// <param name="username">The username to use when authenticating with the database server.</param>
        /// <param name="password">The password to use when authenticating with the database server.</param>
        /// <param name="database">The name of the database with which to connect.</param>
        public DatabaseClient(
            string serverIp,
            int serverPort,
            string username,
            string password,
            string database)
        {
            if (String.IsNullOrEmpty(serverIp))
            {
                throw new ArgumentNullException(nameof(serverIp));
            }
            if (serverPort < 0)
            {
                throw new ArgumentOutOfRangeException(nameof(serverPort));
            }
            if (String.IsNullOrEmpty(database))
            {
                throw new ArgumentNullException(nameof(database));
            }

            _Settings         = new DatabaseSettings(DbTypes.Postgresql, serverIp, serverPort, username, password, database);
            _ConnectionString = PostgresqlHelper.ConnectionString(_Settings);
        }
예제 #13
0
 /// <summary>
 /// Convert a DateTime to a formatted string.
 /// </summary>
 /// <param name="ts">The timestamp.</param>
 /// <returns>A string formatted for use with the specified database.</returns>
 public static string DbTimestamp(DateTime ts)
 {
     return(PostgresqlHelper.DbTimestamp(ts));
 }
예제 #14
0
        /// <summary>
        /// Execute an UPDATE query.
        /// The updated rows are returned.
        /// </summary>
        /// <param name="tableName">The table in which you wish to UPDATE.</param>
        /// <param name="keyValuePairs">The key-value pairs for the data you wish to UPDATE.</param>
        /// <param name="filter">The expression containing the UPDATE filter (i.e. WHERE clause data).</param>
        /// <returns>DataTable containing the updated rows.</returns>
        public DataTable Update(string tableName, Dictionary <string, object> keyValuePairs, Expression filter)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            if (keyValuePairs == null || keyValuePairs.Count < 1)
            {
                throw new ArgumentNullException(nameof(keyValuePairs));
            }

            #region Build-Key-Value-Clause

            string keyValueClause = "";
            int    added          = 0;
            foreach (KeyValuePair <string, object> curr in keyValuePairs)
            {
                if (String.IsNullOrEmpty(curr.Key))
                {
                    continue;
                }

                if (added == 0)
                {
                    if (curr.Value != null)
                    {
                        if (curr.Value is DateTime || curr.Value is DateTime?)
                        {
                            keyValueClause += PostgresqlHelper.PreparedFieldname(curr.Key) + "='" + DbTimestamp((DateTime)curr.Value) + "'";
                        }
                        else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
                        {
                            keyValueClause += PostgresqlHelper.PreparedFieldname(curr.Key) + "=" + curr.Value.ToString();
                        }
                        else
                        {
                            if (Helper.IsExtendedCharacters(curr.Value.ToString()))
                            {
                                keyValueClause += PostgresqlHelper.PreparedFieldname(curr.Key) + "=" + PostgresqlHelper.PreparedUnicodeValue(curr.Value.ToString());
                            }
                            else
                            {
                                keyValueClause += PostgresqlHelper.PreparedFieldname(curr.Key) + "=" + PostgresqlHelper.PreparedStringValue(curr.Value.ToString());
                            }
                        }
                    }
                    else
                    {
                        keyValueClause += PostgresqlHelper.PreparedFieldname(curr.Key) + "= null";
                    }
                }
                else
                {
                    if (curr.Value != null)
                    {
                        if (curr.Value is DateTime || curr.Value is DateTime?)
                        {
                            keyValueClause += "," + PostgresqlHelper.PreparedFieldname(curr.Key) + "='" + DbTimestamp((DateTime)curr.Value) + "'";
                        }
                        else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
                        {
                            keyValueClause += "," + PostgresqlHelper.PreparedFieldname(curr.Key) + "=" + curr.Value.ToString();
                        }
                        else
                        {
                            if (Helper.IsExtendedCharacters(curr.Value.ToString()))
                            {
                                keyValueClause += "," + PostgresqlHelper.PreparedFieldname(curr.Key) + "=" + PostgresqlHelper.PreparedUnicodeValue(curr.Value.ToString());
                            }
                            else
                            {
                                keyValueClause += "," + PostgresqlHelper.PreparedFieldname(curr.Key) + "=" + PostgresqlHelper.PreparedStringValue(curr.Value.ToString());
                            }
                        }
                    }
                    else
                    {
                        keyValueClause += "," + PostgresqlHelper.PreparedFieldname(curr.Key) + "= null";
                    }
                }
                added++;
            }

            #endregion

            #region Build-UPDATE-Query-and-Submit

            return(Query(PostgresqlHelper.UpdateQuery(tableName, keyValueClause, filter)));

            #endregion
        }
예제 #15
0
        /// <summary>
        /// Execute an INSERT query with multiple values within a transaction.
        /// </summary>
        /// <param name="tableName">The table in which you wish to INSERT.</param>
        /// <param name="keyValuePairList">List of dictionaries containing key-value pairs for the rows you wish to INSERT.</param>
        public void InsertMultiple(string tableName, List <Dictionary <string, object> > keyValuePairList)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            if (keyValuePairList == null || keyValuePairList.Count < 1)
            {
                throw new ArgumentNullException(nameof(keyValuePairList));
            }

            #region Validate-Inputs

            Dictionary <string, object> reference = keyValuePairList[0];

            if (keyValuePairList.Count > 1)
            {
                foreach (Dictionary <string, object> dict in keyValuePairList)
                {
                    if (!(reference.Count == dict.Count) || !(reference.Keys.SequenceEqual(dict.Keys)))
                    {
                        throw new ArgumentException("All supplied dictionaries must contain exactly the same keys.");
                    }
                }
            }

            #endregion

            #region Build-Keys

            string keys      = "";
            int    keysAdded = 0;
            foreach (KeyValuePair <string, object> curr in reference)
            {
                if (keysAdded > 0)
                {
                    keys += ",";
                }
                keys += PostgresqlHelper.PreparedFieldName(curr.Key);
                keysAdded++;
            }

            #endregion

            #region Build-Values

            List <string> values = new List <string>();

            foreach (Dictionary <string, object> currDict in keyValuePairList)
            {
                string vals      = "";
                int    valsAdded = 0;

                foreach (KeyValuePair <string, object> currKvp in currDict)
                {
                    if (valsAdded > 0)
                    {
                        vals += ",";
                    }

                    if (currKvp.Value != null)
                    {
                        if (currKvp.Value is DateTime || currKvp.Value is DateTime?)
                        {
                            vals += "'" + DbTimestamp((DateTime)currKvp.Value) + "'";
                        }
                        else if (currKvp.Value is int || currKvp.Value is long || currKvp.Value is decimal)
                        {
                            vals += currKvp.Value.ToString();
                        }
                        else
                        {
                            if (Helper.IsExtendedCharacters(currKvp.Value.ToString()))
                            {
                                vals += PostgresqlHelper.PreparedUnicodeValue(currKvp.Value.ToString());
                            }
                            else
                            {
                                vals += PostgresqlHelper.PreparedStringValue(currKvp.Value.ToString());
                            }
                        }
                    }
                    else
                    {
                        vals += "null";
                    }

                    valsAdded++;
                }

                values.Add(vals);
            }

            #endregion

            #region Build-INSERT-Query-and-Submit

            Query(PostgresqlHelper.InsertMultipleQuery(tableName, keys, values));

            #endregion
        }
예제 #16
0
        /// <summary>
        /// Show the columns and column metadata from a specific table.
        /// </summary>
        /// <param name="tableName">The table to view.</param>
        /// <returns>A list of column objects.</returns>
        public List <Column> DescribeTable(string tableName)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }

            List <Column> columns = new List <Column>();
            DataTable     result  = Query(PostgresqlHelper.LoadTableColumnsQuery(_Settings.DatabaseName, tableName));

            if (result != null && result.Rows.Count > 0)
            {
                foreach (DataRow currColumn in result.Rows)
                {
                    #region Process-Each-Column

                    /*
                     * public bool PrimaryKey;
                     * public string Name;
                     * public string DataType;
                     * public int? MaxLength;
                     * public bool Nullable;
                     */

                    Column tempColumn = new Column();

                    tempColumn.Name = currColumn["COLUMN_NAME"].ToString();

                    tempColumn.MaxLength = null;
                    if (currColumn.Table.Columns.Contains("CHARACTER_MAXIMUM_LENGTH"))
                    {
                        int maxLength = 0;
                        if (Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
                        {
                            tempColumn.MaxLength = maxLength;
                        }
                    }

                    tempColumn.Type = Helper.DataTypeFromString(currColumn["DATA_TYPE"].ToString());

                    if (currColumn.Table.Columns.Contains("IS_NULLABLE"))
                    {
                        if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
                        {
                            tempColumn.Nullable = true;
                        }
                        else
                        {
                            tempColumn.Nullable = false;
                        }
                    }
                    else if (currColumn.Table.Columns.Contains("IS_NOT_NULLABLE"))
                    {
                        tempColumn.Nullable = !(Convert.ToBoolean(currColumn["IS_NOT_NULLABLE"]));
                    }

                    if (currColumn["IS_PRIMARY_KEY"] != null &&
                        currColumn["IS_PRIMARY_KEY"] != DBNull.Value &&
                        !String.IsNullOrEmpty(currColumn["IS_PRIMARY_KEY"].ToString()))
                    {
                        if (currColumn["IS_PRIMARY_KEY"].ToString().ToLower().Equals("yes"))
                        {
                            tempColumn.PrimaryKey = true;
                        }
                    }

                    if (!columns.Exists(c => c.Name.Equals(tempColumn.Name)))
                    {
                        columns.Add(tempColumn);
                    }

                    #endregion
                }
            }

            return(columns);
        }
예제 #17
0
 internal static string PreparedStringValue(string s)
 {
     // uses $xx$ escaping
     return(PostgresqlHelper.SanitizeString(s));
 }
예제 #18
0
        /// <summary>
        /// Execute an INSERT query.
        /// </summary>
        /// <param name="tableName">The table in which you wish to INSERT.</param>
        /// <param name="keyValuePairs">The key-value pairs for the row you wish to INSERT.</param>
        /// <returns>A DataTable containing the results.</returns>
        public DataTable Insert(string tableName, Dictionary <string, object> keyValuePairs)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            if (keyValuePairs == null || keyValuePairs.Count < 1)
            {
                throw new ArgumentNullException(nameof(keyValuePairs));
            }

            #region Build-Key-Value-Pairs

            string keys   = "";
            string values = "";
            int    added  = 0;
            foreach (KeyValuePair <string, object> curr in keyValuePairs)
            {
                if (String.IsNullOrEmpty(curr.Key))
                {
                    continue;
                }

                if (added == 0)
                {
                    #region First

                    keys += PostgresqlHelper.PreparedFieldname(curr.Key);
                    if (curr.Value != null)
                    {
                        if (curr.Value is DateTime || curr.Value is DateTime?)
                        {
                            values += "'" + DbTimestamp((DateTime)curr.Value) + "'";
                        }
                        else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
                        {
                            values += curr.Value.ToString();
                        }
                        else
                        {
                            if (Helper.IsExtendedCharacters(curr.Value.ToString()))
                            {
                                values += PostgresqlHelper.PreparedUnicodeValue(curr.Value.ToString());
                            }
                            else
                            {
                                values += PostgresqlHelper.PreparedStringValue(curr.Value.ToString());
                            }
                        }
                    }
                    else
                    {
                        values += "null";
                    }

                    #endregion
                }
                else
                {
                    #region Subsequent

                    keys += "," + PostgresqlHelper.PreparedFieldname(curr.Key);
                    if (curr.Value != null)
                    {
                        if (curr.Value is DateTime || curr.Value is DateTime?)
                        {
                            values += ",'" + DbTimestamp((DateTime)curr.Value) + "'";
                        }
                        else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
                        {
                            values += "," + curr.Value.ToString();
                        }
                        else
                        {
                            if (Helper.IsExtendedCharacters(curr.Value.ToString()))
                            {
                                values += "," + PostgresqlHelper.PreparedUnicodeValue(curr.Value.ToString());
                            }
                            else
                            {
                                values += "," + PostgresqlHelper.PreparedStringValue(curr.Value.ToString());
                            }
                        }
                    }
                    else
                    {
                        values += ",null";
                    }

                    #endregion
                }

                added++;
            }

            #endregion

            #region Build-INSERT-Query-and-Submit

            return(Query(PostgresqlHelper.InsertQuery(tableName, keys, values)));

            #endregion
        }