| /trunk/Community.CsharpSqlite.SQLiteClient/src/SqliteCommand.cs |
|---|
| @@ -0,0 +1,531 @@ |
|---|
| // |
| // Community.CsharpSqlite.SQLiteClient.SqliteCommand.cs |
| // |
| // Represents a Transact-SQL statement or stored procedure to execute against |
| // a Sqlite database file. |
| // |
| // Author(s): Vladimir Vukicevic <vladimir@pobox.com> |
| // Everaldo Canuto <everaldo_canuto@yahoo.com.br> |
| // Chris Turchin <chris@turchin.net> |
| // Jeroen Zwartepoorte <jeroen@xs4all.nl> |
| // Thomas Zoechling <thomas.zoechling@gmx.at> |
| // Joshua Tauberer <tauberer@for.net> |
| // Noah Hart <noah.hart@gmail.com> |
| // |
| // Copyright (C) 2002 Vladimir Vukicevic |
| // |
| // Permission is hereby granted, free of charge, to any person obtaining |
| // a copy of this software and associated documentation files (the |
| // "Software"), to deal in the Software without restriction, including |
| // without limitation the rights to use, copy, modify, merge, publish, |
| // distribute, sublicense, and/or sell copies of the Software, and to |
| // permit persons to whom the Software is furnished to do so, subject to |
| // the following conditions: |
| // |
| // The above copyright notice and this permission notice shall be |
| // included in all copies or substantial portions of the Software. |
| // |
| // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, |
| // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF |
| // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND |
| // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE |
| // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION |
| // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION |
| // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| // |
| |
| using System; |
| using System.Text; |
| using System.Data; |
| using System.Data.Common; |
| using Community.CsharpSqlite; |
| using System.Globalization; |
| |
| namespace Community.CsharpSqlite.SQLiteClient |
| { |
| public class SqliteCommand : DbCommand, ICloneable |
| { |
| #region Fields |
| |
| private SqliteConnection parent_conn; |
| private SqliteTransaction transaction; |
| private string sql; |
| private int timeout; |
| private CommandType type; |
| #if !SQLITE_SILVERLIGHT |
| private UpdateRowSource upd_row_source; |
| #endif |
| private SqliteParameterCollection sql_params; |
| private bool prepared = false; |
| private bool _designTimeVisible = true; |
| |
| #endregion |
| |
| #region Constructors and destructors |
| |
| public SqliteCommand() |
| { |
| sql = string.Empty; |
| } |
| |
| public SqliteCommand( string sqlText ) |
| { |
| sql = sqlText; |
| } |
| |
| public SqliteCommand( string sqlText, SqliteConnection dbConn ) |
| { |
| sql = sqlText; |
| parent_conn = dbConn; |
| } |
| |
| public SqliteCommand( string sqlText, SqliteConnection dbConn, SqliteTransaction trans ) |
| { |
| sql = sqlText; |
| parent_conn = dbConn; |
| transaction = trans; |
| } |
| |
| #endregion |
| |
| #region Properties |
| |
| public override string CommandText |
| { |
| get |
| { |
| return sql; |
| } |
| set |
| { |
| sql = value; |
| prepared = false; |
| } |
| } |
| |
| public override int CommandTimeout |
| { |
| get |
| { |
| return timeout; |
| } |
| set |
| { |
| timeout = value; |
| } |
| } |
| |
| public override CommandType CommandType |
| { |
| get |
| { |
| return type; |
| } |
| set |
| { |
| type = value; |
| } |
| } |
| |
| protected override DbConnection DbConnection |
| { |
| get { return parent_conn; } |
| set { parent_conn = (SqliteConnection)value; } |
| } |
| |
| public new SqliteConnection Connection |
| { |
| get |
| { |
| return parent_conn; |
| } |
| set |
| { |
| parent_conn = (SqliteConnection)value; |
| } |
| } |
| |
| public new SqliteParameterCollection Parameters |
| { |
| get |
| { |
| if ( sql_params == null ) |
| sql_params = new SqliteParameterCollection(); |
| return sql_params; |
| } |
| } |
| |
| protected override DbParameterCollection DbParameterCollection |
| { |
| get { return Parameters; } |
| } |
| |
| protected override DbTransaction DbTransaction |
| { |
| get |
| { |
| return transaction; |
| } |
| set |
| { |
| transaction = (SqliteTransaction)value; |
| } |
| } |
| |
| public override bool DesignTimeVisible |
| { |
| get { return _designTimeVisible; } |
| set { _designTimeVisible = value; } |
| } |
| #if !SQLITE_SILVERLIGHT |
| public override UpdateRowSource UpdatedRowSource |
| { |
| get |
| { |
| return upd_row_source; |
| } |
| set |
| { |
| upd_row_source = value; |
| } |
| } |
| #endif |
| |
| #endregion |
| |
| #region Internal Methods |
| |
| internal int NumChanges() |
| { |
| //if (parent_conn.Version == 3) |
| return Sqlite3.sqlite3_changes( parent_conn.Handle2 ); |
| //else |
| // return Sqlite.sqlite_changes(parent_conn.Handle); |
| } |
| |
| private void BindParameters3( Sqlite3.Vdbe pStmt ) |
| { |
| if ( sql_params == null ) |
| return; |
| if ( sql_params.Count == 0 ) |
| return; |
| |
| int pcount = Sqlite3.sqlite3_bind_parameter_count( pStmt ); |
| |
| for ( int i = 1; i <= pcount; i++ ) |
| { |
| String name = Sqlite3.sqlite3_bind_parameter_name( pStmt, i ); |
| |
| SqliteParameter param = null; |
| if ( !string.IsNullOrEmpty( name ) ) |
| param = sql_params[name] as SqliteParameter; |
| else |
| param = sql_params[i - 1] as SqliteParameter; |
| |
| if ( param.Value == null ) |
| { |
| Sqlite3.sqlite3_bind_null( pStmt, i ); |
| continue; |
| } |
| |
| Type ptype = param.Value.GetType(); |
| if ( ptype.IsEnum ) |
| ptype = Enum.GetUnderlyingType( ptype ); |
| |
| SqliteError err; |
| |
| if ( ptype.Equals( typeof( String ) ) ) |
| { |
| String s = (String)param.Value; |
| err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, s, -1, null ); |
| } else if ( ptype.Equals( typeof( DBNull ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_null( pStmt, i ); |
| } else if ( ptype.Equals( typeof( Boolean ) ) ) |
| { |
| bool b = (bool)param.Value; |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, b ? 1 : 0 ); |
| } else if ( ptype.Equals( typeof( Byte ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Byte)param.Value ); |
| } else if ( ptype.Equals( typeof( Char ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Char)param.Value ); |
| } else if ( ptype.IsEnum ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Int32)param.Value ); |
| } else if ( ptype.Equals( typeof( Int16 ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Int16)param.Value ); |
| } else if ( ptype.Equals( typeof( Int32 ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Int32)param.Value ); |
| } else if ( ptype.Equals( typeof( SByte ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (SByte)param.Value ); |
| } else if ( ptype.Equals( typeof( UInt16 ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (UInt16)param.Value ); |
| } else if ( ptype.Equals( typeof( DateTime ) ) ) |
| { |
| DateTime dt = (DateTime)param.Value; |
| err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, dt.ToString( "yyyy-MM-dd HH:mm:ss.fff" ), -1, null ); |
| } else if ( ptype.Equals( typeof( Decimal ) ) ) |
| { |
| string val = ( (Decimal)param.Value ).ToString( CultureInfo.InvariantCulture ); |
| err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, val, val.Length, null ); |
| } else if ( ptype.Equals( typeof( Double ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_double( pStmt, i, (Double)param.Value ); |
| } else if ( ptype.Equals( typeof( Single ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_double( pStmt, i, (Single)param.Value ); |
| } else if ( ptype.Equals( typeof( UInt32 ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int64( pStmt, i, (UInt32)param.Value ); |
| } else if ( ptype.Equals( typeof( Int64 ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_int64( pStmt, i, (Int64)param.Value ); |
| } else if ( ptype.Equals( typeof( Byte[] ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_blob( pStmt, i, (byte[])param.Value, ( (byte[])param.Value ).Length, null ); |
| } else if ( ptype.Equals( typeof( Guid ) ) ) |
| { |
| err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, param.Value.ToString(), param.Value.ToString().Length, null ); |
| } else |
| { |
| throw new ApplicationException( "Unknown Parameter Type" ); |
| } |
| if ( err != SqliteError.OK ) |
| { |
| throw new ApplicationException( "Sqlite error in bind " + err ); |
| } |
| } |
| } |
| |
| private void GetNextStatement( string pzStart, ref string pzTail, ref Sqlite3.Vdbe pStmt ) |
| { |
| SqliteError err = (SqliteError)Sqlite3.sqlite3_prepare_v2( parent_conn.Handle2, pzStart, pzStart.Length, ref pStmt, ref pzTail ); |
| if ( err != SqliteError.OK ) |
| throw new SqliteSyntaxException( parent_conn.Handle2.errCode, GetError3() ); |
| } |
| |
| // Executes a statement and ignores its result. |
| private void ExecuteStatement( Sqlite3.Vdbe pStmt ) |
| { |
| int cols; |
| IntPtr pazValue, pazColName; |
| ExecuteStatement( pStmt, out cols, out pazValue, out pazColName ); |
| } |
| |
| // Executes a statement and returns whether there is more data available. |
| internal bool ExecuteStatement( Sqlite3.Vdbe pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName ) |
| { |
| SqliteError err; |
| |
| //if (parent_conn.Version == 3) |
| //{ |
| err = (SqliteError)Sqlite3.sqlite3_step( pStmt ); |
| |
| if ( err == SqliteError.ERROR ) |
| throw new SqliteExecutionException(parent_conn.Handle2.errCode, GetError3() + "\n" + pStmt.zErrMsg ); |
| |
| pazValue = IntPtr.Zero; |
| pazColName = IntPtr.Zero; // not used for v=3 |
| cols = Sqlite3.sqlite3_column_count( pStmt ); |
| |
| /* |
| } |
| else |
| { |
| err = (SqliteError)Sqlite3.sqlite3_step(pStmt, out cols, out pazValue, out pazColName); |
| if (err == SqliteError.ERROR) |
| throw new SqliteExecutionException (); |
| } |
| */ |
| if ( err == SqliteError.BUSY ) |
| throw new SqliteBusyException(); |
| |
| if ( err == SqliteError.MISUSE ) |
| throw new SqliteExecutionException(); |
| |
| // err is either ROW or DONE. |
| return err == SqliteError.ROW; |
| } |
| |
| #endregion |
| |
| #region Public Methods |
| |
| object ICloneable.Clone() |
| { |
| return new SqliteCommand( sql, parent_conn, transaction ); |
| } |
| |
| public override void Cancel() |
| { |
| } |
| |
| public override void Prepare() |
| { |
| // There isn't much we can do here. If a table schema |
| // changes after preparing a statement, Sqlite bails, |
| // so we can only compile statements right before we |
| // want to run them. |
| |
| if ( prepared ) |
| return; |
| prepared = true; |
| } |
| |
| protected override DbParameter CreateDbParameter () |
| { |
| return new SqliteParameter(); |
| } |
| |
| public override int ExecuteNonQuery() |
| { |
| int rows_affected; |
| ExecuteReader( CommandBehavior.Default, false, out rows_affected ); |
| return rows_affected; |
| } |
| |
| public override object ExecuteScalar() |
| { |
| SqliteDataReader r = (SqliteDataReader)ExecuteReader(); |
| if ( r == null || !r.Read() ) |
| { |
| return null; |
| } |
| object o = r[0]; |
| r.Close(); |
| return o; |
| } |
| |
| public new SqliteDataReader ExecuteReader( CommandBehavior behavior ) |
| { |
| int r; |
| return ExecuteReader( behavior, true, out r ); |
| } |
| |
| public new SqliteDataReader ExecuteReader () |
| { |
| return ExecuteReader (CommandBehavior.Default); |
| } |
| |
| protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior) |
| { |
| return ExecuteReader (behavior); |
| } |
| |
| public SqliteDataReader ExecuteReader( CommandBehavior behavior, bool want_results, out int rows_affected ) |
| { |
| Prepare(); |
| |
| // The SQL string may contain multiple sql commands, so the main |
| // thing to do is have Sqlite iterate through the commands. |
| // If want_results, only the last command is returned as a |
| // DataReader. Otherwise, no command is returned as a |
| // DataReader. |
| |
| //IntPtr psql; // pointer to SQL command |
| |
| // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit |
| // character (iso8859). But if you give the --enable-utf8 option to the configure script, then the |
| // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB |
| // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set |
| // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h |
| // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate. |
| // |
| // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the |
| // default. Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI. |
| // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless |
| // of what Sqlite is treating them as, |
| |
| // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string. |
| /* |
| if (parent_conn.Version == 2) |
| psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding); |
| else |
| psql = Marshal.StringToHGlobalUni (sql.Trim()); |
| */ |
| string pzTail = sql.Trim(); |
| |
| parent_conn.StartExec(); |
| |
| rows_affected = 0; |
| |
| try |
| { |
| while ( true ) |
| { |
| Sqlite3.Vdbe pStmt = null; |
| |
| string queryval = pzTail; |
| GetNextStatement( queryval, ref pzTail, ref pStmt ); |
| |
| if ( pStmt == null ) |
| throw new Exception(); |
| |
| // pzTail is positioned after the last byte in the |
| // statement, which will be the NULL character if |
| // this was the last statement. |
| bool last = pzTail.Length == 0; |
| |
| try |
| { |
| if ( parent_conn.Version == 3 ) |
| BindParameters3( pStmt ); |
| |
| if ( last && want_results ) |
| return new SqliteDataReader( this, pStmt, parent_conn.Version ); |
| |
| ExecuteStatement( pStmt ); |
| |
| if ( last ) // rows_affected is only used if !want_results |
| rows_affected = NumChanges(); |
| |
| } |
| finally |
| { |
| //if (parent_conn.Version == 3) |
| Sqlite3.sqlite3_finalize( pStmt ); |
| //else |
| // Sqlite.sqlite_finalize (pStmt, out errMsgPtr); |
| } |
| |
| if ( last ) |
| break; |
| } |
| |
| return null; |
| } |
| //alxwest: Console.WriteLine in shared functionality. |
| //catch ( Exception ex ) |
| //{ |
| // Console.WriteLine( ex.Message ); |
| // return null; |
| //} |
| finally |
| { |
| parent_conn.EndExec(); |
| //Marshal.FreeHGlobal (psql); |
| } |
| } |
| |
| public int LastInsertRowID() |
| { |
| return parent_conn.LastInsertRowId; |
| } |
| public string GetLastError() |
| { |
| return Sqlite3.sqlite3_errmsg( parent_conn.Handle2 ); |
| } |
| private string GetError3() |
| { |
| return Sqlite3.sqlite3_errmsg( parent_conn.Handle2 ); |
| //return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle)); |
| } |
| #endregion |
| } |
| } |
| /trunk/Community.CsharpSqlite.SQLiteClient/src/SqliteCommandBuilder.cs |
|---|
| @@ -0,0 +1,522 @@ |
|---|
| // |
| // Community.CsharpSqlite.SQLiteClient.SqliteCommandBuilder.cs |
| // |
| // Author(s): Tim Coleman (tim@timcoleman.com) |
| // Marek Habersack (grendello@gmail.com) |
| // |
| // Copyright (C) Tim Coleman, 2002 |
| // Copyright (C) 2004 Novell, Inc (http://www.novell.com) |
| // Copyright (C) 2007 Marek Habersack |
| // |
| // Permission is hereby granted, free of charge, to any person obtaining |
| // a copy of this software and associated documentation files (the |
| // "Software"), to deal in the Software without restriction, including |
| // without limitation the rights to use, copy, modify, merge, publish, |
| // distribute, sublicense, and/or sell copies of the Software, and to |
| // permit persons to whom the Software is furnished to do so, subject to |
| // the following conditions: |
| // |
| // The above copyright notice and this permission notice shall be |
| // included in all copies or substantial portions of the Software. |
| // |
| // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, |
| // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF |
| // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND |
| // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE |
| // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION |
| // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION |
| // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| // |
| |
| using System; |
| using System.Data; |
| using System.Data.Common; |
| using System.Text; |
| |
| namespace Community.CsharpSqlite.SQLiteClient |
| { |
| public sealed class SqliteCommandBuilder : DbCommandBuilder |
| { |
| static readonly string clause1 = "({0} = 1 AND {1} IS NULL)"; |
| static readonly string clause2 = "({0} = {1})"; |
| |
| DataTable _schemaTable; |
| SqliteDataAdapter _dataAdapter; |
| SqliteCommand _insertCommand; |
| SqliteCommand _updateCommand; |
| SqliteCommand _deleteCommand; |
| bool _disposed; |
| string _quotePrefix = "'"; |
| string _quoteSuffix = "'"; |
| string _tableName; |
| SqliteRowUpdatingEventHandler rowUpdatingHandler; |
| |
| public new DbDataAdapter DataAdapter { |
| get { return _dataAdapter; } |
| set { |
| if (_dataAdapter != null) |
| _dataAdapter.RowUpdating -= new SqliteRowUpdatingEventHandler (RowUpdatingHandler); |
| _dataAdapter = value as SqliteDataAdapter; |
| if (_dataAdapter != null) |
| _dataAdapter.RowUpdating += new SqliteRowUpdatingEventHandler (RowUpdatingHandler); |
| } |
| } |
| |
| public override string QuotePrefix { |
| get { return _quotePrefix; } |
| |
| set { |
| if (_schemaTable != null) |
| throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update or Delete commands have been generated."); |
| _quotePrefix = value; |
| } |
| } |
| |
| public override string QuoteSuffix { |
| get { return _quoteSuffix; } |
| |
| set { |
| if (_schemaTable != null) |
| throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update or Delete commands have been generated."); |
| _quoteSuffix = value; |
| } |
| } |
| |
| private SqliteCommand SourceCommand { |
| get { |
| if (_dataAdapter != null) |
| return _dataAdapter.SelectCommand as SqliteCommand; |
| return null; |
| } |
| } |
| |
| private string QuotedTableName { |
| get { return GetQuotedString (_tableName); } |
| } |
| |
| public new SqliteCommand GetDeleteCommand () |
| { |
| BuildCache (true); |
| if (_deleteCommand == null) |
| return CreateDeleteCommand (false); |
| return _deleteCommand; |
| } |
| |
| public new SqliteCommand GetInsertCommand () |
| { |
| BuildCache (true); |
| if (_insertCommand == null) |
| return CreateInsertCommand (false); |
| return _insertCommand; |
| } |
| |
| public new SqliteCommand GetUpdateCommand () |
| { |
| BuildCache (true); |
| if (_updateCommand == null) |
| return CreateUpdateCommand (false); |
| return _updateCommand; |
| } |
| |
| public override void RefreshSchema () |
| { |
| // FIXME: "Figure out what else needs to be cleaned up when we refresh." |
| _tableName = String.Empty; |
| _schemaTable = null; |
| CreateNewCommand (ref _deleteCommand); |
| CreateNewCommand (ref _updateCommand); |
| CreateNewCommand (ref _insertCommand); |
| } |
| |
| protected override void SetRowUpdatingHandler (DbDataAdapter adapter) |
| { |
| if (!(adapter is SqliteDataAdapter)) { |
| throw new InvalidOperationException ("Adapter needs to be a SqliteDataAdapter"); |
| } |
| rowUpdatingHandler = new SqliteRowUpdatingEventHandler (RowUpdatingHandler); |
| ((SqliteDataAdapter) adapter).RowUpdating += rowUpdatingHandler; |
| } |
| |
| protected override void ApplyParameterInfo (DbParameter dbParameter, |
| DataRow row, |
| StatementType statementType, |
| bool whereClause) |
| { |
| // Nothing to do here |
| } |
| |
| protected override string GetParameterName (int position) |
| { |
| return String.Format ("?p{0}", position); |
| } |
| |
| protected override string GetParameterName (string parameterName) |
| { |
| if (string.IsNullOrEmpty(parameterName)) |
| throw new ArgumentException ("parameterName cannot be null or empty"); |
| if (parameterName [0] == '?') |
| return parameterName; |
| return String.Format ("?{0}", parameterName); |
| } |
| |
| |
| protected override string GetParameterPlaceholder (int position) |
| { |
| return String.Format ("?p{0}", position); |
| } |
| |
| protected override void Dispose (bool disposing) |
| { |
| if (!_disposed) { |
| if (disposing) { |
| if (_insertCommand != null) |
| _insertCommand.Dispose (); |
| if (_deleteCommand != null) |
| _deleteCommand.Dispose (); |
| if (_updateCommand != null) |
| _updateCommand.Dispose (); |
| if (_schemaTable != null) |
| _schemaTable.Dispose (); |
| } |
| _disposed = true; |
| } |
| } |
| |
| 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); |
| } |
| } |
| |
| private void BuildInformation (DataTable schemaTable) |
| { |
| _tableName = String.Empty; |
| foreach (DataRow schemaRow in schemaTable.Rows) { |
| if (schemaRow.IsNull ("BaseTableName") || |
| (string) schemaRow ["BaseTableName"] == String.Empty) |
| continue; |
| |
| if (_tableName == String.Empty) |
| _tableName = (string) schemaRow ["BaseTableName"]; |
| else if (_tableName != (string) schemaRow["BaseTableName"]) |
| throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables."); |
| } |
| if (_tableName == String.Empty) |
| throw new InvalidOperationException ("Dynamic SQL generation is not supported with no base table."); |
| _schemaTable = schemaTable; |
| } |
| |
| private SqliteCommand CreateInsertCommand (bool option) |
| { |
| if (QuotedTableName == String.Empty) |
| return null; |
| |
| CreateNewCommand (ref _insertCommand); |
| |
| string command = String.Format ("INSERT INTO {0}", QuotedTableName); |
| string sql; |
| StringBuilder columns = new StringBuilder (); |
| StringBuilder values = new StringBuilder (); |
| |
| int parmIndex = 1; |
| foreach (DataRow schemaRow in _schemaTable.Rows) { |
| if (!IncludedInInsert (schemaRow)) |
| continue; |
| |
| if (parmIndex > 1) { |
| columns.Append (", "); |
| values.Append (", "); |
| } |
| |
| SqliteParameter parameter = null; |
| if (option) { |
| parameter = _insertCommand.Parameters.Add (CreateParameter (schemaRow)); |
| } else { |
| parameter = _insertCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow)); |
| } |
| parameter.SourceVersion = DataRowVersion.Current; |
| columns.Append (GetQuotedString (parameter.SourceColumn)); |
| values.Append (parameter.ParameterName); |
| } |
| |
| sql = String.Format ("{0} ({1}) VALUES ({2})", command, columns.ToString (), values.ToString ()); |
| _insertCommand.CommandText = sql; |
| return _insertCommand; |
| } |
| |
| private SqliteCommand CreateDeleteCommand (bool option) |
| { |
| // If no table was found, then we can't do an delete |
| if (QuotedTableName == String.Empty) |
| return null; |
| |
| CreateNewCommand (ref _deleteCommand); |
| |
| string command = String.Format ("DELETE FROM {0}", QuotedTableName); |
| StringBuilder whereClause = new StringBuilder (); |
| bool keyFound = false; |
| int parmIndex = 1; |
| |
| foreach (DataRow schemaRow in _schemaTable.Rows) { |
| if ((bool)schemaRow["IsExpression"] == true) |
| continue; |
| if (!IncludedInWhereClause (schemaRow)) |
| continue; |
| |
| if (whereClause.Length > 0) |
| whereClause.Append (" AND "); |
| |
| bool isKey = (bool) schemaRow ["IsKey"]; |
| SqliteParameter parameter = null; |
| |
| if (isKey) |
| keyFound = true; |
| |
| bool allowNull = (bool) schemaRow ["AllowDBNull"]; |
| if (!isKey && allowNull) { |
| if (option) { |
| parameter = _deleteCommand.Parameters.Add ( |
| String.Format ("@{0}", schemaRow ["BaseColumnName"]), DbType.Int32); |
| } else { |
| parameter = _deleteCommand.Parameters.Add ( |
| String.Format ("@p{0}", parmIndex++), DbType.Int32); |
| } |
| String sourceColumnName = (string) schemaRow ["BaseColumnName"]; |
| parameter.Value = 1; |
| |
| whereClause.Append ("("); |
| whereClause.Append (String.Format (clause1, parameter.ParameterName, |
| GetQuotedString (sourceColumnName))); |
| whereClause.Append (" OR "); |
| } |
| |
| if (option) { |
| parameter = _deleteCommand.Parameters.Add (CreateParameter (schemaRow)); |
| } else { |
| parameter = _deleteCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow)); |
| } |
| parameter.SourceVersion = DataRowVersion.Original; |
| |
| whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), |
| parameter.ParameterName)); |
| |
| if (!isKey && allowNull) |
| whereClause.Append (")"); |
| } |
| if (!keyFound) |
| throw new InvalidOperationException ("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information."); |
| |
| string sql = String.Format ("{0} WHERE ({1})", command, whereClause.ToString ()); |
| _deleteCommand.CommandText = sql; |
| return _deleteCommand; |
| } |
| |
| private SqliteCommand CreateUpdateCommand (bool option) |
| { |
| if (QuotedTableName == String.Empty) |
| return null; |
| |
| CreateNewCommand (ref _updateCommand); |
| |
| string command = String.Format ("UPDATE {0} SET ", QuotedTableName); |
| StringBuilder columns = new StringBuilder (); |
| StringBuilder whereClause = new StringBuilder (); |
| int parmIndex = 1; |
| bool keyFound = false; |
| |
| foreach (DataRow schemaRow in _schemaTable.Rows) { |
| if (!IncludedInUpdate (schemaRow)) |
| continue; |
| if (columns.Length > 0) |
| columns.Append (", "); |
| |
| SqliteParameter parameter = null; |
| if (option) { |
| parameter = _updateCommand.Parameters.Add (CreateParameter (schemaRow)); |
| } else { |
| parameter = _updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow)); |
| } |
| parameter.SourceVersion = DataRowVersion.Current; |
| |
| columns.Append (String.Format ("{0} = {1}", GetQuotedString (parameter.SourceColumn), |
| parameter.ParameterName)); |
| } |
| |
| foreach (DataRow schemaRow in _schemaTable.Rows) { |
| if ((bool)schemaRow["IsExpression"] == true) |
| continue; |
| |
| if (!IncludedInWhereClause (schemaRow)) |
| continue; |
| |
| if (whereClause.Length > 0) |
| whereClause.Append (" AND "); |
| |
| bool isKey = (bool) schemaRow ["IsKey"]; |
| SqliteParameter parameter = null; |
| |
| if (isKey) |
| keyFound = true; |
| |
| bool allowNull = (bool) schemaRow ["AllowDBNull"]; |
| if (!isKey && allowNull) { |
| if (option) { |
| parameter = _updateCommand.Parameters.Add ( |
| String.Format ("@{0}", schemaRow ["BaseColumnName"]), SqlDbType.Int); |
| } else { |
| parameter = _updateCommand.Parameters.Add ( |
| String.Format ("@p{0}", parmIndex++), SqlDbType.Int); |
| } |
| parameter.Value = 1; |
| whereClause.Append ("("); |
| whereClause.Append (String.Format (clause1, parameter.ParameterName, |
| GetQuotedString ((string) schemaRow ["BaseColumnName"]))); |
| whereClause.Append (" OR "); |
| } |
| |
| if (option) { |
| parameter = _updateCommand.Parameters.Add (CreateParameter (schemaRow)); |
| } else { |
| parameter = _updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow)); |
| } |
| parameter.SourceVersion = DataRowVersion.Original; |
| whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), |
| parameter.ParameterName)); |
| |
| if (!isKey && allowNull) |
| whereClause.Append (")"); |
| } |
| if (!keyFound) |
| throw new InvalidOperationException ("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."); |
| |
| string sql = String.Format ("{0}{1} WHERE ({2})", command, columns.ToString (), whereClause.ToString ()); |
| _updateCommand.CommandText = sql; |
| return _updateCommand; |
| } |
| |
| private void CreateNewCommand (ref SqliteCommand command) |
| { |
| SqliteCommand sourceCommand = SourceCommand; |
| if (command == null) { |
| command = sourceCommand.Connection.CreateCommand () as SqliteCommand; |
| command.CommandTimeout = sourceCommand.CommandTimeout; |
| command.Transaction = sourceCommand.Transaction; |
| } |
| command.CommandType = CommandType.Text; |
| command.UpdatedRowSource = UpdateRowSource.None; |
| command.Parameters.Clear (); |
| } |
| |
| private bool IncludedInWhereClause (DataRow schemaRow) |
| { |
| if ((bool) schemaRow ["IsLong"]) |
| return false; |
| return true; |
| } |
| |
| private bool IncludedInInsert (DataRow schemaRow) |
| { |
| // not all of the below are supported by Sqlite, but we leave them here anyway, since some day Sqlite may |
| // support some of them. |
| if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"]) |
| return false; |
| if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"]) |
| return false; |
| if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"]) |
| return false; |
| if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"]) |
| return false; |
| if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"]) |
| return false; |
| return true; |
| } |
| |
| private bool IncludedInUpdate (DataRow schemaRow) |
| { |
| // not all of the below are supported by Sqlite, but we leave them here anyway, since some day Sqlite may |
| // support some of them. |
| if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"]) |
| return false; |
| if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"]) |
| return false; |
| if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"]) |
| return false; |
| if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"]) |
| return false; |
| if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"]) |
| return false; |
| |
| return true; |
| } |
| |
| private SqliteParameter CreateParameter (DataRow schemaRow) |
| { |
| string sourceColumn = (string) schemaRow ["BaseColumnName"]; |
| string name = String.Format ("@{0}", sourceColumn); |
| DbType dbType = (DbType) schemaRow ["ProviderType"]; |
| int size = (int) schemaRow ["ColumnSize"]; |
| |
| return new SqliteParameter (name, dbType, size, sourceColumn); |
| } |
| |
| private SqliteParameter CreateParameter (int parmIndex, DataRow schemaRow) |
| { |
| string name = String.Format ("@p{0}", parmIndex); |
| string sourceColumn = (string) schemaRow ["BaseColumnName"]; |
| DbType dbType = (DbType) schemaRow ["ProviderType"]; |
| int size = (int) schemaRow ["ColumnSize"]; |
| |
| return new SqliteParameter (name, dbType, size, sourceColumn); |
| } |
| |
| private string GetQuotedString (string value) |
| { |
| if (value == String.Empty || value == null) |
| return value; |
| if (string.IsNullOrEmpty(_quotePrefix) && string.IsNullOrEmpty(_quoteSuffix)) |
| return value; |
| return String.Format ("{0}{1}{2}", _quotePrefix, value, _quoteSuffix); |
| } |
| |
| private void RowUpdatingHandler (object sender, RowUpdatingEventArgs args) |
| { |
| if (args.Command != null) |
| return; |
| try { |
| switch (args.StatementType) { |
| case StatementType.Insert: |
| args.Command = GetInsertCommand (); |
| break; |
| case StatementType.Update: |
| args.Command = GetUpdateCommand (); |
| break; |
| case StatementType.Delete: |
| args.Command = GetDeleteCommand (); |
| break; |
| } |
| } catch (Exception e) { |
| args.Errors = e; |
| args.Status = UpdateStatus.ErrorsOccurred; |
| } |
| } |
| } |
| } |
| /trunk/Community.CsharpSqlite.SQLiteClient/src/SqliteConnection.cs |
|---|
| @@ -0,0 +1,875 @@ |
|---|
| // |
| // Community.CsharpSqlite.SQLiteClient.SqliteConnection.cs |
| // |
| // Represents an open connection to a Sqlite database file. |
| // |
| // Author(s): Vladimir Vukicevic <vladimir@pobox.com> |
| // Everaldo Canuto <everaldo_canuto@yahoo.com.br> |
| // Daniel Morgan <monodanmorg@yahoo.com> |
| // Noah Hart <Noah.Hart@gmail.com> |
| // Stewart Adcock <stewart.adcock@medit.fr> |
| // |
| // Copyright (C) 2002 Vladimir Vukicevic |
| // |
| // Permission is hereby granted, free of charge, to any person obtaining |
| // a copy of this software and associated documentation files (the |
| // "Software"), to deal in the Software without restriction, including |
| // without limitation the rights to use, copy, modify, merge, publish, |
| // distribute, sublicense, and/or sell copies of the Software, and to |
| // permit persons to whom the Software is furnished to do so, subject to |
| // the following conditions: |
| // |
| // The above copyright notice and this permission notice shall be |
| // included in all copies or substantial portions of the Software. |
| // |
| // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, |
| // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF |
| // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND |
| // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE |
| // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION |
| // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION |
| // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| // |
| |
| using System; |
| using System.Data; |
| using System.IO; |
| using System.Data.Common; |
| using System.Text; |
| using Community.CsharpSqlite; |
| |
| namespace Community.CsharpSqlite.SQLiteClient |
| { |
| /// <summary> |
| /// Represents an open connection to a SQLite3 database. |
| /// </summary> |
| /// <remarks> |
| /// This only supports SQLite version 3, NOT version 2. |
| /// </remarks> |
| public class SqliteConnection : DbConnection, ICloneable |
| { |
| |
| #region Fields |
| |
| private string conn_str; |
| private string db_file; |
| private int db_version; |
| private int db_BusyTimeout; |
| private string db_password; |
| private bool db_IsReadonly; |
| private bool db_IsFailIfMissing; |
| private Encoding db_Encoding; // This is ignored for SQLIte3. |
| private IntPtr sqlite_handle; |
| private Sqlite3.sqlite3 sqlite_handle2; |
| private ConnectionState state; |
| private bool disposed; |
| |
| #endregion |
| |
| #region Constructors and destructors |
| |
| public SqliteConnection () |
| { |
| db_file = null; |
| db_version = 3; |
| state = ConnectionState.Closed; |
| sqlite_handle = IntPtr.Zero; |
| db_Encoding = null; |
| db_BusyTimeout = 0; |
| } |
| |
| public SqliteConnection (string connstring) : this () |
| { |
| ConnectionString = connstring; |
| } |
| |
| protected override void Dispose (bool disposing) |
| { |
| try { |
| if (disposing && !disposed) { |
| Close (); |
| conn_str = null; |
| } |
| } finally { |
| disposed = true; |
| base.Dispose (disposing); |
| } |
| } |
| |
| #endregion |
| |
| #region Properties |
| |
| public override string ConnectionString { |
| get { return conn_str; } |
| set { SetConnectionString(value); } |
| } |
| |
| public override int ConnectionTimeout { |
| get { return 0; } |
| } |
| |
| public override string Database { |
| get { return db_file; } |
| } |
| |
| public override ConnectionState State { |
| get { return state; } |
| } |
| |
| [Obsolete("Only meaningful for SQLite2 which is unsupported.")] |
| public Encoding Encoding { |
| get { return db_Encoding; } |
| } |
| |
| public int Version { |
| get { return db_version; } |
| } |
| |
| public override string ServerVersion |
| { |
| get { return Sqlite3.sqlite3_libversion(); } |
| } |
| |
| internal Sqlite3.sqlite3 Handle2 |
| { |
| get { return sqlite_handle2; } |
| } |
| |
| internal IntPtr Handle { |
| get { return sqlite_handle; } |
| } |
| |
| public override string DataSource { |
| get { return db_file; } |
| } |
| |
| public int LastInsertRowId { |
| get { |
| return (int) Sqlite3.sqlite3_last_insert_rowid(Handle2); |
| } |
| } |
| |
| public int BusyTimeout { |
| get { |
| return db_BusyTimeout; |
| } |
| set { |
| db_BusyTimeout = value < 0 ? 0 : value; |
| } |
| } |
| |
| #endregion |
| |
| #region Private Methods |
| private void SetConnectionString (string connstring) |
| { |
| if (connstring == null) { |
| Close (); |
| conn_str = null; |
| return; |
| } |
| |
| if (connstring != conn_str) { |
| Close (); |
| conn_str = connstring; |
| |
| db_file = null; |
| db_IsReadonly = false; |
| db_IsFailIfMissing = false; |
| |
| string[] conn_pieces = connstring.Split (new char[]{',',';'}, StringSplitOptions.RemoveEmptyEntries); |
| for (int i = 0; i < conn_pieces.Length; i++) { |
| string piece = conn_pieces [i].Trim (); |
| int firstEqual = piece.IndexOf ('='); |
| if (firstEqual == -1) { |
| throw new InvalidOperationException ("Invalid connection string"); |
| } |
| string token = piece.Substring (0, firstEqual); |
| string tvalue = piece.Remove (0, firstEqual + 1).Trim (); |
| string tvalue_lc = tvalue.ToLower (System.Globalization.CultureInfo.InvariantCulture).Trim (); |
| switch (token.ToLower (System.Globalization.CultureInfo.InvariantCulture).Trim ()) { |
| case "data source": |
| case "uri": |
| if (tvalue_lc.StartsWith ("file://")) { |
| db_file = tvalue.Substring (7); |
| } else if (tvalue_lc.StartsWith ("file:")) { |
| db_file = tvalue.Substring (5); |
| } else if (tvalue_lc.StartsWith ("/")) { |
| db_file = tvalue; |
| #if !(SQLITE_SILVERLIGHT || WINDOWS_MOBILE) |
| } else if (tvalue_lc.StartsWith ("|DataDirectory|", |
| StringComparison.OrdinalIgnoreCase)) { |
| AppDomainSetup ads = AppDomain.CurrentDomain.SetupInformation; |
| string filePath = String.Format ("App_Data{0}{1}", |
| Path.DirectorySeparatorChar, |
| tvalue_lc.Substring (15)); |
| |
| db_file = Path.Combine (ads.ApplicationBase, filePath); |
| #endif |
| } else { |
| #if !WINDOWS_PHONE |
| throw new InvalidOperationException ("Invalid connection string: invalid URI"); |
| #else |
| db_file = tvalue; |
| #endif |
| } |
| break; |
| |
| case "mode": // Ignored for SQLite3. |
| ////int db_mode = Convert.ToInt32 (tvalue); |
| break; |
| |
| case "version": |
| db_version = Convert.ToInt32 (tvalue); |
| if (db_version < 3) |
| throw new InvalidOperationException ("Minimum database version is 3"); |
| break; |
| |
| case "encoding": // Ignored for SQLite3. |
| db_Encoding = Encoding.GetEncoding (tvalue); |
| break; |
| |
| case "busy_timeout": |
| db_BusyTimeout = Convert.ToInt32 (tvalue); |
| break; |
| |
| case "read only": |
| case "readonly": |
| db_IsReadonly = ConvertStringToBoolean (tvalue.ToLowerInvariant()); |
| break; |
| |
| case "failifmissing": |
| db_IsFailIfMissing = ConvertStringToBoolean (tvalue.ToLowerInvariant()); |
| break; |
| |
| case "password": |
| if (!string.IsNullOrEmpty (db_password) && (db_password.Length != 34 || !db_password.StartsWith ("0x"))) |
| throw new InvalidOperationException ("Invalid password string: must be 34 hex digits starting with 0x"); |
| db_password = tvalue; |
| break; |
| } |
| } |
| |
| if (db_file == null) { |
| throw new InvalidOperationException ("Invalid connection string: no URI"); |
| } |
| } |
| } |
| |
| /// <summary> |
| /// Convert the specified string to a boolean value. |
| /// </summary> |
| /// <remarks> |
| /// The string must be one of "true", "yes" (converted to <c>true<c/c>), |
| /// "false", "no" (converted to <c>false<c/c>). |
| /// </remarks> |
| /// <exception cref="ArgumentNullException">Thrown if string is null.</exception> |
| /// <exception cref="ArgumentException">Thrown if string is not converted to valid boolean.</exception> |
| private static bool ConvertStringToBoolean (string value) |
| { |
| if (value == null) |
| throw new ArgumentNullException ("null value cannot be converted to boolean"); |
| if (value == "yes" || value == "true") |
| return true; |
| if (value == "no" || value == "false") |
| return false; |
| throw new ArgumentException (string.Format ("Invalid boolean value: \"{0}\"", value)); |
| } |
| #endregion |
| |
| #region Internal Methods |
| |
| internal void StartExec () |
| { |
| // use a mutex here |
| state = ConnectionState.Executing; |
| } |
| |
| internal void EndExec () |
| { |
| state = ConnectionState.Open; |
| } |
| |
| #endregion |
| |
| #region Public Methods |
| |
| object ICloneable.Clone () |
| { |
| return new SqliteConnection (ConnectionString); |
| } |
| |
| protected override DbTransaction BeginDbTransaction (IsolationLevel il) |
| { |
| if (state != ConnectionState.Open) |
| throw new InvalidOperationException("Invalid operation: The connection is closed"); |
| |
| SqliteTransaction t = new SqliteTransaction(); |
| t.SetConnection (this); |
| SqliteCommand cmd = (SqliteCommand)this.CreateCommand(); |
| cmd.CommandText = "BEGIN"; |
| cmd.ExecuteNonQuery(); |
| return t; |
| } |
| |
| public new DbTransaction BeginTransaction () |
| { |
| return BeginDbTransaction (IsolationLevel.Unspecified); |
| } |
| |
| public new DbTransaction BeginTransaction (IsolationLevel il) |
| { |
| return BeginDbTransaction (il); |
| } |
| |
| public override void Close () |
| { |
| if (state != ConnectionState.Open) { |
| return; |
| } |
| |
| state = ConnectionState.Closed; |
| |
| if (Version == 3) |
| Sqlite3.sqlite3_close (sqlite_handle2); |
| sqlite_handle = IntPtr.Zero; |
| } |
| |
| public override void ChangeDatabase (string databaseName) |
| { |
| Close (); |
| db_file = databaseName; |
| Open (); |
| } |
| |
| protected override DbCommand CreateDbCommand () |
| { |
| return new SqliteCommand (null, this); |
| } |
| |
| /// <summary> |
| /// Opens the connection using the parameters provided by the <see cref="ConnectionString">ConnectionString</see>. |
| /// </summary> |
| /// <exception cref="InvalidOperationException">Thrown if no database was specified.</exception> |
| /// <exception cref="InvalidOperationException">Thrown if the connection stater is not closed.</exception> |
| /// <exception cref="ApplicationException">Thrown if a database error occurred.</exception> |
| public override void Open () |
| { |
| if (conn_str == null) { |
| throw new InvalidOperationException ("No database specified"); |
| } |
| |
| if (state != ConnectionState.Closed) { |
| throw new InvalidOperationException ("Connection state is not closed."); |
| } |
| |
| if (Version == 3) { |
| int flags = Sqlite3.SQLITE_OPEN_NOMUTEX; |
| if (!db_IsFailIfMissing && !db_IsReadonly) |
| flags |= Sqlite3.SQLITE_OPEN_CREATE; |
| if (db_IsReadonly) { |
| flags |= Sqlite3.SQLITE_OPEN_READONLY; |
| } else { |
| flags |= Sqlite3.SQLITE_OPEN_READWRITE; |
| } |
| |
| sqlite_handle = (IntPtr)1; |
| int err = Sqlite3.sqlite3_open_v2( db_file, out sqlite_handle2, flags, null ); |
| if (err == (int)SqliteError.ERROR) |
| throw new ApplicationException (Sqlite3.sqlite3_errmsg(sqlite_handle2)); |
| if (db_BusyTimeout != 0) |
| Sqlite3.sqlite3_busy_timeout(sqlite_handle2, db_BusyTimeout); |
| if ( !string.IsNullOrEmpty( db_password ) ) |
| { |
| SqliteCommand cmd = (SqliteCommand)this.CreateCommand(); |
| cmd.CommandText = "pragma hexkey='" + db_password + "'"; |
| cmd.ExecuteNonQuery(); |
| } |
| } |
| state = ConnectionState.Open; |
| } |
| |
| #if !SQLITE_SILVERLIGHT |
| public override DataTable GetSchema( String collectionName ) |
| { |
| return GetSchema( collectionName, null ); |
| } |
| |
| public override DataTable GetSchema( String collectionName, string[] restrictionValues ) |
| { |
| if ( State != ConnectionState.Open ) |
| throw new InvalidOperationException( "Invalid operation. The connection is closed." ); |
| |
| int restrictionsCount = 0; |
| if ( restrictionValues != null ) |
| restrictionsCount = restrictionValues.Length; |
| |
| DataTable metaTable = GetSchemaMetaDataCollections(); |
| foreach ( DataRow row in metaTable.Rows ) |
| { |
| if ( String.Compare( row["CollectionName"].ToString(), collectionName, true ) == 0 ) |
| { |
| int restrictions = (int)row["NumberOfRestrictions"]; |
| if ( restrictionsCount > restrictions ) |
| throw new ArgumentException( "More restrictions were provided than needed." ); |
| } |
| } |
| |
| switch ( collectionName.ToUpper() ) |
| { |
| case "METADATACOLLECTIONS": |
| return metaTable; |
| case "DATASOURCEINFORMATION": |
| return GetSchemaDataSourceInformation(); |
| case "DATATYPES": |
| return GetSchemaDataTypes(); |
| case "RESTRICTIONS": |
| return GetSchemaRestrictions(); |
| case "RESERVEDWORDS": |
| return GetSchemaReservedWords(); |
| case "TABLES": |
| return GetSchemaTables( restrictionValues ); |
| case "COLUMNS": |
| return GetSchemaColumns( restrictionValues ); |
| case "VIEWS": |
| return GetSchemaViews( restrictionValues ); |
| case "INDEXCOLUMNS": |
| return GetSchemaIndexColumns( restrictionValues ); |
| case "INDEXES": |
| return GetSchemaIndexes( restrictionValues ); |
| case "UNIQUEKEYS": |
| throw new NotImplementedException( collectionName ); |
| case "PRIMARYKEYS": |
| throw new NotImplementedException( collectionName ); |
| case "FOREIGNKEYS": |
| return GetSchemaForeignKeys( restrictionValues ); |
| case "FOREIGNKEYCOLUMNS": |
| throw new NotImplementedException( collectionName ); |
| case "TRIGGERS": |
| return GetSchemaTriggers( restrictionValues ); |
| } |
| |
| throw new ArgumentException( "The requested collection is not defined." ); |
| } |
| |
| static DataTable metaDataCollections = null; |
| DataTable GetSchemaMetaDataCollections() |
| { |
| if ( metaDataCollections != null ) |
| return metaDataCollections; |
| |
| DataTable dt = new DataTable(); |
| |
| dt.Columns.Add( "CollectionName", typeof( System.String ) ); |
| dt.Columns.Add( "NumberOfRestrictions", typeof( System.Int32 ) ); |
| dt.Columns.Add( "NumberOfIdentifierParts", typeof( System.Int32 ) ); |
| |
| dt.LoadDataRow( new object[] { "MetaDataCollections", 0, 0 }, true ); |
| dt.LoadDataRow( new object[] { "DataSourceInformation", 0, 0 }, true ); |
| dt.LoadDataRow( new object[] { "DataTypes", 0, 0 }, true ); |
| dt.LoadDataRow( new object[] { "Restrictions", 0, 0 }, true ); |
| dt.LoadDataRow( new object[] { "ReservedWords", 0, 0 }, true ); |
| dt.LoadDataRow( new object[] { "Tables", 1, 1 }, true ); |
| dt.LoadDataRow( new object[] { "Columns", 1, 1 }, true ); |
| dt.LoadDataRow( new object[] { "Views", 1, 1 }, true ); |
| dt.LoadDataRow( new object[] { "IndexColumns", 1, 1 }, true ); |
| dt.LoadDataRow( new object[] { "Indexes", 1, 1 }, true ); |
| //dt.LoadDataRow(new object[] { "UniqueKeys", 1, 1 }, true); |
| //dt.LoadDataRow(new object[] { "PrimaryKeys", 1, 1 }, true); |
| dt.LoadDataRow( new object[] { "ForeignKeys", 1, 1 }, true ); |
| //dt.LoadDataRow(new object[] { "ForeignKeyColumns", 1, 1 }, true); |
| dt.LoadDataRow( new object[] { "Triggers", 1, 1 }, true ); |
| |
| return dt; |
| } |
| |
| DataTable GetSchemaRestrictions() |
| { |
| DataTable dt = new DataTable(); |
| |
| dt.Columns.Add( "CollectionName", typeof( System.String ) ); |
| dt.Columns.Add( "RestrictionName", typeof( System.String ) ); |
| dt.Columns.Add( "ParameterName", typeof( System.String ) ); |
| dt.Columns.Add( "RestrictionDefault", typeof( System.String ) ); |
| dt.Columns.Add( "RestrictionNumber", typeof( System.Int32 ) ); |
| |
| dt.LoadDataRow( new object[] { "Tables", "Table", "TABLENAME", "TABLE_NAME", 1 }, true ); |
| dt.LoadDataRow( new object[] { "Columns", "Table", "TABLENAME", "TABLE_NAME", 1 }, true ); |
| dt.LoadDataRow( new object[] { "Views", "View", "VIEWNAME", "VIEW_NAME", 1 }, true ); |
| dt.LoadDataRow( new object[] { "IndexColumns", "Name", "NAME", "INDEX_NAME", 1 }, true ); |
| dt.LoadDataRow( new object[] { "Indexes", "TableName", "TABLENAME", "TABLE_NAME", 1 }, true ); |
| dt.LoadDataRow( new object[] { "ForeignKeys", "Foreign_Key_Table_Name", "TABLENAME", "TABLE_NAME", 1 }, true ); |
| dt.LoadDataRow( new object[] { "Triggers", "TableName", "TABLENAME", "TABLE_NAME", 1 }, true ); |
| |
| return dt; |
| } |
| |
| DataTable GetSchemaTables( string[] restrictionValues ) |
| { |
| SqliteCommand cmd = new SqliteCommand( |
| "SELECT type, name, tbl_name, rootpage, sql " + |
| " FROM sqlite_master " + |
| " WHERE (name = :pname or (:pname is null)) " + |
| " AND type = 'table' " + |
| " ORDER BY name", this ); |
| cmd.Parameters.Add( "pname", DbType.String ).Value = DBNull.Value; |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| DataTable GetSchemaColumns( string[] restrictionValues ) |
| { |
| if ( restrictionValues == null || restrictionValues.Length == 0 ) |
| { |
| throw new ArgumentException( "Columns must contain at least one restriction value for the table name." ); |
| } |
| ValidateIdentifier( restrictionValues[0] ); |
| |
| SqliteCommand cmd = (SqliteCommand)CreateCommand(); |
| cmd.CommandText = string.Format( "PRAGMA table_info({0})", restrictionValues[0] ); |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| DataTable GetSchemaTriggers( string[] restrictionValues ) |
| { |
| SqliteCommand cmd = new SqliteCommand( |
| "SELECT type, name, tbl_name, rootpage, sql " + |
| " FROM sqlite_master " + |
| " WHERE (tbl_name = :pname or :pname is null) " + |
| " AND type = 'trigger' " + |
| " ORDER BY name", this ); |
| cmd.Parameters.Add( "pname", DbType.String ).Value = DBNull.Value; |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| DataTable GetSchemaIndexColumns( string[] restrictionValues ) |
| { |
| if ( restrictionValues == null || restrictionValues.Length == 0 ) |
| { |
| throw new ArgumentException( "IndexColumns must contain at least one restriction value for the index name." ); |
| } |
| ValidateIdentifier( restrictionValues[0] ); |
| |
| SqliteCommand cmd = (SqliteCommand)CreateCommand(); |
| cmd.CommandText = string.Format( "PRAGMA index_info({0})", restrictionValues[0] ); |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| DataTable GetSchemaIndexes( string[] restrictionValues ) |
| { |
| if ( restrictionValues == null || restrictionValues.Length == 0 ) |
| { |
| throw new ArgumentException( "Indexes must contain at least one restriction value for the table name." ); |
| } |
| ValidateIdentifier( restrictionValues[0] ); |
| |
| SqliteCommand cmd = (SqliteCommand)CreateCommand(); |
| cmd.CommandText = string.Format( "PRAGMA index_list({0})", restrictionValues[0] ); |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| DataTable GetSchemaForeignKeys( string[] restrictionValues ) |
| { |
| if ( restrictionValues == null || restrictionValues.Length == 0 ) |
| { |
| throw new ArgumentException( "Foreign Keys must contain at least one restriction value for the table name." ); |
| } |
| ValidateIdentifier( restrictionValues[0] ); |
| |
| SqliteCommand cmd = (SqliteCommand)CreateCommand(); |
| cmd.CommandText = string.Format( "PRAGMA foreign_key_list({0})", restrictionValues[0] ); |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| #endif |
| void ValidateIdentifier( string value ) |
| { |
| if ( value.Contains( "'" ) ) |
| throw new ArgumentException( "Identifiers can not contain a single quote." ); |
| } |
| |
| #if !SQLITE_SILVERLIGHT |
| DataTable GetSchemaViews( string[] restrictionValues ) |
| { |
| SqliteCommand cmd = new SqliteCommand( |
| "SELECT type, name, tbl_name, rootpage, sql " + |
| " FROM sqlite_master " + |
| " WHERE (name = :pname or :pname is null) " + |
| " AND type = 'view' " + |
| " ORDER BY name", this ); |
| cmd.Parameters.Add( "pname", DbType.String ).Value = DBNull.Value; |
| return GetSchemaDataTable( cmd, restrictionValues ); |
| } |
| |
| DataTable GetSchemaDataSourceInformation() |
| { |
| DataTable dt = new DataTable(); |
| |
| dt.Columns.Add( "CompositeIdentifierSeparatorPattern", typeof( System.String ) ); |
| dt.Columns.Add( "DataSourceProductName", typeof( System.String ) ); |
| dt.Columns.Add( "DataSourceProductVersion", typeof( System.String ) ); |
| dt.Columns.Add( "DataSourceProductVersionNormalized", typeof( System.String ) ); |
| #if !WINDOWS_MOBILE |
| dt.Columns.Add( "GroupByBehavior", typeof( System.Data.Common.GroupByBehavior ) ); |
| #else |
| dt.Columns.Add("GroupByBehavior", typeof(object)); |
| #endif |
| dt.Columns.Add( "IdentifierPattern", typeof( System.String ) ); |
| #if !WINDOWS_MOBILE |
| dt.Columns.Add( "IdentifierCase", typeof( System.Data.Common.IdentifierCase ) ); |
| #else |
| dt.Columns.Add("IdentifierCase", typeof(object )); |
| #endif |
| dt.Columns.Add( "OrderByColumnsInSelect", typeof( System.Boolean ) ); |
| dt.Columns.Add( "ParameterMarkerFormat", typeof( System.String ) ); |
| dt.Columns.Add( "ParameterMarkerPattern", typeof( System.String ) ); |
| dt.Columns.Add( "ParameterNameMaxLength", typeof( System.Int32 ) ); |
| dt.Columns.Add( "ParameterNamePattern", typeof( System.String ) ); |
| dt.Columns.Add( "QuotedIdentifierPattern", typeof( System.String ) ); |
| #if !WINDOWS_MOBILE |
| dt.Columns.Add( "QuotedIdentifierCase", typeof( System.Data.Common.IdentifierCase ) ); |
| #else |
| dt.Columns.Add("QuotedIdentifierCase", typeof(object)); |
| #endif |
| dt.Columns.Add( "StatementSeparatorPattern", typeof( System.String ) ); |
| dt.Columns.Add( "StringLiteralPattern", typeof( System.String ) ); |
| #if !WINDOWS_MOBILE |
| dt.Columns.Add( "SupportedJoinOperators", typeof( System.Data.Common.SupportedJoinOperators ) ); |
| #else |
| dt.Columns.Add("SupportedJoinOperators", typeof(object )); |
| #endif |
| |
| // TODO: set correctly |
| dt.LoadDataRow( new object[] { "", |
| "SQLite", |
| ServerVersion, |
| ServerVersion, |
| 3, |
| "", |
| 1, |
| false, |
| "", |
| "", |
| 30, |
| "", |
| 2, |
| DBNull.Value, |
| "" }, |
| true ); |
| |
| return dt; |
| } |
| |
| DataTable GetSchemaDataTypes() |
| { |
| DataTable dt = new DataTable(); |
| |
| dt.Columns.Add( "TypeName", typeof( System.String ) ); |
| dt.Columns.Add( "ProviderDbType", typeof( System.String ) ); |
| dt.Columns.Add( "StorageType", typeof( System.Int32 ) ); |
| dt.Columns.Add( "DataType", typeof( System.String ) ); |
| // TODO: fill the rest of these |
| /* |
| dt.Columns.Add("ColumnSize", typeof(System.Int64)); |
| dt.Columns.Add("CreateFormat", typeof(System.String)); |
| dt.Columns.Add("CreateParameters", typeof(System.String)); |
| dt.Columns.Add("IsAutoIncrementable",typeof(System.Boolean)); |
| dt.Columns.Add("IsBestMatch", typeof(System.Boolean)); |
| dt.Columns.Add("IsCaseSensitive", typeof(System.Boolean)); |
| dt.Columns.Add("IsFixedLength", typeof(System.Boolean)); |
| dt.Columns.Add("IsFixedPrecisionScale",typeof(System.Boolean)); |
| dt.Columns.Add("IsLong", typeof(System.Boolean)); |
| dt.Columns.Add("IsNullable", typeof(System.Boolean)); |
| dt.Columns.Add("IsSearchable", typeof(System.Boolean)); |
| dt.Columns.Add("IsSearchableWithLike",typeof(System.Boolean)); |
| dt.Columns.Add("IsUnsigned", typeof(System.Boolean)); |
| dt.Columns.Add("MaximumScale", typeof(System.Int16)); |
| dt.Columns.Add("MinimumScale", typeof(System.Int16)); |
| dt.Columns.Add("IsConcurrencyType",typeof(System.Boolean)); |
| dt.Columns.Add("IsLiteralSupported",typeof(System.Boolean)); |
| dt.Columns.Add("LiteralPrefix", typeof(System.String)); |
| dt.Columns.Add("LiteralSuffix", typeof(System.String)); |
| */ |
| |
| dt.LoadDataRow( new object[] { "INT", "INTEGER", 1, "System.Int32" }, true ); |
| dt.LoadDataRow( new object[] { "INTEGER", "INTEGER", 1, "System.Int32" }, true ); |
| dt.LoadDataRow( new object[] { "TINYINT", "INTEGER", 1, "System.Byte" }, true ); |
| dt.LoadDataRow( new object[] { "SMALLINT", "INTEGER", 1, "System.Int16" }, true ); |
| dt.LoadDataRow( new object[] { "MEDIUMINT", "INTEGER", 1, "System.Int32" }, true ); |
| dt.LoadDataRow( new object[] { "BIGINT", "INTEGER", 1, "System.Int64" }, true ); |
| dt.LoadDataRow( new object[] { "UNSIGNED BIGINT", "INTEGER", 1, "System.UInt64" }, true ); |
| dt.LoadDataRow( new object[] { "INT2", "INTEGER", 1, "System.Int16" }, true ); |
| dt.LoadDataRow( new object[] { "INT8", "INTEGER", 1, "System.Int64" }, true ); |
| |
| dt.LoadDataRow( new object[] { "CHARACTER", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "VARCHAR", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "VARYING CHARACTER", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "NCHAR", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "NATIVE CHARACTER", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "NVARHCAR", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "TEXT", "TEXT", 2, "System.String" }, true ); |
| dt.LoadDataRow( new object[] { "CLOB", "TEXT", 2, "System.String" }, true ); |
| |
| dt.LoadDataRow( new object[] { "BLOB", "NONE", 3, "System.Byte[]" }, true ); |
| |
| dt.LoadDataRow( new object[] { "REAL", "REAL", 4, "System.Double" }, true ); |
| dt.LoadDataRow( new object[] { "DOUBLE", "REAL", 4, "System.Double" }, true ); |
| dt.LoadDataRow( new object[] { "DOUBLE PRECISION", "REAL", 4, "System.Double" }, true ); |
| dt.LoadDataRow( new object[] { "FLOAT", "REAL", 4, "System.Double" }, true ); |
| |
| dt.LoadDataRow( new object[] { "NUMERIC", "NUMERIC", 5, "System.Decimal" }, true ); |
| dt.LoadDataRow( new object[] { "DECIMAL", "NUMERIC", 5, "System.Decimal" }, true ); |
| dt.LoadDataRow( new object[] { "BOOLEAN", "NUMERIC", 5, "System.Boolean" }, true ); |
| dt.LoadDataRow( new object[] { "DATE", "NUMERIC", 5, "System.DateTime" }, true ); |
| dt.LoadDataRow( new object[] { "DATETIME", "NUMERIC", 5, "System.DateTime" }, true ); |
| |
| return dt; |
| } |
| |
| DataTable GetSchemaReservedWords() |
| { |
| DataTable dt = new DataTable(); |
| |
| dt.Columns.Add( "ReservedWord", typeof( System.String ) ); |
| |
| dt.LoadDataRow( new object[] { "ABORT" }, true ); |
| dt.LoadDataRow( new object[] { "ACTION" }, true ); |
| dt.LoadDataRow( new object[] { "ADD" }, true ); |
| dt.LoadDataRow( new object[] { "AFTER" }, true ); |
| dt.LoadDataRow( new object[] { "ALL" }, true ); |
| dt.LoadDataRow( new object[] { "ANALYZE" }, true ); |
| dt.LoadDataRow( new object[] { "AND" }, true ); |
| dt.LoadDataRow( new object[] { "AS" }, true ); |
| dt.LoadDataRow( new object[] { "ATTACH" }, true ); |
| dt.LoadDataRow( new object[] { "AUTOINCREMENT" }, true ); |
| dt.LoadDataRow( new object[] { "BEFORE" }, true ); |
| dt.LoadDataRow( new object[] { "BEFORE" }, true ); |
| dt.LoadDataRow( new object[] { "BEGIN" }, true ); |
| dt.LoadDataRow( new object[] { "BETWEEN" }, true ); |
| dt.LoadDataRow( new object[] { "BY" }, true ); |
| dt.LoadDataRow( new object[] { "CASCADE" }, true ); |
| dt.LoadDataRow( new object[] { "CASE" }, true ); |
| dt.LoadDataRow( new object[] { "CAST" }, true ); |
| dt.LoadDataRow( new object[] { "CHECK" }, true ); |
| dt.LoadDataRow( new object[] { "COLLATE" }, true ); |
| dt.LoadDataRow( new object[] { "COLUMN" }, true ); |
| dt.LoadDataRow( new object[] { "COMMIT" }, true ); |
| dt.LoadDataRow( new object[] { "CONFLICT" }, true ); |
| dt.LoadDataRow( new object[] { "CONTRAINT" }, true ); |
| dt.LoadDataRow( new object[] { "CREATE" }, true ); |
| dt.LoadDataRow( new object[] { "CROSS" }, true ); |
| dt.LoadDataRow( new object[] { "CURRENT_DATE" }, true ); |
| dt.LoadDataRow( new object[] { "CURRENT_TIME" }, true ); |
| dt.LoadDataRow( new object[] { "CURRENT_TIMESTAMP" }, true ); |
| dt.LoadDataRow( new object[] { "DATABASE" }, true ); |
| dt.LoadDataRow( new object[] { "DEFAULT" }, true ); |
| dt.LoadDataRow( new object[] { "DEFERRABLE" }, true ); |
| dt.LoadDataRow( new object[] { "DEFERRED" }, true ); |
| dt.LoadDataRow( new object[] { "DELETE" }, true ); |
| dt.LoadDataRow( new object[] { "DESC" }, true ); |
| dt.LoadDataRow( new object[] { "DETACH" }, true ); |
| dt.LoadDataRow( new object[] { "DISTINCT" }, true ); |
| dt.LoadDataRow( new object[] { "DROP" }, true ); |
| dt.LoadDataRow( new object[] { "EACH" }, true ); |
| dt.LoadDataRow( new object[] { "ELSE" }, true ); |
| dt.LoadDataRow( new object[] { "END" }, true ); |
| dt.LoadDataRow( new object[] { "ESCAPE" }, true ); |
| dt.LoadDataRow( new object[] { "EXCEPT" }, true ); |
| dt.LoadDataRow( new object[] { "EXCLUSIVE" }, true ); |
| dt.LoadDataRow( new object[] { "EXISTS" }, true ); |
| dt.LoadDataRow( new object[] { "EXPLAIN" }, true ); |
| dt.LoadDataRow( new object[] { "FAIL" }, true ); |
| dt.LoadDataRow( new object[] { "FOR" }, true ); |
| dt.LoadDataRow( new object[] { "FOREIGN" }, true ); |
| dt.LoadDataRow( new object[] { "FROM" }, true ); |
| dt.LoadDataRow( new object[] { "FULL" }, true ); |
| dt.LoadDataRow( new object[] { "GLOB" }, true ); |
| dt.LoadDataRow( new object[] { "GROUP" }, true ); |
| dt.LoadDataRow( new object[] { "HAVING" }, true ); |
| dt.LoadDataRow( new object[] { "IF" }, true ); |
| dt.LoadDataRow( new object[] { "IGNORE" }, true ); |
| dt.LoadDataRow( new object[] { "IMMEDIATE" }, true ); |
| dt.LoadDataRow( new object[] { "IN" }, true ); |
| dt.LoadDataRow( new object[] { "INDEX" }, true ); |
| dt.LoadDataRow( new object[] { "INITIALLY" }, true ); |
| dt.LoadDataRow( new object[] { "INNER" }, true ); |
| dt.LoadDataRow( new object[] { "INSERT" }, true ); |
| dt.LoadDataRow( new object[] { "INSTEAD" }, true ); |
| dt.LoadDataRow( new object[] { "INTERSECT" }, true ); |
| dt.LoadDataRow( new object[] { "INTO" }, true ); |
| dt.LoadDataRow( new object[] { "IS" }, true ); |
| dt.LoadDataRow( new object[] { "ISNULL" }, true ); |
| dt.LoadDataRow( new object[] { "JOIN" }, true ); |
| dt.LoadDataRow( new object[] { "KEY" }, true ); |
| dt.LoadDataRow( new object[] { "LEFT" }, true ); |
| dt.LoadDataRow( new object[] { "LIKE" }, true ); |
| dt.LoadDataRow( new object[] { "LIMIT" }, true ); |
| dt.LoadDataRow( new object[] { "MATCH" }, true ); |
| dt.LoadDataRow( new object[] { "NATURAL" }, true ); |
| dt.LoadDataRow( new object[] { "NO" }, true ); |
| dt.LoadDataRow( new object[] { "NOT" }, true ); |
| dt.LoadDataRow( new object[] { "NOT NULL" }, true ); |
| dt.LoadDataRow( new object[] { "OF" }, true ); |
| dt.LoadDataRow( new object[] { "OFFSET" }, true ); |
| dt.LoadDataRow( new object[] { "ON" }, true ); |
| dt.LoadDataRow( new object[] { "OR" }, true ); |
| dt.LoadDataRow( new object[] { "ORDER" }, true ); |
| dt.LoadDataRow( new object[] { "OUTER" }, true ); |
| dt.LoadDataRow( new object[] { "PLAN" }, true ); |
| dt.LoadDataRow( new object[] { "PRAGMA" }, true ); |
| dt.LoadDataRow( new object[] { "PRIMARY" }, true ); |
| dt.LoadDataRow( new object[] { "QUERY" }, true ); |
| dt.LoadDataRow( new object[] { "RAISE" }, true ); |
| dt.LoadDataRow( new object[] { "REFERENCES" }, true ); |
| dt.LoadDataRow( new object[] { "REGEXP" }, true ); |
| dt.LoadDataRow( new object[] { "REINDEX" }, true ); |
| dt.LoadDataRow( new object[] { "RELEASE" }, true ); |
| dt.LoadDataRow( new object[] { "RENAME" }, true ); |
| dt.LoadDataRow( new object[] { "REPLACE" }, true ); |
| dt.LoadDataRow( new object[] { "RESTRICT" }, true ); |
| dt.LoadDataRow( new object[] { "RIGHT" }, true ); |
| dt.LoadDataRow( new object[] { "ROLLBACK" }, true ); |
| dt.LoadDataRow( new object[] { "ROW" }, true ); |
| dt.LoadDataRow( new object[] { "SAVEPOOINT" }, true ); |
| dt.LoadDataRow( new object[] { "SELECT" }, true ); |
| dt.LoadDataRow( new object[] { "SET" }, true ); |
| dt.LoadDataRow( new object[] { "TABLE" }, true ); |
| dt.LoadDataRow( new object[] { "TEMP" }, true ); |
| dt.LoadDataRow( new object[] { "TEMPORARY" }, true ); |
| dt.LoadDataRow( new object[] { "THEN" }, true ); |
| dt.LoadDataRow( new object[] { "TO" }, true ); |
| dt.LoadDataRow( new object[] { "TRANSACTION" }, true ); |
| dt.LoadDataRow( new object[] { "TRIGGER" }, true ); |
| dt.LoadDataRow( new object[] { "UNION" }, true ); |
| dt.LoadDataRow( new object[] { "UNIQUE" }, true ); |
| dt.LoadDataRow( new object[] { "UPDATE" }, true ); |
| dt.LoadDataRow( new object[] { "USING" }, true ); |
| dt.LoadDataRow( new object[] { "VACUUM" }, true ); |
| dt.LoadDataRow( new object[] { "VALUES" }, true ); |
| dt.LoadDataRow( new object[] { "VIEW" }, true ); |
| dt.LoadDataRow( new object[] { "VIRTUAL" }, true ); |
| dt.LoadDataRow( new object[] { "WHEN" }, true ); |
| dt.LoadDataRow( new object[] { "WHERE" }, true ); |
| |
| return dt; |
| } |
| |
| DataTable GetSchemaDataTable( SqliteCommand cmd, string[] restrictionValues ) |
| { |
| if ( restrictionValues != null && cmd.Parameters.Count > 0 ) |
| { |
| for ( int i = 0; i < restrictionValues.Length; i++ ) |
| cmd.Parameters[i].Value = restrictionValues[i]; |
| } |
| |
| SqliteDataAdapter adapter = new SqliteDataAdapter( cmd ); |
| DataTable dt = new DataTable(); |
| adapter.Fill( dt ); |
| |
| return dt; |
| } |
| #endif |
| #endregion |
| |
| } |
| } |
| /trunk/Community.CsharpSqlite.SQLiteClient/src/SqliteConnectionStringBuilder.cs |
|---|
| @@ -0,0 +1,323 @@ |
|---|
| // |
| // Community.CsharpSqlite.SQLiteClient.SqliteConnectionStringBuilder.cs |
| // |
| // Author(s): |
| // Sureshkumar T (tsureshkumar@novell.com) |
| // Marek Habersack (grendello@gmail.com) |
| // Stewart Adcock (stewart.adcock@medit.fr) |
| // |
| // Copyright (C) 2004 Novell, Inc (http://www.novell.com) |
| // Copyright (C) 2007 Marek Habersack |
| // Copyright (C) 2012 MEDIT SA (http://medit-pharma.com) |
| // |
| // Permission is hereby granted, free of charge, to any person obtaining |
| // a copy of this software and associated documentation files (the |
| // "Software"), to deal in the Software without restriction, including |
| // without limitation the rights to use, copy, modify, merge, publish, |
| // distribute, sublicense, and/or sell copies of the Software, and to |
| // permit persons to whom the Software is furnished to do so, subject to |
| // the following conditions: |
| // |
| // The above copyright notice and this permission notice shall be |
| // included in all copies or substantial portions of the Software. |
| // |
| // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, |
| // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF |
| // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND |
| // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE |
| // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION |
| // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION |
| // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| // |
| |
| using System; |
| using System.Collections; |
| using System.Collections.Generic; |
| using System.Data; |
| using System.Data.Common; |
| using System.Globalization; |
| using System.Text; |
| |
| namespace Community.CsharpSqlite.SQLiteClient |
| { |
| public sealed class SqliteConnectionStringBuilder : DbConnectionStringBuilder |
| { |
| private const string DEF_URI = null; |
| private const Int32 DEF_MODE = 0644; |
| private const Int32 DEF_VERSION = 3; |
| private const Encoding DEF_ENCODING = null; |
| private const Int32 DEF_BUSYTIMEOUT = 0; |
| private const bool DEF_READONLY = false; |
| private const bool DEF_FAILIFMISSING = false; |
| |
| #region // Fields |
| private string _uri; |
| private Int32 _mode; |
| private Int32 _version; |
| private Encoding _encoding; |
| private Int32 _busy_timeout; |
| private bool _readonly; |
| private bool _failIfMissing; |
| |
| private static Dictionary <string, string> _keywords; // for mapping duplicate keywords |
| #endregion // Fields |
| |
| #region Constructors |
| public SqliteConnectionStringBuilder () : this (String.Empty) |
| { |
| } |
| |
| public SqliteConnectionStringBuilder (string connectionString) |
| { |
| Init (); |
| base.ConnectionString = connectionString; |
| } |
| |
| static SqliteConnectionStringBuilder () |
| { |
| _keywords = new Dictionary <string, string> (); |
| _keywords ["URI"] = "Uri"; |
| _keywords ["DATA SOURCE"] = "Data Source"; |
| _keywords ["DATASOURCE"] = "Data Source"; |
| _keywords ["URI"] = "Data Source"; |
| _keywords ["MODE"] = "Mode"; |
| _keywords ["VERSION"] = "Version"; |
| _keywords ["BUSY TIMEOUT"] = "Busy Timeout"; |
| _keywords ["BUSYTIMEOUT"] = "Busy Timeout"; |
| _keywords ["ENCODING"] = "Encoding"; |
| _keywords ["READ ONLY"] = "Read Only"; |
| _keywords ["READONLY"] = "Read Only"; |
| _keywords ["FAILIFMISSING"] = "FailIfMissing"; |
| } |
| #endregion // Constructors |
| |
| #region Properties |
| public string DataSource { |
| get { return _uri; } |
| set { |
| base ["Data Source"] = value; |
| _uri = value; |
| } |
| } |
| |
| public string Uri { |
| get { return _uri; } |
| set { |
| base ["Data Source"] = value; |
| _uri = value; |
| } |
| } |
| |
| public Int32 Mode { |
| get { return _mode; } |
| set { |
| base ["Mode"] = value; |
| _mode = value; |
| } |
| } |
| |
| public Int32 Version { |
| get { return _version; } |
| set { |
| base ["Version"] = value; |
| _version = value; |
| } |
| } |
| |
| public Int32 BusyTimeout { |
| get { return _busy_timeout; } |
| set { |
| base ["Busy Timeout"] = value; |
| _busy_timeout = value; |
| } |
| } |
| |
| public Encoding Encoding { |
| get { return _encoding; } |
| set { |
| base ["Encoding"] = value; |
| _encoding = value; |
| } |
| } |
| |
| public override bool IsFixedSize { |
| get { return true; } |
| } |
| |
| public bool ReadOnly { |
| get { return _readonly; } |
| set { |
| base ["Read Only"] = value; |
| _readonly = value; |
| } |
| } |
| |
| public bool FailIfMissing { |
| get { return _failIfMissing; } |
| set { |
| base ["FailIfMissing"] = value; |
| _failIfMissing = value; |
| } |
| } |
| |
| public override object this [string keyword] { |
| get { |
| string mapped = MapKeyword (keyword); |
| return base [mapped]; |
| } |
| set {SetValue (keyword, value);} |
| } |
| |
| public override ICollection Keys { |
| get { return base.Keys; } |
| } |
| |
| public override ICollection Values { |
| get { return base.Values; } |
| } |
| #endregion // Properties |
| |
| #region Methods |
| private void Init () |
| { |
| _uri = DEF_URI; |
| _mode = DEF_MODE; |
| _version = DEF_VERSION; |
| _encoding = DEF_ENCODING; |
| _busy_timeout = DEF_BUSYTIMEOUT; |
| _readonly = DEF_READONLY; |
| _failIfMissing = DEF_FAILIFMISSING; |
| } |
| |
| public override void Clear () |
| { |
| base.Clear (); |
| Init (); |
| } |
| |
| public override bool ContainsKey (string keyword) |
| { |
| keyword = keyword.ToUpper ().Trim (); |
| if (_keywords.ContainsKey (keyword)) |
| return base.ContainsKey (_keywords [keyword]); |
| return false; |
| } |
| |
| public override bool Remove (string keyword) |
| { |
| if (!ContainsKey (keyword)) |
| return false; |
| this [keyword] = null; |
| return true; |
| } |
| |
| public override bool TryGetValue (string keyword, out object value) |
| { |
| if (! ContainsKey (keyword)) { |
| value = String.Empty; |
| return false; |
| } |
| return base.TryGetValue (_keywords [keyword.ToUpper ().Trim ()], out value); |
| } |
| |
| #endregion // Methods |
| |
| #region Private Methods |
| private string MapKeyword (string keyword) |
| { |
| keyword = keyword.ToUpper ().Trim (); |
| if (! _keywords.ContainsKey (keyword)) |
| throw new ArgumentException("Keyword not supported :" + keyword); |
| return _keywords [keyword]; |
| } |
| |
| private void SetValue (string key, object value) |
| { |
| if (key == null) |
| throw new ArgumentNullException ("key cannot be null!"); |
| |
| string mappedKey = MapKeyword (key); |
| |
| switch (mappedKey.ToUpper (CultureInfo.InvariantCulture).Trim ()) { |
| case "DATA SOURCE": |
| if (value == null) { |
| _uri = DEF_URI; |
| base.Remove (mappedKey); |
| } else |
| this.Uri = value.ToString (); |
| break; |
| |
| case "MODE": |
| if (value == null) { |
| _mode = DEF_MODE; |
| base.Remove (mappedKey); |
| } else |
| this.Mode = ConvertToInt32 (value); |
| break; |
| |
| case "VERSION": |
| if (value == null) { |
| _version = DEF_MODE; |
| base.Remove (mappedKey); |
| } else |
| this.Version = ConvertToInt32 (value); |
| break; |
| |
| case "BUSY TIMEOUT": |
| if (value == null) { |
| _busy_timeout = DEF_BUSYTIMEOUT; |
| base.Remove (mappedKey); |
| } else |
| this.BusyTimeout = ConvertToInt32 (value); |
| break; |
| |
| case "ENCODING": |
| if (value == null) { |
| _encoding = DEF_ENCODING; |
| base.Remove (mappedKey); |
| } else if (value is string) { |
| this.Encoding = Encoding.GetEncoding ((string)value); |
| } else |
| throw new ArgumentException ("Cannot set encoding from a non-string argument"); |
| break; |
| |
| case "READ ONLY": |
| if (value == null) { |
| _readonly = DEF_READONLY; |
| base.Remove (mappedKey); |
| } else |
| this.ReadOnly = ConvertToBoolean (value); |
| break; |
| |
| case "FAILIFMISSING": |
| if (value == null) { |
| _failIfMissing = DEF_FAILIFMISSING; |
| base.Remove (mappedKey); |
| } else |
| this.FailIfMissing = ConvertToBoolean (value); |
| break; |
| |
| default: |
| throw new ArgumentException ("Keyword not supported :" + key); |
| } |
| } |
| |
| private static int ConvertToInt32 (object value) |
| { |
| return Int32.Parse (value.ToString (), CultureInfo.InvariantCulture); |
| } |
| |
| private static bool ConvertToBoolean (object value) |
| { |
| if (value == null) |
| throw new ArgumentNullException ("null value cannot be converted to boolean"); |
| string upper = value.ToString ().ToUpper ().Trim (); |
| if (upper == "YES" || upper == "TRUE") |
| return true; |
| if (upper == "NO" || upper == "FALSE") |
| return false; |
| throw new ArgumentException (String.Format ("Invalid boolean value: {0}", value.ToString ())); |
| } |
| #endregion // Private Methods |
| } |
| } |
| /trunk/Community.CsharpSqlite.SQLiteClient/src/SqliteDataReader.cs |
|---|
| @@ -0,0 +1,483 @@ |
|---|
| // |
| // Community.CsharpSqlite.SQLiteClient.SqliteDataReader.cs |
| // |
| // Provides a means of reading a forward-only stream of rows from a Sqlite |
| // database file. |
| // |
| // Author(s): Vladimir Vukicevic <vladimir@pobox.com> |
| // Everaldo Canuto <everaldo_canuto@yahoo.com.br> |
| // Joshua Tauberer <tauberer@for.net> |
| // |
| // Copyright (C) 2002 Vladimir Vukicevic |
| // |
| // Permission is hereby granted, free of charge, to any person obtaining |
| // a copy of this software and associated documentation files (the |
| // "Software"), to deal in the Software without restriction, including |
| // without limitation the rights to use, copy, modify, merge, publish, |
| // distribute, sublicense, and/or sell copies of the Software, and to |
| // permit persons to whom the Software is furnished to do so, subject to |
| // the following conditions: |
| // |
| // The above copyright notice and this permission notice shall be |
| // included in all copies or substantial portions of the Software. |
| // |
| // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, |
| // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF |
| // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND |
| // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE |
| // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION |
| // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION |
| // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| // |
| |
| using System; |
| using System.Collections; |
| using System.Collections.Generic ; |
| using System.Data; |
| using System.Data.Common; |
| using Community.CsharpSqlite; |
| |
| namespace Community.CsharpSqlite.SQLiteClient |
| { |
| public class SqliteDataReader : DbDataReader, IDataReader, IDisposable, IDataRecord |
| { |
| |
| #region Fields |
| |
| private SqliteCommand command; |
| private List<object[]> rows; |
| private string[] columns; |
| private Dictionary<String, Object> column_names_sens, column_names_insens; |
| private int current_row; |
| private bool closed; |
| private bool reading; |
| private int records_affected; |
| private string[] decltypes; |
| |
| #endregion |
| |
| #region Constructors and destructors |
| |
| internal SqliteDataReader (SqliteCommand cmd, Sqlite3.Vdbe pVm, int version) |
| { |
| command = cmd; |
| rows = new List<object[]>(); |
| column_names_sens = new Dictionary<String, Object>(); |
| column_names_insens = new Dictionary<String, Object>( StringComparer.InvariantCultureIgnoreCase ); |
| closed = false; |
| current_row = -1; |
| reading = true; |
| ReadpVm (pVm, version, cmd); |
| ReadingDone (); |
| } |
| |
| #endregion |
| |
| #region Properties |
| |
| public override int Depth { |
| get { return 0; } |
| } |
| |
| public override int FieldCount { |
| get { return columns.Length; } |
| } |
| |
| public override object this[string name] { |
| get { |
| return GetValue (GetOrdinal (name)); |
| } |
| } |
| |
| public override object this[int i] { |
| get { return GetValue (i); } |
| } |
| |
| public override bool IsClosed { |
| get { return closed; } |
| } |
| |
| public override int RecordsAffected { |
| get { return records_affected; } |
| } |
| |
| #endregion |
| |
| #region Internal Methods |
| |
| internal void ReadpVm (Sqlite3.Vdbe pVm, int version, SqliteCommand cmd) |
| { |
| int pN; |
| IntPtr pazValue; |
| IntPtr pazColName; |
| bool first = true; |
| |
| int[] declmode = null; |
| |
| while (true) { |
| bool hasdata = cmd.ExecuteStatement(pVm, out pN, out pazValue, out pazColName); |
| |
| // For the first row, get the column information |
| if (first) { |
| first = false; |
| |
| if (version == 3) { |
| // A decltype might be null if the type is unknown to sqlite. |
| decltypes = new string[pN]; |
| declmode = new int[pN]; // 1 == integer, 2 == datetime |
| for (int i = 0; i < pN; i++) { |
| string decl = Sqlite3.sqlite3_column_decltype (pVm, i); |
| if (decl != null) { |
| decltypes[i] = decl.ToLower(System.Globalization.CultureInfo.InvariantCulture); |
| if (decltypes[i] == "int" || decltypes[i] == "integer") |
| declmode[i] = 1; |
| else if (decltypes[i] == "date" || decltypes[i] == "datetime") |
| declmode[i] = 2; |
| } |
| } |
| } |
| |
| columns = new string[pN]; |
| for (int i = 0; i < pN; i++) { |
| string colName; |
| //if (version == 2) { |
| // IntPtr fieldPtr = Marshal.ReadIntPtr (pazColName, i*IntPtr.Size); |
| // colName = Sqlite.HeapToString (fieldPtr, ((SqliteConnection)cmd.Connection).Encoding); |
| //} else { |
| colName = Sqlite3.sqlite3_column_name (pVm, i); |
| //} |
| columns[i] = colName; |
| column_names_sens [colName] = i; |
| column_names_insens [colName] = i; |
| } |
| } |
| |
| if (!hasdata) break; |
| |
| object[] data_row = new object [pN]; |
| for (int i = 0; i < pN; i++) { |
| /* |
| if (version == 2) { |
| IntPtr fieldPtr = Marshal.ReadIntPtr (pazValue, i*IntPtr.Size); |
| data_row[i] = Sqlite.HeapToString (fieldPtr, ((SqliteConnection)cmd.Connection).Encoding); |
| } else { |
| */ |
| switch (Sqlite3.sqlite3_column_type (pVm, i)) { |
| case 1: |
| long val = Sqlite3.sqlite3_column_int64 (pVm, i); |
| |
| // If the column was declared as an 'int' or 'integer', let's play |
| // nice and return an int (version 3 only). |
| if (declmode[i] == 1 && val >= int.MinValue && val <= int.MaxValue) |
| data_row[i] = (int)val; |
| |
| // Or if it was declared a date or datetime, do the reverse of what we |
| // do for DateTime parameters. |
| else if (declmode[i] == 2) |
| data_row[i] = DateTime.FromFileTime(val); |
| else |
| data_row[i] = val; |
| |
| break; |
| case 2: |
| data_row[i] = Sqlite3.sqlite3_column_double (pVm, i); |
| break; |
| case 3: |
| data_row[i] = Sqlite3.sqlite3_column_text (pVm, i); |
| |
| // If the column was declared as a 'date' or 'datetime', let's play |
| // nice and return a DateTime (version 3 only). |
| if (declmode[i] == 2) |
| if (data_row[i] == null) data_row[i] = null; |
| else data_row[i] = DateTime.Parse((string)data_row[i], System.Globalization.CultureInfo.InvariantCulture); |
| break; |
| case 4: |
| byte[] blob = Sqlite3.sqlite3_column_blob(pVm, i); |
| ////int blobbytes = Sqlite3.sqlite3_column_bytes16 (pVm, i); |
| ////byte[] blob = new byte[blobbytes]; |
| ////Marshal.Copy (blobptr, blob, 0, blobbytes); |
| data_row[i] = blob; |
| break; |
| case 5: |
| data_row[i] = null; |
| break; |
| default: |
| throw new Exception ("FATAL: Unknown sqlite3_column_type"); |
| //} |
| } |
| } |
| |
| rows.Add (data_row); |
| } |
| } |
| internal void ReadingDone () |
| { |
| records_affected = command.NumChanges (); |
| reading = false; |
| } |
| |
| #endregion |
| |
| #region Public Methods |
| |
| public override void Close () |
| { |
| closed = true; |
| } |
| |
| protected override void Dispose (bool disposing) |
| { |
| if (disposing) |
| Close (); |
| } |
| |
| public override IEnumerator GetEnumerator () |
| { |
| return new DbEnumerator (this); |
| } |
| #if !SQLITE_SILVERLIGHT |
| public override DataTable GetSchemaTable () |
| { |
| DataTable dataTableSchema = new DataTable (); |
| |
| dataTableSchema.Columns.Add ("ColumnName", typeof (String)); |
| dataTableSchema.Columns.Add ("ColumnOrdinal", typeof (Int32)); |
| dataTableSchema.Columns.Add ("ColumnSize", typeof (Int32)); |
| dataTableSchema.Columns.Add ("NumericPrecision", typeof (Int32)); |
| dataTableSchema.Columns.Add ("NumericScale", typeof (Int32)); |
| dataTableSchema.Columns.Add ("IsUnique", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsKey", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("BaseCatalogName", typeof (String)); |
| dataTableSchema.Columns.Add ("BaseColumnName", typeof (String)); |
| dataTableSchema.Columns.Add ("BaseSchemaName", typeof (String)); |
| dataTableSchema.Columns.Add ("BaseTableName", typeof (String)); |
| dataTableSchema.Columns.Add ("DataType", typeof(Type)); |
| dataTableSchema.Columns.Add ("AllowDBNull", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("ProviderType", typeof (Int32)); |
| dataTableSchema.Columns.Add ("IsAliased", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsExpression", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsIdentity", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsAutoIncrement", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsRowVersion", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsHidden", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsLong", typeof (Boolean)); |
| dataTableSchema.Columns.Add ("IsReadOnly", typeof (Boolean)); |
| |
| dataTableSchema.BeginLoadData(); |
| for (int i = 0; i < this.FieldCount; i += 1 ) { |
| |
| DataRow schemaRow = dataTableSchema.NewRow (); |
| |
| schemaRow["ColumnName"] = columns[i]; |
| schemaRow["ColumnOrdinal"] = i; |
| schemaRow["ColumnSize"] = 0; |
| schemaRow["NumericPrecision"] = 0; |
| schemaRow["NumericScale"] = 0; |
| schemaRow["IsUnique"] = false; |
| schemaRow["IsKey"] = false; |
| schemaRow["BaseCatalogName"] = ""; |
| schemaRow["BaseColumnName"] = columns[i]; |
| schemaRow["BaseSchemaName"] = ""; |
| schemaRow["BaseTableName"] = ""; |
| schemaRow["DataType"] = typeof(string); |
| schemaRow["AllowDBNull"] = true; |
| schemaRow["ProviderType"] = 0; |
| schemaRow["IsAliased"] = false; |
| schemaRow["IsExpression"] = false; |
| schemaRow["IsIdentity"] = false; |
| schemaRow["IsAutoIncrement"] = false; |
| schemaRow["IsRowVersion"] = false; |
| schemaRow["IsHidden"] = false; |
| schemaRow["IsLong"] = false; |
| schemaRow["IsReadOnly"] = false; |
| |
| dataTableSchema.Rows.Add (schemaRow); |
| schemaRow.AcceptChanges(); |
| } |
| dataTableSchema.EndLoadData(); |
| |
| return dataTableSchema; |
| } |
| #endif |
| public override bool NextResult () |
| { |
| current_row++; |
| |
| return (current_row < rows.Count); |
| } |
| |
| public override bool Read () |
| { |
| return NextResult (); |
| } |
| |
| #endregion |
| |
| #region IDataRecord getters |
| |
| public override bool GetBoolean (int i) |
| { |
| int result = Convert.ToInt32(((object[])rows[current_row])[i]); |
| return Convert.ToBoolean(result); |
| } |
| |
| public override byte GetByte (int i) |
| { |
| return Convert.ToByte (((object[]) rows[current_row])[i]); |
| |
| } |
| |
| public override long GetBytes (int i, long fieldOffset, byte[] buffer, int bufferOffset, int length) |
| { |
| byte[] data = (byte[])(((object[]) rows[current_row])[i]); |
| if (buffer != null) |
| Array.Copy (data, (int)fieldOffset, buffer, bufferOffset, length); |
| #if (SQLITE_SILVERLIGHT||WINDOWS_MOBILE) |
| return data.Length - fieldOffset; |
| #else |
| return data.LongLength - fieldOffset; |
| #endif |
| } |
| |
| public override char GetChar (int i) |
| { |
| return Convert.ToChar (((object[]) rows[current_row])[i]); |
| } |
| |
| public override long GetChars (int i, long fieldOffset, char[] buffer, int bufferOffset, int length) |
| { |
| char[] data = (char[])(((object[]) rows[current_row])[i]); |
| if (buffer != null) |
| Array.Copy (data, (int)fieldOffset, buffer, bufferOffset, length); |
| #if (SQLITE_SILVERLIGHT||WINDOWS_MOBILE) |
| return data.Length - fieldOffset; |
| #else |
| return data.LongLength - fieldOffset; |
| #endif |
| } |
| |
| public override string GetDataTypeName (int i) |
| { |
| if (decltypes != null && decltypes[i] != null) |
| return decltypes[i]; |
| return "text"; // SQL Lite data type |
| } |
| |
| public override DateTime GetDateTime (int i) |
| { |
| return Convert.ToDateTime (((object[]) rows[current_row])[i]); |
| } |
| |
| public override decimal GetDecimal (int i) |
| { |
| return Convert.ToDecimal (((object[]) rows[current_row])[i]); |
| } |
| |
| public override double GetDouble (int i) |
| { |
| return Convert.ToDouble (((object[]) rows[current_row])[i]); |
| } |
| |
| public override Type GetFieldType (int i) |
| { |
| int row = current_row; |
| if (row == -1 && rows.Count == 0) return typeof(string); |
| if (row == -1) row = 0; |
| object element = ((object[]) rows[row])[i]; |
| if (element != null) |
| return element.GetType(); |
| else |
| return typeof (string); |
| |
| // Note that the return value isn't guaranteed to |
| // be the same as the rows are read if different |
| // types of information are stored in the column. |
| } |
| |
| public override float GetFloat (int i) |
| { |
| return Convert.ToSingle (((object[]) rows[current_row])[i]); |
| } |
| |
| public override Guid GetGuid (int i) |
| { |
| object value = GetValue (i); |
| if (!(value is Guid)) { |
| if (value is DBNull) |
| throw new SqliteExecutionException ("Column value must not be null"); |
| throw new InvalidCastException ("Type is " + value.GetType ().ToString ()); |
| } |
| return ((Guid) value); |
| } |
| |
| public override short GetInt16 (int i) |
| { |
| return Convert.ToInt16 (((object[]) rows[current_row])[i]); |
| } |
| |
| public override int GetInt32 (int i) |
| { |
| return Convert.ToInt32 (((object[]) rows[current_row])[i]); |
| } |
| |
| public override long GetInt64 (int i) |
| { |
| return Convert.ToInt64 (((object[]) rows[current_row])[i]); |
| } |
| |
| public override string GetName (int i) |
| { |
| return columns[i]; |
| } |
| |
| public override int GetOrdinal (string name) |
| { |
| object v = column_names_sens.ContainsKey( name ) ? column_names_sens[name] : null; |
| if ( v == null ) |
| v = column_names_insens.ContainsKey( name ) ? column_names_insens[name] : null; |
| if ( v == null ) |
| throw new ArgumentException( "Column does not exist." ); |
| return (int)v; |
| } |
| |
| public override string GetString (int i) |
| { |
| if (((object[]) rows[current_row])[i] != null) |
| return (((object[]) rows[current_row])[i]).ToString(); |
| else return null; |
| } |
| |
| public override object GetValue (int i) |
| { |
| return ((object[]) rows[current_row])[i]; |
| } |
| |
| public override int GetValues (object[] values) |
| { |
| int num_to_fill = System.Math.Min (values.Length, columns.Length); |
| for (int i = 0; i < num_to_fill; i++) { |
| if (((object[]) rows[current_row])[i] != null) { |
| values[i] = ((object[]) rows[current_row])[i]; |
| } else { |
| values[i] = DBNull.Value; |
| } |
| } |
| return num_to_fill; |
| } |
| |
| public override bool IsDBNull (int i) |
| { |
| return (((object[]) rows[current_row])[i] == null); |
| } |
| |
| public override bool HasRows { |
| get { return rows.Count > 0; } |
| } |
| |
| public override int VisibleFieldCount { |
| get { return FieldCount; } |
| } |
| |
| #endregion |
| } |
| } |
| /trunk/Community.CsharpSqlite.SQLiteClient/src/SqliteParameterCollection.cs |
|---|
| @@ -0,0 +1,300 @@ |
|---|
| // |
| // Community.CsharpSqlite.SQLiteClient.SqliteParameterCollection.cs |
| // |
| // Represents a collection of parameters relevant to a SqliteCommand as well as |
| // their respective mappings to columns in a DataSet. |
| // |
| //Author(s): Vladimir Vukicevic <vladimir@pobox.com> |
| // Everaldo Canuto <everaldo_canuto@yahoo.com.br> |
| // Chris Turchin <chris@turchin.net> |
| // Jeroen Zwartepoorte <jeroen@xs4all.nl> |
| // Thomas Zoechling <thomas.zoechling@gmx.at> |
| // Alex West <alxwest@gmail.com> |
| // Stewart Adcock <stewart.adcock@medit.fr> |
| // |
| // Copyright (C) 2002 Vladimir Vukicevic |
| // |
| // Permission is hereby granted, free of charge, to any person obtaining |
| // a copy of this software and associated documentation files (the |
| // "Software"), to deal in the Software without restriction, including |
| // without limitation the rights to use, copy, modify, merge, publish, |
| // distribute, sublicense, and/or sell copies of the Software, and to |
| // permit persons to whom the Software is furnished to do so, subject to |
| // the following conditions: |
| // |
| // The above copyright notice and this permission notice shall be |
| // included in all copies or substantial portions of the Software. |
| // |
| // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, |
| // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF |
| // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND |
| // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE |
| // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION |
| // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION |
| // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| // |
| |
| using System; |
| using System.Data; |
| using System.Data.Common; |
| using System.Collections; |
| using System.Collections.Generic ; |
| |
| namespace Community.CsharpSqlite.SQLiteClient |
| { |
| public class SqliteParameterCollection : DbParameterCollection |
| { |
| |
| #region Fields |
| |
| List<SqliteParameter> numeric_param_list = new List<SqliteParameter>(); |
| Dictionary<string, int> named_param_hash = new Dictionary<string, int>(); |
| |
| #endregion |
| |
| #region Private Methods |
| |
| private void CheckSqliteParam (object value) |
| { |
| if (!(value is SqliteParameter)) |
| throw new InvalidCastException ("Can only use SqliteParameter objects"); |
| SqliteParameter sqlp = value as SqliteParameter; |
| if (sqlp.ParameterName == null || sqlp.ParameterName.Length == 0) |
| sqlp.ParameterName = this.GenerateParameterName(); |
| } |
| |
| private void RecreateNamedHash () |
| { |
| for (int i = 0; i < numeric_param_list.Count; i++) |
| { |
| named_param_hash[((SqliteParameter) numeric_param_list[i]).ParameterName] = i; |
| } |
| } |
| |
| //FIXME: if the user is calling Insert at various locations with unnamed parameters, this is not going to work.... |
| private string GenerateParameterName() |
| { |
| int index = this.Count + 1; |
| string name = String.Empty; |
| |
| while (index > 0) |
| { |
| name = ":" + index.ToString(); |
| if (this.IndexOf(name) == -1) |
| index = -1; |
| else |
| index++; |
| } |
| return name; |
| } |
| |
| #endregion |
| |
| #region Properties |
| |
| private bool isPrefixed (string parameterName) |
| { |
| return parameterName.Length > 1 && (parameterName[0] == ':' || parameterName[0] == '$' || parameterName[0] == '@'); |
| } |
| |
| protected override DbParameter GetParameter (int parameterIndex) |
| { |
| if (this.Count >= parameterIndex+1) |
| return (SqliteParameter) numeric_param_list[parameterIndex]; |
| else |
| throw new IndexOutOfRangeException("The specified parameter index does not exist: " + parameterIndex.ToString()); |
| } |
| |
| protected override DbParameter GetParameter (string parameterName) |
| { |
| if (this.Contains(parameterName)) |
| return this[(int) named_param_hash[parameterName]]; |
| else if (isPrefixed(parameterName) && this.Contains(parameterName.Substring(1))) |
| return this[(int) named_param_hash[parameterName.Substring(1)]]; |
| else |
| throw new IndexOutOfRangeException("The specified name does not exist: " + parameterName); |
| } |
| |
| protected override void SetParameter (int parameterIndex, DbParameter parameter) |
| { |
| if (this.Count >= parameterIndex+1) |
| numeric_param_list[parameterIndex] = (SqliteParameter)parameter; |
| else |
| throw new IndexOutOfRangeException("The specified parameter index does not exist: " + parameterIndex.ToString()); |
| } |
| |
| protected override void SetParameter (string parameterName, DbParameter parameter) |
| { |
| if (this.Contains(parameterName)) |
| numeric_param_list[(int)named_param_hash[parameterName]] = (SqliteParameter)parameter; |
| else if (parameterName.Length > 1 && this.Contains(parameterName.Substring(1))) |
| numeric_param_list[(int) named_param_hash[parameterName.Substring(1)]] = (SqliteParameter)parameter; |
| else |
| throw new IndexOutOfRangeException("The specified name does not exist: " + parameterName); |
| } |
| |
| public override int Count |
| { |
| get |
| { |
| return this.numeric_param_list.Count; |
| } |
| } |
| |
| public override bool IsSynchronized |
| { |
| get { return ((IList)this.numeric_param_list).IsSynchronized ; } |
| } |
| |
| public override bool IsFixedSize |
| { |
| get { return ((IList)this.numeric_param_list).IsFixedSize; } |
| } |
| |
| public override bool IsReadOnly |
| { |
| get { return ((IList)this.numeric_param_list).IsReadOnly; } |
| } |
| |
| public override object SyncRoot |
| { |
| get { return ((IList)this.numeric_param_list).SyncRoot ; } |
| } |
| |
| #endregion |
| |
| #region Public Methods |
| |
| public override void AddRange (Array values) |
| { |
| if (values == null || values.Length == 0) |
| return; |
| |
| foreach (object value in values) |
| Add (value); |
| } |
| |
| public override int Add (object value) |
| { |
| CheckSqliteParam (value); |
| SqliteParameter sqlp = value as SqliteParameter; |
| if (named_param_hash.ContainsKey(sqlp.ParameterName)) |
| throw new DuplicateNameException ("Parameter collection already contains the a SqliteParameter with the given ParameterName."); |
| numeric_param_list.Add(sqlp); |
| named_param_hash.Add(sqlp.ParameterName, numeric_param_list.IndexOf(sqlp)); |
| return (int) named_param_hash[sqlp.ParameterName]; |
| } |
| |
| public SqliteParameter Add (SqliteParameter param) |
| { |
| Add ((object)param); |
| return param; |
| } |
| |
| public SqliteParameter Add (string name, object value) |
| { |
| return Add (new SqliteParameter (name, value)); |
| } |
| |
| public SqliteParameter Add (string name, DbType type) |
| { |
| return Add (new SqliteParameter (name, type)); |
| } |
| |
| public override void Clear () |
| { |
| numeric_param_list.Clear (); |
| named_param_hash.Clear (); |
| } |
| |
| public override void CopyTo (Array array, int index) |
| { |
| this.numeric_param_list.CopyTo((SqliteParameter[])array, index); |
| } |
| |
| public override bool Contains (object value) |
| { |
| return Contains ((SqliteParameter) value); |
| } |
| |
| public override bool Contains (string parameterName) |
| { |
| return named_param_hash.ContainsKey(parameterName); |
| } |
| |
| public bool Contains (SqliteParameter param) |
| { |
| return Contains (param.ParameterName); |
| } |
| |
| public override IEnumerator GetEnumerator() |
| { |
| return this.numeric_param_list.GetEnumerator(); |
| } |
| |
| public override int IndexOf (object param) |
| { |
| return IndexOf ((SqliteParameter) param); |
| } |
| |
| public override int IndexOf (string parameterName) |
| { |
| if (isPrefixed (parameterName)){ |
| string sub = parameterName.Substring (1); |
| if (named_param_hash.ContainsKey(sub)) |
| return (int) named_param_hash [sub]; |
| } |
| if (named_param_hash.ContainsKey(parameterName)) |
| return (int) named_param_hash[parameterName]; |
| else |
| return -1; |
| } |
| |
| public int IndexOf (SqliteParameter param) |
| { |
| return IndexOf (param.ParameterName); |
| } |
| |
| public override void Insert (int index, object value) |
| { |
| CheckSqliteParam (value); |
| if (numeric_param_list.Count == index) |
| { |
| Add (value); |
| return; |
| } |
| |
| numeric_param_list.Insert(index,(SqliteParameter) value); |
| RecreateNamedHash (); |
| } |
| |
| public override void Remove (object value) |
| { |
| CheckSqliteParam (value); |
| RemoveAt ((SqliteParameter) value); |
| } |
| |
| public override void RemoveAt (int index) |
| { |
| RemoveAt (((SqliteParameter) numeric_param_list[index]).ParameterName); |
| } |
| |
| public override void RemoveAt (string parameterName) |
| { |
| if (!named_param_hash.ContainsKey (parameterName)) |
| throw new ApplicationException ("Parameter " + parameterName + " not found"); |
| |
| numeric_param_list.RemoveAt((int) named_param_hash[parameterName]); |
| named_param_hash.Remove (parameterName); |
| |
| RecreateNamedHash (); |
| } |
| |
| public void RemoveAt (SqliteParameter param) |
| { |
| RemoveAt (param.ParameterName); |
| } |
| |
| #endregion |
| } |
| } |
| /trunk/Community.CsharpSqlite.SQLiteClient/TestDriver_src/SQLiteClientTestDriver.cs |
|---|
| @@ -0,0 +1,1112 @@ |
|---|
| using System; |
| using System.Data; |
| using System.Data.Common; |
| using System.IO; |
| using System.Text; |
| using System.Threading; |
| using System.Collections.Generic; |
| using Community.CsharpSqlite; |
| using Community.CsharpSqlite.SQLiteClient; |
| using System.Globalization; |
| |
| namespace SQLiteClientTests |
| { |
| public class SQLiteClientTestDriver |
| { |
| public void Test1() |
| { |
| Console.WriteLine( "Test1 Start." ); |
| |
| Console.WriteLine( "Create connection..." ); |
| SqliteConnection con = new SqliteConnection(); |
| |
| string dbFilename = @"SqliteTest3.db"; |
| string cs = string.Format( "Version=3;uri=file:{0}", dbFilename ); |
| |
| Console.WriteLine( "Set connection String: {0}", cs ); |
| |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine( "Open database..." ); |
| con.Open(); |
| |
| Console.WriteLine( "create command..." ); |
| IDbCommand cmd = con.CreateCommand(); |
| |
| Console.WriteLine( "create table TEST_TABLE..." ); |
| cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert row 1..." ); |
| cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )"; |
| |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert row 2..." ); |
| cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )"; |
| cmd.ExecuteNonQuery(); |
| |
| //Console.WriteLine("commit..."); |
| //cmd.CommandText = "COMMIT"; |
| //cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "SELECT data from TEST_TABLE..." ); |
| cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE"; |
| IDataReader reader = cmd.ExecuteReader(); |
| int r = 0; |
| Console.WriteLine( "Read the data..." ); |
| while ( reader.Read() ) |
| { |
| Console.WriteLine( " Row: {0}", r ); |
| int i = reader.GetInt32( reader.GetOrdinal( "COLA" ) ); |
| Console.WriteLine( " COLA: {0}", i ); |
| |
| string s = reader.GetString( reader.GetOrdinal( "COLB" ) ); |
| Console.WriteLine( " COLB: {0}", s ); |
| |
| DateTime dt = reader.GetDateTime( reader.GetOrdinal( "COLC" ) ); |
| Console.WriteLine( " COLB: {0}", dt.ToString( "MM/dd/yyyy HH:mm:ss" ) ); |
| |
| r++; |
| } |
| Console.WriteLine( "Rows retrieved: {0}", r ); |
| |
| //alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight |
| #if !SQLITE_SILVERLIGHT |
| SqliteCommand command = new SqliteCommand( "PRAGMA table_info('TEST_TABLE')", con ); |
| DataTable dataTable = new DataTable(); |
| SqliteDataAdapter dataAdapter = new SqliteDataAdapter(); |
| dataAdapter.SelectCommand = command; |
| dataAdapter.Fill( dataTable ); |
| DisplayDataTable( dataTable, "Columns" ); |
| #endif |
| |
| Console.WriteLine( "Close and cleanup..." ); |
| con.Close(); |
| con = null; |
| |
| Console.WriteLine( "Test1 Done." ); |
| } |
| public void Test2() |
| { |
| Console.WriteLine( "Test2 Start." ); |
| |
| Console.WriteLine( "Create connection..." ); |
| SqliteConnection con = new SqliteConnection(); |
| |
| string dbFilename = @"SqliteTest3.db"; |
| string cs = string.Format( "Version=3,uri=file:{0}", dbFilename ); |
| |
| Console.WriteLine( "Set connection String: {0}", cs ); |
| |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine( "Open database..." ); |
| con.Open(); |
| |
| Console.WriteLine( "create command..." ); |
| IDbCommand cmd = con.CreateCommand(); |
| |
| Console.WriteLine( "create table TEST_TABLE..." ); |
| cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert row 1..." ); |
| cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert row 2..." ); |
| cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )"; |
| cmd.ExecuteNonQuery(); |
| |
| //Console.WriteLine("commit..."); |
| //cmd.CommandText = "COMMIT"; |
| //cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "SELECT data from TBL..." ); |
| cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'"; |
| IDataReader reader = cmd.ExecuteReader(); |
| int r = 0; |
| Console.WriteLine( "Read the data..." ); |
| while ( reader.Read() ) |
| { |
| Console.WriteLine( " Row: {0}", r ); |
| int i = reader.GetInt32( reader.GetOrdinal( "ID" ) ); |
| Console.WriteLine( " ID: {0}", i ); |
| |
| string s = reader.GetString( reader.GetOrdinal( "NAME" ) ); |
| Console.WriteLine( " NAME: {0} = {1}", s, s == "ä¸æ–‡" ); |
| r++; |
| } |
| Console.WriteLine( "Rows retrieved: {0}", r ); |
| |
| //alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight |
| #if !SQLITE_SILVERLIGHT |
| SqliteCommand command = new SqliteCommand( "PRAGMA table_info('TEST_TABLE')", con ); |
| DataTable dataTable = new DataTable(); |
| SqliteDataAdapter dataAdapter = new SqliteDataAdapter(); |
| dataAdapter.SelectCommand = command; |
| dataAdapter.Fill( dataTable ); |
| DisplayDataTable( dataTable, "Columns" ); |
| #endif |
| |
| Console.WriteLine( "Close and cleanup..." ); |
| con.Close(); |
| con = null; |
| |
| Console.WriteLine( "Test1 Done." ); |
| } |
| public void Test3() |
| { |
| Console.WriteLine( "Test3 (Date Paramaters) Start." ); |
| |
| Console.WriteLine( "Create connection..." ); |
| SqliteConnection con = new SqliteConnection(); |
| |
| string dbFilename = @"SqliteTest3.db"; |
| string cs = string.Format( "Version=3,uri=file:{0}", dbFilename ); |
| |
| Console.WriteLine( "Set connection String: {0}", cs ); |
| |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine( "Open database..." ); |
| con.Open(); |
| |
| Console.WriteLine( "create command..." ); |
| IDbCommand cmd = con.CreateCommand(); |
| |
| Console.WriteLine( "create table TEST_TABLE..." ); |
| cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert ..." ); |
| cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)"; |
| cmd.Parameters.Add( |
| new SqliteParameter |
| { |
| ParameterName = "@DATETEXT", |
| Value = DateTime.Now |
| } |
| ); |
| |
| cmd.ExecuteNonQuery(); |
| |
| |
| Console.WriteLine( "SELECT data from TBL..." ); |
| cmd.CommandText = "SELECT * FROM tbl"; |
| IDataReader reader = cmd.ExecuteReader(); |
| int r = 0; |
| Console.WriteLine( "Read the data..." ); |
| while ( reader.Read() ) |
| { |
| Console.WriteLine( " Row: {0}", r ); |
| int i = reader.GetInt32( reader.GetOrdinal( "ID" ) ); |
| Console.WriteLine( " ID: {0}", i ); |
| |
| string s = reader.GetString( reader.GetOrdinal( "DATE_TEXT" ) ); |
| Console.WriteLine( " DATE_TEXT: {0}", s ); |
| r++; |
| } |
| Console.WriteLine( "Rows retrieved: {0}", r ); |
| |
| |
| Console.WriteLine( "Close and cleanup..." ); |
| con.Close(); |
| con = null; |
| |
| Console.WriteLine( "Test3 Done." ); |
| } |
| |
| //nSoftware code for Threading |
| string connstring_T4; |
| public void Test4() |
| { |
| string dbFilename = "threading_t4.db"; |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| connstring_T4 = @"Version=3,busy_timeout=100,uri=file:" + dbFilename; |
| |
| Setup_T4(); |
| InsertSameTable_T4(); //concurrent inserts |
| SelectorWrite_T4(); //concurrent selects and inserts |
| Console.WriteLine( "Testing for Threading done. Press enter to continue" ); |
| Console.In.Read(); |
| } |
| private void SelectorWrite_T4() |
| { |
| //concurrent reads/writes in the same table, if there were only Selects it would be preferable for the sqlite engine not to lock internally. |
| for ( int i = 0; i < 10; i++ ) |
| { |
| Console.WriteLine( "SELECT/INSERT ON Thread {0}", i ); |
| Thread worker = new Thread( () => |
| { |
| // Cannot use value of i, since it exceeds the scope of this thread and will be |
| // reused by multiple threads |
| int aValue = 100 + Thread.CurrentThread.ManagedThreadId; |
| int op = aValue % 2; |
| |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T4; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| if ( op == 0 ) |
| { |
| cmd.CommandText = String.Format( "Select * FROM ATABLE" ); |
| cmd.ExecuteReader(); |
| } |
| else |
| { |
| cmd.CommandText = String.Format( "INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue ); |
| Console.WriteLine( cmd.CommandText ); |
| cmd.ExecuteNonQuery(); |
| } |
| } ); |
| worker.Start(); |
| } |
| } |
| //we need concurrency support on a table level inside of the database file. |
| private void InsertSameTable_T4() |
| { |
| for ( int i = 0; i < 10; i++ ) |
| { |
| Console.WriteLine( "INSERTING ON Thread {0}", i ); |
| Thread worker = new Thread( () => |
| { |
| // Cannot use value of i, since it exceeds the scope of this thread and will be |
| // reused by multiple threads |
| |
| int aValue = Thread.CurrentThread.ManagedThreadId; |
| |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T4; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| cmd.CommandText = String.Format( "INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue ); |
| Console.WriteLine( cmd.CommandText ); |
| cmd.ExecuteNonQuery(); |
| } |
| ); |
| worker.Start(); |
| } |
| } |
| |
| private void Setup_T4() |
| { |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T4; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" ); |
| cmd.ExecuteNonQuery(); |
| } |
| |
| // Software code for Threading |
| string connstring_T5; |
| public void Test5() |
| { |
| string dbFilename = "threading_t5.db"; |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| connstring_T5 = @"Version=3,busy_timeout=2500,uri=file:" + dbFilename; |
| |
| Setup_T5(); |
| MultiInsertsSameThread_T5(); //concurrent inserts |
| Console.WriteLine( "Threads are running..." ); |
| Console.In.Read(); |
| } |
| |
| private void MultiInsertsSameThread_T5() |
| { |
| for ( int i = 0; i < 10; i++ ) |
| { |
| //Console.WriteLine( "SELECT/INSERT ON Thread {0}", i ); |
| Thread worker = new Thread( () => |
| { |
| string commandt = String.Empty; |
| try |
| { |
| // Cannot use value of i, since it exceeds the scope of this thread and will be |
| // reused by multiple threads |
| int aValue = 100 + Thread.CurrentThread.ManagedThreadId; |
| int op = aValue % 2; |
| |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T5; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| if ( op == 0 ) |
| { |
| for ( int j = 0; j < 1000; j++ ) |
| { |
| int rows; |
| int retry = 0; |
| cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", ( aValue * 10000 ) + j ); |
| commandt = cmd.CommandText; |
| do |
| { |
| rows = cmd.ExecuteNonQuery(); |
| if ( rows == 0 ) |
| { |
| retry += 1; // Insert Failed |
| Console.WriteLine( cmd.CommandText ); |
| Console.WriteLine( "retry {0}", retry ); |
| Console.WriteLine( ( (SqliteCommand)cmd ).GetLastError() ); |
| } |
| } while ( rows == 0 && retry < 5 ); |
| } |
| } |
| else |
| { |
| cmd.CommandText = String.Format( "Select * FROM ATABLE" ); |
| commandt = cmd.CommandText; |
| cmd.ExecuteReader(); |
| } |
| } |
| catch ( Exception ex ) |
| { |
| Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) ); |
| } |
| } ); |
| |
| worker.Start(); |
| } |
| } |
| |
| private void Setup_T5() |
| { |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T5; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" ); |
| cmd.ExecuteNonQuery(); |
| } |
| |
| // Software code for Threading & Transactions |
| static string connstring_T6; |
| public void Test6() |
| { |
| string dbFilename = "threading_t6.db"; |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| connstring_T6 = @"Version=3,busy_timeout=2000,uri=file:" + dbFilename; |
| |
| Setup_T6(); |
| MultiInsertsTransactionsSameThread_T6(); //concurrent inserts |
| Console.WriteLine( "Threads are running..." ); |
| Console.In.Read(); |
| } |
| |
| static Random rnd = new Random(); |
| private void MultiInsertsTransactionsSameThread_T6() |
| { |
| for ( int i = 0; i < 20; i++ ) |
| { |
| Thread.Sleep( rnd.Next( 100, 1000 ) ); |
| Console.WriteLine( "Launching Thread {0}", i ); |
| Thread worker = new Thread( SQLiteClientTestDriver.T6_ThreadStart ); |
| worker.Start( i ); |
| } |
| } |
| private static void T6_ThreadStart( object data ) |
| { |
| string commandt = String.Empty; |
| int i = (int)data; |
| try |
| { |
| int aValue = 100 + i; |
| int op = aValue % 2; |
| |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T6; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| if ( op == 0 ) |
| { |
| SqliteTransaction trans = (SqliteTransaction)con.BeginTransaction(); |
| for ( int j = 0; j < 5000; j++ ) |
| { |
| int rows; |
| int retry = 0; |
| cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", ( aValue * 10000 ) + j ); |
| commandt = cmd.CommandText; |
| do |
| { |
| rows = cmd.ExecuteNonQuery(); |
| if ( rows == 0 ) |
| { |
| retry += 1; // Insert Failed |
| Console.WriteLine( "retry {0}:{1}:{2}", retry, ( (SqliteCommand)cmd ).GetLastError(), cmd.CommandText ); |
| Thread.Sleep( rnd.Next( 50, 1000 ) ); |
| } |
| } while ( rows == 0 && retry < 10 ); |
| } |
| trans.Commit(); |
| } |
| else |
| { |
| cmd.CommandText = String.Format( "Select * FROM ATABLE" ); |
| commandt = cmd.CommandText; |
| cmd.ExecuteReader(); |
| } |
| } |
| catch ( Exception ex ) |
| { |
| Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) ); |
| } |
| } |
| private void Setup_T6() |
| { |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T6; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" ); |
| cmd.ExecuteNonQuery(); |
| } |
| |
| // Software code for Threading |
| string connstring_T7; |
| public void Test7() |
| { |
| string dbFilename = "threading_t7.db"; |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| connstring_T7 = @"Version=3,busy_timeout=1000,uri=file:" + dbFilename; |
| |
| MultiInsertsSameThread_T7(); //concurrent table creation |
| Console.WriteLine( "Threads are running..." ); |
| Console.In.Read(); |
| } |
| |
| private void MultiInsertsSameThread_T7() |
| { |
| List<Thread> lthread = new List<Thread>(); |
| for ( int i = 0; i < 70; i++ ) |
| { |
| Thread.Sleep( rnd.Next( 100, 1000 ) ); |
| Console.WriteLine( "Launching Thread {0}", i ); |
| Thread worker = new Thread( this.T7_ThreadStart ); |
| lthread.Add( worker ); |
| worker.Start( i ); |
| } |
| bool alldone = false; |
| |
| while ( !alldone ) |
| { |
| alldone = true; |
| for ( int i = 0; i < lthread.Count; i++ ) |
| { |
| if ( lthread[i].ThreadState == ThreadState.Running ) |
| alldone = false; |
| Thread.Sleep( 100 ); |
| } |
| } |
| Console.WriteLine( "finished" ); |
| } |
| |
| private void T7_ThreadStart( object iSequence ) |
| { |
| int aValue = (int)iSequence * 1000; |
| |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T7; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| string commandt = String.Format( "CREATE TABLE IF NOT EXISTS ATABLE{0}(A integer primary key , B varchar (50), C integer, D varchar (500))", aValue ); |
| cmd.CommandText = commandt; |
| try |
| { |
| cmd.ExecuteNonQuery(); |
| Console.WriteLine( "Created table: ATABLE" + aValue ); |
| } |
| catch ( Exception ex ) |
| { |
| Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) ); |
| } |
| } |
| |
| private void Setup_T7() |
| { |
| SqliteConnection con = new SqliteConnection(); |
| con.ConnectionString = connstring_T7; |
| con.Open(); |
| IDbCommand cmd = con.CreateCommand(); |
| cmd = con.CreateCommand(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)"; |
| cmd.ExecuteNonQuery(); |
| cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" ); |
| cmd.ExecuteNonQuery(); |
| } |
| |
| // Connectio string parsing tests/ |
| public void Test8 () |
| { |
| Console.WriteLine ("Test8 Start."); |
| |
| Console.WriteLine ("Create connection..."); |
| SqliteConnection con = new SqliteConnection (); |
| |
| string dbFilename = @"SqliteTest3.db"; |
| |
| // Test Read Only = True, missing db file |
| string cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename); |
| |
| Console.WriteLine ("Set connection string: {0}", cs); |
| |
| if (File.Exists (dbFilename)) |
| File.Delete (dbFilename); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine ("Open database..."); |
| con.Open (); |
| |
| Console.WriteLine ("create command..."); |
| IDbCommand cmd = con.CreateCommand (); |
| |
| Console.WriteLine ("create table TEST_TABLE..."); |
| cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )"; |
| bool didFail = false; |
| try { |
| cmd.ExecuteNonQuery (); |
| } catch (Exception ex) { |
| didFail = true; |
| } |
| if (!didFail) { |
| Console.WriteLine ("Test failed!"); |
| throw new ApplicationException("Test failed!"); |
| } |
| |
| con.Close (); |
| |
| // Test Read Only = True, existng db file |
| cs = string.Format ("Version=3;uri=file:{0}", dbFilename); |
| |
| Console.WriteLine ("Set connection string: {0}", cs); |
| |
| if (File.Exists (dbFilename)) |
| File.Delete (dbFilename); |
| |
| con.ConnectionString = cs; |
| con.Open (); |
| |
| cmd = con.CreateCommand (); |
| cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )"; |
| cmd.ExecuteNonQuery (); |
| con.Close (); |
| |
| cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename); |
| |
| Console.WriteLine ("Set connection string: {0}", cs); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine ("Open database..."); |
| con.Open (); |
| |
| Console.WriteLine ("create command..."); |
| cmd = con.CreateCommand (); |
| |
| Console.WriteLine ("create table TEST_TABLE2..."); |
| cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )"; |
| didFail = false; |
| try { |
| cmd.ExecuteNonQuery (); |
| } catch (Exception ex) { |
| didFail = true; |
| } |
| if (didFail) { |
| Console.WriteLine ("Test failed!"); |
| throw new ApplicationException("Test failed!"); |
| } |
| |
| // Test FailIfMissing = True, existng db file |
| cs = string.Format ("Version=3;FailIfMissing=True;uri=file:{0}", dbFilename); |
| |
| Console.WriteLine ("Set connection string: {0}", cs); |
| |
| if (File.Exists (dbFilename)) |
| File.Delete (dbFilename); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine ("Open database..."); |
| con.Open (); |
| |
| Console.WriteLine ("create command..."); |
| cmd = con.CreateCommand (); |
| |
| Console.WriteLine ("create table TEST_TABLE2..."); |
| cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )"; |
| didFail = false; |
| try { |
| cmd.ExecuteNonQuery (); |
| } catch (Exception ex) { |
| didFail = true; |
| } |
| if (!didFail) { |
| Console.WriteLine ("Test failed!"); |
| throw new ApplicationException("Test failed!"); |
| } |
| |
| Console.WriteLine( "Test8 Done." ); |
| } |
| |
| public void Issue_65() |
| { |
| //alxwest: causes error "Unable to open database" as TempDirectory.ToString() is set to "B:/TEMP/" |
| //string datasource = "file://" + TempDirectory.ToString() + "myBigDb.s3db"; |
| string datasource = "file://" + "myBigDb.s3db"; |
| |
| using ( IDbConnection conn = new SqliteConnection( "uri=" + datasource ) ) |
| { |
| long targetFileSize = (long)Math.Pow( 2, 32 ) - 1; |
| int rowLength = 1024; // 2^10 |
| |
| long loopCount = (int)( targetFileSize / rowLength ) + 10000; |
| |
| char[] chars = new char[rowLength]; |
| for ( int i = 0; i < rowLength; i++ ) |
| { |
| chars[i] = 'A'; |
| } |
| |
| string row = new string( chars ); |
| |
| conn.Open(); |
| IDbCommand cmd = conn.CreateCommand(); |
| |
| try |
| { |
| cmd.CommandText = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY;"; |
| cmd.ExecuteNonQuery(); |
| |
| cmd.CommandText = "drop table if exists [MyTable]"; |
| cmd.ExecuteNonQuery(); |
| |
| cmd.CommandText = "create table [MyTable] ([MyField] varchar(" + rowLength + ") null)"; |
| cmd.ExecuteNonQuery(); |
| |
| cmd.CommandText = "insert into [MyTable] ([MyField]) VALUES ('" + row + "')"; |
| for ( int i = 0; i < loopCount; i++ ) |
| { |
| cmd.ExecuteNonQuery(); |
| } |
| } |
| catch |
| { |
| Console.WriteLine( ( (SqliteCommand)cmd ).GetLastError() ); |
| } |
| finally |
| { |
| cmd.Cancel(); |
| conn.Close(); |
| conn.Dispose(); |
| } |
| } |
| } |
| |
| // Issue 76 Encryption is not implemented in C#SQLite client connection and command objects |
| public void Issue_76() |
| { |
| Console.WriteLine( "Test for Issue_76 Start." ); |
| |
| Console.WriteLine( "Create connection..." ); |
| SqliteConnection con = new SqliteConnection(); |
| |
| string dbFilename = @"SqliteTest3.db"; |
| string cs = string.Format( "Version=3,uri=file:{0}", dbFilename ); |
| |
| Console.WriteLine( "Set connection String: {0}", cs ); |
| |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine( "Open database..." ); |
| con.Open(); |
| |
| Console.WriteLine( "create command..." ); |
| IDbCommand cmd = con.CreateCommand(); |
| |
| cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'"; |
| Console.WriteLine( cmd.CommandText ); |
| cmd.ExecuteNonQuery(); |
| |
| cmd.CommandText = "create table a (b); insert into a values ('row 1');select * from a;"; |
| Console.WriteLine( cmd.CommandText ); |
| Console.WriteLine( "Result {0}", cmd.ExecuteScalar() ); |
| |
| Console.WriteLine( "Close & Reopen Connection" ); |
| con.Close(); |
| con.Open(); |
| |
| cmd.CommandText = "select * from a;"; |
| Console.WriteLine( cmd.CommandText ); |
| Console.WriteLine( "Result {0}", cmd.ExecuteScalar() ); |
| |
| Console.WriteLine( "Close & Reopen Connection" ); |
| con.Close(); |
| con.Open(); |
| cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'"; |
| Console.WriteLine( cmd.CommandText ); |
| cmd.ExecuteNonQuery(); |
| |
| cmd.CommandText = "select * from a;"; |
| Console.WriteLine( cmd.CommandText ); |
| Console.WriteLine( "Result {0}", cmd.ExecuteScalar() ); |
| |
| Console.WriteLine( "Close & Reopen Connection with password" ); |
| con.Close(); |
| |
| con.ConnectionString = cs + ",Password=0x73656372657470617373776F72640f11"; |
| con.Open(); |
| cmd.CommandText = "select * from a;"; |
| Console.WriteLine( cmd.CommandText ); |
| Console.WriteLine( "Result {0}", cmd.ExecuteScalar() ); |
| |
| con = null; |
| |
| Console.WriteLine( "Issue_76 Done." ); |
| } |
| |
| // Multi thread execution special command or ddl command results in exception.. |
| public void Issue_86() |
| { |
| AppDomain.CurrentDomain.UnhandledException += |
| ( sender, eventArgs ) => |
| { |
| Console.WriteLine( eventArgs.ExceptionObject ); |
| }; |
| int flags = Sqlite3.SQLITE_OPEN_NOMUTEX | Sqlite3.SQLITE_OPEN_READWRITE | Sqlite3.SQLITE_OPEN_CREATE; |
| for ( int i = 0; i < 10; i++ ) |
| { |
| Console.WriteLine( "Running Thread {0}", i ); |
| var t = new Thread( |
| () => |
| { |
| string dbFilename = string.Format( "db{0}.sqlite", Thread.CurrentThread.ManagedThreadId ); |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| Console.WriteLine( "Using Database {0}", dbFilename ); |
| Sqlite3.sqlite3 db = null; |
| Sqlite3.sqlite3_open_v2( dbFilename, out db, flags, null ); |
| var command = string.Format( "create table [t{0}] (id, name, amount)", Thread.CurrentThread.ManagedThreadId ); |
| ExecuteCommand( db, command ); |
| Sqlite3.sqlite3_close( db ); |
| } ); |
| t.Start(); |
| } |
| } |
| |
| private static void ExecuteCommand( Sqlite3.sqlite3 db, string command ) |
| { |
| int rc; |
| Sqlite3.Vdbe vm = null; |
| if ( Sqlite3.sqlite3_prepare_v2( db, command, command.Length, ref vm, 0 ) != Sqlite3.SQLITE_OK ) |
| { |
| throw new InvalidOperationException( string.Format( "Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg( db ) ) ); |
| } |
| rc = Sqlite3.sqlite3_step( vm ); |
| if ( rc != Sqlite3.SQLITE_DONE && rc != Sqlite3.SQLITE_ROW ) |
| { |
| throw new InvalidOperationException( string.Format( "Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg( db ) ) ); |
| } |
| Sqlite3.sqlite3_finalize( vm ); |
| } |
| |
| //alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight |
| #if !SQLITE_SILVERLIGHT |
| public void DisplayDataTable( DataTable table, string name ) |
| { |
| Console.WriteLine( "Display DataTable: {0}", name ); |
| int r = 0; |
| foreach ( DataRow row in table.Rows ) |
| { |
| Console.WriteLine( "Row {0}", r ); |
| int c = 0; |
| foreach ( DataColumn col in table.Columns ) |
| { |
| |
| Console.WriteLine( " Col {0}: {1} {2}", c, col.ColumnName, col.DataType ); |
| Console.WriteLine( " Value: {0}", row[col] ); |
| c++; |
| } |
| r++; |
| } |
| Console.WriteLine( "Rows in data table: {0}", r ); |
| |
| } |
| #endif |
| |
| public void Issue_119() |
| { |
| Console.WriteLine( "Test Start." ); |
| |
| Console.WriteLine( "Create connection..." ); |
| SqliteConnection con = new SqliteConnection(); |
| |
| string dbFilename = @"=SqliteTest3=.db"; |
| string cs = string.Format( "Version=3,uri=file:{0}", dbFilename ); |
| |
| Console.WriteLine( "Set connection String: {0}", cs ); |
| |
| if ( File.Exists( dbFilename ) ) |
| File.Delete( dbFilename ); |
| |
| con.ConnectionString = cs; |
| |
| Console.WriteLine( "Open database..." ); |
| con.Open(); |
| |
| Console.WriteLine( "create command..." ); |
| IDbCommand cmd = con.CreateCommand(); |
| |
| Console.WriteLine( "create table TEST_TABLE..." ); |
| cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert row 1..." ); |
| cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "insert row 2..." ); |
| cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )"; |
| cmd.ExecuteNonQuery(); |
| |
| Console.WriteLine( "SELECT data from TEST_TABLE..." ); |
| cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE"; |
| IDataReader reader = cmd.ExecuteReader(); |
| int r = 0; |
| Console.WriteLine( "Read the data..." ); |
| while ( reader.Read() ) |
| { |
| Console.WriteLine( " Row: {0}", r ); |
| int rowid = reader.GetInt32( reader.GetOrdinal( "RowID" ) ); |
| Console.WriteLine( " RowID: {0}", rowid ); |
| |
| int i = reader.GetInt32( reader.GetOrdinal( "COLA" ) ); |
| Console.WriteLine( " COLA: {0}", i ); |
| |
| string s = reader.GetString( reader.GetOrdinal( "COLB" ) ); |
| Console.WriteLine( " COLB: {0}", s ); |
| |
| DateTime dt = reader.GetDateTime( reader.GetOrdinal( "COLC" ) ); |
| Console.WriteLine( " COLB: {0}", dt.ToString( "MM/dd/yyyy HH:mm:ss" ) ); |
| |
| r++; |
| } |
| |
| Console.WriteLine( "Close and cleanup..." ); |
| con.Close(); |
| con = null; |
| |
| Console.WriteLine( "Test Done." ); |
| } |
| |
| public void Issue_124() |
| { |
| Console.WriteLine( "Test Start." ); |
| |
| Sqlite3.sqlite3 db = null; |
| Sqlite3.sqlite3_open( ":memory:", out db ); |
| Sqlite3.Vdbe stmt = null; |
| string zero = null; |
| string val; |
| |
| Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; |
| |
| //create table |
| { |
| Sqlite3.sqlite3_prepare_v2( db, "create table Test (val REAL NOT NULL)", -1, ref stmt, ref zero ); |
| Sqlite3.sqlite3_step( stmt ); |
| Sqlite3.sqlite3_finalize( stmt ); |
| } |
| |
| //insert 0.1 |
| { |
| Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.1')", -1, ref stmt, ref zero ); |
| Sqlite3.sqlite3_step( stmt ); |
| Sqlite3.sqlite3_finalize( stmt ); |
| } |
| //insert 0.1 |
| { |
| Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.2')", -1, ref stmt, ref zero ); |
| Sqlite3.sqlite3_step( stmt ); |
| Sqlite3.sqlite3_finalize( stmt ); |
| } |
| |
| //insert 0.000000001 |
| { |
| Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.000000001')", -1, ref stmt, ref zero ); |
| Sqlite3.sqlite3_step( stmt ); |
| Sqlite3.sqlite3_finalize( stmt ); |
| } |
| |
| //invariant culture |
| { |
| System.Console.WriteLine( "invariant culture" ); |
| Sqlite3.sqlite3_prepare_v2( db, "select val from Test", -1, ref stmt, ref zero ); |
| Sqlite3.sqlite3_step( stmt ); |
| val = Sqlite3.sqlite3_column_text( stmt, 0 ); |
| System.Console.WriteLine( "value: " + val ); |
| Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture( "ru" ); |
| Sqlite3.sqlite3_step( stmt ); |
| val = Sqlite3.sqlite3_column_text( stmt, 0 ); |
| System.Console.WriteLine( "value: " + val ); |
| Sqlite3.sqlite3_step( stmt ); |
| val = Sqlite3.sqlite3_column_text( stmt, 0 ); |
| System.Console.WriteLine( "value: " + val ); |
| Sqlite3.sqlite3_finalize( stmt ); |
| } |
| |
| //ru-ru culture |
| { |
| System.Console.WriteLine( "ru" ); |
| Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture( "ru" ); |
| Sqlite3.sqlite3_prepare_v2( db, "select val from Test", -1, ref stmt, ref zero ); |
| Sqlite3.sqlite3_step( stmt ); |
| val = Sqlite3.sqlite3_column_text( stmt, 0 ); |
| System.Console.WriteLine( "value: " + val ); |
| Sqlite3.sqlite3_step( stmt ); |
| val = Sqlite3.sqlite3_column_text( stmt, 0 ); |
| System.Console.WriteLine( "value: " + val ); |
| Sqlite3.sqlite3_finalize( stmt ); |
| } |
| |
| Console.WriteLine( "Test Done." ); |
| } |
| |
| public void Issue_149 () |
| { |
| Console.WriteLine ("Test Issue 149 - Test Start."); |
| |
| SqliteConnection con = new SqliteConnection (); |
| |
| string dbFilename = @"SqliteTest3.db"; |
| string cs = string.Format ("Version=3,uri=file:{0}", dbFilename); |
| |
| if (File.Exists (dbFilename)) |
| File.Delete (dbFilename); |
| |
| con.ConnectionString = cs; |
| con.Open (); |
| |
| Console.WriteLine ("create command..."); |
| IDbCommand cmd = con.CreateCommand (); |
| |
| cmd.CommandText = "CREATE TABLE TestZeroArrayTable (TheField BLOB)"; |
| cmd.ExecuteNonQuery (); |
| |
| cmd.CommandText = "INSERT INTO TestZeroArrayTable VALUES(?)"; |
| IDbDataParameter field6 = cmd.CreateParameter (); |
| cmd.Parameters.Add (field6); |
| |
| byte[] data = new byte[] {}; |
| |
| field6.Value = data; |
| cmd.ExecuteNonQuery (); |
| |
| cmd.CommandText = "SELECT TheField FROM TestZeroArrayTable"; |
| byte[] res = (byte[])cmd.ExecuteScalar (); |
| |
| if (res == null) |
| throw new ArgumentException ("res == null"); |
| if (res.Length != 0) |
| throw new ArgumentException ("res.Length != 0"); |
| |
| Console.WriteLine ("Test Done."); |
| } |
| |
| public static int Main( string[] args ) |
| { |
| SQLiteClientTestDriver tests = new SQLiteClientTestDriver(); |
| |
| int Test = 1; |
| switch ( Test ) |
| { |
| case 1: |
| tests.Test1(); |
| break; |
| case 2: |
| tests.Test2(); |
| break; |
| case 3: |
| tests.Test3(); |
| break; |
| case 4: |
| tests.Test4(); |
| break; |
| case 5: |
| tests.Test5(); |
| break; |
| case 6: |
| tests.Test6(); |
| break; |
| case 7: |
| tests.Test7(); |
| break; |
| case 8: |
| tests.Test8(); |
| break; |
| case 65: |
| tests.Issue_65(); |
| break; |
| case 76: |
| tests.Issue_76(); |
| break; |
| case 86: |
| tests.Issue_86(); |
| break; |
| case 119: |
| tests.Issue_119(); |
| break; |
| case 124: |
| tests.Issue_124(); |
| break; |
| case 149: |
| tests.Issue_149(); |
| break; |
| } |
| Console.WriteLine( "Press Enter to Continue" ); |
| Console.ReadKey(); |
| tests = null; |
| |
| return 0; |
| } |
| } |
| } |
