List <string> run_column_name_query(sqlite3 db, string table)
{
List <string> columnNames = new List <string>();
string sql = "PRAGMA table_info(" + table + ");";
sqlite3_stmt pSelect = new sqlite3_stmt();
int rc;
string sDummy = null;
rc = Sqlite3.sqlite3_prepare(db, sql, -1, ref pSelect, ref sDummy);
if (rc != Sqlite3.SQLITE_OK || null == pSelect)
{
return(columnNames);
}
rc = Sqlite3.sqlite3_step(pSelect);
while (rc == Sqlite3.SQLITE_ROW)
{
sqlite3_value val = Sqlite3.sqlite3_column_value(pSelect, 1);
columnNames.Add(val.z);
rc = Sqlite3.sqlite3_step(pSelect);
}
Sqlite3.sqlite3_finalize(pSelect);
return(columnNames);
}
/// <summary>
/// Retrieves the name of all the tables within the DB
/// </summary>
/// <param name="db">pointer to database</param>
/// <param name="zSelect"></param>
/// <returns>List of tablenames in database</returns>
List <string> run_table_names_query(sqlite3 db)
{
List <string> tableNames = new List <string>();
string getTables = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;";
sqlite3_stmt pSelect = new sqlite3_stmt();
int rc;
string sDummy = null;
rc = Sqlite3.sqlite3_prepare(db, getTables, -1, ref pSelect, ref sDummy);
if (rc != Sqlite3.SQLITE_OK || null == pSelect)
{
return(tableNames);
}
rc = Sqlite3.sqlite3_step(pSelect);
while (rc == Sqlite3.SQLITE_ROW)
{
tableNames.Add(Sqlite3.sqlite3_column_text(pSelect, 0));
rc = Sqlite3.sqlite3_step(pSelect);
}
Sqlite3.sqlite3_finalize(pSelect);
return(tableNames);
}
public DataSet GetDBTables()
{
DataSet fredDs = new DataSet("FRED");
List <string> tables = run_table_names_query(pDb);
int rc = 0;
foreach (string table in tables)
{
bool firstRun = true;
List <string> names = new List <string>();
List <string> arrRows = new List <string>();
List <string> colNames = run_column_name_query(pDb, table);
string zSql = "SELECT * from " + table + ";";
sqlite3_stmt pSelect = new sqlite3_stmt();
string sDummy = null;
rc = Sqlite3.sqlite3_prepare(pDb, zSql, -1, ref pSelect, ref sDummy);
if (rc != Sqlite3.SQLITE_OK || null == pSelect)
{
continue;
}
rc = Sqlite3.sqlite3_step(pSelect);
DataTable dt = new DataTable(table);
while (rc == Sqlite3.SQLITE_ROW)
{
//Get the data in the cell
int total = Sqlite3.sqlite3_column_count(pSelect);
DataRow dr = dt.NewRow();
List <string> values = new List <string>();
for (int i = 0; i < total; i++)
{
int valInt = 0;
string value = "";
sqlite3_value val = Sqlite3.sqlite3_column_value(pSelect, i);
if (val.type == 1)
{
valInt = Sqlite3.sqlite3_column_int(pSelect, i);
value = valInt.ToString();
}
else
{
value = val.z;
}
values.Add(value);
if (firstRun)
{
string name = Sqlite3.sqlite3_column_name(pSelect, i);
if (name == null)
{
continue;
}
names.Add(name);
dt.Columns.Add(name);
}
}
rc = Sqlite3.sqlite3_step(pSelect);
firstRun = false;
for (int i = 0; i < names.Count && i < values.Count; i++)
{
dr[names[i]] = values[i];
}
dt.Rows.Add(dr);
}
Sqlite3.sqlite3_finalize(pSelect);
fredDs.Tables.Add(dt);
}
Sqlite3.sqlite3_close(pDb);
return(fredDs);
}
