1 /********************************************************
\r
2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
\r
3 * Written by Robert Simpson (robert@blackcastlesoft.com)
\r
5 * Released to the public domain, use at your own risk!
\r
6 ********************************************************/
\r
8 namespace Mono.Data.Sqlite
\r
12 using System.Data.Common;
\r
13 using System.Collections.Generic;
\r
14 using System.Globalization;
\r
15 using System.Reflection;
\r
18 /// SQLite implementation of DbDataReader.
\r
20 public sealed class SqliteDataReader : DbDataReader
\r
23 /// Underlying command this reader is attached to
\r
25 private SqliteCommand _command;
\r
27 /// Index of the current statement in the command being processed
\r
29 private int _activeStatementIndex;
\r
31 /// Current statement being Read()
\r
33 private SqliteStatement _activeStatement;
\r
35 /// State of the current statement being processed.
\r
36 /// -1 = First Step() executed, so the first Read() will be ignored
\r
37 /// 0 = Actively reading
\r
38 /// 1 = Finished reading
\r
39 /// 2 = Non-row-returning statement, no records
\r
41 private int _readingState;
\r
43 /// Number of records affected by the insert/update statements executed on the command
\r
45 private int _rowsAffected;
\r
47 /// Count of fields (columns) in the row-returning statement currently being processed
\r
49 private int _fieldCount;
\r
51 /// Datatypes of active fields (columns) in the current statement, used for type-restricting data
\r
53 private SQLiteType[] _fieldTypeArray;
\r
56 /// The behavior of the datareader
\r
58 private CommandBehavior _commandBehavior;
\r
61 /// If set, then dispose of the command object when the reader is finished
\r
63 internal bool _disposeCommand;
\r
66 /// An array of rowid's for the active statement if CommandBehavior.KeyInfo is specified
\r
68 private SqliteKeyReader _keyInfo;
\r
70 internal long _version; // Matches the version of the connection
\r
73 /// Internal constructor, initializes the datareader and sets up to begin executing statements
\r
75 /// <param name="cmd">The SqliteCommand this data reader is for</param>
\r
76 /// <param name="behave">The expected behavior of the data reader</param>
\r
77 internal SqliteDataReader(SqliteCommand cmd, CommandBehavior behave)
\r
80 _version = _command.Connection._version;
\r
82 _commandBehavior = behave;
\r
83 _activeStatementIndex = -1;
\r
84 _activeStatement = null;
\r
88 if (_command != null)
\r
92 internal void Cancel()
\r
98 /// Closes the datareader, potentially closing the connection as well if CommandBehavior.CloseConnection was specified.
\r
100 public override void Close()
\r
104 if (_command != null)
\r
110 // Make sure we've not been canceled
\r
115 while (NextResult())
\r
123 _command.ClearDataReader();
\r
127 // If the datareader's behavior includes closing the connection, then do so here.
\r
128 if ((_commandBehavior & CommandBehavior.CloseConnection) != 0 && _command.Connection != null) {
\r
129 // We need to call Dispose on the command before we call Dispose on the Connection,
\r
130 // otherwise we'll get a SQLITE_LOCKED exception.
\r
131 var conn = _command.Connection;
\r
132 _command.Dispose ();
\r
134 _disposeCommand = false;
\r
140 if (_disposeCommand)
\r
141 _command.Dispose();
\r
146 _activeStatement = null;
\r
147 _fieldTypeArray = null;
\r
151 if (_keyInfo != null)
\r
153 _keyInfo.Dispose();
\r
160 /// Throw an error if the datareader is closed
\r
162 private void CheckClosed()
\r
164 if (_command == null)
\r
165 throw new InvalidOperationException("DataReader has been closed");
\r
168 throw new SqliteException((int)SQLiteErrorCode.Abort, "Execution was aborted by the user");
\r
170 if (_command.Connection.State != ConnectionState.Open || _command.Connection._version != _version)
\r
171 throw new InvalidOperationException("Connection was closed, statement was terminated");
\r
175 /// Throw an error if a row is not loaded
\r
177 private void CheckValidRow()
\r
179 if (_readingState != 0)
\r
180 throw new InvalidOperationException("No current row");
\r
184 /// Enumerator support
\r
186 /// <returns>Returns a DbEnumerator object.</returns>
\r
187 public override global::System.Collections.IEnumerator GetEnumerator()
\r
189 return new DbEnumerator(this, ((_commandBehavior & CommandBehavior.CloseConnection) == CommandBehavior.CloseConnection));
\r
193 /// Not implemented. Returns 0
\r
195 public override int Depth
\r
205 /// Returns the number of columns in the current resultset
\r
207 public override int FieldCount
\r
212 if (_keyInfo == null)
\r
213 return _fieldCount;
\r
215 return _fieldCount + _keyInfo.Count;
\r
220 /// Returns the number of visible fielsd in the current resultset
\r
222 public override int VisibleFieldCount
\r
227 return _fieldCount;
\r
232 /// SQLite is inherently un-typed. All datatypes in SQLite are natively strings. The definition of the columns of a table
\r
233 /// and the affinity of returned types are all we have to go on to type-restrict data in the reader.
\r
235 /// This function attempts to verify that the type of data being requested of a column matches the datatype of the column. In
\r
236 /// the case of columns that are not backed into a table definition, we attempt to match up the affinity of a column (int, double, string or blob)
\r
237 /// to a set of known types that closely match that affinity. It's not an exact science, but its the best we can do.
\r
240 /// This function throws an InvalidTypeCast() exception if the requested type doesn't match the column's definition or affinity.
\r
242 /// <param name="i">The index of the column to type-check</param>
\r
243 /// <param name="typ">The type we want to get out of the column</param>
\r
244 private TypeAffinity VerifyType(int i, DbType typ)
\r
248 TypeAffinity affinity = GetSQLiteType(i).Affinity;
\r
252 case TypeAffinity.Int64:
\r
253 if (typ == DbType.Int16) return affinity;
\r
254 if (typ == DbType.Int32) return affinity;
\r
255 if (typ == DbType.Int64) return affinity;
\r
256 if (typ == DbType.Boolean) return affinity;
\r
257 if (typ == DbType.Byte) return affinity;
\r
258 if (typ == DbType.DateTime) return affinity;
\r
259 if (typ == DbType.Single) return affinity;
\r
260 if (typ == DbType.Double) return affinity;
\r
261 if (typ == DbType.Decimal) return affinity;
\r
263 case TypeAffinity.Double:
\r
264 if (typ == DbType.Single) return affinity;
\r
265 if (typ == DbType.Double) return affinity;
\r
266 if (typ == DbType.Decimal) return affinity;
\r
267 if (typ == DbType.DateTime) return affinity;
\r
269 case TypeAffinity.Text:
\r
270 if (typ == DbType.SByte) return affinity;
\r
271 if (typ == DbType.String) return affinity;
\r
272 if (typ == DbType.SByte) return affinity;
\r
273 if (typ == DbType.Guid) return affinity;
\r
274 if (typ == DbType.DateTime) return affinity;
\r
275 if (typ == DbType.Decimal) return affinity;
\r
277 case TypeAffinity.Blob:
\r
278 if (typ == DbType.Guid) return affinity;
\r
279 if (typ == DbType.String) return affinity;
\r
280 if (typ == DbType.Binary) return affinity;
\r
284 throw new InvalidCastException();
\r
288 /// Retrieves the column as a boolean value
\r
290 /// <param name="i">The index of the column to retrieve</param>
\r
291 /// <returns>bool</returns>
\r
292 public override bool GetBoolean(int i)
\r
294 if (i >= VisibleFieldCount && _keyInfo != null)
\r
295 return _keyInfo.GetBoolean(i - VisibleFieldCount);
\r
297 VerifyType(i, DbType.Boolean);
\r
298 return Convert.ToBoolean(GetValue(i), CultureInfo.CurrentCulture);
\r
302 /// Retrieves the column as a single byte value
\r
304 /// <param name="i">The index of the column to retrieve</param>
\r
305 /// <returns>byte</returns>
\r
306 public override byte GetByte(int i)
\r
308 if (i >= VisibleFieldCount && _keyInfo != null)
\r
309 return _keyInfo.GetByte(i - VisibleFieldCount);
\r
311 VerifyType(i, DbType.Byte);
\r
312 return Convert.ToByte(_activeStatement._sql.GetInt32(_activeStatement, i));
\r
316 /// Retrieves a column as an array of bytes (blob)
\r
318 /// <param name="i">The index of the column to retrieve</param>
\r
319 /// <param name="fieldOffset">The zero-based index of where to begin reading the data</param>
\r
320 /// <param name="buffer">The buffer to write the bytes into</param>
\r
321 /// <param name="bufferoffset">The zero-based index of where to begin writing into the array</param>
\r
322 /// <param name="length">The number of bytes to retrieve</param>
\r
323 /// <returns>The actual number of bytes written into the array</returns>
\r
325 /// To determine the number of bytes in the column, pass a null value for the buffer. The total length will be returned.
\r
327 public override long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
\r
329 if (i >= VisibleFieldCount && _keyInfo != null)
\r
330 return _keyInfo.GetBytes(i - VisibleFieldCount, fieldOffset, buffer, bufferoffset, length);
\r
332 VerifyType(i, DbType.Binary);
\r
333 return _activeStatement._sql.GetBytes(_activeStatement, i, (int)fieldOffset, buffer, bufferoffset, length);
\r
337 /// Returns the column as a single character
\r
339 /// <param name="i">The index of the column to retrieve</param>
\r
340 /// <returns>char</returns>
\r
341 public override char GetChar(int i)
\r
343 if (i >= VisibleFieldCount && _keyInfo != null)
\r
344 return _keyInfo.GetChar(i - VisibleFieldCount);
\r
346 VerifyType(i, DbType.SByte);
\r
347 return Convert.ToChar(_activeStatement._sql.GetInt32(_activeStatement, i));
\r
351 /// Retrieves a column as an array of chars (blob)
\r
353 /// <param name="i">The index of the column to retrieve</param>
\r
354 /// <param name="fieldoffset">The zero-based index of where to begin reading the data</param>
\r
355 /// <param name="buffer">The buffer to write the characters into</param>
\r
356 /// <param name="bufferoffset">The zero-based index of where to begin writing into the array</param>
\r
357 /// <param name="length">The number of bytes to retrieve</param>
\r
358 /// <returns>The actual number of characters written into the array</returns>
\r
360 /// To determine the number of characters in the column, pass a null value for the buffer. The total length will be returned.
\r
362 public override long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
\r
364 if (i >= VisibleFieldCount && _keyInfo != null)
\r
365 return _keyInfo.GetChars(i - VisibleFieldCount, fieldoffset, buffer, bufferoffset, length);
\r
367 VerifyType(i, DbType.String);
\r
368 return _activeStatement._sql.GetChars(_activeStatement, i, (int)fieldoffset, buffer, bufferoffset, length);
\r
372 /// Retrieves the name of the back-end datatype of the column
\r
374 /// <param name="i">The index of the column to retrieve</param>
\r
375 /// <returns>string</returns>
\r
376 public override string GetDataTypeName(int i)
\r
378 if (i >= VisibleFieldCount && _keyInfo != null)
\r
379 return _keyInfo.GetDataTypeName(i - VisibleFieldCount);
\r
381 SQLiteType typ = GetSQLiteType(i);
\r
382 if (typ.Type == DbType.Object) return SqliteConvert.SQLiteTypeToType(typ).Name;
\r
383 return _activeStatement._sql.ColumnType(_activeStatement, i, out typ.Affinity);
\r
387 /// Retrieve the column as a date/time value
\r
389 /// <param name="i">The index of the column to retrieve</param>
\r
390 /// <returns>DateTime</returns>
\r
391 public override DateTime GetDateTime(int i)
\r
393 if (i >= VisibleFieldCount && _keyInfo != null)
\r
394 return _keyInfo.GetDateTime(i - VisibleFieldCount);
\r
396 VerifyType(i, DbType.DateTime);
\r
397 return _activeStatement._sql.GetDateTime(_activeStatement, i);
\r
401 /// Retrieve the column as a decimal value
\r
403 /// <param name="i">The index of the column to retrieve</param>
\r
404 /// <returns>decimal</returns>
\r
405 public override decimal GetDecimal(int i)
\r
407 if (i >= VisibleFieldCount && _keyInfo != null)
\r
408 return _keyInfo.GetDecimal(i - VisibleFieldCount);
\r
410 VerifyType(i, DbType.Decimal);
\r
411 return Decimal.Parse(_activeStatement._sql.GetText(_activeStatement, i), NumberStyles.AllowDecimalPoint | NumberStyles.AllowExponent | NumberStyles.AllowLeadingSign, CultureInfo.InvariantCulture);
\r
415 /// Returns the column as a double
\r
417 /// <param name="i">The index of the column to retrieve</param>
\r
418 /// <returns>double</returns>
\r
419 public override double GetDouble(int i)
\r
421 if (i >= VisibleFieldCount && _keyInfo != null)
\r
422 return _keyInfo.GetDouble(i - VisibleFieldCount);
\r
424 VerifyType(i, DbType.Double);
\r
425 return _activeStatement._sql.GetDouble(_activeStatement, i);
\r
429 /// Returns the .NET type of a given column
\r
431 /// <param name="i">The index of the column to retrieve</param>
\r
432 /// <returns>Type</returns>
\r
433 public override Type GetFieldType(int i)
\r
435 if (i >= VisibleFieldCount && _keyInfo != null)
\r
436 return _keyInfo.GetFieldType(i - VisibleFieldCount);
\r
438 return SqliteConvert.SQLiteTypeToType(GetSQLiteType(i));
\r
442 /// Returns a column as a float value
\r
444 /// <param name="i">The index of the column to retrieve</param>
\r
445 /// <returns>float</returns>
\r
446 public override float GetFloat(int i)
\r
448 if (i >= VisibleFieldCount && _keyInfo != null)
\r
449 return _keyInfo.GetFloat(i - VisibleFieldCount);
\r
451 VerifyType(i, DbType.Single);
\r
452 return Convert.ToSingle(_activeStatement._sql.GetDouble(_activeStatement, i));
\r
456 /// Returns the column as a Guid
\r
458 /// <param name="i">The index of the column to retrieve</param>
\r
459 /// <returns>Guid</returns>
\r
460 public override Guid GetGuid(int i)
\r
462 if (i >= VisibleFieldCount && _keyInfo != null)
\r
463 return _keyInfo.GetGuid(i - VisibleFieldCount);
\r
465 TypeAffinity affinity = VerifyType(i, DbType.Guid);
\r
466 if (affinity == TypeAffinity.Blob)
\r
468 byte[] buffer = new byte[16];
\r
469 _activeStatement._sql.GetBytes(_activeStatement, i, 0, buffer, 0, 16);
\r
470 return new Guid(buffer);
\r
473 return new Guid(_activeStatement._sql.GetText(_activeStatement, i));
\r
477 /// Returns the column as a short
\r
479 /// <param name="i">The index of the column to retrieve</param>
\r
480 /// <returns>Int16</returns>
\r
481 public override Int16 GetInt16(int i)
\r
483 if (i >= VisibleFieldCount && _keyInfo != null)
\r
484 return _keyInfo.GetInt16(i - VisibleFieldCount);
\r
486 VerifyType(i, DbType.Int16);
\r
487 return Convert.ToInt16(_activeStatement._sql.GetInt32(_activeStatement, i));
\r
491 /// Retrieves the column as an int
\r
493 /// <param name="i">The index of the column to retrieve</param>
\r
494 /// <returns>Int32</returns>
\r
495 public override Int32 GetInt32(int i)
\r
497 if (i >= VisibleFieldCount && _keyInfo != null)
\r
498 return _keyInfo.GetInt32(i - VisibleFieldCount);
\r
500 VerifyType(i, DbType.Int32);
\r
501 return _activeStatement._sql.GetInt32(_activeStatement, i);
\r
505 /// Retrieves the column as a long
\r
507 /// <param name="i">The index of the column to retrieve</param>
\r
508 /// <returns>Int64</returns>
\r
509 public override Int64 GetInt64(int i)
\r
511 if (i >= VisibleFieldCount && _keyInfo != null)
\r
512 return _keyInfo.GetInt64(i - VisibleFieldCount);
\r
514 VerifyType(i, DbType.Int64);
\r
515 return _activeStatement._sql.GetInt64(_activeStatement, i);
\r
519 /// Retrieves the name of the column
\r
521 /// <param name="i">The index of the column to retrieve</param>
\r
522 /// <returns>string</returns>
\r
523 public override string GetName(int i)
\r
525 if (i >= VisibleFieldCount && _keyInfo != null)
\r
526 return _keyInfo.GetName(i - VisibleFieldCount);
\r
528 return _activeStatement._sql.ColumnName(_activeStatement, i);
\r
532 /// Retrieves the i of a column, given its name
\r
534 /// <param name="name">The name of the column to retrieve</param>
\r
535 /// <returns>The int i of the column</returns>
\r
536 public override int GetOrdinal(string name)
\r
539 int r = _activeStatement._sql.ColumnIndex(_activeStatement, name);
\r
540 if (r == -1 && _keyInfo != null)
\r
542 r = _keyInfo.GetOrdinal(name);
\r
543 if (r > -1) r += VisibleFieldCount;
\r
550 /// Schema information in SQLite is difficult to map into .NET conventions, so a lot of work must be done
\r
551 /// to gather the necessary information so it can be represented in an ADO.NET manner.
\r
553 /// <returns>Returns a DataTable containing the schema information for the active SELECT statement being processed.</returns>
\r
554 public override DataTable GetSchemaTable()
\r
556 return GetSchemaTable(true, false);
\r
559 internal DataTable GetSchemaTable(bool wantUniqueInfo, bool wantDefaultValue)
\r
563 DataTable tbl = new DataTable("SchemaTable");
\r
564 DataTable tblIndexes = null;
\r
565 DataTable tblIndexColumns;
\r
568 string strCatalog = "";
\r
569 string strTable = "";
\r
570 string strColumn = "";
\r
572 tbl.Locale = CultureInfo.InvariantCulture;
\r
573 tbl.Columns.Add(SchemaTableColumn.ColumnName, typeof(String));
\r
574 tbl.Columns.Add(SchemaTableColumn.ColumnOrdinal, typeof(int));
\r
575 tbl.Columns.Add(SchemaTableColumn.ColumnSize, typeof(int));
\r
576 tbl.Columns.Add(SchemaTableColumn.NumericPrecision, typeof(short));
\r
577 tbl.Columns.Add(SchemaTableColumn.NumericScale, typeof(short));
\r
578 tbl.Columns.Add(SchemaTableColumn.IsUnique, typeof(Boolean));
\r
579 tbl.Columns.Add(SchemaTableColumn.IsKey, typeof(Boolean));
\r
580 tbl.Columns.Add(SchemaTableOptionalColumn.BaseServerName, typeof(string));
\r
581 tbl.Columns.Add(SchemaTableOptionalColumn.BaseCatalogName, typeof(String));
\r
582 tbl.Columns.Add(SchemaTableColumn.BaseColumnName, typeof(String));
\r
583 tbl.Columns.Add(SchemaTableColumn.BaseSchemaName, typeof(String));
\r
584 tbl.Columns.Add(SchemaTableColumn.BaseTableName, typeof(String));
\r
585 tbl.Columns.Add(SchemaTableColumn.DataType, typeof(Type));
\r
586 tbl.Columns.Add(SchemaTableColumn.AllowDBNull, typeof(Boolean));
\r
587 tbl.Columns.Add(SchemaTableColumn.ProviderType, typeof(int));
\r
588 tbl.Columns.Add(SchemaTableColumn.IsAliased, typeof(Boolean));
\r
589 tbl.Columns.Add(SchemaTableColumn.IsExpression, typeof(Boolean));
\r
590 tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean));
\r
591 tbl.Columns.Add(SchemaTableOptionalColumn.IsRowVersion, typeof(Boolean));
\r
592 tbl.Columns.Add(SchemaTableOptionalColumn.IsHidden, typeof(Boolean));
\r
593 tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean));
\r
594 tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean));
\r
595 tbl.Columns.Add(SchemaTableOptionalColumn.ProviderSpecificDataType, typeof(Type));
\r
596 tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(object));
\r
597 tbl.Columns.Add("DataTypeName", typeof(string));
\r
598 tbl.Columns.Add("CollationType", typeof(string));
\r
599 tbl.BeginLoadData();
\r
601 for (int n = 0; n < _fieldCount; n++)
\r
603 row = tbl.NewRow();
\r
605 DbType typ = GetSQLiteType(n).Type;
\r
607 // Default settings for the column
\r
608 row[SchemaTableColumn.ColumnName] = GetName(n);
\r
609 row[SchemaTableColumn.ColumnOrdinal] = n;
\r
610 row[SchemaTableColumn.ColumnSize] = SqliteConvert.DbTypeToColumnSize(typ);
\r
611 row[SchemaTableColumn.NumericPrecision] = SqliteConvert.DbTypeToNumericPrecision(typ);
\r
612 row[SchemaTableColumn.NumericScale] = SqliteConvert.DbTypeToNumericScale(typ);
\r
613 row[SchemaTableColumn.ProviderType] = GetSQLiteType(n).Type;
\r
614 row[SchemaTableColumn.IsLong] = false;
\r
615 row[SchemaTableColumn.AllowDBNull] = true;
\r
616 row[SchemaTableOptionalColumn.IsReadOnly] = false;
\r
617 row[SchemaTableOptionalColumn.IsRowVersion] = false;
\r
618 row[SchemaTableColumn.IsUnique] = false;
\r
619 row[SchemaTableColumn.IsKey] = false;
\r
620 row[SchemaTableOptionalColumn.IsAutoIncrement] = false;
\r
621 row[SchemaTableColumn.DataType] = GetFieldType(n);
\r
622 row[SchemaTableOptionalColumn.IsHidden] = false;
\r
625 strColumn = _command.Connection._sql.ColumnOriginalName(_activeStatement, n);
\r
626 if (String.IsNullOrEmpty(strColumn) == false) row[SchemaTableColumn.BaseColumnName] = strColumn;
\r
628 row[SchemaTableColumn.IsExpression] = String.IsNullOrEmpty(strColumn);
\r
629 row[SchemaTableColumn.IsAliased] = (String.Compare(GetName(n), strColumn, true, CultureInfo.InvariantCulture) != 0);
\r
631 temp = _command.Connection._sql.ColumnTableName(_activeStatement, n);
\r
632 if (String.IsNullOrEmpty(temp) == false) row[SchemaTableColumn.BaseTableName] = temp;
\r
634 temp = _command.Connection._sql.ColumnDatabaseName(_activeStatement, n);
\r
635 if (String.IsNullOrEmpty(temp) == false) row[SchemaTableOptionalColumn.BaseCatalogName] = temp;
\r
638 string dataType = null;
\r
639 // If we have a table-bound column, extract the extra information from it
\r
640 if (String.IsNullOrEmpty(strColumn) == false)
\r
645 bool bAutoIncrement;
\r
648 // Get the column meta data
\r
649 _command.Connection._sql.ColumnMetaData(
\r
650 (string)row[SchemaTableOptionalColumn.BaseCatalogName],
\r
651 (string)row[SchemaTableColumn.BaseTableName],
\r
653 out dataType, out collSeq, out bNotNull, out bPrimaryKey, out bAutoIncrement);
\r
655 if (bNotNull || bPrimaryKey) row[SchemaTableColumn.AllowDBNull] = false;
\r
657 row[SchemaTableColumn.IsKey] = bPrimaryKey;
\r
658 row[SchemaTableOptionalColumn.IsAutoIncrement] = bAutoIncrement;
\r
659 row["CollationType"] = collSeq;
\r
661 // For types like varchar(50) and such, extract the size
\r
662 arSize = dataType.Split('(');
\r
663 if (arSize.Length > 1)
\r
665 dataType = arSize[0];
\r
666 arSize = arSize[1].Split(')');
\r
667 if (arSize.Length > 1)
\r
669 arSize = arSize[0].Split(',', '.');
\r
670 if (GetSQLiteType(n).Type == DbType.String || GetSQLiteType(n).Type == DbType.Binary)
\r
672 row[SchemaTableColumn.ColumnSize] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture);
\r
676 row[SchemaTableColumn.NumericPrecision] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture);
\r
677 if (arSize.Length > 1)
\r
678 row[SchemaTableColumn.NumericScale] = Convert.ToInt32(arSize[1], CultureInfo.InvariantCulture);
\r
683 if (wantDefaultValue)
\r
685 // Determine the default value for the column, which sucks because we have to query the schema for each column
\r
686 using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].TABLE_INFO([{1}])",
\r
687 row[SchemaTableOptionalColumn.BaseCatalogName],
\r
688 row[SchemaTableColumn.BaseTableName]
\r
689 ), _command.Connection))
\r
690 using (DbDataReader rdTable = cmdTable.ExecuteReader())
\r
692 // Find the matching column
\r
693 while (rdTable.Read())
\r
695 if (String.Compare((string)row[SchemaTableColumn.BaseColumnName], rdTable.GetString(1), true, CultureInfo.InvariantCulture) == 0)
\r
697 if (rdTable.IsDBNull(4) == false)
\r
698 row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
\r
706 // Determine IsUnique properly, which is a pain in the butt!
\r
707 if (wantUniqueInfo)
\r
709 if ((string)row[SchemaTableOptionalColumn.BaseCatalogName] != strCatalog
\r
710 || (string)row[SchemaTableColumn.BaseTableName] != strTable)
\r
712 strCatalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
\r
713 strTable = (string)row[SchemaTableColumn.BaseTableName];
\r
715 tblIndexes = _command.Connection.GetSchema("Indexes", new string[] {
\r
716 (string)row[SchemaTableOptionalColumn.BaseCatalogName],
\r
718 (string)row[SchemaTableColumn.BaseTableName],
\r
722 foreach (DataRow rowIndexes in tblIndexes.Rows)
\r
724 tblIndexColumns = _command.Connection.GetSchema("IndexColumns", new string[] {
\r
725 (string)row[SchemaTableOptionalColumn.BaseCatalogName],
\r
727 (string)row[SchemaTableColumn.BaseTableName],
\r
728 (string)rowIndexes["INDEX_NAME"],
\r
731 foreach (DataRow rowColumnIndex in tblIndexColumns.Rows)
\r
733 if (String.Compare((string)rowColumnIndex["COLUMN_NAME"], strColumn, true, CultureInfo.InvariantCulture) == 0)
\r
735 if (tblIndexColumns.Rows.Count == 1 && (bool)row[SchemaTableColumn.AllowDBNull] == false)
\r
736 row[SchemaTableColumn.IsUnique] = rowIndexes["UNIQUE"];
\r
738 // If its an integer primary key and the only primary key in the table, then its a rowid alias and is autoincrement
\r
739 // NOTE: Currently commented out because this is not always the desired behavior. For example, a 1:1 relationship with
\r
740 // another table, where the other table is autoincrement, but this one is not, and uses the rowid from the other.
\r
741 // It is safer to only set Autoincrement on tables where we're SURE the user specified AUTOINCREMENT, even if its a rowid column.
\r
743 if (tblIndexColumns.Rows.Count == 1 && (bool)rowIndexes["PRIMARY_KEY"] == true && String.IsNullOrEmpty(dataType) == false &&
\r
744 String.Compare(dataType, "integer", true, CultureInfo.InvariantCulture) == 0)
\r
746 // row[SchemaTableOptionalColumn.IsAutoIncrement] = true;
\r
755 if (String.IsNullOrEmpty(dataType))
\r
757 TypeAffinity affin;
\r
758 dataType = _activeStatement._sql.ColumnType(_activeStatement, n, out affin);
\r
761 if (String.IsNullOrEmpty(dataType) == false)
\r
762 row["DataTypeName"] = dataType;
\r
767 if (_keyInfo != null)
\r
768 _keyInfo.AppendSchemaTable(tbl);
\r
770 tbl.AcceptChanges();
\r
777 /// Retrieves the column as a string
\r
779 /// <param name="i">The index of the column to retrieve</param>
\r
780 /// <returns>string</returns>
\r
781 public override string GetString(int i)
\r
783 if (i >= VisibleFieldCount && _keyInfo != null)
\r
784 return _keyInfo.GetString(i - VisibleFieldCount);
\r
786 VerifyType(i, DbType.String);
\r
787 return _activeStatement._sql.GetText(_activeStatement, i);
\r
791 /// Retrieves the column as an object corresponding to the underlying datatype of the column
\r
793 /// <param name="i">The index of the column to retrieve</param>
\r
794 /// <returns>object</returns>
\r
795 public override object GetValue(int i)
\r
797 if (i >= VisibleFieldCount && _keyInfo != null)
\r
798 return _keyInfo.GetValue(i - VisibleFieldCount);
\r
800 SQLiteType typ = GetSQLiteType(i);
\r
802 return _activeStatement._sql.GetValue(_activeStatement, i, typ);
\r
806 /// Retreives the values of multiple columns, up to the size of the supplied array
\r
808 /// <param name="values">The array to fill with values from the columns in the current resultset</param>
\r
809 /// <returns>The number of columns retrieved</returns>
\r
810 public override int GetValues(object[] values)
\r
812 int nMax = FieldCount;
\r
813 if (values.Length < nMax) nMax = values.Length;
\r
815 for (int n = 0; n < nMax; n++)
\r
817 values[n] = GetValue(n);
\r
824 /// Returns True if the resultset has rows that can be fetched
\r
826 public override bool HasRows
\r
831 return (_readingState != 1);
\r
836 /// Returns True if the data reader is closed
\r
838 public override bool IsClosed
\r
840 get { return (_command == null); }
\r
844 /// Returns True if the specified column is null
\r
846 /// <param name="i">The index of the column to retrieve</param>
\r
847 /// <returns>True or False</returns>
\r
848 public override bool IsDBNull(int i)
\r
850 if (i >= VisibleFieldCount && _keyInfo != null)
\r
851 return _keyInfo.IsDBNull(i - VisibleFieldCount);
\r
853 return _activeStatement._sql.IsNull(_activeStatement, i);
\r
857 /// Moves to the next resultset in multiple row-returning SQL command.
\r
859 /// <returns>True if the command was successful and a new resultset is available, False otherwise.</returns>
\r
860 public override bool NextResult()
\r
864 SqliteStatement stmt = null;
\r
869 if (_activeStatement != null && stmt == null)
\r
871 // Reset the previously-executed statement
\r
872 _activeStatement._sql.Reset(_activeStatement);
\r
874 // If we're only supposed to return a single rowset, step through all remaining statements once until
\r
875 // they are all done and return false to indicate no more resultsets exist.
\r
876 if ((_commandBehavior & CommandBehavior.SingleResult) != 0)
\r
880 stmt = _command.GetStatement(_activeStatementIndex + 1);
\r
881 if (stmt == null) break;
\r
882 _activeStatementIndex++;
\r
884 stmt._sql.Step(stmt);
\r
885 if (stmt._sql.ColumnCount(stmt) == 0)
\r
887 if (_rowsAffected == -1) _rowsAffected = 0;
\r
888 _rowsAffected += stmt._sql.Changes;
\r
890 stmt._sql.Reset(stmt); // Gotta reset after every step to release any locks and such!
\r
896 // Get the next statement to execute
\r
897 stmt = _command.GetStatement(_activeStatementIndex + 1);
\r
899 // If we've reached the end of the statements, return false, no more resultsets
\r
903 // If we were on a current resultset, set the state to "done reading" for it
\r
904 if (_readingState < 1)
\r
907 _activeStatementIndex++;
\r
909 fieldCount = stmt._sql.ColumnCount(stmt);
\r
911 // If the statement is not a select statement or we're not retrieving schema only, then perform the initial step
\r
912 if ((_commandBehavior & CommandBehavior.SchemaOnly) == 0 || fieldCount == 0)
\r
914 if (stmt._sql.Step(stmt))
\r
916 _readingState = -1;
\r
918 else if (fieldCount == 0) // No rows returned, if fieldCount is zero, skip to the next statement
\r
920 if (_rowsAffected == -1) _rowsAffected = 0;
\r
921 _rowsAffected += stmt._sql.Changes;
\r
922 stmt._sql.Reset(stmt);
\r
923 continue; // Skip this command and move to the next, it was not a row-returning resultset
\r
925 else // No rows, fieldCount is non-zero so stop here
\r
927 _readingState = 1; // This command returned columns but no rows, so return true, but HasRows = false and Read() returns false
\r
931 // Ahh, we found a row-returning resultset eligible to be returned!
\r
932 _activeStatement = stmt;
\r
933 _fieldCount = fieldCount;
\r
934 _fieldTypeArray = null;
\r
936 if ((_commandBehavior & CommandBehavior.KeyInfo) != 0)
\r
944 /// Retrieves the SQLiteType for a given column, and caches it to avoid repetetive interop calls.
\r
946 /// <param name="i">The index of the column to retrieve</param>
\r
947 /// <returns>A SQLiteType structure</returns>
\r
948 private SQLiteType GetSQLiteType(int i)
\r
952 // Initialize the field types array if not already initialized
\r
953 if (_fieldTypeArray == null)
\r
954 _fieldTypeArray = new SQLiteType[VisibleFieldCount];
\r
956 // Initialize this column's field type instance
\r
957 if (_fieldTypeArray[i] == null) _fieldTypeArray[i] = new SQLiteType();
\r
959 typ = _fieldTypeArray[i];
\r
961 // If not initialized, then fetch the declared column datatype and attempt to convert it
\r
962 // to a known DbType.
\r
963 if (typ.Affinity == TypeAffinity.Uninitialized)
\r
964 typ.Type = SqliteConvert.TypeNameToDbType(_activeStatement._sql.ColumnType(_activeStatement, i, out typ.Affinity));
\r
966 typ.Affinity = _activeStatement._sql.ColumnAffinity(_activeStatement, i);
\r
972 /// Reads the next row from the resultset
\r
974 /// <returns>True if a new row was successfully loaded and is ready for processing</returns>
\r
975 public override bool Read()
\r
979 if (_readingState == -1) // First step was already done at the NextResult() level, so don't step again, just return true.
\r
984 else if (_readingState == 0) // Actively reading rows
\r
986 // Don't read a new row if the command behavior dictates SingleRow. We've already read the first row.
\r
987 if ((_commandBehavior & CommandBehavior.SingleRow) == 0)
\r
989 if (_activeStatement._sql.Step(_activeStatement) == true)
\r
991 if (_keyInfo != null)
\r
998 _readingState = 1; // Finished reading rows
\r
1005 /// Retrieve the count of records affected by an update/insert command. Only valid once the data reader is closed!
\r
1007 public override int RecordsAffected
\r
1009 get { return (_rowsAffected < 0) ? 0 : _rowsAffected; }
\r
1013 /// Indexer to retrieve data from a column given its name
\r
1015 /// <param name="name">The name of the column to retrieve data for</param>
\r
1016 /// <returns>The value contained in the column</returns>
\r
1017 public override object this[string name]
\r
1019 get { return GetValue(GetOrdinal(name)); }
\r
1023 /// Indexer to retrieve data from a column given its i
\r
1025 /// <param name="i">The index of the column to retrieve</param>
\r
1026 /// <returns>The value contained in the column</returns>
\r
1027 public override object this[int i]
\r
1029 get { return GetValue(i); }
\r
1032 private void LoadKeyInfo()
\r
1034 if (_keyInfo != null)
\r
1035 _keyInfo.Dispose();
\r
1037 _keyInfo = new SqliteKeyReader(_command.Connection, this, _activeStatement);
\r