private DataTable convertToDataTable(SQLiteDataReader reader)
{
DataTable table = new DataTable();
DataTable dtb = new DataTable();
table = reader.GetSchemaTable();
for (int x = 0; x < table.Rows.Count; x++)
{
string colNome = table.Rows[x]["ColumnName"].ToString();
dtb.Columns.Add(colNome, typeof(string));
}
dtb.AcceptChanges();
while (reader.Read())
{
DataRow dr = dtb.NewRow();
for (int i = 0; i < table.Rows.Count; i++)
{
string colNome = table.Rows[i]["ColumnName"].ToString();
string colType = table.Rows[i]["DataTypeName"].ToString();
string valor = string.Empty;
if (colType.ToUpper().Equals("NUMBER") || colType.ToUpper().Equals("INT") || colType.ToUpper().Equals("INTERGER"))
{
try
{
long Lvalor = reader.GetInt64(i);
if (Lvalor != null)
valor = Lvalor.ToString();
}
catch (InvalidCastException inv)
{
valor = string.Empty;
}
}
else if (colType.ToUpper().Equals("VARCHAR2") || colType.ToUpper().Equals("VARCHAR") || colType.ToUpper().Equals("TEXT"))
valor = reader.GetString(i).ToString();
dr[colNome] = valor;
}
dtb.Rows.Add(dr);
}
dtb.AcceptChanges();
return dtb;
}
/// <summary>
/// Get the <see cref="DataTable"/> from a <see cref="SQLiteDataReader"/>
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
static public DataTable GetDataTable(this SQLiteDataReader dataReader)
{
DataTable rslt = new DataTable(dataReader.GetTableName(0));
foreach (DataRow item in dataReader.GetSchemaTable().Rows)
{
rslt.Columns.Add(item[0].ToString(), item[12] as Type);
}
while (dataReader.Read())
{
object[] tbl = new object[dataReader.FieldCount];
for (int i = 0; i < dataReader.FieldCount; i++)
{
tbl[i] = dataReader[i];
}
rslt.Rows.Add(tbl);
}
return(rslt);
}
private void BuildCache(bool closeConnection)
{
SQLiteCommand sourceCommand = SourceCommand;
if (sourceCommand == null)
{
throw new InvalidOperationException("The DataAdapter.SelectCommand property needs to be initialized.");
}
SQLiteConnection connection = sourceCommand.Connection as SQLiteConnection;
if (connection == null)
{
throw new InvalidOperationException("The DataAdapter.SelectCommand.Connection property needs to be initialized.");
}
if (_schemaTable == null)
{
if (connection.State == ConnectionState.Open)
{
closeConnection = false;
}
else
{
connection.Open();
}
SQLiteDataReader reader = sourceCommand.ExecuteReader(CommandBehavior.SchemaOnly |
CommandBehavior.KeyInfo);
_schemaTable = reader.GetSchemaTable();
reader.Close();
if (closeConnection)
{
connection.Close();
}
BuildInformation(_schemaTable);
}
}
/// <summary>
/// This function does all the nasty work at determining what keys need to be returned for
/// a given statement.
/// </summary>
/// <param name="cnn"></param>
/// <param name="reader"></param>
/// <param name="stmt"></param>
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
Dictionary<string, int> catalogs = new Dictionary<string, int>();
Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>();
List<string> list;
List<KeyInfo> keys = new List<KeyInfo>();
// Record the statement so we can use it later for sync'ing
_stmt = stmt;
// Fetch all the attached databases on this connection
using (DataTable tbl = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// Fetch all the unique tables and catalogs used by the current statement
using (DataTable schema = reader.GetSchemaTable(false, false))
{
foreach (DataRow row in schema.Rows)
{
// Check if column is backed to a table
if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
continue;
// Record the unique table so we can look up its keys
string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string table = (string)row[SchemaTableColumn.BaseTableName];
if (tables.ContainsKey(catalog) == false)
{
list = new List<string>();
tables.Add(catalog, list);
}
else
list = tables[catalog];
if (list.Contains(table) == false)
list.Add(table);
}
// For each catalog and each table, query the indexes for the table.
// Find a primary key index if there is one. If not, find a unique index instead
foreach (KeyValuePair<string, List<string>> pair in tables)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string table = pair.Value[i];
DataRow preferredRow = null;
using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
{
// Loop twice. The first time looking for a primary key index,
// the second time looking for a unique index
for (int n = 0; n < 2 && preferredRow == null; n++)
{
foreach (DataRow row in tbl.Rows)
{
if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
{
preferredRow = row;
break;
}
else if (n == 1 && (bool)row["UNIQUE"] == true)
{
preferredRow = row;
break;
}
}
}
if (preferredRow == null) // Unable to find any suitable index for this table so remove it
{
pair.Value.RemoveAt(i);
i--;
}
else // We found a usable index, so fetch the necessary table details
{
using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
{
// Find the root page of the table in the current statement and get the cursor that's iterating it
int database = catalogs[pair.Key];
int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture);
int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
// Now enumerate the members of the index we're going to use
using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
{
KeyQuery query = null;
List<string> cols = new List<string>();
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
bool addKey = true;
// If the column in the index already appears in the query, skip it
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(SchemaTableColumn.BaseColumnName))
continue;
if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
(string)row[SchemaTableColumn.BaseTableName] == table &&
(string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
{
indexColumns.Rows.RemoveAt(x);
x--;
addKey = false;
break;
}
}
if (addKey == true)
cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
}
// If the index is not a rowid alias, record all the columns
// needed to make up the unique index and construct a SQL query for it
if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
{
// Whatever remains of the columns we need that make up the index that are not
// already in the query need to be queried separately, so construct a subquery
if (cols.Count > 0)
{
string[] querycols = new string[cols.Count];
cols.CopyTo(querycols);
query = new KeyQuery(cnn, pair.Key, table, querycols);
}
}
// Create a KeyInfo struct for each column of the index
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
KeyInfo key = new KeyInfo();
key.rootPage = rootPage;
key.cursor = cursor;
key.database = database;
key.databaseName = pair.Key;
key.tableName = table;
key.columnName = columnName;
key.query = query;
key.column = x;
keys.Add(key);
}
}
}
}
}
}
}
}
// Now we have all the additional columns we have to return in order to support
// CommandBehavior.KeyInfo
_keyInfo = new KeyInfo[keys.Count];
keys.CopyTo(_keyInfo);
}
/// <summary>
/// This function does all the nasty work at determining what keys need to be returned for
/// a given statement.
/// </summary>
/// <param name="cnn"></param>
/// <param name="reader"></param>
/// <param name="stmt"></param>
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
Dictionary <string, int> catalogs = new Dictionary <string, int>();
Dictionary <string, List <string> > tables = new Dictionary <string, List <string> >();
List <string> list;
List <KeyInfo> keys = new List <KeyInfo>();
// Record the statement so we can use it later for sync'ing
_stmt = stmt;
// Fetch all the attached databases on this connection
using (DataTable tbl = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// Fetch all the unique tables and catalogs used by the current statement
using (DataTable schema = reader.GetSchemaTable(false, false))
{
foreach (DataRow row in schema.Rows)
{
// Check if column is backed to a table
if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
{
continue;
}
// Record the unique table so we can look up its keys
string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string table = (string)row[SchemaTableColumn.BaseTableName];
if (tables.ContainsKey(catalog) == false)
{
list = new List <string>();
tables.Add(catalog, list);
}
else
{
list = tables[catalog];
}
if (list.Contains(table) == false)
{
list.Add(table);
}
}
// For each catalog and each table, query the indexes for the table.
// Find a primary key index if there is one. If not, find a unique index instead
foreach (KeyValuePair <string, List <string> > pair in tables)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string table = pair.Value[i];
DataRow preferredRow = null;
using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
{
// Loop twice. The first time looking for a primary key index,
// the second time looking for a unique index
for (int n = 0; n < 2 && preferredRow == null; n++)
{
foreach (DataRow row in tbl.Rows)
{
if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
{
preferredRow = row;
break;
}
else if (n == 1 && (bool)row["UNIQUE"] == true)
{
preferredRow = row;
break;
}
}
}
if (preferredRow == null) // Unable to find any suitable index for this table so remove it
{
pair.Value.RemoveAt(i);
i--;
}
else // We found a usable index, so fetch the necessary table details
{
using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
{
// Find the root page of the table in the current statement and get the cursor that's iterating it
int database = catalogs[pair.Key];
int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture);
int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
// Now enumerate the members of the index we're going to use
using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
{
KeyQuery query = null;
List <string> cols = new List <string>();
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
bool addKey = true;
// If the column in the index already appears in the query, skip it
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(SchemaTableColumn.BaseColumnName))
{
continue;
}
if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
(string)row[SchemaTableColumn.BaseTableName] == table &&
(string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
{
indexColumns.Rows.RemoveAt(x);
x--;
addKey = false;
break;
}
}
if (addKey == true)
{
cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
}
}
// If the index is not a rowid alias, record all the columns
// needed to make up the unique index and construct a SQL query for it
if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
{
// Whatever remains of the columns we need that make up the index that are not
// already in the query need to be queried separately, so construct a subquery
if (cols.Count > 0)
{
string[] querycols = new string[cols.Count];
cols.CopyTo(querycols);
query = new KeyQuery(cnn, pair.Key, table, querycols);
}
}
// Create a KeyInfo struct for each column of the index
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
KeyInfo key = new KeyInfo();
key.rootPage = rootPage;
key.cursor = cursor;
key.database = database;
key.databaseName = pair.Key;
key.tableName = table;
key.columnName = columnName;
key.query = query;
key.column = x;
keys.Add(key);
}
}
}
}
}
}
}
}
// Now we have all the additional columns we have to return in order to support
// CommandBehavior.KeyInfo
_keyInfo = new KeyInfo[keys.Count];
keys.CopyTo(_keyInfo);
}
private string[] PopulateTableSchema(IFeatureSet fs, string geomColumnName, SQLiteDataReader rdr)
{
DataTable schemaTable = rdr.GetSchemaTable();
List<string> columnNameList = new List<string>();
foreach (DataRow r in schemaTable.Rows)
{
if (r["ColumnName"].ToString() != geomColumnName)
{
string colName = Convert.ToString(r["ColumnName"]);
string colDataType = Convert.ToString(r["DataType"]);
fs.DataTable.Columns.Add(colName, Type.GetType(colDataType));
columnNameList.Add(colName);
}
}
return columnNameList.ToArray();
}
public DataSet DataReader2DataSet ( SQLiteDataReader reader )
{
DataSet dataSet = new DataSet ( );
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable ( );
DataTable dataTable = new DataTable ( );
if ( schemaTable != null )
{
// A query returning records was executed
for ( int i = 0 ; i < schemaTable.Rows.Count ; i++ )
{
DataRow dataRow = schemaTable.Rows [ i ];
// Create a column name that is unique in the data table
string columnName = ( string ) dataRow [ "ColumnName" ];
// Add the column definition to the data table
DataColumn column = new DataColumn ( columnName , ( Type ) dataRow [ "DataType" ] );
dataTable.Columns.Add ( column );
}
dataSet.Tables.Add ( dataTable );
// Fill the data table we just created
while ( reader.Read ( ) )
{
DataRow dataRow = dataTable.NewRow ( );
for ( int i = 0 ; i < reader.FieldCount ; i++ )
dataRow [ i ] = reader.GetValue ( i );
dataTable.Rows.Add ( dataRow );
}
}
else
{
// No records were returned
DataColumn column = new DataColumn ( "RowsAffected" );
dataTable.Columns.Add ( column );
dataSet.Tables.Add ( dataTable );
DataRow dataRow = dataTable.NewRow ( );
dataRow [ 0 ] = reader.RecordsAffected;
dataTable.Rows.Add ( dataRow );
}
}
while ( reader.NextResult ( ) );
return dataSet;
}
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt)
{
Dictionary <string, int> dictionary = new Dictionary <string, int>();
Dictionary <string, List <string> > dictionary2 = new Dictionary <string, List <string> >();
List <KeyInfo> list2 = new List <KeyInfo>();
this._stmt = stmt;
using (DataTable table = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in table.Rows)
{
dictionary.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"]));
}
}
using (DataTable table2 = reader.GetSchemaTable(false, false))
{
foreach (DataRow row2 in table2.Rows)
{
if (row2[SchemaTableOptionalColumn.BaseCatalogName] != DBNull.Value)
{
List <string> list;
string key = (string)row2[SchemaTableOptionalColumn.BaseCatalogName];
string item = (string)row2[SchemaTableColumn.BaseTableName];
if (!dictionary2.ContainsKey(key))
{
list = new List <string>();
dictionary2.Add(key, list);
}
else
{
list = dictionary2[key];
}
if (!list.Contains(item))
{
list.Add(item);
}
}
}
foreach (KeyValuePair <string, List <string> > pair in dictionary2)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string str3 = pair.Value[i];
DataRow row3 = null;
string[] restrictionValues = new string[3];
restrictionValues[0] = pair.Key;
restrictionValues[2] = str3;
using (DataTable table3 = cnn.GetSchema("Indexes", restrictionValues))
{
for (int j = 0; (j < 2) && (row3 == null); j++)
{
foreach (DataRow row4 in table3.Rows)
{
if ((j == 0) && ((bool)row4["PRIMARY_KEY"]))
{
row3 = row4;
break;
}
if ((j == 1) && ((bool)row4["UNIQUE"]))
{
row3 = row4;
break;
}
}
}
if (row3 == null)
{
pair.Value.RemoveAt(i);
i--;
}
else
{
string[] strArray3 = new string[3];
strArray3[0] = pair.Key;
strArray3[2] = str3;
using (DataTable table4 = cnn.GetSchema("Tables", strArray3))
{
int database = dictionary[pair.Key];
int rootPage = Convert.ToInt32(table4.Rows[0]["TABLE_ROOTPAGE"]);
int num5 = stmt._sql.GetCursorForTable(stmt, database, rootPage);
string[] strArray4 = new string[4];
strArray4[0] = pair.Key;
strArray4[2] = str3;
strArray4[3] = (string)row3["INDEX_NAME"];
using (DataTable table5 = cnn.GetSchema("IndexColumns", strArray4))
{
KeyQuery query = null;
List <string> list3 = new List <string>();
for (int k = 0; k < table5.Rows.Count; k++)
{
bool flag = true;
foreach (DataRow row5 in table2.Rows)
{
if ((!row5.IsNull(SchemaTableColumn.BaseColumnName) && (((string)row5[SchemaTableColumn.BaseColumnName]) == ((string)table5.Rows[k]["COLUMN_NAME"]))) && ((((string)row5[SchemaTableColumn.BaseTableName]) == str3) && (((string)row5[SchemaTableOptionalColumn.BaseCatalogName]) == pair.Key)))
{
table5.Rows.RemoveAt(k);
k--;
flag = false;
break;
}
}
if (flag)
{
list3.Add((string)table5.Rows[k]["COLUMN_NAME"]);
}
}
if ((((string)row3["INDEX_NAME"]) != ("sqlite_master_PK_" + str3)) && (list3.Count > 0))
{
string[] array = new string[list3.Count];
list3.CopyTo(array);
query = new KeyQuery(cnn, pair.Key, str3, array);
}
for (int m = 0; m < table5.Rows.Count; m++)
{
string str4 = (string)table5.Rows[m]["COLUMN_NAME"];
KeyInfo info = new KeyInfo {
rootPage = rootPage,
cursor = num5,
database = database,
databaseName = pair.Key,
tableName = str3,
columnName = str4,
query = query,
column = m
};
list2.Add(info);
}
}
}
}
}
}
}
}
this._keyInfo = new KeyInfo[list2.Count];
list2.CopyTo(this._keyInfo);
}
