Example #1
0
        /// <summary>
        /// Updated a previously recorded entry
        /// </summary>
        public void Update(Entry entry)
        {
            //Sync the schema of the entry with the database and cache
            SyncTable(entry);

            //Update the entry based on a matching Key value
            UpdateEntry(entry);
        }
Example #2
0
        /// <summary>
        /// Insert a new entry
        /// </summary>
        public void Insert(Entry entry)
        {
            //Sync the schema of the entry with the database and cache
            SyncTable(entry);

            //Insert the new entry into the database
            InsertEntry(entry);
        }
Example #3
0
        /// <summary>
        /// Syncronizes the database and cache with any new tables or columns based on entry data
        /// </summary>
        private void SyncTable(Entry entry)
        {
            Table table;

            //Attempt to find the cached fields for the table
            if (!Tables.TryGetValue(entry.Table, out table))
            {
                //Create the table in the database
                CreateTable(entry.Table);

                //Cache the table
                table = Tables.GetOrAdd(entry.Table, new Table(entry.Table));
            }

            //Check if there are any new columns on the entry which the cache doesn't have
            var newColumns = entry.Data.Where(d => !table.Columns.Contains(d.Key)).Select(c => c.Key).ToList();
            if (newColumns.Any())
            {
                //Alter the existing table with any new fields that are found and add to cache
                AlterTable(entry.Table, newColumns);

                //Cache the columns
                newColumns.ForEach((c) => table.Columns.Add(c));
            }
        }
Example #4
0
        /// <summary>
        /// Updates a previously recorded entry using the Key value as the identifier
        /// </summary>
        private void UpdateEntry(Entry entry)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                conn.Open();

                string[] parameters = entry.Data.Select(d => string.Format("[{0}] = @{1}", d.Key, d.Key)).ToArray();
                string query = string.Format(@"UPDATE [{0}].[{1}] SET [UpdatedDate] = @UpdatedDate,{2} WHERE [Key] = @Key;", this.Schema, entry.Table, string.Join(",", parameters));

                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.Add(new SqlParameter("@UpdatedDate", DateTime.UtcNow));
                cmd.Parameters.Add(new SqlParameter("@Key", entry.Key));
                cmd.Parameters.AddRange(entry.Data.Select(d => new SqlParameter("@" + d.Key, d.Value)).ToArray());
                cmd.ExecuteNonQuery();
            }
        }
Example #5
0
        /// <summary>
        /// Inserts a new entry into an existing table
        /// </summary>
        private void InsertEntry(Entry entry)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                conn.Open();

                string[] columnNames = entry.Data.Select(d => "[" + d.Key + "]").ToArray();
                string[] dataParameters = entry.Data.Select(d => "@" + d.Key).ToArray();
                string query = string.Format(@"INSERT INTO [{0}].[{1}] ([Key],[CreatedDate],{2}) VALUES (@Key,@CreatedDate,{3});", this.Schema, entry.Table, string.Join(",", columnNames), string.Join(",", dataParameters));

                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.Add(new SqlParameter("@Key", entry.Key));
                cmd.Parameters.Add(new SqlParameter("@CreatedDate", DateTime.UtcNow));
                cmd.Parameters.AddRange(entry.Data.Select(d => new SqlParameter("@" + d.Key, d.Value)).ToArray());
                cmd.ExecuteNonQuery();
            }
        }