/// 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);
}
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);
}
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++;
}
}
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);
}
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);
}
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");
}