/// <summary> /// This method executes a query using the given execution type and command /// behavior and returns the results. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connection"> /// The connection used to create and execute the command. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, SQLiteConnection connection, params object[] args ) { SQLiteConnection.Check(connection); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { SQLiteParameter parameter = arg as SQLiteParameter; if (parameter == null) { parameter = command.CreateParameter(); parameter.DbType = DbType.Object; parameter.Value = arg; } command.Parameters.Add(parameter); } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return(command.ExecuteNonQuery(commandBehavior)); } case SQLiteExecuteType.Scalar: { return(command.ExecuteScalar(commandBehavior)); } case SQLiteExecuteType.Reader: { return(command.ExecuteReader(commandBehavior)); } } } return(null); }
/// <summary> /// Executes passed SQL string using appropriate SQLiteExecuteType. /// </summary> /// <param name="sql">SQL string to execute.</param> /// <param name="executeType">Type of execution to use.</param> private async void ExecuteSql(string sql, SQLiteExecuteType executeType = SQLiteExecuteType.Default) { try { // Open connection, if necessary. if (Connection.State != System.Data.ConnectionState.Open) { await Connection.OpenAsync(); } // Create command from passed SQL string. var command = new SQLiteCommand(sql, Connection); // Check executeType parameter. switch (executeType) { case SQLiteExecuteType.Default: var rowsAffected = await command.ExecuteNonQueryAsync(); // Output number of affected rows. Logging.Log($"Query complete: {rowsAffected} row(s) affected."); break; case SQLiteExecuteType.Reader: var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { // Retrieve values for each read row. var values = new object[reader.FieldCount - 1]; reader.GetValues(values); // Output values. Logging.Log(values); } break; case SQLiteExecuteType.Scalar: await command.ExecuteScalarAsync(); break; case SQLiteExecuteType.None: break; default: // Throw exception if executeType value is something unexpected. throw new ArgumentOutOfRangeException(nameof(executeType), executeType, null); } } catch (System.Data.Linq.DuplicateKeyException exception) { // Output expected DuplicateKeyException. Logging.Log(exception); } catch (SQLiteException exception) { // Output unexpected SQLiteException. Logging.Log(exception, false); } }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type, closes the connection, and returns the results. If the /// connection string is null, a temporary in-memory database connection will /// be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, string connectionString, params object[] args ) { return(Execute( commandText, executeType, CommandBehavior.Default, connectionString, args)); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection, and returns /// the results. If the connection string is null, a temporary in-memory /// database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { if (connectionString == null) { connectionString = DefaultConnectionString; } using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { if (arg is SQLiteParameter) { command.Parameters.Add((SQLiteParameter)arg); } else { command.Parameters.Add(new SQLiteParameter(DbType.Object, arg)); } } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return(command.ExecuteNonQuery(commandBehavior)); } case SQLiteExecuteType.Scalar: { return(command.ExecuteScalar(commandBehavior)); } case SQLiteExecuteType.Reader: { return(command.ExecuteReader(commandBehavior)); } } } } return(null); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection unless a data /// reader is created, and returns the results. If the connection string is /// null, a temporary in-memory database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { SQLiteConnection connection = null; try { if (connectionString == null) { connectionString = DefaultConnectionString; } using (connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { SQLiteParameter parameter = arg as SQLiteParameter; if (parameter == null) { parameter = command.CreateParameter(); parameter.DbType = DbType.Object; parameter.Value = arg; } command.Parameters.Add(parameter); } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return(command.ExecuteNonQuery(commandBehavior)); } case SQLiteExecuteType.Scalar: { return(command.ExecuteScalar(commandBehavior)); } case SQLiteExecuteType.Reader: { bool success = true; try { // // NOTE: The CloseConnection flag is being added here. // This should force the returned data reader to // close the connection when it is disposed. In // order to prevent the containing using block // from disposing the connection prematurely, // the innermost finally block sets the internal // no-disposal flag to true. The outer finally // block will reset the internal no-disposal flag // to false so that the data reader will be able // to (eventually) dispose of the connection. // return(command.ExecuteReader( commandBehavior | CommandBehavior.CloseConnection)); } catch { success = false; throw; } finally { // // NOTE: If an exception was not thrown, that can only // mean the data reader was successfully created // and now owns the connection. Therefore, set // the internal no-disposal flag (temporarily) // in order to exit the containing using block // without disposing it. // if (success) { connection._noDispose = true; } } } } } } } finally { // // NOTE: Now that the using block has been exited, reset the // internal disposal flag for the connection. This is // always done if the connection was created because // it will be harmless whether or not the data reader // now owns it. // if (connection != null) { connection._noDispose = false; } } return(null); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection unless a data /// reader is created, and returns the results. If the connection string is /// null, a temporary in-memory database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { SQLiteConnection connection = null; try { if (connectionString == null) connectionString = DefaultConnectionString; using (connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { if (arg is SQLiteParameter) command.Parameters.Add((SQLiteParameter)arg); else command.Parameters.Add(new SQLiteParameter(DbType.Object, arg)); } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return command.ExecuteNonQuery(commandBehavior); } case SQLiteExecuteType.Scalar: { return command.ExecuteScalar(commandBehavior); } case SQLiteExecuteType.Reader: { bool success = true; try { // // NOTE: The CloseConnection flag is being added here. // This should force the returned data reader to // close the connection when it is disposed. In // order to prevent the containing using block // from disposing the connection prematurely, // the innermost finally block sets the internal // no-disposal flag to true. The outer finally // block will reset the internal no-disposal flag // to false so that the data reader will be able // to (eventually) dispose of the connection. // return command.ExecuteReader( commandBehavior | CommandBehavior.CloseConnection); } catch { success = false; throw; } finally { // // NOTE: If an exception was not thrown, that can only // mean the data reader was successfully created // and now owns the connection. Therefore, set // the internal no-disposal flag (temporarily) // in order to exit the containing using block // without disposing it. // if (success) connection._noDispose = true; } } } } } } finally { // // NOTE: Now that the using block has been exited, reset the // internal disposal flag for the connection. This is // always done if the connection was created because // it will be harmless whether or not the data reader // now owns it. // if (connection != null) connection._noDispose = false; } return null; }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type, closes the connection, and returns the results. If the /// connection string is null, a temporary in-memory database connection will /// be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, string connectionString, params object[] args ) { return Execute( commandText, executeType, CommandBehavior.Default, connectionString, args); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection, and returns /// the results. If the connection string is null, a temporary in-memory /// database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { if (connectionString == null) connectionString = DefaultConnectionString; using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { if (arg is SQLiteParameter) command.Parameters.Add((SQLiteParameter)arg); else command.Parameters.Add(new SQLiteParameter(DbType.Object, arg)); } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return command.ExecuteNonQuery(commandBehavior); } case SQLiteExecuteType.Scalar: { return command.ExecuteScalar(commandBehavior); } case SQLiteExecuteType.Reader: { return command.ExecuteReader(commandBehavior); } } } } return null; }
public static object Execute(string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args) { object obj; SQLiteConnection sQLiteConnection = null; try { if (connectionString == null) { connectionString = SQLiteCommand.DefaultConnectionString; } SQLiteConnection sQLiteConnection1 = new SQLiteConnection(connectionString); sQLiteConnection = sQLiteConnection1; using (sQLiteConnection1) { sQLiteConnection.Open(); using (SQLiteCommand sQLiteCommand = sQLiteConnection.CreateCommand()) { sQLiteCommand.CommandText = commandText; if (args != null) { object[] objArray = args; for (int i = 0; i < (int)objArray.Length; i++) { object obj1 = objArray[i]; SQLiteParameter sQLiteParameter = obj1 as SQLiteParameter; if (sQLiteParameter == null) { sQLiteParameter = sQLiteCommand.CreateParameter(); sQLiteParameter.DbType = DbType.Object; sQLiteParameter.Value = obj1; } sQLiteCommand.Parameters.Add(sQLiteParameter); } } switch (executeType) { case SQLiteExecuteType.None: { break; } case SQLiteExecuteType.NonQuery: { obj = sQLiteCommand.ExecuteNonQuery(commandBehavior); return(obj); } case SQLiteExecuteType.Scalar: { obj = sQLiteCommand.ExecuteScalar(commandBehavior); return(obj); } case SQLiteExecuteType.Reader: { bool flag = true; try { try { obj = sQLiteCommand.ExecuteReader(commandBehavior | CommandBehavior.CloseConnection); return(obj); } catch { flag = false; throw; } } finally { if (flag) { sQLiteConnection._noDispose = true; } } break; } default: { goto case SQLiteExecuteType.None; } } } } return(null); } finally { if (sQLiteConnection != null) { sQLiteConnection._noDispose = false; } } return(obj); }