/// <summary> /// Select and open if needed the properly connection to database /// </summary> /// <param name="tbName"></param> /// <param name="pw"></param> /// <returns></returns> private TurboDBConnection GetConnection(string dbName, string pw) { TurboDBConnection conn; // Set (and open if needed) connection properly from request or class properties if (dbName != null && dbName.Length > 0) { conn = new TurboDBConnection(dbName); conn.Exclusive = false; conn.Open(); } else { if (_defaultConn.State != System.Data.ConnectionState.Open) { if (DataSourceName != null && DataSourceName.Length > 0) { _defaultConn = new TurboDBConnection(DataSourceName); _defaultConn.Exclusive = false; _defaultConn.Open(); } else { return(null); // TODO generate error } } conn = _defaultConn; } // Set password from request or class properties _currentDatabasePassword = pw != null ? pw : DataSourcePassword; // Event subscription for databases with table password conn.PasswordNeeded += turboDBConnection_PasswordNeeded; return(conn); }
public TurboDbDataReader(string dataSourceName, Authentication authentication) : this() { DataSourceName = dataSourceName; Authentication = authentication; defaultConn = new TurboDBConnection(dataSourceName); defaultConn.ConnectionString = ""; defaultConn.Exclusive = false; }
/// <summary> /// Constructor with dataSourceName and authentication /// </summary> /// <param name="dataSourceName">Name of datasource (DataBase) origin of data</param> /// <param name="authentication">Authentication data; username and password</param> public TurboDbDataAccessBase(string dataSourceName, Authentication authentication) : this() { DataSourceName = dataSourceName; Authentication = authentication; _defaultConn = new TurboDBConnection(DataSourceName); _defaultConn.ConnectionString = "Datasource=" + DataSourceName + ";Exclusive=False;"; _defaultConn.Exclusive = false; }
/// <summary> /// Read datatable from database with request parameters /// </summary> /// <param name="request">The request with all parameters</param> /// <returns>A datatable filled with results</returns> private DataTable GetDataTable(SqlRequest request) { // Reading data from database TurboDBConnection conn = GetConnection(request.DataSourceName, request.DataSourcePassword); TurboDBDataAdapter apt = new TurboDBDataAdapter(BuildSqlString(request), conn); DataTable tb = new DataTable(); apt.Fill(tb); return(tb); }
/// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="request"></param> /// <returns></returns> public override Response <T> Get <T>(GetRequest request) { string s = ParseFilters(request.Filters); TurboDBConnection conn = new TurboDBConnection(request.DataSourceName); conn.ConnectionString = ""; conn.Exclusive = false; // System.Diagnostics.Debug.Print(s); return(null); }
/// <summary> /// Simplify the creation of a TurboDB command object by allowing /// a CommandType and Command Text to be provided /// </summary> /// <remarks> /// e.g.: /// TurboDBCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers"); /// </remarks> /// <param name="connection">A valid TurboDBConnection object</param> /// <param name="commandType">CommandType (TableDirect, Text)</param> /// <param name="commandText">CommandText</param> /// <returns>A valid TurboDBCommand object</returns> public static TurboDBCommand CreateCommand(TurboDBConnection connection, CommandType commandType, string commandText ) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported."); // If we receive parameter values, we need to figure out where they go if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" ); // Create a TurboDBCommand TurboDBCommand cmd = new TurboDBCommand(commandText, connection ); cmd.CommandType = CommandType.Text ; return cmd; }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset) against the specified TurboDBConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <returns>An string containing the resultset generated by the command</returns> public static string ExecuteXml(TurboDBConnection connection, CommandType commandType, string commandText, params TurboDBParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution TurboDBCommand cmd = new TurboDBCommand(); try { PrepareCommand(cmd, connection, (TurboDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet TurboDBDataAdapter obj_Adapter =new TurboDBDataAdapter (cmd); DataSet ds=new DataSet(); ds.Locale =CultureInfo.InvariantCulture; obj_Adapter.Fill(ds); // Detach the TurboDBParameters from the command object, so they can be used again cmd.Parameters.Clear(); string retval= ds.GetXml(); ds.Clear(); obj_Adapter.Dispose (); return retval; } catch { if( mustCloseConnection ) connection.Close(); throw; } }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset and takes no parameters) against the provided TurboDBConnection. /// </summary> /// <remarks> /// e.g.: /// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction"); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param> /// <returns>An string containing the resultset generated by the command</returns> public static string ExecuteXml(TurboDBConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of TurboDBParameters return ExecuteXml(connection, commandType, commandText, (TurboDBParameter[])null); }
/// <summary> /// Execute a TurboDBCommand (that returns a 1x1 resultset) against the specified TurboDBConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(TurboDBConnection connection, CommandType commandType, string commandText, params TurboDBParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); // Create a command and prepare it for execution TurboDBCommand cmd = new TurboDBCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (TurboDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the TurboDBParameters from the command object, so they can be used again cmd.Parameters.Clear(); if( mustCloseConnection ) connection.Close(); return retval; }
/// <summary> /// Execute a TurboDBCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">A valid connection string for a TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params TurboDBParameter[] commandParameters) { if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" ); // Create & open a TurboDBConnection, and dispose of it after we are done using (TurboDBConnection connection = new TurboDBConnection(connectionString)) { // Call the overload that takes a connection in place of the connection string return ExecuteScalar(connection, commandType, commandText, commandParameters); } }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset) against the specified TurboDBConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// FillDataset(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param> /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// </param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> public static void FillDataset(TurboDBConnection connection, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params TurboDBParameter[] commandParameters) { FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters); }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset) against the specified TurboDBConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <returns>A dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(TurboDBConnection connection, CommandType commandType, string commandText, params TurboDBParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); // Create a command and prepare it for execution TurboDBCommand cmd = new TurboDBCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (TurboDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet TurboDBDataAdapter da = new TurboDBDataAdapter(cmd); DataSet ds = new DataSet(); ds.Locale =CultureInfo.InvariantCulture; // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the TurboDBParameters from the command object, so they can be used again cmd.Parameters.Clear(); if( mustCloseConnection ) connection.Close(); // Return the dataset return ds; }
/// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command /// </summary> /// <param name="command">The TurboDBCommand to be prepared</param> /// <param name="connection">A valid TurboDBConnection, on which to execute this command</param> /// <param name="transaction">A valid TurboDBTransaction, or 'null'</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param> private static void PrepareCommand(TurboDBCommand command, TurboDBConnection connection, TurboDBTransaction transaction, CommandType commandType, string commandText, TurboDBParameter[] commandParameters, out bool mustCloseConnection ) { if( command == null ) throw new ArgumentNullException( "command" ); if(commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported."); // If the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { mustCloseConnection = true; connection.Open(); } else { mustCloseConnection = false; } // Associate the connection with the command command.Connection = connection; // Set the command text (SQL statement) command.CommandText = commandText; // If we were provided a transaction, assign it if (transaction != null) { if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); command.Transaction = transaction; } // Set the command type command.CommandType = commandType; // Attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }
/// <summary> /// Private helper method that execute a TurboDBCommand (that returns a resultset) against the specified TurboDBTransaction and TurboDBConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="transaction">A valid TurboDBTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param> /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// </param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> private static void FillDataset(TurboDBConnection connection, TurboDBTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params TurboDBParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( dataSet == null ) throw new ArgumentNullException( "dataSet" ); // Create a command and prepare it for execution TurboDBCommand command = new TurboDBCommand(); bool mustCloseConnection = false; PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet TurboDBDataAdapter dataAdapter = new TurboDBDataAdapter(command); try { // Add the table mappings specified by the user if (tableNames != null && tableNames.Length > 0) { string tableName = "Table"; for (int index=0; index < tableNames.Length; index++) { if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" ); dataAdapter.TableMappings.Add(tableName, tableNames[index]); tableName += (index + 1).ToString(); } } // Fill the DataSet using default values for DataTable names, etc dataAdapter.Fill(dataSet); // Detach the TurboDBParameters from the command object, so they can be used again command.Parameters.Clear(); if( mustCloseConnection ) connection.Close(); } finally { dataAdapter.Dispose(); } }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// FillDataset(connString, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">A valid connection string for a TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param> /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// </param> public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params TurboDBParameter[] commandParameters) { if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" ); if( dataSet == null ) throw new ArgumentNullException( "dataSet" ); // Create & open a TurboDBConnection, and dispose of it after we are done using (TurboDBConnection connection = new TurboDBConnection(connectionString)) { // Call the overload that takes a connection in place of the connection string FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters); } }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset and takes no parameters) against the provided TurboDBConnection. /// </summary> /// <remarks> /// e.g.: /// FillDataset(conn, CommandType.Text, "Select * from TableTransaction", ds, new string[] {"orders"}); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param> /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// </param> public static void FillDataset(TurboDBConnection connection, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames) { FillDataset(connection, commandType, commandText, dataSet, tableNames, null); }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// TurboDBDataReader dr = ExecuteReader(connString, CommandType.Text, "Select Orderid from TableTransaction where ProdId=?", new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">A valid connection string for a TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <returns>A TurboDBDataReader containing the resultset generated by the command</returns> public static TurboDBDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params TurboDBParameter[] commandParameters) { if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" ); TurboDBConnection connection = null; try { connection = new TurboDBConnection(connectionString); // Call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(connection, null, commandType, commandText, commandParameters,TurboDBConnectionOwnership.Internal); } catch { // If we fail to return the TurboDBDatReader, we need to close the connection ourselves if( connection != null ) connection.Close(); throw; } }
/// <summary> /// Create and prepare a TurboDBCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. /// </remarks> /// <param name="connection">A valid TurboDBConnection, on which to execute this command</param> /// <param name="transaction">A valid TurboDBTransaction, or 'null'</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by TurboDBHelper</param> /// <returns>TurboDBDataReader containing the results of the command</returns> private static TurboDBDataReader ExecuteReader(TurboDBConnection connection, TurboDBTransaction transaction, CommandType commandType, string commandText, TurboDBParameter[] commandParameters, TurboDBConnectionOwnership connectionOwnership) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution TurboDBCommand cmd = new TurboDBCommand(); try { PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create a reader TurboDBDataReader dataReader; // Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == TurboDBConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // Detach the TurboDBParameters from the command object, so they can be used again. // HACK: There is a problem here, the output parameter values are fletched // when the reader is closed, so if the parameters are detached from the command // then the TurboDBReader can´t set its values. // When this happen, the parameters can´t be used again in other command. bool canClear = true; foreach(TurboDBParameter commandParameter in cmd.Parameters) { if (commandParameter.Direction != ParameterDirection.Input) canClear = false; } if (canClear) { cmd.Parameters.Clear(); } return dataReader; } catch { if( mustCloseConnection ) connection.Close(); throw; } }
/// <summary> /// Execute a TurboDBCommand (that returns a resultset) against the specified TurboDBConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// TurboDBDataReader dr = ExecuteReader(conn, CommandType.Text, "Select Orderid from TableTransaction where ProdId=?", new TurboDBParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid TurboDBConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParamters used to execute the command</param> /// <returns>A TurboDBDataReader containing the resultset generated by the command</returns> public static TurboDBDataReader ExecuteReader(TurboDBConnection connection, CommandType commandType, string commandText, params TurboDBParameter[] commandParameters) { // Pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader(connection, (TurboDBTransaction)null, commandType, commandText, commandParameters, TurboDBConnectionOwnership.External); }