Example #1
0
        /// Select one or more rows into a row set.
        public static List <BillRow> Select(string query)
        {
            var result           = new List <BillRow>();
            SQLiteConnection con = null;

            try {
                using (con = DB.Connect()) {       // Obtain SQLiteConnection
                    con.Open();                    // Open the connection to the database
                    using (SQLiteCommand com = new SQLiteCommand(con)) {
                        com.CommandText = query;
                        using (SQLiteDataReader reader = com.ExecuteReader()) {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    var row = new BillRow(reader);
                                    table_contents.Add(new BillRow(reader));
                                }
                            }
                        }
                    }
                }
            } catch (Exception ex) {
                Log.Instance.Info($"BillRowsTable.ReadYourself: {ex.Message}");
                throw;
            }
            return(result);
        }
Example #2
0
        public static string RowToColumns(BillRow row)
        {
            var    forward = row.Lob.Replace('\\', '/').Trim();
            string result  =
                $"'{row.Bill          }'{DB.Separator}'{row.MeasureType   }'{DB.Separator}" +
                $"'{row.MeasureNum    }'{DB.Separator}'{forward           }'{DB.Separator}" +
                $"{ row.NegativeScore }{ DB.Separator}{ row.PositiveScore }{ DB.Separator}" +
                $"'{row.Position      }'{DB.Separator}'{row.BillVersionID }'{DB.Separator}" +
                $"'{row.Author        }'{DB.Separator}'{row.Title         }'{DB.Separator}" +
                $"'{row.Location      }'{DB.Separator}'{row.Location2nd   }'{DB.Separator}" +
                $"'{row.MeasureState  }'{DB.Separator}'{row.CurrentHouse  }'{DB.Separator}" +
                $"'{row.CurrentStatus }'";

            return(result);
        }
Example #3
0
        public static void InsertCollection(List <Bill_Identifier> identifers)
        {
            var sql_accumulator = new StringBuilder(); // Accumulate SQL statements here, submit as single statement
            var accumulated     = 0;

            foreach (var identifier in identifers)
            {
                if (accumulated >= SQL_INSERT_ROW_LIMIT)
                {
                    FlushAccumulator(sql_accumulator);
                    accumulated = 0;
                }
                sql_accumulator.Append($" ({BillRow.RowToColumns(new BillRow(identifier))}),");
                accumulated++;
            }
        }
Example #4
0
        public static BillRow Row(string bill)
        {
            BillRow result = null;
            var     query  = $"Select BillID,MeasureType,MeasureNum,Lob,NegativeScore,PositiveScore,Position,BillVersionID,Author,Title,Location,Location2nd,MeasureState,CurrentHouse,CurrentStatus from BillRows Where BillID='{bill}';";

            try {
                using (SQLiteConnection con = DB.Connect()) { // Obtain SQLiteConnection
                    con.Open();                               // Open the connection to the database
                    using (SQLiteCommand cmd = new SQLiteCommand(query, con)) {
                        using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                            reader.Read();
                            result = new BillRow(reader);
                        }
                    }
                }
            } catch (Exception ex) {
                Log.Instance.Info($"BillRow.Row({query}): {ex.Message}");
                throw;
            }
            return(result);
        }
Example #5
0
 private static bool FlushAccumulator(StringBuilder sql_accumulator)
 {
     try {
         string raw = sql_accumulator.ToString();
         if (raw.Length > 0)
         {
             string inserts  = raw.Substring(0, raw.Length - 1); // Trim trailing comma
             var    prefix   = $"Insert Into [{table_Name}] ({ BillRow.Columns()}) Values";
             string nonQuery = prefix + inserts + ";";
             sql_accumulator.Clear();
             DB.NonQuery(nonQuery, "BillRowsTable.FlushAccumulator");
             Log.Instance.Info($"BillRowsTable.FlushAccumulator wrote {SQL_INSERT_ROW_LIMIT} rows.");
         }
     } catch (SqlException sql) {
         Log.Instance.Info($"BillRowsTable.FlushAccumulator: {sql.Message}");
         throw;
     } catch (Exception e) {
         Log.Instance.Info($"BillRowsTable.FlushAccumulator: {e.Message}");
         throw;
     }
     return(true);
 }
Example #6
0
        public static void Insert(BillRow row, StringBuilder sql_accumulator)
        {
            var non_query = $"Insert Into [{table_Name}] ({BillRow.Columns()}) Values ({BillRow.RowToColumns(row)});";

            DB.NonQuery(non_query, "BillRowsTable.Insert");
        }