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); }